MySQL: Partition-wise backups with mysqldump

To whom it may concern,

in response to a query from André Simões (also known as ITXpander), I slapped together a MySQL script that outputs mysqldump commands for backing up individual partitions of the tables in the current schema. The script is maintained as a snippet at MySQL Forge a gist on github.

How it works

The script works by querying the information_schema.PARTITIONS system view to generate an appropriate expression for mysqldump's --where option. The generated command also redirects the output to a file with this name pattern:

<schema>.<table>.<partition-name>.sql

For example, for this table (taken from the MySQL reference manual):

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

the script generates the following commands:

mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) < 1960" test members > test.members.p0.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1960 and  YEAR(joined) < 1970" test members > test.members.p1.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1970 and  YEAR(joined) < 1980" test members > test.members.p2.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1980 and  YEAR(joined) < 1990" test members > test.members.p3.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1990 and  YEAR(joined) < 18446744073709551615" test members > test.members.p4.sql

Tip: in order to obtain directly executable output from the mysql command line tool, run the script with the --skip-column-names (or -N) option.

Features

Currently, the script supports the following partitioning methods:

• HASH


• LIST


• RANGE

Limitations

The LINEAR HASH method is currently not supported, but I may implement that in the future.

Currently I do not have plans to implement the KEY and LINEAR KEY partitioning methods, but I may reconsider if and when I have more information about the storage-engine specific partitioning functions used by these methods.

Finally, I should point out that querying the information_schema.PARTITIONS table is dog-slow. This may not be too big of an issue, however it is pretty annoying. If anybody has some tips to increase performance, please let me know.

Acknowledgements

Thanks to André for posing the problem. I had a fun hour of procrastination to implement this, and it made me read part of the MySQL reference manual on partitioning.

I also would like to thank Giuseppe Maxia (the Datacharmer) for providing valuable feedback. If you're interested in either partitioning or the mysql command line, you should visit his tutorials at the MySQL conference, april 12-15, 2010.

The most misunderstood character in MySQL's SQL-Dialect

Many MySQL users like to use the MySQL monitor. The MySQL monitor is the executable bin/mysql (or if you are on MS Windows, bin\mysql.exe) and is perhaps better known as the MySQL command-line tool or MySQL command-line client.

The MySQL monitor

Basically, the MySQL monitor is a standard, text-based terminal that connects to a running MySQL Server when it is started. Once the connection is established, the user can enter text via the computer keyboard, which is at some point sent to the server. The server attempts to interpret the received text as SQL, which is then executed, sending the result back to the MySQL monitor. The MySQL monitor then prints the received results on the screen:

mysql> SELECT COUNT(*) FROM world.City;
+----------+
| COUNT(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.02 sec)

Here we see that immediately after the mysql> prompt, a line of text was entered. In the next few lines we see the result returned by the server. Or, to be really precise, the server returned a resultset consisting of one column and one row having the value 4079. The MySQL monitor is responsible for printing the ASCII art and the padding, printing this in a nice, human readable manner.

Statement terminator

This little dialog may seem monumentally uninteresting, but I hope to show that there really is something here to be learned. Let's focus for a moment on relating this dialog with the general description I gave of the MySQL client/server communication right preceding this example.

I explained that the client sends the text to the server, which interprets it as SQL and returns the result. The 'interesting' thing is this: if the server is the one that interprets text as SQL, how then does the client know when to send the entered text to server?

For many people the answer will be obvious: it is the semi-colon that appears as the last character on the first line of our example, right after the entered text. So the MySQL monitor does not know how to recognize SQL, but it does know how to recognize a semi-colon, and interprets that as a statement-terminator. So, that semi-colon actually never reaches the server - the MySQL monitor just sends the text that appears before the poition where the semi-colon was detected.

The DELIMITER command

We can tell the MySQL monitor to not scan for semi-colons, but for something else entirely. This is done using the DELIMITER command:

mysql> DELIMITER go
mysql> SELECT COUNT(*) FROM world.City
    -> go
+----------+
| COUNT(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.02 sec)

So here we see that the MySQL monitor is instructed to scan for go rather than the semi-colon. The line that goes like DELIMITER go is not an SQL-statement: it is a command recognized by the MySQL monitor, and it is never sent to the server.

The semi-colon in stored routines

Most MySQL users that have created stored routines and the like will be familiar with this DELIMITER command.

The reason is that in many cases, stored routines are composed of typical procedural constructs such as BEGIN...END compound statements and flow-control statements such as IF...THEN and WHILE...DO. These types of statements can themselves contain a list of statements, and the server requires each of these statements to be terminated (or actually, separated) by the semi-colon.

Obviously, if stored routine statements like these would be entered through the MySQL monitor, it is no use if the MySQL monitor still scans for semi-colons. It will just send text prematurely to the server, which in turn tries to interpret each as a well-formed SQL statement, usually resulting in a frenzy of errors being returned back to the MySQL monitor.

So, this is probably one of the most abundant usages of the DELIMITER command: to allow stored routines to be entered through the MySQL monitor.

The semi-colon outside stored routines

What many MySQL users don't realize is that it is perfectly valid to separate normal, non-procedureal SQL statements with the semi-colon. Like when entering stored routines, the MySQL monitor needs to have its delimiter set to something other than the semi-colon, but after having done so, you can enter a batch of statements to be sent to the server at once:

mysql> DELIMITER go
mysql> SELECT COUNT(*)
    -> FROM world.Country;
    -> SELECT COUNT(*)
    -> FROM world.City;
    -> go
+----------+
| COUNT(*) |
+----------+
|      239 |
+----------+
1 row in set (0.00 sec)

+----------+
| COUNT(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.00 sec)

This is not some trick of the MySQL monitor: the batch is sent as a whole, and the results are received as a whole too, all in one network rountrip.