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!

6 thoughts on “Upgrading Directly from MySQL 5.0 to 5.7 using an ‘In Place’ Upgrade

  1. Is there any way how to setup replication from 5.5 to 5.7? I used your method for upgrade, but I need to start replication, but it fails on unknown server_uuid on 5.5 master. Thank you.

    1. Hi Vit – I am working to write up something including replication for upgrading, but that is not ready to be published. There would need to be a little bit more information on the problem that you are having. Possibly addressing to the MySQL forum (http://forums.mysql.com/) would be a better location for solving specific issues? Thanks! Susan

      1. Hello Susan, after some research, MySQL 5.5 doesn’t support system variable server_uuid, which is required for the replication.

        This variable is present from MySQL 5.6. So to be able to switch online from 5.5 to 5.7 without any downtime, I had to setup 3rd server with 5.6 version and setup replication from server 1 (5.5) -> server 2 (5.6) -> server 3 (5.7) .

        Your manual was very helpful, but keep in mind it can’t be used for master or slave servers.

        1. Hi Vit,

          maybe you don’t need it anymore, but it could help others.
          The newest version 5.7.13 makes replication 5.5 -> 5.7 possible:

          “Replication: Slaves running MySQL 5.7 could not connect to a MySQL 5.5 master due to an error retrieving the server_uuid, which is not part of MySQL 5.5. This was caused by changes in the method of retrieving the server_uuid. (Bug #22748612)

          References: This issue is a regression of: Bug #21455603.”


          – Jan

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.