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 applies to upgrading replication from older versions of MySQL to MySQL 5.1. A 4.0 server should be 4.0.3 or newer.
When you upgrade a master to 5.1 from an earlier MySQL release series, you should first ensure that all the slaves of this master are using the same 5.1.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 5.1.x version, restart it, and restart replication. The 5.1 slave is able to read the old relay logs written prior to the upgrade and to execute the statements they contain. Relay logs created by the slave after the upgrade are in 5.1 format.
After the slaves have been upgraded, shut down the master, upgrade it to the same 5.1.x release as the slaves, and restart it. The 5.1 master is able to read the old binary logs written prior to the upgrade and to send them to the 5.1 slaves. The slaves recognize the old format and handle it properly. Binary logs created by the master subsequent to the upgrade are in 5.1 format. These too are recognized by the 5.1 slaves.
In other words, when upgrading to MySQL 5.1, the slaves must be MySQL 5.1 before you can upgrade the master to 5.1. Note that downgrading from 5.1 to older versions does not work so simply: You must ensure that any 5.1 binary log or relay log has been fully processed, so that you can remove it before proceeding with the downgrade.
Downgrading a replication setup to a previous version cannot be done once you have switched from statement-based to row-based replication, and after the first row-based statement has been written to the binlog. See Section 16.1.2, “Replication Formats”.
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, will be detailed at Section 184.108.40.206, “Upgrading from MySQL 5.0 to 5.1”. 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-start option 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
REPAIR TABLE or
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 =
0 statement. Alternatively, stop the master and
restart it without the
--log-bin option. If you
restart the master, you might also want to disallow client
connections. For example, if all clients connect using TCP/IP,
option 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_bin to 0 earlier,
SET sql_log_bin = 1 statement. If
you restarted the master to disable the binary log, restart it
--log-bin, and without
--skip-networking so that
clients and slaves can connect.
Restart the slaves, this time without the