Martello

Here is a useful MySQL command that shows (an approximation of) the amount of storage (disk and memory) consumed by each table in a MySQL database.

SELECT
 TABLE_NAME,
 TABLE_ROWS,
 DATA_LENGTH / (1024*1024),
 INDEX_LENGTH / (1024*1024),
 (DATA_LENGTH + INDEX_LENGTH) / (1024*1024) as total
FROM
 information_schema.TABLES
WHERE
 TABLE_SCHEMA = (SELECT DATABASE())
GROUP BY
 TABLE_NAME
ORDER BY
 total;

Please bear in mind that the data returned will be an approximation, but should still show you a reasonable enough split which of your tables are consuming most of the resources.

Some clever one-liners that print a neatly organised version of your MySQL configuration file without all the comments:

Perl
$ perl -ne 'm/^([^#][^\s=]+)\s*(=.*|)/ && printf("%-35s%s\n", $1, $2)' /etc/my.cnf

Awk
$ awk '! /^#/ && ! /^$/ {if($1 ~ /^\[/ ){gsub("\[","\n[",$1) };printf("%-35s%s %s\n",$1, $2, $3)}' /etc/my.cnf

Grep
$ egrep -v '^$|^#' /etc/my.cnf

Source: MySQL Performance Blog

If you have ever installed of MySQL on a *nix system, you may have come across the mysql_secure_installation script. It takes you through a number of interactive questions/steps that help you to increase the security of your MySQL installation, and is quite a useful script.

As we have a high deree of automation in our server environment, we decided to break this script down to its raw MySQL command, and in a nut shell, here is what it actually does behind the scenes (assuming you want to answer Yes to all of the questions it asks in interactive mode):

UPDATE mysql.user SET Password=PASSWORD('XXXX') WHERE User='root';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host!='localhost';
DROP DATABASE test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;

Naturally, you should replace the XXXX in the first command with an appropriate password.

I was trialling a MySQL database upgrade today (from 5.0 to 5.1) and hit a nasty little gotcha that I feel warrants a mention (mostly to myself). In its simplest form, the upgrade process is very straightforward and involves simply loading data dumped from MySQL 5.0 into a 5.1 database (using a regular mysql command), followed by a once-off execution of the mysql_upgrade command (to bring the various tables and grants up to date):

$ mysql -u root -p testdb < dumped50.sql
$ mysql_upgrade -u root -p testdb

However, when I tried the latter command, I received this error:

Looking for 'mysql' in: mysql
FATAL ERROR: Can't find 'mysql'


I did fine a simple solution here which was to first change to the directory containing the mysql_upgrade program itself and execute it from there:

$ cd /opt/coolstack/mysql/bin
$ ./mysql_upgrade -u root -p testdb

As I said, its a simple solution and I do tend to follow this general rule most of the time but nevertheless, the error generated when you don’t do this is far for informative.

A colleague of mine just sent me this hugely useful MySQL command for seeing which users have privileges in a database, and from which host. It prints a list of other MySQL commands which can then be used to show more precisely what privileges that user has been granted.

SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM mysql.user;

Prior to this, I found myself having to connect to the database from the appropriate (remote) MySQL client and issue a SHOW GRANTS from there.

Thanks to Mate Racz for this one.