MySQL

SELECT * (but not x) FROM table

mysql> set @table = "sometable";

mysql> set @database = "somedb";

mysql> set @cols=(select group_concat(column_name) from information_schema.columns where table_name=@table and table_schema=@database and column_name not in ('somecolumn'));

mysql> set @sql=concat("SELECT ", @cols, " FROM ", @table);

mysql> prepare s from @sql;

mysql> execute s;

MySQL server has gone away

During import of a large file (using 'source databasebackup.sql'), you might see something like:

Query OK, 16 rows affected (0.11 sec)
Records: 16  Duplicates: 0  Warnings: 0

ERROR 2006 (HY000) at line 188 in file: 'databasebackup.sql': MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2537
Current database: mydatabase

Query OK, 0 rows affected, 1 warning (0.00 sec)

This is likely to be because the max_allowed_packet variable hasn't been set high enough on either the server or the client. Edit /etc/my.cnf and add to the [mysqld] section:

set max_allowed_packet=1073741824

And add the same to your ~/.my.cnf in the [mysql] section. Restart the MySQL server and retry the import.

innodb_log_file_size

If you start to use innodb a lot and see the following error in the mysql error log, you need to tune the innodb_log_file_size variable.

130923  9:27:06  InnoDB: ERROR: the age of the last checkpoint is 9448377,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

To change the variable innodb_log_file_size, you have to shutdown cleanly, rename the logfiles as backups, edit the config and restart the server. Once new logfiles are being created, the old ones can be deleted. The server will crash on startup if you change the value without renaming the old logfiles.

mysql> pager grep sequence; show engine innodb status\G select sleep(60); show engine innodb status\G pager;
PAGER set to 'grep sequence'
Log sequence number 83 3166929032
1 row in set (0.00 sec)

1 row in set (59.99 sec)

Log sequence number 83 3167469638
1 row in set (0.00 sec)

Default pager wasn't set, using stdout.
mysql> select (3167469638 - 3166929032)/(1024*1024) as MB_per_min;
+------------+
| MB_per_min |
+------------+
|     0.5156 |
+------------+
1 row in set (0.00 sec)

This results in 30MB per hour so we may want to increase the value on this server to 15M instead of the default of 5M (half the overall MB per hour since there are two logfiles).

Reset mysql password

To reset the mysql password for a user (other than root), login with the root account and enter the following:

mysql> update mysql.user set password=password('XXXX') where user='username';
mysql> flush privileges;

If it's the root user you need to reset, stop mysql and then start it

# /usr/sbin/mysqld_safe -u root --skip-grant-tables --skip-networking

In another terminal, use the above SQL to reset the password for user root. Once done, restart the server as normal:

# mysqladmin shutdown

At this point the first terminal will display some messages about the server exiting and then return to the prompt.

# service mysqld start