Making MySQL 5.1 Dump Compatible with MySQL 5.0

While posting this, the process of restoring a MySQL dump is still running. When all you have is a MySQL 5.1 dump and you need to restore it to a server running MySQL 5.0, you’re out of luck, because MySQL 5.0 will throw up errors about MySQL 5.1 BTREE keys which is not recogniced by MySQL 5.0.

Background

During a visit on the production site far far away from Metro Manila, we perform a full database backup using mysqldump. The servers are running MySQL 5.1 database. We need the data for testing a certain module for performance. However, all servers in the office are running MySQL 5.0 (for maximum compatibility – while the production server running MySQL 5.1 for performance).

When I tried to restore the dump, it throws an error about the BTREE keys, which are not recognized by MySQL 5.0. The dirty way to solve this is to replace the KEY declaration in the dump file into a compatible type, thus need editing the file. However, it was a 200MB dump file that makes it very difficult to open in Windows.

Good thing I have a virtual machine running Slackware Linux Current as of March 16 2010 which has MySQL 5.1.x. What I did was restore the dump to MySQL 5.1.x then dump again using some sort of compatibility mode.

1. Restore the MySQL 5.1 dump to intermediate database also running 5.1

mysql -D db_name -h virtual_hostname -u username -p < mysql5.1-dump-file.sql
&#91;/sourcecode&#93;

2. Dump it again with compatibility mode (key stuff only)

&#91;sourcecode language='bash'&#93;
mysqldump db_name -h virtual_hostname -u username -p --compatible=no_key_options > mysql5.0-compatible.sql

3. Then restore it finally to the MySQL 5.0 testing server.

mysql -D db_name -h testing_hostname -u username -p < mysql5.0-compatible.sql [/sourcecode] That's it, only if you have an intermediate database server. If not, you're off to editing dump files.