Replication capabilities allowing the databases on one MySQL server to be duplicated on another were introduced in MySQL version 3.23.15. This section describes the various replication features in MySQL. It serves as a reference to the options available with replication. You will be introduced to replication and learn how to implement it. Toward that end, there are some frequently asked questions, descriptions of problems, and how to solve them.
For a description of the syntax of replication SQL statements, see section 13.6 Replication Statements.
We suggest that you visit our website at http://www.mysql.com/ often and read updates to this section. Replication is constantly being improved, and we update the manual frequently with the most current information.
Starting in Version 3.23.15, MySQL supports one-way replication internally. One server acts as the master, while one or more other servers act as slaves. The master server keeps a binary log of updates (see section 5.7.4 The Binary Log). It also maintains an index file of the binary logs to keep track of log rotation. Each slave, upon connecting, informs the master where it left off since the last successfully propagated update, 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 can also serve as a master if you 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 query processing load of the master. Queries that modify data should still be sent to the master so that the master and slave to 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 database (updates, deletes, etc) in the binary log (see section 5.7.4 The Binary Log). Each slave server receives from the master the saved queries that the master has recorded in its binary log, so that the slave can execute the same queries on its copy of the data.
It is very important to realize that the binary log is simply a record starting from a fixed point in time (the moment 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 data that is not the same as what was on the master when the binary log was started, your slaves may fail.
Starting from 4.0.0, you can use
LOAD DATA FROM MASTER to set up
a slave. Be aware that
LOAD DATA FROM MASTER currently works only
if all the tables on the master are
MyISAM type. Also, this statement
global read lock, so no writes are possible while the tables are being
transferred from the master. 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 is going to take is 1 second
per 1 MB of the datafile. You will get close to the estimate if both master
and slave are equivalent to 700 MHz Pentium and are connected through a
100 MBit/s network. Note that this is only a rough estimate.
Once a slave is properly configured and running, 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.
Three threads are involved in replication: one on the master and
two on the slave. When
START SLAVE is issued, the I/O thread
is created on the slave. It connects to the master and asks it to
send the queries recorded in its binlogs. Then one thread is created
on the master to send these binlogs. This thread is identified by
Binlog Dump in
SHOW PROCESSLIST output on the master.
The 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 last thread, the SQL thread, is
created on the slave; it reads the relay logs and executes the
queries it contains.
Note that the master has one thread for each currently connected slave server.
SHOW PROCESSLIST you can know what is happening on the
master and on the slave as regards 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 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
On the slave server, the output 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
Here thread 2 is on the master. Thread 10 is the I/O thread on the
Thread 11 is the SQL thread on the slave; note that the value in the
Time column can tell how late the slave is compared to the
master (see section 6.8 Replication FAQ).
The following list shows
the most common states you will see in the
column for the master's
Binlog Dump thread. If you don't see this
thread on a master server, replication is not running.
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
Here are the most common states you will see in the
column for the I/O thread of a slave server. Beginning with MySQL 4.1.1, this
state also appears in the
Slave_IO_State column of
SLAVE STATUS output. 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
master-connect-retryseconds before retrying.
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
Here are the most common states you will see in the
column for the SQL thread of a slave server:
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 a query string.
This indicates that the thread has read an event from the relay log,
extracted the query from it and is executing the query.
Before MySQL 4.0.2, the slave I/O and SQL threads were combined as a single thread, and no relay log files were used. The advantage of using two threads is that it separates query reading and query execution into two independent tasks, so the task of reading queries is not slowed down if query 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 binlog 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 queries, the I/O thread has at least fetched everything so that a safe copy of the queries is locally stored in the slave's relay logs for execution when next the slave starts. This allows the binlogs to be purged on the master, because it no longer need wait for the slave to fetch their contents.
By default, relay logs are named using filenames of the form
host_name is the name of the
slave server host, and
nnn is a sequence number.
Successive relay log files are created using successive sequence numbers,
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.
Relay logs have the same format as binary logs, so they can be read
A relay log is automatically deleted by the SQL thread as soon as it
no longer needs it (that is, as soon as it has executed all its
events). There is no command to delete relay logs, because
the SQL thread takes care of doing so. However, from MySQL 4.0.14,
FLUSH LOGS rotates relay logs, which will influence when
the SQL thread deletes them.
A new relay log is created under the following conditions:
FLUSH LOGSstatement is issued (4.0.14 and up only).
max_relay_log_size= 0 or MySQL is older than 4.0.14
A slave replication server
creates additional two small files in the data directory.
These files are named `master.info' and `relay-log.info' by default.
They contain information like that shown in the output of the
STATUS statement (see section 13.6.2 SQL Statements for Controlling Slave Servers for a description of this command).
As disk files they survive slave's shutdown. The next time the slave starts
up, it can read these files to know how far it has proceeded in
reading binlogs from the master and in processing its own relay logs.
The `master.info' file is updated by the I/O thread.
The correspondance between the lines in the file and the
columns displayed by
SHOW SLAVE STATUS is as follows:
|5|| Password (not shown by |
The `relay-log.info' file is updated by the SQL thread.
The correspondance 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 2 small files
as well, along with the relay log files. because 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 binlogs. Then you can use
CHANGE MASTER TO with the
MASTER_RELAY_POS options to tell the slave to re-read the binlogs from
that point. This requires that the binlogs still exist on the master server.
If your slave is subject to replicating
DATA INFILE statements, you should also backup the `SQL_LOAD-*' files
that may 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 statements.
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 on your current MySQL server. It assumes 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.8 Replication FAQ.
If you want to administer a MySQL replication setup, we suggest that you read this entire chapter through and try all commands mentioned in section 13.6.1 SQL Statements for Controlling Master Servers ans section 13.6.2 SQL Statements for Controlling Slave Servers. You should also familiarise yourself with replication startup options in `my.cnf' in section 6.7 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 MySQL versions older than 4.0.2, give the account the
FILEprivilege instead.) If the account is only for replication (which is recommended), you don't need to grant any additional privileges. The hostname in the account name should be such that each of the slave servers can use the account to connect to the master. For example, to create a user named
replwhich can access your master from any host, you might use this command:
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY '<password>';For MySQL versions older than 4.0.2, use this command instead:
mysql> GRANT FILE ON *.* TO repl@'%' IDENTIFIED BY '<password>';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.
FLUSH TABLES WITH READ LOCKcommand.
mysql> FLUSH TABLES WITH READ LOCK;and then take a snapshot of the data on your master server. The easiest way to create a snapshot is to simply use an archiving program (
WinZipor any similar software on Windows) to produce an archive of the databases in your master's data directory. For example, 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 not, you can 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,bar | foo,manual,mysql | +---------------+----------+--------------+------------------+ 1 row in set (0.06 sec)The
Filecolumn shows the name of the log, while
Positionshows the offset. In the above 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. Once 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 InnoDB tables, ideally you should use the InnoDB Hot Backup tool that is available to those who purchase MySQL commercial licenses, support, or the backup tool itself. 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. More information about the tool is available at http://www.innodb.com/order.php. Without the Hot Backup tool, the quickest way to take a snapshot of InnoDB tables is to shut down the master server and copy the InnoDB datafiles and logs, and the table definition files (
.frm). To record the current log file name and offset, you should do the following before you shut down the server:
mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS;And then record the log name and the offset from the output of
SHOW MASTER STATUSas was shown earlier. Once you have recorded the log name and the offset, shut down the server without unlocking the tables to make sure it goes down with the snapshot corresponding to the current log file and offset:
shell> mysqladmin -uroot shutdownAn alternative for both MyISAM and InnoDB tables is to take an SQL dump of the master instead of a binary copy like above; for this you can use
mysqldump --master-dataon your master and later run this SQL dump 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
mysqldumpwill be empty. In that case, record empty string ('') for the log name, and 4 for the offset.
[mysqld]section of the `my.cnf' file on the master host includes a
log-binoption. The section should also have a
master_idmust be an 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 an integer value from 1 to 2^32 - 1. In addition, it is very important that the ID of the slave be different than the ID of the master. For example:
[mysqld] server-id=2If you are setting up multiple slaves, each one must have a
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 only good for backup with a binary log.
mysqldump, start the slave first (see next step).
--skip-slave-startoption. You also may want to start the slave server with the
--log-warningsoption. That way, you will get more messages about problems (for example, network or connection problems).
mysqldump, load the dump file into the slave server:
shell> mysql -u root -p < dump_file.sql
<>with the actual values relevant to your system:
mysql> CHANGE MASTER TO -> MASTER_HOST='<master hostname>', -> MASTER_USER='<replication username>', -> MASTER_PASSWORD='<replication password>', -> MASTER_LOG_FILE='<recorded log file name>', -> MASTER_LOG_POS=<recorded log offset>;The following table lists the maximum string length for these variables:
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
server-id for the master, slaves will
not be able to connect to it.
If you have forgotten to set
server-id for the slave, you will get
the following error in its error log:
Warning: one should set server_id to a non-0 value if master_host is set. The 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 file called
`master.info' and another called `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
CHANGE MASTER TO command.
NOTE: The content of `master.info' overrides some options specified on the command-line or in `my.cnf' See section 6.7 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.
Any MySQL 4.1.x version is identical to MySQL 4.0.3 (and newer 4.0) as far as replication is concerned (same binary log format). So replication between 4.0.3 (and newer 4.0) and any 4.1.x (whatever of the two is the master or slave) is working seamlessly.
Binary log format was changed between MySQL 3.23 and MySQL 4.0, and between MySQL 4.0 (or 4.1, as it's the same binary log format) and MySQL 5.0. This has consequences on how to upgrade a replication setup, which is explained below.
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|
Versions 4.0.0, 4.0.1 and 4.0.2 were very early development versions which should not be used anymore (their compatibility is still documented in the manual included in these versions' distributions).
As a general rule, it's always recommended to use recent MySQL versions, because replication capabilities are continually being improved. We recommend using same version for both the master and the slave.
FLUSH TABLES WITH READ LOCK).
SHOW MASTER STATUSon the master, and
SELECT MASTER_POS_WAIT()on the slaves). Then run
STOP SLAVEon the slaves.
SHOW MASTER STATUSon the master. Then issue these commands on each slave:
mysql> CHANGE MASTER TO MASTER_LOG_FILE='<name>', MASTER_LOG_POS=4; mysql> START SLAVE;
sql_mode; see section C.1.2 Changes in release 5.0.0 (22 Dec 2003: Alpha)), it has not been tested a lot yet so, as with any alpha release, we recommend you do not use in critical production environment yet. When you upgrade a master from MySQL 3.23 or 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 as explained a few lines below). Then just shut down your master, upgrade it to 5.0.0 and restart it. The 5.0.0 master will be able to read the old binary logs (of before the master upgrade) and to send them to the 5.0.0 slaves which will recognize this old format and handle it. Binary logs created after the master upgrade will be in 5.0.0 format and be recognized by 5.0.0 slaves too. To upgrade the slaves, just shut them down, upgrade them to 5.0.0, and restart them (and restart replication). The 5.0.0 slaves will be able to read the old relay logs (of before the slave upgrade) and execute the statements they contain. Relay logs created after the slave upgrade will be in 5.0.0 format. In other words, there are no measures to take when upgrading to 5.0.0, except that slaves must be 5.0.0 to be able to upgrade the master to 5.0.0. Note that downgrading from 5.0.0 to older versions does not work as automatically; you will have to remove any 5.0.0 binary logs or relay logs before proceeding.
Here is an explanation of what is supported and what is not:
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 since 4.1.1 masters; your slaves must be 4.1.0 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 option
SQL_AUTO_IS_NULLvariables are replicated only starting from 5.0.0.
TABLE_TYPEvariables are not replicated yet.
FOREIGN_KEY_CHECKSis replicated since version 4.0.14.
--default-character-set) 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 character set may not be unique in the slave character set. Character sets will be replicated in 5.0.x.
BEGIN/COMMITblock, as the slave will later start at the beginning of the
BEGINblock. This issue is on our TODO and will be fixed in the near future.
INDEX DIRECTORYclause was used in a
CREATE TABLEon master, then these clauses will be used too on slave. Starting from MySQL 4.0.15 there is a
NO_DIR_IN_CREATE; if the slave server is run in this mode, it will simply cut off the clauses before replicating the
CREATE TABLE(so the MyISAM data and index files will be created in the slave's
REPAIRcommands are not stored in the binary log and thus are not replicated to the slaves. This is not normally a problem as these commands don't change anything. However, it does mean that if you update the MySQL privilege tables directly without using the
GRANTstatement and you replicate the
mysqlprivilege database, you must do a
FLUSH PRIVILEGESon your slaves to put the new privileges into effect. Also if you use
FLUSH TABLESwhen renaming a
MyISAMtable involved in a
MERGEtable, you will have to issue
FLUSH TABLESmanually on the slave. Since MySQL 4.1.1, these commands are written to the binary log (except
FLUSH TABLES WITH READ LOCK) unless you specify
NO_WRITE_TO_BINLOG(or its alias
LOCAL). For a syntax example, see section 22.214.171.124
SELECTqueries to different slaves.
HEAPtable having been emptied by master's shutdown/restart by writing a
DELETE FROMto its binary log the first time it uses the table since startup. See section 14.3
HEAPTables for more details.
SHOW STATUSto check the value of the
mysqladmin shutdowncommand to shut down the slave.
log-slave-updatesenabled. Note, however, that many queries will not work correctly in this kind of setup unless your client code is written to take care of the potential problems that can happen from updates that occur in different sequence on different servers. This means that you can do a setup like the following:
A -> B -> C -> AServer IDs are encoded in the binary log events. A will know when an event it reads had originally been created by A, so A will not execute it and there will be no infinite loop. But this circular setup will work only if you only if you perform no conflicting updates between the tables. In other words, if you insert data in A and C, you should never insert a row in A that may have a conflicting key with a row insert in C. You should also not update the same rows on two servers if the order in which the updates are applied matters.
master-connect-retryseconds (default 60). Because of this, it is safe to shut down the master, and then restart it after a while. 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. So if your outages are short, you may want to decrease
slave_net_timeout. See section 126.96.36.199
--slave-skip-errorsoption. This option is available starting with MySQL Version 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's commited.
COMMITor not written at all if you use
ROLLBACK; you have to take this into account when updating both transactional tables and non-transactional tables in the same transaction if you are using binary logging for backups or replication. 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 queries is good in binlog, and all needed queries are written to the binlog even in case of
ROLLBACK). The problem which 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).
The following table lists 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 LOCAL DATA INFILEwill be skipped.
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()for the argument to
RAND(). This is fixed in 4.0.
On both the master and the slave you need to use the
to establish a unique replication ID for each server. You should pick a unique
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 all described in section 5.7.4 The Binary Log.
The following table describes the options you can use on slave servers. You can specify them on the command line or in an option file.
NOTE: Replication handles the following options in a special way:
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 you have run
RESET SLAVE and shut down and restarted the slave server.
However, if the `master.info' file exists when the slave server starts, it uses the values in the file and IGNORES any values specified for those options in option files or on the command line.
Suppose you specify this option in your `my.cnf' file:
The first time you start the server as a replication slave, it will
read and use that option from the `my.cnf' file. The server
will then record that value in the `master.info' file. The
next time you start the server, it will read the master host value
from the `master.info' file only. If you modify the `my.cnf'
file to specify a different master host, it will have no effect.
You must use
CHANGE MASTER TO instead.
As of MySQL 4.1.1, the following options also are handled specially:
The `master.info' file includes the values corresponding to those 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 `master.info' will be upgraded to the new format automatically when the new server starts. (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.)
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 188.8.131.52
CHANGE MASTER TO.
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:
--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, where B is both a master and a slave, you must start B with the
--log-slave-updatesoption. A and B must both be started with binary logging enabled.
--bootstrap-master-host, but it is too late to change now.
REPLICATION SLAVEprivilege (prior to MySQL 4.0.2, it must have the
FILEprivilege instead). If the master user is not set, user
testis assumed. The value in `master.info' takes precedence if it can be read.
MYSQL_PORTis assumed. If you have not tinkered with
configureoptions, this should be 3306. The value in `master.info' takes precedence if it can be read.
--ssl-keyoptions described in section 184.108.40.206 SSL Command-line Options. These options are operational as of MySQL 4.1.1.
max_relay_log_size) and you need to put them on some area different from the data directory, or if you want to increase speed by balancing load between disks.
SET GLOBAL RELAY_LOG_PURGE=0|1. The default value is 1. 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) because in that case there are chances that when the I/O thread waits for free space because
--relay-log-space-limitis exceeded, the SQL thread has no relay log to purge and so cannot satisfy the I/O thread, forcing the I/O thread to temporarily ignore
--replicate-do-db. Please read the notes that follow this option list.
--replicate-ignore-db. Please read the notes that follow this option list.
--replicate-wild-do-table=foo%.bar%will replicate only updates that uses a table in any databases that start with
fooand whose table names start with
bar. Note that if you do
--replicate-wild-do-table=foo%.%then the rule will be propagated to
DROP DATABASE, that is, these two statements will be replicated if the database name matches the database pattern (
foo%here) (this magic is triggered by
%being the table pattern). Escaping wildcard characters
%: if you want to replicate, for example, all tables of the
my_own%dbdatabase (this is the exact name of the database), but not replicate tables from the
my1ownAABCdbdatabase, you should escape the
%: you should use something like this:
replicate-wild-do-table=my\_own\%db. And if you are specifying this option from the command-line, depending on your system you may need to escape the
\(for example, with a
bashshell, you would need to type
--replicate-wild-ignore-table=foo%.bar%will not do updates to tables in databases that start with
fooand whose table names start with
bar. Note that if you do
--replicate-wild-ignore-table=foo%.%then the rule will be propagated to
DROP DATABASE, that is, these two statements will not be replicated if the database name matches the database pattern (
foo%here) (this magic is triggered by
%being the table pattern). Escaping wildcard characters
%: see notes in the description of
database_name. To specify more than one database, use the directive multiple times, once for each database. Note that this will not replicate cross-database queries such as
UPDATE some_db.some_table SET foo='bar'while having selected a different or no database. If you need cross database updates to work, make sure you have 3.23.28 or later, and use
--replicate-wild-do-table=db_name.%. Please read the notes that follow this option list. Example of what does not work as you could expect it: if the slave is started with
--replicate-do-db=sales, and you do
USE prices; UPDATE sales.january SET amount=amount+1000;, this query will not be replicated. If you need cross database updates to work, use
--replicate-wild-do-table=db_name.%instead. The main reason for this ``just-check-the-current-database'' behavior is that it's hard from the command alone to know if a query should be replicated or not; for example if you are using multiple-table-delete or multiple-table-update commands that go across multiple databases. It's also very fast to just check the current database.
database_name. To specify more than one database to ignore, use the directive multiple times, once for each database. You should not use this directive if you are using cross table updates and you don't want these update to be replicated. Please read the notes that follow this option list. Example of what does not work as you could expect it: if the slave is started with
--replicate-ignore-db=sales, and you do
USE prices; UPDATE sales.january SET amount=amount+1000;, this query will be replicated. If you need cross database updates to work, use
to_nameif it was
from_nameon the master. Only statements involving tables may be affected (
DROP DATABASEwon't), and only if
from_namewas the current database on the master. This will not work for cross-database updates. Note that the translation is done before
--replicate-*rules are tested. Example:
SHOW SLAVE HOSTS. Leave 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 once 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.
tmpdirvariable. When the slave SQL thread replicates a
LOAD DATA INFILEcommand, 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 you may wish/have to tell the slave to put the temporary files on some large disk different from
tmpdir, using this option. In that case, you may also use the
--relay-logoption, as relay logs will be huge, too.
--slave-load-tmpdirshould point to a disk-based filesystem; not a memory-based one. Because the slave needs the temporary files used to replicate
LOAD DATA INFILE) to survive a machine's reboot.
--slave-skip-errors= [err_code1,err_code2,... | all]
SHOW SLAVE STATUS. A full list of error messages can be found in the source distribution in `Docs/mysqld_error.txt'. The server error codes also are listed at section 20.1 Error Returns. You can (but should not) also use a very non-recommended value of
allwhich will ignore all error messages and keep barging along regardless. 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:
Some of these options, like all
--replicate-* options, can only
be set at the slave server's startup, not on-the-fly. We plan to fix this.
Here is the order of evaluation of the
r--eplicate-* rules, to
decide if the query is going to be executed by the slave or ignored by
--binlog-ignore-db(see section 5.7.4 The Binary Log). What is the result of the test?
INSERT INTO sales SELECT * from prices: only
saleswill be compared to 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 rules, then if no decision could be taken the second table is compared to rules, etc).
--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 binlog name and offset ( from the
SHOW MASTER STATUS ) corresponding to the snapshot, do
mysql> CHANGE MASTER TO -> MASTER_HOST='master_host-name', -> MASTER_USER='master_user_name', -> MASTER_PASSWORD='master_pass', -> MASTER_LOG_FILE='recorded_log_name', -> MASTER_LOG_POS=recorded_log_pos;
START SLAVEon the slave.
If you do not have a backup of the master already, here is a quick way to do it consistently:
FLUSH TABLES WITH READ LOCK
gtar zcf /tmp/backup.tar.gz /var/lib/mysql(or a variation of this)
SHOW MASTER STATUS- make sure to record the output - you will need it later
An alternative is taking an SQL dump of the master instead of a binary
copy like above; for this you can use
on your master and later run this SQL dump into your slave. However, this is
slower than makeing a binary copy.
No matter which of the two methods you use, afterwards 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. As long as the binary logs of the master are left intact, you can wait as long as several days or in some cases maybe a month to set up a slave once you have the snapshot of the master. In theory the waiting gap can be infinite. The two practical limitations is the diskspace of the master getting filled with old logs, and the amount of time it will take the slave to catch up.
You can also use
LOAD DATA FROM
MASTER. This is a convenient command that takes a snapshot,
restores it to the slave, and adjusts the log name and offset on the slave
all at once. In the future,
LOAD DATA FROM MASTER will be the
recommended way to set up a slave. Be warned, howerver, that the read
lock may be held for a long time if you use this command. It is not yet
implemented as efficiently as we would like to have it. If you have
large tables, the preferred method at this time is still with a local
tar snapshot 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
This is possible only if the slave SQL thread exists
(that is, if it shows up in
SHOW PROCESSLIST, see section 6.3 Replication Implementation Details)
(in MySQL 3.23: if the slave thread exists, that is, shows up in
and if it has executed at least one event
from the master. Indeed, when the slave SQL thread executes an event
read from the master, this thread modifies its own time to the event's
timestamp (this is why
TIMESTAMP is well replicated). So 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, then reconnects,
you may immediately see
Time values like 3600 for the slave SQL
SHOW PROCESSLIST... This would be because the slave
is executing queries 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
MASTER_POS_WAIT()function are the values recorded 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 will make 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. So 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 amount 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 so, 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 money and
rackspace for, and distribute the reads among the master and the slaves.
You can also start the slaves with
to get speed improvements for the slave. In this case the slave will
MyISAM tables instead of
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/modularised, 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 the 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 query took, or which query, 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 the
standard distribution of MySQL, or just write your own Perl script.
Hopefully, your code follows some recognizable pattern. If not, then
you are probably better off rewriting it anyway, or at least going
through and manually beating it into a pattern.
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 empirically
(by benchmarking) determine the relationship between the throughput
on reads (reads per second, or
max_reads) and on writes
max_writes) on a typical master and a typical slave. The
example here will show you 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
max_reads to be 1200 - 2 *
In other words, the system can do 1200 reads per second with no
writes, the average write is twice as slow as average read,
and the relationship is
linear. Let us suppose that the master and each slave have the same
capacity, and that we have 1 master and N slaves. Then we have for each
server (master or slave):
reads = 1200 - 2 * writes (from benchmarks)
reads = 9* writes / (N + 1) (reads split, but writes go
to all servers)
9*writes/(N+1) + 2 * writes = 1200
writes = 1200/(2 + 9/(N+1)
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 one above 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 if it is up, and instruct your applications and the slaves of the master change in case of failure. Some suggestions:
CHANGE MASTER TOcommand.
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, consider you have the following setup (``M'' 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):
WC \ v WC----> M / | \ / | \ v v v S1 S2 S3S1 (like S2 and S3) is a slave running with
--log-slave-updates. As the only writes executed on S1 are those replicated from M, the binary log on S1 is empty (remember, S1 runs without
--log-slave-updates). Then, for some reason, M becomes unavailable, and you want S1 to become the new master (that is, direct all WC to S1, and make S2 and S3 replicate S1). Make sure that all slaves have processed any queries 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. Issue
STOP SLAVEon all slaves,
RESET MASTERon the slave being promoted to master, and
CHANGE MASTERon the other slaves. No WC accesses M. Instruct all WC to direct their queries to S1. From now on, all queries sent by WC to S1 are written to the binary log of S1. The binary log of S1 contains exactly every writing query sent to S1 since M died. On S2 (and S3) do
CHANGE MASTER TO MASTER_HOST='S1'(where
'S1'is replaced by 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, 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, from position 4, and these are the defaults of
CHANGE MASTER. Finally do
START SLAVEon S2 and S3, and now you have this:
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), follow the preceding procedure as if S1 was unavailable and M was to be the new master; then during the procedure don't forget to run
RESET MASTERon M before making S1, S2, S3 slaves of M, or they may pick old WC writes from before M's unavailibility.
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 have given the master
log-binoption and have set
SHOW SLAVE STATUSand check that the
Slave_SQL_Runningvalues are both
Yes. If not, verify slave options
SHOW PROCESSLIST, find the I/O and SQL threads (see section 6.3 Replication Implementation Details to see how they display), and check their
Statecolumn. If it 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, 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 query does not use
LAST_INSERT_ID(). Otherwise, the value should be 2. The reason for using a value of 2 for queries 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 way to demonstrate 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 in its own binlogs.
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:
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.