Chapter 17 Replication


Replication enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). Replication is asynchronous by default; slaves do not need to be connected permanently to receive updates from the master. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

Advantages of replication in MySQL include:

  • Scale-out solutions - spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves.

  • Data security - because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.

  • Analytics - live data can be created on the master, while the analysis of the information can take place on the slave without affecting the performance of the master.

  • Long-distance data distribution - you can use replication to create a local copy of data for a remote site to use, without permanent access to the master.

For information on how to use replication in such scenarios, see Section 17.3, “Replication Solutions” .

MySQL 8.0 supports different methods of replication. The traditional method is based on replicating events from the master's binary log, and requires the log files and positions in them to be synchronized between master and slave. The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files, which greatly simplifies many common replication tasks. Replication using GTIDs guarantees consistency between master and slave as long as all transactions committed on the master have also been applied on the slave. For more information about GTIDs and GTID-based replication in MySQL, see Section 17.1.3, “Replication with Global Transaction Identifiers” . For information on using binary log file position based replication, see Section 17.1, “Configuring Replication” .

Replication in MySQL supports different types of synchronization. The original type of synchronization is one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of NDB Cluster (see Chapter 22, MySQL NDB Cluster 8.0 ). In MySQL 8.0, semisynchronous replication is supported in addition to the built-in asynchronous replication. With semisynchronous replication, a commit performed on the master blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction; see Section 17.3.11, “Semisynchronous Replication” . MySQL 8.0 also supports delayed replication such that a slave server deliberately lags behind the master by at least a specified amount of time; see Section 17.3.12, “Delayed Replication” . For scenarios where synchronous replication is required, use NDB Cluster (see Chapter 22, MySQL NDB Cluster 8.0 ).

There are a number of solutions available for setting up replication between servers, and the best method to use depends on the presence of data and the engine types you are using. For more information on the available options, see Section 17.1.2, “Setting Up Binary Log File Position Based Replication” .

There are two core types of replication format, Statement Based Replication (SBR), which replicates entire SQL statements, and Row Based Replication (RBR), which replicates only the changed rows. You can also use a third variety, Mixed Based Replication (MBR). For more information on the different replication formats, see Section 17.2.1, “Replication Formats” .

Replication is controlled through a number of different options and variables. For more information, see Section 17.1.6, “Replication and Binary Logging Options and Variables” .

You can use replication to solve a number of different problems, including performance, supporting the backup of different databases, and as part of a larger solution to alleviate system failures. For information on how to address these issues, see Section 17.3, “Replication Solutions” .

For notes and tips on how different data types and statements are treated during replication, including details of replication features, version compatibility, upgrades, and potential problems and their resolution, see Section 17.4, “Replication Notes and Tips” . For answers to some questions often asked by those who are new to MySQL Replication, see Section A.13, “MySQL 8.0 FAQ: Replication” .

For detailed information on the implementation of replication, how replication works, the process and contents of the binary log, background threads and the rules used to decide how statements are recorded and replicated, see Section 17.2, “Replication Implementation” .

17.1 Configuring Replication

This section describes how to configure the different types of replication available in MySQL and includes the setup and configuration required for a replication environment, including step-by-step instructions for creating a new replication environment. The major components of this section are:

17.1.1 Binary Log File Position Based Replication Configuration Overview

This section describes replication between MySQL servers based on the binary log file position method, where the MySQL instance operating as the master (the source of the database changes) writes updates and changes as events to the binary log. The information in the binary log is stored in different logging formats according to the database changes being recorded. Slaves are configured to read the binary log from the master and to execute the events in the binary log on the slave's local database.

Each slave receives a copy of the entire contents of the binary log. It is the responsibility of the slave to decide which statements in the binary log should be executed. Unless you specify otherwise, all events in the master binary log are executed on the slave. If required, you can configure the slave to process only events that apply to particular databases or tables.

Important

You cannot configure the master to log only certain events.

Each slave keeps a record of the binary log coordinates: the file name and position within the file that it has read and processed from the master. This means that multiple slaves can be connected to the master and executing different parts of the same binary log. Because the slaves control this process, individual slaves can be connected and disconnected from the server without affecting the master's operation. Also, because each slave records the current position within the binary log, it is possible for slaves to be disconnected, reconnect and then resume processing.

The master and each slave must be configured with a unique ID (using the server-id option). In addition, each slave must be configured with information about the master host name, log file name, and position within that file. These details can be controlled from within a MySQL session using the CHANGE MASTER TO statement on the slave. The details are stored within the slave's master info repository (see Section 17.2.4, “Replication Relay and Status Logs” ).

17.1.2 Setting Up Binary Log File Position Based Replication

This section describes how to set up a MySQL server to use binary log file position based replication. There are a number of different methods for setting up replication, and the exact method to use depends on how you are setting up replication, and whether you already have data within your master database.

There are some generic tasks that are common to all setups:

Note

Certain steps within the setup process require the SUPER privilege. If you do not have this privilege, it might not be possible to enable replication.

After configuring the basic options, select your scenario:

Before administering MySQL replication servers, read this entire chapter and try all statements mentioned in Section 13.4.1, “SQL Statements for Controlling Master Servers” , and Section 13.4.2, “SQL Statements for Controlling Slave Servers” . Also familiarize yourself with the replication startup options described in Section 17.1.6, “Replication and Binary Logging Options and Variables” .

17.1.2.1 Setting the Replication Master Configuration

To configure a master to use binary log file position based replication, you must ensure that binary logging is enabled, and establish a unique server ID. If this has not already been done, a server restart is required.

Binary logging is required on the master because the binary log is the basis for replicating changes from the master to its slaves. Binary logging is enabled by default (the log_bin system variable is set to ON). The --log-bin option tells the server what base name to use for binary log files. It is recommended that you specify this option to give the binary log files a non-default base name, so that if the host name changes, you can easily continue to use the same binary log file names (see Section B.6.7, “Known Issues in MySQL” ).

Each server within a replication topology must be configured with a unique server ID, which you can specify using the --server-id option. This server ID is used to identify individual servers within the replication topology, and must be a positive integer between 1 and (2 32 )−1. If you set a server ID of 0 on a master, it refuses any connections from slaves, and if you set a server ID of 0 on a slave, it refuses to connect to a master. Other than that, how you organize and select the numbers is your choice, so long as each server ID is different from every other server ID in use by any other server in the replication topology. The server_id system variable is set to 1 by default. A server can be started with this default server ID, but an informational message is issued if you did not specify a server ID explicitly.

Note

The following options also have an impact on the replication master:

  • For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the replication master's my.cnf file.

  • Ensure that the skip-networking option is not enabled on the replication master. If networking has been disabled, the slave cannot communicate with the master and replication fails.

17.1.2.2 Setting the Replication Slave Configuration

Each replication slave must have a unique server ID. If this has not already been done, this part of slave setup requires a server restart.

If the slave server ID is not already set, or the current value conflicts with the value that you have chosen for the master server, shut down the slave server and edit the [mysqld] section of the configuration file to specify a unique server ID. For example:

[mysqld]
server-id=2
                            

After making the changes, restart the server.

If you are setting up multiple slaves, each one must have a unique nonzero server-id value that differs from that of the master and from any of the other slaves.

Binary logging is enabled by default on all servers. A slave is not required to have binary logging enabled for replication to take place. However, binary logging on a slave means that the slave's binary log can be used for data backups and crash recovery.

Slaves that have binary logging enabled can also be used as part of a more complex replication topology. For example, you might want to set up replication servers using this chained arrangement:

A -> B -> C
                            

Here, A serves as the master for the slave B , and B serves as the master for the slave C . For this to work, B must be both a master and a slave. Updates received from A must be logged by B to its binary log, in order to be passed on to C . In addition to binary logging, this replication topology requires the --log-slave-updates option to be enabled. With this option, the slave writes updates that are received from a master server and performed by the slave's SQL thread to the slave's own binary log. The --log-slave-updates option is enabled by default.

If you need to disable binary logging or slave update logging on a slave server, you can do this by specifying the --skip-log-bin and --skip-log-slave-updates options for the slave.

17.1.2.3 Creating a User for Replication

Each slave connects to the master using a MySQL user name and password, so there must be a user account on the master that the slave can use to connect. The user name is specified by the MASTER_USER option on the CHANGE MASTER TO command when you set up a replication slave. Any account can be used for this operation, providing it has been granted the REPLICATION SLAVE privilege. You can choose to create a different account for each slave, or connect to the master using the same account for each slave.

Although you do not have to create an account specifically for replication, you should be aware that the replication user name and password are stored in plain text in the master info repository table mysql.slave_master_info (see Section 17.2.4.2, “Slave Status Logs” ). Therefore, you may want to create a separate account that has privileges only for the replication process, to minimize the possibility of compromise to other accounts.

To create a new account, use CREATE USER . To grant this account the privileges required for replication, use the GRANT statement. If you create an account solely for the purposes of replication, that account needs only the REPLICATION SLAVE privilege. For example, to set up a new user, repl , that can connect for replication from any host within the example.com domain, issue these statements on the master:

mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';
                            

See Section 13.7.1, “Account Management Statements” , for more information on statements for manipulation of user accounts.

Important

To connect to the replication master using a user account that authenticates with the caching_sha2_password plugin, you must either set up a secure connection as described in Section 17.3.9, “Setting Up Replication to Use Encrypted Connections” , or enable the unencrypted connection to support password exchange using an RSA key pair. The caching_sha2_password authentication plugin is the default for new users created from MySQL 8.0 (for details, see Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication” ). If the user account that you create or use for replication (as specified by the MASTER_USER option) uses this authentication plugin, and you are not using a secure connection, you must enable RSA key pair-based password exchange for a successful connection.

17.1.2.4 Obtaining the Replication Master Binary Log Coordinates

To configure the slave to start the replication process at the correct point, you need to note the master's current coordinates within its binary log.

Warning

This procedure uses FLUSH TABLES WITH READ LOCK , which blocks COMMIT operations for InnoDB tables.

If you are planning to shut down the master to create a data snapshot, you can optionally skip this procedure and instead store a copy of the binary log index file along with the data snapshot. In that situation, the master creates a new binary log file on restart. The master binary log coordinates where the slave must start the replication process are therefore the start of that new file, which is the next binary log file on the master following after the files that are listed in the copied binary log index file.

To obtain the master binary log coordinates, follow these steps:

  1. Start a session on the master by connecting to it with the command-line client, and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:

    mysql> FLUSH TABLES WITH READ LOCK;
                                            
    Warning

    Leave the client from which you issued the FLUSH TABLES statement running so that the read lock remains in effect. If you exit the client, the lock is released.

  2. In a different session on the master, use the SHOW MASTER STATUS statement to determine the current binary log file name and position:

    mysql > SHOW MASTER STATUS;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000003 | 73       | test         | manual,mysql     |
    +------------------+----------+--------------+------------------+
                                            

    The File column shows the name of the log file and the Position column shows the position within the file. In this example, the binary log file is mysql-bin.000003 and the position is 73. Record these values. You need them later when you are setting up the slave. They represent the replication coordinates at which the slave should begin processing new updates from the master.

    If the master has been running previously with binary logging disabled, the log file name and position values displayed by SHOW MASTER STATUS or mysqldump --master-data will be empty. In that case, the values that you need to use later when specifying the slave's log file and position are the empty string ( '' ) and 4 .

You now have the information you need to enable the slave to start reading from the binary log in the correct place to start replication.

The next step depends on whether you have existing data on the master. Choose one of the following options:

17.1.2.5 Choosing a Method for Data Snapshots

If the master database contains existing data it is necessary to copy this data to each slave. There are different ways to dump the data from the master database. The following sections describe possible options.

To select the appropriate method of dumping the database, choose between these options:

  • Use the mysqldump tool to create a dump of all the databases you want to replicate. This is the recommended method, especially when using InnoDB .

  • If your database is stored in binary portable files, you can copy the raw data files to a slave. This can be more efficient than using mysqldump and importing the file on each slave, because it skips the overhead of updating indexes as the INSERT statements are replayed. With storage engines such as InnoDB this is not recommended.

17.1.2.5.1 Creating a Data Snapshot Using mysqldump

To create a snapshot of the data in an existing master database, use the mysqldump tool. Once the data dump has been completed, import this data into the slave before starting the replication process.

The following example dumps all databases to a file named dbdump.db , and includes the --master-data option which automatically appends the CHANGE MASTER TO statement required on the slave to start the replication process:

shell> mysqldump --all-databases --master-data > dbdump.db
                                
Note

If you do not use --master-data , then it is necessary to lock all tables in a separate session manually. See Section 17.1.2.4, “Obtaining the Replication Master Binary Log Coordinates” .

It is possible to exclude certain databases from the dump using the mysqldump tool. If you want to choose which databases to include in the dump, do not use --all-databases . Choose one of these options:

  • Exclude all the tables in the database using --ignore-table option.

  • Name only those databases which you want dumped using the --databases option.

更多信息,请见 Section 4.5.4, “ mysqldump — A Database Backup Program” .

To import the data, either copy the dump file to the slave, or access the file from the master when connecting remotely to the slave.

17.1.2.5.2 Creating a Data Snapshot Using Raw Data Files

This section describes how to create a data snapshot using the raw files which make up the database. Employing this method with a table using a storage engine that has complex caching or logging algorithms requires extra steps to produce a perfect point in time snapshot: the initial copy command could leave out cache information and logging updates, even if you have acquired a global read lock. How the storage engine responds to this depends on its crash recovery abilities.

If you use InnoDB tables, you can use the mysqlbackup command from the MySQL Enterprise Backup component to produce a consistent snapshot. This command records the log name and offset corresponding to the snapshot to be used on the slave. MySQL Enterprise Backup is a commercial product that is included as part of a MySQL Enterprise subscription. See Section 30.2, “MySQL Enterprise Backup Overview” for detailed information.

This method also does not work reliably if the master and slave have different values for ft_stopword_file , ft_min_word_len , or ft_max_word_len and you are copying tables having full-text indexes.

Assuming the above exceptions do not apply to your database, use the cold backup technique to obtain a reliable binary snapshot of InnoDB tables: do a slow shutdown of the MySQL Server, then copy the data files manually.

To create a raw data snapshot of MyISAM tables when your MySQL data files exist on a single file system, you can use standard file copy tools such as cp or copy , a remote copy tool such as scp or rsync , an archiving tool such as zip or tar , or a file system snapshot tool such as dump . If you are replicating only certain databases, copy only those files that relate to those tables. For InnoDB , all tables in all databases are stored in the system tablespace files, unless you have the innodb_file_per_table option enabled.

The following files are not required for replication:

  • Files relating to the mysql database.

  • The master info repository file master.info , if used; the use of this file is now deprecated (see Section 17.2.4, “Replication Relay and Status Logs” ).

  • The master's binary log files, with the exception of the binary log index file if you are going to use this to locate the master binary log coordinates for the slave.

  • Any relay log files.

Depending on whether you are using InnoDB tables or not, choose one of the following:

If you are using InnoDB tables, and also to get the most consistent results with a raw data snapshot, shut down the master server during the process, as follows:

  1. Acquire a read lock and get the master's status. See Section 17.1.2.4, “Obtaining the Replication Master Binary Log Coordinates” .

  2. In a separate session, shut down the master server:

    shell> mysqladmin shutdown
                                                
  3. Make a copy of the MySQL data files. The following examples show common ways to do this. You need to choose only one of them:

    shell> tar cf /tmp/db.tar ./data
    shell> zip -r /tmp/db.zip ./data
    shell> rsync --recursive ./data /tmp/dbdata
                                                
  4. Restart the master server.

If you are not using InnoDB tables, you can get a snapshot of the system from a master without shutting down the server as described in the following steps:

  1. Acquire a read lock and get the master's status. See Section 17.1.2.4, “Obtaining the Replication Master Binary Log Coordinates” .

  2. Make a copy of the MySQL data files. The following examples show common ways to do this. You need to choose only one of them:

    shell> tar cf /tmp/db.tar ./data
    shell> zip -r /tmp/db.zip ./data
    shell> rsync --recursive ./data /tmp/dbdata
                                                
  3. In the client where you acquired the read lock, release the lock:

    mysql> UNLOCK TABLES;
                                                

Once you have created the archive or copy of the database, copy the files to each slave before starting the slave replication process.

17.1.2.6 Setting Up Replication Slaves

The following sections describe how to set up slaves. Before you proceed, ensure that you have:

The next steps depend on whether you have existing data to import to the slave or not. See Section 17.1.2.5, “Choosing a Method for Data Snapshots” for more information. Choose one of the following:

17.1.2.6.1 Setting Up Replication with New Master and Slaves

When there is no snapshot of a previous database to import, configure the slave to start the replication from the new master.

To set up replication between a master and a new slave:

  1. Start up the MySQL slave.

  2. Execute a CHANGE MASTER TO statement to set the master replication server configuration. See Section 17.1.2.7, “Setting the Master Configuration on the Slave” .

Perform these slave setup steps on each slave.

This method can also be used if you are setting up new servers but have an existing dump of the databases from a different server that you want to load into your replication configuration. By loading the data into a new master, the data is automatically replicated to the slaves.

If you are setting up a new replication environment using the data from a different existing database server to create a new master, run the dump file generated from that server on the new master. The database updates are automatically propagated to the slaves:

shell> mysql -h master < fulldb.dump
                                
17.1.2.6.2 Setting Up Replication with Existing Data

When setting up replication with existing data, transfer the snapshot from the master to the slave before starting replication. The process for importing data to the slave depends on how you created the snapshot of data on the master.

Choose one of the following:

If you used mysqldump :

  1. Start the slave, using the --skip-slave-start option so that replication does not start.

  2. Import the dump file:

    shell> mysql < fulldb.dump
                                                

If you created a snapshot using the raw data files:

  1. Extract the data files into your slave data directory. For example:

    shell> tar xvf dbdump.tar
                                                

    You may need to set permissions and ownership on the files so that the slave server can access and modify them.

  2. Start the slave, using the --skip-slave-start option so that replication does not start.

  3. Configure the slave with the replication coordinates from the master. This tells the slave the binary log file and position within the file where replication needs to start. Also, configure the slave with the login credentials and host name of the master. For more information on the CHANGE MASTER TO statement required, see Section 17.1.2.7, “Setting the Master Configuration on the Slave” .

  4. Start the slave threads:

    mysql> START SLAVE;
                                                

After you have performed this procedure, the slave connects to the master and replicates any updates that have occurred on the master since the snapshot was taken. Error messages are issued to the slave's error log if it is not able to replicate for any reason.

The slave uses information logged in its master info log and relay log info log to keep track of how much of the master's binary log it has processed. From MySQL 8.0, by default, the repositories for these slave status logs are tables named slave_master_info and slave_relay_log_info in the mysql database. The alternative settings --master-info-repository=FILE and --relay-log-info-repository=FILE , where the repositories are files named master.info and relay-log.info in the data directory, are now deprecated and will be removed in a future release.

Do not remove or edit these tables (or files, if used) unless you know exactly what you are doing and fully understand the implications. Even in that case, it is preferred that you use the CHANGE MASTER TO statement to change replication parameters. The slave uses the values specified in the statement to update the slave status logs automatically. See Section 17.2.4, “Replication Relay and Status Logs” , for more information.

Note

The contents of the master info log override some of the server options specified on the command line or in my.cnf . See Section 17.1.6, “Replication and Binary Logging Options and Variables” , for more details.

A single snapshot of the master suffices for multiple slaves. To set up additional slaves, use the same master snapshot and follow the slave portion of the procedure just described.

17.1.2.7 Setting the Master Configuration on the Slave

To set up the slave to communicate with the master for replication, configure the slave with the necessary connection information. To do this, execute the following statement on the slave, replacing the option values with the actual values relevant to your system:

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;
                            
Note

Replication cannot use Unix socket files. You must be able to connect to the master MySQL server using TCP/IP.

The CHANGE MASTER TO statement has other options as well. For example, it is possible to set up secure replication using SSL. For a full list of options, and information about the maximum permissible length for the string-valued options, see Section 13.4.2.1, “CHANGE MASTER TO Syntax” .

Important

As noted in Section 17.1.2.3, “Creating a User for Replication” , if you are not using a secure connection and the user account named in the MASTER_USER option authenticates with the caching_sha2_password plugin (the default from MySQL 8.0), you must specify the MASTER_PUBLIC_KEY_PATH or GET_MASTER_PUBLIC_KEY option in the CHANGE MASTER TO statement to enable RSA key pair-based password exchange.

17.1.2.8 Adding Slaves to a Replication Environment

You can add another slave to an existing replication configuration without stopping the master. To do this, you can set up the new slave by copying the data directory of an existing slave, and giving the new slave a different server ID (which is user-specified) and server UUID (which is generated at startup).

To duplicate an existing slave:

  1. Stop the existing slave and record the slave status information, particularly the master binary log file and relay log file positions. You can view the slave status either in the Performance Schema replication tables (see Section 26.12.11, “Performance Schema Replication Tables” ), or by issuing SHOW SLAVE STATUS as follows:

    mysql> STOP SLAVE;
    mysql> SHOW SLAVE STATUS\G
                                            
  2. Shut down the existing slave:

    shell> mysqladmin shutdown
                                            
  3. Copy the data directory from the existing slave to the new slave, including the log files and relay log files. You can do this by creating an archive using tar or WinZip , or by performing a direct copy using a tool such as cp or rsync .

    Important
    • Before copying, verify that all the files relating to the existing slave actually are stored in the data directory. For example, the InnoDB system tablespace, undo tablespace, and redo log might be stored in an alternative location. InnoDB tablespace files and file-per-table tablespaces might have been created in other directories. The binary logs and relay logs for the slave might be in their own directories outside the data directory. Check through the system variables that are set for the existing slave and look for any alternative paths that have been specified. If you find any, copy these directories over as well.

    • During copying, if files have been used for the master info and relay log info repositories (see Section 17.2.4, “Replication Relay and Status Logs” ), ensure that you also copy these files from the existing slave to the new slave. If tables have been used for the repositories, which is the default from MySQL 8.0, the tables are in the data directory.

    • After copying, delete the auto.cnf file from the copy of the data directory on the new slave, so that the new slave is started with a different generated server UUID. The server UUID must be unique.

    A common problem that is encountered when adding new replication slaves is that the new slave fails with a series of warning and error messages like these:

    071118 16:44:10 [Warning] Neither --relay-log nor --relay-log-index were used; so
    replication may break when this MySQL server acts as a slave and has his hostname
    changed!! Please use '--relay-log=new_slave_hostname-relay-bin' to avoid this problem.
    071118 16:44:10 [ERROR] Failed to open the relay log './old_slave_hostname-relay-bin.003525'
    (relay_log_pos 22940879)
    071118 16:44:10 [ERROR] Could not find target log during relay log initialization
    071118 16:44:10 [ERROR] Failed to initialize the master info structure
                                            

    This situation can occur if the --relay-log option is not specified, as the relay log files contain the host name as part of their file names. This is also true of the relay log index file if the --relay-log-index option is not used. See Section 17.1.6, “Replication and Binary Logging Options and Variables” , for more information about these options.

    To avoid this problem, use the same value for --relay-log on the new slave that was used on the existing slave. If this option was not set explicitly on the existing slave, use existing_slave_hostname -relay-bin . If this is not possible, copy the existing slave's relay log index file to the new slave and set the --relay-log-index option on the new slave to match what was used on the existing slave. If this option was not set explicitly on the existing slave, use existing_slave_hostname -relay-bin.index . Alternatively, if you have already tried to start the new slave after following the remaining steps in this section and have encountered errors like those described previously, then perform the following steps:

    1. If you have not already done so, issue STOP SLAVE on the new slave.

      If you have already started the existing slave again, issue STOP SLAVE on the existing slave as well.

    2. Copy the contents of the existing slave's relay log index file into the new slave's relay log index file, making sure to overwrite any content already in the file.

    3. Proceed with the remaining steps in this section.

  4. When copying is complete, restart the existing slave.

  5. On the new slave, edit the configuration and give the new slave a unique server ID (using the server-id option) that is not used by the master or any of the existing slaves.

  6. Start the new slave server, specifying the --skip-slave-start option so that replication does not start yet. Use the Performance Schema replication tables or issue SHOW SLAVE STATUS to confirm that the new slave has the correct settings when compared with the existing slave. Also display the server ID and server UUID and verify that these are correct and unique for the new slave.

  7. Start the slave threads by issuing a START SLAVE statement:

    mysql> START SLAVE;
                                            

    The new slave now uses the information in its master info repository to start the replication process.

17.1.3 Replication with Global Transaction Identifiers

This section explains transaction-based replication using global transaction identifiers (GTIDs). When using GTIDs, each transaction can be identified and tracked as it is committed on the originating server and applied by any slaves; this means that it is not necessary when using GTIDs to refer to log files or positions within those files when starting a new slave or failing over to a new master, which greatly simplifies these tasks. Because GTID-based replication is completely transaction-based, it is simple to determine whether masters and slaves are consistent; as long as all transactions committed on a master are also committed on a slave, consistency between the two is guaranteed. You can use either statement-based or row-based replication with GTIDs (see Section 17.2.1, “Replication Formats” ); however, for best results, we recommend that you use the row-based format.

GTIDs are always preserved between master and slave. This means that you can always determine the source for any transaction applied on any slave by examining its binary log. In addition, once a transaction with a given GTID is committed on a given server, any subsequent transaction having the same GTID is ignored by that server. Thus, a transaction committed on the master can be applied no more than once on the slave, which helps to guarantee consistency.

This section discusses the following topics:

For information about MySQL Server options and variables relating to GTID-based replication, see Section 17.1.6.5, “Global Transaction ID Options and Variables” . See also Section 12.18, “Functions Used with Global Transaction Identifiers (GTIDs)” , which describes SQL functions supported by MySQL 8.0 for use with GTIDs.

17.1.3.1 GTID Format and Storage

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (the master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication topology.

GTID assignment distinguishes between client transactions, which are committed on the master, and replicated transactions, which are reproduced on a slave. When a client transaction is committed on the master, it is assigned a new GTID, provided that the transaction was written to the binary log. Client transactions are guaranteed to have monotonically increasing GTIDs without gaps between the generated numbers. If a client transaction is not written to the binary log (for example, because the transaction was filtered out, or the transaction was read-only), it is not assigned a GTID on the server of origin.

Replicated transactions retain the same GTID that was assigned to the transaction on the server of origin. The GTID is present before the replicated transaction begins to execute, and is persisted even if the replicated transaction is not written to the binary log on the slave, or is filtered out on the slave. The MySQL system table mysql.gtid_executed is used to preserve the assigned GTIDs of all the transactions applied on a MySQL server, except those that are stored in a currently active binary log file.

The auto-skip function for GTIDs means that a transaction committed on the master can be applied no more than once on the slave, which helps to guarantee consistency. Once a transaction with a given GTID has been committed on a given server, any attempt to execute a subsequent transaction with the same GTID is ignored by that server. No error is raised, and no statement in the transaction is executed.

If a transaction with a given GTID has started to execute on a server, but has not yet committed or rolled back, any attempt to start a concurrent transaction on the server with the same GTID will block. The server neither begins to execute the concurrent transaction nor returns control to the client. Once the first attempt at the transaction commits or rolls back, concurrent sessions that were blocking on the same GTID may proceed. If the first attempt rolled back, one concurrent session proceeds to attempt the transaction, and any other concurrent sessions that were blocking on the same GTID remain blocked. If the first attempt committed, all the concurrent sessions stop being blocked, and auto-skip all the statements of the transaction.

A GTID is represented as a pair of coordinates, separated by a colon character ( : ), as shown here:

GTID = source_id:transaction_id
                            

The source_id identifies the originating server. Normally, the master's server_uuid is used for this purpose. The transaction_id is a sequence number determined by the order in which the transaction was committed on the master; for example, the first transaction to be committed has 1 as its transaction_id , and the tenth transaction to be committed on the same originating server is assigned a transaction_id of 10 . It is not possible for a transaction to have 0 as a sequence number in a GTID. For example, the twenty-third transaction to be committed originally on the server with the UUID 3E11FA47-71CA-11E1-9E33-C80AA9429562 has this GTID:

3E11FA47-71CA-11E1-9E33-C80AA9429562:23
                            

The GTID for a transaction is shown in the output from mysqlbinlog , and it is used to identify an individual transaction in the Performance Schema replication status tables, for example, replication_applier_status_by_worker . The value stored by the gtid_next system variable ( @@GLOBAL.gtid_next ) is a single GTID.

GTID Sets

A GTID set is a set comprising one or more single GTIDs or ranges of GTIDs. GTID sets are used in a MySQL server in several ways. For example, the values stored by the gtid_executed and gtid_purged system variables are GTID sets. The START SLAVE clauses UNTIL SQL_BEFORE_GTIDS and UNTIL SQL_AFTER_GTIDS can be used to make a slave process transactions only up to the first GTID in a GTID set, or stop after the last GTID in a GTID set. The built-in functions GTID_SUBSET() and GTID_SUBTRACT() require GTID sets as input.

A range of GTIDs originating from the same server can be collapsed into a single expression, as shown here:

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5
                                

The above example represents the first through fifth transactions originating on the MySQL server whose server_uuid is 3E11FA47-71CA-11E1-9E33-C80AA9429562 . Multiple single GTIDs or ranges of GTIDs originating from the same server can also be included in a single expression, with the GTIDs or ranges separated by colons, as in the following example:

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-3:11:47-49
                                

A GTID set can include any combination of single GTIDs and ranges of GTIDs, and it can include GTIDs originating from different servers. This example shows the GTID set stored in the gtid_executed system variable ( @@GLOBAL.gtid_executed ) of a slave that has applied transactions from more than one master:

2174B383-5441-11E8-B90A-C80AA9429562:1-3, 24DA167-0C0C-11E8-8442-00059A3C7B00:1-19
                                

When GTID sets are returned from server variables, UUIDs are in alphabetical order, and numeric intervals are merged and in ascending order.

The syntax for a GTID set is as follows:

gtid_set:
    uuid_set [, uuid_set] ...
    | ''
uuid_set:
    uuid:interval[:interval]...
uuid:
    hhhhhhhh-hhhh-hhhh-hhhh-hhhhhhhhhhhh
h:
    [0-9|A-F]
interval:
    n[-n]
    (n >= 1)
                                
mysql.gtid_executed Table

GTIDs are stored in a table named gtid_executed , in the mysql database. A row in this table contains, for each GTID or set of GTIDs that it represents, the UUID of the originating server, and the starting and ending transaction IDs of the set; for a row referencing only a single GTID, these last two values are the same.

The mysql.gtid_executed table is created (if it does not already exist) when MySQL Server is installed or upgraded, using a CREATE TABLE statement similar to that shown here:

CREATE TABLE gtid_executed (
    source_uuid CHAR(36) NOT NULL,
    interval_start BIGINT(20) NOT NULL,
    interval_end BIGINT(20) NOT NULL,
    PRIMARY KEY (source_uuid, interval_start)
)
                                
Warning

As with other MySQL system tables, do not attempt to create or modify this table yourself.

The mysql.gtid_executed table is provided for internal use by the MySQL server. It enables a slave to use GTIDs when binary logging is disabled on the slave, and it enables retention of the GTID state when the binary logs have been lost. The mysql.gtid_executed table is reset by RESET MASTER .

GTIDs are stored in the mysql.gtid_executed table only when gtid_mode is ON or ON_PERMISSIVE . The point at which GTIDs are stored depends on whether binary logging is enabled or disabled:

  • If binary logging is disabled ( log_bin is OFF ), or if log_slave_updates is disabled, the server stores the GTID belonging to each transaction together with the transaction in the table. In addition, the table is compressed periodically at a user-configurable rate; see mysql.gtid_executed Table Compression , for more information. This situation can only apply on a replication slave where binary logging or slave update logging is disabled. It does not apply on a replication master, because on a master, binary logging must be enabled for replication to take place.

  • If binary logging is enabled ( log_bin is ON ), whenever the binary log is rotated or the server is shut down, the server writes GTIDs for all transactions that were written into the previous binary log into the mysql.gtid_executed table. This situation applies on a replication master, or a replication slave where binary logging is enabled.

    In the event of the server stopping unexpectedly, the set of GTIDs from the current binary log file is not saved in the mysql.gtid_executed table. These GTIDs are added to the table from the binary log file during recovery. The exception to this is if you disable binary logging when the server is restarted (using --skip-log-bin or --disable-log-bin ). In this situation, the server cannot access the binary log file to recover the GTIDs, so replication cannot be started.

    When binary logging is enabled, the mysql.gtid_executed table does not hold a complete record of the GTIDs for all executed transactions. That information is provided by the global value of the gtid_executed system variable. Always use @@GLOBAL.gtid_executed , which is updated after every commit, to represent the GTID state for the MySQL server, and do not query the mysql.gtid_executed table.

mysql.gtid_executed Table Compression

Over the course of time, the mysql.gtid_executed table can become filled with many rows referring to individual GTIDs that originate on the same server, and whose transaction IDs make up a range, similar to what is shown here:

+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
|--------------------------------------+----------------+--------------|
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 37             | 37           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 38             | 38           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 39             | 39           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 40             | 40           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 41             | 41           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 42             | 42           |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 43             | 43           |
...
                                

To save space, the MySQL server compresses the mysql.gtid_executed table periodically by replacing each such set of rows with a single row that spans the entire interval of transaction identifiers, like this:

+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
|--------------------------------------+----------------+--------------|
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 37             | 43           |
...
                                

You can control the number of transactions that are allowed to elapse before the table is compressed, and thus the compression rate, by setting the gtid_executed_compression_period system variable. This variable's default value is 1000, meaning that by default, compression of the table is performed after each 1000 transactions. Setting gtid_executed_compression_period to 0 prevents the compression from being performed at all, and you should be prepared for a potentially large increase in the amount of disk space that may be required by the gtid_executed table if you do this.

Note

When binary logging is enabled, the value of gtid_executed_compression_period is not used and the mysql.gtid_executed table is compressed on each binary log rotation.

Compression of the mysql.gtid_executed table is performed by a dedicated foreground thread named thread/sql/compress_gtid_table . This thread is not listed in the output of SHOW PROCESSLIST , but it can be viewed as a row in the threads table, as shown here:

mysql> SELECT * FROM performance_schema.threads WHERE NAME LIKE '%gtid%'\G
*************************** 1. row ***************************
          THREAD_ID: 26
               NAME: thread/sql/compress_gtid_table
               TYPE: FOREGROUND
     PROCESSLIST_ID: 1
   PROCESSLIST_USER: NULL
   PROCESSLIST_HOST: NULL
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Daemon
   PROCESSLIST_TIME: 1509
  PROCESSLIST_STATE: Suspending
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 18677
                                

The thread/sql/compress_gtid_table thread normally sleeps until gtid_executed_compression_period transactions have been executed, then wakes up to perform compression of the mysql.gtid_executed table as described previously. It then sleeps until another gtid_executed_compression_period transactions have taken place, then wakes up to perform the compression again, repeating this loop indefinitely. Setting this value to 0 when binary logging is disabled means that the thread always sleeps and never wakes up.

17.1.3.2 GTID Life Cycle

The life cycle of a GTID consists of the following steps:

  1. A transaction is executed and committed on the master. This client transaction is assigned a GTID composed of the master's UUID and the smallest nonzero transaction sequence number not yet used on this server. The GTID is written to the master's binary log (immediately preceding the transaction itself in the log). If a client transaction is not written to the binary log (for example, because the transaction was filtered out, or the transaction was read-only), it is not assigned a GTID.

  2. If a GTID was assigned for the transaction, the GTID is persisted atomically at commit time by writing it to the binary log at the beginning of the transaction (as a Gtid_log_event ). Whenever the binary log is rotated or the server is shut down, the server writes GTIDs for all transactions that were written into the previous binary log file into the mysql.gtid_executed table.

  3. If a GTID was assigned for the transaction, the GTID is externalized non-atomically (very shortly after the transaction is committed) by adding it to the set of GTIDs in the gtid_executed system variable ( @@GLOBAL.gtid_executed ). This GTID set contains a representation of the set of all committed GTID transactions, and it is used in replication as a token that represents the server state. With binary logging enabled (as required for the master), the set of GTIDs in the gtid_executed system variable is a complete record of the transactions applied, but the mysql.gtid_executed table is not, because the most recent history is still in the current binary log file.

  4. After the binary log data is transmitted to the slave and stored in the slave's relay log (using established mechanisms for this process, see Section 17.2, “Replication Implementation” , for details), the slave reads the GTID and sets the value of its gtid_next system variable as this GTID. This tells the slave that the next transaction must be logged using this GTID. It is important to note that the slave sets gtid_next in a session context.

  5. The slave verifies that no thread has yet taken ownership of the GTID in gtid_next in order to process the transaction. By reading and checking the replicated transaction's GTID first, before processing the transaction itself, the slave guarantees not only that no previous transaction having this GTID has been applied on the slave, but also that no other session has already read this GTID but has not yet committed the associated transaction. So if multiple clients attempt to apply the same transaction concurrently, the server resolves this by letting only one of them execute. The gtid_owned system variable ( @@GLOBAL.gtid_owned ) for the slave shows each GTID that is currently in use and the ID of the thread that owns it. If the GTID has already been used, no error is raised, and the auto-skip function is used to ignore the transaction.

  6. If the GTID has not been used, the slave applies the replicated transaction. Because gtid_next is set to the GTID already assigned by the master, the slave does not attempt to generate a new GTID for this transaction, but instead uses the GTID stored in gtid_next .

  7. If binary logging is enabled on the slave, the GTID is persisted atomically at commit time by writing it to the binary log at the beginning of the transaction (as a Gtid_log_event ). Whenever the binary log is rotated or the server is shut down, the server writes GTIDs for all transactions that were written into the previous binary log file into the mysql.gtid_executed table.

  8. If binary logging is disabled on the slave, the GTID is persisted atomically by writing it directly into the mysql.gtid_executed table. MySQL appends a statement to the transaction to insert the GTID into the table. From MySQL 8.0, this operation is atomic for DDL statements as well as for DML statements. In this situation, the mysql.gtid_executed table is a complete record of the transactions applied on the slave.

  9. Very shortly after the replicated transaction is committed on the slave, the GTID is externalized non-atomically by adding it to the set of GTIDs in the gtid_executed system variable ( @@GLOBAL.gtid_executed ) for the slave. As for the master, this GTID set contains a representation of the set of all committed GTID transactions. If binary logging is disabled on the slave, the mysql.gtid_executed table is also a complete record of the transactions applied on the slave. If binary logging is enabled on the slave, meaning that some GTIDs are only recorded in the binary log, the set of GTIDs in the gtid_executed system variable is the only complete record.

Client transactions that are completely filtered out on the master are not assigned a GTID, therefore they are not added to the set of transactions in the gtid_executed system variable, or added to the mysql.gtid_executed table. However, the GTIDs of replicated transactions that are completely filtered out on the slave are persisted. If binary logging is enabled on the slave, the filtered-out transaction is written to the binary log as a Gtid_log_event followed by an empty transaction containing only BEGIN and COMMIT statements. If binary logging is disabled, the GTID of the filtered-out transaction is written to the mysql.gtid_executed table. Preserving the GTIDs for filtered-out transactions ensures that the mysql.gtid_executed table and the set of GTIDs in the gtid_executed system variable can be compressed. It also ensures that the filtered-out transactions are not retrieved again if the slave reconnects to the master, as explained in Section 17.1.3.3, “GTID Auto-Positioning” .

On a multithreaded replication slave (with slave_parallel_workers > 0 ), transactions can be applied in parallel, so replicated transactions can commit out of order (unless slave_preserve_commit_order=1 is set). When that happens, the set of GTIDs in the gtid_executed system variable will contain multiple GTID ranges with gaps between them. (On a master or a single-threaded replication slave, there will be monotonically increasing GTIDs without gaps between the numbers.) Gaps on multithreaded replication slaves only occur among the most recently applied transactions, and are filled in as replication progresses. When replication threads are stopped cleanly using the STOP SLAVE statement, ongoing transactions are applied so that the gaps are filled in. In the event of a shutdown such as a server failure or the use of the KILL statement to stop replication threads, the gaps might remain.

It is possible for a client to simulate a replicated transaction by setting the variable @@SESSION.gtid_next to a valid GTID (consisting of a UUID and a transaction sequence number, separated by a colon) before executing the transaction. This technique is used by mysqlbinlog to generate a dump of the binary log that the client can replay to preserve GTIDs. A simulated replicated transaction committed through a client is completely equivalent to a replicated transaction committed through a replication thread, and they cannot be distinguished after the fact.

gtid_purged

The set of GTIDs in the gtid_purged system variable ( @@GLOBAL.gtid_purged ) contains the GTIDs of all the transactions that have been committed on the server, but do not exist in any binary log file on the server. The following categories of GTIDs are in this set:

  • GTIDs of replicated transactions that were committed with binary logging disabled on the slave

  • GTIDs of transactions that were written to a binary log file that has now been purged

  • GTIDs that were added explicitly to the set by the statement SET @@GLOBAL.gtid_purged

The set of GTIDs in the gtid_purged system variable is initialized when the server starts. Every binary log file begins with the event Previous_gtids_log_event , which contains the set of GTIDs in all previous binary log files (composed from the GTIDs in the preceding file's Previous_gtids_log_event , and the GTIDs of every Gtid_log_event in the file itself). The contents of Previous_gtids_log_event in the oldest and most recent binary log files are used to compute the gtid_purged set at server startup, as follows:

   All GTIDs in Previous_gtids_log_event in the most recent binary log file
+  All GTIDs of transactions in the most recent binary log file
+  All GTIDs in the mysql.gtid_executed table
-  All GTIDs in Previous_gtids_log_event in the oldest binary log file
                                

17.1.3.3 GTID Auto-Positioning

GTIDs replace the file-offset pairs previously required to determine points for starting, stopping, or resuming the flow of data between master and slave. When GTIDs are in use, all the information that the slave needs for synchronizing with the master is obtained directly from the replication data stream.

To start a slave using GTID-based replication, you do not include MASTER_LOG_FILE or MASTER_LOG_POS options in the CHANGE MASTER TO statement used to direct the slave to replicate from a given master. These options specify the name of the log file and the starting position within the file, but with GTIDs the slave does not need this nonlocal data. Instead, you need to enable the MASTER_AUTO_POSITION option. For full instructions to configure and start masters and slaves using GTID-based replication, see Section 17.1.3.4, “Setting Up Replication Using GTIDs” .

The MASTER_AUTO_POSITION option is disabled by default. If multi-source replication is enabled on the slave, you need to set the option for each applicable replication channel. Disabling the MASTER_AUTO_POSITION option again makes the slave revert to file-based replication, in which case you must also specify one or both of the MASTER_LOG_FILE or MASTER_LOG_POS options.

When a replication slave has GTIDs enabled ( GTID_MODE=ON , ON_PERMISSIVE, or OFF_PERMISSIVE ) and the MASTER_AUTO_POSITION option enabled, auto-positioning is activated for connection to the master. The master must have GTID_MODE=ON set in order for the connection to succeed. In the initial handshake, the slave sends a GTID set containing the transactions that it has already received, committed, or both. This GTID set is equal to the union of the set of GTIDs in the gtid_executed system variable ( @@GLOBAL.gtid_executed ), and the set of GTIDs recorded in the Performance Schema replication_connection_status table as received transactions (the result of the statement SELECT RECEIVED_TRANSACTION_SET FROM PERFORMANCE_SCHEMA.replication_connection_status ).

The master responds by sending all transactions recorded in its binary log whose GTID is not included in the GTID set sent by the slave. This exchange ensures that the master only sends the transactions with a GTID that the slave has not already received or committed. If the slave receives transactions from more than one master, as in the case of a diamond topology, the auto-skip function ensures that the transactions are not applied twice.

If any of the transactions that should be sent by the master have been purged from the master's binary log, or added to the set of GTIDs in the gtid_purged system variable by another method, the master sends the error ER_MASTER_HAS_PURGED_REQUIRED_GTIDS to the slave, and replication does not start. The GTIDs of the missing purged transactions are identified and listed in the master's error log in the warning message ER_FOUND_MISSING_GTIDS . The slave cannot recover automatically from this error because parts of the transaction history that are needed to catch up with the master have been purged. Attempting to reconnect without the MASTER_AUTO_POSITION option enabled only results in the loss of the purged transactions on the slave. The correct approach to recover from this situation is for the slave to replicate the missing transactions listed in the ER_FOUND_MISSING_GTIDS message from another source, or for the slave to be replaced by a new slave created from a more recent backup. Consider revising the binary log expiration period ( binlog_expire_logs_seconds ) on the master to ensure that the situation does not occur again.

If during the exchange of transactions it is found that the slave has received or committed transactions with the master's UUID in the GTID, but the master itself does not have a record of them, the master sends the error ER_SLAVE_HAS_MORE_GTIDS_THAN_MASTER to the slave and replication does not start. This situation can occur if a master that does not have sync_binlog=1 set experiences a power failure or operating system crash, and loses committed transactions that have not yet been synchronized to the binary log file, but have been received by the slave. The master and slave can diverge if any clients commit transactions on the master after it is restarted, which can lead to the situation where the master and slave are using the same GTID for different transactions. The correct approach to recover from this situation is to check manually whether the master and slave have diverged. If the same GTID is now in use for different transactions, you either need to perform manual conflict resolution for individual transactions as required, or remove either the master or the slave from the replication topology. If the issue is only missing transactions on the master, you can make the master into a slave instead, allow it to catch up with the other servers in the replication topology, and then make it a master again if needed.

17.1.3.4 Setting Up Replication Using GTIDs

This section describes a process for configuring and starting GTID-based replication in MySQL 8.0. This is a cold start procedure that assumes either that you are starting the replication master for the first time, or that it is possible to stop it; for information about provisioning replication slaves using GTIDs from a running master, see Section 17.1.3.5, “Using GTIDs for Failover and Scaleout” . For information about changing GTID mode on servers online, see Section 17.1.5, “Changing Replication Modes on Online Servers” .

The key steps in this startup process for the simplest possible GTID replication topology, consisting of one master and one slave, are as follows:

  1. If replication is already running, synchronize both servers by making them read-only.

  2. Stop both servers.

  3. Restart both servers with GTIDs enabled and the correct options configured.

    The mysqld options necessary to start the servers as described are discussed in the example that follows later in this section.

  4. Instruct the slave to use the master as the replication data source and to use auto-positioning. The SQL statements needed to accomplish this step are described in the example that follows later in this section.

  5. Take a new backup. Binary logs containing transactions without GTIDs cannot be used on servers where GTIDs are enabled, so backups taken before this point cannot be used with your new configuration.

  6. Start the slave, then disable read-only mode on both servers, so that they can accept updates.

In the following example, two servers are already running as master and slave, using MySQL's binary log position-based replication protocol. If you are starting with new servers, see Section 17.1.2.3, “Creating a User for Replication” for information about adding a specific user for replication connections and Section 17.1.2.1, “Setting the Replication Master Configuration” for information about setting the server_id variable. The following examples show how to store mysqld startup options in server's option file, see Section 4.2.7, “Using Option Files” for more information. Alternatively you can use startup options when running mysqld .

Most of the steps that follow require the use of the MySQL root account or another MySQL user account that has the SUPER privilege. mysqladmin shutdown requires either the SUPER privilege or the SHUTDOWN privilege.

Step 1: Synchronize the servers. This step is only required when working with servers which are already replicating without using GTIDs. For new servers proceed to Step 3. Make the servers read-only by setting the read_only system variable to ON on each server by issuing the following:

mysql> SET @@GLOBAL.read_only = ON;
                            

Wait for all ongoing transactions to commit or roll back. Then, allow the slave to catch up with the master. It is extremely important that you make sure the slave has processed all updates before continuing .

If you use binary logs for anything other than replication, for example to do point in time backup and restore, wait until you do not need the old binary logs containing transactions without GTIDs. Ideally, wait for the server to purge all binary logs, and wait for any existing backup to expire.

Important

It is important to understand that logs containing transactions without GTIDs cannot be used on servers where GTIDs are enabled. Before proceeding, you must be sure that transactions without GTIDs do not exist anywhere in the topology.

Step 2: Stop both servers. Stop each server using mysqladmin as shown here, where username is the user name for a MySQL user having sufficient privileges to shut down the server:

shell> mysqladmin -uusername -p shutdown
                            

Then supply this user's password at the prompt.

Step 3: Start both servers with GTIDs enabled. To enable GTID-based replication, each server must be started with GTID mode enabled by setting the gtid_mode variable to ON , and with the enforce_gtid_consistency variable enabled to ensure that only statements which are safe for GTID-based replication are logged. For example:

gtid_mode=ON
enforce-gtid-consistency=true
                            

In addition, you should start slaves with the --skip-slave-start option before configuring the slave settings. For more information on GTID related options and variables, see Section 17.1.6.5, “Global Transaction ID Options and Variables” .

It is not mandatory to have binary logging enabled in order to use GTIDs when using the mysql.gtid_executed Table . Masters must always have binary logging enabled in order to be able to replicate. However, slave servers can use GTIDs but without binary logging. If you need to disable binary logging on a slave server, you can do this by specifying the --skip-log-bin and --skip-log-slave-updates options for the slave.

Step 4: Configure the slave to use GTID-based auto-positioning. Tell the slave to use the master with GTID based transactions as the replication data source, and to use GTID-based auto-positioning rather than file-based positioning. Issue a CHANGE MASTER TO statement on the slave, including the MASTER_AUTO_POSITION option in the statement to tell the slave that the master's transactions are identified by GTIDs.

You may also need to supply appropriate values for the master's host name and port number as well as the user name and password for a replication user account which can be used by the slave to connect to the master; if these have already been set prior to Step 1 and no further changes need to be made, the corresponding options can safely be omitted from the statement shown here.

mysql> CHANGE MASTER TO
     >     MASTER_HOST = host,
     >     MASTER_PORT = port,
     >     MASTER_USER = user,
     >     MASTER_PASSWORD = password,
     >     MASTER_AUTO_POSITION = 1;
                            

Neither the MASTER_LOG_FILE option nor the MASTER_LOG_POS option may be used with MASTER_AUTO_POSITION set equal to 1. Attempting to do so causes the CHANGE MASTER TO statement to fail with an error.

Step 5: Take a new backup. Existing backups that were made before you enabled GTIDs can no longer be used on these servers now that you have enabled GTIDs. Take a new backup at this point, so that you are not left without a usable backup.

For instance, you can execute FLUSH LOGS on the server where you are taking backups. Then either explicitly take a backup or wait for the next iteration of any periodic backup routine you may have set up.

Step 6: Start the slave and disable read-only mode. Start the slave like this:

mysql> START SLAVE;
                            

The following step is only necessary if you configured a server to be read-only in Step 1. To allow the server to begin accepting updates again, issue the following statement:

mysql> SET @@GLOBAL.read_only = OFF;
                            

GTID-based replication should now be running, and you can begin (or resume) activity on the master as before. Section 17.1.3.5, “Using GTIDs for Failover and Scaleout” , discusses creation of new slaves when using GTIDs.

17.1.3.5 Using GTIDs for Failover and Scaleout

There are a number of techniques when using MySQL Replication with Global Transaction Identifiers (GTIDs) for provisioning a new slave which can then be used for scaleout, being promoted to master as necessary for failover. This section describes the following techniques:

Global transaction identifiers were added to MySQL Replication for the purpose of simplifying in general management of the replication data flow and of failover activities in particular. Each identifier uniquely identifies a set of binary log events that together make up a transaction. GTIDs play a key role in applying changes to the database: the server automatically skips any transaction having an identifier which the server recognizes as one that it has processed before. This behavior is critical for automatic replication positioning and correct failover.

The mapping between identifiers and sets of events comprising a given transaction is captured in the binary log. This poses some challenges when provisioning a new server with data from another existing server. To reproduce the identifier set on the new server, it is necessary to copy the identifiers from the old server to the new one, and to preserve the relationship between the identifiers and the actual events. This is neccessary for restoring a slave that is immediately available as a candidate to become a new master on failover or switchover.

Simple replication. The easiest way to reproduce all identifiers and transactions on a new server is to make the new server into the slave of a master that has the entire execution history, and enable global transaction identifiers on both servers. See Section 17.1.3.4, “Setting Up Replication Using GTIDs” , for more information.

Once replication is started, the new server copies the entire binary log from the master and thus obtains all information about all GTIDs.

This method is simple and effective, but requires the slave to read the binary log from the master; it can sometimes take a comparatively long time for the new slave to catch up with the master, so this method is not suitable for fast failover or restoring from backup. This section explains how to avoid fetching all of the execution history from the master by copying binary log files to the new server.

Copying data and transactions to the slave. Executing the entire transaction history can be time-consuming when the source server has processed a large number of transactions previously, and this can represent a major bottleneck when setting up a new replication slave. To eliminate this requirement, a snapshot of the data set, the binary logs and the global transaction information the source server contains can be imported to the new slave. The source server can be either the master or the slave, but you must ensure that the source has processed all required transactions before copying the data.

There are several variants of this method, the difference being in the manner in which data dumps and transactions from binary logs are transfered to the slave, as outlined here:

Data Set
  1. Create a dump file using mysqldump on the source server. Set the mysqldump option --master-data (with the default value of 1) to include a CHANGE MASTER TO statement with binary logging information. Set the --set-gtid-purged option to AUTO (the default) or ON , to include information about executed transactions in the dump. Then use the mysql client to import the dump file on the target server.

  2. Alternatively, create a data snapshot of the source server using raw data files, then copy these files to the target server, following the instructions in Section 17.1.2.5, “Choosing a Method for Data Snapshots” . If you use InnoDB tables, you can use the mysqlbackup command from the MySQL Enterprise Backup component to produce a consistent snapshot. This command records the log name and offset corresponding to the snapshot to be used on the slave. MySQL Enterprise Backup is a commercial product that is included as part of a MySQL Enterprise subscription. See Section 30.2, “MySQL Enterprise Backup Overview” for detailed information.

  3. Alternatively, stop both the source and target servers, copy the contents of the source's data directory to the new slave's data directory, then restart the slave. If you use this method, the slave must be configured for GTID-based replication, in other words with gtid_mode=ON . For instructions and important information for this method, see Section 17.1.2.8, “Adding Slaves to a Replication Environment” .

Transaction History

If the source server has a complete transaction history in its binary logs (that is, the GTID set @@GLOBAL.gtid_purged is empty), you can use these methods.

  1. Import the binary logs from the source server to the new slave using mysqlbinlog , with the --read-from-remote-server and --read-from-remote-master options.

  2. Alternatively, copy the source server's binary log files to the slave. You can make copies from the slave using mysqlbinlog with the --read-from-remote-server and --raw options. These can be read into the slave by using mysqlbinlog > file (without the --raw option) to export the binary log files to SQL files, then passing these files to the mysql client for processing. Ensure that all of the binary log files are processed using a single mysql process, rather than multiple connections. For example:

    shell> mysqlbinlog copied-binlog.000001 copied-binlog.000002 | mysql -u root -p
                                                        

    更多信息,请见 Section 4.6.8.3, “Using mysqlbinlog to Back Up Binary Log Files” .

This method has the advantage that a new server is available almost immediately; only those transactions that were committed while the snapshot or dump file was being replayed still need to be obtained from the existing master. This means that the slave's availability is not instantanteous, but only a relatively short amount of time should be required for the slave to catch up with these few remaining transactions.

Copying over binary logs to the target server in advance is usually faster than reading the entire transaction execution history from the master in real time. However, it may not always be feasible to move these files to the target when required, due to size or other considerations. The two remaining methods for provisioning a new slave discussed in this section use other means to transfer information about transactions to the new slave.

Injecting empty transactions. The master's global gtid_executed variable contains the set of all transactions executed on the master. Rather than copy the binary logs when taking a snapshot to provision a new server, you can instead note the content of gtid_executed on the server from which the snapshot was taken. Before adding the new server to the replication chain, simply commit an empty transaction on the new server for each transaction identifier contained in the master's gtid_executed , like this:

SET GTID_NEXT='aaa-bbb-ccc-ddd:N';
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';
                            

Once all transaction identifiers have been reinstated in this way using empty transactions, you must flush and purge the slave's binary logs, as shown here, where N is the nonzero suffix of the current binary log file name:

FLUSH LOGS;
PURGE BINARY LOGS TO 'master-bin.00000N';
                            

You should do this to prevent this server from flooding the replication stream with false transactions in the event that it is later promoted to master. (The FLUSH LOGS statement forces the creation of a new binary log file; PURGE BINARY LOGS purges the empty transactions, but retains their identifiers.)

This method creates a server that is essentially a snapshot, but in time is able to become a master as its binary log history converges with that of the replication stream (that is, as it catches up with the master or masters). This outcome is similar in effect to that obtained using the remaining provisioning method, which we discuss in the next few paragraphs.

Excluding transactions with gtid_purged. The master's global gtid_purged variable contains the set of all transactions that have been purged from the master's binary log. As with the method discussed previously (see Injecting empty transactions ), you can record the value of gtid_executed on the server from which the snapshot was taken (in place of copying the binary logs to the new server). Unlike the previous method, there is no need to commit empty transactions (or to issue PURGE BINARY LOGS ); instead, you can set gtid_purged on the slave directly, based on the value of gtid_executed on the server from which the backup or snapshot was taken.

As with the method using empty transactions, this method creates a server that is functionally a snapshot, but in time is able to become a master as its binary log history converges with that of the replication master or group.

Restoring GTID mode slaves. When restoring a slave in a GTID based replication setup that has encountered an error, injecting an empty transaction may not solve the problem because an event does not have a GTID.

Use mysqlbinlog to find the next transaction, which is probably the first transaction in the next log file after the event. Copy everything up to the COMMIT for that transaction, being sure to include the SET @@SESSION.GTID_NEXT . Even if you are not using row-based replication, you can still run binary log row events in the command line client.

Stop the slave and run the transaction you copied. The mysqlbinlog output sets the delimiter to /*!*/; , so set it back:

mysql> DELIMITER ;
                            

Restart replication from the correct position automatically:

mysql> SET GTID_NEXT=automatic;
mysql> RESET SLAVE;
mysql> START SLAVE;
                            

17.1.3.6 Restrictions on Replication with GTIDs

Because GTID-based replication is dependent on transactions, some features otherwise available in MySQL are not supported when using it. This section provides information about restrictions on and limitations of replication with GTIDs.

Updates involving nontransactional storage engines. When using GTIDs, updates to tables using nontransactional storage engines such as MyISAM cannot be made in the same statement or transaction as updates to tables using transactional storage engines such as InnoDB .

This restriction is due to the fact that updates to tables that use a nontransactional storage engine mixed with updates to tables that use a transactional storage engine within the same transaction can result in multiple GTIDs being assigned to the same transaction.

Such problems can also occur when the master and the slave use different storage engines for their respective versions of the same table, where one storage engine is transactional and the other is not. Also be aware that triggers that are defined to operate on nontransactional tables can be the cause of these problems.

In any of the cases just mentioned, the one-to-one correspondence between transactions and GTIDs is broken, with the result that GTID-based replication cannot function correctly.

CREATE TABLE ... SELECT statements. CREATE TABLE ... SELECT statements are not allowed when using GTID-based replication. When binlog_format is set to STATEMENT, a CREATE TABLE ... SELECT statement is recorded in the binary log as one transaction with one GTID, but if ROW format is used, the statement is recorded as two transactions with two GTIDs. If a master used STATEMENT format and a slave used ROW format, the slave would be unable to handle the transaction correctly, therefore the CREATE TABLE ... SELECT statement is disallowed with GTIDs to prevent this scenario.

Temporary tables. When binlog_format is set to STATEMENT , CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements cannot be used inside transactions, procedures, functions, and triggers when GTIDs are in use on the server (that is, when the enforce_gtid_consistency system variable is set to ON ). They can be used outside these contexts when GTIDs are in use, provided that autocommit=1 is set. From MySQL 8.0.13, when binlog_format is set to ROW or MIXED , CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are allowed inside a transaction, procedure, function, or trigger when GTIDs are in use. The statements are not written to the binary log and are therefore not replicated to slaves. The use of row-based replication means that the slaves remain in sync without the need to replicate temporary tables. If the removal of these statements from a transaction results in an empty transaction, the transaction is not written to the binary log.

Preventing execution of unsupported statements. To prevent execution of statements that would cause GTID-based replication to fail, all servers must be started with the --enforce-gtid-consistency option when enabling GTIDs. This causes statements of any of the types discussed previously in this section to fail with an error.

Note that --enforce-gtid-consistency only takes effect if binary logging takes place for a statement. If binary logging is disabled on the server, or if statements are not written to the binary log because they are removed by a filter, GTID consistency is not checked or enforced for the statements that are not logged.

For information about other required startup options when enabling GTIDs, see Section 17.1.3.4, “Setting Up Replication Using GTIDs” .

Skipping transactions. sql_slave_skip_counter is not supported when using GTIDs. If you need to skip transactions, use the value of the master's gtid_executed variable instead; see Injecting empty transactions , for more information.

Ignoring servers. The IGNORE_SERVER_IDS option of the CHANGE MASTER TO statement is deprecated when using GTIDs, because transactions that have already been applied are automatically ignored. Before starting GTID-based replication, check for and clear all ignored server ID lists that have previously been set on the servers involved. The SHOW SLAVE STATUS statement, which can be issued for individual channels, displays the list of ignored server IDs if there is one. If there is no list, the Replicate_Ignore_Server_Ids field is blank.

GTID mode and mysqldump. It is possible to import a dump made using mysqldump into a MySQL server running with GTID mode enabled, provided that there are no GTIDs in the target server's binary log.

GTID mode and mysql_upgrade. When the server is running with global transaction identifiers (GTIDs) enabled ( gtid_mode=ON ), do not enable binary logging by mysql_upgrade (the --write-binlog option).

17.1.3.7 Stored Function Examples to Manipulate GTIDs

MySQL includes some built-in (native) functions for use with GTID-based replication. These functions are as follows:

GTID_SUBSET( set1 , set2 )

Given two sets of global transaction identifiers set1 and set2 , returns true if all GTIDs in set1 are also in set2 . Returns false otherwise.

GTID_SUBTRACT( set1 , set2 )

Given two sets of global transaction identifiers set1 and set2 , returns only those GTIDs from set1 that are not in set2 .

WAIT_FOR_EXECUTED_GTID_SET( gtid_set [, timeout ])

Wait until the server has applied all of the transactions whose global transaction identifiers are contained in gtid_set . The optional timeout stops the function from waiting after the specified number of seconds have elapsed.

WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS( gtid_set [, timeout ][, channel ])

Like WAIT_FOR_EXECUTED_GTID_SET(), but for a single started replication channel. Use WAIT_FOR_EXECUTED_GTID_SET() instead to ensure all channels are covered in all states.

For details of these functions, see Section 12.18, “Functions Used with Global Transaction Identifiers (GTIDs)” .

You can define your own stored functions to work with GTIDs. For information on defining stored functions, see Chapter 24, Stored Programs and Views . The following examples show some useful stored functions that can be created based on the built-in GTID_SUBSET() and GTID_SUBTRACT() functions.

Note that in these stored functions, the delimiter command has been used to change the MySQL statement delimiter to a vertical bar, as follows:

mysql> delimiter |
                            

All of these functions take string representations of GTID sets as arguments, so GTID sets must always be quoted when used with them.

This function returns nonzero (true) if two GTID sets are the same set, even if they are not formatted in the same way.

CREATE FUNCTION GTID_IS_EQUAL(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT)
RETURNS INT
  RETURN GTID_SUBSET(gtid_set_1, gtid_set_2) AND GTID_SUBSET(gtid_set_2, gtid_set_1)|
                            

This function returns nonzero (true) if two GTID sets are disjoint.

CREATE FUNCTION GTID_IS_DISJOINT(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT)
RETURNS INT
  RETURN GTID_SUBSET(gtid_set_1, GTID_SUBTRACT(gtid_set_1, gtid_set_2))|
                            

This function returns nonzero (true) if two GTID sets are disjoint, and sum is the union of the two sets.

CREATE FUNCTION GTID_IS_DISJOINT_UNION(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT, sum LONGTEXT)
RETURNS INT
  RETURN GTID_IS_EQUAL(GTID_SUBTRACT(sum, gtid_set_1), gtid_set_2) AND
         GTID_IS_EQUAL(GTID_SUBTRACT(sum, gtid_set_2), gtid_set_1)|
                            

This function returns a normalized form of the GTID set, in all uppercase, with no whitespace and no duplicates. The UUIDs are arranged in alphabetic order and intervals are arranged in numeric order.

CREATE FUNCTION GTID_NORMALIZE(g LONGTEXT)
RETURNS LONGTEXT
RETURN GTID_SUBTRACT(g, '')|
                            

This function returns the union of two GTID sets.

CREATE FUNCTION GTID_UNION(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT)
RETURNS LONGTEXT
  RETURN GTID_NORMALIZE(CONCAT(gtid_set_1, ',', gtid_set_2))|
                            

This function returns the intersection of two GTID sets.

CREATE FUNCTION GTID_INTERSECTION(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT)
RETURNS LONGTEXT
  RETURN GTID_SUBTRACT(gtid_set_1, GTID_SUBTRACT(gtid_set_1, gtid_set_2))|
                            

This function returns the symmetric difference between two GTID sets, that is, the GTIDs that exist in gtid_set_1 but not in gtid_set_2 , and also the GTIDs that exist in gtid_set_2 but not in gtid_set_1 .

CREATE FUNCTION GTID_SYMMETRIC_DIFFERENCE(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT)
RETURNS LONGTEXT
  RETURN GTID_SUBTRACT(CONCAT(gtid_set_1, ',', gtid_set_2), GTID_INTERSECTION(gtid_set_1, gtid_set_2))|
                            

This function removes from a GTID set all the GTIDs from a specified origin, and returns the remaining GTIDs, if any. The UUID is the identifier used by the server where the transaction originated, which is normally the server_uuid value.

CREATE FUNCTION GTID_SUBTRACT_UUID(gtid_set LONGTEXT, uuid TEXT)
RETURNS LONGTEXT
  RETURN GTID_SUBTRACT(gtid_set, CONCAT(UUID, ':1-', (1 << 63) - 2))|
                            

This function reverses the previously listed function to return only those GTIDs from the GTID set that originate from the server with the specified identifier (UUID).

CREATE FUNCTION GTID_INTERSECTION_WITH_UUID(gtid_set LONGTEXT, uuid TEXT)
RETURNS LONGTEXT
  RETURN GTID_SUBTRACT(gtid_set, GTID_SUBTRACT_UUID(gtid_set, uuid))|
                            

Example 17.1 Verifying that a replication slave is up to date

The built-in functions GTID_SUBSET and GTID_SUBTRACT can be used to check that a replication slave has applied at least every transaction that a master has applied.

To perform this check with GTID_SUBSET , execute the following statement on the slave:

SELECT GTID_SUBSET(master_gtid_executed, slave_gtid_executed)
                                    

If this returns 0 (false), some GTIDs in master_gtid_executed are not present in slave_gtid_executed , so the master has applied some transactions that the slave has not applied, and the slave is therefore not up to date.

To perform the check with GTID_SUBTRACT , execute the following statement on the slave:

SELECT GTID_SUBTRACT(master_gtid_executed, slave_gtid_executed)
                                    

This statement returns any GTIDs that are in master_gtid_executed but not in slave_gtid_executed . If any GTIDs are returned, the master has applied some transactions that the slave has not applied, and the slave is therefore not up to date.


Example 17.2 Backup and restore scenario

The stored functions GTID_IS_EQUAL , GTID_IS_DISJOINT , and GTID_IS_DISJOINT_UNION could be used to verify backup and restore operations involving multiple databases and servers. In this example scenario, server1 contains database db1 , and server2 contains database db2 . The goal is to copy database db2 to server1 , and the result on server1 should be the union of the two databases. The procedure used is to back up server2 using mysqlpump or mysqldump , then restore this backup on server1 .

Provided the backup program's option --set-gtid-purged was set to ON or the default of AUTO , the program's output contains a SET @@GLOBAL.gtid_purged statement that will add the gtid_executed set from server2 to the gtid_purged set on server1 . The gtid_purged set contains the GTIDs of all the transactions that have been committed on a server but do not exist in any binary log file on the server. When database db2 is copied to server1 , the GTIDs of the transactions committed on server2 , which are not in the binary log files on server1 , must be added to server1 's gtid_purged set to make the set complete.

The stored functions can be used to assist with the following steps in this scenario:

  • Use GTID_IS_EQUAL to verify that the backup operation computed the correct GTID set for the SET @@GLOBAL.gtid_purged statement. On server2 , extract that statement from the mysqlpump or mysqldump output, and store the GTID set into a local variable, such as $gtid_purged_set . Then execute the following statement:

    server2> SELECT GTID_IS_EQUAL($gtid_purged_set, @@GLOBAL.gtid_executed);
                                                    

    If the result is 1, the two GTID sets are equal, and the set has been computed correctly.

  • Use GTID_IS_DISJOINT to verify that the GTID set in the mysqlpump or mysqldump output does not overlap with the gtid_executed set on server1 . If there is any overlap, with identical GTIDs present on both servers for some reason, you will see errors when copying database db2 to server1 . To check, on server1 , extract and store the gtid_purged set from the output into a local variable as above, then execute the following statement:

    server1> SELECT GTID_IS_DISJOINT($gtid_purged_set, @@GLOBAL.gtid_executed);
                                                    

    If the result is 1, there is no overlap between the two GTID sets, so no duplicate GTIDs are present.

  • Use GTID_IS_DISJOINT_UNION to verify that the restore operation resulted in the correct GTID state on server1 . Before restoring the backup, on server1 , obtain the existing gtid_executed set by executing the following statement:

    server1> SELECT @@GLOBAL.gtid_executed;
                                                    

    Store the result in a local variable $original_gtid_executed . Also store the gtid_purged set in a local variable as described above. When the backup from server2 has been restored onto server1 , execute the following statement to verify the GTID state:

    server1> SELECT GTID_IS_DISJOINT_UNION($original_gtid_executed,
                                           $gtid_purged_set,
                                           @@GLOBAL.gtid_executed);
                                                    

    If the result is 1, the stored function has verified that the original gtid_executed set from server1 ( $original_gtid_executed ) and the gtid_purged set that was added from server2 ( $gtid_purged_set ) have no overlap, and also that the updated gtid_executed set on server1 now consists of the previous gtid_executed set from server1 plus the gtid_purged set from server2 , which is the desired result. Ensure that this check is carried out before any further transactions take place on server1 , otherwise the new transactions in the gtid_executed set will cause it to fail.


Example 17.3 Selecting the most up-to-date slave for manual failover

The stored function GTID_UNION could be used to identify the most up-to-date replication slave from a set of slaves, in order to perform a manual failover operation after a replication master has stopped unexpectedly. If some of the slaves are experiencing replication lag, this stored function can be used to compute the most up-to-date slave without waiting for all the slaves to apply their existing relay logs, and therefore to minimize the failover time. The function can return the union of the gtid_executed set on each slave with the set of transactions received by the slave, which is recorded in the Performance Schema table replication_connection_status . You can compare these results to find which slave's record of transactions is the most up-to-date, even if not all of the transactions have been committed yet.

On each replication slave, compute the complete record of transactions by issuing the following statement:

SELECT GTID_UNION(RECEIVED_TRANSACTION_SET, @@GLOBAL.gtid_executed)
    FROM performance_schema.replication_connection_status
    WHERE channel_name = 'name';
                                    

You can then compare the results from each slave to see which one has the most up-to-date record of transactions, and use this slave as the new replication master.


Example 17.4 Checking for extraneous transactions on a replication slave

The stored function GTID_SUBTRACT_UUID could be used to check whether a replication slave has received transactions that did not originate from its designated master or masters. If it has, there might be an issue with your replication setup, or with a proxy, router, or load balancer. This function works by removing from a GTID set all the GTIDs from a specified originating server, and returning the remaining GTIDs, if any.

For a replication slave with a single master, issue the following statement, giving the identifier of the originating replication master, which is normally the server_uuid value:

SELECT GTID_SUBTRACT_UUID(@@GLOBAL.gtid_executed, server_uuid_of_master);
                                    

If the result is not empty, the transactions returned are extra transactions that did not originate from the designated master.

For a slave in a multi-master replication topology, repeat the function, for example:

SELECT GTID_SUBTRACT_UUID(GTID_SUBTRACT_UUID(@@GLOBAL.gtid_executed,
                                             server_uuid_of_master_1),
                                             server_uuid_of_master_2);
                                    

If the result is not empty, the transactions returned are extra transactions that did not originate from any of the designated masters.


Example 17.5 Verifying that a server in a replication topology is read-only

The stored function GTID_INTERSECTION_WITH_UUID could be used to verify that a server has not originated any GTIDs and is in a read-only state. The function returns only those GTIDs from the GTID set that originate from the server with the specified identifier. If any of the transactions in the server's gtid_executed set have the server's own identifier, the server itself originated those transactions. You can issue the following statement on the server to check:

SELECT GTID_INTERSECTION_WITH_UUID(@@GLOBAL.gtid_executed, my_server_uuid);
                                    


Example 17.6 Validating an additional slave in a multi-master replication setup

The stored function GTID_INTERSECTION_WITH_UUID could be used to find out if a slave attached to a multi-master replication setup has applied all the transactions originating from one particular master. In this scenario, master1 and master2 are both masters and slaves and replicate to each other. master2 also has its own replication slave. The replication slave will also receive and apply master1 's transactions if master2 is configured with log_slave_updates=ON , but it will not do so if master2 uses log_slave_updates=OFF . Whatever the case, we currently only want to find out if the replication slave is up to date with master2 . In this situation, the stored function GTID_INTERSECTION_WITH_UUID can be used to identify the transactions that master2 originated, discarding the transactions that master2 has replicated from master1 . The built-in function GTID_SUBSET can then be used to compare the result to the gtid_executed set on the slave. If the slave is up to date with master2 , the gtid_executed set on the slave contains all the transactions in the intersection set (the transactions that originated from master2 ).

To carry out this check, store master2 's gtid_executed set, master2 's server UUID, and the slave's gtid_executed set, into client-side variables as follows:

    $master2_gtid_executed :=
      master2> SELECT @@GLOBAL.gtid_executed;
    $master2_server_uuid :=
      master2> SELECT @@GLOBAL.server_uuid;
    $slave_gtid_executed :=
      slave> SELECT @@GLOBAL.gtid_executed;
                                    

Then use GTID_INTERSECTION_WITH_UUID and GTID_SUBSET with these variables as input, as follows:

SELECT GTID_SUBSET(GTID_INTERSECTION_WITH_UUID($master2_gtid_executed,
                                               $master2_server_uuid),
                                               $slave_gtid_executed);
                                    

The server identifier from master2 ( $master2_server_uuid ) is used with GTID_INTERSECTION_WITH_UUID to identify and return only those GTIDs from master2 's gtid_executed set that originated on master2 , omitting those that originated on master1 . The resulting GTID set is then compared with the set of all executed GTIDs on the slave, using GTID_SUBSET . If this statement returns nonzero (true), all the identified GTIDs from master2 (the first set input) are also in the slave's gtid_executed set (the second set input), meaning that the slave has replicated all the transactions that originated from master2 .


17.1.4 MySQL Multi-Source Replication

This section describes MySQL Multi-Source Replication, which enables you to replicate from multiple immediate masters in parallel. This section describes multi-source replication, and how to configure, monitor and troubleshoot it.

17.1.4.1 MySQL Multi-Source Replication Overview

MySQL Multi-Source Replication enables a replication slave to receive transactions from multiple sources simultaneously. Multi-source replication can be used to back up multiple servers to a single server, to merge table shards, and consolidate data from multiple servers to a single server. Multi-source replication does not implement any conflict detection or resolution when applying the transactions, and those tasks are left to the application if required. In a multi-source replication topology, a slave creates a replication channel for each master that it should receive transactions from. See Section 17.2.3, “Replication Channels” . The following sections describe how to set up multi-source replication.

17.1.4.2 Multi-Source Replication Tutorials

This section provides tutorials on how to configure masters and slaves for multi-source replication, and how to start, stop and reset multi-source slaves.

17.1.4.2.1 Configuring Multi-Source Replication

This section explains how to configure a multi-source replication topology, and provides details about configuring masters and slaves. Such a topology requires at least two masters and one slave configured.

Masters in a multi-source replication topology can be configured to use either global transaction identifier (GTID) based replication, or binary log position-based replication. See Section 17.1.3.4, “Setting Up Replication Using GTIDs” for how to configure a master using GTID based replication. See Section 17.1.2.1, “Setting the Replication Master Configuration” for how to configure a master using file position based replication.

Slaves in a multi-source replication topology require TABLE repositories for the master info log and relay log info log, which are the default in MySQL 8.0. Multi-source replication is not compatible with FILE based repositories, and the FILE setting for the --master-info-repository and --relay-log-info-repository options is now deprecated.

To modify an existing replication slave that is using a FILE repository for the slave status logs to use TABLE repositories, convert the existing replication repositories dynamically by running the following commands:

STOP SLAVE;
SET GLOBAL master_info_repository = 'TABLE';
SET GLOBAL relay_log_info_repository = 'TABLE';
                                
17.1.4.2.2 Adding a GTID Based Master to a Multi-Source Replication Slave

This section assumes you have enabled GTID based transactions on the master using gtid_mode=ON , enabled a replication user, and ensured that the slave is using TABLE based replication repositories. Use the CHANGE MASTER TO statement to add a new master to a channel by using a FOR CHANNEL channel clause. For more information on replication channels, see Section 17.2.3, “Replication Channels”

For example, to add a new master with the host name master1 using port 3451 to a channel called master-1 :


CHANGE MASTER TO MASTER_HOST='master1', MASTER_USER='rpl', MASTER_PORT=3451, MASTER_PASSWORD='', \
MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master-1';

                                

Multi-source replication is compatible with auto-positioning. See Section 13.4.2.1, “CHANGE MASTER TO Syntax” for more information.

Repeat this process for each extra master that you want to add to a channel, changing the host name, port and channel as appropriate.

17.1.4.2.3 Adding a Binary Log Based Master to a Multi-Source Replication Slave

This section assumes that binary logging is enabled on the master (which is the default), the slave is using TABLE based replication repositories (which is the default in MySQL 8.0), and that you have enabled a replication user and noted the current binary log position. You need to know the current MASTER_LOG_FILE and MASTER_LOG_POSITION . Use the CHANGE MASTER TO statement to add a new master to a channel by specifying a FOR CHANNEL channel clause. For example, to add a new master with the host name master1 using port 3451 to a channel called master-1 :


CHANGE MASTER TO MASTER_HOST='master1', MASTER_USER='rpl', MASTER_PORT=3451, MASTER_PASSWORD='' \
MASTER_LOG_FILE='master1-bin.000006', MASTER_LOG_POS=628 FOR CHANNEL 'master-1';

                                

Repeat this process for each extra master that you want to add to a channel, changing the host name, port and channel as appropriate.

17.1.4.2.4 Starting Multi-Source Replication Slaves

Once you have added all of the channels you want to use as replication masters, use a START SLAVE thread_types statement to start replication. When you have enabled multiple channels on a slave, you can choose to either start all channels, or select a specific channel to start.

  • To start all currently configured replication channels:

     
    START SLAVE thread_types;
                                                
  • To start only a named channel, use a FOR CHANNEL channel clause:

     
    START SLAVE thread_types FOR CHANNEL channel;
    
                                                

Use the thread_types option to choose specific threads you want the above statements to start on the slave. See Section 13.4.2.6, “START SLAVE Syntax” for more information.

17.1.4.2.5 Stopping Multi-Source Replication Slaves

The STOP SLAVE statement can be used to stop a multi-source replication slave. By default, if you use the STOP SLAVE statement on a multi-source replication slave all channels are stopped. Optionally, use the FOR CHANNEL channel clause to stop only a specific channel.

  • To stop all currently configured replication channels:

    
    STOP SLAVE thread_types;
                                                
  • To stop only a named channel, use a FOR CHANNEL channel clause:

     
    STOP SLAVE thread_types FOR CHANNEL channel;
    
                                                

Use the thread_types option to choose specific threads you want the above statements to stop on the slave. See Section 13.4.2.7, “STOP SLAVE Syntax” for more information.

17.1.4.2.6 Resetting Multi-Source Replication Slaves

The RESET SLAVE statement can be used to reset a multi-source replication slave. By default, if you use the RESET SLAVE statement on a multi-source replication slave all channels are reset. Optionally, use the FOR CHANNEL channel clause to reset only a specific channel.

  • To reset all currently configured replication channels:

     
    RESET SLAVE;
                                                
  • To reset only a named channel, use a FOR CHANNEL channel clause:

     
    RESET SLAVE FOR CHANNEL channel;
    
                                                

See Section 13.4.2.4, “RESET SLAVE Syntax” for more information.

17.1.4.3 Multi-Source Replication Monitoring

To monitor the status of replication channels the following options exist:

  • Using the replication Performance Schema tables. The first column of these tables is Channel_Name . This enables you to write complex queries based on Channel_Name as a key. See Section 26.12.11, “Performance Schema Replication Tables” .

  • Using SHOW SLAVE STATUS FOR CHANNEL channel . By default, if the FOR CHANNEL channel clause is not used, this statement shows the slave status for all channels with one row per channel. The identifier Channel_name is added as a column in the result set. If a FOR CHANNEL channel clause is provided, the results show the status of only the named replication channel.

Note

The SHOW VARIABLES statement does not work with multiple replication channels. The information that was available through these variables has been migrated to the replication performance tables. Using a SHOW VARIABLES statement in a topology with multiple channels shows the status of only the default channel.

17.1.4.3.1 Monitoring Channels Using Performance Schema Tables

This section explains how to use the replication Performance Schema tables to monitor channels. You can choose to monitor all channels, or a subset of the existing channels.

To monitor the connection status of all channels:

mysql> SELECT * FROM replication_connection_status\G;
*************************** 1. row ***************************
CHANNEL_NAME: master1
GROUP_NAME:
SOURCE_UUID: 046e41f8-a223-11e4-a975-0811960cc264
THREAD_ID: 24
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
RECEIVED_TRANSACTION_SET: 046e41f8-a223-11e4-a975-0811960cc264:4-37
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: master2
GROUP_NAME:
SOURCE_UUID: 7475e474-a223-11e4-a978-0811960cc264
THREAD_ID: 26
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
RECEIVED_TRANSACTION_SET: 7475e474-a223-11e4-a978-0811960cc264:4-6
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
2 rows in set (0.00 sec)
                                

In the above output there are two channels enabled, and as shown by the CHANNEL_NAME field they are called master1 and master2 .

The addition of the CHANNEL_NAME field enables you to query the Performance Schema tables for a specific channel. To monitor the connection status of a named channel, use a WHERE CHANNEL_NAME= channel clause:

mysql> SELECT * FROM replication_connection_status WHERE CHANNEL_NAME='master1'\G
*************************** 1. row ***************************
CHANNEL_NAME: master1
GROUP_NAME:
SOURCE_UUID: 046e41f8-a223-11e4-a975-0811960cc264
THREAD_ID: 24
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
RECEIVED_TRANSACTION_SET: 046e41f8-a223-11e4-a975-0811960cc264:4-37
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)
                                

Similarly, the WHERE CHANNEL_NAME= channel clause can be used to monitor the other replication Performance Schema tables for a specific channel. For more information, see Section 26.12.11, “Performance Schema Replication Tables” .

17.1.4.4 Multi-Source Replication Error Messages

Error codes and messages provide information about errors encountered in a multi-source replication topology. These error codes and messages are only emitted when multi-source replication is enabled, and provide information related to the channel which generated the error. For example:

Slave is already running and Slave is already stopped have been replaced with Replication thread(s) for channel channel_name are already running and Replication threads(s) for channel channel_name are already stopped respectively.

The server log messages have also been changed to indicate which channel the log messages relate to. This makes debugging and tracing easier.

17.1.5 Changing Replication Modes on Online Servers

This section describes how to change the mode of replication being used without having to take the server offline.

17.1.5.1 Replication Mode Concepts

To be able to safely configure the replication mode of an online server it is important to understand some key concepts of replication. This section explains these concepts and is essential reading before attempting to modify the replication mode of an online server.

The modes of replication available in MySQL rely on different techniques for identifying transactions which are logged. The types of transactions used by replication are as follows:

  • GTID transactions are identified by a global transaction identifier (GTID) in the form UUID:NUMBER . Every GTID transaction in a log is always preceded by a Gtid_log_event . GTID transactions can be addressed using either the GTID or using the file name and position.

  • Anonymous transactions do not have a GTID assigned, and MySQL ensures that every anonymous transaction in a log is preceded by an Anonymous_gtid_log_event . In previous versions, anonymous transactions were not preceded by any particular event. Anonymous transactions can only be addressed using file name and position.

When using GTIDs you can take advantage of auto-positioning and automatic fail-over, as well as use WAIT_FOR_EXECUTED_GTID_SET() , session_track_gtids , and monitor replicated transactions using Performance Schema tables. With GTIDs enabled you cannot use sql_slave_skip_counter , instead use empty transactions.

Transactions in a relay log that was received from a master running a previous version of MySQL may not be preceded by any particular event at all, but after being replayed and logged in the slave's binary log, they are preceded with an Anonymous_gtid_log_event .

The ability to configure the replication mode online means that the gtid_mode and enforce_gtid_consistency variables are now both dynamic and can be set from a top-level statement by an account that has privileges sufficient to set global system variables. See Section 5.1.9.1, “System Variable Privileges” . In MySQL 5.6 and earlier, both of these variables could only be configured using the appropriate option at server start, meaning that changes to the replication mode required a server restart. In all versions gtid_mode could be set to ON or OFF , which corresponded to whether GTIDs were used to identify transactions or not. When gtid_mode=ON it is not possible to replicate anonymous transactions, and when gtid_mode=OFF only anonymous transactions can be replicated. When gtid_mode=OFF_PERMISSIVE then new transactions are anonymous while permitting replicated transactions to be either GTID or anonymous transactions. When gtid_mode=ON_PERMISSIVE then new transactions use GTIDs while permitting replicated transactions to be either GTID or anonymous transactions. This means it is possible to have a replication topology that has servers using both anonymous and GTID transactions. For example a master with gtid_mode=ON could be replicating to a slave with gtid_mode=ON_PERMISSIVE . The valid values for gtid_mode are as follows and in this order:

  • OFF

  • OFF_PERMISSIVE

  • ON_PERMISSIVE

  • ON

It is important to note that the state of gtid_mode can only be changed by one step at a time based on the above order. For example, if gtid_mode is currently set to OFF_PERMISSIVE , it is possible to change to OFF or ON_PERMISSIVE but not to ON . This is to ensure that the process of changing from anonymous transactions to GTID transactions online is correctly handled by the server. When you switch between gtid_mode=ON and gtid_mode=OFF , the GTID state (in other words the value of gtid_executed ) is persistent. This ensures that the GTID set that has been applied by the server is always retained, regardless of changes between types of gtid_mode .

The fields related to GTIDs display the correct information regardless of the currently selected gtid_mode . This means that fields which display GTID sets, such as gtid_executed , gtid_purged , RECEIVED_TRANSACTION_SET in the replication_connection_status Performance Schema table, and the GTID related results of SHOW SLAVE STATUS , now return the empty string when there are no GTIDs present. Fields that display a single GTID, such as CURRENT_TRANSACTION in the Performance Schema replication_applier_status_by_worker table, now display ANONYMOUS when GTID transactions are not being used.

Replication from a master using gtid_mode=ON provides the ability to use auto-positioning, configured using the CHANGE MASTER TO MASTER_AUTO_POSITION = 1; statement. The replication topology being used impacts on whether it is possible to enable auto-positioning or not, as this feature relies on GTIDs and is not compatible with anonymous transactions. An error is generated if auto-positioning is enabled and an anonymous transaction is encountered. It is strongly recommended to ensure there are no anonymous transactions remaining in the topology before enabling auto-positioning, see Section 17.1.5.2, “Enabling GTID Transactions Online” .

The valid combinations of gtid_mode and auto-positioning on master and slave are shown in the following table, where the master's gtid_mode is shown on the horizontal and the slave's gtid_mode is on the vertical. The meaning of each entry is as follows:

  • Y : the gtid_mode of master and slave is compatible

  • N : the gtid_mode of master and slave is not compatible

  • * : auto-positioning can be used with this combination

Table 17.1 Valid Combinations of Master and Slave gtid_mode

gtid_mode

Master OFF

Master OFF_PERMISSIVE

Master ON_PERMISSIVE

Master ON

Slave OFF

Y

Y

N

N

Slave OFF_PERMISSIVE

Y

Y

Y

Y*

Slave ON_PERMISSIVE

Y

Y

Y

Y*

Slave ON

N

N

Y

Y*


The currently selected gtid_mode also impacts on the gtid_next variable. The following table shows the behavior of the server for the different values of gtid_mode and gtid_next . The meaning of each entry is as follows:

  • ANONYMOUS : generate an anonymous transaction.

  • Error : generate an error and fail to execute SET GTID_NEXT .

  • UUID:NUMBER : generate a GTID with the specified UUID:NUMBER.

  • New GTID : generate a GTID with an automatically generated number.

Table 17.2 Valid Combinations of gtid_mode and gtid_next

gtid_next AUTOMATIC

binary log on

gtid_next AUTOMATIC

binary log off

gtid_next ANONYMOUS

gtid_next UUID:NUMBER

gtid_mode OFF

ANONYMOUS

ANONYMOUS

ANONYMOUS

Error

gtid_mode OFF_PERMISSIVE

ANONYMOUS

ANONYMOUS

ANONYMOUS

UUID:NUMBER

gtid_mode ON_PERMISSIVE

New GTID

ANONYMOUS

ANONYMOUS

UUID:NUMBER

gtid_mode ON

New GTID

ANONYMOUS

Error

UUID:NUMBER


When the binary log is off and gtid_next is set to AUTOMATIC , then no GTID is generated. This is consistent with the behavior of previous versions.

17.1.5.2 Enabling GTID Transactions Online

This section describes how to enable GTID transactions, and optionally auto-positioning, on servers that are already online and using anonymous transactions. This procedure does not require taking the server offline and is suited to use in production. However, if you have the possibility to take the servers offline when enabling GTID transactions that process is easier.

Before you start, ensure that the servers meet the following pre-conditions:

  • All servers in your topology must use MySQL 5.7.6 or later. You cannot enable GTID transactions online on any single server unless all servers which are in the topology are using this version.

  • All servers have gtid_mode set to the default value OFF .

The following procedure can be paused at any time and later resumed where it was, or reversed by jumping to the corresponding step of Section 17.1.5.3, “Disabling GTID Transactions Online” , the online procedure to disable GTIDs. This makes the procedure fault-tolerant because any unrelated issues that may appear in the middle of the procedure can be handled as usual, and then the procedure continued where it was left off.

Note

It is crucial that you complete every step before continuing to the next step.

To enable GTID transactions:

  1. On each server, execute:

    SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
                                            

    Let the server run for a while with your normal workload and monitor the logs. If this step causes any warnings in the log, adjust your application so that it only uses GTID-compatible features and does not generate any warnings.

    Important

    This is the first important step. You must ensure that no warnings are being generated in the error logs before going to the next step.

  2. On each server, execute:

    SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
                                            
  3. On each server, execute:

    SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
                                            

    It does not matter which server executes this statement first, but it is important that all servers complete this step before any server begins the next step.

  4. On each server, execute:

    SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
                                            

    It does not matter which server executes this statement first.

  5. On each server, wait until the status variable ONGOING_ANONYMOUS_TRANSACTION_COUNT is zero. This can be checked using:

    SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
                                            
    Note

    On a replication slave, it is theoretically possible that this shows zero and then nonzero again. This is not a problem, it suffices that it shows zero once.

  6. Wait for all transactions generated up to step 5 to replicate to all servers. You can do this without stopping updates: the only important thing is that all anonymous transactions get replicated.

    See Section 17.1.5.4, “Verifying Replication of Anonymous Transactions” for one method of checking that all anonymous transactions have replicated to all servers.

  7. If you use binary logs for anything other than replication, for example point in time backup and restore, wait until you do not need the old binary logs having transactions without GTIDs.

    For instance, after step 6 has completed, you can execute FLUSH LOGS on the server where you are taking backups. Then either explicitly take a backup or wait for the next iteration of any periodic backup routine you may have set up.

    Ideally, wait for the server to purge all binary logs that existed when step 6 was completed. Also wait for any backup taken before step 6 to expire.

    Important

    This is the second important point. It is vital to understand that binary logs containing anonymous transactions, without GTIDs cannot be used after the next step. After this step, you must be sure that transactions without GTIDs do not exist anywhere in the topology.

  8. On each server, execute:

    SET @@GLOBAL.GTID_MODE = ON;
                                            
  9. On each server, add gtid-mode=ON to my.cnf .

    You are now guaranteed that all transactions have a GTID (except transactions generated in step 5 or earlier, which have already been processed). To start using the GTID protocol so that you can later perform automatic fail-over, execute the following on each slave. Optionally, if you use multi-source replication, do this for each channel and include the FOR CHANNEL channel clause:

    STOP SLAVE [FOR CHANNEL 'channel'];
    CHANGE MASTER TO MASTER_AUTO_POSITION = 1 [FOR CHANNEL 'channel'];
    START SLAVE [FOR CHANNEL 'channel'];
                                            

17.1.5.3 Disabling GTID Transactions Online

This section describes how to disable GTID transactions on servers that are already online. This procedure does not require taking the server offline and is suited to use in production. However, if you have the possibility to take the servers offline when disabling GTIDs mode that process is easier.

The process is similar to enabling GTID transactions while the server is online, but reversing the steps. The only thing that differs is the point at which you wait for logged transactions to replicate.

Before you start, ensure that the servers meet the following pre-conditions:

  • All servers in your topology must use MySQL 5.7.6 or later. You cannot disable GTID transactions online on any single server unless all servers which are in the topology are using this version.

  • All servers have gtid_mode set to ON .

  1. Execute the following on each slave, and if you using multi-source replication, do it for each channel and include the FOR CHANNEL channel clause:

    STOP SLAVE [FOR CHANNEL 'channel'];
    CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE = file, \
    MASTER_LOG_POS = position [FOR CHANNEL 'channel'];
    START SLAVE [FOR CHANNEL 'channel'];
                                            
  2. On each server, execute:

    SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
                                            
  3. On each server, execute:

    SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
                                            
  4. On each server, wait until the variable @@GLOBAL.GTID_OWNED is equal to the empty string. This can be checked using:

    SELECT @@GLOBAL.GTID_OWNED;
                                            

    On a replication slave, it is theoretically possible that this is empty and then nonempty again. This is not a problem, it suffices that it is empty once.

  5. Wait for all transactions that currently exist in any binary log to replicate to all slaves. See Section 17.1.5.4, “Verifying Replication of Anonymous Transactions” for one method of checking that all anonymous transactions have replicated to all servers.

  6. If you use binary logs for anything else than replication, for example to do point in time backup or restore: wait until you do not need the old binary logs having GTID transactions.

    For instance, after step 5 has completed, you can execute FLUSH LOGS on the server where you are taking the backup. Then either explicitly take a backup or wait for the next iteration of any periodic backup routine you may have set up.

    Ideally, wait for the server to purge all binary logs that existed when step 5 was completed. Also wait for any backup taken before step 5 to expire.

    Important

    This is the one important point during this procedure. It is important to understand that logs containing GTID transactions cannot be used after the next step. Before proceeding you must be sure that GTID transactions do not exist anywhere in the topology.

  7. On each server, execute:

    SET @@GLOBAL.GTID_MODE = OFF;
                                            
  8. On each server, set gtid-mode=OFF in my.cnf .

    If you want to set enforce_gtid_consistency=OFF , you can do so now. After setting it, you should add enforce_gtid_consistency=OFF to your configuration file.

If you want to downgrade to an earlier version of MySQL, you can do so now, using the normal downgrade procedure.

17.1.5.4 Verifying Replication of Anonymous Transactions

This section explains how to monitor a replication topology and verify that all anonymous transactions have been replicated. This is helpful when changing the replication mode online as you can verify that it is safe to change to GTID transactions.

There are several possible ways to wait for transactions to replicate:

The simplest method, which works regardless of your topology but relies on timing is as follows: if you are sure that the slave never lags more than N seconds, just wait for a bit more than N seconds. Or wait for a day, or whatever time period you consider safe for your deployment.

A safer method in the sense that it does not depend on timing: if you only have a master with one or more slaves, do the following:

  1. On the master, execute:

    SHOW MASTER STATUS;
                                            

    Note down the values in the File and Position column.

  2. On every slave, use the file and position information from the master to execute:

    SELECT MASTER_POS_WAIT(file, position);
                                            

If you have a master and multiple levels of slaves, or in other words you have slaves of slaves, repeat step 2 on each level, starting from the master, then all the direct slaves, then all the slaves of slaves, and so on.

If you use a circular replication topology where multiple servers may have write clients, perform step 2 for each master-slave connection, until you have completed the full circle. Repeat the whole process so that you do the full circle twice .

For example, suppose you have three servers A, B, and C, replicating in a circle so that A -> B -> C -> A. The procedure is then:

  • Do step 1 on A and step 2 on B.

  • Do step 1 on B and step 2 on C.

  • Do step 1 on C and step 2 on A.

  • Do step 1 on A and step 2 on B.

  • Do step 1 on B and step 2 on C.

  • Do step 1 on C and step 2 on A.

17.1.6 Replication and Binary Logging Options and Variables

The following sections contain information about mysqld options and server variables that are used in replication and for controlling the binary log. Options and variables for use on replication masters and replication slaves are covered separately, as are options and variables relating to binary logging and global transaction identifiers (GTIDs). A set of quick-reference tables providing basic information about these options and variables is also included.

Of particular importance is the --server-id option.

Property
Command-Line Format --server-id=#
System Variable server_id
Scope Global
Dynamic Yes
SET_VAR Hint Applies No
Type Integer
Default Value (>= 8.0.3) 1
Default Value (<= 8.0.2) 0
Minimum Value 0
Maximum Value 4294967295

Specifies the server ID. The server_id system variable is set to 1 by default. The server can be started with this default ID, but when binary logging is enabled, an informational message is issued if you did not specify a server ID explicitly using the --server-id option.

For servers that are used in a replication topology, you must specify a unique server ID for each replication server, in the range from 1 to 2 32 − 1. Unique means that each ID must be different from every other ID in use by any other replication master or slave. For additional information, see Section 17.1.6.2, “Replication Master Options and Variables” , and Section 17.1.6.3, “Replication Slave Options and Variables” .

If the server ID is set to 0, binary logging takes place, but a master with a server ID of 0 refuses any connections from slaves, and a slave with a server ID of 0 refuses to connect to a master. Note that although you can change the server ID dynamically to a nonzero value, doing so does not enable replication to start immediately. You must change the server ID and then restart the server to initialize the replication slave.

更多信息,请见 Section 17.1.2.2, “Setting the Replication Slave Configuration” .

server_uuid

The MySQL server generates a true UUID in addition to the default or user-supplied server ID set in the server_id system variable. This is available as the global, read-only variable server_uuid .

Note

The presence of the server_uuid system variable does not change the requirement for setting a unique --server-id for each MySQL server as part of preparing and running MySQL replication, as described earlier in this section.

Property
System Variable server_uuid
Scope Global
Dynamic No
SET_VAR Hint Applies No
Type String

When starting, the MySQL server automatically obtains a UUID as follows:

  1. Attempt to read and use the UUID written in the file data_dir /auto.cnf (where data_dir is the server's data directory).

  2. If data_dir /auto.cnf is not found, generate a new UUID and save it to this file, creating the file if necessary.

The auto.cnf file has a format similar to that used for my.cnf or my.ini files. auto.cnf has only a single [auto] section containing a single server_uuid setting and value; the file's contents appear similar to what is shown here:

[auto]
server_uuid=8a94f357-aab4-11df-86ab-c80aa9429562
                        
Important

The auto.cnf file is automatically generated; do not attempt to write or modify this file.

When using MySQL replication, masters and slaves know each other's UUIDs. The value of a slave's UUID can be seen in the output of SHOW SLAVE HOSTS . Once START SLAVE has been executed, the value of the master's UUID is available on the slave in the output of SHOW SLAVE STATUS .

Note

Issuing a STOP SLAVE or RESET SLAVE statement does not reset the master's UUID as used on the slave.

A server's server_uuid is also used in GTIDs for transactions originating on that server. For more information, see Section 17.1.3, “Replication with Global Transaction Identifiers” .

When starting, the slave I/O thread generates an error and aborts if its master's UUID is equal to its own unless the --replicate-same-server-id option has been set. In addition, the slave I/O thread generates a warning if either of the following is true:

17.1.6.1 Replication and Binary Logging Option and Variable Reference

The following two lists provide basic information about the MySQL command-line options and system variables applicable to replication and the binary log.

The command-line options and system variables in the following list relate to replication masters and replication slaves. Section 17.1.6.2, “Replication Master Options and Variables” , provides more detailed information about options and variables relating to replication master servers. For more information about options and variables relating to replication slaves, see Section 17.1.6.3, “Replication Slave Options and Variables” .

The command-line options and system variables in the following list relate to the binary log. Section 17.1.6.4, “Binary Logging Options and Variables” , provides more detailed information about options and variables relating to binary logging. For additional general information about the binary log, see Section 5.4.4, “The Binary Log” .

For a listing of all command-line options, system and status variables used with mysqld , see Section 5.1.4, “Server Option, System Variable, and Status Variable Reference” .

17.1.6.2 Replication Master Options and Variables

This section describes the server options and system variables that you can use on replication master servers. You can specify the options either on the command line or in an option file . You can specify system variable values using SET .

On the master and each slave, you must use the server-id option to establish a unique replication ID. For each server, you should pick a unique positive integer in the range from 1 to 2 32 − 1, and each ID must be different from every other ID in use by any other replication master or slave. Example: server-id=3 .

For options used on the master for controlling binary logging, see Section 17.1.6.4, “Binary Logging Options and Variables” .

Startup Options for Replication Masters

The following list describes startup options for controlling replication master servers. Replication-related system variables are discussed later in this section.

System Variables Used on Replication Masters

The following system variables are used for or by replication masters:

  • auto_increment_increment

    Property
    System Variable auto_increment_increment
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type Integer
    Default Value 1
    Minimum Value 1
    Maximum Value 65535

    auto_increment_increment and auto_increment_offset are intended for use with master-to-master replication, and can be used to control the operation of AUTO_INCREMENT columns. Both variables have global and session values, and each can assume an integer value between 1 and 65,535 inclusive. Setting the value of either of these two variables to 0 causes its value to be set to 1 instead. Attempting to set the value of either of these two variables to an integer greater than 65,535 or less than 0 causes its value to be set to 65,535 instead. Attempting to set the value of auto_increment_increment or auto_increment_offset to a noninteger value produces an error, and the actual value of the variable remains unchanged.

    As of MySQL 8.0.14, setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.9.1, “System Variable Privileges” .

    Note

    auto_increment_increment is also supported for use with NDB tables.

    These two variables affect AUTO_INCREMENT column behavior as follows:

    • auto_increment_increment controls the interval between successive column values. For example:

      mysql> SHOW VARIABLES LIKE 'auto_inc%';
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | auto_increment_increment | 1     |
      | auto_increment_offset    | 1     |
      +--------------------------+-------+
      2 rows in set (0.00 sec)
      mysql> CREATE TABLE autoinc1
          -> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
        Query OK, 0 rows affected (0.04 sec)
      mysql> SET @@auto_increment_increment=10;
      Query OK, 0 rows affected (0.00 sec)
      mysql> SHOW VARIABLES LIKE 'auto_inc%';
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | auto_increment_increment | 10    |
      | auto_increment_offset    | 1     |
      +--------------------------+-------+
      2 rows in set (0.01 sec)
      mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
      Query OK, 4 rows affected (0.00 sec)
      Records: 4  Duplicates: 0  Warnings: 0
      mysql> SELECT col FROM autoinc1;
      +-----+
      | col |
      +-----+
      |   1 |
      |  11 |
      |  21 |
      |  31 |
      +-----+
      4 rows in set (0.00 sec)
                                                              
    • auto_increment_offset determines the starting point for the AUTO_INCREMENT column value. Consider the following, assuming that these statements are executed during the same session as the example given in the description for auto_increment_increment :

      mysql> SET @@auto_increment_offset=5;
      Query OK, 0 rows affected (0.00 sec)
      mysql> SHOW VARIABLES LIKE 'auto_inc%';
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | auto_increment_increment | 10    |
      | auto_increment_offset    | 5     |
      +--------------------------+-------+
      2 rows in set (0.00 sec)
      mysql> CREATE TABLE autoinc2
          -> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
      Query OK, 0 rows affected (0.06 sec)
      mysql> INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);
      Query OK, 4 rows affected (0.00 sec)
      Records: 4  Duplicates: 0  Warnings: 0
      mysql> SELECT col FROM autoinc2;
      +-----+
      | col |
      +-----+
      |   5 |
      |  15 |
      |  25 |
      |  35 |
      +-----+
      4 rows in set (0.02 sec)
                                                              

      When the value of auto_increment_offset is greater than that of auto_increment_increment , the value of auto_increment_offset is ignored.

    If either of these variables is changed, and then new rows inserted into a table containing an AUTO_INCREMENT column, the results may seem counterintuitive because the series of AUTO_INCREMENT values is calculated without regard to any values already present in the column, and the next value inserted is the least value in the series that is greater than the maximum existing value in the AUTO_INCREMENT column. The series is calculated like this:

    auto_increment_offset + N × auto_increment_increment

    where N is a positive integer value in the series [1, 2, 3, ...]. For example:

    mysql> SHOW VARIABLES LIKE 'auto_inc%';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | auto_increment_increment | 10    |
    | auto_increment_offset    | 5     |
    +--------------------------+-------+
    2 rows in set (0.00 sec)
    mysql> SELECT col FROM autoinc1;
    +-----+
    | col |
    +-----+
    |   1 |
    |  11 |
    |  21 |
    |  31 |
    +-----+
    4 rows in set (0.00 sec)
    mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    mysql> SELECT col FROM autoinc1;
    +-----+
    | col |
    +-----+
    |   1 |
    |  11 |
    |  21 |
    |  31 |
    |  35 |
    |  45 |
    |  55 |
    |  65 |
    +-----+
    8 rows in set (0.00 sec)
                                                

    The values shown for auto_increment_increment and auto_increment_offset generate the series 5 + N × 10, that is, [5, 15, 25, 35, 45, ...]. The highest value present in the col column prior to the INSERT is 31, and the next available value in the AUTO_INCREMENT series is 35, so the inserted values for col begin at that point and the results are as shown for the SELECT query.

    It is not possible to restrict the effects of these two variables to a single table; these variables control the behavior of all AUTO_INCREMENT columns in all tables on the MySQL server. If the global value of either variable is set, its effects persist until the global value is changed or overridden by setting the session value, or until mysqld is restarted. If the local value is set, the new value affects AUTO_INCREMENT columns for all tables into which new rows are inserted by the current user for the duration of the session, unless the values are changed during that session.

    The default value of auto_increment_increment is 1. See Section 17.4.1.1, “Replication and AUTO_INCREMENT” .

  • auto_increment_offset

    Property
    System Variable auto_increment_offset
    Scope Global, Session
    Dynamic Yes
    SET_VAR Hint Applies Yes
    Type Integer
    Default Value 1
    Minimum Value 1
    Maximum Value 65535

    This variable has a default value of 1. For more information, see the description for auto_increment_increment .

    As of MySQL 8.0.14, setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.9.1, “System Variable Privileges” .

    Note

    auto_increment_offset is also supported for use with NDB tables.

  • immediate_server_version

    Property
    Introduced 8.0.14
    System Variable immediate_server_version
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer

    For internal use by replication. This session system variable holds the MySQL Server release number of the server that is the immediate master in a replication topology (for example, 80014 for a MySQL 8.0.14 server instance). If this immediate server is at a release that does not support the session system variable, the value of the variable is set to 0 ( UNKNOWN_SERVER_VERSION ).

    The value of the variable is replicated from a master to a slave. With this information the slave can correctly process data originating from a master at an older release, by recognizing where syntax changes or semantic changes have occurred between the releases involved and handling these appropriately. The information can also be used in a Group Replication environment where one or more members of the replication group is at a newer release than the others. The value of the variable can be viewed in the binary log for each transaction (as part of the Gtid_log_event , or Anonymous_gtid_log_event if GTIDs are not in use on the server), and could be helpful in debugging cross-version replication issues.

    Setting the session value of this system variable is a restricted operation. See Section 5.1.9.1, “System Variable Privileges” . However, note that the variable is not intended for users to set; it is set automatically by the replication infrastructure.

  • original_server_version

    Property
    Introduced 8.0.14
    System Variable original_server_version
    Scope Session
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer

    For internal use by replication. This session system variable holds the MySQL Server release number of the server where a transaction was originally committed (for example, 80014 for a MySQL 8.0.14 server instance). If this original server is at a release that does not support the session system variable, the value of the variable is set to 0 ( UNKNOWN_SERVER_VERSION ). Note that when a release number is set by the original server, the value of the variable is reset to 0 if the immediate server or any other intervening server in the replication topology does not support the session system variable, and so does not replicate its value.

    The value of the variable is set and used in the same ways as for the immediate_server_version system variable. If the value of the variable is the same as that for the immediate_server_version system variable, only the latter is recorded in the binary log, with an indicator that the original server version is the same.

    In a Group Replication environment, view change log events, which are special transactions queued by each group member when a new member joins the group, are tagged with the server version of the group member queuing the transaction. This ensures that the server version of the original donor is known to the joining member. Because the view change log events queued for a particular view change have the same GTID on all members, for this case only, instances of the same GTID might have a different original server version.

    Setting the session value of this system variable is a restricted operation. See Section 5.1.9.1, “System Variable Privileges” . However, note that the variable is not intended for users to set; it is set automatically by the replication infrastructure.

  • rpl_semi_sync_master_enabled

    Property
    System Variable rpl_semi_sync_master_enabled
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value OFF

    Controls whether semisynchronous replication is enabled on the master. To enable or disable the plugin, set this variable to ON or OFF (or 1 or 0), respectively. The default is OFF .

    This variable is available only if the master-side semisynchronous replication plugin is installed.

  • rpl_semi_sync_master_timeout

    Property
    System Variable rpl_semi_sync_master_timeout
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 10000

    A value in milliseconds that controls how long the master waits on a commit for acknowledgment from a slave before timing out and reverting to asynchronous replication. The default value is 10000 (10 seconds).

    This variable is available only if the master-side semisynchronous replication plugin is installed.

  • rpl_semi_sync_master_trace_level

    Property
    System Variable rpl_semi_sync_master_trace_level
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 32

    The semisynchronous replication debug trace level on the master. Four levels are defined:

    • 1 = general level (for example, time function failures)

    • 16 = detail level (more verbose information)

    • 32 = net wait level (more information about network waits)

    • 64 = function level (information about function entry and exit)

    This variable is available only if the master-side semisynchronous replication plugin is installed.

  • rpl_semi_sync_master_wait_for_slave_count

    Property
    System Variable rpl_semi_sync_master_wait_for_slave_count
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 1
    Minimum Value 1
    Maximum Value 65535

    The number of slave acknowledgments the master must receive per transaction before proceeding. By default rpl_semi_sync_master_wait_for_slave_count is 1 , meaning that semisynchronous replication proceeds after receiving a single slave acknowledgment. Performance is best for small values of this variable.

    For example, if rpl_semi_sync_master_wait_for_slave_count is 2 , then 2 slaves must acknowledge receipt of the transaction before the timeout period configured by rpl_semi_sync_master_timeout for semisynchronous replication to proceed. If less slaves acknowledge receipt of the transaction during the timeout period, the master reverts to normal replication.

    Note

    This behavior also depends on rpl_semi_sync_master_wait_no_slave

    This variable is available only if the master-side semisynchronous replication plugin is installed.

  • rpl_semi_sync_master_wait_no_slave

    Property
    System Variable rpl_semi_sync_master_wait_no_slave
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Boolean
    Default Value ON

    Controls whether the master waits for the timeout period configured by rpl_semi_sync_master_timeout to expire, even if the slave count drops to less than the number of slaves configured by rpl_semi_sync_master_wait_for_slave_count during the timeout period.

    When the value of rpl_semi_sync_master_wait_no_slave is ON (the default), it is permissible for the slave count to drop to less than rpl_semi_sync_master_wait_for_slave_count during the timeout period. As long as enough slaves acknowledge the transaction before the timeout period expires, semisynchronous replication continues.

    When the value of rpl_semi_sync_master_wait_no_slave is OFF , if the slave count drops to less than the number configured in rpl_semi_sync_master_wait_for_slave_count at any time during the timeout period configured by rpl_semi_sync_master_timeout , the master reverts to normal replication.

    This variable is available only if the master-side semisynchronous replication plugin is installed.

  • rpl_semi_sync_master_wait_point

    Property
    System Variable rpl_semi_sync_master_wait_point
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Enumeration
    Default Value AFTER_SYNC
    Valid Values

    AFTER_SYNC

    AFTER_COMMIT

    This variable controls the point at which a semisynchronous replication master waits for slave acknowledgment of transaction receipt before returning a status to the client that committed the transaction. These values are permitted:

    • AFTER_SYNC (the default): The master writes each transaction to its binary log and the slave, and syncs the binary log to disk. The master waits for slave acknowledgment of transaction receipt after the sync. Upon receiving acknowledgment, the master commits the transaction to the storage engine and returns a result to the client, which then can proceed.

    • AFTER_COMMIT : The master writes each transaction to its binary log and the slave, syncs the binary log, and commits the transaction to the storage engine. The master waits for slave acknowledgment of transaction receipt after the commit. Upon receiving acknowledgment, the master returns a result to the client, which then can proceed.

    The replication characteristics of these settings differ as follows:

    • With AFTER_SYNC , all clients see the committed transaction at the same time: After it has been acknowledged by the slave and committed to the storage engine on the master. Thus, all clients see the same data on the master.

      In the event of master failure, all transactions committed on the master have been replicated to the slave (saved to its relay log). A crash of the master and failover to the slave is lossless because the slave is up to date. Note, however, that the master cannot be restarted in this scenario and must be discarded, because its binary log might contain uncommitted transactions that would cause a conflict with the slave when externalized after binary log recovery.

    • With AFTER_COMMIT , the client issuing the transaction gets a return status only after the server commits to the storage engine and receives slave acknowledgment. After the commit and before slave acknowledgment, other clients can see the committed transaction before the committing client.

      If something goes wrong such that the slave does not process the transaction, then in the event of a master crash and failover to the slave, it is possible that such clients will see a loss of data relative to what they saw on the master.

    This variable is available only if the master-side semisynchronous replication plugin is installed.

    With the addition of rpl_semi_sync_master_wait_point in MySQL 5.7, a version compatibility constraint was created because it increments the semisynchronous interface version: Servers for MySQL 5.7 and higher do not work with semisynchronous replication plugins from older versions, nor do servers from older versions work with semisynchronous replication plugins for MySQL 5.7 and higher.

17.1.6.3 Replication Slave Options and Variables

This section explains the server options and system variables that apply to slave replication servers and contains the following:

Specify the options either on the command line or in an option file . Many of the options can be set while the server is running by using the CHANGE MASTER TO statement. Specify system variable values using SET .

Server ID. On the master and each slave, you must use the server-id option to establish a unique replication ID in the range from 1 to 2 32 − 1. Unique means that each ID must be different from every other ID in use by any other replication master or slave. Example my.cnf file:

[mysqld]
server-id=3
                            
Startup Options for Replication Slaves

This section explains startup options for controlling replication slave servers. Many of these options can be set while the server is running by using the CHANGE MASTER TO statement. Others, such as the --replicate-* options, can be set only when the slave server starts. Replication-related system variables are discussed later in this section.

  • --log-slave-updates

    Property
    Command-Line Format --log-slave-updates
    System Variable log_slave_updates
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type Boolean
    Default Value (>= 8.0.3) ON
    Default Value (<= 8.0.2) OFF

    This option makes a slave write updates that are received from a master server and performed by the slave's SQL thread to the slave's own binary log. Binary logging, which is controlled by the --log-bin option and is enabled by default, must also be enabled on the slave for updates to be logged. --log-slave-updates is enabled by default, unless you specify --skip-log-bin to disable binary logging, in which case MySQL also disables slave update logging by default. If you need to disable slave update logging when binary logging is enabled, specify --skip-log-slave-updates .

    --log-slave-updates enables replication servers to be chained. For example, you might want to set up replication servers using this arrangement:

    A -> B -> C
                                                

    Here, A serves as the master for the slave B , and B serves as the master for the slave C . For this to work, B must be both a master and a slave. With binary logging and the --log-slave-updates option enabled, which are the default settings, updates received from A are logged by B to its binary log, and can therefore be passed on to C .

  • --master-info-file= file_name

    Property
    Command-Line Format --master-info-file=file_name
    Type File name
    Default Value master.info

    The name for the master info log, if --master-info-repository=FILE is set. The default name is master.info in the data directory. --master-info-repository=FILE is now deprecated. For information about the master info log, see Section 17.2.4.2, “Slave Status Logs” .

  • --master-retry-count= count

    Property
    Command-Line Format --master-retry-count=#
    Deprecated Yes
    Type Integer
    Default Value 86400
    Minimum Value 0
    Maximum Value (64-bit platforms) 18446744073709551615
    Maximum Value (32-bit platforms) 4294967295

    The number of times that the slave tries to reconnect to the master before giving up. The default value is 86400 times. A value of 0 means infinite , and the slave attempts to connect forever. Reconnection attempts are triggered when the slave reaches its connection timeout (specified by the --slave-net-timeout option) without receiving data or a heartbeat signal from the master. Reconnection is attempted at intervals set by the MASTER_CONNECT_RETRY option of the CHANGE MASTER TO statement (which defaults to every 60 seconds).

    This option is deprecated and will be removed in a future MySQL release. Use the MASTER_RETRY_COUNT option of the CHANGE MASTER TO statement instead.

  • --max-relay-log-size= size

    Property
    Command-Line Format --max-relay-log-size=#
    System Variable max_relay_log_size
    Scope Global
    Dynamic Yes
    SET_VAR Hint Applies No
    Type Integer
    Default Value 0
    Minimum Value 0
    Maximum Value 1073741824

    The size at which the server rotates relay log files automatically. If this value is nonzero, the relay log is rotated automatically when its size exceeds this value. If this value is zero (the default), the size at which relay log rotation occurs is determined by the value of max_binlog_size . For more information, see Section 17.2.4.1, “The Slave Relay Log” .

  • --relay-log= file_name

    Property
    Command-Line Format --relay-log=file_name
    System Variable relay_log
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type File name

    The base name for the relay log. The server creates relay log files in sequence by adding a numeric suffix to the base name.

    For the default replication channel, the default base name for relay logs is host_name -relay-bin , using the name of the host machine. For non-default replication channels, the default base name for relay logs is host_name -relay-bin- channel , where channel is the name of the replication channel recorded in this relay log.

    The default location for relay log files is the data directory. You can use the --relay-log option to specify an alternative location, by adding a leading absolute path name to the base name to specify a different directory.

    The relay log and relay log index on a replication server cannot be given the same names as the binary log and binary log index, whose names are specified by the --log-bin and --log-bin-index options. The server issues an error message and does not start if the binary log and relay log file base names would be the same.

    Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default base name is used only if the option is not actually specified . If you use the --relay-log option without specifying a value, unexpected behavior is likely to result; this behavior depends on the other options used, the order in which they are specified, and whether they are specified on the command line or in an option file. For more information about how MySQL handles server options, see Section 4.2.4, “Specifying Program Options” .

    If you specify this option, the value specified is also used as the base name for the relay log index file. You can override this behavior by specifying a different relay log index file base name using the --relay-log-index option.

    When the server reads an entry from the index file, it checks whether the entry contains a relative path. If it does, the relative part of the path is replaced with the absolute path set using the --relay-log option. An absolute path remains unchanged; in such a case, the index must be edited manually to enable the new path or paths to be used. Previously, manual intervention was required whenever relocating the binary log or relay log files. (Bug #11745230, Bug #12133)

    You may find the --relay-log option useful in performing the following tasks:

    • Creating relay logs whose names are independent of host names.

    • If you need to put the relay logs in some area other than the data directory because your relay logs tend to be very large and you do not want to decrease max_relay_log_size .

    • To increase speed by using load-balancing between disks.

    You can obtain the relay log file name (and path) from the relay_log_basename system variable.

  • --relay-log-index= file_name

    Property
    Command-Line Format --relay-log-index=file_name
    System Variable relay_log_index
    Scope Global
    Dynamic No
    SET_VAR Hint Applies No
    Type File name

    The name for the index file for the relay log. If you do not specify the --relay-log-index option, but the --relay-log option is specified, its value is used as the default base name for the relay log index file. If the --relay-log option is also not specified, then for the default replication channel, the default name is host_name -relay-bin.index , using the name of the host machine. For non-default replication channels, the default name is host_name -relay-bin- channel .index , where channel is the name of the replication channel recorded in this relay log index.

    The default location for relay log files is the data directory, or any other location that was specified using the --relay-log option. You can use the --relay-log-index option to specify an alternative location, by adding a leading absolute path name to the base name to specify a different directory.

    The relay log and relay log index on a replication server cannot be given the same names as the binary log and binary log index, whose names are specified by the --log-bin and --log-bin-index options. The server issues an error message and does not start if the binary log and relay log file base names would be the same.

    Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default base name is used only if the option is not actually specified . If you use the --relay-log-index option without specifying a value, unexpected behavior is likely to result; this behavior depends on the other options used, the order in which they are specified, and whether they are specified on the command line or in an option file. For more information about how MySQL handles server options, see Section 4.2.4, “Specifying Program Options” .

  • --relay-log-info-file= file_name