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.
We suggest that you visit our Web site at
http://www.mysql.com frequently as well as check for
revisions to this chapter. Replication is constantly being improved,
and we update the manual regularly with the most current
information.
6.1. Introduction to Replication
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 itself 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:
Robustness is increased with a master/slave setup. In the
event of problems with the master, you can switch to the slave
as a backup.
Better response time for clients can be achieved by splitting
the load for processing client queries between the master and
slave servers. SELECT queries 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.
Another benefit of using replication is that you can perform
backups using a slave server without disturbing the master.
The master continues to process updates while the backup is
being made. See Section 5.8.1, “Database Backups”.
6.2. Replication Implementation Overview
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.10.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 extremely 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 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 in the same state as those on the master when the binary
log was started, your slaves are quite likely to fail.
One way to copy the master's data to the slave is to use the
LOAD 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, 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 takes is 1 second per 1MB of data. This
is a rough estimate, but you should find it fairly accurate if
both master and slave are equivalent to 700MHz Pentium CPUs in
performance and are connected through a 100Mbps network.
After the slave has been set up with a copy of the master's data,
it connects to the master and waits for updates to process. If the
master fails, or the slave loses connectivity with your master,
the slave keeps trying to connect periodically until it is able to
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 of 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 a
START SLAVE is issued, the slave creates an I/O
thread, which 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 output of SHOW PROCESSLIST on the master.
The slave I/O thread reads what the master Binlog
Dump thread sends and copies this data to local files,
known as relay logs, in the slave's data
directory. The third thread is the SQL thread, which the slave
creates in order to read the relay logs and to execute the updates
they contain.
In the preceding description, there are three threads per slave. A
master that has multiple slaves creates one thread for each slave
that is currently connected slave; 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 stored locally in the slave's
relay logs, ready for execution the next time that the slave
starts. This allows the binary logs to be purged on the master,
because it no longer needs to wait for the slave to fetch their
contents.
The SHOW PROCESSLIST statement provides
information that tells you what is happening on the master and on
the slave regarding replication.
The following example illustrates how the three threads show up in
SHOW PROCESSLIST. The output format is that
used by SHOW PROCESSLIST as of MySQL version
4.0.15, when the content of the State column
was changed to be more meaningful compared to earlier versions.
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. At
the time that the SHOW PROCESSLIST was run,
both threads were idle, waiting for further updates.
Note that the value in the Time column can show
how late the slave is compared to the master. See
Section 6.9, “Replication FAQ”.
6.3.1. Replication Master Thread States
The following list shows the most common states you may 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, this
means that replication is not running — that is, that no
slaves are currently connected.
Sending binlog event to slave
Binary logs consist of events, where an
event is usually an update plus some other information. The
thread has read an event from the binary log and is now
sending it to the slave.
Finished reading one binlog; switching to next
binlog
The thread has finished reading a binary log file and is
opening the next one to send to the slave.
Has sent all binlog to slave; waiting for binlog to
be updated
The thread has read all outstanding updates from the binary
logs and sent them to the slave. The thread is now idle,
waiting for new events to appear in the binary log resulting
from new updates occurring on the master.
Waiting to finalize termination
A very brief state that occurs as the thread is stopping.
6.3.2. Replication Slave I/O Thread States
The following list shows the most common states you 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 by the
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
The thread is attempting to connect to the master.
Checking master version
A state that occurs very briefly, immediately after the
connection to the master is established.
Registering slave on master
A state that occurs very briefly immediately after the
connection to the master is established.
Requesting binlog dump
A state that occurs very briefly, immediately after the
connection to the master is established. The thread sends to
the master a request for the contents of its binary logs,
starting from the requested binary log filename and
position.
Waiting to reconnect after a failed binlog dump
request
If the binary log dump request failed (due to
disconnection), the thread goes into this state while it
sleeps, then tries to reconnect periodically. The interval
between retries can be specified using the
--master-connect-retry option.
Reconnecting after a failed binlog dump
request
The thread is trying to reconnect to the master.
Waiting for master to send event
The thread has connected to the master and is waiting for
binary log events to arrive. This can last for a long time
if the master is idle. If the wait lasts for
slave_read_timeout seconds, a timeout
occurs. At that point, the thread considers the connection
to be broken and make an attempt to reconnect.
Queueing master event to the relay log
The thread has read an event and is copying it to the relay
log so that the SQL thread can process it.
Waiting to reconnect after a failed master event
read
An error occurred while reading (due to disconnection). The
thread is sleeping for
master-connect-retry seconds before
attempting to reconnect.
Reconnecting after a failed master event
read
The thread is trying to reconnect to the master. When
connection is established again, the state becomes
Waiting for master to send event.
Waiting for the slave SQL thread to free enough
relay log space
You are using a non-zero
relay_log_space_limit value, and the
relay logs have grown until 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
A state that occurs briefly as the thread is stopping.
6.3.3. Replication Slave SQL Thread States
The following list shows the most common states you may see in
the State column for a slave server SQL
thread:
Reading event from the relay log
The thread has read an event from the relay log so that the
event can be processed.
Has read all relay log; waiting for the slave I/O
thread to update it
The thread has processed all events in the relay log files,
and is now waiting for the I/O thread to write new events to
the relay log.
Waiting for slave mutex on exit
A very brief state that occurs as the thread is stopping.
The 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.
6.3.4. Replication Relay and Status Files
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, beginning with
000001 (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
host_name-relay-bin.index.
By default, these files are created in the slave's data
directory. The default filenames may be overridden with the
--relay-log and
--relay-log-index server options. See
Section 6.8, “Replication Startup Options”.
Relay logs have the same format as binary logs, and can be read
using mysqlbinlog. 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,
FLUSH LOGS rotates relay logs, which
influences when the SQL thread deletes them.
A new relay log is created under the following conditions:
When the I/O thread starts for the first time after the
slave server starts.
When the logs are flushed; for example, with FLUSH
LOGS or mysqladmin flush-logs.
(This creates a new relay log only as of MySQL 4.0.14.)
When the size of the current relay log file becomes too
large. The meaning of “too large” is determined
as follows:
max_relay_log_size, if
max_relay_log_size > 0
max_binlog_size, if
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 status files 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:
Line
Description
1
Master_Log_File
2
Read_Master_Log_Pos
3
Master_Host
4
Master_User
5
Password (not shown by SHOW SLAVE STATUS)
6
Master_Port
7
Connect_Retry
As of MySQL 4.1, the file includes a line count and information
about SSL options:
Line
Description
1
Number of lines in the file
2
Master_Log_File
3
Read_Master_Log_Pos
4
Master_Host
5
Master_User
6
Password (not shown by SHOW SLAVE STATUS)
7
Master_Port
8
Connect_Retry
9
Master_SSL_Allowed
10
Master_SSL_CA_File
11
Master_SSL_CA_Path
12
Master_SSL_Cert
13
Master_SSL_Cipher
14
Master_SSL_Key
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
shown here:
Line
Description
1
Relay_Log_File
2
Relay_Log_Pos
3
Relay_Master_Log_File
4
Exec_Master_Log_Pos
When you back up the 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 can use CHANGE MASTER
TO with the MASTER_LOG_FILE and
MASTER_LOG_POS options to tell the slave to
re-read the binary logs from that point. Of course, this
requires that the binary logs still exist on the master server.
If your slave is subject to replicating LOAD DATA
INFILE statements, you should also back up any
SQL_LOAD-* files that exist in the
directory that the slave uses for this purpose. The slave needs
these files to resume replication of any interrupted
LOAD DATA INFILE operations. The directory
location is specified using the
--slave-load-tmpdir option. Its default value,
if not specified, is the value of the tmpdir
variable.
6.4. How to Set Up Replication
Here is a brief description of how to set up complete replication
of your current MySQL server. It assumes that you want to
replicate all databases on the master and have not previously
configured replication. You need to shut down your master server
briefly to complete the steps outlined here.
This 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 have a snapshot
of the master's data, and the master 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”.
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.
Please do not report bugs until you have verified that the
problem is present in the latest MySQL release.
Set up an account on the master server that the slave server
can use to connect. This account must be given the
REPLICATION SLAVE privilege. If the account
is used only for replication (which is recommended), you don't
need to grant any additional privileges. (For information
about setting up user accounts and privileges, see
Section 5.7, “MySQL User Account Management”.)
Suppose that your domain is mydomain.com
and you want to create an account with a username of
repl such 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 GRANT
statement:
mysql> GRANT REPLICATION SLAVE ON *.*
-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
For MySQL versions older than 4.0.2, the REPLICATION
SLAVE privilege does not exist. Grant the
FILE privilege instead:
mysql> GRANT FILE ON *.*
-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
If you plan to use the LOAD TABLE FROM
MASTER or LOAD DATA FROM MASTER
statements from the slave host, you need to grant this account
additional privileges:
Grant the account the SUPER and
RELOAD global privileges.
Grant the SELECT privilege for all
tables that you want to load. Any master tables from which
the account cannot SELECT are ignored
by LOAD DATA FROM MASTER.
Flush all the tables and block write statements by executing a
FLUSH TABLES WITH READ LOCK statement:
mysql> FLUSH TABLES WITH READ LOCK;
For InnoDB tables, note the following:
FLUSH TABLES WITH READ LOCK blocks
COMMIT operations, too. (This is true as of
MySQL version 4.0.20.) When you have acquired your global read
lock, you can start a filesystem snapshot of your
InnoDB tables. Internally (inside the
InnoDB storage engine) the snapshot won't
be consistent (because the InnoDB caches
are not flushed), but there's no need to worry at all, because
InnoDB will resolve this at startup, and consequently deliver
a consistent result. This means that InnoDB
will perform a crash recovery when started on this snapshot,
but it will not be corrupted. If you want to have a consistent
snapshot of your InnoDB tables, there's no
way around taking down the MySQL server, though.
Leave running the client from which you issue the
FLUSH TABLES statement 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
tar on Unix, or
PowerArchiver, WinRAR,
WinZip, or any similar software on Windows.
To use tar to 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_db
Then 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.tar
You may not want to replicate the mysql
database 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
LOCK is 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 File column shows the name of the log,
while Position shows the offset. In this
example, the binary log value is
mysql-bin.003 and the offset is 73. Record
the values. You 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 InnoDB tables, ideally you
should use the InnoDB Hot
Backup tool, which takes a consistent snapshot
without acquiring any locks on the master server, and records
the log name and offset corresponding to the snapshot to be
later used on the slave. Hot Backup is an
additional non-free (commercial) tool that is not included in
the standard MySQL distribution. See the
InnoDB Hot Backup home
page at http://www.innodb.com/manual.php for
detailed information.
Without the Hot Backup tool, the quickest
way to take a binary snapshot of InnoDB
tables is to shut down the master server and copy the
InnoDB data 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 STATUS as 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 shutdown
An alternative that works for both MyISAM
and InnoDB tables 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-data on 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-bin enabled, the log name and position
values displayed by SHOW MASTER STATUS or
mysqldump --master-data are empty. In that
case, the values that you need to use later when specifying
the slave's log file and position are the empty string
('') and 4.
Make sure that the [mysqld] section of the
my.cnf file on the master host includes a
log-bin option. The section should also
have a server-id=master_id option, where
master_id must be a positive integer value
from 1 to 232 – 1. For
example:
[mysqld]
log-bin=mysql-bin
server-id=1
If those options are not present, add them and restart the
server.
Stop the server that is to be used as a slave server and add
the following to its my.cnf file:
[mysqld]
server-id=slave_id
The slave_id value, like the
master_id value, must be a positive integer
value from 1 to 232 – 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=2
If you are setting up multiple slaves, each one must have a
unique server-id value that differs from
that of the master and from each of the other slaves. Think of
server-id values as something similar to IP
addresses: These IDs uniquely identify each server instance in
the community of replication partners.
If you do not specify a server-id value, it
is set to 1 if you have not defined
master-host; otherwise it is set to 2. Note
that in the case of server-id omission, a
master refuses connections from all slaves, and a slave
refuses to connect to a master. Thus, omitting
server-id is good only for backup with a
binary log.
If you made a binary backup of the master server's data, copy
it to the slave server's data directory before starting the
slave. Make sure that the privileges on the files and
directories are correct. The user that the server MySQL runs
as must able to read and write the files, just as on the
master.
If you made a backup using mysqldump, start
the slave first (see next step).
Start the slave server. If it has been replicating previously,
start the slave server with the
--skip-slave-start option so that it doesn't
immediately try to connect to its master. You also may want to
start the slave server with the
--log-warnings option (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.
If you made a backup of the master server's data using
mysqldump, load the dump file into the
slave server:
shell> mysql -u root -p < dump_file.sql
Execute the following statement on the slave, replacing the
option values with the actual values relevant to your system:
The following table shows the maximum length for the string
options:
MASTER_HOST
60
MASTER_USER
16
MASTER_PASSWORD
32
MASTER_LOG_FILE
255
Start the slave threads:
mysql> START SLAVE;
After you have performed this procedure, the slave should connect
to the master and catch up on any updates that have occurred since
the snapshot was taken.
If you have forgotten to set the server-id
value for the master, slaves are not able to connect to it.
If you have forgotten to set the server-id
value for the slave, you get the following error in the slave's
error log:
Warning: You should set server-id to a non-0 value if master_host is set;
we will force server id to 2, but this MySQL server will not act as a slave.
You also find error messages in the slave's error log if it is not
able to replicate for any other reason.
Once a slave is replicating, you can find in its data directory
one file named master.info and another named
relay-log.info. The slave uses these two
files to keep track of how much of the master's binary log it has
processed. Do not remove or edit these files
unless you know exactly what you are doing and fully understand
the implications. Even in that case, it is preferred that you use
the CHANGE MASTER TO statement.
Note: The content of
master.info overrides some of the 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.
Note: For the greatest possible durability and consistency in a
replication setup using InnoDB with
transactions you should use
innodb_flush_log_at_trx_commit=1,
sync-binlog=1, and
innodb_safe_binlog in your master
my.cnf file.
6.5. Replication Compatibility Between MySQL Versions
The original binary log format was developed in MySQL 3.23. It was
changed in MySQL 4.0.
Note: You cannot replicate from a
master that uses a newer binary log format to a slave that uses an
older format (for example, from MySQL 4.1 to MySQL 3.23.) This has
significant consequences for upgrading servers in a replication
setup, as described in Section 6.6, “Upgrading a Replication Setup”.
As far as replication is concerned, the binary log format for any
MySQL 4.1.x version and any 4.0.x version is identical. However,
replication from a 4.1 master to a 4.0 slave is unsupported, has
not been tested thoroughly, and no further development or bug
fixing is planned for this master/slave combination. Although the
binary log format is the same for 4.0 and 4.1, there are other
constraints, such as SQL-level compatibility issues. For example,
a 4.1 master cannot replicate to a 4.0 slave if the replicated
statements use SQL features available in 4.1 but not 4.0. These
and other issues are discussed in
Section 6.7, “Replication Features and Known Problems”.
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. We recommend upgrading masters and slaves running alpha
or beta versions to new versions.
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.
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
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 unfortunately confuses the 4.0 or 4.1 slaves.
Block all updates on the master by issuing a FLUSH
TABLES WITH READ LOCK statement.
Wait until all the slaves have caught up with all changes
from the master server. Use SHOW MASTER
STATUS on 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 blocks on the slave and returns
when the slave has caught up. Then run STOP
SLAVE on the slave.
Stop the master server and upgrade it to MySQL 4.0 or 4.1.
Restart the master server and record the name of its newly
created binary log. You can obtain the name of the file by
issuing a SHOW MASTER STATUS statement 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;
6.7. Replication Features and Known Problems
In general, replication compatibility at the SQL level requires
that any features used be supported by both the master and the
slave servers. For example, the GROUP_CONCAT()
function is available in MySQL 4.1 and up. If you use this
function on the master server, you cannot replicate to a slave
server that is older than MySQL 4.1.
The following list provides details about what is supported and
what is not. Additional InnoDB-specific
information about replication is given in
Section 15.7.5, “InnoDB and MySQL Replication”.
Replication is done correctly with
AUTO_INCREMENT,
LAST_INSERT_ID(), and
TIMESTAMP values.
The USER(), UUID(), and
LOAD_FILE() functions are replicated
without changes and thus do not work reliably on the slave.
This is also true for CONNECTION_ID() in
slave versions older than 4.1.1. The
newPASSWORD() 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-password option, 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 scenario.)
The following restriction applies to statement-based
replication only, not to row-based replication. The
functions handling user-level locks:
GET_LOCK(),
RELEASE_LOCK(),
IS_FREE_LOCK(),
IS_USED_LOCK() are replicated without the
slave knowing the concurrency context on master; so these
functions should not be used to insert into a master's table
as the content on slave would differ (i.e. do not do
INSERT INTO mytable VALUES(GET_LOCK(...))).
The FOREIGN_KEY_CHECKS variable is
replicated as of MySQL 4.0.14. The
SQL_MODE, UNIQUE_CHECKS,
SQL_AUTO_IS_NULL, and
TABLE_TYPE (same as
STORAGE_ENGINE) variables are not
replicated in MySQL 4.1 or earlier versions.
The following applies to replication between MySQL servers
using different character sets:
You must always use the
same global character set
and collation (--default-character-set,
--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.
If the master is older than MySQL 4.1.3, then the
character set of the session should never be made
different from its global value (in other words, do not
use SET NAMES, SET CHARACTER
SET, and so on) because this character set
change is not known to the slave. If both the master and
the slave are 4.1.3 or newer, the session can freely set
local values for character set variables (such as
NAMES, CHARACTER
SET, COLLATION_CLIENT, and
COLLATION_SERVER) as these settings are
written to the binary log and so known to the slave.
However, the session is prevented from changing the
global value of these; as
stated previously, the master and slave must always have
identical global character set values.
If on the master you have databases with different
character sets from the global
collation_server value, you should
design your CREATE TABLE statements so
that they do not implicitly rely on the default database's
character set, because there currently is a bug (Bug
#2326); a good workaround is to state the character set
and collation explicitly in CREATE
TABLE.
For both master and slave the same system time zone should be
set. Otherwise some statements, for example statements using
NOW() or FROM_UNIXTIME()
functions, won't be replicated properly. One could set the
time zone in which MySQL server runs by using the
--timezone=timezone_name
option of the mysqld_safe script or by
setting the TZ environment variable. Also
starting from version 4.1.3 both master and slave should have
the same default connection time zone set, that is the
--default-time-zone parameter should have the
same value for both master and slave.
CONVERT_TZ(...,...,@global.time_zone) is
not properly replicated.
Session variables are not replicated properly when used in
statements which update tables; for example: SET
MAX_JOIN_SIZE=1000; INSERT INTO mytable
VALUES(@MAX_JOIN_SIZE); will not insert the same
data on the master and on the slave.
It is possible to replicate transactional tables on the master
using non-transactional tables on the slave. For example, you
can replicate an InnoDB master table as a
MyISAM slave table. However, if you do
this, there are problems if the slave is stopped in the middle
of a BEGIN/COMMIT block,
because the slave restarts at the beginning of the
BEGIN block. This issue is on our TODO and
will be fixed in the near future.
Update statements that refer to user variables (that is,
variables of the form
@var_name) are
badly replicated in 3.23 and 4.0. This is fixed in 4.1.
The slave can connect to the master using SSL if both are
4.1.1 or newer.
Starting from MySQL 4.1.11, there is a global system variable
slave_transaction_retries: If the
replication slave SQL thread fails to execute a transaction
because of an InnoDB deadlock or exceeded
InnoDB's innodb_lock_wait_timeout or
NDBCluster's
TransactionDeadlockDetectionTimeout or
TransactionInactiveTimeout, it
automatically retries
slave_transaction_retries times before
stopping with an error. The default value is 0 in MySQL 4.1.
Starting from MySQL 4.1.11, the total count of retries can be
seen in SHOW STATUS; see
Section 5.2.4, “Server Status Variables”.
If a DATA DIRECTORY or INDEX
DIRECTORY clause is used in a CREATE
TABLE statement 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 sql_mode
option called NO_DIR_IN_CREATE. If the
slave server is run with its SQL mode set to include this
option, it ignores the clauses before replicating the
CREATE TABLE statement. The result is that
the MyISAM data and index files are created
in the table's database directory.
The following restriction applies to statement-based
replication only, not to row-based replication: It
is possible for the data on the master and slave to become
different if a query is designed in such a way that the data
modification is non-deterministic; that is, left to the will
of the query optimizer. (This is in general not a good
practice, even outside of replication.) For a detailed
explanation of this issue, see Section A.8.4, “Open Issues in MySQL”.
If on the master a LOAD DATA INFILE is
interrupted (for example, by a integrity constraint violation
or a killed connection), the slave skips this LOAD
DATA INFILE entirely. This means that if this
command permanently inserted or updated table records before
being interrupted, these modifications are
not replicated to the slave.
Before MySQL 4.1.1, FLUSH, ANALYZE
TABLE, OPTIMIZE TABLE, and
REPAIR TABLE statements 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 mysql database and update those
tables directly without using the GRANT
statement, you must issue a FLUSH
PRIVILEGES statement on your slaves to put the new
privileges into effect. Also if you use FLUSH
TABLES when renaming a MyISAM
table that is part of a MERGE table, you
have to issue FLUSH TABLES manually 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
LOGS, FLUSH MASTER,
FLUSH SLAVE, and FLUSH TABLES WITH
READ LOCK are not logged in any case. (Any of these
may cause problems if replicated to a slave.) For a syntax
example, see Section 13.5.5.2, “FLUSH Syntax”.
MySQL 4.1 and earlier support only replication scenarios
involving one master and many slaves.
When a server shuts down and restarts, its
MEMORY (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
MEMORY table after startup, it notifies
slaves that the table needs to be emptied by writing a
DELETE FROM statement for that table to the
binary log. See Section 14.3, “The MEMORY (HEAP) Storage Engine” for
more information.
Temporary tables are replicated except in the case where you
shut down the slave server (not just the slave threads) and
you have replicated temporary tables that are used in updates
that have not yet been executed on the slave. If you shut down
the slave server, the temporary tables needed by those updates
are no longer available when the slave is restarted. To avoid
this problem, do not shut down the slave while it has
temporary tables open. Instead, use the following procedure:
Issue a STOP SLAVE statement.
Use SHOW STATUS to check the value of
the Slave_open_temp_tables variable.
If the value is 0, issue a mysqladmin
shutdown command to shut down the slave.
If the value is not 0, restart the slave threads with
START SLAVE.
Repeat the procedure later to see if you have better luck
next time.
We plan to fix this problem in the near future.
It is safe to connect servers in a circular master/slave
relationship with the --log-slave-updates
option specified. Note, however, that many statements do 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 -> A
Server IDs are encoded in binary log events, so server A knows
when an event that it reads was originally created by itself
and does not execute the event (unless server A was started
with the --replicate-same-server-id option,
which is meaningful only in rare cases). Thus, there are no
infinite loops. This type of circular setup works 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.
If a statement on the slave produces an error, the slave SQL
thread terminates, and the slave writes a message to its error
log. You should then connect to the slave manually, fix the
problem (for example, a non-existent table), and then run
START SLAVE.
It is safe to shut down a master server and restart it later.
If a slave loses its connection to the master, the slave tries
to reconnect immediately. If that fails, the slave retries
periodically. (The default is to retry every 60 seconds. This
may be changed with the
--master-connect-retry option.) The slave
also is able to deal with network connectivity outages.
However, the slave does notice the network outage only after
receiving no data from the master for
slave_net_timeout seconds. If your outages
are short, you may want to decrease
slave_net_timeout. See
Section 5.2.3, “Server System Variables”.
Shutting down the slave (cleanly) is also safe, as it keeps
track of where it left off. Unclean shutdowns might produce
problems, especially if disk cache was not flushed to disk
before the system went down. Your system fault tolerance is
greatly increased if you have a good uninterruptible power
supply. Unclean shutdowns of the master may cause
inconsistencies between the content of tables and the binary
log in master; this can be avoided by using
InnoDB tables and the
--innodb-safe-binlog option on the master.
See Section 5.10.4, “The Binary Log”.
Due to the non-transactional nature of
MyISAM tables, it is possible to have a
statement that only partially updates a table and returns an
error code. This can happen, for example, on a multiple-row
insert that has one row violating a key constraint, or if a
long update statement is killed after updating some of the
rows. If that happens on the master, the slave thread exits
and waits for the database administrator to decide what to do
about it unless the error code is legitimate and 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-errors option. This option is
available starting with MySQL 3.23.47.
If you update transactional tables from non-transactional
tables inside a
BEGIN/COMMIT sequence,
updates to the binary log may be out of sync if the
non-transactional table is updated before the transaction
commits. This is because the transaction is written to the
binary log only when it is committed.
Before version 4.0.15, any update to a non-transactional table
is written to the binary log at once when the update is made,
whereas transactional updates are written on
COMMIT or 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
is written immediately after it is done.
When a 4.x slave replicates a LOAD DATA
INFILE from a 3.23 master, the values of the
Exec_Master_Log_Pos and
Relay_Log_Space columns of SHOW
SLAVE STATUS become incorrect. The inaccuracy in
Exec_Master_Log_Pos causes problems when
you stop and restart replication; so it is a good idea to
correct the value before this, by doing FLUSH
LOGS on the master.
The following table lists replication problems in MySQL 3.23 that
are fixed in MySQL 4.0:
LOAD DATA INFILE is handled properly, as
long as the data file still resides on the master server at
the time of update propagation.
LOAD DATA LOCAL INFILE is no longer skipped
on the slave as it was in 3.23.
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 RAND().
6.8. Replication Startup Options
On both the master and the slave, you must use the
server-id option to establish a unique
replication ID for each server. You should pick a unique positive
integer in the range from 1 to 232
– 1 for each master and slave. Example:
server-id=3
The options that you can use on the master server for controlling
binary logging are described in Section 5.10.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:
--master-host
--master-user
--master-password
--master-port
--master-connect-retry
As of MySQL 4.1.1, the following options also are handled
specially:
--master-ssl
--master-ssl-ca
--master-ssl-capath
--master-ssl-cert
--master-ssl-cipher
--master-ssl-key
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 the values for those options that are
specified in option files or on the command line. This occurs when
you start the server as a replication slave for the very first
time, or when you have run RESET SLAVE and then
have shut down and restarted the slave.
If the master.info file exists when the slave
server starts, the server 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 running.
Suppose that you specify this option in your
my.cnf file:
[mysqld]
master-host=some_host
The first time you start the server as a replication slave, it
reads and uses that option from the my.cnf
file. The server then records the value in the
master.info file. The next time you start the
server, it reads the master host value from the
master.info file only and ignores the value
in the option file. If you modify the my.cnf
file to specify a different master host of
some_other_host, the change still has no
effect. You should use CHANGE MASTER TO
instead.
Because the server gives an existing
master.info file precedence over the startup
options just described, you might prefer not to use startup
options for these values at all, and instead specify them by using
the CHANGE MASTER TO statement. See
Section 13.6.2.1, “CHANGE MASTER TO Syntax”.
This example shows a more extensive use of startup options to
configure a slave server:
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 --replicate-*
options, can be set only when the slave server starts. We plan to
fix this.
--log-slave-updates
Normally, updates received from a master server by a slave are
not logged to its binary log. This option tells the slave to
log the updates performed by its SQL thread to the slave's own
binary log. For this option to have any effect, the slave must
also be started with the --log-bin option to
enable binary logging. --log-slave-updates is
used when you want to chain replication servers. For example,
you might want a setup like this:
A -> B -> C
That 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-bin to enable binary
logging, and B with the --log-slave-updates
option.
--log-warnings
Makes the slave print more messages to the error log about
what it is doing. For example, it warns you that it succeeded
in reconnecting after a network/connection failure, and warns
you about how each slave thread started. This option is
enabled by default as of MySQL 4.0.19 and 4.1.2; to disable
it, use --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.
Note that the effects of this option are not limited to
replication. It produces warnings across a spectrum of server
activities.
--master-connect-retry=seconds
The number of seconds the slave thread sleeps before retrying
to connect to the master in case the master goes down or the
connection is lost. The value in the
master.info file takes precedence if it
can be read. If not set, the default is 60.
--master-host=host
The hostname or IP number of the master replication server. If
this option is not given, the slave thread does not start. The
value in master.info takes precedence if
it can be read.
--master-info-file=file_name
The name to use for the file in which the slave records
information about the master. The default name is
mysql.info in the data directory.
--master-password=password
The password of the account that the slave thread uses for
authentication when connecting to the master. The value in the
master.info file takes precedence if it
can be read. If not set, an empty password is assumed.
--master-port=port_number
The TCP/IP port the master is listening on. The value in the
master.info file takes precedence if it
can be read. If not set, the compiled-in setting is assumed.
If you have not tinkered with configure
options, this should be 3306.
These options are used for setting up a secure replication
connection to the master server using SSL. Their meanings are
the same as the corresponding --ssl,
--ssl-ca, --ssl-capath,
--ssl-cert, --ssl-cipher,
--ssl-key options described in
Section 5.7.7.5, “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.
--master-user=username
The username of the account that the slave thread uses for
authentication when connecting to the master. The account must
have the REPLICATION SLAVE privilege.
(Prior to MySQL 4.0.2, it must have the
FILE privilege instead.) The value in the
master.info file takes precedence if it
can be read. If the master user is not set, user
test is assumed.
This option causes the slave to allow no updates except from
slave threads or from users with the SUPER
privilege. 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.
--relay-log=file_name
The name for the relay log. The default name is
host_name-relay-bin.nnnnnn,
where host_name is the name of the
slave server host and nnnnnn
indicates that relay logs are created in numbered sequence.
You can specify the option to create hostname-independent
relay log names, or if your relay logs tend to be big (and you
don't want to decrease max_relay_log_size)
and you need to put them in some area different from the data
directory, or if you want to increase speed by balancing load
between disks.
--relay-log-index=file_name
The location and name that should be used for the relay log
index file. The default name is
host_name-relay-bin.index,
where host_name is the name of the
slave server.
--relay-log-info-file=file_name
The name to use for the file in which the slave records
information about the relay logs. The default name is
relay-log.info in the data directory.
--relay-log-purge={0|1}
Disables or enables automatic purging of relay logs as soon as
they are not needed any more. The default value is 1
(enabled). This is a global variable that can be changed
dynamically with SET GLOBAL
relay_log_purge.
This option is available as of MySQL 4.1.1.
--relay-log-space-limit=size
Places an upper limit on the total size of all relay logs on
the slave (a value of 0 means “unlimited”). This
is useful for a slave server host that has limited disk space.
When the limit is reached, the I/O thread stops reading binary
log events from the master server until the SQL thread has
caught up and deleted some unused relay logs. Note that this
limit is not absolute: There are cases where the SQL thread
needs more events before it can delete relay logs. In that
case, the I/O thread exceeds the limit until it becomes
possible for the SQL thread to delete some relay logs. Not
doing so would cause a deadlock (which is what happens before
MySQL 4.0.13). You should not set
--relay-log-space-limit to less than twice
the value of --max-relay-log-size (or
--max-binlog-size if
--max-relay-log-size is 0). In that case,
there is a chance that the I/O thread waits for free space
because --relay-log-space-limit is exceeded,
but the SQL thread has no relay log to purge and is unable to
satisfy the I/O thread. This forces the I/O thread to
temporarily ignore --relay-log-space-limit.
--replicate-do-db=db_name
Tells the slave to restrict replication to statements where
the default database (that is, the one selected by
USE) is db_name.
To specify more than one database, use this option multiple
times, once for each database. Note that this does not
replicate cross-database statements such as UPDATE
some_db.some_table SET
foo='bar' while having selected a different database
or no database. 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=sales
and you issue the following statements on the master, the
UPDATE statement is
not replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
If you need cross-database updates to work, use
--replicate-wild-do-table=db_name.%
instead.
The main reason for this
“check-just-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 DELETE or multiple-table
UPDATE statements that go across multiple
databases). It is also faster to check only the default
database rather than all databases if there is no need.
--replicate-do-table=db_name.tbl_name
Tells the slave thread to restrict replication to the
specified table. To specify more than one table, use this
option multiple times, once for each table. This works for
cross-database updates, in contrast to
--replicate-do-db. Please read the notes that
follow this option list.
--replicate-ignore-db=db_name
Tells the slave to not replicate any statement where the
default database (that is, the one selected by
USE) is db_name.
To specify more than one database to ignore, use this option
multiple times, once for each database. You should not use
this option if you are using cross-database updates and you do
not want these updates to be replicated. 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=sales and you issue the
following statements on the master, the
UPDATE statement is
not replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
If you need cross-database updates to work, use
--replicate-wild-ignore-table=db_name.%
instead.
--replicate-ignore-table=db_name.tbl_name
Tells the slave thread to not replicate any statement that
updates the specified table (even if any other tables might be
updated by the same statement). To specify more than one table
to ignore, use this option multiple times, once for each
table. This works for cross-database updates, in contrast to
--replicate-ignore-db. Please read the notes
that follow this option list.
--replicate-wild-do-table=db_name.tbl_name
Tells the slave thread to restrict replication to statements
where any of the updated tables match the specified database
and table name patterns. Patterns can contain the
‘%’ and
‘_’ wildcard characters, which
have the same meaning as for the LIKE
pattern-matching operator. To specify more than one table, use
this option multiple times, once for each table. This works
for cross-database updates. Please read the notes that follow
this option list.
Example: --replicate-wild-do-table=foo%.bar%
replicates only updates that use a table where the database
name starts with foo and the table name
starts with bar.
If the table name pattern is %, it matches
any table name and the option also applies to database-level
statements (CREATE DATABASE, DROP
DATABASE, and ALTER DATABASE).
For example, if you use
--replicate-wild-do-table=foo%.%,
database-level statements are replicated if the database name
matches the pattern foo%.
To include literal wildcard characters in the database or
table name patterns, escape them with a backslash. For
example, to replicate all tables of a database that is named
my_own%db, but not replicate tables from
the my1ownAABCdb database, you should
escape the ‘_’ and
‘%’ characters like this:
--replicate-wild-do-table=my\_own\%db. If
you're using the option on the command line, you might need to
double the backslashes or quote the option value, depending on
your command interpreter. For example, with the
bash shell, you would need to type
--replicate-wild-do-table=my\\_own\\%db.
--replicate-wild-ignore-table=db_name.tbl_name
Tells the slave thread to not replicate a statement where any
table matches the given wildcard pattern. To specify more than
one table to ignore, use this option multiple times, once for
each table. This works for cross-database updates. Please read
the notes that follow this option list.
Example:
--replicate-wild-ignore-table=foo%.bar% does
not replicate updates that use a table where the database name
starts with foo and the table name starts
with bar.
For information about how matching works, see the description
of the --replicate-wild-do-table option. The
rules for including literal wildcard characters in the option
value are the same as for
--replicate-wild-ignore-table as well.
--replicate-rewrite-db=from_name->to_name
Tells the slave to translate the default database (that is,
the one selected by USE) to
to_name if it was
from_name on the master. Only
statements involving tables are affected (not statements such
as CREATE DATABASE, DROP
DATABASE, and ALTER DATABASE),
and only if from_name was the
default database on the master. This does 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:
To be used on slave servers. Usually you can should the
default setting of 0, to prevent infinite loops in circular
replication. If set to 1, this slave does not skip events
having its own server id; normally this is useful only in rare
configurations. Cannot be set to 1 if
--log-slave-updates is used. 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-id in 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.
--report-host=slave_name
The hostname or IP number of the slave to be reported to the
master during slave registration. This value appears in the
output of SHOW SLAVE HOSTS on the master
server. Leave the value unset if you do not want the slave to
register itself with the master. Note that it is not
sufficient for the master to simply read the IP number of the
slave from the TCP/IP socket after the slave connects. Due to
NAT and other routing issues, that IP may
not be valid for connecting to the slave from the master or
other hosts.
This option is available as of MySQL 4.0.0.
--report-port=slave_port
The TCP/IP port number for connecting to the slave, to be
reported to the master during slave registration. Set it only
if the slave is listening on a non-default port or if you have
a special tunnel from the master or other clients to the
slave. If you are not sure, leave this option unset.
This option is available as of MySQL 4.0.0.
--skip-slave-start
Tells the slave server not to start the slave threads when the
server starts. To start the threads later, use a
START SLAVE statement.
--slave_compressed_protocol={0|1}
If this option is set to 1, use compression for the
slave/master protocol if both the slave and the master support
it.
--slave-load-tmpdir=file_name
The name of the directory where the slave creates temporary
files. This option is by default equal to the value of the
tmpdir system variable. When the slave SQL
thread replicates a LOAD DATA INFILE
statement, it extracts the 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 are huge, too. Therefore, it might be advisable to
use this option to tell the slave to put temporary files in a
directory located in some filesystem that has a lot of
available space. In that case, you may also use the
--relay-log option to place the relay logs in
that filesystem, because the relay logs are huge as well.
--slave-load-tmpdir should point to a
disk-based filesystem, not a memory-based one: The slave needs
the temporary files used to replicate LOAD DATA
INFILE to 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-net-timeout=seconds
The number of seconds to wait for more data from the master
before aborting the read, considering the connection broken,
and trying to reconnect. The first retry occurs immediately
after the timeout. The interval between retries is controlled
by the --master-connect-retry option.
Normally, replication stops when an error occurs, which gives
you the opportunity to resolve the inconsistency in the data
manually. This option tells the slave SQL thread to continue
replication when a statement returns any of the errors listed
in the option value.
Do not use this option unless you fully understand why you are
getting errors. If there are no bugs in your replication setup
and client programs, and no bugs in MySQL itself, an error
that stops replication should never occur. Indiscriminate use
of this option results in slaves becoming hopelessly out of
sync with the master, with you having no idea why this has
occurred.
For error codes, you should use the numbers provided by the
error message in your slave error log and in the output of
SHOW SLAVE STATUS. The server error codes
are listed in Appendix B, Error Codes and Messages.
You can also (but should not) use the very non-recommended
value of all which ignores all error
messages and keeps going regardless of what happens. Needless
to say, if you use it, we make no guarantees regarding
integrity the integrity of your data. Please do not complain
(or file bug reports) in this case if the slave's data is not
anywhere close to what it is on the master. You have
been warned.
The slave server evaluates the --replicate-*
rules as follows to determine whether to execute or ignore a
statement:
Are there any --replicate-do-db or
--replicate-ignore-db rules?
Yes: Test them as for
--binlog-do-db and
--binlog-ignore-db (see
Section 5.10.4, “The Binary Log”). What is the result of the
test?
Ignore the statement: Ignore it and exit.
Permit the statement: Do not execute the statement
immediately. Defer the decision; proceed to the next
step.
No: Go to the next step.
Are there any --replicate-*-table rules?
No: Execute the query and exit.
Yes: Proceed to the next step and
begin evaluating the table rules in the order shown (first
the non-wild rules, and then the wild rules). Only tables
that are to be updated are compared to the rules
(INSERT INTO sales SELECT * FROM
prices: only sales is
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 made,
the second table is compared to the rules, and so on.
Are there any --replicate-do-table rules?
Yes: Does the table match any of
them?
Yes: Execute the query and exit.
No: Proceed to the next step.
No: Proceed to the next step.
Are there any --replicate-ignore-table rules?
Yes: Does the table match any of
them?
Yes: Ignore the query and exit.
No: Proceed to the next step.
No: Proceed to the next step.
Are there any --replicate-wild-do-table
rules?
Yes: Does the table match any of
them?
Yes: Execute the query and exit.
No: Proceed to the next step.
No: Proceed to the next step.
Are there any --replicate-wild-ignore-table
rules?
Yes: Does the table match any of
them?
Yes: Ignore the query and exit.
No: Proceed to the next step.
No: Proceed to the next step.
No --replicate-*-table rule was matched. Is
there another table to test against these rules?
Yes: Loop.
No: We have now tested all tables to
be updated and could not match any rule. Are there
--replicate-do-table or
--replicate-wild-do-table rules?
Yes: There were “do”
rules but no match. Ignore the query and exit.
No: Execute the query and exit.
6.9. Replication FAQ
Q: How do I configure a slave if
the master is 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 output of SHOW
MASTER STATUS) corresponding to the snapshot, use the
following procedure:
Make sure that the slave is assigned a unique server ID.
Execute the following statement on the slave, filling in
appropriate values for each option:
