Downgrading from MySQL 8.0

If your application is not performing as expected, you can always downgrade to a previous GA release (MySQL 5.7). Before downgrading, it is recommended to take a logical backup (refer to Chapter 7, Backups). Note that you can downgrade by only one previous release. Suppose that you want to downgrade from MySQL 8.0 to MySQL 5.6, you have to downgrade to MySQL 5.7, and then from MySQL 5.7 to MySQL 5.6.

You can do it in two ways:

• In-place downgrade (downgrades within MySQL 8)
• Logical downgrade

How to do it...

In the following subsections, you will be learning how to handle the installation/uninstallation/upgrade/downgrade using various repositories, bundles, and so on.

In-place Downgrades

For downgrades between the GA status releases within MySQL 8.0 (note that you cannot downgrade to MySQL 5.7 using this method):

1. Shut down the old MySQL version
2. Replace the MySQL 8.0 binaries or older binaries
3. Restart MySQL on the existing data directory
4. Run the mysql_upgrade utility

Using YUM repositories

1. Prepare MySQL for a slow shutdown, which ensures that the undo logs are empty and data files are fully prepared in case of file format differences between releases:

mysql> SET GLOBAL innodb_fast_shutdown = 0;

2. Shut down the mysql server as described in the Stopping MySQL 8.0 Server section:

shell> sudo systemctl stop mysqld

3. Remove the InnoDB redo log files (the ib_logfile* files) from the data directory to avoid downgrade issues related to redo log file format changes that may have occurred between releases:

shell> sudo rm -rf /var/lib/mysql/ib_logfile*

4. Downgrade MySQL. To downgrade the server, you need to uninstall MySQL 8.0, as described in the Uninstalling MySQL 8 section. The configuration files are automatically stored as backup.
   
   List the available versions:

shell> sudo yum list mysql-community-server

Downgrades are tricky; it is better to remove the existing packages before downgrading:

shell> sudo rpm -qa | grep -i mysql-community | xargs sudo rpm -e --nodeps
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave

Install the older version:

shell> sudo yum install -y mysql-community-server-<version>

Using APT Repositories

1. Reconfigure MySQL and choose the older version:

shell> sudo dpkg-reconfigure mysql-apt-config

2. Run apt-get update:

shell> sudo apt-get update

3. Remove the current version:

shell> sudo apt-get remove mysql-community-server mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-server mysql-community-server-core -y

shell> sudo apt-get autoremove

4. Install the older version (autoselected since you have reconfigured):

shell> sudo apt-get install -y mysql-server

Using the RPM or APT bundle

Uninstall the existing packages (refer to the Uninstalling MySQL 8 section) and install the new packages, which can be downloaded from the MySQL Downloads (refer to the Installing MySQL 8.0 using RPMs or DEB files section).

Using Generic Binaries

If you have installed MySQL through binaries, you have to remove the symlink to the old version (refer to the Uninstalling MySQL 8 section) and do a fresh installation (refer to the Installing MySQL on Linux Using Generic Binaries section):

1. Start the server as described in the Starting or Stopping MySQL 8 section. Please note that the start procedure is the same for all the versions.
2. Run the mysql_upgrade utility:

shell> sudo mysql_upgrade -u root -p

3. Restart the MySQL server to ensure that any changes made to the system tables take effect:

shell> sudo systemctl restart mysqld

Logical Downgrades

Here is an outline of the steps:

1. Export existing data from the MySQL 8.0 version using logical backup (refer to Chapter 7, Backups for logical backup methods)
2. Install MySQL 5.7
3. Load the dump file into the MySQL 5.7 version (refer to Chapter 8, Restoring Data for restoring methods)
4. Run the mysql_upgrade utility

Here are the detailed steps:

1. You need to take logical backup of the database. (refer to Chapter 7, Backups for a quicker backup called mydumper):

shell> mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > mysql80.sql

2. Shut down the MySQL server as described in the Starting or Stopping MySQL 8 section.
3. Move the data directory. Instead of restoring the SQL backup (in step 1), we can move back the data directory if you want to keep MySQL 8:

shell> sudo mv /var/lib/mysql /var/lib/mysql80

4. Downgrade MySQL. To downgrade the server, we need to uninstall MySQL 8. The configuration files are automatically backed up.

Using YUM Repositories

After the uninstallation, install the older version:

1. Switch the repositories:

shell> sudo yum-config-manager --disable mysql80-community
shell> sudo yum-config-manager --enable mysql57-community

2. Verify that mysql57-community is enabled:

shell> yum repolist enabled | grep "mysql.*-community.*"
!mysql-connectors-community/x86_64 MySQL Connectors Community                 42
!mysql-tools-community/x86_64      MySQL Tools Community                      53
!mysql57-community/x86_64          MySQL 5.7 Community Server                227

3. Downgrades are tricky; it is better to remove the existing packages before downgrading:

shell> sudo rpm -qa | grep -i mysql-community | xargs sudo rpm -e --nodeps
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave

4. List the available versions:

shell> sudo yum list mysql-community-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.rackspace.com
 * epel: mirrors.develooper.com
 * extras: centos.s.uw.edu
 * updates: mirrors.syringanetworks.net
Available Packages
mysql-community-server.x86_64   5.7.20-1.el7                         mysql57-community

5. Install MySQL 5.7:

shell> sudo yum install -y mysql-community-server

Using APT Repositories

1. Reconfigure apt to switch to MySQL 5.7:

shell> sudo dpkg-reconfigure mysql-apt-config

2. Run apt-get update:

shell> sudo apt-get update

3. Remove the current version:

shell> sudo apt-get remove mysql-community-server mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-server mysql-community-server-core -y
shell> sudo apt-get autoremove

4. Install MySQL 5.7:

shell> sudo apt-get install -y mysql-server

Using RPM or APT bundles

Uninstall the existing packages (refer to the Uninstalling MySQL 8 section) and install the new packages, which can be downloaded from MySQL Downloads (refer to the Installing MySQL 8 using RPM or DEB files section).

Using Generic Binaries

If you have installed MySQL through binaries, you have to remove the symlink to the old version (refer to the Uninstalling MySQL 8 section) and do a fresh installation (refer to the Installing MySQL on Linux using Generic Binaries section).

Once you have downgraded MySQL, you have to restore the backup and run the mysql_upgrade utility:

1. Start MySQL (refer to the Starting or Stopping MySQL 8 section). You need to reset the password again.
2. Restore the backup (this may take a long time, depending up on the size of backup). Refer to Chapter 8, Restoring Data, for a quick restoration method called myloader:

shell> mysql -u root -p < mysql80.sql

3. Run mysql_upgrade:

shell> mysql_upgrade -u root -p

4. Restart the MySQL server to ensure that any changes made to the system tables take effect. Refer to the Starting or Stopping MySQL 8 section:

shell> sudo /etc/init.d/mysql restart