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.
Changes marked as either Known
issue or Incompatible
change are incompatibilities with earlier
versions of MySQL, and 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
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 18.104.22.168, “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 17.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: In MySQL 5.7.5, these SQL mode changes were made:
Strict SQL mode for transactional storage engines
is now enabled by default.
Implementation of the
mode has been made more sophisticated, to no longer
reject deterministic queries that previously were
rejected. In consequence,
now enabled by default, to prohibit nondeterministic
queries containing expressions not guaranteed to be
uniquely determined within a group.
If you find that having
causes queries for existing applications to be rejected,
either of these actions should restore operation:
If it is possible to modify an offending query, do so,
either so that nondeterministic nonaggregated columns
are functionally dependent on
BY columns, or by referring to nonaggregated
If it is not possible to modify an offending query (for
example, if it is generated by a third-party
application), set the
variable at server startup to not enable
Incompatible change: As of MySQL 5.7.5, support for passwords that use the older pre-4.1 password hashing format is removed, which involves the following changes. Applications that use any feature no longer supported must be modified.
plugin is removed. Accounts that use this plugin are
disabled at startup and the server writes an
“unknown plugin” message to the error log.
For instructions on upgrading accounts that use this
plugin, see Section 22.214.171.124, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password
--secure-auth option to the server
and client programs is the default, but is now a no-op.
It is deprecated and will be removed in a future MySQL
--skip-secure-auth option to the
server and client programs is no longer supported and
using it produces an error.
variable permits only a value of 1; a value of 0 is no
system variable, a value of 1 (produce pre-4.1 hashes)
is no longer permitted.
function is removed.
Incompatible change: In
MySQL 5.6.6, the
type was deprecated. In MySQL 5.7.5, support for
YEAR(2) is removed. Once you
upgrade to MySQL 5.7.5 or newer, any remaining
YEAR(2) columns must be
become usable again. For conversion strategies, see
Section 11.3.4, “YEAR(2) Limitations and Migrating to YEAR(4)”. For example, run
mysql_upgrade after upgrading.
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
also to convert accounts that use the
mysql_old_password authentication plugin
because support for
has been removed. For account upgrade instructions, see
Section 126.96.36.199, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password
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
Incompatible change: To
InnoDB tablespace discovery
during crash recovery, new redo log records types are
introduced in MySQL 5.7.5. This enhancement changes the redo
log format, requiring that MySQL be shut down cleanly before
upgrading to MySQL 5.7.5 or later. For related information,
see Section 14.16.2, “Tablespace Discovery During Crash Recovery”.
As of MySQL 5.7.5, the
field, written to the first page of each
InnoDB system tablespace file and to
InnoDB undo tablespace files, is only
written to the first file of the
system tablespace (page number 0:0). As a result of this
patch, if you have a multiple-file system tablespace and
decide to downgrade from MySQL 5.7 to MySQL 5.6, you may
encounter an invalid message on MySQL 5.6 startup stating
that the log sequence numbers
y in ibdata files do not match
the log sequence number
y in the
ib_logfiles. If you encounter this message,
restart MySQL 5.6 to ensure that startup has run properly.
The invalid message should no longer appear.
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”.