MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
When you upgrade servers that participate in a replication setup, the procedure for upgrading depends on the current server versions and the version to which you are upgrading. This section provides information about how upgrading affects replication. For general information about upgrading MySQL, see Section 2.11, “Upgrading MySQL”
When you upgrade a source to 5.7 from an earlier MySQL release series, you should first ensure that all the replicas of this source are using the same 5.7.x release. If this is not the case, you should first upgrade the replicas. To upgrade each replica, shut it down, upgrade it to the appropriate 5.7.x version, restart it, and restart replication. Relay logs created by the replica after the upgrade are in 5.7 format.
Changes affecting operations in strict SQL mode
(STRICT_TRANS_TABLES
or
STRICT_ALL_TABLES
) may result in
replication failure on an updated replica. For example, as of
MySQL 5.7.2, the server restricts insertion of a
DEFAULT
value of 0 for temporal data types in
strict mode. If you use statement-based logging
(binlog_format=STATEMENT
), if a
replica is upgraded before the source, the source that is not
upgraded executes statements without error that may fail on the
replica and replication stops. To deal with this, stop all new
statements on the source and wait until the replicas catch up.
Then upgrade the replicas. Alternatively, if you cannot stop new
statements, temporarily change to row-based logging on the source
(binlog_format=ROW
) and wait
until all replicas have processed all binary logs produced up to
the point of this change. Then upgrade the replicas.
After the replicas have been upgraded, shut down the source, upgrade it to the same 5.7.x release as the replicas, and restart it. If you had temporarily changed the source to row-based logging, change it back to statement-based logging. The 5.7 source is able to read the old binary logs written prior to the upgrade and to send them to the 5.7 replicas. The replicas recognize the old format and handle it properly. Binary logs created by the source subsequent to the upgrade are in 5.7 format. These too are recognized by the 5.7 replicas.
In other words, when upgrading to MySQL 5.7, the replicas must be MySQL 5.7 before you can upgrade the source to 5.7. Note that downgrading from 5.7 to older versions does not work so simply: You must ensure that any 5.7 binary log or relay log has been fully processed, so that you can remove it before proceeding with the downgrade.
Some upgrades may require that you drop and re-create database objects when you move from one MySQL series to the next. For example, collation changes might require that table indexes be rebuilt. Such operations, if necessary, are detailed at Section 2.11.3, “Changes in MySQL 5.7”. It is safest to perform these operations separately on the replicas and the source, and to disable replication of these operations from the source to the replica. To achieve this, use the following procedure:
Stop all the replicas and upgrade them. Restart them with the
--skip-slave-start
option so
that they do not connect to the source. Perform any table
repair or rebuilding operations needed to re-create database
objects, such as use of REPAIR TABLE
or
ALTER TABLE
, or dumping and reloading
tables or triggers.
Disable the binary log on the source. To do this without
restarting the source, execute a SET sql_log_bin =
OFF
statement. Alternatively, stop the source and
restart it without the
--log-bin
option. If you
restart the source, you might also want to disallow client
connections. For example, if all clients connect using TCP/IP,
enable the skip_networking
system variable when you restart the source.
With the binary log disabled, perform any table repair or rebuilding operations needed to re-create database objects. The binary log must be disabled during this step to prevent these operations from being logged and sent to the replicas later.
Re-enable the binary log on the source. If you set
sql_log_bin
to
OFF
earlier, execute a SET
sql_log_bin = ON
statement. If you restarted the
source to disable the binary log, restart it with
--log-bin
, and without enabling
the skip_networking
system
variable so that clients and replicas can connect.
Restart the replicas, this time without the
--skip-slave-start
option.
If you are upgrading an existing replication setup from a version
of MySQL that does not support global transaction identifiers to a
version that does, you should not enable GTIDs on either the
source or the replica before making sure that the setup meets all
the requirements for GTID-based replication. For example
server_uuid
, which was added in
MySQL 5.6, must exist for GTIDs to function correctly. See
Section 16.1.3.4, “Setting Up Replication Using GTIDs”, which contains
information about converting existing replication setups to use
GTID-based replication.
When the server is running with global transaction identifiers
(GTIDs) enabled (gtid_mode=ON
),
do not enable binary logging by mysql_upgrade.
It is not recommended to load a dump file when GTIDs are enabled
on the server (gtid_mode=ON
), if
your dump file includes system tables.
mysqldump issues DML instructions for the
system tables which use the non-transactional MyISAM storage
engine, and this combination is not permitted when GTIDs are
enabled. Also be aware that loading a dump file from a server with
GTIDs enabled, into another server with GTIDs enabled, causes
different transaction identifiers to be generated.