When you upgrade servers that participate in a replication topology, you need to take into account each server's role in the topology and look out for issues specific to replication. For general information and instructions for upgrading a MySQL Server instance, see Section 2.10, “Upgrading MySQL”.
As explained in Section 17.5.2, “Replication Compatibility Between MySQL Versions”, MySQL supports replication from a source running one release series to a replica running the next higher release series, but does not support replication from a source running a later release to a replica running an earlier release. A replica at an earlier release might not have the required capability to process transactions that can be handled by the source at a later release. You must therefore upgrade all of the replicas in a replication topology to the target MySQL Server release, before you upgrade the source server to the target release. In this way you will never be in the situation where a replica still at the earlier release is attempting to handle transactions from a source at the later release.
In a replication topology where there are multiple sources (multi-source replication), the use of more than two MySQL Server versions is not supported, regardless of the number of source or replica MySQL servers. This restriction applies not only to release series, but to version numbers within the same release series as well.
If you need to downgrade the servers in a replication topology, the source must be downgraded before the replicas are downgraded. On the replicas, you must ensure that the binary log and relay log have been fully processed, and remove them before proceeding with the downgrade.
Although this upgrade sequence is correct, it is possible to
still encounter replication difficulties when replicating from a
source at an earlier release that has not yet been upgraded, to
a replica at a later release that has been upgraded. This can
happen if the source uses statements or relies on behavior that
is no longer supported in the later release installed on the
replica. You can use MySQL Shell's upgrade checker utility
util.checkForServerUpgrade() to check MySQL
5.7 server instances or MySQL 8.0 server instances for upgrade
to a GA MySQL 8.0 release. The utility identifies anything that
needs to be fixed for that server instance so that it does not
cause an issue after the upgrade, including features and
behaviors that are no longer available in the later release. See
Upgrade Checker Utility for information
on the upgrade checker utility.
If you are upgrading an existing replication setup from a version of MySQL that does not support global transaction identifiers (GTIDs) to a version that does, only enable GTIDs on the source and the replicas when you have made sure that the setup meets all the requirements for GTID-based replication. See Section 126.96.36.199, “Setting Up Replication Using GTIDs” for information about converting binary log file position based replication setups to use GTID-based replication.
Changes affecting operations in strict SQL mode
STRICT_ALL_TABLES) may result
in replication failure on an upgraded replica. If you use
binlog_format=STATEMENT), if a
replica is upgraded before the source, the source executes
statements which succeed there but which may fail on the replica
and so cause replication to stop. 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
wait until all replicas have processed all binary logs produced
up to the point of this change, then upgrade the replicas.
The default character set in MySQL 8.1 is
To upgrade a replication topology, follow the instructions in Section 2.10, “Upgrading MySQL” for each individual MySQL Server instance, using this overall procedure:
Upgrade the replicas first. On each replica instance:
Carry out the preliminary checks and steps described in Section 2.10.5, “Preparing Your Installation for Upgrade”.
Shut down MySQL Server.
Upgrade the MySQL Server binaries or packages.
Restart MySQL Server.
MySQL Server performs the entire MySQL upgrade procedure automatically, disabling binary logging during the upgrade.
Restart replication using a
When all the replicas have been upgraded, follow the same steps to upgrade and restart the source server, with the exception of the
START REPLICAstatement. If you made a temporary change to row-based logging or to the default character set, you can revert the change now.
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.10.4, “Changes in MySQL 8.1”. 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 the binaries or packages. Restart them with
--skip-replica-startoption, 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
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 = OFFstatement. Alternatively, stop the source and restart it with the
--skip-log-binoption. 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_networkingsystem 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
OFFearlier, execute a
SET sql_log_bin = ONstatement. If you restarted the source to disable the binary log, restart it without
--skip-log-bin, and without enabling the
skip_networkingsystem variable so that clients and replicas can connect.
Restart the replicas, this time without the