Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.6Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 34.6Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 202.2Kb
Man Pages (Zip) - 307.6Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

2.11.1 Upgrading MySQL

This section describes how to upgrade to a new 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).

Supported Upgrade Methods

Supported upgrade methods include:

  • In-Place Upgrade: Involves shutting down the old MySQL version, replacing the old MySQL binaries or packages with the new ones, restarting MySQL on the existing data directory, and running mysql_upgrade.

  • Logical Upgrade: Involves exporting existing data from the old MySQL version using mysqldump, installing the new MySQL version, loading the dump file into the new MySQL version, and running mysql_upgrade.

For in-place and logical upgrade procedures, see Performing an In-Place Upgrade, and Performing a Logical Upgrade.

If you run MySQL Server on Windows, refer to the upgrade procedure described in Section 2.3.8, “Upgrading MySQL on Windows”.

If your current MySQL installation was installed on an Enterprise Linux platform or Fedora using the MySQL Yum Repository, see Section 2.11.1.2, “Upgrading MySQL with the MySQL Yum Repository”.

If your current MySQL installation was installed on Ubuntu using the MySQL APT repository, see Section 2.11.1.3, “Upgrading MySQL with the MySQL APT Repository”.

Supported Upgrade Paths

Unless otherwise documented, the following upgrade paths are supported:

  • Upgrading from a release series version to a newer release series version is supported. For example, upgrading from 5.7.9 to 5.7.10 is supported. Skipping release series versions is also supported. For example, upgrading from 5.7.9 to 5.7.11 is supported.

  • Upgrading one release level is supported. For example, upgrading from 5.6 to 5.7 is supported. Upgrading to the latest release series version is recommended before upgrading to the next release level. For example, upgrade to the latest 5.6 release before upgrading to 5.7.

  • Upgrading more than one release level is supported, but only if you upgrade one release level at a time. For example, if you currently are running MySQL 5.5 and wish to upgrade to a newer series, upgrade to MySQL 5.6 first before upgrading to MySQL 5.7, and so forth. For information on upgrading to MySQL 5.6 see the MySQL 5.6 Reference Manual.

  • Direct upgrades that skip a release level (for example, upgrading directly from MySQL 5.5 to 5.7) are not recommended or supported.

The following conditions apply to all upgrade paths:

  • Upgrades between General Availability (GA) status releases are supported.

  • Upgrades between milestone releases (or from a milestone release to a GA release) are not supported. For example, upgrading from 5.7.7 to 5.7.8 is not supported, as neither are GA status releases.

  • For upgrades between versions of a MySQL release series that has reached GA status, you can move the MySQL format files and data files between different versions on systems with the same architecture. This is not necessarily true for upgrades between milestone releases. Use of milestone releases is at your own risk.

Before You Begin

Before upgrading, review the following information and perform the recommended steps:

  • Before upgrading, protect your data by creating a backup of your current databases and log files. The backup should include the mysql system database, which contains the MySQL system tables. See Section 8.2, “Database Backup Methods”.

  • Review the Release Notes which provide information about features that are new in the MySQL 5.7 or differ from those found in earlier MySQL releases. Some of these changes may result in incompatibilities.

    For listings of MySQL server variables and options that have been added, deprecated, or removed in MySQL 5.7, see Section 1.5, “Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 5.7”. If you use any of these items, an upgrade requires configuration changes.

  • Review Section 2.11.1.1, “Changes Affecting Upgrades to MySQL 5.7”. This section describes changes that may require action before or after upgrading.

  • Check Section 2.11.3, “Checking Whether Tables or Indexes Must Be Rebuilt”, to see whether changes to table formats or to character sets or collations were made between your current version of MySQL and the version to which you are upgrading. If such changes have resulted in an incompatibility between MySQL versions, you will need to upgrade the affected tables using the instructions in Section 2.11.4, “Rebuilding or Repairing Tables or Indexes”.

  • If you use replication, review Section 18.4.3, “Upgrading a Replication Setup”.

  • If you use XA transactions with InnoDB, run XA RECOVER before upgrading 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.

  • If your MySQL installation contains a large amount of data that might take a long time to convert after an in-place upgrade, you might find it useful to create a dummy database instance for assessing what conversions might be needed and the work involved to perform them. Make a copy of your MySQL instance that contains a full copy of the mysql database, plus all other databases without data. Run your upgrade procedure on this dummy instance to see what actions might be needed so that you can better evaluate the work involved when performing actual data conversion on your original database instance.

  • Rebuilding and reinstalling MySQL language interaces is recommended whenever you install or upgrade to a new release of MySQL. This applies to MySQL interfaces such as PHP mysql extensions, the Perl DBD::mysql module, and the Python MySQLdb module.

Performing an In-Place Upgrade

This section describes how to perform an in-place upgrade. Review Before you Begin before proceeding.

Note

If you upgrade an installation originally produced by installing multiple RPM packages, upgrade all the packages, not just some. For example, if you previously installed the server and client RPMs, do not upgrade just the server RPM.

To perform an in-place upgrade:

  1. Review the changes described in Section 2.11.1.1, “Changes Affecting Upgrades to MySQL 5.7” for steps to be performed before upgrading.

  2. 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 old MySQL server. For example:

    mysqladmin -u root -p shutdown
    
  4. Upgrade the MySQL binaries or packages in place (replace the old binaries or packages with the new ones).

    Note

    For supported Linux distributions, the preferred method for replacing the MySQL packages is to use the MySQL software repositories; see Section 2.11.1.2, “Upgrading MySQL with the MySQL Yum Repository”, Section 2.11.1.3, “Upgrading MySQL with the MySQL APT Repository”, or Upgrading MySQL with the MySQL SLES Repository for instructions.

  5. Start the MySQL 5.7 server, using the existing data directory. For example:

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

    mysql_upgrade -u root -p
    

    mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL. mysql_upgrade also upgrades the mysql system database so that you can take advantage of new privileges or capabilities.

    Note

    mysql_upgrade should not be used when the server is running with --gtid-mode=ON. See GTID mode and mysql_upgrade for more information.

    mysql_upgrade does not upgrade the contents of the help tables. For upgrade instructions, see Section 6.1.10, “Server-Side Help”.

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

Performing a Logical Upgrade

This section describes how to perform a logical upgrade. Review Before you Begin before proceeding.

To perform a logical upgrade:

  1. Review the changes described in Section 2.11.1.1, “Changes Affecting Upgrades to MySQL 5.7” for steps to be performed before upgrading.

  2. Export your existing data from the previous MySQL version:

    mysqldump -u root -p
      --add-drop-table --routines --events
      --all-databases --force > data-for-upgrade.sql
    
    Note

    Use the --routines and --events options with mysqldump (as shown above) if your databases include stored programs. The --all-databases option includes all databases in the dump, including the mysql database that holds the system tables.

    Important

    If you have tables that contain generated columns, use the mysqldump utility provided with MySQL 5.7.9 or higher to create your dump files. The mysqldump utility provided in earlier releases uses incorrect syntax for generated column definitions (Bug #20769542). You can use the INFORMATION_SCHEMA.COLUMNS table to identify tables with generated columns.

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

    mysqladmin -u root -p shutdown
    
  4. Install MySQL 5.7. For installation instructions, see Chapter 2, Installing and Upgrading MySQL.

  5. Initialize a new data directory, as described at Section 2.10.1, “Initializing the Data Directory”. For example:

    mysqld --initialize --datadir=/path/to/5.7-datadir
    

    Copy the temporary 'root'@'localhost' password displayed to your screen or written to your error log for later use.

  6. Start the MySQL 5.7 server, using the new data directory. For example:

    mysqld_safe --user=mysql --datadir=/path/to/5.7-datadir
    
  7. Reset the root password:

    shell> mysql -u root -p
    Enter password: ****  <- enter temporary root password
    mysql> ALTER USER USER() IDENTIFIED BY 'your new password';
    
  8. Load the previously created dump file into the new MySQL server. For example:

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

    mysql_upgrade -u root -p
    

    mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL. mysql_upgrade also upgrades the mysql system database so that you can take advantage of new privileges or capabilities.

    Note

    mysql_upgrade should not be used when the server is running with --gtid-mode=ON. See GTID mode and mysql_upgrade for more information.

    mysql_upgrade does not upgrade the contents of the help tables. For upgrade instructions, see Section 6.1.10, “Server-Side Help”.

  10. 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/5.7-datadir
    

Upgrade Troubleshooting

  • If problems occur, such as that the new mysqld server does not start, verify that you do not have an old my.cnf file from your previous installation. You can check this with the --print-defaults option (for example, mysqld --print-defaults). If this command displays anything other than the program name, you have an active my.cnf file that affects server or client operation.

  • If, after an upgrade, you experience problems with compiled client programs, such as Commands out of sync or unexpected core dumps, you probably have used old header or library files when compiling your programs. In this case, check the date for your mysql.h file and libmysqlclient.a library to verify that they are from the new MySQL distribution. If not, recompile your programs with the new headers and libraries. Recompilation might also be necessary for programs compiled against the shared client library if the library major version number has changed (for example, from libmysqlclient.so.15 to libmysqlclient.so.16).

  • If you have created a user-defined function (UDF) with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF, and then use CREATE FUNCTION to re-create the UDF with a different nonconflicting name. The same is true if the new version of MySQL implements a built-in function with the same name as an existing stored function. See Section 10.2.4, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of functions.


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