|
|
Table of Contents
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 replication problems.
For a description of the syntax of replication-related SQL statements, see Section 13.6, “Replication Statements”.
MySQL features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster (see Chapter 15, MySQL Cluster).
In single-master replication, the master server writes updates to its binary log files and maintains an index of those files to keep track of log rotation. The binary log files serve as a record of updates to be sent to any slave servers. When a slave connects to its master, it informs the master of the position up to which the slave read the logs at its last successful update. The slave receives any updates that have taken place since that time, 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.
Multiple-master replication is possible, but raises issues not present in single-master replication. See Section 6.13, “Auto-Increment in Multiple-Master Replication”.
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.
Replication offers 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
synchrony. 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 database backups using a slave server without disturbing the master. The master continues to process updates while the backup is being made. See Section 5.10.1, “Database Backups”.
MySQL replication is based on the master server keeping track of all changes to your databases (updates, deletes, and so on) in its binary logs. Therefore, to use replication, you must enable binary logging on the master server. See Section 5.12.3, “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. However,
LOAD DATA FROM MASTER
works only if all the
tables on the master use the MyISAM
storage
engine. In addition, 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. Although 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
--master-connect-retry
option controls the retry
interval. The default is 60 seconds.
Each slave keeps track of where it left off when it last read from its master server. The master has no knowledge of how many slaves it has or which ones are up to date at any given time.
MySQL replication capabilities are implemented using three threads
(one on the master server and two on the slave). When a
START SLAVE
statement is issued on a slave
server, the slave creates an I/O thread, which connects to the
master and asks it to send the updates 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
the updates that the master Binlog Dump
thread
sends and copies them to local files, known as relay
logs, in the slave's data directory. The third thread
is the SQL thread, which the slave creates to read the relay logs
and to execute the updates they contain.
In the preceding description, there are three threads per master/slave connection. A master that has multiple slaves creates one thread for each currently-connected slave, and each slave has its own I/O and SQL threads.
The slave uses two threads so that reading updates from the master and executing them can be separated into two independent tasks. Thus, 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. 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 enables the master server to purge its binary logs sooner 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 the output from SHOW
PROCESSLIST
.
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 Binlog Dump
replication
thread for a connected slave. The State
information indicates that all outstanding updates have been sent
to the slave and that the master is waiting for more updates to
occur. If you see no Binlog Dump
threads on a
master server, this means that replication is not running —
that is, that no slaves are currently connected.
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.
The value in the Time
column can show how late
the slave is compared to the master. See
Section 6.10, “Replication FAQ”.
The following list shows the most common states you may see in
the State
column for the master's
Binlog Dump
thread. If you see no
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.
The following list shows the most common states you see in the
State
column for a slave server I/O thread.
This state also appears in the Slave_IO_State
column displayed by SHOW SLAVE STATUS
, so you
can get a good view of what is happening by using that
statement.
Connecting to master
The thread is attempting to connect to the master.
Checking master version
A state that occurs very briefly, after the connection to the master is established.
Registering slave on master
A state that occurs very briefly after the connection to the master is established.
Requesting binlog dump
A state that occurs very briefly, 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 makes 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 large enough 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
A state that occurs briefly as the thread is stopping.
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.
By default, relay logs filenames have the form
,
where host_name
-relay-bin.nnnnnn
host_name
is the name of the
slave server host and nnnnnn
is a
sequence number. Successive relay log files are created using
successive sequence numbers, beginning with
000001
. The slave uses an index file to track
the relay log files currently in use. The default relay log
index filename is
.
By default, the slave server creates relay log files in its data
directory. The default filenames can be overridden with the
host_name
-relay-bin.index--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. The SQL thread
automatically deletes each relay log file as soon as it has
executed all events in the file and no longer needs it. There is
no explicit mechanism for deleting relay logs because the SQL
thread takes care of doing so. However, FLUSH
LOGS
rotates relay logs, which influences when the SQL
thread deletes them.
A slave server creates a new relay log file under the following conditions:
Each time the I/O thread starts.
When the logs are flushed; for example, with FLUSH
LOGS
or mysqladmin flush-logs.
When the size of the current relay log file becomes too large. The meaning of “too large” is determined as follows:
If the value of max_relay_log_size
is
greater than 0, that is the maximum relay log file size.
If the value of max_relay_log_size
is
0, max_binlog_size
determines the
maximum relay log file size.
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. Their names can
be changed by using the --master-info-file
and
--relay-log-info-file
options. See
Section 6.8, “Replication Startup Options”.
The two status files contain information like that shown in the
output of the SHOW SLAVE STATUS
statement,
which is discussed in Section 13.6.2, “SQL Statements for Controlling Slave Servers”.
Because the status files are stored on disk, they survive a
slave server's shutdown. The next time the slave starts up, it
reads the two files to determine how far it has proceeded in
reading binary logs from the master and in processing its own
relay logs.
The I/O thread updates the master.info
file. The following table shows the correspondence between the
lines in the file and the columns displayed by SHOW
SLAVE STATUS
.
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 SQL thread updates the relay-log.info
file. The following table shows the correspondence between the
lines in the file and the columns displayed by SHOW
SLAVE STATUS
.
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
status 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. If this option is
not specified, the directory location is the value of the
tmpdir
system variable.
This section briefly describes how to set up complete replication of a MySQL server. It assumes that you want to replicate all databases on the master and have not previously configured replication. You must 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 repeat it to set up multiple slaves.
Although 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 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.10, “Replication FAQ”.
If you want to administer a MySQL replication setup, we suggest that you read this entire chapter through and try all statements mentioned in Section 13.6.1, “SQL Statements for Controlling Master Servers”, and Section 13.6.2, “SQL Statements for Controlling Slave Servers”. You should also familiarize yourself with the replication startup options described in Section 6.8, “Replication Startup Options”.
Note: This procedure and some of
the replication SQL statements shown in later sections require the
SUPER
privilege.
Make sure that the versions of MySQL installed on the master and slave are compatible according to the table shown in Section 6.5, “Replication Compatibility Between MySQL Versions”. Ideally, you should use the most recent version of MySQL on both master and slave.
If you encounter a problem, please do not report it as a bug 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.
Suppose that your domain is mydomain.com
and that 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, use this GRANT
statement:
mysql>GRANT REPLICATION SLAVE 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 must 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
will be
ignored by LOAD DATA FROM MASTER
.
For additional information about setting up user accounts and privileges, see Section 5.9, “MySQL User Account Management”.
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 that FLUSH
TABLES WITH READ LOCK
also blocks
COMMIT
operations. When you have acquired a
global read lock, you can start a filesystem snapshot of your
InnoDB
tables. Internally (inside the
InnoDB
storage engine) the snapshot won't
be consistent (because the InnoDB
caches
are not flushed), but this is not a cause for concern, because
InnoDB
resolves this at startup and
delivers a consistent result. This means that
InnoDB
can perform crash recovery when
started on this snapshot, without corruption. However, there
is no way to stop the MySQL server while insuring a consistent
snapshot of your InnoDB
tables.
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
and Position
shows the offset within the
file. In this example, the binary log file is
mysql-bin.003
and the offset is 73. Record
these values. You need them later when you are setting up the
slave. They represent the replication coordinates at which the
slave should begin processing new updates from the master.
If the master has been running previously without binary
logging enabled, the log name and position values displayed by
SHOW MASTER STATUS
or mysqldump
--master-data will be empty. In that case, the
values that you need to use later when specifying the slave's
log file and position are the empty string
(''
) and 4
.
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
format 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.
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=
option, where master_id
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. The server cannot act as a replication master unless binary logging is enabled.
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, before MySQL 5.0.3,
innodb_safe_binlog
, in the master
my.cnf
file.
(innodb_safe_binlog
is not needed from
5.0.3 on.)
Stop the server that is to be used as a slave and add the
following lines 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 necessary 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 system account that you use to run the slave server must be able to read and write the files, just as on the master.
If you made a backup using mysqldump, start the slave first. The dump file is loaded in a later 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 to get more messages in
the error log about problems (for example, network or
connection problems). The option is enabled by default, but
aborted connections are not logged to the error log unless the
option 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:
mysql>CHANGE MASTER TO
->MASTER_HOST='
->master_host_name
',MASTER_USER='
->replication_user_name
',MASTER_PASSWORD='
->replication_password
',MASTER_LOG_FILE='
->recorded_log_file_name
',MASTER_LOG_POS=
recorded_log_position
;
The following table shows the maximum allowable length for the string-valued options:
MASTER_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
option for the master, slaves cannot connect to it.
If you have forgotten to set the server-id
option for the slave, you get the following error in the slave's
error log:
Warning: You should set server-id to a non-0 value if master_host is set; we will force server id to 2, but this MySQL server will not act as a slave.
You also find error messages in the slave's error log if it is not able to replicate for any other reason.
Once a slave is replicating, you can find in its data directory
one file named master.info
and another named
relay-log.info
. The slave uses these two
files to keep track of how much of the master's binary log it has
processed. Do not remove or edit these files
unless you know exactly what you are doing and fully understand
the implications. Even in that case, it is preferred that you use
the CHANGE MASTER TO
statement to change
replication parameters. The slave will use the values specified in
the statement to update the status files automatically.
Note: The content of
master.info
overrides some of the server
options specified on the command line or in
my.cnf
. See
Section 6.8, “Replication Startup Options”, for more details.
Once you have a snapshot of the master, you can use it to set up other slaves by following the slave portion of the procedure just described. You do not need to take another snapshot of the master; you can use the same one for each slave.
The binary log format as implemented in MySQL 5.0 is
considerably different from that used in previous versions. Major
changes were made in MySQL 5.0.3 (for improvements to handling of
character sets and LOAD DATA INFILE
) and 5.0.4
(for improvements to handling of time zones).
We recommend using the most recent MySQL version available 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 (production) versions. Replication from a 5.0.3 master to a 5.0.2 slave will fail; from a 5.0.4 master to a 5.0.3 slave will also fail. In general, slaves running MySQL 5.0.x can be used with older masters (even those running MySQL 3.23, 4.0, or 4.1), but not the reverse.
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 5.0 to MySQL 4.1.) This has significant implications for upgrading replication servers, as described in Section 6.6, “Upgrading a Replication Setup”.
The preceding information pertains to replication compatibility at the protocol level. However, there can be other constraints, such as SQL-level compatibility issues. For example, a 5.0 master cannot replicate to a 4.1 slave if the replicated statements use SQL features available in 5.0 but not in 4.1. These and other issues are discussed in Section 6.7, “Replication Features and Known Problems”.
When you upgrade servers that participate in a replication setup, the procedure for upgrading depends on the current server versions and the version to which you are upgrading.
This section applies to upgrading replication from MySQL 3.23, 4.0, or 4.1 to MySQL 5.0. A 4.0 server should be 4.0.3 or newer.
When you upgrade a master to 5.0 from an earlier MySQL release series, you should first ensure that all the slaves of this master are using the same 5.0.x release. If this is not the case, you should first upgrade the slaves. To upgrade each slave, shut it down, upgrade it to the appropriate 5.0.x version, restart it, and restart replication. The 5.0 slave is able to read the old relay logs written prior to the upgrade and to execute the statements they contain. Relay logs created by the slave after the upgrade are in 5.0 format.
After the slaves have been upgraded, shut down the master, upgrade it to the same 5.0.x release as the slaves, and restart it. The 5.0 master is able to read the old binary logs written prior to the upgrade and to send them to the 5.0 slaves. The slaves recognize the old format and handle it properly. Binary logs created by the master following the upgrade are in 5.0 format. These too are recognized by the 5.0 slaves.
In other words, there are no measures to take when upgrading to MySQL 5.0, except that the slaves must be MySQL 5.0 before you can upgrade the master to 5.0. Note that downgrading from 5.0 to older versions does not work so simply: You must ensure that any 5.0 binary logs or relay logs have been fully processed, so that you can remove them before proceeding with the downgrade.
In general, replication compatibility at the SQL level requires
that any features used be supported by both the master and the
slave servers. If you use a feature on a master server that is
available only as of a given version of MySQL, you cannot
replicate to a slave that is older than that version. Such
incompatibilities are likely to occur between series, so that, for
example, you cannot replicate from MySQL 5.0 to
4.1. However, these incompatibilities also can occur
for within-series replication. For example, the
SLEEP()
function is available in MySQL 5.0.12
and up. If you use this function on the master server, you cannot
replicate to a slave server that is older than MySQL 5.0.12.
If you are planning to use replication between 5.0 and a previous version of MySQL you should consult the edition of the MySQL Reference Manual corresponding to the earlier release series for information regarding the replication characteristics of that series.
The following list provides details about what is supported and
what is not. Additional InnoDB
-specific
information about replication is given in
Section 14.2.6.5, “InnoDB
and MySQL Replication”.
Replication issues with regard to stored routines and triggers is described in Section 17.4, “Binary Logging of Stored Routines and Triggers”.
Known issue: In MySQL 5.0.17,
the syntax for CREATE TRIGGER
changed to
include a DEFINER
clause for specifying
which access privileges to check at trigger invocation time.
(See Section 18.1, “CREATE TRIGGER
Syntax”, for more information.)
However, if you attempt to replicate from a master server
older than MySQL 5.0.17 to a slave running MySQL 5.0.17
through 5.0.19, replication of CREATE
TRIGGER
statements fails on the slave with a
Definer not fully qualified
error. A
workaround is to create triggers on the master using a
version-specific comment embedded in each CREATE
TRIGGER
statement:
CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER ... ;
CREATE TRIGGER
statements written this way
will replicate to newer slaves, which pick up the
DEFINER
clause from the comment and execute
successfully.
This slave problem is fixed as of MySQL 5.0.20.
Replication of AUTO_INCREMENT
,
LAST_INSERT_ID()
, and
TIMESTAMP
values is done correctly.
The USER()
, UUID()
, and
LOAD_FILE()
functions are replicated
without change and thus do not work reliably on the slave.
User privileges are replicated only if the
mysql
database is replicated. That is, the
GRANT
, REVOKE
,
SET PASSWORD
, CREATE
USER
, and DROP USER
statements
take effect on the slave only if the replication setup
includes the mysql
database.
If you're replicating all databases, but don't want statements
that affect user privileges to be replicated, set up the slave
to not replicate the mysql
database, using
the --replicate-wild-ignore-table=mysql.%
option. The slave will recognize that issuing
privilege-related SQL statements won't have an effect, and
thus not execute those statements.
The GET_LOCK()
,
RELEASE_LOCK()
,
IS_FREE_LOCK()
, and
IS_USED_LOCK()
functions that handle
user-level locks are replicated without the slave knowing the
concurrency context on master. Therefore, these functions
should not be used to insert into a master's table because the
content on the slave would differ. (For example, do not issue
a statement such as INSERT INTO mytable
VALUES(GET_LOCK(...))
.)
The FOREIGN_KEY_CHECKS
,
SQL_MODE
, UNIQUE_CHECKS
,
and SQL_AUTO_IS_NULL
variables are all
replicated in MySQL 5.0. The
storage_engine
system variable (also known
as table_type
) is not yet replicated, which
is a good thing for replication between different storage
engines.
Starting from MySQL 5.0.3 (master and slave), replication works even if the master and slave have different global character set variables. Starting from MySQL 5.0.4 (master and slave), replication works even if the master and slave have different global time zone variables.
The following applies to replication between MySQL servers that use different character sets:
If the master uses MySQL 4.1, you must
always use the same
global character set and collation on
the master and the slave, regardless of the MySQL version
running on the slave. (These are controlled by the
--character-set-server
and
--collation-server
options.) Otherwise,
you may get duplicate-key errors on the slave, because a
key that is unique in the master character set might not
be unique in the slave character set. Note that this is
not a cause for concern when master and slave are both
MySQL 5.0 or later.
If the master is older than MySQL 4.1.3, the character set
of any client should never be made different from its
global value because this character set change is not
known to the slave. In other words, clients should not use
SET NAMES
, SET CHARACTER
SET
, and so forth. If both the master and the
slave are 4.1.3 or newer, clients can freely set session
values for character set variables because these settings
are written to the binary log and so are known to the
slave. That is, clients can use SET
NAMES
or SET CHARACTER SET
or
can set variables such as
collation_client
or
collation_server
. However, clients are
prevented from changing the global
value of these variables; as stated previously, the master
and slave must always have identical global character set
values.
If you have databases on the master with character sets
that differ from the global
character_set_server
value, you should
design your CREATE TABLE
statements so
that tables in those databases do not implicitly rely on
the database default character set (see Bug #2326). A good
workaround is to state the character set and collation
explicitly in CREATE TABLE
statements.
If the master uses MySQL 4.1, the same system time zone should
be set for both master and slave. Otherwise some statements
will not be replicated properly, such as statements that use
the NOW()
or
FROM_UNIXTIME()
functions. You can set the
time zone in which MySQL server runs by using the
--timezone=
option of the timezone_name
mysqld_safe
script or by
setting the TZ
environment variable. Both
master and slave should also have the same default connection
time zone setting; that is, the
--default-time-zone
parameter should have the
same value for both master and slave. Note that this is not
necessary when the master is MySQL 5.0 or later.
CONVERT_TZ(...,...,@@global.time_zone)
is
not properly replicated.
CONVERT_TZ(...,...,@@session.time_zone)
is
properly replicated only if the master and slave are from
MySQL 5.0.4 or newer.
Session variables are not replicated properly when used in
statements that update tables. For example, SET
MAX_JOIN_SIZE=1000
followed by INSERT INTO
mytable VALUES(@@MAX_JOIN_SIZE)
will not insert the
same data on the master and the slave. This does not apply to
the common sequence of SET TIME_ZONE=...
followed by INSERT INTO mytable
VALUES(CONVERT_TZ(...,...,@@time_zone))
, which
replicates correctly as of MySQL 5.0.4.
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.
Update statements that refer to user-defined variables (that
is, variables of the form
@
) are
replicated correctly in MySQL 5.0. However, this
is not true for versions prior to 4.1. Note that user variable
names are case insensitive starting in MySQL 5.0. You should
take this into account when setting up replication between
MySQL 5.0 and older versions.
var_name
Slaves can connect to masters using SSL.
In MySQL 5.0 (starting from 5.0.3), 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 because it
exceeded the InnoDB
innodb_lock_wait_timeout
or the NDBCluster
TransactionDeadlockDetectionTimeout
or
TransactionInactiveTimeout
value, the
transaction automatically retries
slave_transaction_retries
times before
stopping with an error. The default value is 10. Starting from
MySQL 5.0.4, the total retry count can be seen in the output
of SHOW STATUS
; see
Section 5.2.4, “Server Status Variables”.
If a DATA DIRECTORY
or INDEX
DIRECTORY
table option is used in a CREATE
TABLE
statement on the master server, the table
option is also used on the slave. This can cause problems if
no corresponding directory exists in the slave host filesystem
or if it exists but is not accessible to the slave server.
MySQL supports an sql_mode
option called
NO_DIR_IN_CREATE
. If the slave server is
run with this SQL mode enabled, it ignores the DATA
DIRECTORY
and INDEX DIRECTORY
table options when replicating CREATE TABLE
statements. The result is that MyISAM
data
and index files are created in the table's database directory.
It is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is non-deterministic; that is, 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.1, “Open Issues in MySQL”.
The following applies only if either the master or
the slave is running MySQL version 5.0.3 or older:
If on the master a LOAD DATA INFILE
is
interrupted (integrity constraint violation, killed
connection, and so on), the slave skips the 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.
Some forms of the FLUSH
statement are not
logged because they could cause problems if replicated to a
slave: FLUSH LOGS
, FLUSH
MASTER
, FLUSH SLAVE
, and
FLUSH TABLES WITH READ LOCK
. For a syntax
example, see Section 13.5.5.2, “FLUSH
Syntax”. The FLUSH
TABLES
, ANALYZE TABLE
,
OPTIMIZE TABLE
, and REPAIR
TABLE
statements are written to the binary log and
thus replicated to slaves. This is not normally a problem
because these statements do not modify table data. However,
this can cause difficulties under certain circumstances. If
you replicate the privilege tables in the
mysql
database and update those tables
directly without using GRANT
, you must
issue a FLUSH PRIVILEGES
on the slaves to
put the new privileges into effect. In addition, if you use
FLUSH TABLES
when renaming a
MyISAM
table that is part of a
MERGE
table, you must issue FLUSH
TABLES
manually on the slaves. These statements are
written to the binary log unless you specify
NO_WRITE_TO_BINLOG
or its alias
LOCAL
.
MySQL supports only one master and many slaves. In the future
we plan to add a voting algorithm for changing the master
automatically in the event of problems with the current
master. We also plan to introduce agent processes to help
perform load balancing by sending SELECT
queries to different slaves.
When a server shuts down and restarts, its
MEMORY
(HEAP
tables
become empty. The master replicates this effect to slaves as
follows: The first time that the master uses each
MEMORY
table after startup, it logs an
event that notifies the slaves that the table needs to be
emptied by writing a DELETE
statement for
that table to the binary log. See
Section 14.4, “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 stop the slave.
If the value is not 0, restart the slave threads with
START SLAVE
.
Repeat the procedure later until the
Slave_open_temp_tables
variable is 0
and you can stop the slave.
The syntax for multiple-table DELETE
statements that use table aliases changed between MySQL 4.0
and 4.1. In MySQL 4.0, you should use the true table name to
refer to any table from which rows should be deleted:
DELETE test FROM test AS t1, test2 WHERE ...
In MySQL 4.1, you must use the alias:
DELETE t1 FROM test AS t1, test2 WHERE ...
If you use such DELETE
statements, the
change in syntax means that a 4.0 master cannot replicate to
4.1 (or higher) slaves.
It is safe to connect servers in a circular master/slave
relationship if you use the
--log-slave-updates
option. That means that
you can create a setup such as this:
A -> B -> C -> A
However, 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.
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 a slave produces an error, the slave SQL
thread terminates, and the slave writes a message to its error
log. You should then connect to the slave manually and
determine the cause of the problem. (SHOW SLAVE
STATUS
is useful for this.) Then fix the problem
(for example, you might need to create a non-existent table)
and run START SLAVE
.
It is safe to shut down a master server and restart it later.
When a slave loses its connection to the master, the slave
tries to reconnect immediately and retries periodically if
that fails. The default is to retry every 60 seconds. This may
be changed with the --master-connect-retry
option. A slave also is able to deal with network connectivity
outages. However, the slave notices the network outage only
after receiving no data from the master for
slave_net_timeout
seconds. If your outages
are short, you may want to decrease
slave_net_timeout
. See
Section 5.2.2, “Server System Variables”.
Shutting down the slave (cleanly) is also safe because it
keeps track of where it left off. Unclean shutdowns might
produce problems, especially if the disk cache was not flushed
to disk before the system went down. Your system fault
tolerance is greatly increased if you have a good
uninterruptible power supply. Unclean shutdowns of the master
may cause inconsistencies between the content of tables and
the binary log in master; this can be avoided by using
InnoDB
tables and the
--innodb-safe-binlog
option on the master.
See Section 5.12.3, “The Binary Log”.
Note:
--innodb-safe-binlog
is unneeded as of MySQL
5.0.3, having been made obsolete by the introduction of XA
transaction support.
Due to the non-transactional nature of
MyISAM
tables, it is possible to have a
statement that only partially updates a table and returns an
error code. This can happen, for example, on a multiple-row
insert that has one row violating a key constraint, or if a
long update statement is killed after updating some of the
rows. If that happens on the master, the slave thread exits
and waits for the database administrator to decide what to do
about it unless the error code is legitimate and execution of
the statement results in the same error code on the slave. If
this error code validation behavior is not desirable, some or
all errors can be masked out (ignored) with the
--slave-skip-errors
option.
If you update transactional tables from non-transactional
tables inside a
BEGIN
/COMMIT
sequence,
updates to the binary log may be out of synchrony with table
states if the non-transactional table is updated before the
transaction commits. This occurs because the transaction is
written to the binary log only when it is committed.
In situations where transactions mix updates to transactional
and non-transactional tables, the order of statements in the
binary log is correct, and all needed statements are written
to the binary log even in case of a
ROLLBACK
. However, when a second connection
updates the non-transactional table before the first
connection's transaction is complete, statements can be logged
out of order, because the second connection's update is
written immediately after it is performed, regardless of the
state of the transaction being performed by the first
connection.
This section describes the options that you can use on slave replication servers. You can specify these options either on the command line or in an option file.
On the master and each slave, you must use the
server-id
option to establish a unique
replication ID. For each server, you should pick a unique positive
integer in the range from 1 to 232
– 1, and each ID must be different from every other ID.
Example: server-id=3
Options that you can use on the master server for controlling binary logging are described in Section 5.12.3, “The Binary Log”.
Some slave server replication options are handled in a special
way, in the sense that each is ignored if a
master.info
file exists when the slave starts
and contains a value for the option. The following options are
handled this way:
--master-host
--master-user
--master-password
--master-port
--master-connect-retry
--master-ssl
--master-ssl-ca
--master-ssl-capath
--master-ssl-cert
--master-ssl-cipher
--master-ssl-key
The master.info
file format in MySQL
5.0 includes values corresponding to the SSL options.
In addition, the file format includes as its first line the number
of lines in the file. (See Section 6.3.4, “Replication Relay and Status Files”.) If you
upgrade an older server (before MySQL 4.1.1) to a newer version,
the new server upgrades the master.info
file
to the new format automatically when it starts. However, if you
downgrade a newer server to an older version, you should remove
the first line manually before starting the older server for the
first time.
If no master.info
file exists when the slave
server starts, it uses the values for those options that are
specified in option files or on the command line. This occurs when
you start the server as a replication slave for the very first
time, or when you have run RESET SLAVE
and then
have shut down and restarted the slave.
If the master.info
file exists when the slave
server starts, the server uses its contents and ignores any
options that correspond to the values listed in the file. Thus, if
you start the slave server with different values of the startup
options that correspond to values in the
master.info
file, the different values have
no effect, because the server continues to use the
master.info
file. To use different values,
you must either restart after removing the
master.info
file or (preferably) use the
CHANGE MASTER TO
statement to reset the values
while the slave is running.
Suppose that you specify this option in your
my.cnf
file:
[mysqld]
master-host=some_host
The first time you start the server as a replication slave, it
reads and uses that option from the my.cnf
file. The server then records the value in the
master.info
file. The next time you start the
server, it reads the master host value from the
master.info
file only and ignores the value
in the option file. If you modify the my.cnf
file to specify a different master host of
some_other_host
, the change still has
no effect. You should use CHANGE MASTER TO
instead.
Because the server gives an existing
master.info
file precedence over the startup
options just described, you might prefer not to use startup
options for these values at all, and instead specify them by using
the CHANGE MASTER TO
statement. See
Section 13.6.2.1, “CHANGE MASTER TO
Syntax”.
This example shows a more extensive use of startup options to configure a slave server:
[mysqld] server-id=2 master-host=db-master.mycompany.com master-port=3306 master-user=pertinax master-password=freitag master-connect-retry=60 report-host=db-slave.mycompany.com
The following list describes 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.
Normally, a slave does not log to its own binary log any
updates that are received from a master server. This option
tells the slave to log the updates performed by its SQL thread
to its own binary log. For this option to have any effect, the
slave must also be started with the --log-bin
option to enable binary logging.
--log-slave-updates
is used when you want to
chain replication servers. For example, you might want to set
up replication servers using this arrangement:
A -> B -> C
Here, A serves as the master for the slave B, and B serves as
the master for the slave C. For this to work, B must be both a
master and a slave. You must start both A
and B with --log-bin
to enable binary
logging, and B with the --log-slave-updates
option so that updates received from A are logged by B to its
binary log.
This option causes a server to print more messages to the
error log about what it is doing. With respect to replication,
the server generates warnings that it succeeded in
reconnecting after a network/connection failure, and informs
you as to how each slave thread started. This option is
enabled by default; to disable it, use
--skip-log-warnings
. Aborted connections are
not logged to the error log unless the value is greater than
1.
--master-connect-retry=
seconds
The number of seconds that the slave thread sleeps before
trying to reconnect to the master in case the master goes down
or the connection is lost. The value in the
master.info
file takes precedence if it
can be read. If not set, the default is 60.
The hostname or IP number of the master replication server.
The value in master.info
takes precedence
if it can be read. If no master host is specified, the slave
thread does not start.
The name to use for the file in which the slave records
information about the master. The default name is
master.info
in the data directory.
The password of the account that the slave thread uses for
authentication when it connects to the master. The value in
the master.info
file takes precedence if
it can be read. If not set, an empty password is assumed.
The TCP/IP port number that the master is listening on. The
value in the master.info
file takes
precedence if it can be read. If not set, the compiled-in
setting is assumed (normally 3306).
The number of times that the slave tries to connect to the master before giving up.
--master-ssl
,
--master-ssl-ca=
,
file_name
--master-ssl-capath=
,
directory_name
--master-ssl-cert=
,
file_name
--master-ssl-cipher=
,
cipher_list
--master-ssl-key=
file_name
These options are used for setting up a secure replication
connection to the master server using SSL. Their meanings are
the same as the corresponding --ssl
,
--ssl-ca
, --ssl-capath
,
--ssl-cert
, --ssl-cipher
,
--ssl-key
options that are described in
Section 5.9.7.5, “SSL Command Options”. The values in the
master.info
file take precedence if they
can be read.
The username of the account that the slave thread uses for
authentication when it connects to the master. This account
must have the REPLICATION SLAVE
privilege.
The value in the master.info
file takes
precedence if it can be read. If the master username is not
set, the name test
is assumed.
The size at which the server rotates relay log files automatically. For more information, see Section 6.3.4, “Replication Relay and Status Files”.
Cause the slave to allow no updates except from slave threads
or from users having the SUPER
privilege.
This enables you to ensure that a slave server accepts no
updates from clients. As of MySQL 5.0.16, this option does not
apply to TEMPORARY
tables.
The name for the relay log. The default name is
,
where host_name
-relay-bin.nnnnnn
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.
The name to use for the relay log index file. The default name
is
in the data directory, where
host_name
-relay-bin.indexhost_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.
Disable or enable automatic purging of relay logs as soon as
they are not needed any more. The default value is 1
(enabled). This is a global variable that can be changed
dynamically with SET GLOBAL relay_log_purge =
.
N
This option places an upper limit on the total size in bytes
of all relay logs on the slave. A value of 0 means “no
limit.” This is useful for a slave server host that has
limited disk space. When the limit is reached, the I/O thread
stops reading binary log events from the master server until
the SQL thread has caught up and deleted some unused relay
logs. Note that this limit is not absolute: There are cases
where the SQL thread needs more events before it can delete
relay logs. In that case, the I/O thread exceeds the limit
until it becomes possible for the SQL thread to delete some
relay logs, because not doing so would cause a deadlock. You
should not set --relay-log-space-limit
to
less than twice the value of
--max-relay-log-size
(or
--max-binlog-size
if
--max-relay-log-size
is 0). In that case,
there is a chance that the I/O thread waits for free space
because --relay-log-space-limit
is exceeded,
but the SQL thread has no relay log to purge and is unable to
satisfy the I/O thread. This forces the I/O thread to
temporarily ignore --relay-log-space-limit
.
Tell the slave to restrict replication to statements where the
default database (that is, the one selected by
USE
) is db_name
.
To specify more than one database, use this option multiple
times, once for each database. Note that this does not
replicate cross-database statements such as UPDATE
while having selected a different database
or no database.
some_db.some_table
SET
foo='bar'
An example of what does not work as you might expect: If the
slave is started with --replicate-do-db=sales
and you issue the following statements on the master, the
UPDATE
statement is
not replicated:
USE prices; UPDATE sales.january SET amount=amount+1000;
The main reason for this “just check the default
database” behavior is that it is difficult from the
statement alone to know whether it should be replicated (for
example, if you are using multiple-table
DELETE
statements or multiple-table
UPDATE
statements that act across multiple
databases). It is also faster to check only the default
database rather than all databases if there is no need.
If you need cross-database updates to work, use
--replicate-wild-do-table=
instead. See Section 6.9, “How Servers Evaluate Replication Rules”.
db_name
.%
--replicate-do-table=
db_name.tbl_name
Tell the slave thread to restrict replication to the specified
table. To specify more than one table, use this option
multiple times, once for each table. This works for
cross-database updates, in contrast to
--replicate-do-db
. See
Section 6.9, “How Servers Evaluate Replication Rules”.
Tells the slave to not replicate any statement where the
default database (that is, the one selected by
USE
) is db_name
.
To specify more than one database to ignore, use this option
multiple times, once for each database. You should not use
this option if you are using cross-database updates and you do
not want these updates to be replicated. See
Section 6.9, “How Servers Evaluate Replication Rules”.
An example of what does not work as you might expect: If the
slave is started with
--replicate-ignore-db=sales
and you issue the
following statements on the master, the
UPDATE
statement is
not replicated:
USE prices; UPDATE sales.january SET amount=amount+1000;
If you need cross-database updates to work, use
--replicate-wild-ignore-table=
instead. See Section 6.9, “How Servers Evaluate Replication Rules”.
db_name
.%
--replicate-ignore-table=
db_name.tbl_name
Tells the slave thread to not replicate any statement that
updates the specified table, even if any other tables might be
updated by the same statement. To specify more than one table
to ignore, use this option multiple times, once for each
table. This works for cross-database updates, in contrast to
--replicate-ignore-db
. See
Section 6.9, “How Servers Evaluate Replication Rules”.
--replicate-rewrite-db=
from_name
->to_name
Tells the slave to translate the default database (that is,
the one selected by USE
) to
to_name
if it was
from_name
on the master. Only
statements involving tables are affected (not statements such
as CREATE DATABASE
, DROP
DATABASE
, and ALTER DATABASE
),
and only if from_name
is the
default database on the master. This does not work for
cross-database updates. The database name translation is done
before the --replicate-*
rules are tested.
If you use this option on the command line and the
‘>
’ character is special to
your command interpreter, quote the option value. For example:
shell> mysqld --replicate-rewrite-db="olddb
->newdb
"
To be used on slave servers. Usually you should use the
default setting of 0, to prevent infinite loops caused by
circular replication. If set to 1, the slave does not skip
events having its own server ID. Normally, this is useful only
in rare configurations. Cannot be set to 1 if
--log-slave-updates
is used. Note that by
default the slave I/O thread does not even write binary log
events to the relay log if they have the slave's server id
(this optimization helps save disk usage). So if you want to
use --replicate-same-server-id
, be sure to
start the slave with this option before you make the slave
read its own events that you want the slave SQL thread to
execute.
--replicate-wild-do-table=
db_name.tbl_name
Tells the slave thread to restrict replication to statements
where any of the updated tables match the specified database
and table name patterns. Patterns can contain the
‘%
’ and
‘_
’ wildcard characters, which
have the same meaning as for the LIKE
pattern-matching operator. To specify more than one table, use
this option multiple times, once for each table. This works
for cross-database updates. See
Section 6.9, “How Servers Evaluate Replication Rules”.
Example: --replicate-wild-do-table=foo%.bar%
replicates only updates that use a table where the database
name starts with foo
and the table name
starts with bar
.
If the table name pattern is %
, it matches
any table name and the option also applies to database-level
statements (CREATE DATABASE
, DROP
DATABASE
, and ALTER DATABASE
).
For example, if you use
--replicate-wild-do-table=foo%.%
,
database-level statements are replicated if the database name
matches the pattern foo%
.
To include literal wildcard characters in the database or
table name patterns, escape them with a backslash. For
example, to replicate all tables of a database that is named
my_own%db
, but not replicate tables from
the my1ownAABCdb
database, you should
escape the ‘_
’ and
‘%
’ characters like this:
--replicate-wild-do-table=my\_own\%db
. If
you're using the option on the command line, you might need to
double the backslashes or quote the option value, depending on
your command interpreter. For example, with the
bash shell, you would need to type
--replicate-wild-do-table=my\\_own\\%db
.
--replicate-wild-ignore-table=
db_name.tbl_name
Tells the slave thread not to replicate a statement where any table matches the given wildcard pattern. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates. See Section 6.9, “How Servers Evaluate Replication Rules”.
Example:
--replicate-wild-ignore-table=foo%.bar%
does
not replicate updates that use a table where the database name
starts with foo
and the table name starts
with bar
.
For information about how matching works, see the description
of the --replicate-wild-do-table
option. The
rules for including literal wildcard characters in the option
value are the same as for
--replicate-wild-ignore-table
as well.
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.
The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration. Set this only if the slave is listening on a non-default port or if you have a special tunnel from the master or other clients to the slave. If you are not sure, do not use this option.
Tells the slave server not to start the slave threads when the
server starts. To start the threads later, use a
START SLAVE
statement.
--slave_compressed_protocol={0|1}
If this option is set to 1, use compression for the slave/master protocol if both the slave and the master support it.
The name of the directory where the slave creates temporary
files. This option is by default equal to the value of the
tmpdir
system variable. When the slave SQL
thread replicates a LOAD DATA INFILE
statement, it extracts the file to be loaded from the relay
log into temporary files, and then loads these into the table.
If the file loaded on the master is huge, the temporary files
on the slave are huge, too. Therefore, it might be advisable
to use this option to tell the slave to put temporary files in
a directory located in some filesystem that has a lot of
available space. In that case, the relay logs are huge as
well, so you might also want to use the
--relay-log
option to place the relay logs in
that filesystem.
The directory specified by this option should be located in a
disk-based filesystem (not a memory-based filesystem) because
the temporary files used to replicate LOAD DATA
INFILE
must survive machine restarts. The directory
also should not be one that is cleared by the operating system
during the system startup process.
The number of seconds to wait for more data from the master
before the slave considers the connection broken, aborts the
read, and tries to reconnect. The first retry occurs
immediately after the timeout. The interval between retries is
controlled by the --master-connect-retry
option.
--slave-skip-errors=[
err_code1
,err_code2
,...|all]
Normally, replication stops when an error occurs on the slave. This gives you the opportunity to resolve the inconsistency in the data manually. This option tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the option value.
Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of synchrony with the master, with you having no idea why this has occurred.
For error codes, you should use the numbers provided by the
error message in your slave error log and in the output of
SHOW SLAVE STATUS
.
Appendix B, Error Codes and Messages, lists server error codes.
You can also (but should not) use the very non-recommended
value of all
to cause the slave to ignore
all error messages and keeps going regardless of what happens.
Needless to say, if you use all
, there are
no guarantees regarding the 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.
Examples:
--slave-skip-errors=1062,1053 --slave-skip-errors=all
If a master server does not write a statement to its binary log, the statement is not replicated. If the server does log the statement, the statement is sent to all slaves and each slave determines whether to execute it or ignore it.
On the master side, decisions about which statements to log are
based on the --binlog-do-db
and
--binlog-ignore-db
options that control binary
logging. For a description of the rules that servers use in
evaluating these options, see Section 5.12.3, “The Binary Log”.
On the slave side, decisions about whether to execute or ignore
statements received from the master are made according to the
--replicate-*
options that the slave was started
with. (See Section 6.8, “Replication Startup Options”.) The slave
evaluates these options using the following procedure, which first
checks the database-level options and then the table-level
options.
In the simplest case, when there are no
--replicate-*
options, the procedure yields the
result that the slave executes all statements that it receives
from the master. Otherwise, the result depends on the particular
options given. In general, to make it easier to determine what
effect an option set will have, it is recommended that you avoid
mixing “do” and “ignore” options, or
wildcard and non-wildcard options.
Stage 1. Check the database options.
At this stage, the slave checks whether there are any
--replicate-do-db
or
--replicate-ignore-db
options that specify
database-specific conditions:
No: Permit the statement and proceed to the table-checking stage.
Yes: Test the options using the same
rules as for the --binlog-do-db
and
--binlog-ignore-db
options to determine
whether to permit or ignore the statement. What is the result
of the test?
Permit: Do not execute the statement immediately. Defer the decision and proceed to the table-checking stage.
Ignore: Ignore the statement and exit.
This stage can permit a statement for further option-checking, or cause it to be ignored. However, statements that are permitted at this stage are not actually executed yet. Instead, they pass to the following stage that checks the table options.
Stage 2. Check the table options.
First, as a preliminary condition, the slave checks whether the
statement occurs within a stored function or (prior to MySQL
5.0.12) a stored procedure. If so, execute the statement and exit.
(Stored procedures are exempt from this test as of MySQL 5.0.12
because procedure logging occurs at the level of statements that
are executed within the routine rather than at the
CALL
level.)
Next, the slave checks for table options and evaluates them. If
the server reaches this point, it executes all statements if there
are no table options. If there are “do” table
options, the statement must match one of them if it is to be
executed; otherwise, it is ignored. If there are any
“ignore” options, all statements are executed except
those that match any ignore
option. The
following steps describe how this evaluation occurs in more
detail.
Are there any --replicate-*-table
options?
No: There are no table restrictions, so all statements match. Execute the statement and exit.
Yes: There are table restrictions.
Evaluate the tables to be updated against them. There
might be multiple tables to update, so loop through the
following steps for each table looking for a matching
option (first the non-wild options, and then the wild
options). Only tables that are to be updated are compared
to the options. For example, if the statement is
INSERT INTO sales SELECT * FROM prices
,
only sales
is compared to the options).
If several tables are to be updated (multiple-table
statement), the first table that matches “do”
or “ignore” wins. That is, the server checks
the first table against the options. If no decision could
be made, it checks the second table against the options,
and so on.
Are there any --replicate-do-table
options?
No: Proceed to the next step.
Yes: Does the table match any of them?
No: Proceed to the next step.
Yes: Execute the statement and exit.
Are there any --replicate-ignore-table
options?
No: Proceed to the next step.
Yes: Does the table match any of them?
No: Proceed to the next step.
Yes: Ignore the statement and exit.
Are there any --replicate-wild-do-table
options?
No: Proceed to the next step.
Yes: Does the table match any of them?
No: Proceed to the next step.
Yes: Execute the statement and exit.
Are there any --replicate-wild-ignore-table
options?
No: Proceed to the next step.
Yes: Does the table match any of them?
No: Proceed to the next step.
Yes: Ignore the statement and exit.
No --replicate-*-table
option was matched. Is
there another table to test against these options?
No: We have now tested all tables to
be updated and could not match any option. Are there
--replicate-do-table
or
--replicate-wild-do-table
options?
No: There were no “do” table options, so no explicit “do” match is required. Execute the statement and exit.
Yes: There were “do” table options, so the statement is executed only with an explicit match to one of them. Ignore the statement and exit.
Yes: Loop.
Examples:
No --replicate-*
options at all
The slave executes all statements that it receives from the master.
--replicate-*-db
options, but no table
options
The slave permits or ignores statements using the database options. Then it executes all statements permitted by those options because there are no table restrictions.
--replicate-*-table
options, but no database
options
All statements are permitted at the database-checking stage because there are no database conditions. The slave executes or ignores statements based on the table options.
A mix of database and table options
The slave permits or ignores statements using the database options. Then it evaluates all statements permitted by those options according to the table options. In some cases, this process can yield what might seem a counterintuitive result. Consider the following set of options:
[mysqld] replicate-do-db = db1 replicate-do-table = db2.mytbl2
Suppose that db1
is the default database
and the slave receives this statement:
INSERT INTO mytbl1 VALUES(1,2,3);
The database is db1
, which matches the
--replicate-do-db
option at the
database-checking stage. The algorithm then proceeds to the
table-checking stage. If there were no table options, the
statement would be executed. However, because the options
include a “do” table option, the statement must
match if it is to be executed. The statement does not match,
so it is ignored. (The same would happen for any table in
db1
.)
Q: How do I configure a slave if the master is running and I do not want to stop it?
A: There are several
possibilities. If you have taken a snapshot backup of the master
at some point and recorded the binary log filename 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:
mysql>CHANGE MASTER TO
->MASTER_HOST='
->master_host_name
',MASTER_USER='
->master_user_name
',MASTER_PASSWORD='
->master_pass
',MASTER_LOG_FILE='
->recorded_log_file_name
',MASTER_LOG_POS=
recorded_log_position
;
Execute START SLAVE
on the slave.
If you do not have a backup of the master server, here is a quick procedure for creating one. All steps should be performed on the master host.
Issue this statement to acquire a global read lock:
mysql> FLUSH TABLES WITH READ LOCK;
With the lock still in place, execute this command (or a variation of it):
shell> tar zcf /tmp/backup.tar.gz /var/lib/mysql
Issue this statement and record the output, which you will need later:
mysql> SHOW MASTER STATUS;
Release the lock:
mysql> UNLOCK TABLES;
An alternative to using the preceding procedure to make a binary copy is to make an SQL dump of the master. To do this, you can use mysqldump --master-data on your master and later load the SQL dump into your slave. However, this is slower than making a binary copy.
Regardless of which of the two methods you use, afterward follow the instructions for the case when you have a snapshot and have recorded the log filename and offset. You can use the same snapshot to set up several slaves. Once you have the snapshot of the master, you can wait to set up a slave as long as the binary logs of the master are left intact. The two practical limitations on the length of time you can wait are the amount of disk space available to retain binary logs on the master and the length of time it takes the slave to catch up.
You can also use LOAD DATA FROM MASTER
. This is
a convenient statement that transfers a snapshot to the slave and
adjusts the log filename and offset all at once. Be warned,
however, that it works only for MyISAM
tables
and it may hold a read lock for a long time. It is not yet
implemented as efficiently as we would like. If you have large
tables, the preferred method is still to make a binary snapshot on
the master server after executing FLUSH TABLES WITH READ
LOCK
.
Q: Does the slave need to be connected to the master all the time?
A: No, it does not. The slave can go down or stay disconnected for hours or even days, and then reconnect and catch up on 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 synchrony with the master unless you take some special measures.
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 statement replicated by the slave?
A: You can read the
Seconds_Behind_Master
column in SHOW
SLAVE STATUS
. See
Section 6.3, “Replication Implementation Details”.
When the slave SQL thread executes an event read from the master,
it modifies its own time to the event timestamp. (This is why
TIMESTAMP
is well replicated.) In the
Time
column in the output of SHOW
PROCESSLIST
, the number of seconds displayed for the
slave SQL thread is the number of seconds between the timestamp of
the last replicated event and the real time of the slave machine.
You can use this to determine the date of the last replicated
event. Note that if your slave has been disconnected from the
master for one hour, and then reconnects, you may immediately see
Time
values like 3600 for the slave SQL thread
in SHOW PROCESSLIST
. This is because the slave
is executing statements that are one hour old.
Q: How do I force the master to block updates until the slave catches up?
A: Use the following procedure:
On the master, execute these statements:
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;
Record the replication cooredinates (the log filename and
offset) from the output of the SHOW
statement.
On the slave, issue the following statement, where the
arguments to the MASTER_POS_WAIT()
function
are the replication coordinate values obtained in the previous
step:
mysql> SELECT MASTER_POS_WAIT('log_name
', log_offset
);
The SELECT
statement blocks until the slave
reaches the specified log file and offset. At that point, the
slave is in synchrony with the master and the statement
returns.
On the master, issue the following statement to allow the master to begin processing updates again:
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 makes the update of client A work differently than it did on co-master 1. Thus, when the update of client A makes it to co-master 2, it produces tables that are different from what you have on co-master 1, even after all the updates from co-master 2 have also propagated. This means that you should not chain two servers together 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 should also realize that two-way replication actually does not improve performance very much (if at all) as far as updates are concerned. Each server must do the same number of updates, just as you would have a single server do. The only difference is that there is a little less lock contention, because the updates originating on another server are serialized in one slave thread. Even this benefit might be offset by network delays.
Q: How can I use replication to improve performance of my system?
A: You should set up one server
as the master and direct all writes to it. Then configure as many
slaves as you have the budget and rackspace for, and distribute
the reads among the master and the slaves. You can also start the
slaves with the --skip-innodb
,
--skip-bdb
,
--low-priority-updates
, and
--delay-key-write=ALL
options to get speed
improvements on the slave end. In this case, the slave uses
non-transactional MyISAM
tables instead of
InnoDB
and BDB
tables to get
more speed by eliminating transactional overhead.
Q: What should I do to prepare client code in my own applications to use performance-enhancing replication?
A: If the part of your code that is responsible for database access has been properly abstracted/modularized, converting it to run with a replicated setup should be very smooth and easy. Change the implementation of your database access to send all writes to the master, and to send reads to either the master or a slave. If your code does not have this level of abstraction, setting up a replicated system gives you the opportunity and motivation to it clean up. Start by creating a wrapper library or module that implements the following functions:
safe_writer_connect()
safe_reader_connect()
safe_reader_statement()
safe_writer_statement()
safe_
in each function name means that the
function takes care of handling all 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.
Then convert your client code to use the wrapper library. This may be a painful and scary process at first, but it pays off in the long run. All applications that use the approach just described are able to take advantage of a master/slave configuration, even one involving multiple slaves. The code is much easier to maintain, and adding troubleshooting options is trivial. You need modify only one or two functions; for example, to log how long each statement took, or which statement among those issued gave you an error.
If you have written a lot of code, you may want to automate the conversion task by using the replace utility that comes with standard MySQL distributions, or write your own conversion script. Ideally, your code uses consistent programming style conventions. If not, then you are probably better off rewriting it anyway, or at least going through and manually regularizing it to use a consistent style.
Q: When and how much can MySQL replication improve the performance of my system?
A: MySQL replication is most beneficial for a system that processes 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.
To determine how many slaves you can use before the added benefits
begin to level out, and how much you can improve performance of
your site, you need to know your query patterns, and to determine
empirically by benchmarking the relationship between the
throughput for reads (reads per second, or
reads
) and for writes
(writes
) on a typical master and a typical
slave. The example here shows a rather simplified calculation of
what you can get with replication for a hypothetical system.
Let's say that system load consists of 10% writes and 90% reads,
and we have determined by benchmarking that
reads
is 1200 – 2 ×
writes
. In other words, the system can do 1,200
reads per second with no writes, the average write is twice as
slow as the average read, and the relationship is linear. Let us
suppose that the master and each slave have the same capacity, and
that we have one master and N
slaves.
Then we have for each server (master or slave):
reads = 1200 – 2 × writes
reads = 9 × writes / (
(reads are split, but writes go to all servers)
N
+ 1)
9 × writes / (
N
+ 1) + 2
× writes = 1200
writes = 1200 / (2 +
9/(
N
+1))
The last equation indicates the maximum number of writes for
N
slaves, given a maximum possible read
rate of 1,200 per minute and a ratio of nine reads per write.
This analysis yields the following conclusions:
If N
= 0 (which means we have no
replication), our system can handle about 1200/11 = 109 writes
per second.
If N
= 1, we get up to 184 writes
per second.
If N
= 8, we get up to 400 writes
per second.
If N
= 17, we get up to 480 writes
per second.
Eventually, as N
approaches
infinity (and our budget negative infinity), we can get very
close to 600 writes per second, increasing system throughput
about 5.5 times. However, with only eight servers, we increase
it nearly four times.
Note that these computations assume infinite network bandwidth and
neglect several other factors that could be significant on your
system. In many cases, you may not be able to perform a
computation similar to the one just shown that accurately predicts
what will happen on your system if you add
N
replication slaves. However,
answering the following questions should help you decide whether
and by how much replication will improve the performance of your
system:
What is the read/write ratio on your system?
How much more write load can one server handle if you reduce the reads?
For how many slaves do you have bandwidth available on your network?
Q: How can I use replication to provide redundancy or high availability?
A: With the currently available features, you would have to set up a master and a slave (or several slaves), and to write a script that monitors the master to check whether it is up. Then instruct your applications and the slaves to change master in case of failure. Some suggestions:
To tell a slave to change its master, use the CHANGE
MASTER TO
statement.
A good way to keep your applications informed as to the
location of the master is by having a dynamic DNS entry for
the master. With bind
you can use
nsupdate
to dynamically update your DNS.
Run your slaves with the --log-bin
option and
without --log-slave-updates
. In this way, the
slave is ready to become a master as soon as you issue
STOP SLAVE
; RESET
MASTER
, and CHANGE MASTER TO
statement on the other slaves. For example, assume that you
have the following setup:
WC \ v WC----> M / | \ / | \ v v v S1 S2 S3
In this diagram, M
means the master,
S
the slaves, WC
the
clients issuing database writes and reads; clients that issue
only database reads are not represented, because they need not
switch. S1
, S2
, and
S3
are slaves running with
--log-bin
and without
--log-slave-updates
. Because updates received
by a slave from the master are not logged in the binary log
unless --log-slave-updates
is specified, the
binary log on each slave is empty initially. If for some
reason M
becomes unavailable, you can pick
one of the slaves to become the new master. For example, if
you pick S1
, all WC
should be redirected to S1
, which will log
updates to its binary log. S2
and
S3
should then replicate from
S1
.
The reason for running the slave without
--log-slave-updates
is to prevent slaves from
receiving updates twice in case you cause one of the slaves to
become the new master. Suppose that S1
has
--log-slave-updates
enabled. Then it will
write updates that it receives from M
to
its own binary log. When S2
changes from
M
to S1
as its master,
it may receive updates from S1
that it has
already received from M
Make sure that all slaves have processed any statements in
their relay log. On each slave, issue STOP SLAVE
IO_THREAD
, then check the output of SHOW
PROCESSLIST
until you see Has read all
relay log
. When this is true for all slaves, they
can be reconfigured to the new setup. On the slave
S1
being promoted to become the master,
issue STOP SLAVE
and RESET
MASTER
.
On the other slaves S2
and
S3
, use STOP SLAVE
and
CHANGE MASTER TO MASTER_HOST='S1'
(where
'S1'
represents the real hostname of
S1
). To CHANGE MASTER
,
add all information about how to connect to
S1
from S2
or
S3
(user
,
password
,
port
). In CHANGE
MASTER
, there is no need to specify the name of
S1
's binary log or binary log position to
read from: We know it is the first binary log and position 4,
which are the defaults for CHANGE MASTER
.
Finally, use START SLAVE
on
S2
and S3
.
Then instruct all WC
to direct their
statements to S1
. From that point on, all
updates statements sent by WC
to
S1
are written to the binary log of
S1
, which then contains every update
statement sent to S1
since
M
died.
The result is this configuration:
WC / | WC | M(unavailable) \ | \ | v v S1<--S2 S3 ^ | +-------+
When M
is up again, you must issue on it
the same CHANGE MASTER
as that issued on
S2
and S3
, so that
M
becomes a slave of S1
and picks up all the WC
writes that it
missed while it was down. To make M
a
master again (because it is the most powerful machine, for
example), use the preceding procedure as if
S1
was unavailable and M
was to be the new master. During this procedure, do not forget
to run RESET MASTER
on M
before making S1
, S2
,
and S3
slaves of M
.
Otherwise, they may pick up old WC
writes
from before the point at which M
became
unavailable.
Note that there is no synchronization between the different slaves to a master. Some slaves might be ahead of others. This means that the concept outlined in the previous example might not work. In practice, however, the relay logs of different slaves will most likely not be far behind the master, so it would work, anyway (but there is no guarantee).
Q: Does replication work on mixed operating systems (for example, the master runs on Linux while slaves run on Mac OS X and Windows)?
A: Yes.
Q: Does replication work on mixed hardware architectures (for example, the master runs on a 64-bit machine while slaves run on 32-bit machines)?
A: Yes.
If you have followed the instructions, and your replication setup is not working, the first thing to do is check the error log for messages. Many users have lost time by not doing this soon enough after encountering problems.
If you cannot tell from the error log what the problem was, try the following techniques:
Verify that the master has binary logging enabled by issuing a
SHOW MASTER STATUS
statement. If logging is
enabled, Position
is non-zero. If binary
logging is not enabled, verify that you are running the master
with the --log-bin
and
--server-id
options.
Verify that the slave is running. Use SHOW SLAVE
STATUS
to check whether the
Slave_IO_Running
and
Slave_SQL_Running
values are both
Yes
. If not, verify the options that were
used when starting the slave server. For example,
--skip-slave-start
prevents the slave threads
from starting until you issue a START SLAVE
statement.
If the slave is running, check whether it established a
connection to the master. Use SHOW
PROCESSLIST
, find the I/O and SQL threads and check
their State
column to see what they
display. See
Section 6.3, “Replication Implementation Details”. If the
I/O thread state says Connecting to master
,
verify the privileges for the replication user on the master,
the master hostname, your DNS setup, whether the master is
actually running, and whether it is reachable from the slave.
If the slave was running previously but has stopped, the reason usually is that some statement that succeeded on the master failed on the slave. This should never happen if you have taken a proper snapshot of the master, and never modified the data on the slave outside of the slave thread. If the slave stops unexpectedly, it is a bug or you have encountered one of the known replication limitations described in Section 6.7, “Replication Features and Known Problems”. If it is a bug, see Section 6.12, “How to Report Replication Bugs or Problems”, for instructions on how to report it.
If a statement that succeeded on the master refuses to run on the slave, try the following procedure if it is not feasible to do a full database resynchronization by deleting the slave's databases and copying a new snapshot from the master:
Determine whether the affected table on the slave is
different from the master table. Try to understand how
this happened. Then make the slave's table identical to
the master's and run START SLAVE
.
If the preceding step does not work or does not apply, try to understand whether it would be safe to make the update manually (if needed) and then ignore the next statement from the master.
If you decide that you can skip the next statement from the master, issue the following statements:
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER =
mysql>N
;START SLAVE;
The value of N
should be 1 if
the next statement from the master does not use
AUTO_INCREMENT
or
LAST_INSERT_ID()
. Otherwise, the value
should be 2. The reason for using a value of 2 for
statements that use AUTO_INCREMENT
or
LAST_INSERT_ID()
is that they take two
events in the binary log of the master.
If you are sure that the slave started out perfectly synchronized with the master, and that no one has updated the tables involved outside of the slave thread, then presumably the discrepancy is the result of a bug. If you are running the most recent version of MySQL, please report the problem. If you are running an older version, try upgrading to the latest production release to determine whether the problem persists.
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 obtain as much information as possible from you to be able to track down the bug. Please spend some time and effort in preparing a good bug report.
If you have a repeatable test case that demonstrates the bug, please enter it into our bugs database using the instructions given in Section 1.8, “How to Report Bugs or Problems”. If you have a “phantom” problem (one that you cannot duplicate at will), use the following procedure:
Verify that no user error is involved. For example, if you update the slave outside of the slave thread, the data goes out of synchrony, and you can have unique key violations on updates. In this case, the slave thread stops and waits for you to clean up the tables manually to bring them into synchrony. This is not a replication problem. It is a problem of outside interference causing replication to fail.
Run the slave with the --log-slave-updates
and --log-bin
options. These options cause
the slave to log the updates that it receives from the master
into its own binary logs.
Save all evidence before resetting the replication state. If we have no information or only sketchy information, it becomes difficult or impossible for us to track down the problem. The evidence you should collect is:
All binary logs from the master
All binary logs from the slave
The output of SHOW MASTER STATUS
from
the master at the time you discovered the problem
The output of SHOW SLAVE STATUS
from
the slave at the time you discovered the problem
Error logs from the master and the slave
Use mysqlbinlog to examine the binary logs.
The following should be helpful to find the problem statement.
log_pos
and
log_file
are the
Master_Log_File
and
Read_Master_Log_Pos
values from
SHOW SLAVE STATUS
.
shell> mysqlbinlog -j log_pos
log_file
| head
After you have collected the evidence for the problem, try to isolate it as a separate test case first. Then enter the problem with as much information as possible into our bugs database using the instructions at Section 1.8, “How to Report Bugs or Problems”.
When multiple servers are configured as replication masters,
special steps must be taken to prevent key collisions when using
AUTO_INCREMENT
columns, otherwise multiple
masters may attempt to use the same
AUTO_INCREMENT
value when inserting rows.
The auto_increment_increment
and
auto_increment_offset
system variables help to
accommodate multiple-master replication with
AUTO_INCREMENT
columns. Each of these variables
has a default and minimum value of 1, and a maximum value of
65,535. They were introduced in MySQL 5.0.2.
These two variables effect AUTO_INCREMENT
column behavior as follows:
auto_increment_increment
controls the
increment between successive AUTO_INCREMENT
values.
auto_increment_offset
determines the
starting point for AUTO_INCREMENT
column
values.
By choosing non-conflicting values for these variables on
different masters, servers in a multiple-master configuration will
not use conflicting AUTO_INCREMENT
values when
inserting new rows into the same table. To set up
N
master servers, set the variables
like this:
Set auto_increment_increment
to
N
on each master.
Set each of the N
masters to have a
different auto_increment_offset
, using the
values 1, 2, …, N
.
For additional information about
auto_increment_increment
and
auto_increment_offset
, see
Section 5.2.2, “Server System Variables”.