Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.6Mb
PDF (A4) - 31.6Mb
PDF (RPM) - 30.5Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.5Mb
Man Pages (TGZ) - 189.5Kb
Man Pages (Zip) - 304.2Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

2.11.2 Downgrading MySQL

This section describes how to downgrade to an older MySQL version.

Note

In the following discussion, MySQL commands that must be run using a MySQL account with administrative privileges include -u root on the command line to specify the MySQL root user. Commands that require a password for root also include a -p option. Because -p is followed by no option value, such commands prompt for the password. Type the password when prompted and press Enter.

SQL statements can be executed using the mysql command-line client (connect as root to ensure that you have the necessary privileges).

Downgrade Methods

Supported downgrade methods include:

  • In-Place Downgrade: Involves shutting down the new MySQL version, replacing the new MySQL binaries or packages with the old ones, and restarting the old MySQL version on the new data files. In-place downgrades are supported for downgrades between GA versions within the same release series. For example, in-place downgrades are supported for downgrades from 5.6.27 to 5.6.26.

  • Logical Downgrade: Involves using mysqldump to dump all tables from the new MySQL version, and then loading the dump file into the old MySQL version. Logical downgrades are supported for downgrades between GA versions within the same release series and for downgrades between release levels. For example, logical downgrades are supported for downgrades from 5.6.27 to 5.6.26 and for downgrades from 5.6 to 5.5.

Note

In-place downgrades are not supported for MySQL APT, SLES, and Yum repository installations.

For procedures, see In-Place Downgrade, and Logical Downgrade.

Downgrade Paths

  • Downgrade is only supported between General Availability (GA) releases.

  • Downgrade from MySQL 5.6 to 5.5 is supported using the logical downgrade method.

  • Downgrade that skips versions is not supported. For example, downgrading directly from MySQL 5.6 to 5.1 is not supported.

  • Downgrade within a release series is supported. For example, downgrading from MySQL 5.6.z to 5.6.y is supported. Skipping a release is also supported. For example, downgrading from MySQL 5.6.z to 5.6.x is supported.

Before You Begin

Before downgrading, the following steps are recommended:

  • Review the Release Notes for the MySQL version you are downgrading from to ensure that there are no features or fixes that you really need.

  • Review Section 2.11.2.1, “Changes Affecting Downgrades from MySQL 5.6”. This section describes changes that may require action before or after downgrading.

    Note

    The downgrade procedures described in the following sections assume you are downgrading with data files created or modified by the newer MySQL version. However, if you did not modify your data after upgrading, downgrading using backups taken before upgrading to the new MySQL version is recommended. Many of the changes described in Section 2.11.2.1, “Changes Affecting Downgrades from MySQL 5.6” that require action before or after downgrading are not applicable when downgrading using backups taken before upgrading to the new MySQL version.

  • Always back up your current databases and log files before downgrading. The backup should include the mysql database, which contains the MySQL system tables. See Section 7.2, “Database Backup Methods”.

  • Use of new features, new configuration options, or new configuration option values that are not supported by a previous release may cause downgrade errors or failures. Before downgrading, it is recommended that you reverse changes resulting from the use of new features and remove configuration settings that are not supported by the release you are downgrading to.

  • If you use XA transactions with InnoDB, run XA RECOVER before downgrading to check for uncommitted XA transactions. If results are returned, either commit or rollback the XA transactions by issuing an XA COMMIT or XA ROLLBACK statement.

In-Place Downgrade

In-place downgrades are supported for downgrades between GA releases within the same release series. Before proceeding, review Before You Begin.

To perform an in-place downgrade:

  1. Review the changes described in Section 2.11.2.1, “Changes Affecting Downgrades from MySQL 5.6” for steps to be performed before downgrading.

  2. If you use InnoDB, configure MySQL to perform a slow shutdown by setting innodb_fast_shutdown to 0. For example:

    mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"

    With a slow shutdown, InnoDB performs a full purge and change buffer merge before shutting down, which ensures that data files are fully prepared in case of file format differences between releases.

  3. Shut down the newer MySQL server. For example:

    mysqladmin -u root -p shutdown
  4. After the slow shutdown, 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.

    rm ib_logfile*
  5. Downgrade the MySQL binaries or packages in-place by replacing the newer binaries or packages with the older ones.

  6. Start the older (downgraded) MySQL server, using the existing data directory. For example:

    mysqld_safe --user=mysql --datadir=/path/to/existing-datadir
  7. Run mysql_upgrade. For example:

    mysql_upgrade -u root -p
  8. Shut down and restart the MySQL server to ensure that any changes made to the system tables take effect. For example:

    mysqladmin -u root -p shutdown
    mysqld_safe --user=mysql --datadir=/path/to/existing-datadir

Logical Downgrade

Logical downgrades are supported for downgrades between releases within the same release series and for downgrades to the previous release level. Only downgrades between General Availability (GA) releases are supported. Before proceeding, review Before You Begin.

Note

For MySQL APT, SLES, and Yum repository installations, only downgrades to the previous release level are supported. Where the instructions call for initializing an older instance, use the package management utility to remove MySQL 5.6 packages and install MySQL 5.5 packages.

To perform a logical downgrade:

  1. Review the changes described in Section 2.11.2.1, “Changes Affecting Downgrades from MySQL 5.6” for steps to be performed before downgrading.

  2. Dump all databases. For example:

    mysqldump -u root -p
      --add-drop-table --routines --events
      --all-databases --force > data-for-downgrade.sql
  3. Shut down the newer MySQL server. For example:

    mysqladmin -u root -p shutdown
  4. Initialize an older MySQL instance, with a new data directory. For example:

    scripts/mysql_install_db --user=mysql
  5. Start the older MySQL server, using the new data directory. For example:

    mysqld_safe --user=mysql --datadir=/path/to/new-datadir
  6. Load the dump file into the older MySQL server. For example:

    mysql -u root -p --force < data-for-upgrade.sql
  7. Run mysql_upgrade. For example:

    mysql_upgrade -u root -p
  8. Shut down and restart the MySQL server to ensure that any changes made to the system tables take effect. For example:

    mysqladmin -u root -p shutdown
    mysqld_safe --user=mysql --datadir=/path/to/new-datadir

Downgrade Troubleshooting

If you downgrade from one release series to another, there may be incompatibilities in table storage formats. In this case, use mysqldump to dump your tables before downgrading. After downgrading, reload the dump file using mysql or mysqlimport to re-create your tables. For examples, see Section 2.11.4, “Copying MySQL Databases to Another Machine”.

A typical symptom of a downward-incompatible table format change when you downgrade is that you cannot open tables. In that case, use the following procedure:

  1. Stop the older MySQL server that you are downgrading to.

  2. Restart the newer MySQL server you are downgrading from.

  3. Dump any tables that were inaccessible to the older server by using mysqldump to create a dump file.

  4. Stop the newer MySQL server and restart the older one.

  5. Reload the dump file into the older server. Your tables should be accessible.


User Comments
Sign Up Login You must be logged in to post a comment.