Over a million developers have joined DZone.

MySQL Downgrade Caveats

''Well, that didn't go like we thought it would.'' If your MySQL upgrade didn't go as planned, don't worry. This post will teach you how to walk it back so you can start fresh.

· Database Zone

Sign up for the Couchbase Community Newsletter to stay ahead of the curve on the latest NoSQL news, events, and webinars. Brought to you in partnership with Coucbase.

mysql downgrade

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:

  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: A 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.

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('TIME','DATETIME','TIMESTAMP')
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
*************************** 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
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
[root@slave_5_6 ~]# mysqldump --single-transaction --routines --databases db1 db2 > databases.sql


And to restore on the Percona Server 5.5 slave:

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


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.

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.

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
percona server ,mysql ,database ,downgrade

Published at DZone with permission of Muhammad Irfan, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}