Mysql：Replication

Chapter 16 Replication

Table of Contents

16.1 Configuring Replication

16.1.1 Binary Log File Position Based Replication Configuration Overview
16.1.2 Setting Up Binary Log File Position Based Replication
16.1.3 Replication with Global Transaction Identifiers
16.1.4 MySQL Multi-Source Replication
16.1.5 Changing Replication Modes on Online Servers
16.1.6 Replication and Binary Logging Options and Variables
16.1.7 Common Replication Administration Tasks

16.2 Replication Implementation

16.2.1 Replication Formats
16.2.2 Replication Implementation Details
16.2.3 Replication Channels
16.2.4 Replication Relay and Status Logs
16.2.5 How Servers Evaluate Replication Filtering Rules

16.3 Replication Solutions

16.3.1 Using Replication for Backups
16.3.2 Handling an Unexpected Halt of a Replication Slave
16.3.3 Using Replication with Different Master and Slave Storage Engines
16.3.4 Using Replication for Scale-Out
16.3.5 Replicating Different Databases to Different Slaves
16.3.6 Improving Replication Performance
16.3.7 Switching Masters During Failover
16.3.8 Setting Up Replication to Use Encrypted Connections
16.3.9 Semisynchronous Replication
16.3.10 Delayed Replication

16.4 Replication Notes and Tips

16.4.1 Replication Features and Issues
16.4.2 Replication Compatibility Between MySQL Versions
16.4.3 Upgrading a Replication Setup
16.4.4 Troubleshooting Replication
16.4.5 How to Report Replication Bugs or Problems

16.4.1 Replication Features and Issues

16.4.1.1 Replication and AUTO_INCREMENT
16.4.1.2 Replication and BLACKHOLE Tables
16.4.1.3 Replication and Character Sets
16.4.1.4 Replication and CHECKSUM TABLE
16.4.1.5 Replication of CREATE ... IF NOT EXISTS Statements
16.4.1.6 Replication of CREATE TABLE ... SELECT Statements
16.4.1.7 Replication of CREATE SERVER, ALTER SERVER, and DROP SERVER
16.4.1.8 Replication of CURRENT_USER()
16.4.1.9 Replication of DROP ... IF EXISTS Statements
16.4.1.10 Replication with Differing Table Definitions on Master and Slave
16.4.1.11 Replication and DIRECTORY Table Options
16.4.1.12 Replication and Floating-Point Values
16.4.1.13 Replication and Fractional Seconds Support
16.4.1.14 Replication and FLUSH
16.4.1.15 Replication and System Functions
16.4.1.16 Replication of Invoked Features
16.4.1.17 Replication and LIMIT
16.4.1.18 Replication and LOAD DATA
16.4.1.19 Replication and max_allowed_packet
16.4.1.20 Replication and MEMORY Tables
16.4.1.21 Replication of the mysql System Database
16.4.1.22 Replication and the Query Optimizer
16.4.1.23 Replication and Partitioning
16.4.1.24 Replication and REPAIR TABLE
16.4.1.25 Replication and Reserved Words
16.4.1.26 Replication and Master or Slave Shutdowns
16.4.1.27 Slave Errors During Replication
16.4.1.28 Replication and Server SQL Mode
16.4.1.29 Replication and Temporary Tables
16.4.1.30 Replication Retries and Timeouts
16.4.1.31 Replication and Time Zones
16.4.1.32 Replication and Transaction Inconsistencies
16.4.1.33 Replication and Transactions
16.4.1.34 Replication and Triggers
16.4.1.35 Replication and TRUNCATE TABLE
16.4.1.36 Replication and User Name Length
16.4.1.37 Replication and Variables
16.4.1.38 Replication and Views

16.4.2 Replication Compatibility Between MySQL Versions

MySQL supports replication from one release series to the next higher release series. For example, you can replicate from a master running MySQL 5.6 to a slave running MySQL 5.7, from a master running MySQL 5.7 to a slave running MySQL 8.0, and so on. However, you may encounter difficulties when replicating from an older master to a newer slave if the master uses statements or relies on behavior no longer supported in the version of MySQL used on the slave. For example, foreign key names longer than 64 characters are no longer supported from MySQL 8.0.

The use of more than two MySQL Server versions is not supported in replication setups involving multiple masters, regardless of the number of master or slave MySQL servers. This restriction applies not only to release series, but to version numbers within the same release series as well. For example, if you are using a chained or circular replication setup, you cannot use MySQL 5.7.22, MySQL 5.7.23, and MySQL 5.7.24 concurrently, although you could use any two of these releases together.

Important

It is strongly recommended to use the most recent release available within a given MySQL release series because replication (and other) capabilities are continually being improved. It is also recommended to upgrade masters and slaves that use early releases of a release series of MySQL to GA (production) releases when the latter become available for that release series.

Replication from newer masters to older slaves may be possible, but is generally not supported. This is due to a number of factors:

Binary log format changes. The binary log format can change between major releases. While we attempt to maintain backward compatibility, this is not always possible.

This also has significant implications for upgrading replication servers; see Section 16.4.3, “Upgrading a Replication Setup”, for more information.
For more information about row-based replication, see Section 16.2.1, “Replication Formats”.
SQL incompatibilities. You cannot replicate from a newer master to an older slave using statement-based replication if the statements to be replicated use SQL features available on the master but not on the slave.

However, if both the master and the slave support row-based replication, and there are no data definition statements to be replicated that depend on SQL features found on the master but not on the slave, you can use row-based replication to replicate the effects of data modification statements even if the DDL run on the master is not supported on the slave.

For more information on potential replication issues, see Section 16.4.1, “Replication Features and Issues”.

16.4.3 Upgrading a Replication Setup

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 master to 5.7 from an earlier MySQL release series, you should first ensure that all the slaves of this master are using the same 5.7.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.7.x version, restart it, and restart replication. Relay logs created by the slave 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 slave. 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 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 (binlog_format=ROW) and wait until all slaves have processed all binary logs produced up to the point of this change. Then upgrade the slaves.

After the slaves have been upgraded, shut down the master, upgrade it to the same 5.7.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 5.7 master is able to read the old binary logs written prior to the upgrade and to send them to the 5.7 slaves. The slaves recognize the old format and handle it properly. Binary logs created by the master subsequent to the upgrade are in 5.7 format. These too are recognized by the 5.7 slaves.

In other words, when upgrading to MySQL 5.7, the slaves must be MySQL 5.7 before you can upgrade the master 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 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 = OFF 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, enable the skip_networking system variable 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 OFF earlier, execute a SET sql_log_bin = ON statement. If you restarted the master to disable the binary log, restart it with --log-bin, and without enabling the skip_networking system variable so that clients and slaves can connect.
Restart the slaves, 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 master or the slave 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.

16.4.4 Troubleshooting Replication

If you have followed the instructions but your replication setup is not working, the first thing to do is check the error log for messages. Many users have lost time by not doing this soon enough after encountering problems.

If you cannot tell from the error log what the problem was, try the following techniques:

Verify that the master has binary logging enabled by issuing a SHOW MASTER STATUS statement. If logging is enabled, Position is nonzero. If binary logging is not enabled, verify that you are running the master with the --log-bin option.
Verify that the server_id system variable was set at startup on both the master and slave and that the ID value is unique on each server.
Verify that the slave is running. Use SHOW SLAVE STATUS to check whether the Slave_IO_Running and Slave_SQL_Running values are both Yes. If not, verify the options that were used when starting the slave server. For example, --skip-slave-start prevents the slave threads from starting until you issue a START SLAVE statement.
If the slave is running, check whether it established a connection to the master. Use SHOW PROCESSLIST, find the I/O and SQL threads and check their State column to see what they display. See Section 16.2.2, “Replication Implementation Details”. If the I/O thread state says Connecting to master, check the following:
- Verify the privileges for the user being used for replication on the master.
- Check that the host name of the master is correct and that you are using the correct port to connect to the master. The port used for replication is the same as used for client network communication (the default is 3306). For the host name, ensure that the name resolves to the correct IP address.
- Check the configuration file to see whether the skip_networking system variable has been enabled on the master or slave to disable networking. If so, comment the setting or remove it.
- If the master has a firewall or IP filtering configuration, ensure that the network port being used for MySQL is not being filtered.
- Check that you can reach the master by using ping or traceroute/tracert to reach the host.
If the slave was running previously but has stopped, the reason usually is that some statement that succeeded on the master failed on the slave. This should never happen if you have taken a proper snapshot of the master, and never modified the data on the slave outside of the slave thread. If the slave stops unexpectedly, it is a bug or you have encountered one of the known replication limitations described in Section 16.4.1, “Replication Features and Issues”. If it is a bug, see Section 16.4.5, “How to Report Replication Bugs or Problems”, for instructions on how to report it.
If a statement that succeeded on the master refuses to run on the slave, try the following procedure if it is not feasible to do a full database resynchronization by deleting the slave's databases and copying a new snapshot from the master:
1. Determine whether the affected table on the slave is different from the master table. Try to understand how this happened. Then make the slave's table identical to the master's and run START SLAVE.
2. If the preceding step does not work or does not apply, try to understand whether it would be safe to make the update manually (if needed) and then ignore the next statement from the master.
3. If you decide that the slave can skip the next statement from the master, issue the following statements:
```
mysql> SET GLOBAL sql_slave_skip_counter = N;
mysql> START SLAVE;
```
  The value of N should be 1 if the next statement from the master does not use AUTO_INCREMENT or LAST_INSERT_ID(). Otherwise, the value should be 2. The reason for using a value of 2 for statements that use AUTO_INCREMENT or LAST_INSERT_ID() is that they take two events in the binary log of the master.
  
  See also Section 13.4.2.5, “SET GLOBAL sql_slave_skip_counter Statement”.
4. If you are sure that the slave started out perfectly synchronized with the master, and that no one has updated the tables involved outside of the slave thread, then presumably the discrepancy is the result of a bug. If you are running the most recent version of MySQL, please report the problem. If you are running an older version, try upgrading to the latest production release to determine whether the problem persists.