I have been using partitioning in MySQL 5.1 since it has been in late beta, and with the odd bump I’ve been quite satisfied with its stability and ease of use. Perhaps the days of maintaining nasty application logic to maintain tables like table_yyyymm manually are finally over!

While some control freaks out there might still prefer the painful approach of manually keeping dated tables and using merge tables/views to link them together, you still have a lot of flexibility in how you manage individual partitions with the ALTER TABLE REORGANIZE/CHANGE/ADD PARTITION commands.

One trick i’ve discovered to work pretty well, is that you can swap in and out partitions right out from under MySQL’s nose. This can be useful in some situations.

WARNING!! This is not supported by MySQL to the best of my knowledge, and possibly is a risky, bad idea. Proceed only if you think you know what you’re doing!

Let’s say we have a table big_table, with self-explanatory partitions

p_200811
p_200812
p_200901

In your data directory, assuming you are using MyISAM, you will see MySQL has created files like these:

big_table#P#p_200811.MYD/MYI
big_table#P#p_200812.MYD/MYI
big_table#P#p_200901.MYD/MYI

Now on the server, running live, to make sure MySQL releases any locks on files at the OS level:
LOCK TABLE big_table WRITE; # alternatively use FLUSH TABLES WITH READ LOCK to lock whole DB
FLUSH TABLE big_table;

Now let’s say you want to do some intensive processing on your November data set. You can simply copy  the file to a temporary location, or possibly to another machine:

cp $DATADIR/dbname/big_table*200811* /tmplocation

Then you can unlock your table with UNLOCK TABLES;

Let’s say you have a box you want to do this on. Simply create a table-structure only copy of your table on that machine (*exactly* the same layout!! Bad Things(TM) will probably happen if this gets confused). Then repeat the procedure above in MySQL and on the OS:

mysql> FLUSH TABLES WITH READ LOCK;
$ cp /tmploc/big_table*200811* $DATADIR_SECOND_BOX/dbname/ # this overwrites the empty files; make sure they are owned by “mysql” or the db owner!
mysql> UNLOCK TABLES;

After unlocking, mysql will reload the files, and assuming the permissions and table structures are all correct, doing a count(*) on your date range should confirm that the data has moved over successfully. Now apply your datafixes, and repeat the procedure to return the data back to the host machine!

One possible application of this approach could be to distribute a very large index rebuild to a number of machines if your tables are partitioned into several chunks.

Given that InnoDB is sensitive to the dbname, I don’t expect that this would be possible with innodb tables, although I have not tried. I expect it to work with the ARCHIVE engine but also have not confirmed.

Hoping to get some comments and suggestions on how to improve on this process, or hear about other people’s experience hacking at partitioned tables.