MySQL Downgrade Caveats

mysql downgradeIn this blog, we’ll discuss things to watch out for during a MySQL downgrade.

Previously, I wrote the blog MySQL upgrade best practices. Besides upgrading your MySQL version, sometimes you need to downgrade. When it comes to downgrading MySQL, there are two types of downgrade methods supported:

  1. In-Place Downgrade: In this method, you use the existing data directory and replace MySQL binaries, followed by a mysql_upgrade execution. This type of downgrade is supported within the same release series. For example, in-place downgrades are supported when moving from 5.7.12 to 5.7.10.
  2. SQL Dump Downgrade: An SQL dump is another downgrade method, also known as “Logical Downgrade.” This method involves a backup of all database tables by using the mysqldump program. You can also use mydumper for parallel backup to improve backup times, followed by replacing the MySQL binaries and restoring the dump into a downgraded version of MySQL. Then use the mysql_upgrade program to complete the downgrade process. This type of downgrade is supported within same release series, and between different release levels. For example, downgrading from 5.6 to 5.5 or downgrading from 5.7 to 5.6 or 5.5

Before downgrading, you need to be aware of few things that could affect the process. I’ll list few of the important things here. For all the changes affecting the downgrade process, you should check the manual. Also, it’s advisable to check the release notes for the specific version you are downgrading to avoid any surprises.

  • In MySQL 5.6, file contains line count and replication delay values. The values contain a file format that differs from previous versions of MySQL. If you are downgrading below 5.6 in a replication setup, then the older server will not read that file format. To correct this issue on the slaves in question, you need to modify the in an editor to remove the initial line containing the number of lines.
  • Downgrading from 5.6 to older version via the “In-Place” method. MySQL 5.5 can cause some issues due to differences in on-disk format for temporal types. Basically, if tables containing TIME, DATETIME or TIMESTAMP columns were CREATEd or ALTERed on 5.6, you can’t use those with 5.5. The recommended method to downgrade from version 5.6 to 5.5 is the “logical downgrade” method with mysqldump.

You can use the query below to identify the tables and columns that might be affected by this problem. Some of them are system tables in the mysql database, which means MySQL is one of the databases you need to dump/restore.

In a replication environment, it’s recommended that you upgrade/downgrade your slave(s) first. I tried to downgrade one of slave from Percona Server 5.6 to Percona Server 5.5 in the replication hierarchy via logical downgrade as following:

At this point, Percona Server 5.6 downgraded to Percona Server 5.5. Now, let’s try to restore the backup taken from the 5.6 instance to the 5.5 instance.

After analyzing the results, I found that “STATS_PERSISTENT” was appended in few of the MySQL system tables during backup. It failed during restore because innodb_stats_persistent is not available in MySQL 5.5. I found this reported bug verified the issue.

To remedy this problem, you need to backup the MySQL user grants separately from the application databases: pt-show-grants from Percona Toolkit comes to the rescue!

From the Percona Server 5.6 slave:

And to restore on the Percona Server 5.5 slave:

At this point, the slave downgraded to Percona Server 5.5 from 5.6 and was restored. When trying to restore a replication after a downgrade, the replication failed with the below error, where the master is using MySQL version 5.6 and the slave is downgraded to version 5.5.

Remember, replication from a newer major version to an older major version of MySQL (for example a 5.6 master and a 5.5 replica) is not supported, and is generally not recommended. Replication failed because starting in MySQL 5.6.6, the new binlog_checksum option defaults to CRC32. Since that option did not exist in MySQL 5.5, the replica can’t handle the checksums coming from the master. The other way around, the CRC (cyclic redundant checksum) fails because the binlog is in a pre-5.6 format and has no checksum info. You need to set binlog_checksum to NONE on the MySQL master server.

This will resume the replication on 5.5 slaves from the 5.6 master. I recommend setting binlog_checksum=NONE in my.cnf under the [mysqld] section to make this change persistent across a reboot of the master server 5.6.

To summarize, downgrading through the “logical dump” method requires you to exclude dump/restore the MySQL system database, and dump only user grants with the help of pt-show-grants  to restore the database users and privileges. Also, you need to make binlog_checksum compatible (i.e., NULL) when downgrading from version 5.6 to an older version (e.g., 5.5 or 5.1). Along with that, you need to make sure binlog_rows_query_log_events is enabled and binlog_row_image is set to FULL. Also, when replicating from version 5.6 to 5.5, GTID-based replication is not supported and you need to set gtid_mode=OFF in 5.6. All those variables should be set properly on the 5.6 master. Check the manual for more details.


A MySQL downgrade is the reversal of an upgrade, and can be painful. It might be necessary if you upgrade without proper testing. One of the reasons for a downgrade is if you notice that your application is malfunctioning, MySQL is crashing or performance is not up to mark after an upgrade.

Share this post

Comment (1)

  • Adam mulla