in response to a query from André Simões (also known as ITXpander), I slapped together a MySQL script that outputs
mysqldumpcommands 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 worksThe script works by querying the
information_schema.PARTITIONSsystem view to generate an appropriate expression for mysqldump's
--whereoption. The generated command also redirects the output to a file with this name pattern:
<schema>.<table>.<partition-name>.sqlFor example, for this table (taken from the MySQL reference manual):
CREATE TABLE members (the script generates the following commands:
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
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
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) < 1960" test members > test.members.p0.sqlTip: in order to obtain directly executable output from the
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
mysqlcommand line tool, run the script with the
FeaturesCurrently, the script supports the following partitioning methods:
LINEAR HASHmethod is currently not supported, but I may implement that in the future.
Currently I do not have plans to implement the
LINEAR KEYpartitioning 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.PARTITIONStable 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.
AcknowledgementsThanks 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.