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).

Geez, do posts on Blogger not have titles? Either that or my interface never got upgraded from the olden days.

Anyways, here begins my tech blog, which I said I'd create for years and years and only started when I managed to login to this blog which I'd last used in 2003. Hey, it's got a good URL.

I'm currently a week behind in my SQL Server 2005 class; the first assignment was due last Thursday night and it's now Wednesday afternoon. Given that I've been a DBA for over 3 years the reason I'm behind in class is because Windows hates me, as does all Microsoft software. So many installation problems, you don't want to know.

Currently I've got a Windows Server 2003 that can't connect to the internet (hours of hair-pulling torture last week) running on a triple-core AMD computer but I can't install SQL Server on multiple-core AMD machines. I had to edit boot.ini to use only one processor, uninstall what had been installed of SQL Server, and now start again. Now I am clicking a whole lot of Next > buttons and praying. Well, cursing and praying.