Upgrading MySQL 5.0 to 5.5 with mysqldump

With MySQL 5.5 becoming GA last week, there will probably be a few requests to upgrade to 5.5 straight from 5.0.

MySQL's binary upgrade strategy involves turning off the server, upgrading the software, turning the server back on and running the mysql_upgrade script (which will modify the privilege tables, and run table checks on your data). And this is only supposed to work between versions: the MySQL 5.5 mysql_upgrade script assumes it's looking at a mysql database from 5.1, not 5.0.

A binary upgrade from MySQL 5.0 to 5.5, then, involves an intermediate step of upgrading to 5.1 (and running its mysql_upgrade script) before installing 5.5 and running its mysql_upgrade.

If, however, you want to avoid this step and can backup your data using mysqldump, here's what you can do:

1. Take a backup but without the mysql database.
   shell> mysqldump [other options] --databases db1 db2 db3 > main_backup.sql
   
2. Take a backup of the mysql database, but without the table definitions and with the inserts specifying the columns:
   shell> mysqldump [other options] --complete-insert --no-create-info mysql > mysql_backup.sql
   
3. Shut down current mysql installation, move datadir contents (so datadir is empty for a fresh install of 5.5).
   
4. Install and start up MySQL 5.5 installation. By default you should be able to login as user root with no password.
   
5. Import mysql_backup.sql into MySQL.
   
6. Run these in the client to remove the default users and set the old users and passwords from the previous installation:
   mysql> DELETE FROM mysql.user WHERE user = '';
mysql> DELETE FROM mysql.user WHERE user = 'root' AND password = '';
mysql> FLUSH PRIVILEGES;
   (You might want to check for other unauthorized users at this stage too). You can now log off and log back in as your original root or admin user.
   
7. Import main_backup.sql into MySQL.
   
8. Run the mysql_upgrade script (for proof you did everything right).
   

I did a diff on the schemas of mysql in 5.0 and 5.5, and while columns and tables are added and a few column definitions are changed slightly, no columns are renamed. An insert of mysql data from 5.0 will insert correctly into 5.5 (I tested this using 5.0.87 and 5.5.8).