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

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 (2³²)-1). How you organize and select the numbers is entirely up to you.

To configure both these options you will need to shut down 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

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 shut down 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.

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:

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 19.1.1.5, “Creating a Data Snapshot Using mysqldump”, or Section 19.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.

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:

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.

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 caching 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 depends on its crash recovery abilities.

For example, if you are using InnoDB tables, you should use the InnoDB Hot Backup tool to obtain a consistent snapshot. This tool records the log name and offset corresponding to the snapshot to be later used on the slave. Hot Backup is a 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/hot-backup for detailed information.

Otherwise, you can obtain a reliable binary snapshot of InnoDB tables only after shutting down 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 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:

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

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:

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.

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:

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

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:

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.

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.

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:

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;

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

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:

Disadvantages of statement-based replication:

Advantages of row-based replication:

Disadvantages of row-based replication:

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 command 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
1 row in set (0.01 sec)

The key fields from the status report to examine are:

On the master, you can check the status of slaves by examining the list of running processes. Slaves execute the Binlog Dump command:

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 core of the replication process, very little information is available in this report.

If you have used the --report-host option, then the SHOW SLAVE HOSTS statement will show basic information about connected slaves:

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)

The output includes the ID of the slave server, the value of the --report-host option, the connecting port, master ID and the priority of the slave for receiving binary log updates.

You can stop and start the replication of statements on the slave using the STOP SLAVE and START SLAVE commands.

To stop execution of the binary log from the master, use STOP SLAVE:

mysql> STOP SLAVE;

When execution is stopped, the slave does not read the binary log from the master (the IO_THREAD) and stops processing events from the relay log that have not yet been executed (the SQL_THREAD). You can pause either the IO or SQL threads individually by specifying the thread type. For example:

mysql> STOP SLAVE IO_THREAD;

Stopping the SQL thread can be useful if you want to perform a backup or other task on a slave that only processes events from the master. The IO thread will continue to be read from the master, but not executed, which will make it easier for the slave to catch up when you start slave operations again.

Stopping the IO thread will allow the statements in the relay log to be executed up until the point where the relay log has ceased to receive new events. Using this option can be useful when you want to pause execution to allow the slave to catch up with events from the master, when you want to perform administration on the slave but also ensure you have the latest updates to a specific point. This method can also be used to pause execution on the slave while you conduct administration on the master while ensuring that there is not a massive backlog of events to be executed when replication is started again.

To start execution again, use the START SLAVE statement:

mysql> START SLAVE;

If necessary, you can start either the IO_THREAD or SQL_THREAD threads individually.

Using mysqldump to create a copy of the database enables you to capture all of the data in the database in a format that allows the information to be imported into another instance of MySQL. 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 then mysqldump may be impractical.

When using mysqldump you should stop the slave before starting the dump process to ensure that the dump contains a consistent set of data:

In the preceding example you may want to add login credentials (username, 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 in this way is not affecting the slave's ability to keep up with events from the master. See Section 19.1.4.1, “Checking Replication Status”. If the slave is unable to keep up you may want to add another server and distribute the backup process. For an example of how to configure this scenario, see Section 19.2.4, “Replicating Different Databases to Different Slaves”.

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 then background tasks, particularly with storage engines with background processes such as InnoDB, may still be updating the database files. 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 facility.

To shut down the server and back up the files:

Normally you should back up the entire data folder 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 when you back up the slave's data, you should back up the slave status files, master.info and relay.info, along with 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. Of course, this requires that the binary logs still exist on the master server.

If your slave is subject to 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 directory location is specified using the --slave-load-tmpdir option. If this option is not specified, the directory location is the value of the tmpdir system variable.

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:

The following instructions describe how to do this for a master server and for a slave server.

These instructions require MySQL 5.1.15 or higher. For earlier versions, setting read_only did not block while table locks or outstanding transactions were pending, so that some data changes could still occur during the backup.

For both scenarios discussed here, suppose that you have the following replication setup:

Scenario 1: Backup with a Read-Only Master

Put the master M1 in a read-only state by executing these statements on it:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

While M1 is in a read-only state, the following properties are true:

While M1 is read only, perform the backup. For example, you can use mysqldump.

After the backup on M1 has been done, restore M1 to its normal operational state by executing these statements:

SET GLOBAL read_only = OFF;
UNLOCK TABLES;

Although performing the backup on M1 is safe (as far as the backup is concerned), it is not optimal because clients of M1 are blocked from executing updates.

This strategy also applies to backing up a single server in a non-replication setting.

Scenario 2: Backup with a Read-Only Slave

Put the slave S1 in a read-only state by executing these statements on it:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

While S1 is in a read-only state, the following properties are true:

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. (For example, clients can still perform updates on the master server.)

While S1 is read only, perform the backup.

After the backup on S1 has been done, restore S1 to its normal operational state by executing these statements:

SET GLOBAL read_only = OFF;
UNLOCK TABLES;

After the slave is restored to normal operation, it again synchronizes to the master by catching up with any outstanding updates in the binary log from the master.

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.

Replication of AUTO_INCREMENT, LAST_INSERT_ID(), and TIMESTAMP values is done correctly, subject to the following exceptions.

A stored procedure that uses LAST_INSERT_ID() does not replicate properly using statement-based binary logging. This limitation is lifted in MySQL 5.1.12.

Prior to MySQL 5.1.12, when a stored routine or trigger caused an INSERT into an AUTO_INCREMENT column, the generated AUTO_INCREMENT value was not written into the binary log, so a different value could in some cases be inserted on the slave.

An insert into an AUTO_INCREMENT column caused by a stored routine or trigger running on a master that uses MySQL 5.0.60 or earlier does not replicate correctly to a slave running MySQL 5.1.12 through 5.1.23 (inclusive). (Bug#33029)

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 the table t1, 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;

This assumes that the table t1 has columns col1 and col2.

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.1.7.1, “Problems with ALTER TABLE”.

The following applies to replication between MySQL servers that use different character sets:

The following rules and decisions are applied when a CREATE TABLE ... SELECT statement is replicated:

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 filesystem or if it exists but is not accessible to the slave server. MySQL supports an sql_mode option called NO_DIR_IN_CREATE. If the slave server is run with this SQL mode enabled, it ignores 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.

Replication of invoked features such as scheduled events, user-defined functions (UDFs), stored routines (including both stored procedures and stored functions), and triggers was re-implemented in MySQL 5.1.18 to provide the following characteristics:

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), use SHOW EVENTS, like this:

SHOW EVENTS 
    WHERE STATUS = 'SLAVESIDE_DISABLED';

Alternatively, you might wish to query the INFORMATION_SCHEMA.EVENTS table as shown here:

SELECT EVENT_SCHEMA, EVENT_NAME, ORIGINATOR
    FROM INFORMATION_SCHEMA.EVENTS
    WHERE STATUS = 'SLAVESIDE_DISABLED';

When promoting a replication slave having such events to a replication master, use the following query to enable the events:

UPDATE mysql.event 
    SET STATUS = 'ENABLED' 
    WHERE STATUS = 'SLAVESIDE_DISABLED';

If more than one master was involved in creating events on this slave, and you wish to enable events that were created only on a given master having the server ID master_id, use the following query instead:

UPDATE mysql.event 
    SET STATUS = 'ENABLED' 
    WHERE ORIGINATOR = master_id 
    AND STATUS = 'SLAVESIDE_DISABLED';

UPDATE mysql.event
    SET name = CONCAT('replicated_', name)
    WHERE status = 'SLAVESIDE_DISABLED';

UPDATE mysql.event
    SET name = REPLACE(name, 'replicated_', ''), 
        status = 'SLAVESIDE_DISABLED'
    WHERE INSTR(name, 'replicated_') = 1;

Floating-point values are approximate, so comparisons involving them are inexact. This is true for operations that use floating-point values explicitly, or 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 11.2.2, “Type Conversion in Expression Evaluation”, and Section B.1.5.8, “Problems with Floating-Point Comparisons”.

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 12.5.5.2, “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 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.

Certain functions do not replicate well under some conditions:

As a workaround for the preceding limitations when statement-based replication is in effect, you can use the strategy of saving the problematic function result in a user variable and referring to the variable in a later statement. For example, the following single-row INSERT is problematic due to the reference to the UUID() function:

INSERT INTO t VALUES(UUID());

To work around the problem, do this instead:

SET @my_uuid = UUID();
INSERT INTO t VALUES(@my_uuid);

That sequence of statements replicates because the value of @my_uuid is stored in the binary log as a user-variable event prior to the INSERT statement and is available for use in the INSERT.

The same idea applies to multiple-row inserts, but is more cumbersome to use. For a two-row insert, you can do this:

SET @my_uuid1 = UUID(); @my_uuid2 = UUID();
INSERT INTO t VALUES(@my_uuid1),(@my_uuid2);

However, if the number of rows is large or unknown, the workaround is difficult or impracticable. For example, you cannot convert the following statement to one in which a given individual user variable is associated with each row:

INSERT INTO t2 SELECT UUID(), * FROM t1;

Within a stored function, RAND() replicates correctly as long as it is invoked only once within the function. (You can consider the function execution timestamp and random number seed as implicit inputs that are identical on the master and slave.)

The FOUND_ROWS() and ROW_COUNT() functions are not replicated reliably using statement-based replication. A workaround is to store the result of the function call in a user variable, and then use that in the INSERT statement. For example, if you wish to store the result in a table named mytable, you might normally do so like this:

          
SELECT SQL_CALC_FOUND_ROWS FROM mytable LIMIT 1;
INSERT INTO mytable VALUES( FOUND_ROWS() );

However, if you are replicating mytable, then you should use SELECT INTO, and then store the variable in the table, like this:

          
SELECT SQL_CALC_FOUND_ROWS INTO @found_rows FROM mytable LIMIT 1;
INSERT INTO mytable VALUES(@found_rows);

In this way, the user variable is replicated as part of the context, and applied on the slave correctly.

Beginning with MySQL 5.1.23, these functions are automatically replicated using row-based replication when using MIXED mode, and generate a warning in STATEMENT mode. (Bug#12092, Bug#30244)

Statement-based replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT statements is unsafe since the order of the rows affected is not defined. Beginning with MySQL 5.1.24, when such a statement is encountered:

The LOAD DATA statement is not replicated correctly to a slave running MySQL 5.1 or later from a master running MySQL 4.0 or earlier.

When using statement-based replication, the LOAD DATA INFILE statement's CONCURRENT option is not replicated; that is, LOAD DATA CONCURRENT INFILE is replicated as LOAD DATA INFILE, and LOAD DATA CONCURRENT LOCAL INFILE is replicated as LOAD DATA LOCAL INFILE. The CONCURRENT option is replicated when using row-based replication. (Bug#34628)

A crash on the master side can result in the master's binary log having a final position less than the most recent position read by the slave, due to the master's binary log file not being flushed. This can cause the slave not to be able to replicate when the master comes back up. Setting sync_binlog=1 in the master my.cnf file helps to minimize this problem because it causes the master to flush its binary log more frequently.

It is safe to shut down a master server and restart it later. When a slave loses its connection to the master, the slave tries to reconnect immediately and retries periodically if that fails. The default is to retry every 60 seconds. This may be changed with the CHANGE MASTER TO statement or --master-connect-retry option. A slave also is able to deal with network connectivity outages. However, the slave notices the network outage only after receiving no data from the master for slave_net_timeout seconds. If your outages are short, you may want to decrease slave_net_timeout. See Section 5.1.3, “System Variables”.

When a server shuts down and restarts, its MEMORY tables become empty. The master replicates this effect to slaves as follows: The first time that the master uses each MEMORY table after startup, it logs an event that notifies the slaves that the table needs to be emptied by writing a DELETE statement for that table to the binary log. See Section 13.7, “The MEMORY (HEAP) Storage Engine”, for more information about MEMORY tables.

MySQL 5.1.14 and later.  Data modification statements made to tables in the mysql database are replicated according to the value of binlog_format; if this value is MIXED, then these statement are replicated using the row-based format. However, statements that would normally update this information indirectly — such GRANT, REVOKE, and statements manipulating triggers, stored routines, and views — are replicated to slaves using statement-based replication.

MySQL 5.1.13 and earlier.  User privileges are replicated only if the mysql database is replicated. That is, the GRANT, REVOKE, SET PASSWORD, CREATE USER, and DROP USER statements take effect on the slave only if the replication setup includes the mysql database.

You can encounter problems when you are attempting to replicate from an older master to a newer slave and you make use of identifiers on the master that are reserved words in the newer MySQL version running on the slave. An example of this is using a table column named current_user on a 4.0 master that is replicating to a 4.1 or higher slave, because CURRENT_USER is a reserved word beginning in MySQL 4.1. Replication can fail in such cases with Error 1064 You have an error in your SQL syntax..., even if a database or table named using the reserved word or a table having a column named using the reserved word is excluded from replication. This is due to the fact that each SQL statement must be parsed by the slave prior to execution, so that the slave knows which database object or objects would be effected by the statement; only after the statement is parsed can the slave apply any filtering rules defined by --replicate-do-db, --replicate-do-table, --replicate-ignore-db, and --replicate-ignore-ignore.

To work around the problem of database, table, or column names on the master which would be regarded as reserved words by the slave, do one of the following:

For listings of reserved words by MySQL version, see Reserved Words,.in the MySQL Server Version Reference.

If a statement on a slave produces an error, the slave SQL thread terminates, and the slave writes a message to its error log. You should then connect to the slave manually and determine the cause of the problem. (SHOW SLAVE STATUS is useful for this.) Then fix the problem (for example, you might need to create a non-existent table) and run START SLAVE.

MySQL Enterprise For instant notification when a slave thread terminates subscribe to the MySQL Enterprise Monitor. For more information see http://www.mysql.com/products/enterprise/advisors.html.

Shutting down the slave (cleanly) is also safe because it keeps track of where it left off. Unclean shutdowns might produce problems, especially if the disk cache was not flushed to disk before the system went down. Your system fault tolerance is greatly increased if you have a good uninterruptible power supply. Unclean shutdowns of the master may cause inconsistencies between the content of tables and the binary log in master; this can be avoided by using InnoDB tables and the --innodb-safe-binlog option on the master. See Section 5.2.4, “The Binary Log”.

This item does not apply when row-based replication is in use because in that case temporary tables are not replicated (see Section 19.1.2, “Replication Formats”).

Temporary tables are replicated except in the case where you shut down the slave server (not just the slave threads) and you have replicated temporary tables that are used in updates that have not yet been executed on the slave. If you shut down the slave server, the temporary tables needed by those updates are no longer available when the slave is restarted. To avoid this problem, do not shut down the slave while it has temporary tables open. Instead, use the following procedure:

The global system variable slave_transaction_retries affects replication as follows: If the replication slave SQL thread fails to execute a transaction because of an InnoDB deadlock or because it exceeded the InnoDB innodb_lock_wait_timeout value, or the NDBCLUSTER TransactionDeadlockDetectionTimeout or TransactionInactiveTimeout value, the transaction is automatically retried slave_transaction_retries times before stopping with an error. The default value is 10. The total retry count can be seen in the output of SHOW STATUS; see Section 5.1.6, “Status Variables”.

If the master uses MySQL 4.1, the same system time zone should be set for both master and slave. Otherwise some statements will not be replicated properly, such as statements that use the NOW() or FROM_UNIXTIME() functions. You can set the time zone in which MySQL server runs by using the --timezone=timezone_name option of the mysqld_safe script or by setting the TZ environment variable. Both master and slave should also have the same default connection time zone setting; that is, the --default-time-zone parameter should have the same value for both master and slave. Note that this is not necessary when the master is MySQL 5.0 or later.

CONVERT_TZ(...,...,@@session.time_zone) is properly replicated only if both master and slave are running MySQL 5.0.4 or newer.

It is possible to replicate transactional tables on the master using non-transactional tables on the slave. For example, you can replicate an InnoDB master table as a MyISAM slave table. However, if you do this, there are problems if the slave is stopped in the middle of a BEGIN/COMMIT block because the slave restarts at the beginning of the BEGIN block.

In situations where transactions mix updates to transactional and non-transactional tables, the order of statements in the binary log is correct, and all needed statements are written to the binary log even in case of a ROLLBACK. However, when a second connection updates the non-transactional table before the first connection's transaction is complete, statements can be logged out of order, because the second connection's update is written immediately after it is performed, regardless of the state of the transaction being performed by the first connection.

Due to the non-transactional nature of MyISAM tables, it is possible to have a statement that only partially updates a table and returns an error code. This can happen, for example, on a multiple-row insert that has one row violating a key constraint, or if a long update statement is killed after updating some of the rows. If that happens on the master, the slave thread exits and waits for the database administrator to decide what to do about it unless the error code is legitimate and execution of the statement results in the same error code on the slave. If this error code validation behavior is not desirable, some or all errors can be masked out (ignored) with the --slave-skip-errors option.

When the storage engine type of the slave is non-transactional, transactions on the master that mix updates of transactional and non-transactional tables should be avoided because they can cause inconsistency of the data between the master's transactional table and the slave's non-transactional table. That is, such transactions can lead to master storage engine-specific behavior with the possible effect of replication going out of synchrony. MySQL does not issue a warning about this currently, so extra care should be taken when replicating transactional tables from the master to non-transactional ones on the slaves.

Starting with MySQL 5.1.21, 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 — subject to certain conditions — corresponding table columns on the master and the slave can use different data types.

In all cases where the source and target tables do not have identical definitions, the following must be true in order for replication to work:

Additional conditions are discussed (and examples provided) in the following two sections.

The FOREIGN_KEY_CHECKS, UNIQUE_CHECKS, and SQL_AUTO_IS_NULL variables are all replicated.

SQL_MODE is also replicated except for the NO_DIR_IN_CREATE mode. However, when mysqlbinlog parses a SET @@SQL_MODE = value statement, the full value, including NO_DIR_IN_CREATE, is passed to the receiving server.

The storage_engine system variable is not replicated, which is a good thing for replication between different storage engines.

Session variables are not replicated properly when used in statements that update tables. For example, SET MAX_JOIN_SIZE=1000 followed by INSERT INTO mytable VALUES(@@MAX_JOIN_SIZE) will not insert the same data on the master and the slave. This does not apply to the common sequence of SET TIME_ZONE=... followed by INSERT INTO mytable VALUES(CONVERT_TZ(...,...,@@time_zone)).

Replication of session variables is not a problem when row-based replication is being used. See Section 19.1.2, “Replication Formats”.

Views are always replicated to slaves. Views are filtered by their own name, not by the tables they refer to. This means that a view can be replicated to the slave even if the view contains a table that would normally be filtered out by replication-ignore-table rules. Care should therefore be taken to ensure that views do not replicate table data that would normally be filtered for security reasons.

By default, relay logs filenames have the form host_name-relay-bin.nnnnnn, 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 filename is host_name-relay-bin.index. By default, the slave server creates relay log files in its data directory.

The default filenames for relay logs and relay log index files can be overridden with, respectively, the --relay-log and --relay-log-index server options (see Section 19.1.3, “Replication Options and Variables”). For this reason, changing a replication slave's hostname 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 which we intend to fix in a future MySQL release (see Bug#2122). If you anticipate that a slave's hostname may change in the future (for example, if networking is set up on the slave such that its hostname can be modified via DHCP), then you can use these options to prevent this problem from occurring. However, if you encounter this issue, 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, then restart the slave. On a Unix system, this can be done as shown here, where new_host_name is the new hostname and old_host_name is the old one:

shell< cat new_host_name-relay-bin.index >> old_host_name-relay-bin.index

shell< mv old_host_name-relay-bin.index new_host_name-relay-bin.index

Relay logs have the same format as binary logs and can be read using mysqlbinlog. 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.

A slave server creates a new relay log file under the following conditions:

A slave replication server creates two small files in the data directory. These status files are named master.info and relay-log.info by default. Their names can be changed by using the --master-info-file and --relay-log-info-file options. See Section 19.1.3, “Replication Options and Variables”.

The two status files contain information like that shown in the output of the SHOW SLAVE STATUS statement, which is discussed in Section 12.6.2, “SQL Statements for Controlling Slave Servers”. Because the status files are stored on disk, they survive a slave server's shutdown. The next time the slave starts up, it reads the two files to determine how far it has proceeded in reading binary logs from the master and in processing its own relay logs.

The I/O thread updates the master.info file. The following table shows the correspondence between the lines in the file and the columns displayed by SHOW SLAVE STATUS.

Master_SSL_Verify_Server_Cert is present in master.info as of MySQL 5.1.18. It is used as described for the --ssl-verify-server-cert option in Section 5.5.7.3, “SSL Command Options”.

The SQL thread updates the relay-log.info file. The following table shows the correspondence between the lines in the file and the columns displayed by SHOW SLAVE STATUS.

The contents of the relay-log.info file and the states shown by the SHOW SLAVE STATES command may 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 (i.e. mysqld is not running). For a running system, SHOW SLAVE STATUS should be used.