MySQL Downgrade Caveats

October 18, 2016

Author

Muhammad Irfan

MySQL

Share this Post:

In 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:

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.

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, relay-log.info 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 relay-log.info 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.

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE<br>FROM INFORMATION_SCHEMA.COLUMNS<br>WHERE DATA_TYPE IN ('TIME','DATETIME','TIMESTAMP')<br>ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

1	mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE<br>FROM INFORMATION_SCHEMA.COLUMNS<br>WHERE DATA_TYPE IN ('TIME','DATETIME','TIMESTAMP')<br>ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

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:

[root@slave ~]# mysql> show slave statusG<br>*************************** 1. row ***************************<br>*** Make note of replication coordinates *******<br><br>[root@slave ~]# mysqldump --single-transaction --routines --all-databases > backup56.sql<br><br>[root@slave ~]# rpm -qa | grep -i "percona"<br>percona-release-0.1-3.noarch<br>percona-nagios-plugins-1.1.6-1.noarch<br>Percona-Server-shared-56-5.6.32-rel78.0.el6.x86_64<br>Percona-Server-server-56-5.6.32-rel78.0.el6.x86_64<br>Percona-Server-client-56-5.6.32-rel78.0.el6.x86_64<br><br>[root@slave ~]# /etc/init.d/mysql stop<br>Shutting down MySQL (Percona Server).. SUCCESS! <br><br>[root@slave ~]# rpm -qa | grep Percona-Server | xargs rpm -e --nodeps<br><br>[root@slave ~]# mv /var/lib/mysql/ /var/lib/mysql56_old/<br><br>[root@slave ~]# yum install Percona-Server-client-55.x86_64 Percona-Server-server-55.x86_64 Percona-Server-shared-55.x86_64<br>Installed:<br>  Percona-Server-client-55.x86_64 0:5.5.51-rel38.1.el6               Percona-Server-server-55.x86_64 0:5.5.51-rel38.1.el6               Percona-Server-shared-55.x86_64 0:5.5.51-rel38.1.el6              <br>Complete!<br><br>[root@slave ~]# /etc/init.d/mysql start<br>Starting MySQL (Percona Server).. SUCCESS! <br><br>[root@slave ~]# mysql_upgrade --upgrade-system-tables --skip-write-binlog<br>Looking for 'mysql' as: mysql<br>The --upgrade-system-tables option was used, databases won't be touched.<br>Running 'mysql_fix_privilege_tables'...<br>OK

[root@slave ~]# mysql> show slave statusG *************************** 1. row *************************** *** Make note of replication coordinates ******* [root@slave ~]# mysqldump --single-transaction --routines --all-databases > backup56.sql [root@slave ~]# rpm -qa | grep -i "percona" percona-release-0.1-3.noarch percona-nagios-plugins-1.1.6-1.noarch Percona-Server-shared-56-5.6.32-rel78.0.el6.x86_64 Percona-Server-server-56-5.6.32-rel78.0.el6.x86_64 Percona-Server-client-56-5.6.32-rel78.0.el6.x86_64 [root@slave ~]# /etc/init.d/mysql stop Shutting down MySQL (Percona Server).. SUCCESS! [root@slave ~]# rpm -qa | grep Percona-Server | xargs rpm -e --nodeps [root@slave ~]# mv /var/lib/mysql/ /var/lib/mysql56_old/ [root@slave ~]# yum install Percona-Server-client-55.x86_64 Percona-Server-server-55.x86_64 Percona-Server-shared-55.x86_64 Installed: Percona-Server-client-55.x86_64 0:5.5.51-rel38.1.el6 Percona-Server-server-55.x86_64 0:5.5.51-rel38.1.el6 Percona-Server-shared-55.x86_64 0:5.5.51-rel38.1.el6 Complete! [root@slave ~]# /etc/init.d/mysql start Starting MySQL (Percona Server).. SUCCESS! [root@slave ~]# mysql_upgrade --upgrade-system-tables --skip-write-binlog Looking for 'mysql' as: mysql The --upgrade-system-tables option was used, databases won't be touched. Running 'mysql_fix_privilege_tables'... OK

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.

[root@slave ~]# mysql < backup56.sql <br>ERROR 1064 (42000) at line 320: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STATS_PERSISTENT=0' at line 11

1	[root@slave ~]# mysql < backup56.sql <br>ERROR 1064 (42000) at line 320: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STATS_PERSISTENT=0' at line 11

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:

[root@slave_5_6 ~]# pt-show-grants --flush > grants.sql<br><br>[root@slave_5_6 ~]# mysqldump --single-transaction --routines --databases db1 db2 > databases.sql<br>

1	[root@slave_5_6 ~]# pt-show-grants --flush > grants.sql<br><br>[root@slave_5_6 ~]# mysqldump --single-transaction --routines --databases db1 db2 > databases.sql<br>

And to restore on the Percona Server 5.5 slave:

[root@slave_5_5 ~]# mysql < grants.sql <br><br>[root@slave_5_5 ~]# mysql < databases.sql

1	[root@slave_5_5 ~]# mysql < grants.sql <br><br>[root@slave_5_5 ~]# mysql < databases.sql

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.

mysql> show slave statusG<br>*************************** 1. row ***************************<br>               Slave_IO_State:<br>                  Master_Host: 10.0.3.131<br>                  Master_User: slaveuser<br>                  Master_Port: 3306<br>                Connect_Retry: 60<br>              Master_Log_File: master-bin.000007<br>          Read_Master_Log_Pos: 4<br>               Relay_Log_File: centos4-relay-bin.000004<br>                Relay_Log_Pos: 151<br>        Relay_Master_Log_File: master-bin.000007<br>             Slave_IO_Running: No<br>            Slave_SQL_Running: Yes<br>.<br>.<br>        Seconds_Behind_Master: NULL<br>Master_SSL_Verify_Server_Cert: No<br>                Last_IO_Errno: 1236<br>                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log; the first event 'master-bin.000006' at 120, the last event read from './master-bin.000007' at 120, the last byte read from './master-bin.000007' at 120.'<br>               Last_SQL_Errno: 0<br>               Last_SQL_Error:<br>  Replicate_Ignore_Server_Ids:<br>             Master_Server_Id: 1<br>1 row in set (0.00 sec)<br>

mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.0.3.131 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000007 Read_Master_Log_Pos: 4 Relay_Log_File: centos4-relay-bin.000004 Relay_Log_Pos: 151 Relay_Master_Log_File: master-bin.000007 Slave_IO_Running: No Slave_SQL_Running: Yes . . Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log; the first event 'master-bin.000006' at 120, the last event read from './master-bin.000007' at 120, the last byte read from './master-bin.000007' at 120.' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec)

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.

mysql> SET GLOBAL binlog_checksum = NONE;

1	mysql> SET GLOBAL binlog_checksum = NONE;

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.

Conclusion:

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.