Replication capabilities allowing the databases on one MySQL server to be duplicated on another were introduced in MySQL 3.23.15. This chapter describes the various replication features provided by MySQL. It introduces replication concepts, shows how to set up replication servers, and serves as a reference to the available replication options. It also provides a list of frequently asked questions (with answers), and troubleshooting advice for solving problems.
For a description of the syntax of replication-related SQL statements, see section 13.6 Replication Statements.
We suggest that you visit our Web site at http://www.mysql.com often and read updates to this chapter. Replication is constantly being improved, and we update the manual frequently with the most current information.
MySQL 3.23.15 and up features support for one-way replication. One server acts as the master, while one or more other servers act as slaves. The master server writes updates to its binary log files, and maintains an index of the files to keep track of log rotation. These logs serve as a record of updates to be sent to slave servers. When a slave server connects to the master server, it informs the master of its last position within the logs since the last successfully propagated update. The slave catches up any updates that have occurred since then, and then blocks and waits for the master to notify it of new updates.
A slave server can also serve as a master if you want to set up chained replication servers.
Note that when you are using replication, all updates to the tables that are replicated should be performed on the master server. Otherwise, you must always be careful to avoid conflicts between updates that users make to tables on the master and updates that they make to tables on the slave.
One-way replication has benefits for robustness, speed, and system administration:
SELECTqueries may be sent to the slave to reduce the query processing load of the master. Statements that modify data should still be sent to the master so that the master and slave do not get out of sync. This load-balancing strategy is effective if non-updating queries dominate, but that is the normal case.
MySQL replication is based on the master server keeping track of all changes to your databases (updates, deletes, and so on) in the binary logs. Therefore, to use replication, you must enable binary logging on the master server. See section 5.9.4 The Binary Log.
Each slave server receives from the master the saved updates that the master has recorded in its binary log, so that the slave can execute the same updates on its copy of the data.
It is very important to realize that the binary log is simply a record starting from the fixed point in time at which you enable binary logging. Any slaves that you set up will need copies of the databases on your master as they existed at the moment you enabled binary logging on the master. If you start your slaves with databases that are not the same as what was on the master when the binary log was started, your slaves may fail.
One way to copy the master's data to the slave is to use the
DATA FROM MASTER statement. Be aware that
LOAD DATA FROM MASTER
is available only as of MySQL 4.0.0 and currently works only if all the
tables on the master are
MyISAM type. Also, this statement acquires a
global read lock, so no updates on the master are possible while the tables
are being transferred to the slave. When we implement lock-free hot table
backup (in MySQL 5.0), this global read lock will no longer be necessary.
Due to these limitations, we recommend that at this point you use
LOAD DATA FROM MASTER only if the dataset on the master is relatively
small, or if a prolonged read lock on the master is acceptable. While the
actual speed of
LOAD DATA FROM MASTER may vary from system to system,
a good rule of thumb for how long it will take is 1 second per 1MB of data.
That is only a rough estimate, but you should get close to it if both
master and slave are equivalent to 700MHz Pentium performance and are
connected through a 100MBit/s network.
After the slave has been set up with a copy of the master's data, it
will simply connect to the master and wait for updates to process. If
the master goes away or the slave loses connectivity with your master,
it will keep trying to connect periodically until it is able to reconnect
and resume listening for updates. The retry interval is controlled by the
--master-connect-retry option. The default is 60 seconds.
Each slave keeps track of where it left off. The master server has no knowledge of how many slaves there are or which ones are up to date at any given time.
MySQL replication capabilities are implemented using three threads (one
on the master server and two on the slave). When
START SLAVE is
issued, the slave creates an I/O thread. The I/O thread connects to the
master and asks it to send the statements recorded in its binary logs. The
master creates a thread to send the binary log contents to the slave.
This thread can be identified as the
Binlog Dump thread in the
SHOW PROCESSLIST on the master. The slave I/O thread
reads what the master
Binlog Dump thread sends and simply copies
it to some local files in the slave's data directory called relay logs.
The third thread is the SQL thread, which the slave creates to read the
relay logs and execute the updates they contain.
In the preceding description, there are three threads per slave. For a master that has multiple slaves, it creates one thread for each currently connected slave, and each slave has its own I/O and SQL threads.
For versions of MySQL before 4.0.2, replication involves only two threads (one on the master and one on the slave). The slave I/O and SQL threads are combined as a single thread, and no relay log files are used.
The advantage of using two slave threads is that statement reading and execution are separated into two independent tasks. The task of reading statements is not slowed down if statement execution is slow. For example, if the slave server has not been running for a while, its I/O thread can quickly fetch all the binary log contents from the master when the slave starts, even if the SQL thread lags far behind and may take hours to catch up. If the slave stops before the SQL thread has executed all the fetched statements, the I/O thread has at least fetched everything so that a safe copy of the statements is locally stored in the slave's relay logs for execution when next the slave starts. This allows the binary logs to be purged on the master, because it no longer need wait for the slave to fetch their contents.
SHOW PROCESSLIST statement provides information that tells you
what is happening on the master and on the slave regarding replication.
The following example illustrates how the three threads show up in
SHOW PROCESSLIST. The output format is that used by
PROCESSLIST as of MySQL version 4.0.15, when the content of the
State column was changed to be more meaningful compared to
On the master server, the output from
SHOW PROCESSLIST looks like this:
mysql> SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 2 User: root Host: localhost:32931 db: NULL Command: Binlog Dump Time: 94 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL
Here, thread 2 is a replication thread for a connected slave. The information indicates that all outstanding updates have been sent to the slave and that the master is waiting for more updates to occur.
On the slave server, the output from
SHOW PROCESSLIST looks like this:
mysql> SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 10 User: system user Host: db: NULL Command: Connect Time: 11 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 11 User: system user Host: db: NULL Command: Connect Time: 11 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL
This information indicates that thread 10 is the I/O thread that is communicating with the master server, and thread 11 is the SQL thread that is processing the updates stored in the relay logs. Currently, both threads are idle, waiting for further updates.
Note that the value in the
Time column can tell how late the slave
is compared to the master.
See section 6.9 Replication FAQ.
The following list shows the most common states you will see in the
State column for the master's
Binlog Dump thread. If you
don't see any
Binlog Dump threads on a master server, replication
is not running. That is, no slaves currently are connected.
Sending binlog event to slave
Finished reading one binlog; switching to next binlog
Has sent all binlog to slave; waiting for binlog to be updated
Waiting to finalize termination
The following list shows the most common states you will see in the
State column for a slave server I/O thread. Beginning with MySQL
4.1.1, this state also appears in the
Slave_IO_State column displayed
SHOW SLAVE STATUS statement. This means that you can get a
good view of what is happening by using only
SHOW SLAVE STATUS.
Connecting to master
Checking master version
Registering slave on master
Requesting binlog dump
Waiting to reconnect after a failed binlog dump request
Reconnecting after a failed binlog dump request
Waiting for master to send event
slave_read_timeoutseconds, a timeout will occur. At that point, the thread will consider the connection to be broken and make an attempt to reconnect.
Queueing master event to the relay log
Waiting to reconnect after a failed master event read
master-connect-retryseconds before attempting to reconnect.
Reconnecting after a failed master event read
Waiting for master to send event.
Waiting for the slave SQL thread to free enough relay log space
relay_log_space_limitvalue, and the relay logs have grown so much that their combined size exceeds this value. The I/O thread is waiting until the SQL thread frees enough space by processing relay log contents so that it can delete some relay log files.
Waiting for slave mutex on exit
The following list shows the most common states you will see in the
State column for a slave server SQL thread:
Reading event from the relay log
Has read all relay log; waiting for the slave I/O thread to update it
Waiting for slave mutex on exit
State column for the I/O thread may also show the text of
a statement. This indicates that the thread has read an event from the
relay log, extracted the statement from it, and is executing it.
By default, relay logs are named using filenames of 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,
001 in MySQL 4.0 or older).
The slave keeps track of relay logs currently in use in an index file.
The default relay log index filename is
By default, these files are created in the slave's data directory.
The default filenames may be overridden with the
--relay-log-index server options.
See section 6.8 Replication Startup Options.
Relay logs have the same format as binary logs, so you can use
mysqlbinlog to read them. A relay log is automatically deleted by
the SQL thread as soon as it has executed all its events and no longer needs
it). There is no explicit mechanism for deleting relay logs, because the SQL
thread takes care of doing so. However, from MySQL 4.0.14,
rotates relay logs, which will influence when the SQL thread deletes them.
A new relay log is created under the following conditions:
mysqladmin flush-logs. (This creates a new relay log only as of MySQL 4.0.14.)
max_relay_log_size= 0 or MySQL is older than 4.0.14
A slave replication server creates two additional small files in the
data directory. These are status files and are named `master.info'
and `relay-log.info' by default. They contain information like
that shown in the output of the
SHOW SLAVE STATUS statement
(see section 13.6.2 SQL Statements for Controlling Slave Servers for a description of this statement).
As disk files, they survive a slave server's shutdown. The next time the
slave starts up, it reads these files to determine how far it has proceeded
in reading binary logs from the master and in processing its own relay logs.
The `master.info' file is updated by the I/O thread.
Before MySQL 4.1,
the correspondence between the lines in the file and the
columns displayed by
SHOW SLAVE STATUS is as follows:
|5|| Password (not shown by |
As of MySQL 4.1, the file includes a line count and information about SSL options:
|1||Number of lines in the file|
|6|| Password (not shown by |
The `relay-log.info' file is updated by the SQL thread.
The correspondence between the lines in the file and the
columns displayed by
SHOW SLAVE STATUS is as follows:
When you back up your slave's data, you should back up these two small files
as well, along with the relay log files. They 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
CHANGE MASTER TO with the
MASTER_LOG_POS options to tell the slave to re-read the binary
logs from that point. This requires that the binary logs still exist on
the master server.
If your slave is 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
option. Its default value, if not specified, is the value of the
Here is a quick description of how to set up complete replication of your current MySQL server. It assumes that you want to replicate all your databases and have not configured replication before. You will need to shut down your master server briefly to complete the steps outlined here.
The procedure is written in terms of setting up a single slave, but you can use it to set up multiple slaves.
While this method is the most straightforward way to set up a slave, it is not the only one. For example, if you already have a snapshot of the master's data, and the master already has its server ID set and binary logging enabled, you can set up a slave without shutting down the master or even blocking updates to it. For more details, please see section 6.9 Replication FAQ.
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 replication startup options described in section 6.8 Replication Startup Options.
Note that this procedure and some of the replication SQL statements
in later sections refer to the
SUPER privilege. Prior to MySQL
4.0.2, use the
PROCESS privilege instead.
REPLICATION SLAVEprivilege. If the account is used only for replication (which is recommended), you don't need to grant any additional privileges. Suppose that your domain is
mydomain.comand you want to create an account with a username of
replsuch that slave servers can use the account to access the master server from any host in your domain using a password of
slavepass. To create the account, this use
mysql> GRANT REPLICATION SLAVE ON *.* -> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';For MySQL versions older than 4.0.2, the
REPLICATION SLAVEprivilege does not exist. Grant the
mysql> GRANT FILE ON *.* -> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';If you plan to use the
LOAD TABLE FROM MASTERor
LOAD DATA FROM MASTERstatements from the slave host, you will need to grant this account additional privileges:
SELECTprivilege for all tables that you want to load. Any master tables from which the account cannot
SELECTwill be ignored by
LOAD DATA FROM MASTER.
MyISAMtables, flush all the tables and block write statements by executing a
FLUSH TABLES WITH READ LOCKstatement.
mysql> FLUSH TABLES WITH READ LOCK;Leave the client running from which you issue the
FLUSH TABLESstatement so that the read lock remains in effect. (If you exit the client, the lock is released.) Then take a snapshot of the data on your master server. The easiest way to create a snapshot is to use an archiving program to make a binary backup of the databases in your master's data directory. For example, use
taron Unix, or
WinZip, or any similar software on Windows. To use
tarto create an archive that includes all databases, change location into the master server's data directory, then execute this command:
shell> tar -cvf /tmp/mysql-snapshot.tar .If you want the archive to include only a database called
this_db, use this command instead:
shell> tar -cvf /tmp/mysql-snapshot.tar ./this_dbThen copy the archive file to the `/tmp' directory on the slave server host. On that machine, change location into the slave's data directory, and unpack the archive file using this command:
shell> tar -xvf /tmp/mysql-snapshot.tarYou may not want to replicate the
mysqldatabase if the slave server has a different set of user accounts from those that exist on the master. In this case, you should exclude it from the archive. You also need not include any log files in the archive, or the `master.info' or `relay-log.info' files. While the read lock placed by
FLUSH TABLES WITH READ LOCKis in effect, read the value of 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
Filecolumn shows the name of the log, while
Positionshows the offset. In this example, the binary log value is
mysql-bin.003and the offset is 73. Record the values. You will need to use 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. After you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master:
mysql> UNLOCK TABLES;If you are using
InnoDBtables, ideally you should use the
InnoDB Hot Backuptool. It 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.
InnoDB Hot Backupis a non-free (commercial) additional tool that is not included in the standard MySQL distribution. See the
InnoDB Hot Backuphome page at http://www.innodb.com/manual.php for detailed information and screenshots. Without the
Hot Backuptool, the quickest way to take a binary snapshot of
InnoDBtables is to shut down the master server and copy the
InnoDBdata files, log files, and table definition files (`.frm' files). To record the current log file name and offset, you should issue the following statements before you shut down the server:
mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS;Then record the log name and the offset from the output of
SHOW MASTER STATUSas was shown earlier. After recording the log name and the offset, shut down the server without unlocking the tables to make sure that the server goes down with the snapshot corresponding to the current log file and offset:
shell> mysqladmin -u root shutdownAn alternative that works for both
InnoDBtables is to take an SQL dump of the master instead of a binary copy as described in the preceding discussion. For this, you can use
mysqldump --master-dataon your master and later load the SQL dump file into your slave. However, this is slower than doing a binary copy. If the master has been previously running without
--log-binenabled, the log name and position values displayed by
SHOW MASTER STATUSor
mysqldump --master-datawill be empty. In that case, the values that you will need to use later when specifying the slave's log file and position are the empty string (
[mysqld]section of the `my.cnf' file on the master host includes a
log-binoption. The section should also have a
master_idmust be a positive integer value from 1 to 2^32 - 1. For example:
[mysqld] log-bin server-id=1If those options are not present, add them and restart the server.
slave_idvalue, like the
master_idvalue, must be a positive integer value from 1 to 2^32 - 1. In addition, it is very important that the ID of the slave be different from the ID of the master. For example:
[mysqld] server-id=2If you are setting up multiple slaves, each one must have a unique
server-idvalue that differs from that of the master and from each of the other slaves. Think of
server-idvalues as something similar to IP addresses: These IDs uniquely identify each server instance in the community of replication partners. If you don't specify a
server-idvalue, it will be set to 1 if you have not defined
master-host, else it will be set to 2. Note that in the case of
server-idomission, a master will refuse connections from all slaves, and a slave will refuse to connect to a master. Thus, omitting
server-idis good only for backup with a binary log.
mysqldump, start the slave first (see next step).
--skip-slave-startoption so that it doesn't immediately try to connect to its master. You also may want to start the slave server with the
--log-warningsoption (enabled by default as of MySQL 4.0.19 and 4.1.2), to get more messages in the error log about problems (for example, network or connection problems). As of MySQL 4.0.21 and 4.1.3, aborted connections are not logged to the error log unless the value is greater than 1.
mysqldump, load the dump file into the slave server:
shell> mysql -u root -p < dump_file.sql
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 length for the string options:
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 value for the master, slaves will
not be able to connect to it.
If you have forgotten to set the
server-id value for the slave, you will get
the following error in its error log:
Warning: You should set server-id to a non-0 value if master_host is set; we force server id to 2, but this MySQL server will not act as a slave.
You will 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 will find in its data directory one
`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 really know what you are doing and understand
the implications. Even in that case,
it is preferred that you use the
CHANGE MASTER TO statement.
Note: The content of `master.info' overrides some options specified on the command line or in `my.cnf'. See section 6.8 Replication Startup Options for more details.
Once you have a snapshot, 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.
The original binary log format was developed in MySQL 3.23. It changed in MySQL 4.0, and again in MySQL 5.0. This has consequences when you upgrade servers in a replication setup, as described in section 6.6 Upgrading a Replication Setup.
As far as replication is concerned, any MySQL 4.1.x version and any 4.0.x version are identical, because they all use the same binary log format. Thus, any servers from these versions are compatible, and replication between them should work seamlessly. The exceptions to this compatibility is that versions from MySQL 4.0.0 to 4.0.2 were very early development versions that should not be used anymore. (These were the alpha versions in the 4.0 release series. Compatibility for them is still documented in the manual included with their distributions.)
The following table indicates master/slave replication compatibility between different versions of MySQL.
|3.23.33 and up||4.0.3 and up or any 4.1.x||5.0.0|
|Slave||3.23.33 and up||yes||no||no|
|Slave||4.0.3 and up||yes||yes||no|
As a general rule, we recommended using recent MySQL versions, because replication capabilities are continually being improved. We also recommend using the same version for both the master and the slave.
When you upgrade servers that participate in a replication setup, the procedure for upgrading depends on the current server versions and the version to which you are upgrading.
This section applies to upgrading replication from MySQL 3.23 to 4.0 or 4.1. A 4.0 server should be 4.0.3 or newer, as mentioned in section 6.5 Replication Compatibility Between MySQL Versions.
When you upgrade a master from MySQL 3.23 to MySQL 4.0 or 4.1, you should first ensure that all the slaves of this master are already at 4.0 or 4.1. If that is not the case, you should first upgrade your slaves: Shut down each one, upgrade it, restart it, and restart replication.
The upgrade can safely be done using the following procedure, assuming that you have a 3.23 master to upgrade and the slaves are 4.0 or 4.1. Note that after the master has been upgraded, you should not restart replication using any old 3.23 binary logs, because this will unfortunately confuse the 4.0 or 4.1 slave.
FLUSH TABLES WITH READ LOCKstatement.
SHOW MASTER STATUSon the master to obtain its current binary log file and position. Then, for each slave, use those values with a
SELECT MASTER_POS_WAIT()statement. The statement will block on the slave and return when the slave has caught up. Then run
STOP SLAVEon the slave.
SHOW MASTER STATUSstatement on the master. Then issue these statements on each slave:
mysql> CHANGE MASTER TO MASTER_LOG_FILE='binary_log_name', -> MASTER_LOG_POS=4; mysql> START SLAVE;
This section applies to upgrading replication from MySQL 3.23, 4.0, or 4.1 to 5.0.0. A 4.0 server should be 4.0.3 or newer, as mentioned in section 6.5 Replication Compatibility Between MySQL Versions.
First, note that MySQL 5.0.0 is an alpha release. It is intended to work
better than older versions (easier upgrade, replication of some important
session variables such as
sql_mode; see section D.1.4 Changes in release 5.0.0 (22 Dec 2003: Alpha)). However
it has not yet been extensively tested. As with any alpha release, we
recommend that you not use it in critical production environments yet.
When you upgrade a master from MySQL 3.23, 4.0, or 4.1 to 5.0.0, you should first ensure that all the slaves of this master are already 5.0.0. If that's not the case, you should first upgrade your slaves. To upgrade each slave, just shut it down, upgrade it to 5.0.0, restart it, and restart replication. The 5.0.0 slave will be able to read its old relay logs that were written before the upgrade and execute the statements they contain. Relay logs created by the slave after the upgrade will be in 5.0.0 format.
After the slaves have been upgraded, shut down your master, upgrade it to 5.0.0, and restart it. The 5.0.0 master will be able to read its old binary logs that were written before the upgrade and send them to the 5.0.0 slaves. The slaves will recognize the old format and handle it properly. Binary logs created by master after the upgrade will be in 5.0.0 format. These too will be recognized by the 5.0.0 slaves.
In other words, there are no measures to take when upgrading to 5.0.0, except that slaves must be 5.0.0 before you can upgrade the master to 5.0.0. Note that downgrading from 5.0.0 to older versions does not work so automatically: You must ensure that any 5.0.0 binary logs or relay logs have been fully processed, so that you can remove them before proceeding with the downgrade.
The following list explains
what is supported and what is not.
InnoDB-specific information about replication is given in
InnoDB and MySQL Replication.
LOAD_FILE()functions are replicated without changes and will thus not work reliably on the slave. This is also true for
CONNECTION_ID()in slave versions older than 4.1.1. The new
PASSWORD()function in MySQL 4.1 is well replicated in masters from 4.1.1 and up; your slaves also must be 4.1.1 or above to replicate it. If you have older slaves and need to replicate
PASSWORD()from your 4.1.x master, you must start your master with the
--old-passwordoption, so that it uses the old implementation of
PASSWORD(). (Note that the
PASSWORD()implementation in MySQL 4.1.0 differs from every other version of MySQL. It is best to avoid 4.1.0 in a replication situation.)
FOREIGN_KEY_CHECKSvariable is replicated as of MySQL 4.0.14. The
SQL_AUTO_IS_NULLvariables are replicated as of 5.0.0. The
table_typevariables is not yet replicated, which is a good thing for replication between different storage engines.
--default-collation) on the master and the slave. Otherwise, you may get duplicate-key errors on the slave, because a key that is regarded as unique in the master's character set may not be unique in the slave's character set. Second, if the master is strictly older than MySQL 4.1.3, the character set of the session should never be made different from its global value (in other words, don't use
SET CHARACTER SETetc) because this character set change will not be known to the slave. If the master is 4.1.3 or newer, and the slave too, the session can freely set its local value of character set variables (
COLLATION_SERVERetc) as these settings will be written to the binary log and then known to the slave. The session will however be prevented from changing the global value of these; as said already the master and slave must always have identical global character set values. There also is one last limitation: if on the master you have databases with different character sets from the global
collation_servervalue, you should design your
CREATE TABLEstatements so that they don't implicitly rely on the default database's character set, because there currently is a bug (Bug #2326); a good workaround is to explicitly state the character set and collation in a clause of the
InnoDBmaster table as a
MyISAMslave table. However, if you do this, you will have problems if the slave is stopped in the middle of a
BEGIN/COMMITblock, because the slave will restart at the beginning of the
BEGINblock. This issue is on our TODO and will be fixed in the near future.
@var_name) are badly replicated in 3.23 and 4.0. This is fixed in 4.1. Note that user variable names are case insensitive starting from MySQL 5.0. You should take this into account when setting up replication between 5.0 and an older version.
INDEX DIRECTORYclause is used in a
CREATE TABLEstatement on the master server, the clause is also used on the slave. This can cause problems if no corresponding directory exists in the slave host filesystem or exists but is not accessible to the slave server. Starting from MySQL 4.0.15, there is a
NO_DIR_IN_CREATE. If the slave server is run with its SQL mode set to include this option, it will simply ignore the clauses before replicating the
CREATE TABLEstatement. The result is that the
MyISAMdata and index files are created in the table's database directory.
LOAD DATA INFILEis interrupted in the middle (integrity constraint violation, killed connection...), the slave will skip this
LOAD DATA INFILEentirely. It means that if this command permanently inserted/updated some table records before being interrupted, these modifications won't be replicated to the slave. This will be fixed when MySQL features a record-level binary log format, in development.
OPTIMIZE TABLE, and
REPAIR TABLEstatements are not written to the binary log and thus are not replicated to the slaves. This is not normally a problem because these statements do not modify table data. However, it can cause difficulties under certain circumstances. If you replicate the privilege tables in the
mysqldatabase and update those tables directly without using the
GRANTstatement, you must issue a
FLUSH PRIVILEGESstatement on your slaves to put the new privileges into effect. Also if you use
FLUSH TABLESwhen renaming a
MyISAMtable that is part of a
MERGEtable, you will have to issue
FLUSH TABLESmanually on the slaves. As of MySQL 4.1.1, these statements are written to the binary log (unless you specify
NO_WRITE_TO_BINLOG, or its alias
LOCAL). Exceptions are that
FLUSH SLAVE, and
FLUSH TABLES WITH READ LOCKare not logged in any case. (Any of them may cause problems if replicated to a slave.) For a syntax example, see section 220.127.116.11
SELECTqueries to different slaves.
HEAP) tables become empty. As of MySQL 4.0.18, the master replicates this effect as follows: The first time that the master uses each
MEMORYtable after startup, it notifies slaves that the table needs to be emptied by writing a
DELETE FROMstatement for the table to its binary log. See section 14.3 The
HEAP) Storage Engine for more details.
SHOW STATUSto check the value of the
mysqladmin shutdowncommand to shut down the slave.
--log-slave-updatesoption specified. Note, however, that many statements will not work correctly in this kind of setup unless your client code is written to take care of the potential problems that can occur from updates that occur in different sequence on different servers. This means that you can create a setup such as this:
A -> B -> C -> AServer IDs are encoded in the binary log events, so server A will know when an event that it reads was originally created by itself and will not execute the event (unless server A was started with the
--replicate-same-server-idoption, which is meaningful only in rare setups). Thus, there will be no infinite loop. But this circular setup will work only if you perform no conflicting updates between the tables. In other words, if you insert data in both A and C, you should never insert a row in A that may have a key that conflicts with a row inserted in C. You should also not update the same rows on two servers if the order in which the updates are applied is significant.
--master-connect-retryoption.) The slave will also be able to deal with network connectivity outages. However, the slave will notice the network outage only after receiving no data from the master for
slave_net_timeoutseconds. If your outages are short, you may want to decrease
slave_net_timeout. See section 5.2.3 Server System Variables.
InnoDBtables and the
--innodb-safe-binlogoption on the master. See section 5.9.4 The Binary Log.
MyISAMtables, 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 will exit and wait for the database administrator to decide what to do about it unless the error code is legitimate and the statement execution results in the same error code. If this error code validation behavior is not desirable, some or all errors can be masked out (ignored) with the
--slave-skip-errorsoption. This option is available starting with MySQL 3.23.47.
BEGIN/COMMITsegment, updates to the binary log may be out of sync if some thread changes the non-transactional table before the transaction commits. This is because the transaction is written to the binary log only when it is committed.
COMMITor not written at all if you use
ROLLBACK. You must take this into account when updating both transactional tables and non-transactional tables within the same transaction. (This is true not only for replication, but also if you are using binary logging for backups.) In version 4.0.15, we changed the logging behavior for transactions that mix updates to transactional and non-transactional tables, which solves the problems (order of statements is good in the binary log, and all needed statements are written to the binary log even in case of
ROLLBACK). The problem that remains is when a second connection updates the non-transactional table while the first connection's transaction is not finished yet; wrong order can still occur, because the second connection's update will be written immediately after it is done.
LOAD DATA INFILEfrom a 3.23 master, the values of the
SHOW SLAVE STATUSbecome incorrect. The incorrectness of
Exec_Master_Log_Poswill cause a problem when you stop and restart replication; so it is a good idea to correct the value before this, by doing
FLUSH LOGSon the master. These bugs are already fixed in MySQL 5.0.0 slaves.
The following table lists replication problems in MySQL 3.23 that are fixed in MySQL 4.0:
LOAD DATA INFILEis handled properly, as long as the data file still resides on the master server at the time of update propagation.
LOAD DATA LOCAL INFILEis no longer skipped on the slave as it was in 3.23.
RAND()in updates does not replicate properly. Use
RAND(some_non_rand_expr)if you are replicating updates with
RAND(). You can, for example, use
UNIX_TIMESTAMP()as the argument to
On both the master and the slave, you must use the
to establish a unique replication ID for each server. You should pick a unique
positive integer in the range from 1 to 2^32 - 1 for each master and slave.
The options that you can use on the master server for controlling binary logging are described in section 5.9.4 The Binary Log.
The following table describes the options you can use on slave replication servers. You can specify them on the command line or in an option file.
Some slave server replication options are handled in a special way, in the sense that they are ignored if a `master.info' file exists when the slave starts and contains values for the options. The following options are handled this way:
As of MySQL 4.1.1, the following options also are handled specially:
The `master.info' file format in 4.1.1 changed to include values corresponding to the SSL options. In addition, the 4.1.1 file format includes as its first line the number of lines in the file. If you upgrade an older server to 4.1.1, the new server upgrades the `master.info' file to the new format automatically when it starts. However, if you downgrade a 4.1.1 or newer server to a version older than 4.1.1, you should manually remove the first line before starting the older server for the first time. Note that, in this case, the downgraded server no longer can use an SSL connection to communicate with the master.
If no `master.info' file exists when the slave server starts,
it uses values for those options that are specified in option files
or on the command line. This will occur when you start the server
as a replication slave for the very first time, or when you have run
RESET SLAVE and shut down and restarted the slave server.
If the `master.info' file exists when the slave server starts, the server ignores those options. Instead, it uses the values found in the `master.info' file.
If you restart 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
Suppose that you specify this option in your `my.cnf' file:
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 will have 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 18.104.22.168
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 startup options 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
options, can be set only when the slave server starts. We plan to fix this.
--log-binoption to enable binary logging.
--log-slave-updatesis used when you want to chain replication servers. For example, you might want a setup like this:
A -> B -> CThat is, 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-binto enable binary logging, and B with the
--skip-log-warnings. As of MySQL 4.0.21 and 4.1.3, aborted connections are not logged to the error log unless the value is greater than 1. This option is not limited to replication use only. It produces warnings across a spectrum of server activities.
configureoptions, this should be 3306.
--ssl-keyoptions described in section 22.214.171.124 SSL Command-Line Options. The values in the `master.info' file take precedence if they can be read. These options are operational as of MySQL 4.1.1.
REPLICATION SLAVEprivilege. (Prior to MySQL 4.0.2, it must have the
FILEprivilege instead.) The value in the `master.info' file takes precedence if it can be read. If the master user is not set, user
SUPERprivilege. This can be useful to ensure that a slave server accepts no updates from clients. This option is available as of MySQL 4.0.14.
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.
host_name-relay-bin.index, where host_name is the name of the slave server.
SET GLOBAL relay_log_purge. This option is available as of MySQL 4.1.1.
--relay-log-space-limitto less than twice the value of
--max-relay-log-sizeis 0). In that case, there is a chance that the I/O thread will wait for free space because
--relay-log-space-limitis exceeded, but the SQL thread will have no relay log to purge and be unable to satisfy the I/O thread. This forces the I/O thread to temporarily ignore
USE) is db_name. To specify more than one database, use this option multiple times, once for each database. Note that this will not replicate cross-database statements such as
UPDATE some_db.some_table SET foo='bar'while having selected a different database or no database. If you need cross-database updates to work, make sure that you have MySQL 3.23.28 or later, and use
--replicate-wild-do-table=db_name.%. Please read the notes that follow this option list. An example of what does not work as you might expect: If the slave is started with
--replicate-do-db=salesand you issue the following statements on the master, the
UPDATEstatement will not be replicated:
USE prices; UPDATE sales.january SET amount=amount+1000;If you need cross-database updates to work, use
--replicate-wild-do-table=db_name.%instead. The main reason for this ``just-check-the-default-database'' behavior is that it's difficult from the statement alone to know whether or not it should be replicated (for example, if you are using multiple-table
UPDATEstatements that go across multiple databases). It's also very fast to just check the default database.
--replicate-do-db. Please read the notes that follow this option list.
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 don't want these updates to be replicated. Please read the notes that follow this option list. An example of what does not work as you might expect: If the slave is started with
--replicate-ignore-db=salesand you issue the following statements on the master, the
UPDATEstatement will be replicated:
USE prices; UPDATE sales.january SET amount=amount+1000;If you need cross-database updates to work, use
--replicate-ignore-db. Please read the notes that follow this option list.
LIKEpattern-matching operator. To specify more than one table, use this option multiple times, once for each table. This will work for cross-database updates. Please read the notes that follow this option list. Example:
--replicate-wild-do-table=foo%.bar%will replicate only updates that use a table where the database name starts with
fooand 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 (
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
my1ownAABCdbdatabase, 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
bashshell, you would need to type
--replicate-wild-ignore-table=foo%.bar%will not replicate updates that use a table where the database name starts with
fooand the table name starts with
bar. For information about how matching works, see the description of the
--replicate-wild-ignore-tableoption. The rules for including literal wildcard characters in the option value are the same as for
USE) to to_name if it was from_name on the master. Only statements involving tables are affected (not statements such as
DROP DATABASE, and
ALTER DATABASE), and only if from_name was the default database on the master. This will not work for cross-database updates. Note that the database name translation is done before
--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"
--log-slave-updatesis used. Be careful that starting from MySQL 4.1, 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 compared to 4.0). So if you want to use
--replicate-same-server-idin 4.1 versions, be sure to start the slave with this option before you make the slave read its own events which you want the slave SQL thread to execute.
SHOW SLAVE HOSTSon 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
NATand other routing issues, that IP may not be valid for connecting to the slave from the master or other hosts. This option is available as of MySQL 4.0.0.
tmpdirsystem variable. When the slave SQL thread replicates a
LOAD DATA INFILEstatement, it extracts the to-be-loaded file from the relay log into temporary files, then loads these into the table. If the file loaded on the master was huge, the temporary files on the slave will be 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, you may also use the
--relay-logoption to place the relay logs in that filesystem, because the relay logs will be huge as well.
--slave-load-tmpdirshould point to a disk-based filesystem, not a memory-based one: The slave needs the temporary files used to replicate
LOAD DATA INFILEto survive a machine's restart. The directory also should not be one that is cleared by the operating system during the system startup process.
--slave-skip-errors= [err_code1,err_code2,... | all]
SHOW SLAVE STATUS. The server error codes are listed in section 23 Error Handling in MySQL. You can (but should not) also use the very non-recommended value of
allwhich will ignore all error messages and keep barging along regardless of what happens. Needless to say, if you use it, we make no promises regarding your data integrity. Please do not complain if your data on the slave is not anywhere close to what it is on the master in this case. You have been warned. Examples:
--replicate-* rules are evaluated as follows to determine whether a
statement will be executed by the slave or ignored:
--binlog-ignore-db(see section 5.9.4 The Binary Log). What is the result of the test?
INSERT INTO sales SELECT * FROM prices: only
saleswill be compared to the rules). If several tables are to be updated (multiple-table statement), the first matching table (matching ``do'' or ``ignore'') wins. That is, the first table is compared to the rules. Then, if no decision could be mad, the second table is compared to the rules, and so forth.
--replicate-*-tablerule was matched. Is there another table to test against these rules?
Q: How do I configure a slave if the master is already running and I do not want to stop it?
A: There are several options. If you have taken a backup of the
master at some point and recorded the binary log name and offset (from the
SHOW MASTER STATUS ) corresponding to the snapshot, use
the following procedure:
mysql> CHANGE MASTER TO -> MASTER_HOST='master_host_name', -> MASTER_USER='master_user_name', -> MASTER_PASSWORD='master_pass', -> MASTER_LOG_FILE='recorded_log_file_name', -> MASTER_LOG_POS=recorded_log_position;
START SLAVEon the slave.
If you do not have a backup of the master server already, here is a quick procedure for creating one. All steps should be performed on the master host.
mysql> FLUSH TABLES WITH READ LOCK;
shell> tar zcf /tmp/backup.tar.gz /var/lib/mysql
mysql> SHOW MASTER STATUS;
mysql> UNLOCK TABLES;
An alternative is to make an SQL dump of the master instead of a binary copy
as in the preceding procedure. To do this, you can use
--master-data on your master and later load the SQL dump into your slave.
However, this is slower than making a binary copy.
No matter which of the two methods you use, afterward follow the instructions for the case when you have a snapshot and have recorded the log name and offset. You can use the same snapshot to set up several slaves. Once you have the snapshot of the master, you can wait to set up a slave as long as the binary logs of the master are left intact. The two practical limitations on the length of time you can wait are the amount of disk space available to retain binary logs on the master and the length of time it will take the slave to catch up.
You can also use
LOAD DATA FROM MASTER. This is a convenient
statement that transfers a snapshot to the slave and adjusts the log name
and offset all at once. In the future,
LOAD DATA FROM MASTER will be
the recommended way to set up a slave. Be warned, however, that it works
MyISAM tables and it may hold a read lock for a long time.
It is not yet implemented as efficiently as we would like. If you have large
tables, the preferred method at this time is still to make a binary snapshot
on the master server after executing
FLUSH TABLES WITH READ LOCK.
Q: Does the slave need to be connected to the master all the time?
A: No, it does not. The slave can go down or stay disconnected for hours or even days, then reconnect and catch up on the updates. For example, you can set up a master/slave relationship over a dial-up link where the link is up only sporadically and for short periods of time. The implication of this is that, at any given time, the slave is not guaranteed to be in sync with the master unless you take some special measures. In the future, we will have the option to block the master until at least one slave is in sync.
Q: How do I know how late a slave is compared to the master? In other words, how do I know the date of the last query replicated by the slave?
If the slave is 4.1.1 or newer, read the
SHOW SLAVE STATUS. For older versions, the following applies.
This is possible only if
SHOW SLAVE STATUS on the slave shows that the
SQL thread is running (or for MySQL 3.23, that the slave thread is running),
and that the thread has executed at least one event from the master.
See section 6.3 Replication Implementation Details.
When the slave SQL thread executes an event read from the master, it
modifies its own time to the event timestamp (this is why
is well replicated). In the
Time column in the output of
SHOW PROCESSLIST, the number of seconds displayed for the slave SQL
thread is the number of seconds between the timestamp of the last replicated
event and the real time of the slave machine. You can use this to determine
the date of the last replicated event. Note that if your slave has been
disconnected from the master for one hour, and then reconnects, you may
Time values like 3600 for the slave SQL thread in
SHOW PROCESSLIST. This would be because the slave is executing
statements that are one hour old.
Q: How do I force the master to block updates until the slave catches up?
A: Use the following procedure:
mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS;Record the log name and the offset from the output of the
SHOWstatement. These are the replication coordinates.
MASTER_POS_WAIT()function are the replication coordinate values obtained in the previous step:
mysql> SELECT MASTER_POS_WAIT('log_name', log_offset);The
SELECTstatement will block until the slave reaches the specified log file and offset. At that point, the slave will be in sync with the master and the statement will return.
mysql> UNLOCK TABLES;
Q: What issues should I be aware of when setting up two-way replication?
A: MySQL replication currently does not support any locking protocol between master and slave to guarantee the atomicity of a distributed (cross-server) update. In other words, it is possible for client A to make an update to co-master 1, and in the meantime, before it propagates to co-master 2, client B could make an update to co-master 2 that will make the update of client A work differently than it did on co-master 1. Thus, when the update of client A makes it to co-master 2, it will produce tables that are different than what you have on co-master 1, even after all the updates from co-master 2 have also propagated. This means that you should not co-chain two servers in a two-way replication relationship unless you are sure that your updates can safely happen in any order, or unless you take care of mis-ordered updates somehow in the client code.
You must also realize that two-way replication actually does not improve performance very much (if at all), as far as updates are concerned. Both servers need to do the same number of updates each, as you would have one server do. The only difference is that there will be a little less lock contention, because the updates originating on another server will be serialized in one slave thread. Even this benefit might be offset by network delays.
Q: How can I use replication to improve performance of my system?
A: You should set up one server as the master and direct all
writes to it. Then configure as many slaves as you have the budget and
rackspace for, and distribute the reads among the master and the slaves.
You can also start the slaves with the
to get speed improvements on the slave end. In this case, the slave will
MyISAM tables instead of
BDB tables to get more speed.
Q: What should I do to prepare client code in my own applications to use performance-enhancing replication?
A: If the part of your code that is responsible for database access has been properly abstracted/modularized, converting it to run with a replicated setup should be very smooth and easy. Just change the implementation of your database access to send all writes to the master, and to send reads to either the master or a slave. If your code does not have this level of abstraction, setting up a replicated system will give you the opportunity and motivation to it clean up. You should start by creating a wrapper library or module with the following functions:
safe_ in each function name means that the function will take care
of handling all the error conditions.
You can use different names for the
functions. The important thing is to have a unified interface for connecting
for reads, connecting for writes, doing a read, and doing a write.
You should then convert your client code to use the wrapper library. This may be a painful and scary process at first, but it will pay off in the long run. All applications that use the approach just described will be able to take advantage of a master/slave configuration, even one involving multiple slaves. The code will be a lot easier to maintain, and adding troubleshooting options will be trivial. You will just need to modify one or two functions; for example, to log how long each statement took, or which statement among your many thousands gave you an error.
If you have
written a lot of code already, you may want to automate the conversion
task by using the
replace utility that comes with standard MySQL
distributions, or just write your own conversion script. Ideally, your
code already uses consistent programming style conventions. If not, then you
are probably better off rewriting it anyway, or at least going through
and manually regularizing it to use a consistent style.
Q: When and how much can MySQL replication improve the performance of my system?
A: MySQL replication is most beneficial for a system with frequent reads and infrequent writes. In theory, by using a single-master/multiple-slave setup, you can scale the system by adding more slaves until you either run out of network bandwidth, or your update load grows to the point that the master cannot handle it.
In order to determine how many slaves you can get before the added benefits
begin to level out, and how much you can improve performance of your site,
you need to know your query patterns, and to determine empirically by
benchmarking the relationship between the throughput for reads (reads per
max_reads) and for writes (
max_writes) on a typical
master and a typical slave. The example here shows a rather simplified
calculation of what you can get with replication for a hypothetical system.
Let's say that system load consists of 10% writes and 90% reads, and we
have determined by benchmarking that
max_reads is 1200 -
max_writes. In other words, the system can do 1,200 reads per
second with no writes, the average write is twice as slow as the average
read, and the relationship is linear. Let us suppose that the master
and each slave have the same capacity, and that we have one master and N
slaves. Then we have for each server (master or slave):
reads = 1200 - 2 * writes
reads = 9 * writes / (N + 1) (reads are split, but writes go
to all servers)
9 * writes / (N + 1) + 2 * writes = 1200
writes = 1200 / (2 + 9/(N+1))
The last equation indicates that the maximum number of writes for N slaves, given a maximum possible read rate of 1,200 per minute and a ratio of nine reads per write.
This analysis yields the following conclusions:
Note that these computations assume infinite network bandwidth and neglect several other factors that could turn out to be significant on your system. In many cases, you may not be able to perform a computation similar to the just shown that will accurately predict what will happen on your system if you add N replication slaves. However, answering the following questions should help you decide whether and how much replication will improve the performance of your system:
Q: How can I use replication to provide redundancy/high availability?
A: With the currently available features, you would have to set up a master and a slave (or several slaves), and write a script that will monitor the master to see whether it is up. Then instruct your applications and the slaves to change master in case of failure. Some suggestions:
CHANGE MASTER TOstatement.
bindyou can use `nsupdate' to dynamically update your DNS.
--log-binoption and without
--log-slave-updates. This way the slave will be ready to become a master as soon as you issue
RESET MASTER, and
CHANGE MASTER TOon the other slaves. For example, assume that you have the following setup:
WC \ v WC----> M / | \ / | \ v v v S1 S2 S3M means the master, S the slaves, WC the clients that issue database writes and reads; clients that issue only database reads are not represented, because they need not switch. S1, S2, and S3 are slaves running with
--log-slave-updates. Because updates received by a slave from the master are not logged in the binary log unless
--log-slave-updatesis specified, the binary log on each slave is empty. If for some reason M becomes unavailable, you can pick one slave to become the new master. For example, if you pick S1, all WC should be redirected to S1, and S2 and S3 should replicate from S1. Make sure that all slaves have processed any statements in their relay log. On each slave, issue
STOP SLAVE IO_THREAD, then check the output of
SHOW PROCESSLISTuntil you see
Has read all relay log. When this is true for all slaves, they can be reconfigured to the new setup. On the slave S1 being promoted to become the master, issue
RESET MASTER. On the other slaves S2 and S3, use
CHANGE MASTER TO MASTER_HOST='S1'(where
'S1'represents the real hostname of S1). To
CHANGE MASTER, add all information about how to connect to S1 from S2 or S3 (user, password, port). In
CHANGE MASTER, there is no need to specify the name of S1's binary log or binary log position to read from: We know it is the first binary log and position 4, which are the defaults for
CHANGE MASTER. Finally, use
START SLAVEon S2 and S3. Then instruct all WC to direct their statements to S1. From that point on, all updates statements sent by WC to S1 are written to the binary log of S1, which will contain exactly every update statement sent to S1 since M died. The result is this configuration:
WC / | WC | M(unavailable) \ | \ | v v S1<--S2 S3 ^ | +-------+When M is up again, you just have to issue on it the same
CHANGE MASTERas the one issued on S2 and S3, so that M becomes a slave of S1 and picks all the WC writes it has missed while it was down. Now to make M a master again (because it is the most powerful machine, for example), use the preceding procedure as if S1 was unavailable and M was to be the new master. During the procedure, don't forget to run
RESET MASTERon M before making S1, S2, and S3 slaves of M. Otherwise, they may pick up old WC writes from before the point at which M became unavailable.
We are currently working on integrating an automatic master election system into MySQL, but until it is ready, you will have to create your own monitoring tools.
If you have followed the instructions, and your replication setup is not working, first check the following:
SHOW MASTER STATUS. If it is,
Positionwill be non-zero. If not, verify that you are running the master with the
SHOW SLAVE STATUSto check whether the
Slave_SQL_Runningvalues are both
Yes. If not, verify the options that were used when starting the slave server.
SHOW PROCESSLIST, find the I/O and SQL threads and check their
Statecolumn to see how they display. See section 6.3 Replication Implementation Details. If the I/O thread state says
Connecting to master, verify the privileges for the replication user on the master, master hostname, your DNS setup, whether the master is actually running, and whether it is reachable from the slave.
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n; mysql> START SLAVE;The value of
nshould be 1 if the next statement from the master does not use
LAST_INSERT_ID(). Otherwise, the value should be 2. The reason for using a value of 2 for statements that use
LAST_INSERT_ID()is that they take two events in the binary log of the master.
When you have determined that there is no user error involved, and replication still either does not work at all or is unstable, it is time to send us a bug report. We need to get as much information as possible from you to be able to track down the bug. Please do spend some time and effort preparing a good bug report.
If you have a repeatable test case that demonstrates the bug, please enter it into our bugs database at http://bugs.mysql.com/. If you have a phantom problem (one that you cannot duplicate ``at will''), use the following procedure:
--log-binoptions. They will cause the slave to log the updates that it receives from the master into its own binary logs.
SHOW MASTER STATUSfrom the master at the time you have discovered the problem
SHOW SLAVE STATUSfrom the master at the time you have discovered the problem
mysqlbinlogto examine the binary logs. The following should be helpful to find the trouble query, for example:
shell> mysqlbinlog -j pos_from_slave_status \ /path/to/log_from_slave_status | head
Once you have collected the evidence for the phantom problem, try hard to isolate it into a separate test case first. Then enter the problem into our bugs database at http://bugs.mysql.com/ with as much information as possible.
Go to the first, previous, next, last section, table of contents.