Wednesday, December 17, 2008

Backing up MySQL partitioned tables is nothing special

In case you were wondering, after seeing this self-defined tutorial, backing up MySQL 5.1 partitioned tables does not require any particular additional technology.
This tutorial presents MySQL 5.1 in a short paragraph, and then starts talking about a commercial backup application, as if it were the only way of backing up partitioned tables.
The title, for sure, is misleading. Backing Up MySQL Partitioned Tables. Reading such a title, readers expect either a list of possible solutions, or being presented with the only existing way of dealing with this task. Instead, the only instructions that you get is how to use the above mentioned tool, which is definitely not the only way of getting the job done. It is also doubly misleading because, since partitioning is a new technology, the casual reader may think "oh? What's peculiar with partitioned tables? do I need to take extra care to back them up?. Instead, there's nothing special about partitioned tables. Depending on the backup method, handling of partitioned tables is completely transparent for the user.
I don't doubt that this tool can do the job, but there are native tools with MySQL that can accomplish the task just as well.
There are several ways of backing up a partitioned table. But the first one that springs to mind is mysqldump.
The backup is as simple as:
mysqldump --opt db_name table_name > file.dump
The restore is equally simple:
mysql db_name < file.dump 

Now, to be fair, I know one case where the backup of partitioned tables is problematic, but the one presented in the FUD tutorial was not it. The only possible trouble happens when you define partitions with different data directories and want to restore the table to a server that has a different directory structure. I had this problem two years ago, and I solved it by splitting the dump to keep the DDL and the DML in separate scripts. I wonder if there is a method that can detect and fix this problem on the spot.

Update: Here's the original article I am talking about.
howto_forge article about partition backup

1 comment:

Unknown said...

I would like to make correction. The article is about "ZRM for MySQL community edition" which is open source software (not a commercial backup software). ZRM for MySQL uses a model similar to MySQL community and enterprise releases.

The "howtoforge" article had an error in title which will be fixed soon.