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.1, “Upgrading MySQL”
When you upgrade a master to 8.0 from an earlier MySQL release series, you should first ensure that all the slaves of this master are using the same 8.0.x release. If this is not the case, you should first upgrade the slaves. To upgrade each slave, shut it down, upgrade it to the appropriate 8.0.x version, restart it, and restart replication. Relay logs created by the slave after the upgrade are in 8.0 format.
Changes affecting operations in strict SQL mode
STRICT_ALL_TABLES) may result in
replication failure on an upgraded slave. If you use
if a slave is upgraded before the master, the nonupgraded master
will execute statements without error that may 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. Alternatively, if you cannot stop new
statements, temporarily change to row-based logging on the master
wait until all slaves have processed all binary logs produced up
to the point of this change. Then upgrade the slaves.
The default character set has changed to
utf8mb4 in MySQL 8.0. In a replicated setting,
when upgrading from MySQL 5.7 to 8.0, it is advisable to change
the default character set back to the character set used in MySQL
5.7 before upgrading. After the upgrade is completed, the default
character set can be changed to
Assuming that the previous defaults were used, one way to preserve
them is to start the server with these lines in the
[mysqld] character_set_server=latin1 collation_server=latin1_swedish_ci
If a replication setup was already using
utf8mb4, there has been a change in MySQL 8.0
of the default collation for the
character set that can also result in replication failure on an
upgraded slave. The default collation for
utf8mb4 in MySQL 5.7 is
utf8mb4_general_ci, but it is
utf8mb4_0900_ai_ci in MySQL 8.0. This can
result in duplicate key errors for replicated
CREATE DATABASE, and
ALTER DATABASE statements. To
prevent this issue, on the master, specify the
system variable to name the default collation that the master uses
utf8mb4. The value of this system variable
is replicated to the slave, and the slave uses it to identify the
master's default collation for
therefore to process the replicated data correctly.
After the slaves have been upgraded, shut down the master, upgrade it to the same 8.0.x release as the slaves, and restart it. If you had temporarily changed the master to row-based logging, change it back to statement-based logging. The 8.0 master is able to read the old binary logs written prior to the upgrade and to send them to the 8.0 slaves. The slaves recognize the old format and handle it properly. Binary logs created by the master subsequent to the upgrade are in 8.0 format. These too are recognized by the 8.0 slaves.
In other words, when upgrading to MySQL 8.0, the slaves must be MySQL 8.0 before you can upgrade the master to 8.0. Note that downgrading from 8.0 to older versions does not work so simply: You must ensure that any 8.0 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 126.96.36.199, “Changes Affecting Upgrades to MySQL 8.0”. It is safest to perform these operations separately on the slaves and the master, and to disable replication of these operations from the master to the slave. To achieve this, use the following procedure:
Stop all the slaves and upgrade them. Restart them with the
--skip-slave-startoption so that they do not connect to the master. Perform any table repair or rebuilding operations needed to re-create database objects, such as use of
ALTER TABLE, or dumping and reloading tables or triggers.
Disable the binary log on the master. To do this without restarting the master, execute a
SET sql_log_bin = 0statement. Alternatively, stop the master and restart it with the
--skip-log-binoption. If you restart the master, you might also want to disallow client connections. For example, if all clients connect using TCP/IP, use the
--skip-networkingoption when you restart the master.
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 slaves later.
Re-enable the binary log on the master. If you set
sql_log_binto 0 earlier, execute a
SET sql_log_bin = 1statement. If you restarted the master to disable the binary log, restart it without
--skip-log-bin, and without
--skip-networkingso that clients and slaves can connect.
Restart the slaves, this time without the
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 master or the slave before making sure that the setup meets all the requirements for GTID-based replication. See Section 188.8.131.52, “Setting Up Replication Using GTIDs”, which contains information about converting existing replication setups to use GTID-based replication.
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