MySQL Blog Archive
For the latest blogs go to
Upgrading Directly from MySQL 5.0 to 5.7 using an 'In Place' Upgrade

This article is the second in a two-part series on upgrading MySQL.  The first article, Upgrade from 5.0 directly to 5.7 using mysqldump, discussed performing an upgrade using the mysqldump utility.  We call that a ‘Dump’ Upgrade.  In this article, we will discuss what we call an ‘In Place’ Upgrade, also known as a Binary Upgrade or a Live Upgrade.

We feel it is valuable to share that it is possible to upgrade from MySQL 5.0 to 5.7, even though such a procedure is not officially supported. One of the large limitations of such an upgrade is that as functionality changes, you may miss deprecation warnings and other behavioural changes. Consequently this may result in a much riskier upgrade.

As with the ‘Dump’ Upgrade, it is a good idea to take a backup of the MySQL server prior to making any changes. Before any upgrade process, be sure to read the related upgrade documentation for the version to which you are moving, as well as any versions en route to that version. This can include important tips and information about running the upgrade:  upgrading to 5.1, upgrading to 5.5, upgrading to 5.6, or upgrading to 5.7.

The ‘In Place’ Upgrade is a quicker option than the ‘Dump’ Upgrade because you don’t need to load the databases in as part of the upgrade process.  Again, this saves load time, but a backup before upgrading is even more important, because you are performing all your upgrade steps ‘In Place’ on the original data directory.  Because you are using the same datafiles, you are unable to take advantage of some new features that require a server rebuild, such as creating undo tablespaces (introduced in 5.6.3) or rebuilding your existing innodb tables into individual tablespaces (innodb_file_per_table enabled by default in 5.6.6).

I used the following steps to perform the upgrade.

  1. Start with a basic MySQL 5.0.96 server instance with the sakila schema loaded. I used –no-defaults here for simplicity.
  2. Shut down the MySQL server.  This would be a great time to back up your MySQL server.  Next, change your directory to the new MySQL version and start up the MySQL server.  This uses the same data directory, so all your existing data files should be located in <DATADIR>.

    Please note, when starting a MySQL server 5.7.x for the first time on a data directory created by 5.0.x or 5.1.x, you must use –skip-grant-tables.
  3. Run mysql_upgrade. This upgrades all the system tables and checks the user schemas.
  4. Load in the help tables (optional step).
  5. Shut down and restart the MySQL server.
  6. Run mysqlcheck for a status on all schema tables.

If you are upgrading from 5.0.96, this method will most likely require a rebuild/upgrade of some user tables or triggers. In the case of sakila, I received the following warnings from the repair section of mysql_upgrade for the triggers (extraneous output was removed):

Reloading is required due to changes in the metadata stored for triggers. I took the following steps to recreate the triggers.

  1. Extract the triggers using mysqldump.
  2. Generate a SQL script to drop the existing triggers. Exclude ‘sys’ as that those triggers will not be extracted with the mysqldump procedure outlined above.
  3. Drop the existing triggers.
  4. Reload the triggers.
  5. Run mysqlcheck for a status on all tables.

The steps noted above for performing the ‘In Place’ upgrade were successful in the upgrade from 5.0.96 or 5.1.73 or 5.5.46 or 5.6.27 to 5.7.9. Validation was done using mysqlcheck, counting tables and columns and routines for all schemas, running basic select/insert/update/delete statements on the user schema, and by executing/calling user functions and procedures.

That’s it for now. THANK YOU for using MySQL!