Chapter 17 Replication

Table of Contents

17.1 Replication Configuration
17.1.1 How to Set Up Replication
17.1.2 Replication Formats
17.1.3 Replication with Global Transaction Identifiers
17.1.4 Replication and Binary Logging Options and Variables
17.1.5 Common Replication Administration Tasks
17.2 Replication Implementation
17.2.1 Replication Implementation Details
17.2.2 Replication Relay and Status Logs
17.2.3 How Servers Evaluate Replication Filtering Rules
17.3 Replication Solutions
17.3.1 Using Replication for Backups
17.3.2 Using Replication with Different Master and Slave Storage Engines
17.3.3 Using Replication for Scale-Out
17.3.4 Replicating Different Databases to Different Slaves
17.3.5 Improving Replication Performance
17.3.6 Switching Masters During Failover
17.3.7 Setting Up Replication Using SSL
17.3.8 Semisynchronous Replication
17.3.9 Delayed Replication
17.4 Replication Notes and Tips
17.4.1 Replication Features and Issues
17.4.2 Replication Compatibility Between MySQL Versions
17.4.3 Upgrading a Replication Setup
17.4.4 Troubleshooting Replication
17.4.5 How to Report Replication Bugs or Problems

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous by default, therefore slaves do not need to be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

For answers to some questions often asked by those who are new to MySQL Replication, see Section A.13, “MySQL 5.6 FAQ: Replication”.

Advantages of replication in MySQL include:

Replication in MySQL features support for 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 MySQL Cluster (see Chapter 18, MySQL Cluster NDB 7.3 and MySQL Cluster NDB 7.4). In MySQL 5.6, an interface to semisynchronous replication is supported in addition to the built-in asynchronous replication. With semisynchronous replication, a commit performed on the master side 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.8, “Semisynchronous Replication” MySQL 5.6 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.9, “Delayed Replication”.

There are a number of solutions available for setting up replication between two servers, but 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.1, “How to Set Up 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 may also use a third variety, Mixed Based Replication (MBR). For more information on the different replication formats, see Section 17.1.2, “Replication Formats”. In MySQL 5.6, statement-based format is the default.

MySQL 5.6.5 and later supports transactional replication based on global transaction identifiers (GTIDs). When using this type of replication, it is not necessary to work directly with log files or positions within these files, which greatly simplifies many common replication tasks. Because replication using GTIDs is entirely transactional, consistency between master and slave is guaranteed 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, see Section 17.1.3, “Replication with Global Transaction Identifiers”.

Replication is controlled through a number of different options and variables. These control the core operation of the replication, timeouts, and the databases and filters that can be applied on databases and tables. For more information on the available options, see Section 17.1.4, “Replication and Binary Logging Options and Variables”.

You can use replication to solve a number of different problems, including problems with 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 problems and their resolution, including an FAQ, see Section 17.4, “Replication Notes and Tips”.

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 replication, see Section 17.2, “Replication Implementation”.

17.1 Replication Configuration

Replication between servers in MySQL is based on the binary logging mechanism. 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.

Important

You cannot configure the master to log only certain events.

The master is dumb in this scenario. Once binary logging has been enabled, all statements are recorded in the binary log. 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; you cannot configure the master to log only certain events. If you do not 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.

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, which can be either a file or a table (see Section 17.2.2, “Replication Relay and Status Logs”).

This section describes 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 How to Set Up Replication

This section describes how to set up complete replication of a MySQL server. 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 replication setups:

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.4, “Replication and Binary Logging Options and Variables”.

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.

17.1.1.1 Setting the Replication Master Configuration

On a replication master, you must enable binary logging and establish a unique server ID. If this has not already been done, a server restart is required.

Binary logging must be enabled on the master because the binary log is the basis for replicating changes from the master to its slaves. If binary logging is not enabled using the log-bin option, replication is not possible.

Each server within a replication group must be configured with a unique server ID. This ID is used to identify individual servers within the group, and must be a positive integer between 1 and (232)–1. How you organize and select the numbers is your choice.

To configure the binary log and server ID options, shut down the MySQL server and edit the my.cnf or my.ini file. Within the [mysqld] section of the configuration file, add the log-bin and server-id options. If these options already exist, but are commented out, uncomment the options and alter them according to your needs. For example, to enable binary logging using a log file name prefix of mysql-bin, and configure a server ID of 1, use these lines:

[mysqld]
log-bin=mysql-bin
server-id=1

After making the changes, restart the server.

Note

If you omit server-id (or set it explicitly to its default value of 0), the master refuses any connections from slaves.

Note

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 master my.cnf file.

Note

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

17.1.1.2 Setting the Replication Slave Configuration

On a replication slave, you must establish 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 server-id value that differs from that of the master and from any of the other slaves.

Note

If you omit server-id (or set it explicitly to its default value of 0), the slave refuses to connect to a master.

You do not have to enable binary logging on the slave for replication to be set up. However, if you enable binary logging on the slave, you can use the slave's binary log for data backups and crash recovery, and also use the slave as part of a more complex replication topology. For example, where this slave then acts as a master to other slaves.

17.1.1.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. 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 file or table (see Section 17.2.2.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 mydomain.com domain, issue these statements on the master:

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

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

17.1.1.4 Obtaining the Replication Master Binary Log Coordinates

Note the master's current coordinates within its binary log. You need this information to configure the slave to start the replication process at the correct point.

If you have existing data on your master that you want to synchronize on your slaves before starting the replication process, you must stop processing statements on the master, and then obtain its current binary log coordinates and dump its data, before permitting the master to continue executing statements. If you do not stop the execution of statements, the data dump and the master status information that you use will not match and you will end up with inconsistent or corrupted databases on the slaves.

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;
    

    For InnoDB tables, note that FLUSH TABLES WITH READ LOCK also blocks COMMIT operations.

    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 Position 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 without binary logging enabled, 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.

If you have existing data that needs be to synchronized with the slave before you start replication, leave the client running so that the lock remains in place and then proceed to Section 17.1.1.5, “Creating a Data Snapshot Using mysqldump”, or Section 17.1.1.6, “Creating a Data Snapshot Using Raw Data Files”. The idea here is to prevent any further changes so that the data copied to the slaves is in synchrony with the master.

If you are setting up a brand new master and slave replication group, you can exit the first session to release the read lock.

17.1.1.5 Creating a Data Snapshot Using mysqldump

One way to create a snapshot of the data in an existing master database is to use the mysqldump tool to create a dump of all the databases you want to replicate. Once the data dump has been completed, you then import this data into the slave before starting the replication process.

The example shown here 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

If you do not use --master-data, then it is necessary to lock all tables in a separate session manually (using FLUSH TABLES WITH READ LOCK) prior to running mysqldump, then exiting or running UNLOCK TABLES from the second session to release the locks. You must also obtain binary log position information matching the snapshot, using SHOW MASTER STATUS, and use this to issue the appropriate CHANGE MASTER TO statement when starting the slave.

When choosing databases to include in the dump, remember that you need to filter out databases on each slave that you do not want to include in the replication process.

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.1.6 Creating a Data Snapshot Using Raw Data Files

If your database is large, copying the raw data files can be more efficient than using mysqldump and importing the file on each slave. This technique skips the overhead of updating indexes as the INSERT statements are replayed.

Using this method with tables in storage engines with complex caching or logging algorithms requires extra steps to produce a perfect point in time snapshot: the initial copy command might 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.

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.

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 later used on the slave. MySQL Enterprise Backup is a commercial product that is included as part of a MySQL Enterprise subscription. See Section 25.2, “MySQL Enterprise Backup” for detailed information.

Otherwise, use the cold backup technique to obtain a reliable binary snapshot of InnoDB tables: copy all data files after doing a slow shutdown of the MySQL Server.

To create a raw data snapshot of MyISAM tables, you can use standard 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, providing that your MySQL data files exist on a single file system. 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.)

You might want to specifically exclude the following files from your archive:

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.1.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.1.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.1.7 Setting Up Replication with New Master and Slaves

The easiest and most straightforward method for setting up replication is to use new master and slave servers.

You can also use this method 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 will be automatically replicated to the slaves.

To set up replication between a new master and slave:

  1. Configure the MySQL master with the necessary configuration properties. See Section 17.1.1.1, “Setting the Replication Master Configuration”.

  2. Start up the MySQL master.

  3. Set up a user. See Section 17.1.1.3, “Creating a User for Replication”.

  4. Obtain the master status information. See Section 17.1.1.4, “Obtaining the Replication Master Binary Log Coordinates”.

  5. On the master, release the read lock:

    mysql> UNLOCK TABLES;
    
  6. On the slave, edit the MySQL configuration. See Section 17.1.1.2, “Setting the Replication Slave Configuration”.

  7. Start up the MySQL slave.

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

Perform the slave setup steps on each slave.

Because there is no data to load or exchange on a new server configuration you do not need to copy or import any information.

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

shell> mysql -h master < fulldb.dump

17.1.1.8 Setting Up Replication with Existing Data

When setting up replication with existing data, you will need to decide how best to get the data from the master to the slave before starting the replication service.

The basic process for setting up replication with existing data is as follows:

  1. With the MySQL master running, create a user to be used by the slave when connecting to the master during replication. See Section 17.1.1.3, “Creating a User for Replication”.

  2. If you have not already configured the server-id and enabled binary logging on the master server, you will need to shut it down to configure these options. See Section 17.1.1.1, “Setting the Replication Master Configuration”.

    If you have to shut down your master server, this is a good opportunity to take a snapshot of its databases. You should obtain the master status (see Section 17.1.1.4, “Obtaining the Replication Master Binary Log Coordinates”) before taking down the master, updating the configuration and taking a snapshot. For information on how to create a snapshot using raw data files, see Section 17.1.1.6, “Creating a Data Snapshot Using Raw Data Files”.

  3. If your master server is already correctly configured, obtain its status (see Section 17.1.1.4, “Obtaining the Replication Master Binary Log Coordinates”) and then use mysqldump to take a snapshot (see Section 17.1.1.5, “Creating a Data Snapshot Using mysqldump”) or take a raw snapshot of the live server using the guide in Section 17.1.1.6, “Creating a Data Snapshot Using Raw Data Files”.

  4. Update the configuration of the slave. See Section 17.1.1.2, “Setting the Replication Slave Configuration”.

  5. The next step depends on how you created the snapshot of data on the master.

    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.

  6. 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.1.10, “Setting the Master Configuration on the Slave”.

  7. Start the slave threads:

    mysql> START SLAVE;
    

After you have performed this procedure, the slave should connect to the master and catch up on any updates that have occurred since the snapshot was taken.

If you have forgotten to set the server-id option for the master, slaves cannot connect to it.

If you have forgotten to set the server-id option for the slave, you get the following error in the slave's error log:

Warning: You should set server-id to a non-0 value if master_host
is set; we will force server id to 2, but this MySQL server will
not act as a slave.

You also find error messages in the slave's error log if it is not able to replicate for any other reason.

The slave uses information stored in its master info repository to keep track of how much of the master's binary log it has processed. The repository can be in the form of files or a table, as determined by the value set for --master-info-repository. When a slave runs with --master-info-repository=FILE, you can find in its data directory two files, named master.info and relay-log.info. If --master-info-repository=TABLE instead, this information is saved in the table master_slave_info in the mysql database. In either case, do not remove or edit the files or table 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 can use the values specified in the statement to update the status files automatically. See Section 17.2.2, “Replication Relay and Status Logs”, for more information.

Note

The contents of the master info repository override some of the server options specified on the command line or in my.cnf. See Section 17.1.4, “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.1.9 Introducing Additional Slaves to an Existing Replication Environment

To add another slave to an existing replication configuration, you can do so without stopping the master. Instead, set up the new slave by making a copy of an existing slave, except that you configure the new slave with a different server-id value.

To duplicate an existing slave:

  1. Shut down the existing slave:

    shell> mysqladmin shutdown
    
  2. Copy the data directory from the existing slave to the new slave. 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. Ensure that you also copy the log files and relay log files.

    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 is due to the fact that, if the --relay-log option is not specified, 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.4, “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 feasible, 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 a STOP SLAVE on the new slave.

      If you have already started the existing slave again, issue a 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.

  3. Copy the master info and relay log info repositories (see Section 17.2.2, “Replication Relay and Status Logs”) from the existing slave to the new slave. These hold the current log coordinates for the master's binary log and the slave's relay log.

  4. Start the existing slave.

  5. On the new slave, edit the configuration and give the new slave a unique server-id not used by the master or any of the existing slaves.

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

17.1.1.10 Setting the Master Configuration on the Slave

To set up the slave to communicate with the master for replication, you must tell the slave 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”.

17.1.2 Replication Formats

Replication works because events written to the binary log are read from the master and then processed on the slave. The events are recorded within the binary log in different formats according to the type of event. The different replication formats used correspond to the binary logging format used when the events were recorded in the master's binary log. The correlation between binary logging formats and the terms used during replication are:

  • When using statement-based binary logging, the master writes SQL statements to the binary log. Replication of the master to the slave works by executing the SQL statements on the slave. This is called statement-based replication (often abbreviated as SBR), which corresponds to the standard MySQL statement-based binary logging format. Replication capabilities in MySQL version 5.1.4 and earlier used this format exclusively.

  • When using row-based logging, the master writes events to the binary log that indicate how individual table rows are changed. Replication of the master to the slave works by copying the events representing the changes to the table rows to the slave. This is called row-based replication (often abbreviated as RBR). In row-based replication, the master writes events to the binary log that indicate how individual table rows are changed.

  • You can also configure MySQL to use a mix of both statement-based and row-based logging, depending on which is most appropriate for the change to be logged. This is called mixed-format logging. When using mixed-format logging, a statement-based log is used by default. Depending on certain statements, and also the storage engine being used, the log is automatically switched to row-based in particular cases. Replication using the mixed format is often referred to as mixed-based replication or mixed-format replication. For more information, see Section 5.2.4.3, “Mixed Binary Logging Format”.

In MySQL 5.6, statement-based format is the default.

MySQL Cluster.  The default binary logging format in all MySQL Cluster NDB 7.3 and MySQL Cluster NDB 7.4 releases is MIXED. You should note that MySQL Cluster Replication always uses row-based replication, and that the NDB storage engine is incompatible with statement-based replication. See Section 18.6.2, “General Requirements for MySQL Cluster Replication”, for more information.

When using MIXED format, the binary logging format is determined in part by the storage engine being used and the statement being executed. For more information on mixed-format logging and the rules governing the support of different logging formats, see Section 5.2.4.3, “Mixed Binary Logging Format”.

The logging format in a running MySQL server is controlled by setting the binlog_format server system variable. This variable can be set with session or global scope. The rules governing when and how the new setting takes effect are the same as for other MySQL server system variables—setting the variable for the current session lasts only until the end of that session, and the change is not visible to other sessions; setting the variable globally requires a restart of the server to take effect. For more information, see Section 13.7.4, “SET Syntax”.

There are conditions under which you cannot change the binary logging format at runtime or doing so causes replication to fail. See Section 5.2.4.2, “Setting The Binary Log Format”.

You must have the SUPER privilege to set either the global or session binlog_format value.

The statement-based and row-based replication formats have different issues and limitations. For a comparison of their relative advantages and disadvantages, see Section 17.1.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

With statement-based replication, you may encounter issues with replicating stored routines or triggers. You can avoid these issues by using row-based replication instead. For more information, see Section 20.7, “Binary Logging of Stored Programs”.

17.1.2.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication

Each binary logging format has advantages and disadvantages. For most users, the mixed replication format should provide the best combination of data integrity and performance. If, however, you want to take advantage of the features specific to the statement-based or row-based replication format when performing certain tasks, you can use the information in this section, which provides a summary of their relative advantages and disadvantages, to determine which is best for your needs.

Advantages of statement-based replication
  • Proven technology that has existed in MySQL since 3.23.

  • Less data written to log files. When updates or deletes affect many rows, this results in much less storage space required for log files. This also means that taking and restoring from backups can be accomplished more quickly.

  • Log files contain all statements that made any changes, so they can be used to audit the database.

Disadvantages of statement-based replication
  • Statements that are unsafe for SBR.  Not all statements which modify data (such as INSERT DELETE, UPDATE, and REPLACE statements) can be replicated using statement-based replication. Any nondeterministic behavior is difficult to replicate when using statement-based replication. Examples of such DML (Data Modification Language) statements include the following:

    Statements that cannot be replicated correctly using statement-based replication are logged with a warning like the one shown here:

    [Warning] Statement is not safe to log in statement format.
    

    A similar warning is also issued to the client in such cases. The client can display it using SHOW WARNINGS.

  • INSERT ... SELECT requires a greater number of row-level locks than with row-based replication.

  • UPDATE statements that require a table scan (because no index is used in the WHERE clause) must lock a greater number of rows than with row-based replication.

  • For InnoDB: An INSERT statement that uses AUTO_INCREMENT blocks other nonconflicting INSERT statements.

  • For complex statements, the statement must be evaluated and executed on the slave before the rows are updated or inserted. With row-based replication, the slave only has to modify the affected rows, not execute the full statement.

  • If there is an error in evaluation on the slave, particularly when executing complex statements, statement-based replication may slowly increase the margin of error across the affected rows over time. See Section 17.4.1.26, “Slave Errors During Replication”.

  • Stored functions execute with the same NOW() value as the calling statement. However, this is not true of stored procedures.

  • Deterministic UDFs must be applied on the slaves.

  • Table definitions must be (nearly) identical on master and slave. See Section 17.4.1.9, “Replication with Differing Table Definitions on Master and Slave”, for more information.

Advantages of row-based replication
  • All changes can be replicated. This is the safest form of replication.

    The mysql database is not replicated. The mysql database is instead seen as a node-specific database. Row-based replication is not supported on tables in this database. Instead, statements that would normally update this information—such as GRANT, REVOKE and the manipulation of triggers, stored routines (including stored procedures), and views—are all replicated to slaves using statement-based replication.

    For statements such as CREATE TABLE ... SELECT, a CREATE statement is generated from the table definition and replicated using statement-based format, while the row insertions are replicated using row-based format.

  • The technology is the same as in most other database management systems; knowledge about other systems transfers to MySQL.

  • Fewer row locks are required on the master, which thus achieves higher concurrency, for the following types of statements:

  • Fewer row locks are required on the slave for any INSERT, UPDATE, or DELETE statement.

Disadvantages of row-based replication
  • RBR can generate more data that must be logged. To replicate a DML statement (such as an UPDATE or DELETE statement), statement-based replication writes only the statement to the binary log. By contrast, row-based replication writes each changed row to the binary log. If the statement changes many rows, row-based replication may write significantly more data to the binary log; this is true even for statements that are rolled back. This also means that taking and restoring from backup can require more time. In addition, the binary log is locked for a longer time to write the data, which may cause concurrency problems.

  • Deterministic UDFs that generate large BLOB values take longer to replicate with row-based replication than with statement-based replication. This is because the BLOB column value is logged, rather than the statement generating the data.

  • You cannot examine the logs to see what statements were executed, nor can you see on the slave what statements were received from the master and executed.

    However, you can see what data was changed using mysqlbinlog with the options --base64-output=DECODE-ROWS and --verbose.

  • For tables using the MyISAM storage engine, a stronger lock is required on the slave for INSERT statements when applying them as row-based events to the binary log than when applying them as statements. This means that concurrent inserts on MyISAM tables are not supported when using row-based replication.

17.1.2.2 Usage of Row-Based Logging and Replication

MySQL uses statement-based logging (SBL), row-based logging (RBL) or mixed-format logging. The type of binary log used impacts the size and efficiency of logging.Therefore the choice between row-based replication (RBR) or statement-based replication (SBR) depends on your application and environment. This section describes known issues when using a row-based format log, and discusses some best practices using it in replication.

For additional information, see Section 17.1.2, “Replication Formats”, and Section 17.1.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

For information about issues specific to MySQL Cluster Replication (which depends on row-based replication), see Section 18.6.3, “Known Issues in MySQL Cluster Replication”.

  • Row-based logging of temporary tables.  As noted in Section 17.4.1.22, “Replication and Temporary Tables”, temporary tables are not replicated when using row-based format. When using mixed format logging, safe statements involving temporary tables are logged using statement-based format. For more information, see Section 17.1.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

    Temporary tables are not replicated when using row-based format because there is no need. In addition, because temporary tables can be read only from the thread which created them, there is seldom if ever any benefit obtained from replicating them, even when using statement-based format.

    In MySQL 5.6, you can switch from statement-based to row-based binary logging mode even when temporary tables have been created. However, while using the row-based format, the MySQL server cannot determine the logging mode that was in effect when a given temporary table was created. For this reason, the server in such cases logs a DROP TEMPORARY TABLE IF EXISTS statement for each temporary table that still exists for a given client session when that session ends. While this means that it is possible that an unnecessary DROP TEMPORARY TABLE statement might be logged in some cases, the statement is harmless, and does not cause an error even if the table does not exist, due to the presence of the IF NOT EXISTS option.

    In MySQL 5.6.6 and earlier, the --disable-gtid-unsafe-statements option caused any nontransactional DML statement involving temporary tables to fail with an error when using row-based logging, in spite of the fact that they are not written to the binary log. In MySQL 5.6.7 and later, such statements are allowed when using binlog_format=ROW, as long as any nontransactional tables affected by the statements are temporary tables (Bug #14272672).

  • RBL and synchronization of nontransactional tables.  When many rows are affected, the set of changes is split into several events; when the statement commits, all of these events are written to the binary log. When executing on the slave, a table lock is taken on all tables involved, and then the rows are applied in batch mode. (This may or may not be effective, depending on the engine used for the slave's copy of the table.)

  • Latency and binary log size.  RBL writes changes for each row to the binary log and so its size can increase quite rapidly. This can significantly increase the time required to make changes on the slave that match those on the master. You should be aware of the potential for this delay in your applications.

  • Reading the binary log.  mysqlbinlog displays row-based events in the binary log using the BINLOG statement (see Section 13.7.6.1, “BINLOG Syntax”). This statement displays an event as a base 64-encoded string, the meaning of which is not evident. When invoked with the --base64-output=DECODE-ROWS and --verbose options, mysqlbinlog formats the contents of the binary log to be human readable. When binary log events were written in row-based format and you want to read or recover from a replication or database failure you can use this command to read contents of the binary log. For more information, see Section 4.6.8.2, “mysqlbinlog Row Event Display”.

  • Binary log execution errors and slave_exec_mode.  If slave_exec_mode is IDEMPOTENT, a failure to apply changes from RBL because the original row cannot be found does not trigger an error or cause replication to fail. This means that it is possible that updates are not applied on the slave, so that the master and slave are no longer synchronized. Latency issues and use of nontransactional tables with RBR when slave_exec_mode is IDEMPOTENT can cause the master and slave to diverge even further. For more information about slave_exec_mode, see Section 5.1.4, “Server System Variables”.

    Note

    slave_exec_mode=IDEMPOTENT is generally useful only for circular replication or multi-master replication with MySQL Cluster, for which IDEMPOTENT is the default value.

    For other scenarios, setting slave_exec_mode to STRICT is normally sufficient; this is the default value.

    Note

    Formerly, the default value when using MySQL Cluster was slave_exec_mode=IDEMPOTENT, but this is no longer the case in MySQL Cluster NDB 7.3 and later.

  • Lack of binary log checksums.  RBL does not use checksums, so network, disk, and other errors may not be identified when processing the binary log. To ensure that data is transmitted without network corruption use SSL for replication connections. The CHANGE MASTER TO statement has options to enable replication over SSL. See also Section 13.4.2.1, “CHANGE MASTER TO Syntax”, for general information about setting up MySQL with SSL.

  • Filtering based on server ID not supported.  In MySQL 5.6, you can filter based on server ID by using the IGNORE_SERVER_IDS option for the CHANGE MASTER TO statement. This option works with statement-based and row-based logging formats. Another method to filter out changes on some slaves is to use a WHERE clause that includes the relation @@server_id <> id_value clause with UPDATE and DELETE statements. For example, WHERE @@server_id <> 1. However, this does not work correctly with row-based logging. To use the server_id system variable for statement filtering, use statement-based logging.

  • Database-level replication options.  The effects of the --replicate-do-db, --replicate-ignore-db, and --replicate-rewrite-db options differ considerably depending on whether row-based or statement-based logging is used. Therefore, it is recommended to avoid database-level options and instead use table-level options such as --replicate-do-table and --replicate-ignore-table. For more information about these options and the impact replication format has on how they operate, see Section 17.1.4, “Replication and Binary Logging Options and Variables”.

  • RBL, nontransactional tables, and stopped slaves.  When using row-based logging, if the slave server is stopped while a slave thread is updating a nontransactional table, the slave database can reach an inconsistent state. For this reason, it is recommended that you use a transactional storage engine such as InnoDB for all tables replicated using the row-based format. Use of STOP SLAVE or STOP SLAVE SQL_THREAD prior to shutting down the slave MySQL server helps prevent issues from occurring, and is always recommended regardless of the logging format or storage engine you use.

17.1.2.3 Determination of Safe and Unsafe Statements in Binary Logging

The safeness of a statement in MySQL Replication, refers to whether the statement and its effects can be replicated correctly using statement-based format. If this is true of the statement, we refer to the statement as safe; otherwise, we refer to it as unsafe.

In general, a statement is safe if it deterministic, and unsafe if it is not. However, certain nondeterministic functions are not considered unsafe (see Nondeterministic functions not considered unsafe, later in this section). In addition, statements using results from floating-point math functions—which are hardware-dependent—are always considered unsafe (see Section 17.4.1.12, “Replication and Floating-Point Values”).

Handling of safe and unsafe statements.  A statement is treated differently depending on whether the statement is considered safe, and with respect to the binary logging format (that is, the current value of binlog_format).

  • When using row-based logging, no distinction is made in the treatment of safe and unsafe statements.

  • When using mixed-format logging, statements flagged as unsafe are logged using the row-based format; statements regarded as safe are logged using the statement-based format.

  • When using statement-based logging, statements flagged as being unsafe generate a warning to this effect. Safe statements are logged normally.

Each statement flagged as unsafe generates a warning. Formerly, if a large number of such statements were executed on the master, this could lead to excessively large error log files. To prevent this, MySQL provides a warning suppression mechanism (introduced in MySQL 5.6.7), which behaves as follows: Whenever the 50 most recent ER_BINLOG_UNSAFE_STATEMENT warnings have been generated more than 50 times in any 50-second period, warning suppression is enabled. When activated, this causes such warnings not to be written to the error log; instead, for each 50 warnings of this type, a note The last warning was repeated N times in last S seconds is written to the error log. This continues as long as the 50 most recent such warnings were issued in 50 seconds or less; once the rate has decreased below this threshold, the warnings are once again logged normally. Warning suppression has no effect on how the safety of statements for statement-based logging is determined, nor on how warnings are sent to the client. MySQL clients still receive one warning for each such statement.

For more information, see Section 17.1.2, “Replication Formats”.

Statements considered unsafe.  Statements with the following characteristics are considered unsafe:

For additional information, see Section 17.4.1, “Replication Features and Issues”.

17.1.3 Replication with Global Transaction Identifiers

This section explains transaction-based replication using global transaction identifiers (GTIDs), introduced in MySQL 5.6.5. 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.1.2, “Replication Formats”); however, for best results, we recommend that you use the row-based format.

This section discusses the following topics:

For information about MySQL Server options and variables relating to GTID-based replication, see Section 17.1.4.5, “Global Transaction ID Options and Variables”. See also Section 12.16, “Functions Used with Global Transaction IDs”, which describes SQL functions supported by MySQL 5.6 for use with GTIDs.

Note

GTIDs are not compatible or supported with the NDB storage engine used by MySQL Cluster. Enabling GTIDs in MySQL Cluster is very likely to cause problems with NDB, and to cause MySQL Cluster Replication to fail as well.

17.1.3.1 GTID Concepts

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

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 server'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 this server; 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

This format is used to represent GTIDs in the output of statements such as SHOW SLAVE STATUS as well as in the binary log. They can also be seen when viewing the log file with mysqlbinlog --base64-output=DECODE-ROWS or in the output from SHOW BINLOG EVENTS.

As written in the output of statements such as SHOW MASTER STATUS or SHOW SLAVE STATUS, a sequence of GTIDs originating from the same server may be collapsed into a single expression, as shown here.

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

The example just shown represents the first through fifth transactions originating on the MySQL Server whose server_uuid is 3E11FA47-71CA-11E1-9E33-C80AA9429562.

In MySQL 5.6.6 and later, this format is also used to supply the argument required by the START SLAVE options SQL_BEFORE_GTIDS and SQL_AFTER_GTIDS.

GTID Sets

A GTID set is a set of global transaction identifiers which is represented as shown here:

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) 

GTID sets are used in the MySQL Server in several ways. For example, the values stored by the gtid_executed and gtid_purged system variables are represented as GTID sets. In addition, the functions GTID_SUBSET() and GTID_SUBTRACT() require GTID sets as input.

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.

When GTIDs are in use, the slave has no need for any nonlocal data, such as the name of a file on the master and a position within that file. All necessary information for synchronizing with the master is obtained directly from the replication data stream. From the perspective of the database administrator or developer, GTIDs entirely take the place of the file-offset pairs previously required to determine points for starting, stopping, or resuming the flow of data between master and slave. This means that, when you are using GTIDs for replication, you do not need (or want) to include MASTER_LOG_FILE or MASTER_LOG_POS options in the CHANGE MASTER TO statement used to direct a slave to replicate from a given master; in place of these options, it is necessary only to enable the MASTER_AUTO_POSITION option introduced in MySQL 5.6.5. For the exact steps needed to configure and start masters and slaves using GTID-based replication, see Section 17.1.3.2, “Setting Up Replication Using GTIDs”.

The generation and lifecycle of a GTID consists of the following steps:

  1. A transaction is executed and committed on the master.

    This transaction is assigned a GTID using 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).

  2. 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.

  3. The slave checks to make sure that this GTID has not already been used to log a transaction in its own binary log. If and only if this GTID has not been used, the slave then writes the GTID and applies the transaction (and writes the transaction to its binary log). By reading and checking the 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. In other words, multiple clients are not permitted to apply the same transaction concurrently.

  4. Because gtid_next is not empty, the slave does not attempt to generate a GTID for this transaction but instead writes the GTID stored in this variable—that is, the GTID obtained from the master—immediately preceding the transaction in its binary log.

17.1.3.2 Setting Up Replication Using GTIDs

This section describes a process for configuring and starting GTID-based replication in MySQL 5.6. 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.3, “Using GTIDs for Failover and Scaleout”.

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, binary logging, and slave update logging enabled, and with statements that are unsafe for GTID-based replication disabled. In addition, the servers should be started in read-only mode, and the slave SQL and I/O threads should be prevented from starting on the slave.

    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, and then start the slave.

    The SQL statements needed to accomplish this step are described in the example that follows later in this section.

  5. Disable read-only mode on both servers, so that they can once again accept updates.

In the following example, two servers are already running as master and slave, using MySQL's classic file-based replication protocol.

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.  Make the servers read-only. To do this, enable the read_only system variable by executing the following statement on both servers:

mysql> SET @@global.read_only = ON;

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.

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: Restart both servers with GTIDs enabled.  To enable binary logging with global transaction identifiers, each server must be started with GTID mode, binary logging, slave update logging enabled, and with statements that are unsafe for GTID-based replication disabled. In addition, you should prevent unwanted or accidental updates from being performed on either server by starting both in read-only mode. This means that both servers must be started with (at least) the options shown in the following invocation of mysqld_safe:

shell> mysqld_safe --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency & 
Note

Prior to MySQL 5.6.9, --enforce-gtid-consistency was named --disable-gtid-unsafe-statements.

In addition, you should start the slave with the --skip-slave-start option along with the other server options specified in the example just shown.

Note

--gtid-mode is not a boolean, but an enumeration. Use one of the values ON or OFF only, when setting this option. Using a numeric value such as 0 or 1 can lead to unexpected results.

For more information about the --gtid-mode and --enforce-gtid-consistency server options, see Section 17.1.4.5, “Global Transaction ID Options and Variables”.

Depending on your configuration, supply additional options to mysqld_safe or other mysqld startup script.

Step 4: Direct the slave to use the master.  Tell the slave to use the master as the replication data source, and to use GTID-based auto-positioning rather than file-based positioning. Execute a CHANGE MASTER TO statement on the slave, using the MASTER_AUTO_POSITION option to tell the slave that transactions will be 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. (If you need to revert from GTID-based replication to replication based on files and positions, you must use one or both of these options together with MASTER_AUTO_POSITION = 0 in the CHANGE MASTER TO statement.)

Assuming that the CHANGE MASTER TO statement has succeeded, you can then start the slave, like this:

mysql> START SLAVE;

Step 5: Disable read-only mode.  Allow the master to begin accepting updates once again by running 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.3, “Using GTIDs for Failover and Scaleout”, discusses creation of new slaves when using GTIDs.

17.1.3.3 Using GTIDs for Failover and Scaleout

There are a number of techniques when using MySQL Replication with Global Transaction Identifiers (GTIDs) in MySQL 5.6.9 and later for provisioning a new slave which can then be used for scaleout, being promoted to master as necessary for failover. In this section, we discuss the four techniques listed here:

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.  This is the easiest way to reproduce all identifiers and transactions on a new server; you simply 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.2, “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.  Playing back the entire transaction history can be time-consuming, and represents 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 master contains is imported to the slave. The the binary log is played back, after which replication can be started, allowing the slave to become current with any remaining transactions.

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 SetTransaction History
  • Use the mysql client to import a dump file created with mysqldump. Use the --master-data option to include binary logging information and --set-gtid-purged (available in MySQL 5.6.9 and later) to AUTO (the default) or ON, to include information about executed transactions. You should have --gtid-mode=ON while importing the dump on the slave. (Bug #14832472)

  • Stop the slave, copy the contents of the master's data directory to the slave's data directory, then restart the slave.

If gtid_mode is not ON, restart the server with GTID mode enabled.

See also 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.

Note

Prior to MySQL 5.6.9, gtid_purged was not settable. (Bug #14797808)

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.

17.1.3.4 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.

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 is not safe for statement-based replication. When using row-based replication, this statement is actually logged as two separate events—one for the creation of the table, and another for the insertion of rows from the source table into the new table just created. When this statement is executed within a transaction, it is possible in some cases for these two events to receive the same transaction identifier, which means that the transaction containing the inserts is skipped by the slave. Therefore, CREATE TABLE ... SELECT is not supported when using GTID-based replication.

Temporary tables.  CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are not supported inside transactions when using GTIDs (that is, when the server was started with the --enforce-gtid-consistency option). It is possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1.

Preventing execution of unsupported statements.  In order 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.

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

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.

GTID mode and mysqldump.  In MySQL 5.6.9 and later, 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.

Prior to MySQL 5.6.9, mysqldump did not record global transaction IDs, and it was necessary to use the binary log and mysqlbinlog to restore GTIDs. (Bug #14797808, Bug #14832472)

GTID mode and mysql_upgrade.  Prior to MySQL 5.6.7, mysql_upgrade could not connect to a MySQL Server that was running with --gtid-mode=ON unless mysql_upgrade was run with --write-binlog=OFF. (Otherwise, mysqld had to be restarted with --gtid-mode=OFF before running mysql_upgrade, then restarted with --gtid_mode=ON afterwards.) This is not an issue in MySQL 5.6.7 and later, where mysql_upgrade runs with --write-binlog=OFF by default. (Bug #13833710) However, it is not recommended to do so, since mysql_upgrade can make changes to system tables that use the MyISAM storage engine, which is nontransactional.

17.1.4 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. 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.

Command-Line Format--server-id=#
System VariableNameserver_id
Variable ScopeGlobal
Dynamic VariableYes
Permitted ValuesTypenumeric
Default0
Min Value0
Max Value4294967295

This option is common to both master and slave replication servers, and is used in replication to enable master and slave servers to identify themselves uniquely. For additional information, see Section 17.1.4.2, “Replication Master Options and Variables”, and Section 17.1.4.3, “Replication Slave Options and Variables”.

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 232 – 1. Unique, means that each ID must be different from every other ID in use by any other replication master or slave. For example, server-id=3.

If you omit --server-id, the default ID is 0, in which case the master refuses connections from all slaves, and slaves refuse to connect to the master. In MySQL 5.6, whether the server ID is set to 0 explicitly or the default is allowed to be used, the server sets the server_id system variable to 1; this is a known issue that is fixed in MySQL 5.7.

For more information, see Section 17.1.1.2, “Setting the Replication Slave Configuration”.

server_uuid

Beginning with MySQL 5.6, the server generates a true UUID in addition to the --server-id supplied by the user. This is available as the global, read-only variable server_uuid.

Introduced5.6.0
System VariableNameserver_uuid
Variable ScopeGlobal
Dynamic VariableNo
Permitted ValuesTypestring

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. In MySQL 5.6, 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.

Also beginning with MySQL 5.6, when using MySQL replication, masters and slaves know one another'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 (but not before), 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.

In MySQL 5.6.5 and later, 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:

Note

The addition of the server_uuid system variable in MySQL 5.6 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.

17.1.4.1 Replication and Binary Logging Option and Variable Reference

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

Table 17.1 Summary of Replication options and variables in MySQL 5.6

Option or Variable Name
Command Line System Variable Status Variable
Option File Scope Dynamic
Notes

Com_change_master

No No Yes
No Both No

DESCRIPTION: Count of CHANGE MASTER statements

Com_show_master_status

No No Yes
No Both No

DESCRIPTION: Count of SHOW MASTER STATUS statements

Com_show_new_master

No No Yes
No Both No

DESCRIPTION: Count of SHOW NEW MASTER statements

Com_show_slave_hosts

No No Yes
No Both No

DESCRIPTION: Count of SHOW SLAVE HOSTS statements

Com_show_slave_status

No No Yes
No Both No

DESCRIPTION: Count of SHOW SLAVE STATUS statements

Com_slave_start

No No Yes
No Both No

DESCRIPTION: Count of START SLAVE statements

Com_slave_stop

No No Yes
No Both No

DESCRIPTION: Count of STOP SLAVE statements

Rpl_semi_sync_master_clients

No No Yes
No Global No

DESCRIPTION: Number of semisynchronous slaves

Rpl_semi_sync_master_net_avg_wait_time

No No Yes
No Global No

DESCRIPTION: The average time the master waited for a slave reply

Rpl_semi_sync_master_net_wait_time

No No Yes
No Global No

DESCRIPTION: The total time the master waited for slave replies

Rpl_semi_sync_master_net_waits

No No Yes
No Global No

DESCRIPTION: The total number of times the master waited for slave replies

Rpl_semi_sync_master_no_times

No No Yes
No Global No

DESCRIPTION: Number of times the master turned off semisynchronous replication

Rpl_semi_sync_master_no_tx

No No Yes
No Global No

DESCRIPTION: Number of commits not acknowledged successfully

Rpl_semi_sync_master_status

No No Yes
No Global No

DESCRIPTION: Whether semisynchronous replication is operational on master

Rpl_semi_sync_master_timefunc_failures

No No Yes
No Global No

DESCRIPTION: Number of times the master failed when calling time functions

Rpl_semi_sync_master_tx_avg_wait_time

No No Yes
No Global No

DESCRIPTION: The average time the master waited for each transaction

Rpl_semi_sync_master_tx_wait_time

No No Yes
No Global No

DESCRIPTION: The total time the master waited for transactions

Rpl_semi_sync_master_tx_waits

No No Yes
No Global No

DESCRIPTION: The total number of times the master waited for transactions

Rpl_semi_sync_master_wait_pos_backtraverse

No No Yes
No Global No

DESCRIPTION: The total number of times the master waited for an event with binary coordinates lower than events waited for previously

Rpl_semi_sync_master_wait_sessions

No No Yes
No Global No

DESCRIPTION: Number of sessions currently waiting for slave replies

Rpl_semi_sync_master_yes_tx

No No Yes
No Global No

DESCRIPTION: Number of commits acknowledged successfully

Rpl_semi_sync_slave_status

No No Yes
No Global No

DESCRIPTION: Whether semisynchronous replication is operational on slave

slave_exec_mode

Yes Yes No
Yes Global Yes

DESCRIPTION: Allows for switching the slave thread between IDEMPOTENT mode (key and some other errors suppressed) and STRICT mode; STRICT mode is the default, except for MySQL Cluster, where IDEMPOTENT is always used

Slave_open_temp_tables

No No Yes
No Global No

DESCRIPTION: Number of temporary tables that the slave SQL thread currently has open

Slave_retried_transactions

No No Yes
No Global No

DESCRIPTION: The total number of times since startup that the replication slave SQL thread has retried transactions

Slave_running

No No Yes
No Global No

DESCRIPTION: The state of this server as a replication slave (slave I/O thread status)

abort-slave-event-count

Yes No No
Yes No

DESCRIPTION: Option used by mysql-test for debugging and testing of replication

binlog_gtid_recovery_simplified

Yes Yes No
Yes Global No

DESCRIPTION: Controls how binary logs are iterated during GTID recovery

disable-gtid-unsafe-statements

Yes Yes No
Yes Global No

DESCRIPTION: Obsolete: Replaced by --enforce-gtid-consistency in MySQL 5.6.9.

disable_gtid_unsafe_statements

Yes Yes No
Yes Global No

DESCRIPTION: Obsolete: Replaced by enforce_gtid_consistency in MySQL 5.6.9.

disconnect-slave-event-count

Yes No No
Yes No

DESCRIPTION: Option used by mysql-test for debugging and testing of replication

enforce-gtid-consistency

Yes Yes No
Yes Global No

DESCRIPTION: Prevents execution of statements that cannot be logged in a transactionally safe manner. These include CREATE TEMPORARY TABLE inside transactions and CREATE TABLE ... SELECT, as well as transactions or statements that update both transactional and nontransactional tables.

enforce_gtid_consistency

Yes Yes No
Yes Global No

DESCRIPTION: When true, execution of statements that cannot be logged in a transactionally safe manner is not permitted. Such statements include CREATE TEMPORARY TABLE inside transactions and CREATE TABLE ... SELECT, as well as transactions or statements that update both transactional and nontransactional tables. Read-only; set using --disable-gtid-unsafe-statements when starting the server.

gtid_done

No Yes No
No Both No

DESCRIPTION: Obsolete: Replaced by gtid_executed in MySQL 5.6.9.

gtid_executed

No Yes No
No Both No

DESCRIPTION: Global: All GTIDs in the binary log (global) or current transaction (session). Read-only.

gtid_lost

No Yes No
No Global No

DESCRIPTION: Obsolete: Replaced by gtid_purged in MySQL 5.6.9.

gtid-mode

Yes Yes No
Yes Global No

DESCRIPTION: ON enables GTIDs; OFF disables them. UPGRADE_STEP_1 and UPGRADE_STEP_2 are currently unsupported.

gtid_mode

No Yes No
No Global No

DESCRIPTION: Shows whether GTIDs are enabled. Read-only.

gtid_next

No Yes No
No Session Yes

DESCRIPTION: Specifies the GTID for the next statement to execute. See documentation for details.

gtid_owned

No Yes No
No Both No

DESCRIPTION: The set of GTIDs owned by this client (session), or by all clients, together with the thread ID of the owner (global). Read-only.

gtid_purged

No Yes No
No Global Yes

DESCRIPTION: The set of all groups that have been purged from the binary log.

init_slave

Yes Yes No
Yes Global Yes

DESCRIPTION: Statements that are executed when a slave connects to a master

log-slave-updates

Yes Yes No
Yes Global No

DESCRIPTION: This option tells the slave to log the updates performed by its SQL thread to its own binary log

log_slave_updates

Yes Yes No
Yes Global No

DESCRIPTION: Tells whether the slave should log the updates performed by its SQL thread to its own binary log. Read-only; set using the --log-slave-updates server option.

master-info-file

Yes No No
Yes No

DESCRIPTION: The location and name of the file that remembers the master and where the I/O replication thread is in the master's binary logs

master-info-repository

Yes No No
Yes No

DESCRIPTION: Whether to write master status information and replication I/O thread location in the master's binary logs to a file or table.

master_info_repository

Yes Yes No
Yes Global Yes

DESCRIPTION: Whether to write master status information and replication I/O thread location in the master's binary logs to a file or table.

master-retry-count

Yes No No
Yes No

DESCRIPTION: Number of tries the slave will make to connect to the master before giving up

relay-log

Yes Yes No
Yes Global No

DESCRIPTION: The location and base name to use for relay logs

relay_log_basename

No Yes No
No Global No

DESCRIPTION: Complete path to relay log, including filename

relay-log-index

Yes Yes No
Yes Global No

DESCRIPTION: The location and name to use for the file that keeps a list of the last relay logs

relay-log-info-file

Yes No No
Yes No

DESCRIPTION: The location and name of the file that remembers where the SQL replication thread is in the relay logs

relay_log_info_file

Yes Yes No
Yes Global No

DESCRIPTION: The name of the file in which the slave records information about the relay logs.

relay-log-info-repository

Yes No No
Yes No

DESCRIPTION: Whether to write the replication SQL thread's location in the relay logs to a file or a table.

relay_log_info_repository

No Yes No
No Global Yes

DESCRIPTION: Whether to write the replication SQL thread's location in the relay logs to a file or a table.

relay_log_index

Yes Yes No
Yes Global No

DESCRIPTION: The name of the relay log index file.

relay_log_purge

Yes Yes No
Yes Global Yes

DESCRIPTION: Determines whether relay logs are purged

relay-log-recovery

Yes No No
Yes No

DESCRIPTION: Enables automatic recovery of relay log files from master at startup

relay_log_recovery

Yes Yes No
Yes Global Yes

DESCRIPTION: Whether automatic recovery of relay log files from master at startup is enabled; must be enabled for a crash-proof slave.

relay_log_space_limit

Yes Yes No
Yes Global No

DESCRIPTION: Maximum space to use for all relay logs

replicate-do-db

Yes No No
Yes No

DESCRIPTION: Tells the slave SQL thread to restrict replication to the specified database

replicate-do-table

Yes No No
Yes No

DESCRIPTION: Tells the slave SQL thread to restrict replication to the specified table

replicate-ignore-db

Yes No No
Yes No

DESCRIPTION: Tells the slave SQL thread not to replicate to the specified database

replicate-ignore-table

Yes No No
Yes No

DESCRIPTION: Tells the slave SQL thread not to replicate to the specified table

replicate-rewrite-db

Yes No No
Yes No

DESCRIPTION: Updates to a database with a different name than the original

replicate-same-server-id

Yes No No
Yes No

DESCRIPTION: In replication, if set to 1, do not skip events having our server id

replicate-wild-do-table

Yes No No
Yes No

DESCRIPTION: Tells the slave thread to restrict replication to the tables that match the specified wildcard pattern

replicate-wild-ignore-table

Yes No No
Yes No

DESCRIPTION: Tells the slave thread not to replicate to the tables that match the given wildcard pattern

report-host

Yes Yes No
Yes Global No

DESCRIPTION: Host name or IP of the slave to be reported to the master during slave registration

report-password

Yes Yes No
Yes Global No

DESCRIPTION: An arbitrary password that the slave server should report to the master. Not the same as the password for the MySQL replication user account

report-port

Yes Yes No
Yes Global No

DESCRIPTION: Port for connecting to slave reported to the master during slave registration

report-user

Yes Yes No
Yes Global No

DESCRIPTION: An arbitrary user name that a slave server should report to the master. Not the same as the name used with the MySQL replication user account.

rpl_semi_sync_master_enabled

No Yes No
No Global Yes

DESCRIPTION: Whether semisynchronous replication is enabled on master

rpl_semi_sync_master_timeout

No Yes No
No Global Yes

DESCRIPTION: Number of milliseconds to wait for slave acknowledgment

rpl_semi_sync_master_trace_level

No Yes No
No Global Yes

DESCRIPTION: The semisynchronous replication debug trace level on the master

rpl_semi_sync_master_wait_no_slave

No Yes No
No Global Yes

DESCRIPTION: Whether master waits for timeout even with no slaves

rpl_semi_sync_slave_enabled

No Yes No
No Global Yes

DESCRIPTION: Whether semisynchronous replication is enabled on slave

rpl_semi_sync_slave_trace_level

No Yes No
No Global Yes

DESCRIPTION: The semisynchronous replication debug trace level on the slave

rpl_stop_slave_timeout

Yes Yes No
Yes Global Yes

DESCRIPTION: Set the number of seconds that STOP SLAVE waits before timing out.

server_uuid

No Yes No
No Global No

DESCRIPTION: The server's globally unique ID, automatically (re)generated at server start

show-slave-auth-info

Yes No No
Yes No

DESCRIPTION: Show user name and password in SHOW SLAVE HOSTS on this master

simplified_binlog_gtid_recovery

Yes Yes No
Yes Global No

DESCRIPTION: Controls how binary logs are iterated during GTID recovery

skip-slave-start

Yes No No
Yes No

DESCRIPTION: If set, slave is not autostarted

slave-load-tmpdir

Yes Yes No
Yes Global No

DESCRIPTION: The location where the slave should put its temporary files when replicating a LOAD DATA INFILE statement

slave-skip-errors

Yes Yes No
Yes Global No

DESCRIPTION: Tells the slave thread to continue replication when a query returns an error from the provided list

slave-checkpoint-group

Yes No No
Yes No

DESCRIPTION: Maximum number of transactions processed by a multi-threaded slave before a checkpoint operation is called to update progress status.

slave_checkpoint_group

Yes Yes No
Yes Global Yes

DESCRIPTION: Maximum number of transactions processed by a multi-threaded slave before a checkpoint operation is called to update progress status.

slave-checkpoint-period

Yes No No
Yes No

DESCRIPTION: Update progress status of multi-threaded slave and flush relay log info to disk after this number of milliseconds.

slave_checkpoint_period

Yes Yes No
Yes Global Yes

DESCRIPTION: Update progress status of multi-threaded slave and flush relay log info to disk after this number of milliseconds.

slave_compressed_protocol

Yes Yes No
Yes Global Yes

DESCRIPTION: Use compression on master/slave protocol

slave-max-allowed-packet

Yes No No
Yes No

DESCRIPTION: Maximum size, in bytes, of a packet that can be sent from a replication master to a slave; overrides max_allowed_packet.

slave_max_allowed_packet

No Yes No
No Global Yes

DESCRIPTION: Maximum size, in bytes, of a packet that can be sent from a replication master to a slave; overrides max_allowed_packet.

slave_net_timeout

Yes Yes No
Yes Global Yes

DESCRIPTION: Number of seconds to wait for more data from a master/slave connection before aborting the read

slave_parallel_workers

No Yes No
No Global Yes

DESCRIPTION: Number of worker threads for executing events in parallel. Set to 0 (the default) to disable slave multi-threading.

slave-parallel-workers

Yes No No
Yes No

DESCRIPTION: Number of worker threads for executing events in parallel. Set to 0 (the default) to disable slave multi-threading.

slave-pending-jobs-size-max

Yes No No
No No

DESCRIPTION: Maximum size of slave worker queues holding events not yet applied.

slave_pending_jobs_size_max

No Yes No
No Global Yes

DESCRIPTION: Maximum size of slave worker queues holding events not yet applied.

slave-rows-search-algorithms

Yes No No
Yes No

DESCRIPTION: Determines search algorithms used for slave update batching. Any 2 or 3 from the list INDEX_SEARCH, TABLE_SCAN, HASH_SCAN; the default is TABLE_SCAN,INDEX_SCAN.

slave_rows_search_algorithms

No Yes No
No Global Yes

DESCRIPTION: Determines search algorithms used for slave update batching. Any 2 or 3 from the list INDEX_SEARCH, TABLE_SCAN, HASH_SCAN; the default is TABLE_SCAN,INDEX_SCAN.

slave_transaction_retries

Yes Yes No
Yes Global Yes

DESCRIPTION: Number of times the slave SQL thread will retry a transaction in case it failed with a deadlock or elapsed lock wait timeout, before giving up and stopping

slave_type_conversions

Yes Yes No
Yes Global No

DESCRIPTION: Controls type conversion mode on replication slave. Value is a list of zero or more elements from the list: ALL_LOSSY, ALL_NON_LOSSY. Set to an empty string to disallow type conversions between master and slave.

sql_slave_skip_counter

No Yes No
No Global Yes

DESCRIPTION: Number of events from the master that a slave server should skip. Not compatible with GTID replication.

sync_binlog

Yes Yes No
Yes Global Yes

DESCRIPTION: Synchronously flush binary log to disk after every #th event

sync_master_info

Yes Yes No
Yes Global Yes

DESCRIPTION: Synchronize master.info to disk after every #th event.

sync_relay_log

Yes Yes No
Yes Global Yes

DESCRIPTION: Synchronize relay log to disk after every #th event.

sync_relay_log_info

Yes Yes No
Yes Global Yes

DESCRIPTION: Synchronize relay.info file to disk after every #th event.


Section 17.1.4.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.4.3, “Replication Slave Options and Variables”.

Table 17.2 Summary of Binary Logging options and variables in MySQL 5.6

Option or Variable Name
Command Line System Variable Status Variable
Option File Scope Dynamic
Notes

Binlog_cache_disk_use

No No Yes
No Global No

DESCRIPTION: Number of transactions that used a temporary file instead of the binary log cache

binlog_row_image

Yes Yes No
Yes Both Yes

DESCRIPTION: Use full or minimal images when logging row changes. Allowed values are full, minimal, and noblob.

binlog_rows_query_log_events

No Yes No
No Both Yes

DESCRIPTION: When TRUE, enables logging of rows query log events in row-based logging mode. FALSE by default. Do not enable when producing logs for pre-5.6.2 replication slaves or other readers.

Binlog_stmt_cache_disk_use

No No Yes
No Global No

DESCRIPTION: Number of nontransactional statements that used a temporary file instead of the binary log statement cache

Binlog_cache_use

No No Yes
No Global No

DESCRIPTION: Number of transactions that used the temporary binary log cache

Binlog_stmt_cache_use

No No Yes
No Global No

DESCRIPTION: Number of statements that used the temporary binary log statement cache

Com_show_binlog_events

No No Yes
No Both No

DESCRIPTION: Count of SHOW BINLOG EVENTS statements

Com_show_binlogs

No No Yes
No Both No

DESCRIPTION: Count of SHOW BINLOGS statements

binlog-checksum

Yes No No
Yes No

DESCRIPTION: Enable/disable binary log checksums

binlog_checksum

No Yes No
No Global Yes

DESCRIPTION: Enable/disable binary log checksums

binlog-do-db

Yes No No
Yes No

DESCRIPTION: Limits binary logging to specific databases

binlog_error_action

Yes Yes No
Yes Both Yes

DESCRIPTION: Controls what happens when the server cannot write to the binary log. Can assume one of two values: IGNORE_ERROR (default) means that the server logs the error but continues to process updates. ABORT_SERVER causes the server to shut down when the binary log is not writeable.

binlog-ignore-db

Yes No No
Yes No

DESCRIPTION: Tells the master that updates to the given database should not be logged to the binary log

binlogging_impossible_mode

Yes Yes No
Yes Both Yes

DESCRIPTION: Deprecated and will be removed in a future version. Use the renamed binlog_error_action instead.

binlog-row-event-max-size

Yes No No
Yes No

DESCRIPTION: Binary log max event size

binlog_cache_size

Yes Yes No
Yes Global Yes

DESCRIPTION: Size of the cache to hold the SQL statements for the binary log during a transaction

binlog_max_flush_queue_time

No Yes No
No Global Yes

DESCRIPTION: How long to read transactions before flushing to binary log

binlog_order_commits

No Yes No
No Global Yes

DESCRIPTION: Whether to commit in same order as writes to binary log

binlog_stmt_cache_size

Yes Yes No
Yes Global Yes

DESCRIPTION: Size of the cache to hold nontransactional statements for the binary log during a transaction

binlog_format

Yes Yes No
Yes Both Yes

DESCRIPTION: Specifies the format of the binary log

binlog-rows-query-log-events

Yes No No
Yes No

DESCRIPTION: Enables logging of rows query log events when using row-based logging. Disabled by default. Do not enable when producing logs for pre-5.6.2 slaves/readers.

binlog_direct_non_transactional_updates

Yes Yes No
Yes Both Yes

DESCRIPTION: Causes updates using statement format to nontransactional engines to be written directly to binary log. See documentation before using.

log_bin_basename

No Yes No
No Global No

DESCRIPTION: Complete path to binary log, including filename

log-bin-use-v1-row-events

Yes Yes No
Yes Global No

DESCRIPTION: Use version 1 binary log row events

log_bin_use_v1_row_events

Yes Yes No
Yes Global No

DESCRIPTION: Shows whether server is using version 1 binary log row events

master-verify-checksum

Yes No No
Yes No

DESCRIPTION: Cause master to examine checksums when reading from the binary log

master_verify_checksum

No Yes No
No Global Yes

DESCRIPTION: Cause master to read checksums from binary log.

max-binlog-dump-events

Yes No No
Yes No

DESCRIPTION: Option used by mysql-test for debugging and testing of replication

max_binlog_cache_size

Yes Yes No
Yes Global Yes

DESCRIPTION: Can be used to restrict the total size used to cache a multi-statement transaction

max_binlog_size

Yes Yes No
Yes Global Yes

DESCRIPTION: Binary log will be rotated automatically when size exceeds this value

max_binlog_stmt_cache_size

Yes Yes No
Yes Global Yes

DESCRIPTION: Can be used to restrict the total size used to cache all nontransactional statements during a transaction

slave-sql-verify-checksum

Yes No No
Yes No

DESCRIPTION: Cause slave to examine checksums when reading from the relay log

slave_sql_verify_checksum

No Yes No
No Global Yes

DESCRIPTION: Cause slave to examine checksums when reading from relay log.

sporadic-binlog-dump-fail

Yes No No
Yes No

DESCRIPTION: Option used by mysql-test for debugging and testing of replication


Section 17.1.4.4, “Binary Log 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.2.4, “The Binary Log”.

For information about the sql_log_bin and sql_log_off variables, see Section 5.1.4, “Server System Variables”.

For a table showing all command-line options, system and status variables used with mysqld, see Section 5.1.1, “Server Option and Variable Reference”.

17.1.4.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 232 – 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.4.4, “Binary Log Options and Variables”.

System Variables Used on Replication Masters

The following system variables are used to control replication masters:

  • auto_increment_increment

    System VariableNameauto_increment_increment
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default1
    Min Value1
    Max Value65535

    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.

    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

    System VariableNameauto_increment_offset
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default1
    Min Value1
    Max Value65535

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

    Note

    auto_increment_offset is also supported for use with NDB tables.

17.1.4.3 Replication Slave Options and Variables

Startup Options for Replication Slaves

Options for Logging Slave Status to Tables

Obsolete Replication Slave Options

System Variables Used on Replication Slaves

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 232 – 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.

  • --abort-slave-event-count

    Command-Line Format--abort-slave-event-count=#
    Permitted ValuesTypenumeric
    Default0
    Min Value0

    When this option is set to some positive integer value other than 0 (the default) it affects replication behavior as follows: After the slave SQL thread has started, value log events are permitted to be executed; after that, the slave SQL thread does not receive any more events, just as if the network connection from the master were cut. The slave thread continues to run, and the output from SHOW SLAVE STATUS displays Yes in both the Slave_IO_Running and the Slave_SQL_Running columns, but no further events are read from the relay log.

    This option is used internally by the MySQL test suite for replication testing and debugging. It is not intended for use in a production setting.

  • --disconnect-slave-event-count

    Command-Line Format--disconnect-slave-event-count=#
    Permitted ValuesTypenumeric
    Default0

    This option is used internally by the MySQL test suite for replication testing and debugging.

  • --log-slave-updates

    Command-Line Format--log-slave-updates
    System VariableNamelog_slave_updates
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultOFF

    Normally, a slave does not write to its own binary log any updates that are received from a master server. This option causes the slave to write the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. Prior to MySQL 5.5, the server would not start when using the --log-slave-updates option without also starting the server with the --log-bin option, and would fail with an error; in MySQL 5.6, only a warning is generated. (Bug #44663) --log-slave-updates is used when you want to chain replication servers. 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. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log.

  • --log-slow-slave-statements

    Removed5.6.11
    Command-Line Format--log-slow-slave-statements (<= 5.6.10)
    Permitted ValuesTypeboolean
    DefaultOFF

    When the slow query log is enabled, this option enables logging for queries that have taken more than long_query_time seconds to execute on the slave.

    This command-line option was removed in MySQL 5.6.11 and replaced by the log_slow_slave_statements system variable. The system variable can be set on the command line or in option files the same way as the option, so there is no need for any changes at server startup, but the system variable also makes it possible to examine or set the value at runtime.

  • --log-warnings[=level]

    Command-Line Format--log-warnings[=#]
    System Variable (<= 5.6.3)Namelog_warnings
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    System Variable (>= 5.6.4)Namelog_warnings
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typenumeric
    Default1
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms)Typenumeric
    Default1
    Min Value0
    Max Value18446744073709551615

    Causes the server to record more messages to the error log about what it is doing. With respect to replication, the server generates warnings that it succeeded in reconnecting after a network or connection failure, and provides information about how each slave thread started. This option is enabled (1) by default; to disable it, use --log-warnings=0. If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written. See Section B.5.2.11, “Communication Errors and Aborted Connections”.

    Note

    The effects of this option are not limited to replication. It produces warnings across a spectrum of server activities.

  • --master-info-file=file_name

    Command-Line Format--master-info-file=file_name
    Permitted ValuesTypefile name
    Defaultmaster.info

    The name to use for the file in which the slave records information about the master. The default name is master.info in the data directory. For information about the format of this file, see Section 17.2.2.2, “Slave Status Logs”.

  • --master-retry-count=count

    Deprecated5.6.1
    Command-Line Format--master-retry-count=#
    Permitted Values (32-bit platforms)Typenumeric
    Default86400
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms)Typenumeric
    Default86400
    Min Value0
    Max Value18446744073709551615

    The number of times that the slave tries to connect to the master before giving up. Reconnects are attempted at intervals set by the MASTER_CONNECT_RETRY option of the CHANGE MASTER TO statement (default 60). Reconnects are triggered when data reads by the slave time out according to the --slave-net-timeout option. The default value is 86400. A value of 0 means infinite; the slave attempts to connect forever.

    This option is deprecated as of MySQL 5.6.1 and will be removed in a future MySQL release. Applications should be updated to use the MASTER_RETRY_COUNT option of the CHANGE MASTER TO statement instead.

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

    Command-Line Format--max_relay_log_size=#
    System VariableNamemax_relay_log_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default0
    Min Value0
    Max Value1073741824

    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.2.1, “The Slave Relay Log”.

  • --read-only

    Command-Line Format--read-only
    System VariableNameread_only
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    Defaultfalse

    Cause the slave to permit no updates except from slave threads or from users having the SUPER privilege. On a slave server, this can be useful to ensure that the slave accepts updates only from its master server and not from clients. This variable does not apply to TEMPORARY tables.

  • --relay-log=file_name

    Command-Line Format--relay-log=file_name
    System VariableNamerelay_log
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The basename for the relay log. The default basename is host_name-relay-bin. The server writes the file in the data directory unless the basename is given with a leading absolute path name to specify a different directory. The server creates relay log files in sequence by adding a numeric suffix to the basename.

    Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default basename 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.3, “Specifying Program Options”.

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

    Starting with MySQL 5.6.5, 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 in 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. Prior to MySQL 5.6.5, 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.

    Beginning with MySQL 5.6.2, you can obtain the relay log filename (and path) from the relay_log_basename system variable.

  • --relay-log-index=file_name

    Command-Line Format--relay-log-index=file_name
    System VariableNamerelay_log_index
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The name to use for the relay log index file. The default name is host_name-relay-bin.index in the data directory, where host_name is the name of the slave server.

    Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default basename 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.3, “Specifying Program Options”.

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

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

    Command-Line Format--relay-log-info-file=file_name
    Permitted ValuesTypefile name
    Defaultrelay-log.info

    The name to use for the file in which the slave records information about the relay logs. The default name is relay-log.info in the data directory. For information about the format of this file, see Section 17.2.2.2, “Slave Status Logs”.

  • --relay-log-purge={0|1}

    Command-Line Format--relay_log_purge
    System VariableNamerelay_log_purge
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultTRUE

    Disable or enable automatic purging of relay logs as soon as they are no longer needed. The default value is 1 (enabled). This is a global variable that can be changed dynamically with SET GLOBAL relay_log_purge = N. Disabling purging of relay logs when using the --relay-log-recovery option risks data consistency and is therefore not crash-safe.

  • --relay-log-recovery={0|1}

    Command-Line Format--relay-log-recovery
    Permitted ValuesTypeboolean
    DefaultFALSE

    Enables automatic relay log recovery immediately following server startup. The recovery process creates a new relay log file, initializes the SQL thread position to this new relay log, and initializes the I/O thread to the SQL thread position. Reading of the relay log from the master then continues. This should be used following a crash on the replication slave to ensure that no possibly corrupted relay logs are processed. The default value is 0 (disabled).

    To provide a crash-proof slave, this option must be enabled (set to 1), --relay-log-info-repository must be set to TABLE, and relay-log-purge must be enabled. Enabling the --relay-log-recovery option when relay-log-purge is disabled risks reading the relay log from files that were not purged, leading to data inconsistency, and is therefore not crash-safe. See Crash-safe replication, for more information.

    Prior to MySQL 5.6.6, if this option is enabled for a multi-threaded slave, and the slave fails with errors, you cannot execute CHANGE MASTER TO on that slave. In MySQL 5.6.6 or later, you can use START SLAVE UNTIL SQL_AFTER_MTS_GAPS to ensure that any gaps in the relay log are processed; after running this statement, you can then use CHANGE MASTER TO to fail this slave over to a new master. (Bug #13893363)

  • --relay-log-space-limit=size

    Command-Line Format--relay_log_space_limit=#
    System VariableNamerelay_log_space_limit
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (32-bit platforms)Typenumeric
    Default0
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms)Typenumeric
    Default0
    Min Value0
    Max Value18446744073709551615

    This option places an upper limit on the total size in bytes of all relay logs on the slave. A value of 0 means no limit. This is useful for a slave server host that has limited disk space. When the limit is reached, the I/O thread stops reading binary log events from the master server until the SQL thread has caught up and deleted some unused relay logs. Note that this limit is not absolute: There are cases where the SQL thread needs more events before it can delete relay logs. In that case, the I/O thread exceeds the limit until it becomes possible for the SQL thread to delete some relay logs because not doing so would cause a deadlock. You should not set --relay-log-space-limit to less than twice the value of --max-relay-log-size (or --max-binlog-size if --max-relay-log-size is 0). In that case, there is a chance that the I/O thread waits for free space because --relay-log-space-limit is exceeded, but the SQL thread has no relay log to purge and is unable to satisfy the I/O thread. This forces the I/O thread to ignore --relay-log-space-limit temporarily.

  • --replicate-do-db=db_name

    Command-Line Format--replicate-do-db=name
    Permitted ValuesTypestring

    The effects of this option depend on whether statement-based or row-based replication is in use.

    Statement-based replication.  Tell the slave SQL thread to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database; however, doing so does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while a different database (or no database) is selected.

    Warning

    To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.

    An example of what does not work as you might expect when using statement-based replication: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated:

    USE prices;
    UPDATE sales.january SET amount=amount+1000;
    

    The main reason for this check just the default database behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table DELETE statements or multiple-table UPDATE statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need.

    Row-based replication.  Tells the slave SQL thread to restrict replication to database db_name. Only tables belonging to db_name are changed; the current database has no effect on this. Suppose that the slave is started with --replicate-do-db=sales and row-based replication is in effect, and then the following statements are run on the master:

    USE prices;
    UPDATE sales.february SET amount=amount+100;
    

    The february table in the sales database on the slave is changed in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued. However, issuing the following statements on the master has no effect on the slave when using row-based replication and --replicate-do-db=sales:

    USE prices;
    UPDATE prices.march SET amount=amount-25;
    

    Even if the statement USE prices were changed to USE sales, the UPDATE statement's effects would still not be replicated.

    Another important difference in how --replicate-do-db is handled in statement-based replication as opposed to row-based replication occurs with regard to statements that refer to multiple databases. Suppose that the slave is started with --replicate-do-db=db1, and the following statements are executed on the master:

    USE db1;
    UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;
    

    If you are using statement-based replication, then both tables are updated on the slave. However, when using row-based replication, only table1 is affected on the slave; since table2 is in a different database, table2 on the slave is not changed by the UPDATE. Now suppose that, instead of the USE db1 statement, a USE db4 statement had been used:

    USE db4;
    UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;
    

    In this case, the UPDATE statement would have no effect on the slave when using statement-based replication. However, if you are using row-based replication, the UPDATE would change table1 on the slave, but not table2—in other words, only tables in the database named by --replicate-do-db are changed, and the choice of default database has no effect on this behavior.

    If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

    Note

    This option affects replication in the same manner that --binlog-do-db affects binary logging, and the effects of the replication format on how --replicate-do-db affects replication behavior are the same as those of the logging format on the behavior of --binlog-do-db.

    This option has no effect on BEGIN, COMMIT, or ROLLBACK statements.

  • --replicate-ignore-db=db_name

    Command-Line Format--replicate-ignore-db=name
    Permitted ValuesTypestring

    As with --replicate-do-db, the effects of this option depend on whether statement-based or row-based replication is in use.

    Statement-based replication.  Tells the slave SQL thread not to replicate any statement where the default database (that is, the one selected by USE) is db_name.

    Row-based replication.  Tells the slave SQL thread not to update any tables in the database db_name. The default database has no effect.

    When using statement-based replication, the following example does not work as you might expect. Suppose that the slave is started with --replicate-ignore-db=sales and you issue the following statements on the master:

    USE prices;
    UPDATE sales.january SET amount=amount+1000;
    

    The UPDATE statement is replicated in such a case because --replicate-ignore-db applies only to the default database (determined by the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered. However, when using row-based replication, the UPDATE statement's effects are not propagated to the slave, and the slave's copy of the sales.january table is unchanged; in this instance, --replicate-ignore-db=sales causes all changes made to tables in the master's copy of the sales database to be ignored by the slave.

    To specify more than one database to ignore, use this option multiple times, once for each database. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.

    You should not use this option if you are using cross-database updates and you do not want these updates to be replicated. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

    If you need cross-database updates to work, use --replicate-wild-ignore-table=db_name.% instead. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

    Note

    This option affects replication in the same manner that --binlog-ignore-db affects binary logging, and the effects of the replication format on how --replicate-ignore-db affects replication behavior are the same as those of the logging format on the behavior of --binlog-ignore-db.

    This option has no effect on BEGIN, COMMIT, or ROLLBACK statements.

  • --replicate-do-table=db_name.tbl_name

    Command-Line Format--replicate-do-table=name
    Permitted ValuesTypestring

    Creates a replication filter by telling the slave SQL thread to restrict replication to a given table. To specify more than one table, use this option multiple times, once for each table. This works for both cross-database updates and default database updates, in contrast to --replicate-do-db. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

    This option affects only statements that apply to tables. It does not affect statements that apply only to other database objects, such as stored routines. To filter statements operating on stored routines, use one or more of the --replicate-*-db options.

  • --replicate-ignore-table=db_name.tbl_name

    Command-Line Format--replicate-ignore-table=name
    Permitted ValuesTypestring

    Creates a replication filter by telling the slave SQL thread not to replicate any statement that updates the specified table, even if any other tables might be updated by the same statement. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates, in contrast to --replicate-ignore-db. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

    This option affects only statements that apply to tables. It does not affect statements that apply only to other database objects, such as stored routines. To filter statements operating on stored routines, use one or more of the --replicate-*-db options.

  • --replicate-rewrite-db=from_name->to_name

    Command-Line Format--replicate-rewrite-db=old_name->new_name
    Permitted ValuesTypestring

    Tells the slave to create a replication filter that translates the default database (that is, the one selected by USE) to to_name if it was from_name on the master. Only statements involving tables are affected (not statements such as CREATE DATABASE, DROP DATABASE, and ALTER DATABASE), and only if from_name is the default database on the master. To specify multiple rewrites, use this option multiple times. The server uses the first one with a from_name value that matches. The database name translation is done before the --replicate-* rules are tested.

    Statements in which table names are qualified with database names when using this option do not work with table-level replication filtering options such as --replicate-do-table. Suppose we have a database named a on the master, one named b on the slave, each containing a table t, and have started the master with --replicate-rewrite-db='a->b'. At a later point in time, we execute DELETE FROM a.t. In this case, no relevant filtering rule works, for the reasons shown here:

    1. --replicate-do-table=a.t does not work because the slave has table t in database b.

    2. --replicate-do-table=b.t does not match the original statement and so is ignored.

    3. --replicate-do-table=*.t is handled identically to --replicate-do-table=a.t, and thus does not work, either.

    Similarly, the --replication-rewrite-db option does not work with cross-database updates.

    If you use this option on the command line and the > character is special to your command interpreter, quote the option value. For example:

    shell> mysqld --replicate-rewrite-db="olddb->newdb"
    
    Note

    Prior to MySQL 5.6.7, multi-threaded slaves did not honor this option correctly. (Bug #14232958)

  • --replicate-same-server-id

    Command-Line Format--replicate-same-server-id
    Permitted ValuesTypeboolean
    DefaultFALSE

    To be used on slave servers. Usually you should use the default setting of 0, to prevent infinite loops caused by circular replication. If set to 1, the slave does not skip events having its own server ID. Normally, this is useful only in rare configurations. Cannot be set to 1 if --log-slave-updates is used. By default, the slave I/O thread does not write binary log events to the relay log if they have the slave's server ID (this optimization helps save disk usage). If you want to use --replicate-same-server-id, be sure to start the slave with this option before you make the slave read its own events that you want the slave SQL thread to execute.

  • --replicate-wild-do-table=db_name.tbl_name

    Command-Line Format--replicate-wild-do-table=name
    Permitted ValuesTypestring

    Creates a replication filter by telling the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns. Patterns can contain the % and _ wildcard characters, which have the same meaning as for the LIKE pattern-matching operator. To specify more than one table, use this option multiple times, once for each table. This works for cross-database updates. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

    This option applies to tables, views, and triggers. It does not apply to stored procedures and functions, or events. To filter statements operating on the latter objects, use one or more of the --replicate-*-db options.

    Example: --replicate-wild-do-table=foo%.bar% replicates only updates that use a table where the database name starts with foo and the table name starts with bar.

    If the table name pattern is %, it matches any table name and the option also applies to database-level statements (CREATE DATABASE, DROP DATABASE, and ALTER DATABASE). For example, if you use --replicate-wild-do-table=foo%.%, database-level statements are replicated if the database name matches the pattern foo%.

    To include literal wildcard characters in the database or table name patterns, escape them with a backslash. For example, to replicate all tables of a database that is named my_own%db, but not replicate tables from the my1ownAABCdb database, you should escape the _ and % characters like this: --replicate-wild-do-table=my\_own\%db. If you use the option on the command line, you might need to double the backslashes or quote the option value, depending on your command interpreter. For example, with the bash shell, you would need to type --replicate-wild-do-table=my\\_own\\%db.

  • --replicate-wild-ignore-table=db_name.tbl_name

    Command-Line Format--replicate-wild-ignore-table=name
    Permitted ValuesTypestring

    Creates a replication filter which keeps the slave thread from replicating a statement in which any table matches the given wildcard pattern. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

    Example: --replicate-wild-ignore-table=foo%.bar% does not replicate updates that use a table where the database name starts with foo and the table name starts with bar.

    For information about how matching works, see the description of the --replicate-wild-do-table option. The rules for including literal wildcard characters in the option value are the same as for --replicate-wild-ignore-table as well.

  • --report-host=host_name

    Command-Line Format--report-host=host_name
    System VariableNamereport_host
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring

    The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server. Leave the value unset if you do not want the slave to register itself with the master.

    Note

    It is not sufficient for the master to simply read the IP address of the slave from the TCP/IP socket after the slave connects. Due to NAT and other routing issues, that IP may not be valid for connecting to the slave from the master or other hosts.

  • --report-password=password

    Command-Line Format--report-password=name
    System VariableNamereport_password
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring

    The account password of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server if the --show-slave-auth-info option is given.

    Although the name of this option might imply otherwise, --report-password is not connected to the MySQL user privilege system and so is not necessarily (or even likely to be) the same as the password for the MySQL replication user account.

  • --report-port=slave_port_num

    Command-Line Format--report-port=#
    System VariableNamereport_port
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (<= 5.6.4)Typenumeric
    Default0
    Min Value0
    Max Value65535
    Permitted Values (>= 5.6.5)Typenumeric
    Default[slave_port]
    Min Value0
    Max Value65535

    The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration. Set this only if the slave is listening on a nondefault port or if you have a special tunnel from the master or other clients to the slave. If you are not sure, do not use this option.

    Prior to MySQL 5.6.5, the default value for this option was 3306. In MySQL 5.6.5 and later, the value shown is the port number actually used by the slave (Bug #13333431). This change also affects the default value displayed by SHOW SLAVE HOSTS.

  • --report-user=user_name

    Command-Line Format--report-user=name
    System VariableNamereport_user
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring

    The account user name of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server if the --show-slave-auth-info option is given.

    Although the name of this option might imply otherwise, --report-user is not connected to the MySQL user privilege system and so is not necessarily (or even likely to be) the same as the name of the MySQL replication user account.

  • --show-slave-auth-info

    Command-Line Format--show-slave-auth-info
    Permitted ValuesTypeboolean
    DefaultFALSE

    Display slave user names and passwords in the output of SHOW SLAVE HOSTS on the master server for slaves started with the --report-user and --report-password options.

  • --slave-checkpoint-group=#

    Introduced5.6.3
    Command-Line Format--slave-checkpoint-group=#
    Permitted ValuesTypenumeric
    Default512
    Min Value32
    Max Value524280
    Block Size8

    Sets the maximum number of transactions that can be processed by a multi-threaded slave before a checkpoint operation is called to update its status as shown by SHOW SLAVE STATUS. Setting this option has no effect on slaves for which multi-threading is not enabled.

    This option works in combination with the --slave-checkpoint-period option in such a way that, when either limit is exceeded, the checkpoint is executed and the counters tracking both the number of transactions and the time elapsed since the last checkpoint are reset.

    The minimum allowed value for this option is 32, unless the server was built using -DWITH_DEBUG, in which case the minimum value is 1. The effective value is always a multiple of 8; you can set it to a value that is not such a multiple, but the server rounds it down to the next lower multiple of 8 before storing the value. (Exception: No such rounding is performed by the debug server.) Regardless of how the server was built, the default value is 512, and the maximum allowed value is 524280.

    --slave-checkpoint-group was added in MySQL 5.6.3.

  • --slave-checkpoint-period=#

    Introduced5.6.3
    Command-Line Format--slave-checkpoint-period=#
    Permitted ValuesTypenumeric
    Default300
    Min Value1
    Max Value4G

    Sets the maximum time (in milliseconds) that is allowed to pass before a checkpoint operation is called to update the status of a multi-threaded slave as shown by SHOW SLAVE STATUS. Setting this option has no effect on slaves for which multi-threading is not enabled.

    This option works in combination with the --slave-checkpoint-group option in such a way that, when either limit is exceeded, the checkpoint is executed and the counters tracking both the number of transactions and the time elapsed since the last checkpoint are reset.

    The minimum allowed value for this option is 1, unless the server was built using -DWITH_DEBUG, in which case the minimum value is 0. Regardless of how the server was built, the default value is 300, and the maximum possible value is 4294967296 (4GB).

    --slave-checkpoint-period was added in MySQL 5.6.3.

  • --slave-parallel-workers

    Introduced5.6.3
    Command-Line Format--slave-parallel-workers=#
    Permitted ValuesTypenumeric
    Default0
    Min Value0
    Max Value1024

    Sets the number of slave worker threads for executing replication events (transactions) in parallel. Setting this variable to 0 (the default) disables parallel execution. The maximum is 1024.

    When parallel execution is enabled, the slave SQL thread acts as the coordinator for the slave worker threads, among which transactions are distributed on a per-database basis. This means that a worker thread on the slave slave can process successive transactions on a given database without waiting for updates to other databases to complete. The current implementation of multi-threading on the slave assumes that the data is partitioned per database, and that updates within a given database occur in the same relative order as they do on the master, in order to work correctly. However, transactions do not need to be coordinated between any two databases.

    Due to the fact that transactions on different databases can occur in a different order on the slave than on the master, checking for the most recently executed transaction does not guarantee that all previous transactions from the master have been executed on the slave. This has implications for logging and recovery when using a multi-threaded slave. For information about how to interpret binary logging information when using multi-threading on the slave, see Section 13.7.5.35, “SHOW SLAVE STATUS Syntax”. In addition, this means that START SLAVE UNTIL is not supported with a multi-threaded slave.

    When multi-threading is enabled, slave_transaction_retries is treated as equal to 0, and cannot be changed. (Currently, retrying of transactions is not supported with multi-threaded slaves.)

    You should also note that, in MySQL 5.6.7 and later, enforcing foreign key relationships between tables in different databases causes multi-threaded slaves to use sequential rather than parallel mode, which can have a negative impact on performance. (Bug #14092635)

    This option was added in MySQL 5.6.3.

    Note

    The value set for this option (or for the corresponding slave_parallel_workers system variable) was not always honored correctly in MySQL 5.6.3; this problem was fixed in MySQL 5.6.4 (Bug #13334470).

  • --slave-pending-jobs-size-max=#

    Introduced5.6.3
    Command-Line Format--slave-pending-jobs-size-max=#
    Permitted ValuesTypenumeric
    Default16M
    Min Value1024
    Max Value18EB
    Block Size1024

    For multi-threaded slaves, this option sets the maximum amount of memory (in bytes) available to slave worker queues holding events not yet applied. Setting this option has no effect on slaves for which multi-threading is not enabled.

    The minimum possible value for this option is 1024; the default is 16MB. The maximum possible value is 18446744073709551615 (16 exabytes). Values that are not exact multiples of 1024 are rounded down to the next-highest multiple of 1024 prior to being stored.

    Important

    The value for this option must not be less than the master's value for max_allowed_packet; otherwise a slave worker queue may become full while there remain events coming from the master to be processed.

    This option was added in MySQL 5.6.3.

  • --skip-slave-start

    Command-Line Format--skip-slave-start
    Permitted ValuesTypeboolean
    DefaultFALSE

    Tells the slave server not to start the slave threads when the server starts. To start the threads later, use a START SLAVE statement.

  • --slave_compressed_protocol={0|1}

    Command-Line Format--slave_compressed_protocol
    System VariableNameslave_compressed_protocol
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    If this option is set to 1, use compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression).

  • --slave-load-tmpdir=file_name

    Command-Line Format--slave-load-tmpdir=path
    System VariableNameslave_load_tmpdir
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name
    Default/tmp

    The name of the directory where the slave creates temporary files. This option is by default equal to the value of the tmpdir system variable. When the slave SQL thread replicates a LOAD DATA INFILE statement, it extracts the file to be loaded from the relay log into temporary files, and then loads these into the table. If the file loaded on the master is huge, the temporary files on the slave are huge, too. Therefore, it might be advisable to use this option to tell the slave to put temporary files in a directory located in some file system that has a lot of available space. In that case, the relay logs are huge as well, so you might also want to use the --relay-log option to place the relay logs in that file system.

    The directory specified by this option should be located in a disk-based file system (not a memory-based file system) because the temporary files used to replicate LOAD DATA INFILE must survive machine restarts. The directory also should not be one that is cleared by the operating system during the system startup process.

  • slave-max-allowed-packet=bytes

    Introduced5.6.6
    Command-Line Format--slave-max-allowed-packet=#
    Permitted ValuesTypenumeric
    Default1073741824
    Min Value1024
    Max Value1073741824

    In MySQL 5.6.6 and later, this option sets the maximum packet size in bytes for the slave SQL and I/O threads, so that large updates using row-based replication do not cause replication to fail because an update exceeded max_allowed_packet. (Bug #12400221, Bug #60926)

    The corresponding server variable slave_max_allowed_packet always has a value that is a positive integer multiple of 1024; if you set it to some value that is not such a multiple, the value is automatically rounded down to the next highest multiple of 1024. (For example, if you start the server with --slave-max-allowed-packet=10000, the value used is 9216; setting 0 as the value causes 1024 to be used.) A truncation warning is issued in such cases.

    The maximum (and default) value is 1073741824 (1 GB); the minimum is 1024.

  • --slave-net-timeout=seconds

    Command-Line Format--slave-net-timeout=#
    System VariableNameslave_net_timeout
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default3600
    Min Value1

    The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement, and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds (one hour).

  • slave-rows-search-algorithms=list

    Introduced5.6.6
    Command-Line Format--slave-rows-search-algorithms=list
    Permitted ValuesTypeset
    DefaultTABLE_SCAN,INDEX_SCAN
    Valid ValuesTABLE_SCAN,INDEX_SCAN
    INDEX_SCAN,HASH_SCAN
    TABLE_SCAN,HASH_SCAN
    TABLE_SCAN,INDEX_SCAN,HASH_SCAN (equivalent to INDEX_SCAN,HASH_SCAN)

    When preparing batches of rows for row-based logging and replication using slave_allow_batching, this option controls how the rows are searched for matches—that is, whether or not hashing is used for searches using a primary or unique key, some other key, or no key at all. This option takes a comma-separated list of any 2 (or possibly 3) values from the list INDEX_SCAN, TABLE_SCAN, HASH_SCAN. The list need not be quoted, but must contain no spaces, whether or not quotes are used. Possible combinations (lists) and their effects are shown in the following table:

    Index used / option valueINDEX_SCAN,HASH_SCAN or INDEX_SCAN,TABLE_SCAN,HASH_SCANINDEX_SCAN,TABLE_SCANTABLE_SCAN,HASH_SCAN
    Primary key or unique keyIndex scanIndex scanHash scan over index
    (Other) KeyHash scan over indexIndex scanHash scan over index
    No indexHash scanTable scanHash scan

    The order in which the algorithms are specified in the list does not make any difference in the order in which they are displayed by a SELECT or SHOW VARIABLES statement (which is the same as that used in the table just shown previously).The default value is TABLE_SCAN,INDEX_SCAN, which means that all searches that can use indexes do use them, and searches without any indexes use table scans.

    Specifying INDEX_SCAN,TABLE_SCAN,HASH_SCAN has the same effect as specifying INDEX_SCAN,HASH_SCAN. To use hashing for any searches that does not use a primary or unique key, set this option to INDEX_SCAN,HASH_SCAN. To force hashing for all searches, set it to TABLE_SCAN,HASH_SCAN.

    This option was added in MySQL 5.6.6.

  • --slave-skip-errors=[err_code1,err_code2,...|all|ddl_exist_errors]

    Command-Line Format--slave-skip-errors=name
    System VariableNameslave_skip_errors
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring
    DefaultOFF
    Valid ValuesOFF
    [list of error codes]
    all
    Permitted ValuesTypestring
    DefaultOFF
    Valid ValuesOFF
    [list of error codes]
    all
    ddl_exist_errors
    Permitted ValuesTypestring
    DefaultOFF
    Valid ValuesOFF
    [list of error codes]
    all
    ddl_exist_errors

    Normally, replication stops when an error occurs on the slave. This gives you the opportunity to resolve the inconsistency in the data manually. This option tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the option value.

    Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of synchrony with the master, with you having no idea why this has occurred.

    For error codes, you should use the numbers provided by the error message in your slave error log and in the output of SHOW SLAVE STATUS. Appendix B, Errors, Error Codes, and Common Problems, lists server error codes.

    You can also (but should not) use the very nonrecommended value of all to cause the slave to ignore all error messages and keeps going regardless of what happens. Needless to say, if you use all, there are no guarantees regarding the integrity of your data. Please do not complain (or file bug reports) in this case if the slave's data is not anywhere close to what it is on the master. You have been warned.

    MySQL 5.6 as well as MySQL Cluster NDB 7.3 and later support an additional shorthand value ddl_exist_errors, which is equivalent to the error code list 1007,1008,1050,1051,1054,1060,1061,1068,1094,1146.

    Examples:

    --slave-skip-errors=1062,1053
    --slave-skip-errors=all
    --slave-skip-errors=ddl_exist_errors
    
  • --slave-sql-verify-checksum={0|1}

    Introduced5.6.2
    Command-Line Format--slave-sql-verify-checksum=value
    Permitted ValuesTypeboolean
    Default0
    Valid Values0
    1

    When this option is enabled, the slave examines checksums read from the relay log, in the event of a mismatch, the slave stops with an error. Disabled by default.

    This option was added in MySQL 5.6.2.

Options for Logging Slave Status to Tables

MySQL 5.6 and later supports logging of replication slave status information to tables rather than files. Writing of the master info log and the relay log info log can be configured separately using two server options added in MySQL 5.6.2 and listed here:

  • --master-info-repository={FILE|TABLE}

    Introduced5.6.2
    Command-Line Format--master-info-repository=FILE|TABLE
    Permitted ValuesTypestring
    DefaultFILE
    Valid ValuesFILE
    TABLE

    This option causes the server to write its master info log to a file or a table. The name of the file defaults to master.info; you can change the name of the file using the --master-info-file server option.

    The default value for this option is FILE. If you use TABLE, the log is written to the slave_master_info table in the mysql database.

    The --master-info-repository option was added in MySQL 5.6.2.

  • --relay-log-info-repository={FILE|TABLE}

    Introduced5.6.2
    Command-Line Format--relay-log-info-repository=FILE|TABLE
    Permitted ValuesTypestring
    DefaultFILE
    Valid ValuesFILE
    TABLE

    This option causes the server to log its relay log info to a file or a table. The name of the file defaults to relay-log.info; you can change the name of the file using the --relay-log-info-file server option.

    The default value for this option is FILE. If you use TABLE, the log is written to the slave_relay_log_info table in the mysql database.

    For replication to be crash-safe, this option must be set to TABLE; in additon, the --relay-log-recovery option must be enabled. See Crash-safe replication, for more information.

    The --relay-log-info-repository option was added in MySQL 5.6.2.

The info log tables and their contents are considered local to a given MySQL Server. In MySQL 5.6.9 and later, they are not replicated, and changes to them are not written to the binary log. (Bug #14741537)

For more information, see Section 17.2.2, “Replication Relay and Status Logs”.

Obsolete Replication Slave Options

The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.6, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options, you must use the CHANGE MASTER TO ... statement (see Section 13.4.2.1, “CHANGE MASTER TO Syntax”).

The options affected are shown in this list:

  • --master-host

  • --master-user

  • --master-password

  • --master-port

  • --master-connect-retry

  • --master-ssl

  • --master-ssl-ca

  • --master-ssl-capath

  • --master-ssl-cert

  • --master-ssl-cipher

  • --master-ssl-key

System Variables Used on Replication Slaves

The following list describes system variables for controlling replication slave servers. They can be set at server startup and some of them can be changed at runtime using SET. Server options used with replication slaves are listed earlier in this section.

  • init_slave

    Command-Line Format--init-slave=name
    System VariableNameinit_slave
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring

    This variable is similar to init_connect, but is a string to be executed by a slave server each time the SQL thread starts. The format of the string is the same as for the init_connect variable.

    Note

    The SQL thread sends an acknowledgment to the client before it executes init_slave. Therefore, it is not guaranteed that init_slave has been executed when START SLAVE returns. See Section 13.4.2.5, “START SLAVE Syntax”, for more information.

  • log_slow_slave_statements

    Introduced5.6.11
    System VariableNamelog_slow_slave_statements
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    When the slow query log is enabled, this variable enables logging for queries that have taken more than long_query_time seconds to execute on the slave. This variable was added in MySQL 5.6.11.

  • master_info_repository

    Introduced5.6.2
    Command-Line Format--master-info-repository=FILE|TABLE
    System VariableNamemaster_info_repository
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring
    DefaultFILE
    Valid ValuesFILE
    TABLE

    The setting of this variable determines whether the slave logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info).

    The setting of this variable also has a direct bearing on the effect had by the setting of the sync_master_info system variable; see that variable's description for further information.

    This variable was added in MySQL 5.6.2.

  • relay_log

    Command-Line Format--relay-log=file_name
    System VariableNamerelay_log
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The name of the relay log file.

  • relay_log_basename

    Introduced5.6.2
    System VariableNamerelay_log_basename
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name
    Defaultdatadir + '/' + hostname + '-relay-bin'

    Holds the name and complete path to the relay log file.

    The relay_log_basename system variable was added in MySQL 5.6.2.

  • relay_log_index

    Command-Line Format--relay-log-index
    System VariableNamerelay_log_index
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name
    Default*host_name*-relay-bin.index

    The name of the relay log index file. The default name is host_name-relay-bin.index in the data directory, where host_name is the name of the slave server.

  • relay_log_info_file

    Command-Line Format--relay-log-info-file=file_name
    System VariableNamerelay_log_info_file
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name
    Defaultrelay-log.info

    The name of the file in which the slave records information about the relay logs. The default name is relay-log.info in the data directory.

  • relay_log_info_repository

    Introduced5.6.2
    System VariableNamerelay_log_info_repository
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring
    DefaultFILE
    Valid ValuesFILE
    TABLE

    This variable determines whether the slave's position in the relay logs is written to a FILE (relay-log.info) or to a TABLE (mysql.slave_relay_log_info).

    The setting of this variable also has a direct bearing on the effect had by the setting of the sync_relay_log_info system variable; see that variable's descrption for further information.

    This variable was added in MySQL 5.6.2.

  • relay_log_recovery

    Command-Line Format--relay-log-recovery
    System Variable (<= 5.6.5)Namerelay_log_recovery
    Variable ScopeGlobal
    Dynamic VariableYes
    System Variable (>= 5.6.6)Namerelay_log_recovery
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultFALSE

    Enables automatic relay log recovery immediately following server startup. The recovery process creates a new relay log file, initializes the SQL thread position to this new relay log, and initializes the I/O thread to the SQL thread position. Reading of the relay log from the master then continues. In MySQL 5.6.5 and earlier, it was possible to change this global variable dynamically; beginning with MySQL 5.6.6, it is read-only. (Bug #13840948) Regardless of the MySQL Server version, its value can be changed by starting the slave with the --relay-log-recovery option, which should be used following a crash on the replication slave to ensure that no possibly corrupted relay logs are processed, and must be used in order to guarantee a crash-proof slave. The default value is 0 (disabled).

    When relay_log_recovery is enabled and the slave has stopped due to errors encountered while running in multi-threaded mode, you cannot execute CHANGE MASTER TO if there are any gaps in the log. Beginning with MySQL 5.6.6, you should use START SLAVE UNTIL SQL_AFTER_MTS_GAPS to ensure that all gaps are processed before switching back to single-threaded mode or executing a CHANGE MASTER TO statement.

  • rpl_stop_slave_timeout

    Introduced5.6.13
    Command-Line Format--rpl-stop-slave-timeout=seconds
    System VariableNamerpl_stop_slave_timeout
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (>= 5.6.13)Typeinteger
    Default31536000
    Min Value2
    Max Value31536000

    In MySQL 5.6.13 and later, you can control the length of time (in seconds) that STOP SLAVE waits before timing out by setting this variable. This can be used to avoid deadlocks between STOP SLAVE and other slave SQL statements using different client connections to the slave. The maximum and default value of rpl_stop_slave_timeout is 31536000 seconds (1 year). The minimum is 2 seconds.

  • slave_checkpoint_group

    Introduced5.6.3
    Command-Line Format--slave-checkpoint-group=#
    System VariableNameslave_checkpoint_group=#
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default512
    Min Value32
    Max Value524280
    Block Size8

    Sets the maximum number of transactions that can be processed by a multi-threaded slave before a checkpoint operation is called to update its status as shown by SHOW SLAVE STATUS. Setting this variable has no effect on slaves for which multi-threading is not enabled.

    This variable works in combination with the slave_checkpoint_period system variable in such a way that, when either limit is exceeded, the checkpoint is executed and the counters tracking both the number of transactions and the time elapsed since the last checkpoint are reset.

    The minimum allowed value for this variable is 32, unless the server was built using -DWITH_DEBUG, in which case the minimum value is 1. The effective value is always a multiple of 8; you can set it to a value that is not such a multiple, but the server rounds it down to the next lower multiple of 8 before storing the value. (Exception: No such rounding is performed by the debug server.) Regardless of how the server was built, the default value is 512, and the maximum allowed value is 524280.

    slave_checkpoint_group was added in MySQL 5.6.3.

  • slave_checkpoint_period

    Introduced5.6.3
    Command-Line Format--slave-checkpoint-period=#
    System VariableNameslave_checkpoint_period=#
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default300
    Min Value1
    Max Value4G

    Sets the maximum time (in milliseconds) that is allowed to pass before a checkpoint operation is called to update the status of a multi-threaded slave as shown by SHOW SLAVE STATUS. Setting this variable has no effect on slaves for which multi-threading is not enabled.

    This variable works in combination with the slave_checkpoint_group system variable in such a way that, when either limit is exceeded, the checkpoint is executed and the counters tracking both the number of transactions and the time elapsed since the last checkpoint are reset.

    The minimum allowed value for this variable is 1, unless the server was built using -DWITH_DEBUG, in which case the minimum value is 0. Regardless of how the server was built, the default value is 300, and the maximum possible value is 4294967296 (4GB).

    slave_checkpoint_period was added in MySQL 5.6.3.

  • slave_compressed_protocol

    Command-Line Format--slave_compressed_protocol
    System VariableNameslave_compressed_protocol
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Whether to use compression of the slave/master protocol if both the slave and the master support it.

  • slave_exec_mode

    Command-Line Format--slave-exec-mode=mode
    System VariableNameslave_exec_mode
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultSTRICT (ALL)
    DefaultIDEMPOTENT (NDB)
    Valid ValuesIDEMPOTENT
    STRICT

    Controls whether IDEMPOTENT or STRICT mode is used in replication conflict resolution and error checking. IDEMPOTENT mode causes suppression of duplicate-key and no-key-found errors.

    This mode is needed for multi-master replication, circular replication, and some other special replication scenarios for MySQL Cluster Replication. (See Section 18.6.10, “MySQL Cluster Replication: Multi-Master and Circular Replication”, and Section 18.6.11, “MySQL Cluster Replication Conflict Resolution”, for more information.) The mysqld supplied with MySQL Cluster ignores any value explicitly set for slave_exec_mode, and always treats it as IDEMPOTENT.

    In MySQL Server 5.6, STRICT mode is the default value. This should not be changed; currently, IDEMPOTENT mode is supported only by NDB.

  • slave_load_tmpdir

    Command-Line Format--slave-load-tmpdir=path
    System VariableNameslave_load_tmpdir
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name
    Default/tmp

    The name of the directory where the slave creates temporary files for replicating LOAD DATA INFILE statements.

  • slave_max_allowed_packet

    Introduced5.6.6
    System VariableNameslave_max_allowed_packet
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default1073741824
    Min Value1024
    Max Value1073741824

    In MySQL 5.6.6 and later, this variable sets the maximum packet size for the slave SQL and I/O threads, so that large updates using row-based replication do not cause replication to fail because an update exceeded max_allowed_packet.

    This global variable always has a value that is a positive integer multiple of 1024; if you set it to some value that is not, the value is rounded down to the next highest multiple of 1024 for it is stored or used; setting slave_max_allowed_packet to 0 causes 1024 to be used. (A truncation warning is issued in all such cases.) The default and maximum value is 1073741824 (1 GB); the minimum is 1024.

    slave_max_allowed_packet can also be set at startup, using the --slave-max-allowed-packet option.

  • slave_net_timeout

    Command-Line Format--slave-net-timeout=#
    System VariableNameslave_net_timeout
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default3600
    Min Value1

    The number of seconds to wait for more data from a master/slave connection before aborting the read.

  • slave_parallel_workers

    Introduced5.6.3
    Command-Line Format--slave-parallel-workers=#
    System VariableNameslave_parallel_workers
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default0
    Min Value0
    Max Value1024

    Sets the number of slave worker threads for executing replication events (transactions) in parallel. Setting this variable to 0 (the default) disables parallel execution. The maximum is 1024.

    When parallel execution is enabled, the slave SQL thread acts as the coordinator for the slave worker threads, among which transactions are distributed on a per-database basis. This means that a worker thread on the slave slave can process successive transactions on a given database without waiting for updates to other databases to complete. The current implementation of multi-threading on the slave assumes that the data is partitioned per database, and that updates within a given database occur in the same relative order as they do on the master, in order to work correctly. However, transactions do not need to be coordinated between any two databases.

    Due to the fact that transactions on different databases can occur in a different order on the slave than on the master, checking for the most recently executed transaction does not guarantee that all previous transactions from the master have been executed on the slave. This has implications for logging and recovery when using a multi-threaded slave. For information about how to interpret binary logging information when using multi-threading on the slave, see Section 13.7.5.35, “SHOW SLAVE STATUS Syntax”. In addition, this means that START SLAVE UNTIL is not supported with a multi-threaded slave.

    When multi-threading is enabled, slave_transaction_retries is treated as equal to 0, and cannot be changed. (Currently, retrying of transactions is not supported with multi-threaded slaves.)

    This variable was added in MySQL 5.6.3.

    Note

    The value set for this variable (or for the corresponding --slave-parallel-workers option) was not always honored correctly in MySQL 5.6.3; this problem was fixed in MySQL 5.6.4 (Bug #13334470).

  • slave_pending_jobs_size_max

    Introduced5.6.3
    System VariableNameslave_pending_jobs_size_max
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default0
    Min Value1024
    Max Value18EB
    Block Size1024

    For multi-threaded slaves, this variable sets the maximum amount of memory (in bytes) available to slave worker queues holding events not yet applied. Setting this variable has no effect on slaves for which multi-threading is not enabled.

    The minimum possible value for this variable is 1024; the default is 16MB. The maximum possible value is 18446744073709551615 (16 exabytes). Values that are not exact multiples of 1024 are rounded down to the next-highest multiple of 1024 prior to being stored.

    Important

    The value of this variable must not be less than the master's value for max_allowed_packet; otherwise a slave worker queue may become full while there remain events coming from the master to be processed.

    slave_pending_jobs_size_max was added in MySQL 5.6.3.

  • slave_rows_search_algorithms

    Introduced5.6.6
    System VariableNameslave_rows_search_algorithms=list
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeset
    DefaultTABLE_SCAN,INDEX_SCAN
    Valid ValuesTABLE_SCAN,INDEX_SCAN
    INDEX_SCAN,HASH_SCAN
    TABLE_SCAN,HASH_SCAN
    TABLE_SCAN,INDEX_SCAN,HASH_SCAN (equivalent to INDEX_SCAN,HASH_SCAN)

    When preparing batches of rows for row-based logging and replication using slave_allow_batching, the slave_rows_search_algorithms variable controls how the rows are searched for matches—that is, whether or not hashing is used for searches using a primary or unique key, using some other key, or using no key at all. This option takes a comma-separated list of at least 2 values from the list INDEX_SCAN, TABLE_SCAN, HASH_SCAN. The value expected as a string, so the value must be quoted. In addition, the value must not contain any spaces. Possible combinations (lists) and their effects are shown in the following table:

    Index used / option valueINDEX_SCAN,HASH_SCAN or INDEX_SCAN,TABLE_SCAN,HASH_SCANINDEX_SCAN,TABLE_SCANTABLE_SCAN,HASH_SCAN
    Primary key or unique keyIndex scanindex scanIndex hash
    (Other) KeyIndex hashIndex scanIndex hash
    No indexTable hashTable scanTable hash

    The order in which the algorithms are specified in the list does not make any difference in the order in which they are displayed by a SELECT or SHOW VARIABLES statement, as shown here:

    mysql> SET GLOBAL slave_rows_search_algorithms = "INDEX_SCAN,TABLE_SCAN";
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW VARIABLES LIKE '%algorithms%';
    +------------------------------+-----------------------+
    | Variable_name                | Value                 |
    +------------------------------+-----------------------+
    | slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
    +------------------------------+-----------------------+
    1 row in set (0.00 sec)
    
    mysql> SET GLOBAL slave_rows_search_algorithms = "TABLE_SCAN,INDEX_SCAN";
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW VARIABLES LIKE '%algorithms%';
    +------------------------------+-----------------------+
    | Variable_name                | Value                 |
    +------------------------------+-----------------------+
    | slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
    +------------------------------+-----------------------+
    1 row in set (0.00 sec)
    

    The default value is TABLE_SCAN,INDEX_SCAN, which means that all searches that can use indexes do use them, and searches without any indexes use table scans.

    Specifying INDEX_SCAN,TABLE_SCAN,HASH_SCAN has the same effect as specifying INDEX_SCAN,HASH_SCAN. To use hashing for any searches that does not use a primary or unique key, set this variable to INDEX_SCAN,HASH_SCAN. To force hashing for all searches, set it to TABLE_SCAN,HASH_SCAN.

    This variable was added in MySQL 5.6.6.

  • slave_skip_errors

    Command-Line Format--slave-skip-errors=name
    System VariableNameslave_skip_errors
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring
    DefaultOFF
    Valid ValuesOFF
    [list of error codes]
    all
    Permitted ValuesTypestring
    DefaultOFF
    Valid ValuesOFF
    [list of error codes]
    all
    ddl_exist_errors
    Permitted ValuesTypestring
    DefaultOFF
    Valid ValuesOFF
    [list of error codes]
    all
    ddl_exist_errors

    Normally, replication stops when an error occurs on the slave. This gives you the opportunity to resolve the inconsistency in the data manually. This variable tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the variable value.

  • slave_sql_verify_checksum

    Introduced5.6.2
    System VariableNameslave_sql_verify_checksum
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    Default1
    Valid Values0
    1

    Cause the slave SQL thread to verify data using the checksums read from the relay log. In the event of a mismatch, the slave stops with an error.

    Note

    The slave I/O thread always reads checksums if possible when accepting events from over the network.

    slave_sql_verify_checksum was added in MySQL 5.6.2.

  • slave_transaction_retries

    Command-Line Format--slave_transaction_retries=#
    System VariableNameslave_transaction_retries
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typenumeric
    Default10
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms)Typenumeric
    Default10
    Min Value0
    Max Value18446744073709551615

    If a replication slave SQL thread fails to execute a transaction because of an InnoDB deadlock or because the transaction's execution time exceeded InnoDB's innodb_lock_wait_timeout or NDB's TransactionDeadlockDetectionTimeout or TransactionInactiveTimeout, it automatically retries slave_transaction_retries times before stopping with an error. The default value is 10.

    Transactions cannot be retried when using a multi-threaded slave. In other words, whenever slave_parallel_workers is greater than 0, slave_transaction_retries is treated as equal to 0, and cannot be changed.

  • slave_type_conversions

    Command-Line Format--slave_type_conversions=set
    System VariableNameslave_type_conversions
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (<= 5.6.12)Typeset
    Default
    Valid ValuesALL_LOSSY
    ALL_NON_LOSSY
    Permitted Values (>= 5.6.13)Typeset
    Default
    Valid ValuesALL_LOSSY
    ALL_NON_LOSSY
    ALL_SIGNED
    ALL_UNSIGNED

    Controls the type conversion mode in effect on the slave when using row-based replication. In MySQL 5.6.13 and later, its value is a comma-delimited set of zero or more elements from the list: ALL_LOSSY, ALL_NON_LOSSY, ALL_SIGNED, ALL_UNSIGNED. Set this variable to an empty string to disallow type conversions between the master and the slave. Changes require a restart of the slave to take effect.

    ALL_SIGNED and ALL_UNSIGNED were added in MySQL 5.6.13 (Bug#15831300). For additional information on type conversion modes applicable to attribute promotion and demotion in row-based replication, see Row-based replication: attribute promotion and demotion.

  • sql_slave_skip_counter

    System VariableNamesql_slave_skip_counter
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric

    The number of events from the master that a slave server should skip.

    This option is incompatible with GTID-based replication, and must not be set to a nonzero value when --gtid-mode=ON. In MySQL 5.6.10 and later, trying to do so is specifically disallowed. (Bug #15833516) If you need to skip transactions when employing GTIDs, use gtid_executed from the master instead. See Injecting empty transactions, for information about how to do this.

    Important

    If skipping the number of events specified by setting this variable would cause the slave to begin in the middle of an event group, the slave continues to skip until it finds the beginning of the next event group and begins from that point. For more information, see Section 13.4.2.4, “SET GLOBAL sql_slave_skip_counter Syntax”.

  • sync_master_info

    Command-Line Format--sync-master-info=#
    System VariableNamesync_master_info
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms, <= 5.6.5)Typenumeric
    Default0
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms, <= 5.6.5)Typenumeric
    Default0
    Min Value0
    Max Value18446744073709551615
    Permitted Values (32-bit platforms, >= 5.6.6)Typenumeric
    Default10000
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms, >= 5.6.6)Typenumeric
    Default10000
    Min Value0
    Max Value18446744073709551615

    The effects of this variable on a replication slave depend on whether the slave's master_info_repository is set to FILE or TABLE, as explained in the following paragraphs.

    master_info_repository = FILE.  If the value of sync_master_info is greater than 0, the slave synchronizes its master.info file to disk (using fdatasync()) after every sync_master_info events. If it is 0, the MySQL server performs no synchronization of the master.info file to disk; instead, the server relies on the operating system to flush its contents periodically as with any other file.

    master_info_repository = TABLE.  If the value of sync_master_info is greater than 0, the slave updates its master info repository table after every sync_master_info events. If it is 0, the table is never updated.

    The default value for sync_master_info is 10000 as of MySQL 5.6.6, 0 before that.

  • sync_relay_log

    Command-Line Format--sync-relay-log=#
    System VariableNamesync_relay_log
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms, <= 5.6.5)Typenumeric
    Default0
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms, <= 5.6.5)Typenumeric
    Default0
    Min Value0
    Max Value18446744073709551615
    Permitted Values (32-bit platforms, >= 5.6.6)Typenumeric
    Default10000
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms, >= 5.6.6)Typenumeric
    Default10000
    Min Value0
    Max Value18446744073709551615

    If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk (using fdatasync()) after every sync_relay_log events are written to the relay log.

    Setting sync_relay_log to 0 causes no synchronization to be done to disk; in this case, the server relies on the operating system to flush the relay log's contents from time to time as for any other file.

    Prior to MySQL 5.6.6, 0 was the default for this variable. In MySQL 5.6. and later, the default is 10000.

    A value of 1 is the safest choice because in the event of a crash you lose at most one event from the relay log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

  • sync_relay_log_info

    Command-Line Format--sync-relay-log-info=#
    System VariableNamesync_relay_log_info
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms, <= 5.6.5)Typenumeric
    Default0
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms, <= 5.6.5)Typenumeric
    Default0
    Min Value0
    Max Value18446744073709551615
    Permitted Values (32-bit platforms, >= 5.6.6)Typenumeric
    Default10000
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms, >= 5.6.6)Typenumeric
    Default10000
    Min Value0
    Max Value18446744073709551615

    The effects of this variable on the slave depend on the server's relay_log_info_repository setting (FILE or TABLE), and if this is TABLE, additionally on whether the storage engine used by the relay log info table is transactional (such as InnoDB) or not (MyISAM). The effects of these factors on the behavior of the server for sync_relay_log_info values of zero and greater than zero are shown in the following table:

    sync_relay_log_inforelay_log_info_repository
    FILETABLE
    TransactionalNontransactional
    N > 0

    The slave synchronizes its relay-log.info file to disk (using fdatasync()) after every N transactions.

    The table is updated after each transaction. (N is effectively ignored.)

    The table is updated after every N events.

    0

    The MySQL server performs no synchronization of the relay-log.info file to disk; instead, the server relies on the operating system to flush its contents periodically as with any other file.

    The table is never updated.

    The default value for sync_relay_log_info is 10000 as of MySQL 5.6.6, 0 before that.

17.1.4.4 Binary Log Options and Variables

Startup Options Used with Binary Logging

System Variables Used with Binary Logging

You can use the mysqld options and system variables that are described in this section to affect the operation of the binary log as well as to control which statements are written to the binary log. For additional information about the binary log, see Section 5.2.4, “The Binary Log”. For additional information about using MySQL server options and system variables, see Section 5.1.3, “Server Command Options”, and Section 5.1.4, “Server System Variables”.

Startup Options Used with Binary Logging

The following list describes startup options for enabling and configuring the binary log. System variables used with binary logging are discussed later in this section.

  • --binlog-row-event-max-size=N

    Command-Line Format--binlog-row-event-max-size=#
    Permitted Values (32-bit platforms, <= 5.6.5)Typenumeric
    Default1024
    Min Value256
    Max Value4294967295
    Permitted Values (64-bit platforms, <= 5.6.5)Typenumeric
    Default1024
    Min Value256
    Max Value18446744073709551615
    Permitted Values (32-bit platforms, >= 5.6.6)Typenumeric
    Default8192
    Min Value256
    Max Value4294967295
    Permitted Values (64-bit platforms, >= 5.6.6)Typenumeric
    Default8192
    Min Value256
    Max Value18446744073709551615

    Specify the maximum size of a row-based binary log event, in bytes. Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256. The default is 8192 as of MySQL 5.6.6 and 1024 before that. See Section 17.1.2, “Replication Formats”.

  • --log-bin[=base_name]

    Command-Line Format--log-bin
    System VariableNamelog_bin
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    Enable binary logging. The server logs all statements that change data to the binary log, which is used for backup and replication. See Section 5.2.4, “The Binary Log”.

    The option value, if given, is the basename for the log sequence. The server creates binary log files in sequence by adding a numeric suffix to the basename. It is recommended that you specify a basename (see Section B.5.8, “Known Issues in MySQL”, for the reason). Otherwise, MySQL uses host_name-bin as the basename.

    In MySQL 5.6.5 and later, when the server reads an entry from the index file, it checks whether the entry contains a relative path, and if it does, the relative part of the path in replaced with the absolute path set using the --log-bin 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. Previous to MySQL 5.6.5, manual intervention was required whenever relocating the binary log or relay log files. (Bug #11745230, Bug #12133)

    Setting this option causes the log_bin system variable to be set to ON (or 1), and not to the basename. Beginning with MySQL 5.6.2, the binary log filename (with path) is available as the log_bin_basename system variable.

  • --log-bin-index[=file_name]

    Command-Line Format--log-bin-index=file_name
    Permitted ValuesTypefile name

    The index file for binary log file names. See Section 5.2.4, “The Binary Log”. If you omit the file name, and if you did not specify one with --log-bin, MySQL uses host_name-bin.index as the file name.

  • --log-bin-trust-function-creators[={0|1}]

    Command-Line Format--log-bin-trust-function-creators
    System VariableNamelog_bin_trust_function_creators
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultFALSE

    This option sets the corresponding log_bin_trust_function_creators system variable. If no argument is given, the option sets the variable to 1. log_bin_trust_function_creators affects how MySQL enforces restrictions on stored function and trigger creation. See Section 20.7, “Binary Logging of Stored Programs”.

  • --log-bin-use-v1-row-events[={0|1}]

    Introduced5.6.6
    Command-Line Format--log-bin-use-v1-row-events[={0|1}]
    System VariableNamelog_bin_use_v1_row_events
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (>= 5.6.6)Typeboolean
    Default0

    Version 2 binary log row events are available beginning with MySQL 5.6.6; however, Version 2 events cannot be read by previous MySQL Server releases. Setting this option to 1 causes mysqld to write the binary log using Version 1 logging events, which is the only version of binary log events used in previous releases, and thus produce binary logs that can be read by older slaves. Setting --log-bin-use-v1-row-events to 0 (the default) causes mysqld to use Version 2 binary log events.

    The value used for this option can be obtained from the read-only log_bin_use_v1_row_events system variable.

    --log-bin-use-v1-row-events is chiefly of interest when setting up replication conflict detection and resolution using NDB$EPOCH_TRANS() as the conflict detection function, which requires Version 2 binary log row events. Thus, this option and --ndb-log-transaction-id are not compatible.

    For more information, see Section 18.6.11, “MySQL Cluster Replication Conflict Resolution”.

  • --log-short-format

    Command-Line Format--log-short-format
    Permitted ValuesTypeboolean
    DefaultFALSE

    Log less information to the binary log and slow query log, if they have been activated.

Statement selection options.  The options in the following list affect which statements are written to the binary log, and thus sent by a replication master server to its slaves. There are also options for slave servers that control which statements received from the master should be executed or ignored. For details, see Section 17.1.4.3, “Replication Slave Options and Variables”.

  • --binlog-do-db=db_name

    Command-Line Format--binlog-do-db=name
    Permitted ValuesTypestring

    This option affects binary logging in a manner similar to the way that --replicate-do-db affects replication.

    The effects of this option depend on whether the statement-based or row-based logging format is in use, in the same way that the effects of --replicate-do-db depend on whether statement-based or row-based replication is in use. You should keep in mind that the format used to log a given statement may not necessarily be the same as that indicated by the value of binlog_format. For example, DDL statements such as CREATE TABLE and ALTER TABLE are always logged as statements, without regard to the logging format in effect, so the following statement-based rules for --binlog-do-db always apply in determining whether or not the statement is logged.

    Statement-based logging.  Only those statements are written to the binary log where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database; however, doing so does not cause cross-database statements such as UPDATE some_db.some_table SET foo='bar' to be logged while a different database (or no database) is selected.

    Warning

    To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, the list will be treated as the name of a single database if you supply a comma-separated list.

    An example of what does not work as you might expect when using statement-based logging: If the server is started with --binlog-do-db=sales and you issue the following statements, the UPDATE statement is not logged:

    USE prices;
    UPDATE sales.january SET amount=amount+1000;
    

    The main reason for this just check the default database behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table DELETE statements or multiple-table UPDATE statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need.

    Another case which may not be self-evident occurs when a given database is replicated even though it was not specified when setting the option. If the server is started with --binlog-do-db=sales, the following UPDATE statement is logged even though prices was not included when setting --binlog-do-db:

              
    USE sales;
    UPDATE prices.discounts SET percentage = percentage + 10;
    

    Because sales is the default database when the UPDATE statement is issued, the UPDATE is logged.

    Row-based logging.  Logging is restricted to database db_name. Only changes to tables belonging to db_name are logged; the default database has no effect on this. Suppose that the server is started with --binlog-do-db=sales and row-based logging is in effect, and then the following statements are executed:

    USE prices;
    UPDATE sales.february SET amount=amount+100;
    

    The changes to the february table in the sales database are logged in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued. However, when using the row-based logging format and --binlog-do-db=sales, changes made by the following UPDATE are not logged:

    USE prices;
    UPDATE prices.march SET amount=amount-25;
    

    Even if the USE prices statement were changed to USE sales, the UPDATE statement's effects would still not be written to the binary log.

    Another important difference in --binlog-do-db handling for statement-based logging as opposed to the row-based logging occurs with regard to statements that refer to multiple databases. Suppose that the server is started with --binlog-do-db=db1, and the following statements are executed:

    USE db1;
    UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;
    

    If you are using statement-based logging, the updates to both tables are written to the binary log. However, when using the row-based format, only the changes to table1 are logged; table2 is in a different database, so it is not changed by the UPDATE. Now suppose that, instead of the USE db1 statement, a USE db4 statement had been used:

    USE db4;
    UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;
    

    In this case, the UPDATE statement is not written to the binary log when using statement-based logging. However, when using row-based logging, the change to table1 is logged, but not that to table2—in other words, only changes to tables in the database named by --binlog-do-db are logged, and the choice of default database has no effect on this behavior.

  • --binlog-ignore-db=db_name

    Command-Line Format--binlog-ignore-db=name
    Permitted ValuesTypestring

    This option affects binary logging in a manner similar to the way that --replicate-ignore-db affects replication.

    The effects of this option depend on whether the statement-based or row-based logging format is in use, in the same way that the effects of --replicate-ignore-db depend on whether statement-based or row-based replication is in use. You should keep in mind that the format used to log a given statement may not necessarily be the same as that indicated by the value of binlog_format. For example, DDL statements such as CREATE TABLE and ALTER TABLE are always logged as statements, without regard to the logging format in effect, so the following statement-based rules for --binlog-ignore-db always apply in determining whether or not the statement is logged.

    Statement-based logging.  Tells the server to not log any statement where the default database (that is, the one selected by USE) is db_name.

    Prior to MySQL 5.6.12, this option caused any statements containing fully qualified table names not to be logged if there was no default database specified (that is, when SELECT DATABASE() returned NULL). In MySQL 5.6.12 and later, when there is no default database, no --binlog-ignore-db options are applied, and such statements are always logged. (Bug #11829838, Bug #60188)

    Row-based format.  Tells the server not to log updates to any tables in the database db_name. The current database has no effect.

    When using statement-based logging, the following example does not work as you might expect. Suppose that the server is started with --binlog-ignore-db=sales and you issue the following statements:

    USE prices;
    UPDATE sales.january SET amount=amount+1000;
    

    The UPDATE statement is logged in such a case because --binlog-ignore-db applies only to the default database (determined by the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered. However, when using row-based logging, the UPDATE statement's effects are not written to the binary log, which means that no changes to the sales.january table are logged; in this instance, --binlog-ignore-db=sales causes all changes made to tables in the master's copy of the sales database to be ignored for purposes of binary logging.

    To specify more than one database to ignore, use this option multiple times, once for each database. Because database names can contain commas, the list will be treated as the name of a single database if you supply a comma-separated list.

    You should not use this option if you are using cross-database updates and you do not want these updates to be logged.

Checksum options.  Beginning with MySQL 5.6.2, MySQL supports reading and writing of binary log checksums. These are enabled using the two options listed here:

  • --binlog-checksum={NONE|CRC32}

    Introduced5.6.2
    Command-Line Format--binlog-checksum=type
    Permitted Values (<= 5.6.5)Typestring
    DefaultNONE
    Valid ValuesNONE
    CRC32
    Permitted Values (>= 5.6.6)Typestring
    DefaultCRC32
    Valid ValuesNONE
    CRC32

    Enabling this option causes the master to write checksums for events written to the binary log. Set to NONE to disable, or the name of the algorithm to be used for generating checksums; currently, only CRC32 checksums are supported. As of MySQL 5.6.6, CRC32 is the default.

    This option was added in MySQL 5.6.2.

  • --master-verify-checksum={0|1}

    Introduced5.6.2
    Command-Line Format--master-verify-checksum=name
    Permitted ValuesTypeboolean
    DefaultOFF

    Enabling this option causes the master to verify events from the binary log using checksums, and to stop with an error in the event of a mismatch. Disabled by default.

    This option was added in MySQL 5.6.2.

To control reading of checksums by the slave (from the relay) log, use the --slave-sql-verify-checksum option.

Testing and debugging options.  The following binary log options are used in replication testing and debugging. They are not intended for use in normal operations.

  • --max-binlog-dump-events=N

    Command-Line Format--max-binlog-dump-events=#
    Permitted ValuesTypenumeric
    Default0

    This option is used internally by the MySQL test suite for replication testing and debugging.

  • --sporadic-binlog-dump-fail

    Command-Line Format--sporadic-binlog-dump-fail
    Permitted ValuesTypeboolean
    DefaultFALSE

    This option is used internally by the MySQL test suite for replication testing and debugging.

  • --binlog-rows-query-log-events

    Introduced5.6.2
    Command-Line Format--binlog-rows-query-log-events
    Permitted ValuesTypeboolean
    DefaultFALSE

    Added in MySQL 5.6.2, this option enables binlog_rows_query_log_events. Must be set to OFF (the default) when generating logs for a MySQL 5.6.1 or earlier slave server or version of mysqlbinlog.

System Variables Used with Binary Logging

The following list describes system variables for controlling binary logging. They can be set at server startup and some of them can be changed at runtime using SET. Server options used to control binary logging are listed earlier in this section. For information about the sql_log_bin and sql_log_off variables, see Section 5.1.4, “Server System Variables”.

  • binlog_cache_size

    Command-Line Format--binlog_cache_size=#
    System VariableNamebinlog_cache_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typenumeric
    Default32768
    Min Value4096
    Max Value4294967295
    Permitted Values (64-bit platforms)Typenumeric
    Default32768
    Min Value4096
    Max Value18446744073709551615

    The size of the cache to hold changes to the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (--log-bin option). If you often use large transactions, you can increase this cache size to get better performance. The Binlog_cache_use and Binlog_cache_disk_use status variables can be useful for tuning the size of this variable. See Section 5.2.4, “The Binary Log”.

    binlog_cache_size sets the size for the transaction cache only; the size of the statement cache is governed by the binlog_stmt_cache_size system variable.

  • binlog_checksum

    Introduced5.6.2
    System VariableNamebinlog_checksum
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.6.5)Typestring
    DefaultNONE
    Valid ValuesNONE
    CRC32
    Permitted Values (>= 5.6.6)Typestring
    DefaultCRC32
    Valid ValuesNONE
    CRC32

    When enabled, this variable causes the master to write a checksum for each event in the binary log. binlog_checksum supports the values NONE (disabled) and CRC32. The default is CRC32 as of MySQL 5.6.6, NONE before that.

    When binlog_checksum is disabled (value NONE), the server verifies that it is writing only complete events to the binary log by writing and checking the event length (rather than a checksum) for each event.

    Changing the value of this variable causes the binary log to be rotated; checksums are always written to an entire binary log file, and never to only part of one.

    This variable was added in MySQL 5.6.2.

    In MySQL 5.6.6 and later, setting this variable on the master to a value unrecognized by the slave causes the slave to set its own binlog_checksum value to NONE, and to stop replication with an error. (Bug #13553750, Bug #61096) If backward compatibility with older slaves is a concern, you may want to set the value explicitly to NONE.

  • binlog_direct_non_transactional_updates

    Command-Line Format--binlog_direct_non_transactional_updates[=value]
    System VariableNamebinlog_direct_non_transactional_updates
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Due to concurrency issues, a slave can become inconsistent when a transaction contains updates to both transactional and nontransactional tables. MySQL tries to preserve causality among these statements by writing nontransactional statements to the transaction cache, which is flushed upon commit. However, problems arise when modifications done to nontransactional tables on behalf of a transaction become immediately visible to other connections because these changes may not be written immediately into the binary log.

    The binlog_direct_non_transactional_updates variable offers one possible workaround to this issue. By default, this variable is disabled. Enabling binlog_direct_non_transactional_updates causes updates to nontransactional tables to be written directly to the binary log, rather than to the transaction cache.

    binlog_direct_non_transactional_updates works only for statements that are replicated using the statement-based binary logging format; that is, it works only when the value of binlog_format is STATEMENT, or when binlog_format is MIXED and a given statement is being replicated using the statement-based format. This variable has no effect when the binary log format is ROW, or when binlog_format is set to MIXED and a given statement is replicated using the row-based format.

    Important

    Before enabling this variable, you must make certain that there are no dependencies between transactional and nontransactional tables; an example of such a dependency would be the statement INSERT INTO myisam_table SELECT * FROM innodb_table. Otherwise, such statements are likely to cause the slave to diverge from the master.

    In MySQL 5.6, this variable has no effect when the binary log format is ROW or MIXED. (Bug #51291)

  • binlog_error_action

    Introduced5.6.22
    Command-Line Format--binlog_error_action[=value]
    System VariableNamebinlog_error_action
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultIGNORE_ERROR
    Valid ValuesIGNORE_ERROR
    ABORT_SERVER

    Controls what happens when the server cannot write to the binary log, which can cause the master's log to become inconsistent and replication slaves to lose synchronization. Previous releases used the name binlogging_impossible_mode.

    In MySQL 5.6, the default for binlog_error_action is IGNORE_ERROR, meaning the server logs the error, halts logging, and continues performing updates; this is to provide backward compatibility with older versions of the MySQL Server. Setting this variable to ABORT_SERVER makes the server halt logging and shut down whenever it cannot write to the binary log; this is the recommended setting, particularly in complex replication environments.

  • binlog_format

    Command-Line Format--binlog-format=format
    System VariableNamebinlog_format
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultSTATEMENT
    Valid ValuesROW
    STATEMENT
    MIXED
    Permitted ValuesTypeenumeration
    DefaultMIXED
    Valid ValuesROW
    STATEMENT
    MIXED
    Permitted ValuesTypeenumeration
    DefaultMIXED
    Valid ValuesROW
    STATEMENT
    MIXED
    Permitted ValuesTypeenumeration
    DefaultMIXED
    Valid ValuesROW
    STATEMENT
    MIXED
    Permitted ValuesTypeenumeration
    DefaultMIXED
    Valid ValuesROW
    STATEMENT
    MIXED
    Permitted ValuesTypeenumeration
    DefaultMIXED
    Valid ValuesROW
    STATEMENT
    MIXED

    This variable sets the binary logging format, and can be any one of STATEMENT, ROW, or MIXED. See Section 17.1.2, “Replication Formats”. binlog_format is set by the --binlog-format option at startup, or by the binlog_format variable at runtime.

    Note

    While you can change the logging format at runtime, it is not recommended that you change it while replication is ongoing. This is due in part to the fact that slaves do not honor the master's binlog_format setting; a given MySQL Server can change only its own logging format.

    In MySQL 5.6, the default format is STATEMENT. Exception: In MySQL Cluster NDB 7.3 and later, the default is MIXED; statement-based replication is not supported for MySQL Cluster.

    You must have the SUPER privilege to set either the global or session binlog_format value.

    The rules governing when changes to this variable take effect and how long the effect lasts are the same as for other MySQL server system variables. See Section 13.7.4, “SET Syntax”, for more information.

    When MIXED is specified, statement-based replication is used, except for cases where only row-based replication is guaranteed to lead to proper results. For example, this happens when statements contain user-defined functions (UDF) or the UUID() function. An exception to this rule is that MIXED always uses statement-based replication for stored functions and triggers.

    There are exceptions when you cannot switch the replication format at runtime:

    • From within a stored function or a trigger.

    • If the session is currently in row-based replication mode and has open temporary tables.

    • From within a transaction.

    Trying to switch the format in those cases results in an error.

    The binary log format affects the behavior of the following server options:

    These effects are discussed in detail in the descriptions of the individual options.

  • binlog_gtid_recovery_simplified

    Introduced5.6.23
    Command-Line Format--binlog-gtid-recovery-simplified
    System VariableNamebinlog_gtid_recovery_simplified
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultFALSE

    In MySQL version 5.6.21, this variable was added as simplified_binlog_gtid_recovery and in MySQL version 5.6.23 it was renamed to binlog_gtid_recovery_simplified.

    By default, when MySQL recovers from a crash it iterates through binary log files searching for GTID events starting from the oldest file, which can take a long time if you have a large number of binary log files. By enabling this option, the newest binary log file is instead searched for GTID events. If a Previous_gtids_log_event and Gtid_log_event are found, gtid_executed and gtid_purged are initialized using these events as usual during recovery. If no GTID events are found, a second scan then searches the oldest binary log file for GTID events. If neither of these files contain the GTID events, no more binary log files are searched, and gtid_executed and gtid_purged are set to the empty string.

  • binlogging_impossible_mode

    Introduced5.6.20
    Deprecated5.6.22
    Command-Line Format--binlogging_impossible_mode[=value]
    System VariableNamebinlogging_impossible_mode
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultIGNORE_ERROR
    Valid ValuesIGNORE_ERROR
    ABORT_SERVER

    This option is deprecated and will be removed in a future MySQL release. Use the renamed binlog_error_action to control what happens when the server cannot write to the binary log.

  • binlog_max_flush_queue_time

    Introduced5.6.6
    System VariableNamebinlog_max_flush_queue_time
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default0
    Min Value0
    Max Value100000

    How long in microseconds to keep reading transactions from the flush queue before proceeding with the group commit (and syncing the log to disk, if sync_binlog is greater than 0). If the value is 0 (the default), there is no timeout and the server keeps reading new transactions until the queue is empty.

    Normally, binlog_max_flush_queue_time can remain set to 0. If the server processes a large number of connections (for example, 100 or more) and many short transactions with low-latency requirements, it may be useful to set the value larger than 0 to force more frequent flushes to disk.

    This variable was added in MySQL 5.6.6.

  • binlog_order_commits

    Introduced5.6.6
    System VariableNamebinlog_order_commits
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

    If this variable is enabled (the default), transactions are committed in the same order they are written to the binary log. If disabled, transactions may be committed in parallel. In some cases, disabling this variable might produce a performance increment.

    This variable was added in MySQL 5.6.6.

  • binlog_row_image

    Introduced5.6.2
    Command-Line Format--binlog-row-image=image_type
    System VariableNamebinlog_row_image=image_type
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    Defaultfull
    Valid Valuesfull (Log all columns)
    minimal (Log only changed columns, and columns needed to identify rows)
    noblob (Log all columns, except for unneeded BLOB and TEXT columns)

    In MySQL row-based replication, each row change event contains two images, a before image whose columns are matched against when searching for the row to be updated, and an after image containing the changes. Normally, MySQL logs full rows (that is, all columns) for both the before and after images. However, it is not strictly necessary to include every column in both images, and we can often save disk, memory, and network usage by logging only those columns which are actually required.

    Note

    When deleting a row, only the before image is logged, since there are no changed values to propagate following the deletion. When inserting a row, only the after image is logged, since there is no existing row to be matched. Only when updating a row are both the before and after images required, and both written to the binary log.

    For the before image, it is necessary only that the minimum set of columns required to uniquely identify rows is logged. If the table containing the row has a primary key, then only the primary key column or columns are written to the binary log. Otherwise, if the table has a unique key all of whose columns are NOT NULL, then only the columns in the unique key need be logged. (If the table has neither a primary key nor a unique key without any NULL columns, then all columns must be used in the before image, and logged.) In the after image, it is necessary to log only the columns which have actually changed.

    In MySQL 5.6, you can cause the server to log full or minimal rows using the binlog_row_image system variable. This variable actually takes one of three possible values, as shown in the following list:

    • full: Log all columns in both the before image and the after image.

    • minimal: Log only those columns in the before image that are required to identify the row to be changed; log only those columns in the after image that are actually changed.

    • noblob: Log all columns (same as full), except for BLOB and TEXT columns that are not required to identify rows, or that have not changed.

    Note

    This variable is not supported by MySQL Cluster; setting it has no effect on the logging of NDB tables. (Bug #16316828)

    The default value is full. In MySQL 5.5 and earlier, full row images are always used for both before images and after images. If you need to replicate from a MySQL 5.6 (or later) master to a slave running a previous version of MySQL, the master should always use this value.

    When using minimal or noblob, deletes and updates are guaranteed to work correctly for a given table if and only if the following conditions are true for both the source and destination tables:

    • All columns must be present and in the same order; each column must use the same data type as its counterpart in the other table.

    • The tables must have identical primary key definitions.

    (In other words, the tables must be identical with the possible exception of indexes that are not part of the tables' primary keys.)

    If these conditions are not met, it is possible that the primary key column values in the destination table may prove insufficient to provide a unique match for a delete or update. In this event, no warning or error is issued; the master and slave silently diverge, thus breaking consistency.

    Setting this variable has no effect when the binary logging format is STATEMENT. When binlog_format is MIXED, the setting for binlog_row_image is applied to changes that are logged using row-based format, but this setting no effect on changes logged as statements.

    Setting binlog_row_image on either the global or session level does not cause an implicit commit; this means that this variable can be changed while a transaction is in progress without affecting the transaction.

  • binlog_rows_query_log_events

    Introduced5.6.2
    System VariableNamebinlog_rows_query_log_events
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultFALSE

    The binlog_rows_query_log_events system variable affects row-based logging only. When enabled, it causes a MySQL 5.6.2 or later server to write informational log events such as row query log events into its binary log. This information can be used for debugging and related purposes; such as obtaining the original query issued on the master when it cannot be reconstructed from the row updates.

    These events are normally ignored by MySQL 5.6.2 and later programs reading the binary log and so cause no issues when replicating or restoring from backup. This is not true for a mysqld or mysqlbinlog from MySQL 5.6.1 or earlier: When the older version of the program reading the log encounters an informational log event, it fails, and stops reading at that point. To make the binary log readable by slave replication MySQL servers and other readers (for example, mysqlbinlog) from a MySQL 5.6.1 or earlier distribution, binlog_rows_query_log_events must be disabled during logging.

  • binlog_stmt_cache_size

    Introduced5.6.1
    Command-Line Format--binlog_stmt_cache_size=#
    System VariableNamebinlog_stmt_cache_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typenumeric
    Default32768
    Min Value4096
    Max Value4294967295
    Permitted Values (64-bit platforms)Typenumeric
    Default32768
    Min Value4096
    Max Value18446744073709551615

    This variable determines the size of the cache for the binary log to hold nontransactional statements issued during a transaction. Separate binary log transaction and statement caches are allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (--log-bin option). If you often use large nontransactional statements during transactions, you can increase this cache size to get better performance. The Binlog_stmt_cache_use and Binlog_stmt_cache_disk_use status variables can be useful for tuning the size of this variable. See Section 5.2.4, “The Binary Log”.

    The binlog_cache_size system variable sets the size for the transaction cache.

  • log_bin

    System VariableNamelog_bin
    Variable ScopeGlobal
    Dynamic VariableNo

    Whether the binary log is enabled. If the --log-bin option is used, then the value of this variable is ON; otherwise it is OFF. This variable reports only on the status of binary logging (enabled or disabled); it does not actually report the value to which --log-bin is set.

    See Section 5.2.4, “The Binary Log”.

  • log_bin_basename

    Introduced5.6.2
    System VariableNamelog_bin_basename
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name
    Defaultdatadir + '/' + hostname + '-bin'

    Holds the name and complete path to the binary log file. Unlike the log_bin system variable, log_bin_basename reflects the name set with the --log-bin server option.

    The log_bin_basename system variable was added in MySQL 5.6.2.

  • log_bin_index

    Introduced5.6.4
    System VariableNamelog_bin_index
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name

    The index file for binary log file names.

    The log_bin_index system variable was added in MySQL 5.6.4.

  • log_bin_use_v1_row_events

    Introduced5.6.6
    Command-Line Format--log-bin-use-v1-row-events[={0|1}]
    System VariableNamelog_bin_use_v1_row_events
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (>= 5.6.6)Typeboolean
    Default0

    Shows whether Version 2 binary logging, available beginning with MySQL 5.6.6, is in use. A value of 1 shows that the server is writing the binary log using Version 1 logging events (the only version of binary log events used in MySQL 5.6.5 and previous MySQL Server releases), and thus producing a binary log that can be read by older slaves. 0 indicates that Version 2 binary log events are in use.

    This variable is read-only. To switch between Version 1 and Version 2 binary event binary logging, it is necessary to restart mysqld with the --log-bin-use-v1-row-events option.

    Other than when performing upgrades of MySQL Cluster Replication, --log-bin-use-v1-events is chiefly of interest when setting up replication conflict detection and resolution using NDB$EPOCH_TRANS(), which requires Version 2 binary row event logging. Thus, this option and --ndb-log-transaction-id are not compatible.

    Note

    MySQL Cluster NDB 7.3 and later use Version 2 binary log row events by default. You should keep this mind when planning upgrades or downgrades, and for setups using MySQL Cluster Replication.

    For more information, see Section 18.6.11, “MySQL Cluster Replication Conflict Resolution”.

  • log_slave_updates

    Command-Line Format--log-slave-updates
    System VariableNamelog_slave_updates
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultFALSE

    Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect. See Section 17.1.4, “Replication and Binary Logging Options and Variables”.

  • master_verify_checksum

    Introduced5.6.2
    System VariableNamemaster_verify_checksum
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Enabling this variable causes the master to examine checksums when reading from the binary log. master_verify_checksum is disabled by default; in this case, the master uses the event length from the binary log to verify events, so that only complete events are read from the binary log.

    This variable was added in MySQL 5.6.2.

  • max_binlog_cache_size

    Command-Line Format--max_binlog_cache_size=#
    System VariableNamemax_binlog_cache_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default18446744073709551615
    Min Value4096
    Max Value18446744073709551615

    If a transaction requires more than this many bytes of memory, the server generates a Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage error. The minimum value is 4096. The maximum possible value is 16EB (exabytes). The maximum recommended value is 4GB; this is due to the fact that MySQL currently cannot work with binary log positions greater than 4GB.

    Note

    Prior to MySQL 5.6.7, 64-bit Windows platforms truncated the stored value for this variable to 4G, even when it was set to a greater value (Bug #13961678).

    max_binlog_cache_size sets the size for the transaction cache only; the upper limit for the statement cache is governed by the max_binlog_stmt_cache_size system variable.

    In MySQL 5.6, the visibility to sessions of max_binlog_cache_size matches that of the binlog_cache_size system variable; in other words, changing its value effects only new sessions that are started after the value is changed.

  • max_binlog_size

    Command-Line Format--max_binlog_size=#
    System VariableNamemax_binlog_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default1073741824
    Min Value4096
    Max Value1073741824

    If a write to the binary log causes the current log file size to exceed the value of this variable, the server rotates the binary logs (closes the current file and opens the next one). The minimum value is 4096 bytes. The maximum and default value is 1GB.

    A transaction is written in one chunk to the binary log, so it is never split between several binary logs. Therefore, if you have big transactions, you might see binary log files larger than max_binlog_size.

    If max_relay_log_size is 0, the value of max_binlog_size applies to relay logs as well.

  • max_binlog_stmt_cache_size

    Introduced5.6.1
    Command-Line Format--max_binlog_stmt_cache_size=#
    System VariableNamemax_binlog_stmt_cache_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default18446744073709547520
    Min Value4096
    Max Value18446744073709547520

    If nontransactional statements within a transaction require more than this many bytes of memory, the server generates an error. The minimum value is 4096. The maximum and default values are 4GB on 32-bit platforms and 16EB (exabytes) on 64-bit platforms.

    Note

    Prior to MySQL 5.6.7, 64-bit Windows platforms truncated the stored value for this variable to 4G, even when it was set to a greater value (Bug #13961678).

    max_binlog_stmt_cache_size sets the size for the statement cache only; the upper limit for the transaction cache is governed exclusively by the max_binlog_cache_size system variable.

  • sync_binlog

    Command-Line Format--sync-binlog=#
    System VariableNamesync_binlog
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typenumeric
    Default0
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms)Typenumeric
    Default0
    Min Value0
    Max Value4294967295

    If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after sync_binlog commit groups are written to the binary log. The default value of sync_binlog is 0, which does no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

17.1.4.5 Global Transaction ID Options and Variables

Startup Options Used with GTID Replication

System Variables Used with GTID Replication

The MySQL Server options and system variables described in this section are used to monitor and control Global Transaction Identifiers (GTIDs), introduced in MySQL 5.6.5.

Note

Many of these options and variables were renamed in MySQL 5.6.9. See their descriptions in this section for more information.

For additional information, see Section 17.1.3, “Replication with Global Transaction Identifiers”.

Startup Options Used with GTID Replication

The followup server startup options are used with GTID-based replication:

  • --disable-gtid-unsafe-statements

    Introduced5.6.5
    Removed5.6.9
    Command-Line Format--disable-gtid-unsafe-statements[=value]
    System VariableNamedisable_gtid_unsafe_statements
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    Defaultfalse

    Obsolete: Replaced by --enforce-gtid-consistency in MySQL 5.6.9. (Bug #14775984)

  • --enforce-gtid-consistency

    Introduced5.6.9
    Command-Line Format--enforce-gtid-consistency[=value]
    System VariableNameenforce_gtid_consistency
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    Defaultfalse

    When enabled, this option enforces GTID consistency by allowing execution of only those statements that can be logged in a transactionally safe manner. You must enable --enforce-gtid-consistency before setting --gtid-mode to ON; otherwise, enabling GTID mode fails with an error. You can (and should) use this option prior to using --gtid-mode, in order to test whether the system is ready to use GTIDs.

    Since only transactionally safe statements can be logged when --enforce-gtid-consistency is enabled, it follows that the operations listed here cannot be used with this option:

    Prior to MySQL 5.6.9, this option was named --disable-gtid-unsafe-statements. (Bug #14775984)

    Prior to MySQL 5.6.7, using this option caused nontransactional DML on temporary tables to fail, although changes to temporary tables are not logged when using row-based binary logging. In MySQL 5.6.7 and later, nontransactional DML statements are allowed on temporary tables with --disable-gtid-unsafe-statements (--enforce-gtid-consistency beginning with MySQL 5.6.9) as long as all affected tables are temporary tables (Bug #14272672).

    Prior to MySQL 5.6.7, mysql_upgrade could not be used with a MySQL Server running with this option enabled, unless mysql_upgrade was running with --write-binlog explicitly disabled. (Bug #13833710, Bug #14221043) In MySQL 5.6.7 and later, it is possible but not recommended to run mysql_upgrade on a server where --gtid-mode=ON, since the MySQL system tables use the MyISAM storage engine, which is nontransactional.

    In MySQL 5.6.8 and earlier, you could not use any statements affecting nontransactional tables when --enforce-gtid-consistency was used (the option was then called --disable-gtid-unsafe-statements). In MySQL 5.6.9 and later, this option allows single statements updating nontransactional tables. This is intended chiefly for use with programs such as mysql_install_db and mysql_upgrade. (Bug #14722659)

  • --gtid-mode

    Introduced5.6.5
    Command-Line Format--gtid-mode=MODE
    System VariableNamegtid_mode
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeenumeration
    DefaultOFF
    Valid ValuesOFF
    UPGRADE_STEP_1
    UPGRADE_STEP_2
    ON

    This option specifies whether GTIDs are enabled. Starting the server with --gtid-mode=ON requires that the server also be started with the --log-bin, --log-slave-updates, and --enforce-gtid-consistency options.

    Setting this option to OFF when there are GTIDs in the binary log or in the relay log, or to ON when there remain anonymous transactions to be executed, causes an error.

    Important

    This option does not employ boolean values; its values are in fact enumerated. You should not attempt to use numeric values when setting this option, as these may lead to unexpected results. The values UPGRADE_STEP_1 and UPGRADE_STEP_2 are reserved for future use, but currently are not supported in production; if you use one of these two values with --gtid-mode, the server refuses to start.

    Prior to MySQL 5.6.7, mysql_upgrade could not be used with a MySQL Server running with this option enabled, unless mysql_upgrade was running with --write-binlog explicitly disabled. (Bug #13833710, Bug #14221043) In MySQL 5.6.7 and later, it is possible but not recommended to run mysql_upgrade on a server where --gtid-mode=ON, since it may make changes to MySQL system tables that use the MyISAM storage engine, which is nontransactional.

    Prior to MySQL 5.6.10, setting the global value for the sql_slave_skip_counter variable to 1 had no effect when --gtid-mode was set to ON. (Bug #15833516) A workaround in MySQL 5.6.9 and earlier versions is to reset the slave's position using CHANGE MASTER TO ... MASTER_LOG_FILE = ... MASTER_LOG_POS = ..., including the MASTER_AUTO_POSITION = 0 option with this statement if needed.

System Variables Used with GTID Replication

The following system variables are used with GTID-based replication:

  • disable_gtid_unsafe_statements

    Introduced5.6.5
    Removed5.6.9
    Command-Line Format--disable-gtid-unsafe-statements[=value]
    System VariableNamedisable_gtid_unsafe_statements
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    Defaultfalse

    Obsolete: Replaced by enforce_gtid_consistency in MySQL 5.6.9. (Bug #14775984)

  • gtid_done

    Introduced5.6.5
    Removed5.6.9
    System VariableNamegtid_done
    Variable ScopeGlobal, Session
    Dynamic VariableNo
    Permitted ValuesTypestring

    Obsolete: replaced in MySQL 5.6.9 by gtid_executed. (Bug #14775984)

  • enforce_gtid_consistency

    Introduced5.6.9
    Command-Line Format--enforce-gtid-consistency[=value]
    System VariableNameenforce_gtid_consistency
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    Defaultfalse

    When this variable is true, the server enforces GTID consistency by allowing execution of only those statements that can be logged in a transactionally safe manner. You must enable GTID consistency (by using --enforce-gtid-consistency) before you can start the server with --gtid-mode=ON; otherwise, enabling GTID mode fails with an error. You can (and should) enable GTID consistency prior to using --gtid-mode, in order to test whether the system is ready to use GTIDs.

    Since only transactionally safe statements can be logged when enforce_gtid_consistency is true, it follows that the operations listed here cannot be used when this is the case:

    This variable is read-only. To set it, use the --enforce-gtid-consistency option on the command line or in an option file when starting the MySQL Server.

    Prior to MySQL 5.6.9, this variable was named disable_gtid_unsafe_statements. (Bug #14775984)

  • gtid_executed

    Introduced5.6.9
    System VariableNamegtid_executed
    Variable ScopeGlobal, Session
    Dynamic VariableNo
    Permitted ValuesTypestring

    When used with global scope, this variable contains a representation of the set of all transactions that are logged in the binary log. This is the same as the value of the Executed_Gtid_Set column in the output of SHOW MASTER STATUS and SHOW SLAVE STATUS.

    When used with session scope, this variable contains a representation of the set of transactions that are written to the cache in the current session.

    The set of transactions that can be found in the binary logs at any given time is equal to GTID_SUBTRACT(@@global.gtid_executed, @@global.gtid_purged); that is, to all transactions in the binary log that have not yet been purged.

    When the server starts, @@global.gtid_executed is initialized to the union of the following two sets:

    • The GTIDs listed in the Previous_gtids_log_event of the newest binary log

    • The GTIDs found in every Gtid_log_event in the newest binary log.

    Thereafter, GTIDs are added to the set as transactions are executed.

    Issuing RESET MASTER causes the global value (but not the session value) of this variable to be reset to an empty string. GTIDs are not otherwise removed from this set other than when the set is cleared due to RESET MASTER. The set is also cleared if the server is shut down and all binary logs are removed.

    Prior to MySQL 5.6.9, this variable was known as gtid_done.

  • gtid_lost

    Introduced5.6.5
    Removed5.6.9
    System VariableNamegtid_lost
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring

    Obsolete: Replaced by gtid_purged in MySQL 5.6.9. (Bug #14775984)

  • gtid_mode

    Introduced5.6.5
    System VariableNamegtid_mode
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeenumeration
    DefaultOFF
    Valid ValuesOFF
    UPGRADE_STEP_1
    UPGRADE_STEP_2
    ON

    Shows whether GTIDs are enabled. Read-only; set using --gtid-mode.

  • gtid_next

    Introduced5.6.5
    System VariableNamegtid_next
    Variable ScopeSession
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultAUTOMATIC
    Valid ValuesAUTOMATIC
    ANONYMOUS
    UUID:NUMBER

    This variable is used to specify whether and how the next GTID is obtained. gtid_next can take any of the following values:

    • AUTOMATIC: Use the next automatically-generated global transaction ID.

    • ANONYMOUS: Transactions do not have global identifiers, and are identified by file and position only.

    • A global transaction ID in UUID:NUMBER format.

    You must have the SUPER privilege to set this variable. Setting this variable has no effect if gtid_mode is OFF.

    Prior to MySQL 5.6.20, when GTIDs were enabled but gtid_next was not AUTOMATIC, DROP TABLE did not work correctly when used on a combination of nontemporary tables with temporary tables, or of temporary tables using transactional storage engines with temporary tables using nontransactional storage engines. In MySQL 5.6.20 and later, DROP TABLE or DROP TEMPORARY TABLE fails with an explicit error when used with either of these combinations of tables. (Bug #17620053)

    In MySQL 5.6.11 only, you cannot execute any of the statements CHANGE MASTER TO, START SLAVE, STOP SLAVE, REPAIR TABLE, OPTIMIZE TABLE, ANALYZE TABLE, CHECK TABLE, CREATE SERVER, ALTER SERVER, DROP SERVER, CACHE INDEX, LOAD INDEX INTO CACHE, FLUSH, or RESET when gtid_next is set to any value other than AUTOMATIC; in such cases, the statement fails with an error. Such statements are not disallowed in MySQL 5.6.12 and later. (Bug #16062608, Bug #16715809, Bug #69045)

  • gtid_owned

    Introduced5.6.5
    System VariableNamegtid_owned
    Variable ScopeGlobal, Session
    Dynamic VariableNo
    Permitted ValuesTypestring

    This read-only variable holds a list whose contents depend on its scope. When used with session scope, the list holds all GTIDs that are owned by this client; when used with global scope, it holds a list of all GTIDs along with their owners.

  • gtid_purged

    Introduced5.6.9
    System VariableNamegtid_purged
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring

    The set of all transactions that have been purged from the binary log.

    When the server starts, the global value of gtid_purged is initialized to the set of GTIDs contained by the Previous_gtid_log_event of the oldest binary log. When a binary log is purged, @@global.gtid_purged is re-read from the binary log that has now become the oldest one.

    Issuing RESET MASTER causes the value of this variable to be reset to an empty string.

    Prior to MySQL 5.6.9, this variable was known as gtid_lost, and was read-only. In MySQL 5.6.9 and later, it is possible to update the value of this variable, but only by adding GTIDs to those already listed, and only when gtid_executed is unset—that is, on a new server. (Bug #14797808)

  • simplified_binlog_gtid_recovery

    Introduced5.6.21
    Deprecated5.6.23
    Command-Line Format--simplified-binlog-gtid-recovery
    System VariableNamesimplified_binlog_gtid_recovery
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultFALSE

    This option is deprecated and will be removed in a future MySQL release. Use the renamed binlog_gtid_recovery_simplified to control how MySQL iterates through binary log files after a crash.

17.1.5 Common Replication Administration Tasks

Once replication has been started it should execute without requiring much regular administration. Depending on your replication environment, you will want to check the replication status of each slave periodically, daily, or even more frequently.

17.1.5.1 Checking Replication Status

The most common task when managing a replication process is to ensure that replication is taking place and that there have been no errors between the slave and the master. The primary statement for this is SHOW SLAVE STATUS, which you must execute on each slave:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 931
               Relay_Log_File: slave1-relay-bin.000056
                Relay_Log_Pos: 950
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 931
              Relay_Log_Space: 1365
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids: 0

The key fields from the status report to examine are:

  • Slave_IO_State: The current status of the slave. See Section 8.12.5.6, “Replication Slave I/O Thread States”, and Section 8.12.5.7, “Replication Slave SQL Thread States”, for more information.

  • Slave_IO_Running: Whether the I/O thread for reading the master's binary log is running. Normally, you want this to be Yes unless you have not yet started replication or have explicitly stopped it with STOP SLAVE.

  • Slave_SQL_Running: Whether the SQL thread for executing events in the relay log is running. As with the I/O thread, this should normally be Yes.

  • Last_IO_Error, Last_SQL_Error: The last errors registered by the I/O and SQL threads when processing the relay log. Ideally these should be blank, indicating no errors.

  • Seconds_Behind_Master: The number of seconds that the slave SQL thread is behind processing the master binary log. A high number (or an increasing one) can indicate that the slave is unable to handle events from the master in a timely fashion.

    A value of 0 for Seconds_Behind_Master can usually be interpreted as meaning that the slave has caught up with the master, but there are some cases where this is not strictly true. For example, this can occur if the network connection between master and slave is broken but the slave I/O thread has not yet noticed this—that is, slave_net_timeout has not yet elapsed.

    It is also possible that transient values for Seconds_Behind_Master may not reflect the situation accurately. When the slave SQL thread has caught up on I/O, Seconds_Behind_Master displays 0; but when the slave I/O thread is still queuing up a new event, Seconds_Behind_Master may show a large value until the SQL thread finishes executing the new event. This is especially likely when the events have old timestamps; in such cases, if you execute SHOW SLAVE STATUS several times in a relatively short period, you may see this value change back and forth repeatedly between 0 and a relatively large value.

Several pairs of fields provide information about the progress of the slave in reading events from the master binary log and processing them in the relay log:

  • (Master_Log_file, Read_Master_Log_Pos): Coordinates in the master binary log indicating how far the slave I/O thread has read events from that log.

  • (Relay_Master_Log_File, Exec_Master_Log_Pos): Coordinates in the master binary log indicating how far the slave SQL thread has executed events received from that log.

  • (Relay_Log_File, Relay_Log_Pos): Coordinates in the slave relay log indicating how far the slave SQL thread has executed the relay log. These correspond to the preceding coordinates, but are expressed in slave relay log coordinates rather than master binary log coordinates.

On the master, you can check the status of connected slaves using SHOW PROCESSLIST to examine the list of running processes. Slave connections have Binlog Dump in the Command field:

mysql> SHOW PROCESSLIST \G;
*************************** 4. row ***************************
     Id: 10
   User: root
   Host: slave1:58371
     db: NULL
Command: Binlog Dump
   Time: 777
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

Because it is the slave that drives the replication process, very little information is available in this report.

For slaves that were started with the --report-host option and are connected to the master, the SHOW SLAVE HOSTS statement on the master shows basic information about the slaves. The output includes the ID of the slave server, the value of the --report-host option, the connecting port, and master ID:

mysql> SHOW SLAVE HOSTS;
+-----------+--------+------+-------------------+-----------+
| Server_id | Host   | Port | Rpl_recovery_rank | Master_id |
+-----------+--------+------+-------------------+-----------+
|        10 | slave1 | 3306 |                 0 |         1 |
+-----------+--------+------+-------------------+-----------+
1 row in set (0.00 sec)

17.1.5.2 Pausing Replication on the Slave

You can stop and start the replication of statements on the slave using the STOP SLAVE and START SLAVE statements.

To stop processing of the binary log from the master, use STOP SLAVE:

mysql> STOP SLAVE;

When replication is stopped, the slave I/O thread stops reading events from the master binary log and writing them to the relay log, and the SQL thread stops reading events from the relay log and executing them. You can pause the I/O or SQL thread individually by specifying the thread type:

mysql> STOP SLAVE IO_THREAD;
mysql> STOP SLAVE SQL_THREAD;

To start execution again, use the START SLAVE statement:

mysql> START SLAVE;

To start a particular thread, specify the thread type:

mysql> START SLAVE IO_THREAD;
mysql> START SLAVE SQL_THREAD;

For a slave that performs updates only by processing events from the master, stopping only the SQL thread can be useful if you want to perform a backup or other task. The I/O thread will continue to read events from the master but they are not executed. This makes it easier for the slave to catch up when you restart the SQL thread.

Stopping only the I/O thread enables the events in the relay log to be executed by the SQL thread up to the point where the relay log ends. This can be useful when you want to pause execution to catch up with events already received from the master, when you want to perform administration on the slave but also ensure that it has processed all updates to a specific point. This method can also be used to pause event receipt on the slave while you conduct administration on the master. Stopping the I/O thread but permitting the SQL thread to run helps ensure that there is not a massive backlog of events to be executed when replication is started again.

17.2 Replication Implementation

Replication is based on the master server keeping track of all changes to its databases (updates, deletes, and so on) in its binary log. The binary log serves as a written record of all events that modify database structure or content (data) from the moment the server was started. Typically, SELECT statements are not recorded because they modify neither database structure nor content.

Each slave that connects to the master requests a copy of the binary log. That is, it pulls the data from the master, rather than the master pushing the data to the slave. The slave also executes the events from the binary log that it receives. This has the effect of repeating the original changes just as they were made on the master. Tables are created or their structure modified, and data is inserted, deleted, and updated according to the changes that were originally made on the master.

Because each slave is independent, the replaying of the changes from the master's binary log occurs independently on each slave that is connected to the master. In addition, because each slave receives a copy of the binary log only by requesting it from the master, the slave is able to read and update the copy of the database at its own pace and can start and stop the replication process at will without affecting the ability to update to the latest database status on either the master or slave side.

For more information on the specifics of the replication implementation, see Section 17.2.1, “Replication Implementation Details”.

Masters and slaves report their status in respect of the replication process regularly so that you can monitor them. See Section 8.12.5, “Examining Thread Information”, for descriptions of all replicated-related states.

The master binary log is written to a local relay log on the slave before it is processed. The slave also records information about the current position with the master's binary log and the local relay log. See Section 17.2.2, “Replication Relay and Status Logs”.

Database changes are filtered on the slave according to a set of rules that are applied according to the various configuration options and variables that control event evaluation. For details on how these rules are applied, see Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

17.2.1 Replication Implementation Details

MySQL replication capabilities are implemented using three threads, one on the master server and two on the slave:

  • Binlog dump thread.  The master creates a thread to send the binary log contents to a slave when the slave connects. This thread can be identified in the output of SHOW PROCESSLIST on the master as the Binlog Dump thread.

    The binary log dump thread acquires a lock on the master's binary log for reading each event that is to be sent to the slave. As soon as the event has been read, the lock is released, even before the event is sent to the slave.

  • Slave I/O thread.  When a START SLAVE statement is issued on a slave server, the slave creates an I/O thread, which connects to the master and asks it to send the updates recorded in its binary logs.

    The slave I/O thread reads the updates that the master's Binlog Dump thread sends (see previous item) and copies them to local files that comprise the slave's relay log.

    The state of this thread is shown as Slave_IO_running in the output of SHOW SLAVE STATUS or as Slave_running in the output of SHOW STATUS.

  • Slave SQL thread.  The slave creates an SQL thread to read the relay log that is written by the slave I/O thread and execute the events contained therein.

In the preceding description, there are three threads per master/slave connection. A master that has multiple slaves creates one binary log dump thread for each currently connected slave, and each slave has its own I/O and SQL threads.

A slave uses two threads to separate reading updates from the master and executing them into independent tasks. Thus, the task of reading statements is not slowed down if statement execution is slow. For example, if the slave server has not been running for a while, its I/O thread can quickly fetch all the binary log contents from the master when the slave starts, even if the SQL thread lags far behind. If the slave stops before the SQL thread has executed all the fetched statements, the I/O thread has at least fetched everything so that a safe copy of the statements is stored locally in the slave's relay logs, ready for execution the next time that the slave starts. This enables the master server to purge its binary logs sooner because it no longer needs to wait for the slave to fetch their contents.

The SHOW PROCESSLIST statement provides information that tells you what is happening on the master and on the slave regarding replication. For information on master states, see Section 8.12.5.5, “Replication Master Thread States”. For slave states, see Section 8.12.5.6, “Replication Slave I/O Thread States”, and Section 8.12.5.7, “Replication Slave SQL Thread States”.

The following example illustrates how the three threads show up in the output from SHOW PROCESSLIST.

On the master server, the output from SHOW PROCESSLIST looks like this:

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 2
   User: root
   Host: localhost:32931
     db: NULL
Command: Binlog Dump
   Time: 94
  State: Has sent all binlog to slave; waiting for binlog to
         be updated
   Info: NULL

Here, thread 2 is a Binlog Dump replication thread that services a connected slave. The State information indicates that all outstanding updates have been sent to the slave and that the master is waiting for more updates to occur. If you see no Binlog Dump threads on a master server, this means that replication is not running; that is, no slaves are currently connected.

On a slave server, the output from SHOW PROCESSLIST looks like this:

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 10
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 11
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 11
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 11
  State: Has read all relay log; waiting for the slave I/O
         thread to update it
   Info: NULL

The State information indicates that thread 10 is the I/O thread that is communicating with the master server, and thread 11 is the SQL thread that is processing the updates stored in the relay logs. At the time that SHOW PROCESSLIST was run, both threads were idle, waiting for further updates.

The value in the Time column can show how late the slave is compared to the master. See Section A.13, “MySQL 5.6 FAQ: Replication”. If sufficient time elapses on the master side without activity on the Binlog Dump thread, the master determines that the slave is no longer connected. As for any other client connection, the timeouts for this depend on the values of net_write_timeout and net_retry_count; for more information about these, see Section 5.1.4, “Server System Variables”.

The SHOW SLAVE STATUS statement provides additional information about replication processing on a slave server. See Section 17.1.5.1, “Checking Replication Status”.

17.2.2 Replication Relay and Status Logs

During replication, a slave server creates several logs that hold the binary log events relayed from the master to the slave, and to record information about the current status and location within the relay log. There are three types of logs used in the process, listed here:

  • The relay log consists of the events read from the binary log of the master and written by the slave I/O thread. Events in the relay log are executed on the slave as part of the SQL thread.

  • The master info log contains status and current configuration information for the slave's connection to the master. This log holds information on the master host name, login credentials, and coordinates indicating how far the slave has read from the master's binary log.

    Prior to MySQL 5.6, this log was always a file (master.info), but in MySQL 5.6 and later, this log can be written to the mysql.slave_master_info table instead of a file, by starting the slave with --master-info-repository=TABLE.

  • The relay log info log holds status information about the execution point within the slave's relay log.

    Prior to MySQL 5.6, this log was always a file (relay-log.info), but in MySQL 5.6 and later, this log can be written to the mysql.slave_relay_log_info table instead of a file by starting the slave with --relay-log-info-repository=TABLE.

Prior to MySQL 5.6.7, the Master_id column of the slave_master_info and slave_relay_log_info tables showed the slave's server ID instead of the master's server ID. (Bug #12334346)

Crash-safe replication.  In order for replication to be crash-safe when using tables for logging status and relay information, these tables must use a transactional storage engine, such as InnoDB. Beginning with MySQL 5.6.6, these tables are created using InnoDB. (Bug #13538891)

Therefore, in order to guarantee crash safety on the slave, you must run the slave with --relay-log-recovery enabled, in addition to setting --relay-log-info-repository to TABLE.

Prior to MySQL 5.6.6, if mysqld was unable to initialize the replication logging tables, the slave refused to start. In MySQL 5.6.6 and later, a warning is given when this occurs, but the slave is allowed to continue starting. (Bug #13971348) This situation is most likely to occur when upgrading from a version of MySQL that does not support slave logging tables to one in which they are supported.

In MySQL 5.6.5 and earlier, the slave_master_info and slave_relay_log_info tables used MyISAM by default, which meant that it was necessary before starting replication to change the storage engine used by these tables by issuing ALTER TABLE ... ENGINE=InnoDB, as shown here:

ALTER TABLE mysql.slave_master_info ENGINE=InnoDB;
ALTER TABLE mysql.slave_relay_log_info ENGINE=InnoDB;

The ALTER TABLE statements must be executed by the MySQL root or other user account with the appropriate privileges on the mysql database. You should not attempt to do this while replication is running; beginning with MySQL 5.6.3, trying to execute an ALTER TABLE on either these tables while replication is ongoing is disallowed. Starting with MySQL 5.6.4, execution of any statement requiring a write lock on either or both of these tables is disallowed while replication is ongoing, while statements that perform only reads are permitted at any time.

Important

Do not attempt to update or insert rows in the slave_master_info or slave_relay_log_info table manually. Doing so can cause undefined behavior, and is not supported.

Prior to MySQL 5.6.4, mysqldump did not dump the replication log tables unless they were specified by name and the --master-data option was used.

17.2.2.1 The Slave Relay Log

The relay log, like the binary log, consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files.

The term relay log file generally denotes an individual numbered file containing database events. The term relay log collectively denotes the set of numbered relay log files plus the index file.

Relay log files have the same format as binary log files and can be read using mysqlbinlog (see Section 4.6.8, “mysqlbinlog — Utility for Processing Binary Log Files”).

By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory, where host_name is the name of the slave server host and nnnnnn is a sequence number. Successive relay log files are created using successive sequence numbers, beginning with 000001. The slave uses an index file to track the relay log files currently in use. The default relay log index file name is host_name-relay-bin.index in the data directory.

The default relay log file and relay log index file names can be overridden with, respectively, the --relay-log and --relay-log-index server options (see Section 17.1.4, “Replication and Binary Logging Options and Variables”).

If a slave uses the default host-based relay log file names, changing a slave's host name after replication has been set up can cause replication to fail with the errors Failed to open the relay log and Could not find target log during relay log initialization. This is a known issue (see Bug #2122). If you anticipate that a slave's host name might change in the future (for example, if networking is set up on the slave such that its host name can be modified using DHCP), you can avoid this issue entirely by using the --relay-log and --relay-log-index options to specify relay log file names explicitly when you initially set up the slave. This will make the names independent of server host name changes.

If you encounter the issue after replication has already begun, one way to work around it is to stop the slave server, prepend the contents of the old relay log index file to the new one, and then restart the slave. On a Unix system, this can be done as shown here:

shell> cat new_relay_log_name.index >> old_relay_log_name.index
shell> mv old_relay_log_name.index new_relay_log_name.index

A slave server creates a new relay log file under the following conditions:

The SQL thread automatically deletes each relay log file as soon as it has executed all events in the file and no longer needs it. There is no explicit mechanism for deleting relay logs because the SQL thread takes care of doing so. However, FLUSH LOGS rotates relay logs, which influences when the SQL thread deletes them.

17.2.2.2 Slave Status Logs

A replication slave server creates two logs. By default, these logs are files named master.info and relay-log.info and created in the data directory. The names and locations of these files can be changed by using the --master-info-file and --relay-log-info-file options, respectively. In MySQL 5.6 and later, either or both of these logs can also be written to tables in the mysql database by starting the server with the appropriate option: use --master-info-repository to have the master info log written to the mysql.slave_master_info table, and use --relay-log-info-repository to have the relay log info log written to the mysql.slave_relay_log_info table. See Section 17.1.4, “Replication and Binary Logging Options and Variables”.

The two status logs contain information like that shown in the output of the SHOW SLAVE STATUS statement, which is discussed in Section 13.4.2, “SQL Statements for Controlling Slave Servers”. Because the status logs are stored on disk, they survive a slave server's shutdown. The next time the slave starts up, it reads the two logs to determine how far it has proceeded in reading binary logs from the master and in processing its own relay logs.

The master info log file or table should be protected because it contains the password for connecting to the master. See Section 6.1.2.3, “Passwords and Logging”.

The slave I/O thread updates the master info log. The following table shows the correspondence between the lines in the master.info file, the columns in the mysql.slave_master_info table, and the columns displayed by SHOW SLAVE STATUS.

Line in master.info Fileslave_master_info Table ColumnSHOW SLAVE STATUS ColumnDescription
1Number_of_lines[None]Number of lines in the file, or columns in the table
2Master_log_nameMaster_Log_FileThe name of the master binary log currently being read from the master
3Master_log_posRead_Master_Log_PosThe current position within the master binary log that have been read from the master
4HostMaster_HostThe host name of the master
5User_nameMaster_UserThe user name used to connect to the master
6User_passwordPassword (not shown by SHOW SLAVE STATUS)The password used to connect to the master
7PortMaster_PortThe network port used to connect to the master
8Connect_retryConnect_RetryThe period (in seconds) that the slave will wait before trying to reconnect to the master
9Enabled_sslMaster_SSL_AllowedIndicates whether the server supports SSL connections
10Ssl_caMaster_SSL_CA_FileThe file used for the Certificate Authority (CA) certificate
11Ssl_capathMaster_SSL_CA_PathThe path to the Certificate Authority (CA) certificates
12Ssl_certMaster_SSL_CertThe name of the SSL certificate file
13Ssl_cipherMaster_SSL_CipherThe list of possible ciphers used in the handshake for the SSL connection
14Ssl_keyMaster_SSL_KeyThe name of the SSL key file
15Ssl_verify_server_certMaster_SSL_Verify_Server_CertWhether to verify the server certificate
16Heartbeat[None]Interval between replication heartbeats, in seconds
17BindMaster_BindWhich of the slave's network interfaces should be used for connecting to the master
18Ignored_server_idsReplicate_Ignore_Server_IdsThe list of server IDs to be ignored. Note that for Ignored_server_ids the list of server IDs is preceded by the total number of server IDs to ignore.
19UuidMaster_UUIDThe master's unique ID
20Retry_countMaster_Retry_CountMaximum number of reconnection attempts permitted
21Ssl_crl[None]Path to an ssl certificate revocation list file (added in MySQL version 5.6.3)
22Ssl_crl_path[None]Path to a directory containing ssl certificate revocation list files (added in MySQL version 5.6.3)
23Enabled_auto_positionAuto_positionIf autopositioning is in use or not (added in MySQL version 5.6.5)
Note

Prior to MySQL 5.6.3, the name of the Ssl_verify_server_cert column was Ssl_verify_servert_cert. (Bug #12407446, Bug #60988)

The slave SQL thread updates the relay log info log. In MySQL 5.6, the relay-log.info file includes a line count and a replication delay value. The following table shows the correspondence between the lines in the relay-log.info file, the columns in the mysql.slave_relay_log_info table, and the columns displayed by SHOW SLAVE STATUS.

Line in relay-log.infoslave_relay_log_info Table ColumnSHOW SLAVE STATUS ColumnDescription
1Number_of_lines[None]Number of lines in the file or columns in the table
2Relay_log_nameRelay_Log_FileThe name of the current relay log file
3Relay_log_posRelay_Log_PosThe current position within the relay log file; events up to this position have been executed on the slave database
4Master_log_nameRelay_Master_Log_FileThe name of the master binary log file from which the events in the relay log file were read
5Master_log_posExec_Master_Log_PosThe equivalent position within the master's binary log file of events that have already been executed
5Sql_delaySQL_DelayThe number of seconds that the slave must lag the master
6Number_of_workers[None]The number of slave worker threads for executing replication events (transactions) in parallel (added in MySQL version 5.6.7)
7Id[None]The number of seconds that the slave must lag the master (added in MySQL version 5.6.7)

Prior to MySQL 5.6, the relay-log.info file does not include a line count or a delay value (and the slave_relay_log_info table is not available).

LineStatus ColumnDescription
1Relay_Log_FileThe name of the current relay log file
2Relay_Log_PosThe current position within the relay log file; events up to this position have been executed on the slave database
3Relay_Master_Log_FileThe name of the master binary log file from which the events in the relay log file were read
4Exec_Master_Log_PosThe equivalent position within the master's binary log file of events that have already been executed
Note

If you downgrade a slave server to a version older than MySQL 5.6, the older server does not read the relay-log.info file correctly. To address this, modify the file in a text editor by deleting the initial line containing the number of lines.

The contents of the relay-log.info file and the states shown by the SHOW SLAVE STATUS statement might not match if the relay-log.info file has not been flushed to disk. Ideally, you should only view relay-log.info on a slave that is offline (that is, mysqld is not running). For a running system, you can use SHOW SLAVE STATUS, or query the slave_master_info and slave_relay_log_info tables if you are writing the status logs to tables.

When you back up the slave's data, you should back up these two status logs, along with the relay log files. The status logs are needed to resume replication after you restore the data from the slave. If you lose the relay logs but still have the relay log info log, you can check it to determine how far the SQL thread has executed in the master binary logs. Then you can use CHANGE MASTER TO with the MASTER_LOG_FILE and MASTER_LOG_POS options to tell the slave to re-read the binary logs from that point. Of course, this requires that the binary logs still exist on the master.

17.2.3 How Servers Evaluate Replication Filtering Rules

If a master server does not write a statement to its binary log, the statement is not replicated. If the server does log the statement, the statement is sent to all slaves and each slave determines whether to execute it or ignore it.

On the master, you can control which databases to log changes for by using the --binlog-do-db and --binlog-ignore-db options to control binary logging. For a description of the rules that servers use in evaluating these options, see Section 17.2.3.1, “Evaluation of Database-Level Replication and Binary Logging Options”. You should not use these options to control which databases and tables are replicated. Instead, use filtering on the slave to control the events that are executed on the slave.

On the slave side, decisions about whether to execute or ignore statements received from the master are made according to the --replicate-* options that the slave was started with. (See Section 17.1.4, “Replication and Binary Logging Options and Variables”.)

In the simplest case, when there are no --replicate-* options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given.

Database-level options (--replicate-do-db, --replicate-ignore-db) are checked first; see Section 17.2.3.1, “Evaluation of Database-Level Replication and Binary Logging Options”, for a description of this process. If no database-level options are used, option checking proceeds to any table-level options that may be in use (see Section 17.2.3.2, “Evaluation of Table-Level Replication Options”, for a discussion of these). If one or more database-level options are used but none are matched, the statement is not replicated.

For statements affecting databases only (that is, CREATE DATABASE, DROP DATABASE, and ALTER DATABASE), database-level options always take precedence over any --replicate-wild-do-table options. In other words, for such statements, --replicate-wild-do-table options are checked if and only if there are no database-level options that apply. This is a change in behavior from previous versions of MySQL, where the statement CREATE DATABASE dbx was not replicated if the slave had been started with --replicate-do-db=dbx --replicate-wild-do-table=db%.t1. (Bug #46110)

To make it easier to determine what effect an option set will have, it is recommended that you avoid mixing do and ignore options, or wildcard and nonwildcard options.

If any --replicate-rewrite-db options were specified, they are applied before the --replicate-* filtering rules are tested.

Note

In MySQL 5.6, all replication filtering options follow the same rules for case sensitivity that apply to names of databases and tables elsewhere in the MySQL server, including the effects of the lower_case_table_names system variable.

This is a change from previous versions of MySQL. (Bug #51639)

17.2.3.1 Evaluation of Database-Level Replication and Binary Logging Options

When evaluating replication options, the slave begins by checking to see whether there are any --replicate-do-db or --replicate-ignore-db options that apply. When using --binlog-do-db or --binlog-ignore-db, the process is similar, but the options are checked on the master.

With statement-based replication, the default database is checked for a match. With row-based replication, the database where data is to be changed is the database that is checked. Regardless of the binary logging format, checking of database-level options proceeds as shown in the following diagram.

Evaluation of Database-Level Filtering Rules in Replication

The steps involved are listed here:

  1. Are there any --replicate-do-db options?

    • Yes.  Do any of them match the database?

      • Yes.  Execute the statement and exit.

      • No.  Ignore the statement and exit.

    • No.  Continue to step 2.

  2. Are there any --replicate-ignore-db options?

    • Yes.  Do any of them match the database?

      • Yes.  Ignore the statement and exit.

      • No.  Continue to step 3.

    • No.  Continue to step 3.

  3. Proceed to checking the table-level replication options, if there are any. For a description of how these options are checked, see Section 17.2.3.2, “Evaluation of Table-Level Replication Options”.

    Important

    A statement that is still permitted at this stage is not yet actually executed. The statement is not executed until all table-level options (if any) have also been checked, and the outcome of that process permits execution of the statement.

For binary logging, the steps involved are listed here:

  1. Are there any --binlog-do-db or --binlog-ignore-db options?

    • Yes.  Continue to step 2.

    • No.  Log the statement and exit.

  2. Is there a default database (has any database been selected by USE)?

    • Yes.  Continue to step 3.

    • No.  Ignore the statement and exit.

  3. There is a default database. Are there any --binlog-do-db options?

    • Yes.  Do any of them match the database?

      • Yes.  Log the statement and exit.

      • No.  Ignore the statement and exit.

    • No.  Continue to step 4.

  4. Do any of the --binlog-ignore-db options match the database?

    • Yes.  Ignore the statement and exit.

    • No.  Log the statement and exit.

Important

For statement-based logging, an exception is made in the rules just given for the CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements. In those cases, the database being created, altered, or dropped replaces the default database when determining whether to log or ignore updates.

--binlog-do-db can sometimes mean ignore other databases. For example, when using statement-based logging, a server running with only --binlog-do-db=sales does not write to the binary log statements for which the default database differs from sales. When using row-based logging with the same option, the server logs only those updates that change data in sales.

17.2.3.2 Evaluation of Table-Level Replication Options

The slave checks for and evaluates table options only if either of the following two conditions is true:

First, as a preliminary condition, the slave checks whether statement-based replication is enabled. If so, and the statement occurs within a stored function, the slave executes the statement and exits. If row-based replication is enabled, the slave does not know whether a statement occurred within a stored function on the master, so this condition does not apply.

Note

For statement-based replication, replication events represent statements (all changes making up a given event are associated with a single SQL statement); for row-based replication, each event represents a change in a single table row (thus a single statement such as UPDATE mytable SET mycol = 1 may yield many row-based events). When viewed in terms of events, the process of checking table options is the same for both row-based and statement-based replication.

Having reached this point, if there are no table options, the slave simply executes all events. If there are any --replicate-do-table or --replicate-wild-do-table options, the event must match one of these if it is to be executed; otherwise, it is ignored. If there are any --replicate-ignore-table or --replicate-wild-ignore-table options, all events are executed except those that match any of these options. This process is illustrated in the following diagram.

Evaluation of Table-Level Filtering Rules in Replication

The following steps describe this evaluation in more detail:

  1. Are there any table options?

    • Yes.  Continue to step 2.

    • No.  Execute the event and exit.

  2. Are there any --replicate-do-table options?

    • Yes.  Does the table match any of them?

      • Yes.  Execute the event and exit.

      • No.  Continue to step 3.

    • No.  Continue to step 3.

  3. Are there any --replicate-ignore-table options?

    • Yes.  Does the table match any of them?

      • Yes.  Ignore the event and exit.

      • No.  Continue to step 4.

    • No.  Continue to step 4.

  4. Are there any --replicate-wild-do-table options?

    • Yes.  Does the table match any of them?

      • Yes.  Execute the event and exit.

      • No.  Continue to step 5.

    • No.  Continue to step 5.

  5. Are there any --replicate-wild-ignore-table options?

    • Yes.  Does the table match any of them?

      • Yes.  Ignore the event and exit.

      • No.  Continue to step 6.

    • No.  Continue to step 6.

  6. Are there any --replicate-do-table or --replicate-wild-do-table options?

    • Yes.  Ignore the event and exit.

    • No.  Execute the event and exit.

17.2.3.3 Replication Rule Application

This section provides additional explanation and examples of usage for different combinations of replication filtering options.

Some typical combinations of replication filter rule types are given in the following table:

Condition (Types of Options)Outcome
No --replicate-* options at all:The slave executes all events that it receives from the master.
--replicate-*-db options, but no table options:The slave accepts or ignores events using the database options. It executes all events permitted by those options because there are no table restrictions.
--replicate-*-table options, but no database options:All events are accepted at the database-checking stage because there are no database conditions. The slave executes or ignores events based solely on the table options.
A combination of database and table options:The slave accepts or ignores events using the database options. Then it evaluates all events permitted by those options according to the table options. This can sometimes lead to results that seem counterintuitive, and that may be different depending on whether you are using statement-based or row-based replication; see the text for an example.

A more complex example follows, in which we examine the outcomes for both statement-based and row-based settings.

Suppose that we have two tables mytbl1 in database db1 and mytbl2 in database db2 on the master, and the slave is running with the following options (and no other replication filtering options):

replicate-ignore-db = db1
replicate-do-table  = db2.tbl2

Now we execute the following statements on the master:

USE db1;
INSERT INTO db2.tbl2 VALUES (1);

The results on the slave vary considerably depending on the binary log format, and may not match initial expectations in either case.

Statement-based replication.  The USE statement causes db1 to be the default database. Thus the --replicate-ignore-db option matches, and the INSERT statement is ignored. The table options are not checked.

Row-based replication.  The default database has no effect on how the slave reads database options when using row-based replication. Thus, the USE statement makes no difference in how the --replicate-ignore-db option is handled: the database specified by this option does not match the database where the INSERT statement changes data, so the slave proceeds to check the table options. The table specified by --replicate-do-table matches the table to be updated, and the row is inserted.

17.3 Replication Solutions

Replication can be used in many different environments for a range of purposes. This section provides general notes and advice on using replication for specific solution types.

For information on using replication in a backup environment, including notes on the setup, backup procedure, and files to back up, see Section 17.3.1, “Using Replication for Backups”.

For advice and tips on using different storage engines on the master and slaves, see Section 17.3.2, “Using Replication with Different Master and Slave Storage Engines”.

Using replication as a scale-out solution requires some changes in the logic and operation of applications that use the solution. See Section 17.3.3, “Using Replication for Scale-Out”.

For performance or data distribution reasons, you may want to replicate different databases to different replication slaves. See Section 17.3.4, “Replicating Different Databases to Different Slaves”

As the number of replication slaves increases, the load on the master can increase and lead to reduced performance (because of the need to replicate the binary log to each slave). For tips on improving your replication performance, including using a single secondary server as an replication master, see Section 17.3.5, “Improving Replication Performance”.

For guidance on switching masters, or converting slaves into masters as part of an emergency failover solution, see Section 17.3.6, “Switching Masters During Failover”.

To secure your replication communication, you can use SSL to encrypt the communication channel. For step-by-step instructions, see Section 17.3.7, “Setting Up Replication Using SSL”.

17.3.1 Using Replication for Backups

To use replication as a backup solution, replicate data from the master to a slave, and then back up the data slave. The slave can be paused and shut down without affecting the running operation of the master, so you can produce an effective snapshot of live data that would otherwise require the master to be shut down.

How you back up a database depends on its size and whether you are backing up only the data, or the data and the replication slave state so that you can rebuild the slave in the event of failure. There are therefore two choices:

Another backup strategy, which can be used for either master or slave servers, is to put the server in a read-only state. The backup is performed against the read-only server, which then is changed back to its usual read/write operational status. See Section 17.3.1.3, “Backing Up a Master or Slave by Making It Read Only”.

17.3.1.1 Backing Up a Slave Using mysqldump

Using mysqldump to create a copy of a database enables you to capture all of the data in the database in a format that enables the information to be imported into another instance of MySQL Server (see Section 4.5.4, “mysqldump — A Database Backup Program”). Because the format of the information is SQL statements, the file can easily be distributed and applied to running servers in the event that you need access to the data in an emergency. However, if the size of your data set is very large, mysqldump may be impractical.

When using mysqldump, you should stop replication on the slave before starting the dump process to ensure that the dump contains a consistent set of data:

  1. Stop the slave from processing requests. You can stop replication completely on the slave using mysqladmin:

    shell> mysqladmin stop-slave

    Alternatively, you can stop only the slave SQL thread to pause event execution:

    shell> mysql -e 'STOP SLAVE SQL_THREAD;'

    This enables the slave to continue to receive data change events from the master's binary log and store them in the relay logs using the I/O thread, but prevents the slave from executing these events and changing its data. Within busy replication environments, permitting the I/O thread to run during backup may speed up the catch-up process when you restart the slave SQL thread.

  2. Run mysqldump to dump your databases. You may either dump all databases or select databases to be dumped. For example, to dump all databases:

    shell> mysqldump --all-databases > fulldb.dump
  3. Once the dump has completed, start slave operations again:

    shell> mysqladmin start-slave

In the preceding example, you may want to add login credentials (user name, password) to the commands, and bundle the process up into a script that you can run automatically each day.

If you use this approach, make sure you monitor the slave replication process to ensure that the time taken to run the backup does not affect the slave's ability to keep up with events from the master. See Section 17.1.5.1, “Checking Replication Status”. If the slave is unable to keep up, you may want to add another slave and distribute the backup process. For an example of how to configure this scenario, see Section 17.3.4, “Replicating Different Databases to Different Slaves”.

17.3.1.2 Backing Up Raw Data from a Slave

To guarantee the integrity of the files that are copied, backing up the raw data files on your MySQL replication slave should take place while your slave server is shut down. If the MySQL server is still running, background tasks may still be updating the database files, particularly those involving storage engines with background processes such as InnoDB. With InnoDB, these problems should be resolved during crash recovery, but since the slave server can be shut down during the backup process without affecting the execution of the master it makes sense to take advantage of this capability.

To shut down the server and back up the files:

  1. Shut down the slave MySQL server:

    shell> mysqladmin shutdown
  2. Copy the data files. You can use any suitable copying or archive utility, including cp, tar or WinZip. For example, assuming that the data directory is located under the current directory, you can archive the entire directory as follows:

    shell> tar cf /tmp/dbbackup.tar ./data
  3. Start the MySQL server again. Under Unix:

    shell> mysqld_safe &

    Under Windows:

    C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld"

Normally you should back up the entire data directory for the slave MySQL server. If you want to be able to restore the data and operate as a slave (for example, in the event of failure of the slave), then in addition to the slave's data, you should also back up the slave status files, the master info and relay log info repositories, and the relay log files. These files are needed to resume replication after you restore the slave's data.

If you lose the relay logs but still have the relay-log.info file, you can check it to determine how far the SQL thread has executed in the master binary logs. Then you can use CHANGE MASTER TO with the MASTER_LOG_FILE and MASTER_LOG_POS options to tell the slave to re-read the binary logs from that point. This requires that the binary logs still exist on the master server.

If your slave is replicating LOAD DATA INFILE statements, you should also back up any SQL_LOAD-* files that exist in the directory that the slave uses for this purpose. The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations. The location of this directory is the value of the --slave-load-tmpdir option. If the server was not started with that option, the directory location is the value of the tmpdir system variable.

17.3.1.3 Backing Up a Master or Slave by Making It Read Only

It is possible to back up either master or slave servers in a replication setup by acquiring a global read lock and manipulating the read_only system variable to change the read-only state of the server to be backed up:

  1. Make the server read-only, so that it processes only retrievals and blocks updates.

  2. Perform the backup.

  3. Change the server back to its normal read/write state.

Note

The instructions in this section place the server to be backed up in a state that is safe for backup methods that get the data from the server, such as mysqldump (see Section 4.5.4, “mysqldump — A Database Backup Program”). You should not attempt to use these instructions to make a binary backup by copying files directly because the server may still have modified data cached in memory and not flushed to disk.

The following instructions describe how to do this for a master server and for a slave server. For both scenarios discussed here, suppose that you have the following replication setup:

  • A master server M1

  • A slave server S1 that has M1 as its master

  • A client C1 connected to M1

  • A client C2 connected to S1

In either scenario, the statements to acquire the global read lock and manipulate the read_only variable are performed on the server to be backed up and do not propagate to any slaves of that server.

Scenario 1: Backup with a Read-Only Master

Put the master M1 in a read-only state by executing these statements on it:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

While M1 is in a read-only state, the following properties are true:

  • Requests for updates sent by C1 to M1 will block because the server is in read-only mode.

  • Requests for query results sent by C1 to M1 will succeed.

  • Making a backup on M1 is safe.

  • Making a backup on S1 is not safe. This server is still running, and might be processing the binary log or update requests coming from client C2

While M1 is read only, perform the backup. For example, you can use mysqldump.

After the backup operation on M1 completes, restore M1 to its normal operational state by executing these statements:

mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

Although performing the backup on M1 is safe (as far as the backup is concerned), it is not optimal for performance because clients of M1 are blocked from executing updates.

This strategy applies to backing up a master server in a replication setup, but can also be used for a single server in a nonreplication setting.

Scenario 2: Backup with a Read-Only Slave

Put the slave S1 in a read-only state by executing these statements on it:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

While S1 is in a read-only state, the following properties are true:

  • The master M1 will continue to operate, so making a backup on the master is not safe.

  • The slave S1 is stopped, so making a backup on the slave S1 is safe.

These properties provide the basis for a popular backup scenario: Having one slave busy performing a backup for a while is not a problem because it does not affect the entire network, and the system is still running during the backup. In particular, clients can still perform updates on the master server, which remains unaffected by backup activity on the slave.

While S1 is read only, perform the backup. For example, you can use mysqldump.

After the backup operation on S1 completes, restore S1 to its normal operational state by executing these statements:

mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

After the slave is restored to normal operation, it again synchronizes to the master by catching up with any outstanding updates from the binary log of the master.

17.3.2 Using Replication with Different Master and Slave Storage Engines

It does not matter for the replication process whether the source table on the master and the replicated table on the slave use different engine types. In fact, the default_storage_engine and storage_engine system variables are not replicated.

This provides a number of benefits in the replication process in that you can take advantage of different engine types for different replication scenarios. For example, in a typical scale-out scenario (see Section 17.3.3, “Using Replication for Scale-Out”), you want to use InnoDB tables on the master to take advantage of the transactional functionality, but use MyISAM on the slaves where transaction support is not required because the data is only read. When using replication in a data-logging environment you may want to use the Archive storage engine on the slave.

Configuring different engines on the master and slave depends on how you set up the initial replication process:

  • If you used mysqldump to create the database snapshot on your master, you could edit the dump file text to change the engine type used on each table.

    Another alternative for mysqldump is to disable engine types that you do not want to use on the slave before using the dump to build the data on the slave. For example, you can add the --skip-federated option on your slave to disable the FEDERATED engine. If a specific engine does not exist for a table to be created, MySQL will use the default engine type, usually MyISAM. (This requires that the NO_ENGINE_SUBSTITUTION SQL mode is not enabled.) If you want to disable additional engines in this way, you may want to consider building a special binary to be used on the slave that only supports the engines you want.

  • If you are using raw data files (a binary backup) to set up the slave, you will be unable to change the initial table format. Instead, use ALTER TABLE to change the table types after the slave has been started.

  • For new master/slave replication setups where there are currently no tables on the master, avoid specifying the engine type when creating new tables.

If you are already running a replication solution and want to convert your existing tables to another engine type, follow these steps:

  1. Stop the slave from running replication updates:

    mysql> STOP SLAVE;
    

    This will enable you to change engine types without interruptions.

  2. Execute an ALTER TABLE ... ENGINE='engine_type' for each table to be changed.

  3. Start the slave replication process again:

    mysql> START SLAVE;
    

Although the default_storage_engine variable is not replicated, be aware that CREATE TABLE and ALTER TABLE statements that include the engine specification will be correctly replicated to the slave. For example, if you have a CSV table and you execute:

mysql> ALTER TABLE csvtable Engine='MyISAM';

The above statement will be replicated to the slave and the engine type on the slave will be converted to MyISAM, even if you have previously changed the table type on the slave to an engine other than CSV. If you want to retain engine differences on the master and slave, you should be careful to use the default_storage_engine variable on the master when creating a new table. For example, instead of:

mysql> CREATE TABLE tablea (columna int) Engine=MyISAM;

Use this format:

mysql> SET default_storage_engine=MyISAM;
mysql> CREATE TABLE tablea (columna int);

When replicated, the default_storage_engine variable will be ignored, and the CREATE TABLE statement will execute on the slave using the slave's default engine.

17.3.3 Using Replication for Scale-Out

You can use replication as a scale-out solution; that is, where you want to split up the load of database queries across multiple database servers, within some reasonable limitations.

Because replication works from the distribution of one master to one or more slaves, using replication for scale-out works best in an environment where you have a high number of reads and low number of writes/updates. Most Web sites fit into this category, where users are browsing the Web site, reading articles, posts, or viewing products. Updates only occur during session management, or when making a purchase or adding a comment/message to a forum.

Replication in this situation enables you to distribute the reads over the replication slaves, while still enabling your web servers to communicate with the replication master when a write is required. You can see a sample replication layout for this scenario in Figure 17.1, “Using Replication to Improve Performance During Scale-Out”.

Figure 17.1 Using Replication to Improve Performance During Scale-Out

Using replication to improve performance during scale-out

If the part of your code that is responsible for database access has been properly abstracted/modularized, converting it to run with a replicated setup should be very smooth and easy. Change the implementation of your database access to send all writes to the master, and to send reads to either the master or a slave. If your code does not have this level of abstraction, setting up a replicated system gives you the opportunity and motivation to clean it up. Start by creating a wrapper library or module that implements the following functions:

  • safe_writer_connect()

  • safe_reader_connect()

  • safe_reader_statement()

  • safe_writer_statement()

safe_ in each function name means that the function takes care of handling all error conditions. You can use different names for the functions. The important thing is to have a unified interface for connecting for reads, connecting for writes, doing a read, and doing a write.

Then convert your client code to use the wrapper library. This may be a painful and scary process at first, but it pays off in the long run. All applications that use the approach just described are able to take advantage of a master/slave configuration, even one involving multiple slaves. The code is much easier to maintain, and adding troubleshooting options is trivial. You need modify only one or two functions; for example, to log how long each statement took, or which statement among those issued gave you an error.

If you have written a lot of code, you may want to automate the conversion task by using the replace utility that comes with standard MySQL distributions, or write your own conversion script. Ideally, your code uses consistent programming style conventions. If not, then you are probably better off rewriting it anyway, or at least going through and manually regularizing it to use a consistent style.

17.3.4 Replicating Different Databases to Different Slaves

There may be situations where you have a single master and want to replicate different databases to different slaves. For example, you may want to distribute different sales data to different departments to help spread the load during data analysis. A sample of this layout is shown in Figure 17.2, “Using Replication to Replicate Databases to Separate Replication Slaves”.

Figure 17.2 Using Replication to Replicate Databases to Separate Replication Slaves

Using replication to replicate databases to separate replication slaves

You can achieve this separation by configuring the master and slaves as normal, and then limiting the binary log statements that each slave processes by using the --replicate-wild-do-table configuration option on each slave.

Important

You should not use --replicate-do-db for this purpose when using statement-based replication, since statement-based replication causes this option's affects to vary according to the database that is currently selected. This applies to mixed-format replication as well, since this enables some updates to be replicated using the statement-based format.

However, it should be safe to use --replicate-do-db for this purpose if you are using row-based replication only, since in this case the currently selected database has no effect on the option's operation.

For example, to support the separation as shown in Figure 17.2, “Using Replication to Replicate Databases to Separate Replication Slaves”, you should configure each replication slave as follows, before executing START SLAVE:

  • Replication slave 1 should use --replicate-wild-do-table=databaseA.%.

  • Replication slave 2 should use --replicate-wild-do-table=databaseB.%.

  • Replication slave 3 should use --replicate-wild-do-table=databaseC.%.

Each slave in this configuration receives the entire binary log from the master, but executes only those events from the binary log that apply to the databases and tables included by the --replicate-wild-do-table option in effect on that slave.

If you have data that must be synchronized to the slaves before replication starts, you have a number of choices:

  • Synchronize all the data to each slave, and delete the databases, tables, or both that you do not want to keep.

  • Use mysqldump to create a separate dump file for each database and load the appropriate dump file on each slave.

  • Use a raw data file dump and include only the specific files and databases that you need for each slave.

    Note

    This does not work with InnoDB databases unless you use innodb_file_per_table.

17.3.5 Improving Replication Performance

As the number of slaves connecting to a master increases, the load, although minimal, also increases, as each slave uses a client connection to the master. Also, as each slave must receive a full copy of the master binary log, the network load on the master may also increase and create a bottleneck.

If you are using a large number of slaves connected to one master, and that master is also busy processing requests (for example, as part of a scale-out solution), then you may want to improve the performance of the replication process.

One way to improve the performance of the replication process is to create a deeper replication structure that enables the master to replicate to only one slave, and for the remaining slaves to connect to this primary slave for their individual replication requirements. A sample of this structure is shown in Figure 17.3, “Using an Additional Replication Host to Improve Performance”.

Figure 17.3 Using an Additional Replication Host to Improve Performance

Using an additional replication host to improve performance

For this to work, you must configure the MySQL instances as follows:

  • Master 1 is the primary master where all changes and updates are written to the database. Binary logging should be enabled on this machine.

  • Master 2 is the slave to the Master 1 that provides the replication functionality to the remainder of the slaves in the replication structure. Master 2 is the only machine permitted to connect to Master 1. Master 2 also has binary logging enabled, and the --log-slave-updates option so that replication instructions from Master 1 are also written to Master 2's binary log so that they can then be replicated to the true slaves.

  • Slave 1, Slave 2, and Slave 3 act as slaves to Master 2, and replicate the information from Master 2, which actually consists of the upgrades logged on Master 1.

The above solution reduces the client load and the network interface load on the primary master, which should improve the overall performance of the primary master when used as a direct database solution.

If your slaves are having trouble keeping up with the replication process on the master, there are a number of options available:

  • If possible, put the relay logs and the data files on different physical drives. To do this, use the --relay-log option to specify the location of the relay log.

  • If the slaves are significantly slower than the master, you may want to divide up the responsibility for replicating different databases to different slaves. See Section 17.3.4, “Replicating Different Databases to Different Slaves”.

  • If your master makes use of transactions and you are not concerned about transaction support on your slaves, use MyISAM or another nontransactional engine on the slaves. See Section 17.3.2, “Using Replication with Different Master and Slave Storage Engines”.

  • If your slaves are not acting as masters, and you have a potential solution in place to ensure that you can bring up a master in the event of failure, then you can switch off --log-slave-updates. This prevents dumb slaves from also logging events they have executed into their own binary log.

17.3.6 Switching Masters During Failover

When using replication with GTIDs (see Section 17.1.3, “Replication with Global Transaction Identifiers”), you can provide failover between master and slaves in the event of a failure using mysqlfailover, which is provided by the MySQL Utilities; see mysqlfailover — Automatic replication health monitoring and failover, for more information. Otherwise, you must set up a master and one or more slaves; then, you need to write an application or script that monitors the master to check whether it is up, and instructs the slaves and applications to change to another master in case of failure. This section discusses some of the issues encountered when setting up failover in this fashion.

You can tell a slave to change to a new master using the CHANGE MASTER TO statement. The slave does not check whether the databases on the master are compatible with those on the slave; it simply begins reading and executing events from the specified coordinates in the new master's binary log. In a failover situation, all the servers in the group are typically executing the same events from the same binary log file, so changing the source of the events should not affect the structure or integrity of the database, provided that you exercise care in making the change.

Slaves should be run with the --log-bin option and without --log-slave-updates. In this way, the slave is ready to become a master without restarting the slave mysqld. Assume that you have the structure shown in Figure 17.4, “Redundancy Using Replication, Initial Structure”.

Figure 17.4 Redundancy Using Replication, Initial Structure

Redundancy using replication, initial structure

In this diagram, the MySQL Master holds the master database, the MySQL Slave hosts are replication slaves, and the Web Client machines are issuing database reads and writes. Web clients that issue only reads (and would normally be connected to the slaves) are not shown, as they do not need to switch to a new server in the event of failure. For a more detailed example of a read/write scale-out replication structure, see Section 17.3.3, “Using Replication for Scale-Out”.

Each MySQL Slave (Slave 1, Slave 2, and Slave 3) is a slave running with --log-bin and without --log-slave-updates. Because updates received by a slave from the master are not logged in the binary log unless --log-slave-updates is specified, the binary log on each slave is empty initially. If for some reason MySQL Master becomes unavailable, you can pick one of the slaves to become the new master. For example, if you pick Slave 1, all Web Clients should be redirected to Slave 1, which writes the updates to its binary log. Slave 2 and Slave 3 should then replicate from Slave 1.

The reason for running the slave without --log-slave-updates is to prevent slaves from receiving updates twice in case you cause one of the slaves to become the new master. If Slave 1 has --log-slave-updates enabled, it writes any updates that it receives from Master in its own binary log. This means that, when Slave 2 changes from Master to Slave 1 as its master, it may receive updates from Slave 1 that it has already received from Master.

Make sure that all slaves have processed any statements in their relay log. On each slave, issue STOP SLAVE IO_THREAD, then check the output of SHOW PROCESSLIST until you see Has read all relay log. When this is true for all slaves, they can be reconfigured to the new setup. On the slave Slave 1 being promoted to become the master, issue STOP SLAVE and RESET MASTER.

On the other slaves Slave 2 and Slave 3, use STOP SLAVE and CHANGE MASTER TO MASTER_HOST='Slave1' (where 'Slave1' represents the real host name of Slave 1). To use CHANGE MASTER TO, add all information about how to connect to Slave 1 from Slave 2 or Slave 3 (user, password, port). When issuing the CHANGE MASTER TO statement in this, there is no need to specify the name of the Slave 1 binary log file or log position to read from, since the first binary log file and position 4, are the defaults. Finally, execute START SLAVE on Slave 2 and Slave 3.

Once the new replication setup is in place, you need to tell each Web Client to direct its statements to Slave 1. From that point on, all updates statements sent by Web Client to Slave 1 are written to the binary log of Slave 1, which then contains every update statement sent to Slave 1 since Master died.

The resulting server structure is shown in Figure 17.5, “Redundancy Using Replication, After Master Failure”.

Figure 17.5 Redundancy Using Replication, After Master Failure

Redundancy using replication, after master failure

When Master becomes available again, you should make it a slave of Slave 1. To do this, issue on Master the same CHANGE MASTER TO statement as that issued on Slave 2 and Slave 3 previously. Master then becomes a slave of S1ave 1 and picks up the Web Client writes that it missed while it was offline.

To make Master a master again (for example, because it is the most powerful machine), use the preceding procedure as if Slave 1 was unavailable and Master was to be the new master. During this procedure, do not forget to run RESET MASTER on Master before making Slave 1, Slave 2, and Slave 3 slaves of Master. If you fail to do this, the slaves may pick up stale writes from the Web Client applications dating from before the point at which Master became unavailable.

You should be aware that there is no synchronization between slaves, even when they share the same master, and thus some slaves might be considerably ahead of others. This means that in some cases the procedure outlined in the previous example might not work as expected. In practice, however, relay logs on all slaves should be relatively close together.

One way to keep applications informed about the location of the master is to have a dynamic DNS entry for the master. With bind you can use nsupdate to update the DNS dynamically.

17.3.7 Setting Up Replication Using SSL

To use SSL for encrypting the transfer of the binary log required during replication, both the master and the slave must support SSL network connections. If either host does not support SSL connections (because it has not been compiled or configured for SSL), replication through an SSL connection is not possible.

Setting up replication using an SSL connection is similar to setting up a server and client using SSL. You must obtain (or create) a suitable security certificate that you can use on the master, and a similar certificate (from the same certificate authority) on each slave.

For more information on setting up a server and client for SSL connectivity, see Section 6.3.10.2, “Configuring MySQL for SSL”.

To enable SSL on the master you must create or obtain suitable certificates, and then add the following configuration options to the master's configuration within the [mysqld] section of the master's my.cnf file:

[mysqld]
ssl-ca=cacert.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

The paths to the certificates may be relative or absolute; we recommend that you always use complete paths for this purpose.

The options are as follows:

  • ssl-ca identifies the Certificate Authority (CA) certificate.

  • ssl-cert identifies the server public key. This can be sent to the client and authenticated against the CA certificate that it has.

  • ssl-key identifies the server private key.

On the slave, you have two options available for setting the SSL information. You can either add the slave certificates to the [client] section of the slave's my.cnf file, or you can explicitly specify the SSL information using the CHANGE MASTER TO statement:

  • To add the slave certificates using an option file, add the following lines to the [client] section of the slave's my.cnf file:

    [client]
    ssl-ca=cacert.pem
    ssl-cert=client-cert.pem
    ssl-key=client-key.pem

    Restart the slave server, using the --skip-slave-start option to prevent the slave from connecting to the master. Use CHANGE MASTER TO to specify the master configuration, using the MASTER_SSL option to enable SSL connectivity:

    mysql> CHANGE MASTER TO
        -> MASTER_HOST='master_hostname',
        -> MASTER_USER='replicate',
        -> MASTER_PASSWORD='password',
        -> MASTER_SSL=1;
    
  • To specify the SSL certificate options using the CHANGE MASTER TO statement, append the SSL options:

    mysql> CHANGE MASTER TO
        -> MASTER_HOST='master_hostname',
        -> MASTER_USER='replicate',
        -> MASTER_PASSWORD='password',
        -> MASTER_SSL=1,
        -> MASTER_SSL_CA = 'ca_file_name',
        -> MASTER_SSL_CAPATH = 'ca_directory_name',
        -> MASTER_SSL_CERT = 'cert_file_name',
        -> MASTER_SSL_KEY = 'key_file_name';
    

After the master information has been updated, start the slave replication process:

mysql> START SLAVE;

You can use the SHOW SLAVE STATUS statement to confirm that the SSL connection was established successfully.

For more information on the CHANGE MASTER TO statement, see Section 13.4.2.1, “CHANGE MASTER TO Syntax”.

If you want to enforce the use of SSL connections during replication, then create a user with the REPLICATION SLAVE privilege and use the REQUIRE SSL option for that user. For example:

mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.*
    -> TO 'repl'@'%.mydomain.com' REQUIRE SSL;

If the account already exists, you can add REQUIRE SSL to it with this statement:

mysql> GRANT USAGE ON *.*
    -> TO 'repl'@'%.mydomain.com' REQUIRE SSL;

17.3.8 Semisynchronous Replication

In addition to the built-in asynchronous replication, MySQL 5.6 supports an interface to semisynchronous replication that is implemented by plugins. This section discusses what semisynchronous replication is and how it works. The following sections cover the administrative interface to semisynchronous replication and how to install, configure, and monitor it.

MySQL replication by default is asynchronous. The master writes events to its binary log but does not know whether or when a slave has retrieved and processed them. With asynchronous replication, if the master crashes, transactions that it has committed might not have been transmitted to any slave. Consequently, failover from master to slave in this case may result in failover to a server that is missing transactions relative to the master.

Semisynchronous replication can be used as an alternative to asynchronous replication:

  • A slave indicates whether it is semisynchronous-capable when it connects to the master.

  • If semisynchronous replication is enabled on the master side and there is at least one semisynchronous slave, a thread that performs a transaction commit on the master blocks after the commit is done and waits until at least one semisynchronous slave acknowledges that it has received all events for the transaction, or until a timeout occurs.

  • The slave acknowledges receipt of a transaction's events only after the events have been written to its relay log and flushed to disk.

  • If a timeout occurs without any slave having acknowledged the transaction, the master reverts to asynchronous replication. When at least one semisynchronous slave catches up, the master returns to semisynchronous replication.

  • Semisynchronous replication must be enabled on both the master and slave sides. If semisynchronous replication is disabled on the master, or enabled on the master but on no slaves, the master uses asynchronous replication.

While the master is blocking (waiting for acknowledgment from a slave after having performed a commit), it does not return to the session that performed the transaction. When the block ends, the master returns to the session, which then can proceed to execute other statements. At this point, the transaction has committed on the master side, and receipt of its events has been acknowledged by at least one slave.

Blocking also occurs after rollbacks that are written to the binary log, which occurs when a transaction that modifies nontransactional tables is rolled back. The rolled-back transaction is logged even though it has no effect for transactional tables because the modifications to the nontransactional tables cannot be rolled back and must be sent to slaves.

For statements that do not occur in transactional context (that is, when no transaction has been started with START TRANSACTION or SET autocommit = 0), autocommit is enabled and each statement commits implicitly. With semisynchronous replication, the master blocks after committing each such statement, just as it does for explicit transaction commits.

To understand what the semi in semisynchronous replication means, compare it with asynchronous and fully synchronous replication:

  • With asynchronous replication, the master writes events to its binary log and slaves request them when they are ready. There is no guarantee that any event will ever reach any slave.

  • With fully synchronous replication, when a master commits a transaction, all slaves also will have committed the transaction before the master returns to the session that performed the transaction. The drawback of this is that there might be a lot of delay to complete a transaction.

  • Semisynchronous replication falls between asynchronous and fully synchronous replication. The master waits after commit only until at least one slave has received and logged the events. It does not wait for all slaves to acknowledge receipt, and it requires only receipt, not that the events have been fully executed and committed on the slave side.

Compared to asynchronous replication, semisynchronous replication provides improved data integrity. When a commit returns successfully, it is known that the data exists in at least two places (on the master and at least one slave). If the master commits but a crash occurs while the master is waiting for acknowledgment from a slave, it is possible that the transaction may not have reached any slave.

Semisynchronous replication also places a rate limit on busy sessions by constraining the speed at which binary log events can be sent from master to slave. When one user is too busy, this will slow it down, which is useful in some deployment situations.

Semisynchronous replication does have some performance impact because commits are slower due to the need to wait for slaves. This is the tradeoff for increased data integrity. The amount of slowdown is at least the TCP/IP roundtrip time to send the commit to the slave and wait for the acknowledgment of receipt by the slave. This means that semisynchronous replication works best for close servers communicating over fast networks, and worst for distant servers communicating over slow networks.

17.3.8.1 Semisynchronous Replication Administrative Interface

The administrative interface to semisynchronous replication has several components:

  • Two plugins implement semisynchronous capability. There is one plugin for the master side and one for the slave side.

  • System variables control plugin behavior. Some examples:

    All rpl_semi_sync_xxx system variables are described at Section 5.1.4, “Server System Variables”.

  • Status variables enable semisynchronous replication monitoring. Some examples:

    • Rpl_semi_sync_master_clients

      The number of semisynchronous slaves.

    • Rpl_semi_sync_master_status

      Whether semisynchronous replication currently is operational on the master. The value is 1 if the plugin has been enabled and a commit acknowledgment has not occurred. It is 0 if the plugin is not enabled or the master has fallen back to asynchronous replication due to commit acknowledgment timeout.

    • Rpl_semi_sync_master_no_tx

      The number of commits that were not acknowledged successfully by a slave.

    • Rpl_semi_sync_master_yes_tx

      The number of commits that were acknowledged successfully by a slave.

    • Rpl_semi_sync_slave_status

      Whether semisynchronous replication currently is operational on the slave. This is 1 if the plugin has been enabled and the slave I/O thread is running, 0 otherwise.

    All Rpl_semi_sync_xxx status variables are described at Section 5.1.6, “Server Status Variables”.

The system and status variables are available only if the appropriate master or slave plugin has been installed with INSTALL PLUGIN.

17.3.8.2 Semisynchronous Replication Installation and Configuration

Semisynchronous replication is implemented using plugins, so the plugins must be installed into the server to make them available. After a plugin has been installed, you control it by means of the system variables associated with it. These system variables are unavailable until the associated plugin has been installed.

To use semisynchronous replication, the following requirements must be satisfied:

  • MySQL 5.5 or higher must be installed.

  • The capability of installing plugins requires a MySQL server that supports dynamic loading. To verify this, check that the value of the have_dynamic_loading system variable is YES. Binary distributions should support dynamic loading.

  • Replication must already be working. For information on creating a master/slave relationship, see Section 17.1.1, “How to Set Up Replication”.

To set up semisynchronous replication, use the following instructions. The INSTALL PLUGIN, SET GLOBAL, STOP SLAVE, and START SLAVE statements mentioned here require the SUPER privilege.

The semisynchronous replication plugins are included with MySQL distributions.

Unpack the component distribution, which contains files for the master side and the slave side.

Install the component files in the plugin directory of the appropriate server. Install the semisync_master* files in the plugin directory of the master server. Install the semisync_slave* files in the plugin directory of each slave server. The location of the plugin directory is available as the value of the server's plugin_dir system variable.

To load the plugins, use the INSTALL PLUGIN statement on the master and on each slave that is to be semisynchronous.

On the master:

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

On each slave:

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

The preceding commands use a plugin file name suffix of .so. A different suffix might apply on your system. If you are not sure about the plugin file name, look for the plugins in the server's plugin directory.

If an attempt to install a plugin results in an error on Linux similar to that shown here, you will need to install libimf:

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
ERROR 1126 (HY000): Can't open shared library
'/usr/local/mysql/lib/plugin/semisync_master.so' (errno: 22 libimf.so: cannot open
shared object file: No such file or directory)

You can obtain libimf from http://dev.mysql.com/downloads/os-linux.html.

To see which plugins are installed, use the SHOW PLUGINS statement, or query the INFORMATION_SCHEMA.PLUGINS table.

After a semisynchronous replication plugin has been installed, it is disabled by default. The plugins must be enabled both on the master side and the slave side to enable semisynchronous replication. If only one side is enabled, replication will be asynchronous.

To control whether an installed plugin is enabled, set the appropriate system variables. You can set these variables at runtime using SET GLOBAL, or at server startup on the command line or in an option file.

At runtime, these master-side system variables are available:

mysql> SET GLOBAL rpl_semi_sync_master_enabled = {0|1};
mysql> SET GLOBAL rpl_semi_sync_master_timeout = N;

On the slave side, this system variable is available:

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = {0|1};

For rpl_semi_sync_master_enabled or rpl_semi_sync_slave_enabled, the value should be 1 to enable semisynchronous replication or 0 to disable it. By default, these variables are set to 0.

For rpl_semi_sync_master_timeout, the value N is given in milliseconds. The default value is 10000 (10 seconds).

If you enable semisynchronous replication on a slave at runtime, you must also start the slave I/O thread (stopping it first if it is already running) to cause the slave to connect to the master and register as a semisynchronous slave:

mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

If the I/O thread is already running and you do not restart it, the slave continues to use asynchronous replication.

At server startup, the variables that control semisynchronous replication can be set as command-line options or in an option file. A setting listed in an option file takes effect each time the server starts. For example, you can set the variables in my.cnf files on the master and slave sides as follows.

On the master:

[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 second

On each slave:

[mysqld]
rpl_semi_sync_slave_enabled=1

17.3.8.3 Semisynchronous Replication Monitoring

The plugins for the semisynchronous replication capability expose several system and status variables that you can examine to determine its configuration and operational state.

The system variable reflect how semisynchronous replication is configured. To check their values, use SHOW VARIABLES:

mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';

The status variables enable you to monitor the operation of semisynchronous replication. To check their values, use SHOW STATUS:

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';

When the master switches between asynchronous or semisynchronous replication due to commit-blocking timeout or a slave catching up, it sets the value of the Rpl_semi_sync_master_status status variable appropriately. Automatic fallback from semisynchronous to asynchronous replication on the master means that it is possible for the rpl_semi_sync_master_enabled system variable to have a value of 1 on the master side even when semisynchronous replication is in fact not operational at the moment. You can monitor the Rpl_semi_sync_master_status status variable to determine whether the master currently is using asynchronous or semisynchronous replication.

To see how many semisynchronous slaves are connected, check Rpl_semi_sync_master_clients.

The number of commits that have been acknowledged successfully or unsuccessfully by slaves are indicated by the Rpl_semi_sync_master_yes_tx and Rpl_semi_sync_master_no_tx variables.

On the slave side, Rpl_semi_sync_slave_status indicates whether semisynchronous replication currently is operational.

17.3.9 Delayed Replication

MySQL 5.6 supports delayed replication such that a slave server deliberately lags behind the master by at least a specified amount of time. The default delay is 0 seconds. Use the MASTER_DELAY option for CHANGE MASTER TO to set the delay to N seconds:

CHANGE MASTER TO MASTER_DELAY = N;

An event received from the master is not executed until at least N seconds later than its execution on the master. The exceptions are that there is no delay for format description events or log file rotation events, which affect only the internal state of the SQL thread.

Delayed replication can be used for several purposes:

  • To protect against user mistakes on the master. A DBA can roll back a delayed slave to the time just before the disaster.

  • To test how the system behaves when there is a lag. For example, in an application, a lag might be caused by a heavy load on the slave. However, it can be difficult to generate this load level. Delayed replication can simulate the lag without having to simulate the load. It can also be used to debug conditions related to a lagging slave.

  • To inspect what the database looked like long ago, without having to reload a backup. For example, if the delay is one week and the DBA needs to see what the database looked like before the last few days' worth of development, the delayed slave can be inspected.

START SLAVE and STOP SLAVE take effect immediately and ignore any delay. RESET SLAVE resets the delay to 0.

SHOW SLAVE STATUS has three fields that provide information about the delay:

  • SQL_Delay: A nonnegative integer indicating the number of seconds that the slave must lag the master.

  • SQL_Remaining_Delay: When Slave_SQL_Running_State is Waiting until MASTER_DELAY seconds after master executed event, this field contains an integer indicating the number of seconds left of the delay. At other times, this field is NULL.

  • Slave_SQL_Running_State: A string indicating the state of the SQL thread (analogous to Slave_IO_State). The value is identical to the State value of the SQL thread as displayed by SHOW PROCESSLIST.

When the slave SQL thread is waiting for the delay to elapse before executing an event, SHOW PROCESSLIST displays its State value as Waiting until MASTER_DELAY seconds after master executed event.

The relay-log.info file now contains the delay value, so the file format has changed. See Section 17.2.2.2, “Slave Status Logs”. In particular, the first line of the file now indicates how many lines are in the file. If you downgrade a slave server to a version older than MySQL 5.6, the older server will not read the file correctly. To address this, modify the file in a text editor to delete the initial line containing the number of lines.

17.4 Replication Notes and Tips

17.4.1 Replication Features and Issues

17.4.1.1 Replication and AUTO_INCREMENT
17.4.1.2 Replication and BLACKHOLE Tables
17.4.1.3 Replication and Character Sets
17.4.1.4 Replication of CREATE ... IF NOT EXISTS Statements
17.4.1.5 Replication of CREATE TABLE ... SELECT Statements
17.4.1.6 Replication of CREATE SERVER, ALTER SERVER, and DROP SERVER
17.4.1.7 Replication of CURRENT_USER()
17.4.1.8 Replication of DROP ... IF EXISTS Statements
17.4.1.9 Replication with Differing Table Definitions on Master and Slave
17.4.1.10 Replication and DIRECTORY Table Options
17.4.1.11 Replication of Invoked Features
17.4.1.12 Replication and Floating-Point Values
17.4.1.13 Replication and Fractional Seconds Support
17.4.1.14 Replication and FLUSH
17.4.1.15 Replication and System Functions
17.4.1.16 Replication and LIMIT
17.4.1.17 Replication and LOAD DATA INFILE
17.4.1.18 Replication and REPAIR TABLE
17.4.1.19 Replication and Master or Slave Shutdowns
17.4.1.20 Replication and max_allowed_packet
17.4.1.21 Replication and MEMORY Tables
17.4.1.22 Replication and Temporary Tables
17.4.1.23 Replication of the mysql System Database
17.4.1.24 Replication and the Query Optimizer
17.4.1.25 Replication and Reserved Words
17.4.1.26 Slave Errors During Replication
17.4.1.27 Replication of Server-Side Help Tables
17.4.1.28 Replication and Server SQL Mode
17.4.1.29 Replication Retries and Timeouts
17.4.1.30 Replication and Time Zones
17.4.1.31 Replication and Transactions
17.4.1.32 Replication and Triggers
17.4.1.33 Replication and TRUNCATE TABLE
17.4.1.34 Replication and Variables
17.4.1.35 Replication and Views

The following sections provide information about what is supported and what is not in MySQL replication, and about specific issues and situations that may occur when replicating certain statements.

Statement-based replication depends on compatibility at the SQL level between the master and slave. In others, successful SBR requires that any SQL features used be supported by both the master and the slave servers. For example, if you use a feature on the master server that is available only in MySQL 5.6 (or later), you cannot replicate to a slave that uses MySQL 5.5 (or earlier).

Such incompatibilities also can occur within a release series when using pre-production releases of MySQL. For example, the SLEEP() function is available beginning with MySQL 5.0.12. If you use this function on the master, you cannot replicate to a slave that uses MySQL 5.0.11 or earlier.

For this reason, use Generally Available (GA) releases of MySQL for statement-based replication in a production setting, since we do not introduce new SQL statements or change their behavior within a given release series once that series reaches GA release status.

If you are planning to use statement-based replication between MySQL 5.6 and a previous MySQL release series, it is also a good idea to consult the edition of the MySQL Reference Manual corresponding to the earlier release series for information regarding the replication characteristics of that series.

With MySQL's statement-based replication, there may be issues with replicating stored routines or triggers. You can avoid these issues by using MySQL's row-based replication instead. For a detailed list of issues, see Section 20.7, “Binary Logging of Stored Programs”. For more information about row-based logging and row-based replication, see Section 5.2.4.1, “Binary Logging Formats”, and Section 17.1.2, “Replication Formats”.

For additional information specific to replication and InnoDB, see Section 14.17, “InnoDB and MySQL Replication”. For information relating to replication with MySQL Cluster, see Section 18.6, “MySQL Cluster Replication”.

17.4.1.1 Replication and AUTO_INCREMENT

Statement-based replication of AUTO_INCREMENT, LAST_INSERT_ID(), and TIMESTAMP values is done correctly, subject to the following exceptions:

  • When using statement-based replication prior to MySQL 5.6.10, AUTO_INCREMENT columns in tables on the slave must match the same columns on the master; that is, AUTO_INCREMENT columns must be replicated to AUTO_INCREMENT columns. (Bug #12669186)

  • A statement invoking a trigger or function that causes an update to an AUTO_INCREMENT column is not replicated correctly using statement-based replication. In MySQL 5.6, such statements are marked as unsafe. (Bug #45677)

  • An INSERT into a table that has a composite primary key that includes an AUTO_INCREMENT column that is not the first column of this composite key is not safe for statement-based logging or replication. Beginning with MySQL 5.6.6, such statements are marked as unsafe. (Bug #11754117, Bug #45670)

    This issue does not affect tables using the InnoDB storage engine, since an InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column.

  • Adding an AUTO_INCREMENT column to a table with ALTER TABLE might not produce the same ordering of the rows on the slave and the master. This occurs because the order in which the rows are numbered depends on the specific storage engine used for the table and the order in which the rows were inserted. If it is important to have the same order on the master and slave, the rows must be ordered before assigning an AUTO_INCREMENT number. Assuming that you want to add an AUTO_INCREMENT column to a table t1 that has columns col1 and col2, the following statements produce a new table t2 identical to t1 but with an AUTO_INCREMENT column:

    CREATE TABLE t2 LIKE t1;
    ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
    INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;
    
    Important

    To guarantee the same ordering on both master and slave, the ORDER BY clause must name all columns of t1.

    The instructions just given are subject to the limitations of CREATE TABLE ... LIKE: Foreign key definitions are ignored, as are the DATA DIRECTORY and INDEX DIRECTORY table options. If a table definition includes any of those characteristics, create t2 using a CREATE TABLE statement that is identical to the one used to create t1, but with the addition of the AUTO_INCREMENT column.

    Regardless of the method used to create and populate the copy having the AUTO_INCREMENT column, the final step is to drop the original table and then rename the copy:

    DROP t1;
    ALTER TABLE t2 RENAME t1;
    

    See also Section B.5.7.1, “Problems with ALTER TABLE”.

17.4.1.2 Replication and BLACKHOLE Tables

The BLACKHOLE storage engine accepts data but discards it and does not store it. When performing binary logging, all inserts to such tables are always logged, regardless of the logging format in use. Updates and deletes are handled differently depending on whether statement based or row based logging is in use. With the statement based logging format, all statements affecting BLACKHOLE tables are logged, but their effects ignored. When using row-based logging, updates and deletes to such tables are simply skipped—they are not written to the binary log. In MySQL 5.6.12 and later, a warning is logged whenever this occurs (Bug #13004581)

For this reason we recommend when you replicate to tables using the BLACKHOLE storage engine that you have the binlog_format server variable set to STATEMENT, and not to either ROW or MIXED.

17.4.1.3 Replication and Character Sets

The following applies to replication between MySQL servers that use different character sets:

  • If the master uses MySQL 4.1, you must always use the same global character set and collation on the master and the slave, regardless of the slave MySQL version. (These are controlled by the --character-set-server and --collation-server options.) Otherwise, you may get duplicate-key errors on the slave, because a key that is unique in the master character set might not be unique in the slave character set. Note that this is not a cause for concern when master and slave are both MySQL 5.0 or later.

  • If the master is older than MySQL 4.1.3, the character set of any client should never be made different from its global value because this character set change is not known to the slave. In other words, clients should not use SET NAMES, SET CHARACTER SET, and so forth. If both the master and the slave are 4.1.3 or newer, clients can freely set session values for character set variables because these settings are written to the binary log and so are known to the slave. That is, clients can use SET NAMES or SET CHARACTER SET or can set variables such as collation_client or collation_server. However, clients are prevented from changing the global value of these variables; as stated previously, the master and slave must always have identical global character set values. This is true whether you are using statement-based or row-based replication.

  • If the master has databases with a character set different from the global character_set_server value, you should design your CREATE TABLE statements so that they do not implicitly rely on the database default character set. A good workaround is to state the character set and collation explicitly in CREATE TABLE statements.

17.4.1.4 Replication of CREATE ... IF NOT EXISTS Statements

MySQL applies these rules when various CREATE ... IF NOT EXISTS statements are replicated:

See also Bug #45574.

17.4.1.5 Replication of CREATE TABLE ... SELECT Statements

This section discusses how MySQL replicates CREATE TABLE ... SELECT statements.

MySQL 5.6 does not allow a CREATE TABLE ... SELECT statement to make any changes in tables other than the table that is created by the statement. This is a change in behavior from previous versions of MySQL, which permitted these statements to do so. This means that, when using statement-based replication between a MySQL 5.6 or later slave and a master running a previous version of MySQL, a CREATE TABLE ... SELECT statement causing changes in other tables on the master fails on the slave, causing replication to stop. To keep this from happening, you should use row-based replication, rewrite the offending statement before running it on the master, or upgrade the master to MySQL 5.6 (or later). (If you choose to upgrade the master, keep in mind that such a CREATE TABLE ... SELECT statement will fail following the upgrade unless it is rewritten to remove any side effects on other tables.) This is not an issue when using row-based replication, because the statement is logged as a CREATE TABLE statement with any changes to table data logged as row-insert events, rather than as the entire CREATE TABLE ... SELECT.

These behaviors are not dependent on MySQL version:

  • CREATE TABLE ... SELECT always performs an implicit commit (Section 13.3.3, “Statements That Cause an Implicit Commit”).

  • If destination table does not exist, logging occurs as follows. It does not matter whether IF NOT EXISTS is present.

    • STATEMENT or MIXED format: The statement is logged as written.

    • ROW format: The statement is logged as a CREATE TABLE statement followed by a series of insert-row events.

  • If the statement fails, nothing is logged. This includes the case that the destination table exists and IF NOT EXISTS is not given.

When the destination table exists and IF NOT EXISTS is given, MySQL handles the statement in a version-dependent way.

In MySQL 5.1 before 5.1.51 and in MySQL 5.5 before 5.5.6 (this is the original behavior):

  • STATEMENT or MIXED format: The statement is logged as written.

  • ROW format: The statement is logged as a CREATE TABLE statement followed by a series of insert-row events.

In MySQL 5.1 as of 5.1.51:

  • STATEMENT or MIXED format: The statement is logged as the equivalent pair of CREATE TABLE and INSERT INTO ... SELECT statements.

  • ROW format: The statement is logged as a CREATE TABLE statement followed by a series of insert-row events.

In MySQL 5.5 as of 5.5.6:

  • Nothing is inserted or logged.

These version dependencies arise due to a change in MySQL 5.5.6 in handling of CREATE TABLE ... SELECT not to insert rows if the destination table already exists, and a change made in MySQL 5.1.51 to preserve forward compatibility in replication of such statements from a 5.1 master to a 5.5 slave. For details, see Section 13.1.17.1, “CREATE TABLE ... SELECT Syntax”.

17.4.1.6 Replication of CREATE SERVER, ALTER SERVER, and DROP SERVER

In MySQL 5.6, the statements CREATE SERVER, ALTER SERVER, and DROP SERVER are not written to the binary log, regardless of the binary logging format that is in use.

17.4.1.7 Replication of CURRENT_USER()

The following statements support use of the CURRENT_USER() function to take the place of the name of (and, possibly, the host for) an affected user or a definer; in such cases, CURRENT_USER() is expanded where and as needed:

When CURRENT_USER() or CURRENT_USER is used as the definer in any of the statements CREATE FUNCTION, CREATE PROCEDURE, CREATE TRIGGER, CREATE EVENT, CREATE VIEW, or ALTER VIEW when binary logging is enabled, the function reference is expanded before it is written to the binary log, so that the statement refers to the same user on both the master and the slave when the statement is replicated. CURRENT_USER() or CURRENT_USER is also expanded prior to being written to the binary log when used in DROP USER, RENAME USER, GRANT, REVOKE, or ALTER EVENT.

17.4.1.8 Replication of DROP ... IF EXISTS Statements

The DROP DATABASE IF EXISTS, DROP TABLE IF EXISTS, and DROP VIEW IF EXISTS statements are always replicated, even if the database, table, or view to be dropped does not exist on the master. This is to ensure that the object to be dropped no longer exists on either the master or the slave, once the slave has caught up with the master.

DROP ... IF EXISTS statements for stored programs (stored procedures and functions, triggers, and events) are also replicated, even if the stored program to be dropped does not exist on the master.

17.4.1.9 Replication with Differing Table Definitions on Master and Slave

Source and target tables for replication do not have to be identical. A table on the master can have more or fewer columns than the slave's copy of the table. In addition, corresponding table columns on the master and the slave can use different data types, subject to certain conditions.

In all cases where the source and target tables do not have identical definitions, the database and table names must be the same on both the master and the slave. Additional conditions are discussed, with examples, in the following two sections.

17.4.1.9.1 Replication with More Columns on Master or Slave

You can replicate a table from the master to the slave such that the master and slave copies of the table have differing numbers of columns, subject to the following conditions:

  • Columns common to both versions of the table must be defined in the same order on the master and the slave.

    (This is true even if both tables have the same number of columns.)

  • Columns common to both versions of the table must be defined before any additional columns.

    This means that executing an ALTER TABLE statement on the slave where a new column is inserted into the table within the range of columns common to both tables causes replication to fail, as shown in the following example:

    Suppose that a table t, existing on the master and the slave, is defined by the following CREATE TABLE statement:

    CREATE TABLE t (
        c1 INT,
        c2 INT,
        c3 INT
    ); 
    

    Suppose that the ALTER TABLE statement shown here is executed on the slave:

    ALTER TABLE t ADD COLUMN cnew1 INT AFTER c3;
    

    The previous ALTER TABLE is permitted on the slave because the columns c1, c2, and c3 that are common to both versions of table t remain grouped together in both versions of the table, before any columns that differ.

    However, the following ALTER TABLE statement cannot be executed on the slave without causing replication to break:

    ALTER TABLE t ADD COLUMN cnew2 INT AFTER c2;
    

    Replication fails after execution on the slave of the ALTER TABLE statement just shown, because the new column cnew2 comes between columns common to both versions of t.

  • Each extra column in the version of the table having more columns must have a default value.

    A column's default value is determined by a number of factors, including its type, whether it is defined with a DEFAULT option, whether it is declared as NULL, and the server SQL mode in effect at the time of its creation; for more information, see Section 11.6, “Data Type Default Values”).

In addition, when the slave's copy of the table has more columns than the master's copy, each column common to the tables must use the same data type in both tables.

Examples.  The following examples illustrate some valid and invalid table definitions:

More columns on the master.  The following table definitions are valid and replicate correctly:

master> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
slave>  CREATE TABLE t1 (c1 INT, c2 INT);

The following table definitions would raise Error 1532 (ER_BINLOG_ROW_RBR_TO_SBR) because the definitions of the columns common to both versions of the table are in a different order on the slave than they are on the master:

master> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
slave>  CREATE TABLE t1 (c2 INT, c1 INT);

The following table definitions would also raise Error 1532 because the definition of the extra column on the master appears before the definitions of the columns common to both versions of the table:

master> CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);
slave>  CREATE TABLE t1 (c1 INT, c2 INT);

More columns on the slave.  The following table definitions are valid and replicate correctly:

master> CREATE TABLE t1 (c1 INT, c2 INT);
slave>  CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
                  

The following definitions raise Error 1532 because the columns common to both versions of the table are not defined in the same order on both the master and the slave:

master> CREATE TABLE t1 (c1 INT, c2 INT);
slave>  CREATE TABLE t1 (c2 INT, c1 INT, c3 INT);

The following table definitions also raise Error 1532 because the definition for the extra column in the slave's version of the table appears before the definitions for the columns which are common to both versions of the table:

master> CREATE TABLE t1 (c1 INT, c2 INT);
slave>  CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);

The following table definitions fail because the slave's version of the table has additional columns compared to the master's version, and the two versions of the table use different data types for the common column c2:

master> CREATE TABLE t1 (c1 INT, c2 BIGINT);
slave>  CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
17.4.1.9.2 Replication of Columns Having Different Data Types

Corresponding columns on the master's and the slave's copies of the same table ideally should have the same data type. However, beginning with MySQL 5.1.21, this is not always strictly enforced, as long as certain conditions are met.

All other things being equal, it is always possible to replicate from a column of a given data type to another column of the same type and same size or width, where applicable, or larger. For example, you can replicate from a CHAR(10) column to another CHAR(10), or from a CHAR(10) column to a CHAR(25) column without any problems. In certain cases, it also possible to replicate from a column having one data type (on the master) to a column having a different data type (on the slave); when the data type of the master's version of the column is promoted to a type that is the same size or larger on the slave, this is known as attribute promotion.

Attribute promotion can be used with both statement-based and row-based replication, and is not dependent on the storage engine used by either the master or the slave. However, the choice of logging format does have an effect on the type conversions that are permitted; the particulars are discussed later in this section.

Important

Whether you use statement-based or row-based replication, the slave's copy of the table cannot contain more columns than the master's copy if you wish to employ attribute promotion.

Statement-based replication.  When using statement-based replication, a simple rule of thumb to follow is, If the statement run on the master would also execute successfully on the slave, it should also replicate successfully. In other words, if the statement uses a value that is compatible with the type of a given column on the slave, the statement can be replicated. For example, you can insert any value that fits in a TINYINT column into a BIGINT column as well; it follows that, even if you change the type of a TINYINT column in the slave's copy of a table to BIGINT, any insert into that column on the master that succeeds should also succeed on the slave, since it is impossible to have a legal TINYINT value that is large enough to exceed a BIGINT column.

Prior to MySQL 5.6.10, when using statement-based replication, AUTO_INCREMENT columns were required to be the same on both the master and the slave; otherwise, updates could be applied to the wrong table on the slave. (Bug #12669186)

Row-based replication: attribute promotion and demotion.  Row-based replication in MySQL 5.6 supports attribute promotion and demotion between smaller data types and larger types. It is also possible to specify whether or not to permit lossy (truncated) or non-lossy conversions of demoted column values, as explained later in this section.

Lossy and non-lossy conversions.  In the event that the target type cannot represent the value being inserted, a decision must be made on how to handle the conversion. If we permit the conversion but truncate (or otherwise modify) the source value to achieve a fit in the target column, we make what is known as a lossy conversion. A conversion which does not require truncation or similar modifications to fit the source column value in the target column is a non-lossy conversion.

Type conversion modes (slave_type_conversions variable).  The setting of the slave_type_conversions global server variable controls the type conversion mode used on the slave. This variable takes a set of values from the following table, which shows the effects of each mode on the slave's type-conversion behavior:

ModeEffect
ALL_LOSSY

In this mode, type conversions that would mean loss of information are permitted.

This does not imply that non-lossy conversions are permitted, merely that only cases requiring either lossy conversions or no conversion at all are permitted; for example, enabling only this mode permits an INT column to be converted to TINYINT (a lossy conversion), but not a TINYINT column to an INT column (non-lossy). Attempting the latter conversion in this case would cause replication to stop with an error on the slave.

ALL_NON_LOSSY

This mode permits conversions that do not require truncation or other special handling of the source value; that is, it permits conversions where the target type has a wider range than the source type.

Setting this mode has no bearing on whether lossy conversions are permitted; this is controlled with the ALL_LOSSY mode. If only ALL_NON_LOSSY is set, but not ALL_LOSSY, then attempting a conversion that would result in the loss of data (such as INT to TINYINT, or CHAR(25) to VARCHAR(20)) causes the slave to stop with an error.

ALL_LOSSY,ALL_NON_LOSSY

When this mode is set, all supported type conversions are permitted, whether or not they are lossy conversions.

ALL_SIGNED

Treat promoted integer types as signed values (the default behavior).

ALL_UNSIGNED

Treat promoted integer types as unsigned values.

ALL_SIGNED,ALL_UNSIGNED

Treat promoted integer types as signed if possible, otherwise as unsigned.

[empty]

When slave_type_conversions is not set, no attribute promotion or demotion is permitted; this means that all columns in the source and target tables must be of the same types.

This mode is the default.

When an integer type is promoted, its signedness is not preserved. By default, the slave treats all such values as signed. Beginning with MySQL 5.6.13, you can control this behavior using ALL_SIGNED, ALL_UNSIGNED, or both. (Bug#15831300) ALL_SIGNED tells the slave to treat all promoted integer types as signed; ALL_UNSIGNED instructs it to treat these as unsigned. Specifying both causes the slave to treat the value as signed if possible, otherwise to treat it as unsigned; the order in which they are listed is not significant. Neither ALL_SIGNED nor ALL_UNSIGNED has any effect if at least one of ALL_LOSSY or ALL_NONLOSSY is not also used.

Changing the type conversion mode requires restarting the slave with the new slave_type_conversions setting.

Supported conversions.  Supported conversions between different but similar data types are shown in the following list:

  • Between any of the integer types TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT.

    This includes conversions between the signed and unsigned versions of these types.

    Lossy conversions are made by truncating the source value to the maximum (or minimum) permitted by the target column. For insuring non-lossy conversions when going from unsigned to signed types, the target column must be large enough to accommodate the range of values in the source column. For example, you can demote TINYINT UNSIGNED non-lossily to SMALLINT, but not to TINYINT.

  • Between any of the decimal types DECIMAL, FLOAT, DOUBLE, and NUMERIC.

    FLOAT to DOUBLE is a non-lossy conversion; DOUBLE to FLOAT can only be handled lossily. A conversion from DECIMAL(M,D) to DECIMAL(M',D') where D' >= D and (M'-D') >= (M-D) are non-lossy; for any case where M' < M, D' < D, or both, only a lossy conversion can be made.

    For any of the decimal types, if a value to be stored cannot be fit in the target type, the value is rounded down according to the rounding rules defined for the server elsewhere in the documentation. See Section 12.20.4, “Rounding Behavior”, for information about how this is done for decimal types.

  • Between any of the string types CHAR, VARCHAR, and TEXT, including conversions between different widths.

    Conversion of a CHAR, VARCHAR, or TEXT to a CHAR, VARCHAR, or TEXT column the same size or larger is never lossy. Lossy conversion is handled by inserting only the first N characters of the string on the slave, where N is the width of the target column.

    Important

    Replication between columns using different character sets is not supported.

  • Between any of the binary data types BINARY, VARBINARY, and BLOB, including conversions between different widths.

    Conversion of a BINARY, VARBINARY, or BLOB to a BINARY, VARBINARY, or BLOB column the same size or larger is never lossy. Lossy conversion is handled by inserting only the first N bytes of the string on the slave, where N is the width of the target column.

  • Between any 2 BIT columns of any 2 sizes.

    When inserting a value from a BIT(M) column into a BIT(M') column, where M' > M, the most significant bits of the BIT(M') columns are cleared (set to zero) and the M bits of the BIT(M) value are set as the least significant bits of the BIT(M') column.

    When inserting a value from a source BIT(M) column into a target BIT(M') column, where M' < M, the maximum possible value for the BIT(M') column is assigned; in other words, an all-set value is assigned to the target column.

Conversions between types not in the previous list are not permitted.

Replication type conversions in MySQL 5.5.3 and earlier.  Prior to MySQL 5.5.3, with row-based binary logging, you could not replicate between different INT subtypes, such as from TINYINT to BIGINT, because changes to columns of these types were represented differently from one another in the binary log when using row-based logging. (However, you could replicate from BLOB to TEXT using row-based replication because changes to BLOB and TEXT columns were represented using the same format in the binary log.)

Supported conversions for attribute promotion when using row-based replication prior to MySQL 5.5.3 are shown in the following table:

Note

In all cases, the size or width of the column on the slave must be equal to or greater than that of the column on the master. For example, you could replicate from a CHAR(10) column on the master to a column that used BINARY(10) or BINARY(25) on the slave, but you could not replicate from a CHAR(10) column on the master to BINARY(5) column on the slave.

Any unique index (including primary keys) having a prefix must use a prefix of the same length on both master and slave; in such cases, differing prefix lengths are disallowed. It is possible to use a nonunique index whose prefix length differs between master and slave, but this can cause serious performance issues, particularly when the prefix used on the master is longer. This is due to the fact that 2 unique prefixes of a given length may no longer be unique at a shorter length; for example, the words catalogue and catamount have the 5-character prefixes catal and catam, respectively, but share the same 4-character prefix (cata). This can lead to queries that use such indexes executing less efficiently on the slave, when a shorter prefix is employed in the slave' definition of the same index than on the master.

For DECIMAL and NUMERIC columns, both the mantissa (M) and the number of decimals (D) must be the same size or larger on the slave as compared with the master. For example, replication from a NUMERIC(5,4) to a DECIMAL(6,4) worked, but not from a NUMERIC(5,4) to a DECIMAL(5,3).

Prior to MySQL 5.5.3, MySQL replication did not support attribute promotion of any of the following data types to or from any other data type when using row-based replication:

  • INT (including TINYINT, SMALLINT, MEDIUMINT, BIGINT).

    Promotion between INT subtypes—for example, from SMALLINT to BIGINT—was also not supported prior to MySQL 5.5.3.

  • SET or ENUM.

  • FLOAT or DOUBLE.

  • All of the data types relating to dates, times, or both: DATE, TIME, DATETIME, TIMESTAMP, and YEAR.

17.4.1.10 Replication and DIRECTORY Table Options

If a DATA DIRECTORY or INDEX DIRECTORY table option is used in a CREATE TABLE statement on the master server, the table option is also used on the slave. This can cause problems if no corresponding directory exists in the slave host file system or if it exists but is not accessible to the slave server. This can be overridden by using the NO_DIR_IN_CREATE server SQL mode on the slave, which causes the slave to ignore the DATA DIRECTORY and INDEX DIRECTORY table options when replicating CREATE TABLE statements. The result is that MyISAM data and index files are created in the table's database directory.

For more information, see Section 5.1.7, “Server SQL Modes”.

17.4.1.11 Replication of Invoked Features

Replication of invoked features such as user-defined functions (UDFs) and stored programs (stored procedures and functions, triggers, and events) provides the following characteristics:

  • The effects of the feature are always replicated.

  • The following statements are replicated using statement-based replication:

    However, the effects of features created, modified, or dropped using these statements are replicated using row-based replication.

    Note

    Attempting to replicate invoked features using statement-based replication produces the warning Statement is not safe to log in statement format. For example, trying to replicate a UDF with statement-based replication generates this warning because it currently cannot be determined by the MySQL server whether the UDF is deterministic. If you are absolutely certain that the invoked feature's effects are deterministic, you can safely disregard such warnings.

  • In the case of CREATE EVENT and ALTER EVENT:

  • The feature implementation resides on the slave in a renewable state so that if the master fails, the slave can be used as the master without loss of event processing.

To determine whether there are any scheduled events on a MySQL server that were created on a different server (that was acting as a replication master), query the INFORMATION_SCHEMA.EVENTS table in a manner similar to what is shown here:

SELECT EVENT_SCHEMA, EVENT_NAME
    FROM INFORMATION_SCHEMA.EVENTS
    WHERE STATUS = 'SLAVESIDE_DISABLED';

Alternatively, you can use the SHOW EVENTS statement, like this:

SHOW EVENTS
    WHERE STATUS = 'SLAVESIDE_DISABLED';

When promoting a replication slave having such events to a replication master, you must enable each event using ALTER EVENT event_name ENABLED, where event_name is the name of the event.

If more than one master was involved in creating events on this slave, and you wish to identify events that were created only on a given master having the server ID master_id, modify the previous query on the EVENTS table to include the ORIGINATOR column, as shown here:

SELECT EVENT_SCHEMA, EVENT_NAME, ORIGINATOR
    FROM INFORMATION_SCHEMA.EVENTS
    WHERE STATUS = 'SLAVESIDE_DISABLED'
    AND   ORIGINATOR = 'master_id'

You can employ ORIGINATOR with the SHOW EVENTS statement in a similar fashion:

SHOW EVENTS
    WHERE STATUS = 'SLAVESIDE_DISABLED'
    AND   ORIGINATOR = 'master_id'

Before enabling events that were replicated from the master, you should disable the MySQL Event Scheduler on the slave (using a statement such as SET GLOBAL event_scheduler = OFF;), run any necessary ALTER EVENT statements, restart the server, then re-enable the Event Scheduler on the slave afterward (using a statement such as SET GLOBAL event_scheduler = ON;)-

If you later demote the new master back to being a replication slave, you must disable manually all events enabled by the ALTER EVENT statements. You can do this by storing in a separate table the event names from the SELECT statement shown previously, or using ALTER EVENT statements to rename the events with a common prefix such as replicated_ to identify them.

If you rename the events, then when demoting this server back to being a replication slave, you can identify the events by querying the EVENTS table, as shown here:

SELECT CONCAT(EVENT_SCHEMA, '.', EVENT_NAME) AS 'Db.Event'
      FROM INFORMATION_SCHEMA.EVENTS
      WHERE INSTR(EVENT_NAME, 'replicated_') = 1;

17.4.1.12 Replication and Floating-Point Values

With statement-based replication, values are converted from decimal to binary. Because conversions between decimal and binary representations of them may be approximate, comparisons involving floating-point values are inexact. This is true for operations that use floating-point values explicitly, or that use values that are converted to floating-point implicitly. Comparisons of floating-point values might yield different results on master and slave servers due to differences in computer architecture, the compiler used to build MySQL, and so forth. See Section 12.2, “Type Conversion in Expression Evaluation”, and Section B.5.5.8, “Problems with Floating-Point Values”.

17.4.1.13 Replication and Fractional Seconds Support

MySQL 5.6.4 and up permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision. See Section 11.3.6, “Fractional Seconds in Time Values”.

There may be problems replicating from a master server that understands fractional seconds to an older slave that does not:

  • For CREATE TABLE statements containing columns that have an fsp (fractional seconds precision) value greater than 0, replication will fail due to parser errors.

  • Statements that use temporal data types with an fsp value of 0 will work for with statement-based logging but not row-based logging. In the latter case, the data types have binary formats and type codes on the master that differ from those on the slave.

  • Some expression results will differ on master and slave. Examples: On the master, the timestamp system variable returns a value that includes a microseconds fractional part; on the slave, it returns an integer. On the master, functions that return a result that includes the current time (such as CURTIME(), SYSDATE(), or UTC_TIMESTAMP()) interpret an argument as an fsp value and the return value includes a fractional seconds part of that many digits. On the slave, these functions permit an argument but ignore it.

17.4.1.14 Replication and FLUSH

Some forms of the FLUSH statement are not logged because they could cause problems if replicated to a slave: FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK. For a syntax example, see Section 13.7.6.3, “FLUSH Syntax”. The FLUSH TABLES, ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements are written to the binary log and thus replicated to slaves. This is not normally a problem because these statements do not modify table data.

However, this behavior can cause difficulties under certain circumstances. If you replicate the privilege tables in the mysql database and update those tables directly without using GRANT, you must issue a FLUSH PRIVILEGES on the slaves to put the new privileges into effect. In addition, if you use FLUSH TABLES when renaming a MyISAM table that is part of a MERGE table, you must issue FLUSH TABLES manually on the slaves. These statements are written to the binary log unless you specify NO_WRITE_TO_BINLOG or its alias LOCAL.

17.4.1.15 Replication and System Functions

Certain functions do not replicate well under some conditions: