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
--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.10.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
CHECK TABLE or
For dump and reload instructions, see
Section 2.10.4, “Rebuilding or Repairing Tables or Indexes”. Any procedure that
REPAIR TABLE with
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 126.96.36.199, “
REPAIR TABLE Syntax”.
Before upgrading to a new version of MySQL, Section 2.10.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.10.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.)
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 you use
InnoDB, consider setting
innodb_fast_shutdown to 0
before shutting down and upgrading your server. When you set
innodb_fast_shutdown to 0,
InnoDB does a slow shutdown, a full purge
and an insert buffer merge before shutting down, which
ensures that all data files are fully prepared in case the
upgrade process modifies the file format.
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:
Incompatible change: As of
MySQL 5.7.4, the deprecated
NO_ZERO_IN_DATE SQL modes
do nothing. Instead, their previous effects are included in
the effects of strict SQL mode
other words, strict mode now means the same thing as the
previous meaning of strict mode plus the
This change reduces the number of SQL modes with an effect
dependent on strict mode and makes them part of strict mode
To prepare for these SQL mode changes, it is advisable before upgrading to read SQL Mode Changes in MySQL 5.7. That discussion provides guidelines to assess whether your applications will be affected by these changes.
NO_ZERO_IN_DATE SQL modes
are still recognized so that statements that name them do
not produce an error, but will be removed in a future
version of MySQL. To make advance preparation for versions
of MySQL in which these modes do not exist, applications
should be modified to not refer to those mode names.
Incompatible change: As of
MySQL 5.7.2, the server requires account rows in the
mysql.user table to have a nonempty
plugin column value and disables accounts
with an empty value. This requires that you upgrade as
For an upgrade in which you plan to use the data directory from your existing MySQL installation:
For an upgrade in which you plan to reload a dump file generated from your existing MySQL installation:
Stop the server
Upgrade MySQL in place
Restart the server with the
option to disable privilege checking
Reload the dump file (mysql <
Restart the server normally (without
mysql_upgrade runs by default as the
root user. For either of the
preceding procedures, if the
password is expired when you run
mysql_upgrade, you will see a message
that your password is expired and that
mysql_upgrade failed as a result. To
correct this, reset the
root password to
unexpire it and run mysql_upgrade again:
mysql -u root -pEnter password:
****<- enter root password here mysql>
SET PASSWORD = PASSWORD('root-password');mysql>
SET PASSWORD normally does
not work if the server is started with
--skip-grant-tables, but the
first invocation of mysql_upgrade flushes
the privileges, so when you run mysql,
SET PASSWORD statement is
After following the preceding instructions, DBAs are advised
to also convert accounts that use the deprecated
mysql_old_password authentication plugin
mysql_native_password instead. For
account upgrade instructions, see
Section 188.8.131.52, “Migrating Away from Pre-4.1 Password Hashing and the
SET sql_mode = ''; CREATE TABLE t (d DATE DEFAULT 0); SET sql_mode = 'NO_ZERO_DATE,STRICT_ALL_TABLES'; INSERT INTO t (d) VALUES(DEFAULT);
In this case, 0 should be accepted for the
CREATE TABLE but rejected for
INSERT. However, the
server did not evaluate
used for inserts or updates against the current
sql_mode. In the example, the
INSERT succeeds and inserts
'0000-00-00' into the
As of MySQL 5.7.2, the server applies the proper
sql_mode checks to generate
a warning or error at insert or update time.
A resulting incompatibility for replication if you use
is that if a slave is upgraded, a nonupgraded master will
execute the preceding example without error, whereas the
INSERT will fail on the slave
and replication will stop.
To deal with this, stop all new statements on the master and
wait until the slaves catch up. Then upgrade the slaves
followed by the master. Alternatively, if you cannot stop
new statements, temporarily change to row-based logging on
wait until all slaves have processed all binary logs
produced up to the point of this change. Then upgrade the
slaves followed by the master and change the master back to
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 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:
Stop the server.
Rename the current audit log file manually. This file will contain only old-format log entries.
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.13, “MySQL Enterprise Audit Log Plugin”.
A trigger can have triggers for different combinations of
trigger event (
DELETE) and action time
before MySQL 5.7.2 cannot have multiple triggers that have
the same trigger event and action time. MySQL 5.7.2 lifts
this limitation and multiple triggers are permitted. This
change has implications for upgrades.
Suppose that you upgrade an old server that does not support multiple triggers to MySQL 5.7.2 or newer. If the new server is a replication master and has old slaves that do not support multiple triggers, an error occurs on those slaves if a trigger is created on the master for a table that already has a trigger with the same trigger event and action time. To avoid this problem, upgrade the slaves first, then upgrade the master.
Some keywords may be reserved in MySQL 5.7 that were not reserved in MySQL 5.6. See Section 9.3, “Reserved Words”.
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices