Chapter 6. Replication
Prev   Next

Chapter 6. Replication

Table of Contents

6.1. Replication Configuration
6.1.1. How to Set Up Replication
6.1.2. Replication Formats
6.1.3. Replication Options and Variables
6.1.4. Common Replication Administration Tasks
6.2. Replication Solutions
6.2.1. Using Replication for Backups
6.2.2. Using Replication with different Master and Slave Storage Engines
6.2.3. Using Replication for Scale-out
6.2.4. Replicating Different Databases to Different Slaves
6.2.5. Improving Replication Performance
6.2.6. Switching Masters During Failover
6.2.7. Setting up Replication using SSL
6.3. Replication Notes and Tips
6.3.1. Replication Features and Issues
6.3.2. Replication Compatibility Between MySQL Versions
6.3.3. Upgrading a Replication Setup
6.3.4. Replication FAQ
6.3.5. Troubleshooting Replication
6.3.6. How to Report Replication Bugs or Problems
6.4. Replication Implementation
6.4.1. Replication Implementation Details
6.4.2. Replication Master Thread States
6.4.3. Replication Slave I/O Thread States
6.4.4. Replication Slave SQL Thread States
6.4.5. Replication Relay and Status Files
6.4.6. How Servers Evaluate Replication Rules

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

The target uses for 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 15, MySQL Cluster).

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 6.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), which is the default mode within MySQL 5.1.14 and later. For more information on the different replication formats, see Section 6.1.2, “Replication Formats”.

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 6.1.3, “Replication 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 for use as part of a larger solution to alleviate system failures. For information on how to address these issues, see Section 6.2, “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 6.3, “Replication Notes and Tips”.

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 6.4, “Replication Implementation”.

MySQL Enterprise The MySQL Network Monitoring and Advisory Service provides numerous advisors that provide immediate feedback about replication-related problems. For more information see, http://www.mysql.com/products/enterprise/advisors.html.

6.1. Replication Configuration

6.1.1. How to Set Up Replication
6.1.2. Replication Formats
6.1.3. Replication Options and Variables
6.1.4. Common Replication Administration Tasks

Replication between servers in MySQL works through the use of the binary logging mechanism. The MySQL instance operating as the master (the source of the database changes) writes updates and changes to the database 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.

The Master is dumb in this scenario. Once binary logging has been enabled, all statements are recorded in the binary log. Each slave will receive 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 only process events that apply to particular databases or tables.

Slaves keep a record of the binary log file and position within the log file that they have 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 remembers the position within the binary log, it is possible for slaves to be disconnected, reconnect and then 'catch up' by continuing from the recorded position.

Both the master and each slave must be configured with a unique id (using the server-id option). In addition, the 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 statement. The details are stored within the master.info file.

In this section the setup and configuration required for a replication environment is described, including step-by-step instructions for creating a new replication environment. The major components of this section are:

  • For a guide to setting up two or more servers for replication see Section 6.1.1, “How to Set Up Replication”. This section deals with the setup of the systems and provides methods for copying data between the master and slaves.

  • Events in the binary log are recorded using a number of formats. These are referred to as statement based replication (SBR) or row based replication (RBR). A third type, mixed-format replication (MIXED), uses SBR or RBR replication automatically to take advantage of the benefits of both SBR and RBR formats when appropriate. The different formats are discussed in Section 6.1.2, “Replication Formats”.

  • Detailed information on the different configuration options and variables that apply to replication is provided in Section 6.1.3, “Replication Options and Variables”.

  • Once started, the replication process should require little administration or monitoring. However, for advice on common tasks that you may want to executed, see Section 6.1.4, “Common Replication Administration Tasks”.

6.1.1. How to Set Up Replication

6.1.1.1. Creating a User For Replication
6.1.1.2. Setting the Replication Master Configuration
6.1.1.3. Setting the Replication Slave Configuration
6.1.1.4. Obtaining the Master Replication Information
6.1.1.5. Creating a Data Snapshot using mysqldump
6.1.1.6. Creating a Data Snapshot Using Raw Data Files
6.1.1.7. Setting up Replication with new Master and Slaves
6.1.1.8. Setting up replication with existing data
6.1.1.9. Introducing Additional Slaves to an Existing Replication Environment
6.1.1.10. Setting the Master Configuration on the Slave

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 that you use will depend on how you are setting up replication, and whether you already have data within your master database.

There are some generic tasks which may be required for all replication setups:

Once you have configured the basic options, you will need to follow the instructions for your replication setup. A number of alternatives are provided:

If you want to administer a MySQL replication setup, we suggest that you read this entire chapter through and try all statements mentioned in Section 13.6.1, “SQL Statements for Controlling Master Servers”, and Section 13.6.2, “SQL Statements for Controlling Slave Servers”. You should also familiarize yourself with the replication startup options described in Section 6.1.3, “Replication Options and Variables”.

Note

Note that certain steps within the setup process require the SUPER privilege. If you do not have this privilege then enabling replication may not be possible.

6.1.1.1. Creating a User For Replication

Each Slave must connect to the Master using a standard username and password. The user that you use for this operation can be any user, providing they have been granted the REPLICATION SLAVE privilege.

You do not need to create a specific user for replication. However, you should be aware that the username and password will be stored in plain text within the master.info file. Therefore you may want to create a user that only has privileges for the replication process.

To create a user or grant an existing user the privileges required for replication use the GRANT statement. If you create a user solely for the purposes of replication then that user only needs the REPLICATION SLAVE privilege. For example, to create a user, repl, that allows all hosts within the domain mydomain.com to connect for replication: 

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

See Section 13.5.1.3, “GRANT Syntax”, for more information on the GRANT statement.

You may wish to create a different user for each slave, or use the same user for each slave that needs to connect. As long as each user that you want to use for the replication process has the REPLICATION SLAVE privilege you can create as many users as you require.

6.1.1.2. Setting the Replication Master Configuration

For replication to work you must enable binary logging on the master. If binary logging is not enabled, replication will not be possible as it is the binary log that is used to exchange data between the master and slaves.

Each server within a replication group must have a unique server-id. The server-id is used to identify individual servers within the group, and must be positive integer between 1 and (232)-1). How you organize and select the numbers is entirely up to you.

To configure both these options you will need to shutdown your MySQL server and edit the configuration of the my.cnf or my.ini file.

You will need to add the following options to the configuration file within the [mysqld] section. 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 filename prefix of mysql-bin, and setting a server ID of 1: 

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

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 has not been enabled on your replication master. If networking has been disabled, then your slave will not able to communicate with the master and replication will fail.

6.1.1.3. Setting the Replication Slave Configuration

The only option you must configure on the slave is to set the unique server ID. If this option is not already set, or the current value conflicts with the value that you have chosen for the master server, then you should shutdown your slave server, and edit the configuration to specify the server id. For example: 

[mysqld]
server-id=2

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 each of the other slaves. Think of server-id values as something similar to IP addresses: These IDs uniquely identify each server instance in the community of replication partners.

If you do not specify a server-id value, it is set to 1 if you have not defined master-host; otherwise it is set to 2. Note that in the case of server-id omission, a master refuses connections from all slaves, and a slave refuses to connect to a master. Thus, omitting server-id is good only for backup with a binary log.

You do not have to enable binary logging on the slave for replication to be enabled. However, if you enable binary logging on the slave then you can use the binary log for data backups and crash recovery on the slave, and also use the slave as part of a more complex replication topology.

6.1.1.4. Obtaining the Master Replication Information

To configure replication on the slave you must determine the masters current point within the master binary log. You will need this information so that when the slave starts the replication process, it is able to start processing events from the binary log 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, then you must stop processing statements on the master, obtain the current position, and then dump the data, before allowing the master to continue executing statements. If you do not stop the execution of statements then the data dump, the master status information that you use will not match and you will end up with inconsistent or corrupted databases on the slaves.

To get the master status information, follow these steps:

  1. Start 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. Use the SHOW MASTER STATUS statement to determine the current binary log name and offset on the master: 

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

    The File column shows the name of the log and Position shows the offset within the file. In this example, the binary log file is mysql-bin.003 and the offset 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 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 synchronised with the slave before you start replication, leave the client running so that the lock remains in place and then proceed to Section 6.1.1.5, “Creating a Data Snapshot using mysqldump”, or Section 6.1.1.6, “Creating a Data Snapshot Using Raw Data Files”.

If you are setting up a brand new master and slave replication group, then you can exit the client and release the locks.

6.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. Once the data dump has been completed, you then import this data into the slave before starting the replication process.

To obtain a snapshot of the data using mysqldump:

  • If you haven't already locked the tables on the server to prevent queries that update data from executing:

    Start 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;

    Remember to use SHOW MASTER STATUS and record the binary log details for use when starting up the slave. The point in time of your snapshot and the binary log position must match. See Section 6.1.1.4, “Obtaining the Master Replication Information”.

  • In another session, use mysqldump to create a dump either of all the databases you want to replicate, or by selecting specific databases individually. For example: 

    shell> mysqldump --all-databases --lock-all-tables >dbdump.db

  • An alternative to using a bare dump, is to use the --master-data option, which will automatically append the CHANGE MASTER statement required on the slave to start the replication process. 

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

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

You will need either to copy the dump file to the slave, or to use the file from the master when connecting remotely to the slave to import the data.

6.1.1.6. Creating a Data Snapshot Using Raw Data Files

If your database is particularly large then copying the raw data files may be more efficient than using mysqldump and importing the file on each slave.

However, using this method with tables in storage engines with complex cache and/or logging algorithms may not give you a perfect 'in time' snapshot as cache information and logging updates may not have been applied, even if you have acquired a global read lock. How the storage engine responds to this will depend on the crash recovery abilities.

For example, when you have acquired a global read lock, you can start a filesystem snapshot of your InnoDB tables. Internally (inside the InnoDB storage engine) the snapshot won't be consistent (because the InnoDB caches are not flushed), but this is not a cause for concern, because InnoDB resolves this at startup and delivers a consistent result. This means that InnoDB can perform crash recovery when started on this snapshot, without corruption. However, there is no way to stop the MySQL server while insuring a consistent snapshot of your InnoDB tables.

To create your raw data snapshot 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 filesystem. If you are only replicating certain databases then make sure you only copy those files that related to those tables. For InnoDB, all tables in all databases are stored in a single file unless you have the innodb_file_per_table option enabled.

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

  • Files relating to the mysql database.

  • The master.info file.

  • The master's binary log files.

  • Any relay log files.

To get the most consistent results with a raw data snapshot you should shut down the server during the process, as below:

  1. Acquire a read lock and get the master status. See Section 6.1.1.4, “Obtaining the Master Replication Information”.

  2. In a separate session, shutdown the MySQL server: 

    shell> mysqladmin shutdown

  3. Take a copy of the MySQL data files. Examples are shown below for common solutions - you need to choose only one of these solutions: 

    shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata

  4. Startup the MySQL instance on the master.

To get a snapshot of the system from a master without shutting down the database:

  1. Acquire a read lock and get the master status. See Section 6.1.1.4, “Obtaining the Master Replication Information”.

  2. Take a copy of the MySQL data files. Examples are shown below for common solutions - you need to choose only one of these solutions: 

    shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata

    If you are using InnoDB tables, ideally you should use the InnoDB Hot Backup tool, which takes a consistent snapshot without acquiring any locks on the master server, and records the log name and offset corresponding to the snapshot to be later used on the slave. Hot Backup is an additional non-free (commercial) tool that is not included in the standard MySQL distribution. See the InnoDB Hot Backup home page at http://www.innodb.com/manual.php for detailed information.

  3. In the client where you acquired read lock, free the lock: 

    mysql> UNLOCK TABLES;

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

6.1.1.7. Setting up Replication with new Master and Slaves

Setting up replication with a new Master and Slaves (i.e. with no existing data) is the easiest and most straightforward method for setting up replication.

You can also use this method if you are setting up new servers and have an existing dump of the databases that you want to load into your replication configuration. By loading the data onto 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 6.1.1.2, “Setting the Replication Master Configuration”.

  2. Start up the MySQL master.

  3. Setup a user, see Section 6.1.1.1, “Creating a User For Replication”.

  4. Obtain the master status information. See Section 6.1.1.4, “Obtaining the Master Replication Information”.

  5. Free the read lock: 

    mysql> UNLOCK TABLES;

  6. On the slave, edit the MySQL configuration. See Section 6.1.1.3, “Setting the Replication Slave Configuration”.

  7. Start up the MySQL slave.

  8. Execute the CHANGE MASTER command to set the master replication server configuration.

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 an existing database server, you will now need to run the dump file on the master. The database updates will automatically be propagated to the slaves: 

shell> mysql -h master < fulldb.dump

6.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. If you have not already configured the server-id and binary logging, you will need to shutdown your master to configure these options. See Section 6.1.1.2, “Setting the Replication Master Configuration”.

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

  2. If your server is already correctly configured, obtain the master status (see Section 6.1.1.4, “Obtaining the Master Replication Information”) and then use mysqldump to take a snapshot (see Section 6.1.1.5, “Creating a Data Snapshot using mysqldump”) or take a raw snapshot of the live database using the guide in Section 6.1.1.6, “Creating a Data Snapshot Using Raw Data Files”.

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

  4. Update the configuration of the slave, see Section 6.1.1.3, “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. Startup the slave, skipping replication by using the --skip-slave option.

    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 to match the configuration of your slave.

    2. Startup the slave, skipping replication by using the --skip-slave option.

  6. Configure the slave with the master status information. This will tell the slave the binary log file and position within the file where replication needs to start, and configure the login credentials and hostname of the master. For more information on the statement required, see Section 6.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.

Once a slave is replicating, you can find in its data directory one file named master.info and another named relay-log.info. The slave uses these two files to keep track of how much of the master's binary log it has processed. Do not remove or edit these files 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 will use the values specified in the statement to update the status files automatically.

Note

The content of master.info overrides some of the server options specified on the command line or in my.cnf. See Section 6.1.3, “Replication Options and Variables”, for more details.

Once you have a snapshot of the master, you can use it to set up other slaves by following the slave portion of the procedure just described. You do not need to take another snapshot of the master; you can use the same one for each slave.

6.1.1.9. Introducing Additional Slaves to an Existing Replication Environment

If you want to add another slave to the existing replication configuration then you can do so without stopping the master. Instead, you duplicate the settings on the slaves.

To duplicate the slave:

  1. Shutdown the existing slave (slavea): 

    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 you also copy the log files and relay log files.

  3. Copy the master.info and relay.info files from the existing slave to the new slave. These files hold the current log positions.

  4. Start the existing slave.

  5. On the new slave, edit the configuration and the give the new slave a new unique server-id.

  6. Start the new slave; the master.info file options will be used to start the replication process.

6.1.1.10. Setting the Master Configuration on the Slave

To setup 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;

The following table shows the maximum allowable length for the string-valued options:

MASTER_HOST60
MASTER_USER16
MASTER_PASSWORD32
MASTER_LOG_FILE255

6.1.2. Replication Formats

6.1.2.1. Setting The Replication Format
6.1.2.2. Mixed Replication Format
6.1.2.3. Comparison of Statement-Based Versus Row-Based Replication
6.1.2.4. Logging Format for Changes to mysql Database Tables

Replication works because events written to the binary log are read from the master and then processed on the slave. The events are recorded in different formats according the event being recorded. The different formats are as follows:

  • Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based replication (SBR).

  • In row-based replication (RBR), the master writes events to the binary log that indicate how individual table rows are affected. Support for RBR was added in MySQL 5.1.5.

  • As of MySQL 5.1.8, a third option is available: mixed-based replication (MBR). With MBR, statement-based replication is used by default, but automatically switches to row-based replication in particular cases as described below. See Section 6.1.2.2, “Mixed Replication Format”.

Starting with MySQL 5.1.12, mixed-based replication (MBR) is the default format for all replication environment unless you specify otherwise.

For a comparison that shows the advantages and disadvantages of statement-based and row-based replication, see Section 6.1.2.3, “Comparison of Statement-Based Versus Row-Based Replication”.

MySQL Cluster Replication also makes use of row-based replication. For more information, see Section 15.11, “MySQL Cluster Replication”.

With MySQL's classic 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 18.4, “Binary Logging of Stored Routines and Triggers”.

If you build MySQL from source, row-based replication is available by default unless you invoke configure with the --without-row-based-replication option.

6.1.2.1. Setting The Replication Format

The default replication format depends on the version of MySQL you are using:

  • For MySQL 5.1.11 and earlier, statement-based replication is used by default.

  • For MySQL 5.1.12 and later, mixed-based replication is used by default.

You can force the default replication format by specifying the format type to the --binlog-format=type option. When set, all replication slaves connecting to the server will read the events according to this setting. The supported options are:

  • ROW — sets row-based replication as the default.

  • STATEMENT — sets statement-based replication as the default. This is the default for MySQL 5.1.11 and earlier.

  • MIXED — sets mixed-based replication as the default. This is the default for MySQL 5.1.12 and later.

The logging format also can be switched at runtime. To specify the format globally for all clients, set the global value of the binlog_format system variable. (To change a global variable you need the SUPER privilege.)

To switch to statement-based format, use either of these statements: 

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 1;

To switch to row-based format, use either of these statements: 

mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 2;

To switch to mixed format, use either of these statements: 

mysql> SET GLOBAL binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 3;

Individual clients can control the logging format for their own statements by setting the session value of binlog_format. For example: 

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

In addition to switching the logging format manually, a slave server may switch the format automatically. This happens when the server is running in either STATEMENT or MIXED format and encounters a row in the binary log that is written in ROW logging format. In that case, the slave switches to row-based replication temporarily for that event, and switches back to the previous format afterwards.

There are two reasons why you might want to set replication logging on a per-connection basis:

  • A thread that makes many small changes to the database might want to use row-based logging. A thread that performs updates that match many rows in the WHERE clause might want to use statement-based logging because it will be more efficient to log a few statements than many rows.

  • Some statements require a lot of execution time on the master, but result in just a few rows being modified. It might therefore be beneficial to replicate them using row-based logging.

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

  • From within a stored function or a trigger.

  • If NDB is enabled.

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

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

Switching the replication format at runtime is not recommended when any temporary tables exist, because temporary tables are logged only when using statement-based replication, whereas with row-based replication they are not logged. With mixed replication, temporary tables are usually logged; exceptions happen with user-defined functions (UDF) and with the UUID() function.

With the binlog format set to ROW, many changes are written to the binary log using the row-based format. Some changes, however, still use the statement-based format. Examples include all DDL (data definition language) statements such as CREATE TABLE, ALTER TABLE, or DROP TABLE.

The --binlog-row-event-max-size option is available for servers that are capable of row-based replication. Rows are stored into the binary log in chunks having a size in bytes not exceeding the value of this option. The value must be a multiple of 256. The default value is 1024.

Warning

When using row-based replication It is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is non-deterministic; that is, it is left to the will of the query optimizer. In general, this is not a good practice even outside of replication. For a detailed explanation of this issue, see Section B.1.8.1, “Open Issues in MySQL”.

6.1.2.2. Mixed Replication Format

When running in MIXED mode, automatic switching from statement-based to row-based replication takes place under the following conditions:

  • When a DML statement updates an NDB table

  • When a function contains UUID()

  • When 2 or more tables with AUTO_INCREMENT columns are updated

  • When any INSERT DELAYED is executed

  • When the body of a view requires row-based replication, the statement creating the view also uses it — for example, this occurs when the statement creating a view uses the UUID() function

  • When a call to a UDF is involved.

6.1.2.3. Comparison of Statement-Based Versus Row-Based Replication

Each binary logging format has advantages and disadvantages. For most users, the mixed-based replication format should be fine and should provide the best combination of data integrity and performance. If, however, you want to take advantage of the differences in the replication format when performing specific updates or large data inserts, then the information in this section summarizes the advantages and disadvantages of the row and statement based formats.

Advantages of statement-based replication:

  • Proven technology that has existed in MySQL since 3.23.

  • Smaller log files. When updates or deletes affect many rows, much smaller log files. Smaller log files require less storage space and are faster to back up.

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

  • Log files can be used for point-in-time recovery, not just for replication purposes. See Section 5.9.3, “Point-in-Time Recovery”.

  • You can use a slave with a higher version than that used on the master, even when there is a different row structure in the table. This can be useful if you are unable to upgrade the master but want to take advantage of features in a recent slave version, perhaps for testing and evaluation purposes.

Disadvantages of statement-based replication:

  • Not all UPDATE statements can be replicated: Any non-deterministic behavior (for example, when using random functions in an SQL statement) is hard to replicate when using statement-based replication. For statements that use a non-deterministic user-defined function (UDF), it is not possible to replicate the result using statement-based replication, whereas row-based replication will just replicate the value returned by the UDF.

  • Statements cannot be replicated properly if they use a UDF that is non-deterministic (its value depends on other factors than the given parameters).

  • Statements that use one of the following functions cannot be replicated properly:

    • LOAD_FILE()

    • UUID()

    • USER()

    • FOUND_ROWS()

    • SYSDATE() (unless the server is started with the --sysdate-is-now option)

    All other functions are replicated correctly (including RAND(), NOW(), LOAD DATA INFILE, and so forth).

  • 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 non-conflicting INSERT statements.

  • For complex queries, 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 run the statement to apply the differences, not the full query.

  • Stored functions (not stored procedures) will execute with the same NOW() value as the calling statement. (This may be regarded both as a bad thing and a good thing.)

  • Deterministic UDFs must be applied on the slaves.

  • If there is an error in evaluation on the slave, particularly when executing complex queries, then using statement based replication may slowly increase the margin of error across the affected rows over time.

  • Tables have to be (almost) identical on master and slave.

Advantages of row-based replication:

  • Everything can be replicated. This is the safest form of replication.

    For MySQL versions earlier than 5.1.14, DDL (data definition language) statements such as CREATE TABLE are replicated using statement-based replication, while DML (data manipulation language) statements, as well as GRANT and REVOKE statements, are replicated using row-based-replication.

    For MySQL 5.1.14 and later, the mysql database is not replicated. The mysql database is instead seen as a node specific database. Row-based replication is not supported on this table. Instead, statements that would normally update this information (including GRANT, REVOKE and the manipulation of triggers, stored routines/procedures and views are all replicated to slaves using Statement based replication.

    For statements like CREATE ... SELECT, a CREATE statement is generated from the table definition and replicated statement-based, while the row insertions are replicated row-based.

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

  • In many cases, it is faster to apply data on the slave for tables that have primary keys.

  • Fewer locks needed (and thus higher concurrency) on the master for the following types of statements:

    • INSERT ... SELECT

    • INSERT statements with AUTO_INCREMENT

    • UPDATE or DELETE statements with WHERE clauses that don't use keys or don't change most of the examined rows.

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

  • It's possible to add multiple threads to apply data on the slave in the future (works better on SMP machines).

Disadvantages of row-based replication:

  • Larger log files (much larger in some cases).

  • Binary log will contain data for large statements that were rolled back.

  • When using row-based replication to replicate a statement (for example, an UPDATE or DELETE statement), each changed row must be written to the binary log. In contrast, when using statement-based replication, only the statement is written to the binary log. If the statement changes many rows, row-based replication may write significantly more data to the binary log. In these cases the binary log will be locked for a longer time to write the data, which may cause concurrency problems.

  • Deterministic UDFs that generate large BLOB values will be notably slower to replicate.

  • You cannot examine the logs to see what statements were executed.

  • You cannot see on the slave what statements were received from the master and executed.

  • When making a bulk operation that includes non-transactional storage engines, changes are applied as the statement executes. With row-based replication logging, this means that the binary log is written while the statement is running. On the master, this doesn't provide any problems with concurrency, because tables are locked until the bulk operation terminates. On the slave server, however, tables aren't locked while the slave applies changes, because it doesn't know that those changes are part of a bulk operation.

    In that scenario, if you retrieve data from a table on the master (for example, SELECT * FROM table_name), the server will wait for the bulk operation to complete before executing the SELECT statement, because the table is read-locked. On the slave, the server won't wait (because there is no lock). This means that until the “bulk operation” on the slave has completed you will get different results for the same SELECT query on the master and on the slave.

    This behavior will eventually change, but until it does, you should probably use statement-based replication in a scenario like this.

6.1.2.4. Logging Format for Changes to mysql Database Tables

The contents of the grant tables in the mysql database can be modified directly (for example, with INSERT or DELETE) or indirectly (for example, with GRANT or CREATE USER). As of MySQL 5.1.17, statements that affect mysql database tables are written to the binary log using the following rules:

  • Data manipulation statements that change data in mysql database tables directly are logged according to the setting of the binlog_format system variable. This pertains to statements such as INSERT, UPDATE, DELETE, REPLACE, DO, LOAD DATA INFILE, SELECT, and TRUNCATE.

  • Statements that change the mysql database indirectly are logged as statements regardless of the value of binlog_format. This pertains to statements such as GRANT, REVOKE, SET PASSWORD, RENAME USER, CREATE (all forms except CREATE TABLE ... SELECT), ALTER (all forms), and DROP (all forms).

CREATE TABLE ... SELECT is a combination of data definition and data manipulation. The CREATE TABLE part is logged using statement format and the SELECT part is logged according to the value of binlog_format.

6.1.3. Replication Options and Variables

This section describes the options that you can use on slave replication servers. You can specify these options either on the command line or in an option file.

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. Example: server-id=3

Options that you can use on the master server for controlling binary logging are described in Section 5.11.4, “The Binary Log”.

Certain options are handled in a special way in order to ensure that the active replication configuration is not inadvertently altered or affected. 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

In MySQL 5.1.17 and later the use of these options is deprecated. The settings they alter are ignored when mysqld is started and a warning will be provided in the mysqld log. To configure replication, you must use the CHANGE MASTER TO ... statement.

In MySQL 5.1.16 and earlier, these options are ignored if the master.info file exists (i.e. when the MySQL server has already previously been configured for replication). If the file exists and these options are present in the my.cnf or as options on the command line to mysqld, they will be silently ignored and the information in master.info used instead.

The master.info file format in MySQL 5.1 includes values corresponding to the SSL options. In addition, the file format includes as its first line the number of lines in the file. (See Section 6.4.5, “Replication Relay and Status Files”.) If you upgrade an older server (before MySQL 4.1.1) to a newer version, the new server upgrades the master.info file to the new format automatically when it starts. However, if you downgrade a newer server to an older version, you should remove the first line manually before starting the older server for the first time.

If no master.info file exists when the slave server starts, it uses the values for those options that are specified in option files or on the command line. This occurs when you start the server as a replication slave for the very first time, or when you have run RESET SLAVE and then have shut down and restarted the slave.

If the master.info file exists when the slave server starts, the server uses its contents and ignores any options that correspond to the values listed in the file. Thus, if you start the slave server with different values of the startup options that correspond to values in the master.info file, the different values have no effect, because the server continues to use the master.info file. To use different values, you must either restart after removing the master.info file or (preferably) use the CHANGE MASTER TO statement to reset the values while the slave is running.

Suppose that you specify this option in your my.cnf file: 

[mysqld]
master-host=some_host

The first time you start the server as a replication slave, it reads and uses that option from the my.cnf file. The server then records the value in the master.info file. The next time you start the server, it reads the master host value from the master.info file only and ignores the value in the option file. If you modify the my.cnf file to specify a different master host of some_other_host, the change still has no effect. You should use CHANGE MASTER TO instead.

Because the server gives an existing master.info file precedence over the startup options just described, you might prefer not to use startup options for these values at all, and instead specify them by using the CHANGE MASTER TO statement. See Section 13.6.2.1, “CHANGE MASTER TO Syntax”.

This example shows a more extensive use of startup options to configure a slave server: 

[mysqld]
server-id=2
master-host=db-master.mycompany.com
master-port=3306
master-user=pertinax
master-password=freitag
master-connect-retry=60
report-host=db-slave.mycompany.com

The following list describes the options and variables used for controlling replication. Many of these options can be reset 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.

  • --log-slave-updates

    Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log 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. --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-warnings[=level]

    This option causes a server to print 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/connection failure, and informs you as to how each slave thread started. This option is enabled by default; to disable it, use --skip-log-warnings. Aborted connections are not logged to the error log unless the value is greater than 1.

  • --master-connect-retry=seconds

    The number of seconds that the slave thread sleeps before trying to reconnect to the master in case the master goes down or the connection is lost. The value in the master.info file takes precedence if it can be read. If not set, the default is 60. Connection retries are not invoked until the slave times out reading data from the master according to the value of --slave-net-timeout. The number of reconnection attempts is limited by the --master-retry-count option.

  • --master-host=host_name

    The hostname or IP number of the master replication server. The value in master.info takes precedence if it can be read. If no master host is specified, the slave thread does not start.

  • --master-info-file=file_name

    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.

  • --master-password=password

    The password of the account that the slave thread uses for authentication when it connects to the master. The value in the master.info file takes precedence if it can be read. If not set, an empty password is assumed.

  • --master-port=port_number

    The TCP/IP port number that the master is listening on. The value in the master.info file takes precedence if it can be read. If not set, the compiled-in setting is assumed (normally 3306).

  • --master-retry-count=count

    The number of times that the slave tries to connect to the master before giving up. Reconnects are attempted at intervals set by --master-connect-retry and reconnects are triggered when data reads by the slave time out according to the --slave-net-timeout option. The default value is 86400.

  • --master-ssl, --master-ssl-ca=file_name, --master-ssl-capath=directory_name, --master-ssl-cert=file_name, --master-ssl-cipher=cipher_list, --master-ssl-key=file_name

    These options are used for setting up a secure replication connection to the master server using SSL. Their meanings are the same as the corresponding --ssl, --ssl-ca, --ssl-capath, --ssl-cert, --ssl-cipher, --ssl-key options that are described in Section 5.8.7.3, “SSL Command Options”. The values in the master.info file take precedence if they can be read.

  • --master-user=user_name

    The username of the account that the slave thread uses for authentication when it connects to the master. This account must have the REPLICATION SLAVE privilege. The value in the master.info file takes precedence if it can be read. If the master username is not set, the name test is assumed.

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

    The size at which the server rotates relay log files automatically. For more information, see Section 6.4.5, “Replication Relay and Status Files”. The default size is 1GB.

  • --read-only

    Cause the slave to allow no updates except from slave threads or from users having the SUPER privilege. This enables you to ensure that a slave server accepts no updates from clients. This option does not apply to TEMPORARY tables.

  • --relay-log=file_name

    The name for the relay log. The default name is host_name-relay-bin.nnnnnn, where host_name is the name of the slave server host and nnnnnn indicates that relay logs are created in numbered sequence. You can specify the option to create hostname-independent relay log names, or if your relay logs tend to be big (and you don't want to decrease max_relay_log_size) and you need to put them in some area different from the data directory, or if you want to increase speed by balancing load between disks.

  • --relay-log-index=file_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.

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

    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.

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

    Disable or enable automatic purging of relay logs as soon as they are not needed any more. The default value is 1 (enabled). This is a global variable that can be changed dynamically with SET GLOBAL relay_log_purge = N.

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

    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 temporarily ignore --relay-log-space-limit.

  • --replicate-do-db=db_name

    Tell the slave 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. Note that this does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database.

    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: 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 “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.

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

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

    Tell the slave thread to restrict replication to the specified table. To specify more than one table, use this option multiple times, once for each table. This works for cross-database updates, in contrast to --replicate-do-db. See Section 6.4.6, “How Servers Evaluate Replication Rules”.

  • --replicate-ignore-db=db_name

    Tells the slave to not replicate any statement where the default database (that is, the one selected by USE) is db_name. To specify more than one database to ignore, use this option multiple times, once for each 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 6.4.6, “How Servers Evaluate Replication Rules”.

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

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

    Note

    In the preceding example the statement is replicated because --replicate-ignore-db only applies to the default database (set through the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered.

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

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

    Tells the slave thread to not 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 6.4.6, “How Servers Evaluate Replication Rules”.

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

    Tells the slave to translate 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. This does not work for cross-database updates. The database name translation is done before the --replicate-* rules are tested.

    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"

  • --replicate-same-server-id

    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. Note that by default the slave I/O thread does not even write binary log events to the relay log if they have the slave's server id (this optimization helps save disk usage). So 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

    Tells 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 6.4.6, “How Servers Evaluate Replication Rules”.

    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're using 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

    Tells the slave thread not to replicate a statement where 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 6.4.6, “How Servers Evaluate Replication 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=slave_name

    The hostname or IP number 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 that it is not sufficient for the master to simply read the IP number 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-port=slave_port_num

    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 non-default 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.

  • --report-password=password

    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.

  • --report-user=user_name

    The account username 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.

  • --show-slave-auth-info

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

  • --skip-slave-start

    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}

    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

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

    The directory specified by this option should be located in a disk-based filesystem (not a memory-based filesystem) 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-net-timeout=seconds

    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 and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds (one hour).

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

    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. Errors, Error Codes, and Common Problems, lists server error codes.

    You can also (but should not) use the very non-recommended 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