Upgrading MySQL

from the Artful MySQL Tips List


Major versions of MySQL since 2000 have been 3.2, 4.0, 4.1, 5.0, 5.1, 6.0, 5.5, 5.6, 5.7 (yep, you read that right: 6.0 preceded 5.5, then 6.0 was withdrawn).

Upgrading within a major version

It is usually safe to upgrade within a major version over the same data. Here's how:

1. Unless you already have a complete backup, backup all databases with:

mysqldump -uUSR -pPWD -K -E -A -R -f >some_backup_dir/mysqlbakVERSION.sql 

substituting your values for USR, PWD and VERSION. If mysqldump reports errors, fix them and repeat.

2. Stop the server

3. Upgrade MySQL in place

4. Restart the server with --skip-grant-tables to disable privilege checking

5. Run mysql_upgrade

6. Restart the server normally, ie without --skip-grant-tables

Upgrading across major versions

For upgrades across major versions, MySQL recommends that for each major version jump, you do a complete backup with mysqldump, then restore that copy of your databases into a new folder for the new installation. Here is a safe sequence:

1. Substituting your values for USR, PWD and VERSION, backup all databases with:

mysqldump -uUSR -pPWD -K -E -A -R -f >some_backup_dir/mysqlbakVERSION.sql 

If you are upgrading to 5.7.2 or later, add --skip_flush_privileges to the above command. If mysqldump reports errors, fix them and repeat.

2. Stop the server

3. Install the next version

4. Restart the server with --skip-grant-tables

5. Reload the dump file using the source command in the mysql client

6. Run mysql_upgrade

7. Restart the server normally, ie without --skip-grant-tables

8. Test thoroughly, and fix any issues before proceeding to the next version

There is a MySQL document about one-step upgrade from 5.0 to 5.6: http://mysqlserverteam.com/upgrading-directly-from-mysql-5-0-to-5-6-with-mysqldump/

Return to the Artful MySQL Tips page