We have a number of tables which we plan to partition based on months. The issue now before we can drop those partition we would like to perform backup. One option we found is this link http://www.percona.com/doc/percona-xtrabackup/howtos/recipes_ibkx_partition.html. Can any one suggest is the best mechanism or there other option such as mysqldump etc?
If you need to get backups of tables individualized, you may want to consider performing a mysqldump on each table into separate files.
Start by creating a list of tables
If you have a moderate number of tables, you could dumps them all at the same time:
If you have too many tables to dump in parallel, then dump all tables in 10 at a time
If you can load a massage the mysqldump as needed.
Here is another alternative: Do a mysqldump of data only and not the schema for each table you will be partitioning. In the above code just dump the data by adding
With the data only dumped to a separate file, you can focus on creating the InnoDB partitioned to your liking and then loading the mysqldump of the data in it.
UPDATE 2013-01-16 08:24 EDT
There are two things you could try
If you prefer dumping one table at a time, you will have to dump everything to one file but do so as a transaction
You could try parallel mysqldumps each marked as a transaction
Hope this helps !!!
The Percona xtrabackup solution that you linked in your question is a good solution. It is a hot backup and doesn't lock innodb tables. I would prefer that to mysqldump in most cases.
Did you try xtrabackup? Have questions about using it?
|show 1 more comment|