2.11.1.1. Upgrading from MySQL 5.6 to 5.7

Note

It is good practice to back up your data before installing any new version of software. Although MySQL works very hard to ensure a high level of quality, protect your data by making a backup.

To upgrade to 5.7 from any previous version, MySQL recommends that you dump your tables with mysqldump before upgrading and reload the dump file after upgrading. Use the --all-databases option to include all databases in the dump. If your databases include stored programs, use the --routines and --events options as well.

In general, do the following when upgrading from MySQL 5.6 to 5.7:

Read all the items in these sections to see whether any of them might affect your applications:
- Section 2.11.1, "Upgrading MySQL", has general update information.
- The items in the change lists provided later in this section enable you to identify upgrade issues that apply to your current MySQL installation. Some incompatibilities discussed there require your attention before upgrading. Others should be dealt with after upgrading.
- The MySQL 5.7 Release Notes describe significant new features you can use in 5.7 or that differ from those found in earlier MySQL releases. Some of these changes may result in incompatibilities.
Note particularly any changes that are marked Known issue or Incompatible change. These incompatibilities with earlier versions of MySQL may require your attention before you upgrade. Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases. If any upgrade issue applicable to your installation involves an incompatibility that requires special handling, follow the instructions given in the incompatibility description. Sometimes this involves dumping and reloading tables, or use of a statement such as CHECK TABLE or REPAIR TABLE.

For dump and reload instructions, see Section 2.11.4, "Rebuilding or Repairing Tables or Indexes". Any procedure that involves REPAIR TABLE with the USE_FRM option must be done before upgrading. Use of this statement with a version of MySQL different from the one used to create the table (that is, using it after upgrading) may damage the table. See Section 13.7.2.5, "REPAIR TABLE Syntax".
Before upgrading to a new version of MySQL, 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 so and these changes result 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".
After upgrading to a new version of MySQL, run mysql_upgrade (see Section 4.4.7, "mysql_upgrade — Check and Upgrade MySQL Tables"). This program checks your tables, and attempts to repair them if necessary. It also updates your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. (Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features.)

mysql_upgrade does not upgrade the contents of the help tables. For upgrade instructions, see Section 5.1.10, "Server-Side Help".
If you run MySQL Server on Windows, see Section 2.3.7, "Upgrading MySQL on Windows".
If you use replication, see Section 16.4.3, "Upgrading a Replication Setup", for information on upgrading your replication setup.

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.

Read all the items in the following sections to see whether any of them might affect your applications:

Server Changes

Incompatible change: Several changes were made to the audit log plugin for better compatibility with Oracle Audit Vault. For upgrading purpose, the main issue is that the format of the audit log file has changed: Information within <AUDIT_RECORD> elements previously written using attributes now is written using subelements.

Example of old <AUDIT_RECORD> format:
```
<AUDIT_RECORD TIMESTAMP="2013-04-15T15:27:27" NAME="Query" CONNECTION_ID="3" STATUS="0" SQLTEXT="SELECT 1"/>
```
Example of new format:
```
<AUDIT_RECORD> <TIMESTAMP>2013-04-15T15:27:27 UTC</TIMESTAMP> <RECORD_ID>3998_2013-04-15T15:27:27</RECORD_ID> <NAME>Query</NAME> <CONNECTION_ID>3</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER>root[root] @ localhost [127.0.0.1]</USER> <OS_LOGIN></OS_LOGIN> <HOST>localhost</HOST> <IP>127.0.0.1</IP> <COMMAND_CLASS>select</COMMAND_CLASS> <SQLTEXT>SELECT 1</SQLTEXT></AUDIT_RECORD>
```
If you previously used an older version of the audit log plugin, use this procedure to avoid writing new-format log entries to an existing log file that contains old-format entries:
1. Stop the server.
2. Rename the current audit log file manually. This file will contain only old-format log entries.
3. Update the server and restart it. The audit log plugin will create a new log file, which will contain only new-format log entries.
For information about the audit log plugin, see Section 6.3.11, "MySQL Enterprise Audit Log Plugin".

SQL Changes

Some keywords may be reserved in MySQL 5.7 that were not reserved in MySQL 5.6. See Section 9.3, "Reserved Words".