Go to the first, previous, next, last section, table of contents.
Replication capabilities allowing the databases on one MySQL server to be
duplicated on another were introduced in MySQL version 3.23.15.
This chapter describes the various replication features provided by MySQL.
It introduces replication concepts, shows how to set up replication servers,
and serves as a reference to the available replication options.
It also provides a list of
frequently asked questions (with answers), and troubleshooting advice for
solving problems.
For a description of the syntax of replication-related SQL statements, see
section 14.6 Replication Statements.
We suggest that you visit our Web site at http://www.mysql.com
often and read updates to this chapter. Replication is constantly being
improved, and we update the manual frequently with the most current
information.
Starting in Version 3.23.15, MySQL features support for one-way replication.
One server acts as the master, while one or more other servers
act as slaves. The master server writes updates to its binary log files,
and maintains an index of the files to keep track of log rotation. These logs
serve as a record of updates to be sent to slave servers. When a
slave server connects to the master server, it informs the master of its last
position within the logs since the last successfully propagated update. Then
the slave catches up any updates that have occurred since then, and then
blocks and waits for the master to notify it of new updates.
A slave server can also serve as a master if you want to set up chained
replication servers.
Note that when you are using replication, all updates to the
tables that are replicated
should be performed on the master server. Otherwise, you must always be
careful to avoid conflicts between updates that users make to tables on the
master and updates that they make to tables on the slave.
One-way replication has benefits for robustness, speed, and system
administration:
-
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 to not get out of sync.
This load-balancing strategy is effective if non-updating queries dominate,
but that is the normal case.
-
Another benefit of using replication is that you can perform backups using a
slave server without disturbing the master. The master continues to process
updates while the backup is being made.
See section 5.6.1 Database Backups.
MySQL replication is based on the master server keeping track of all changes
to your database (updates, deletes, etc) in the binary logs. Therefore,
to use replication, you must enable binary logging on the master server.
See section 5.8.4 The Binary Log.
Each slave server receives from the master the saved updates that the
master has recorded in its binary log, so that the slave can execute the
same updates on its copy of the data.
It is very important to realize that the binary log is simply a
record starting from the fixed point in time at which you enable binary
logging. Any slaves that you set up will need copies of the databases on
your master as they existed at the moment you enabled binary
logging on the master. If you start your slaves with data that is not the same
as what was on the master when the binary log was
started, your slaves may fail.
One way to copy the master's data to the slave is to use the LOAD
DATA FROM MASTER statement. Be aware that LOAD DATA FROM MASTER
is available only as of MySQL 4.0.0 and currently works only if all the
tables on the master are MyISAM type. Also, this statement acquires a
global read lock, so no updates on the master are possible while the tables
are being transferred to the slave. When we implement lock-free hot table
backup (in MySQL 5.0), this global read lock will no longer be necessary.
Due to these limitations, we recommend that at this point you use
LOAD DATA FROM MASTER only if the dataset on the master is relatively
small, or if a prolonged read lock on the master is acceptable. While the
actual speed of LOAD DATA FROM MASTER may vary from system to system,
a good rule of thumb for how long it will take is 1 second
per 1MB of the datafile. You will get close to the estimate if both master
and slave are equivalent to 700 MHz Pentium and are connected through a
100 MBit/s network. Note that this is only a rough estimate.
After the slave has been set up with a copy of the master's data, it
will simply connect to the master and wait for updates to process. If
the master goes away or the slave loses connectivity with your master,
it will keep trying to connect periodically until it is able to reconnect
and resume listening for updates. The retry interval is controlled by the
--master-connect-retry option. The default is 60 seconds.
Each slave keeps track of where it left off. The master server has no
knowledge of how many slaves there are or which ones are up to date at
any given time.
MySQL replication capabilities are implemented using three threads (one
on the master server and two on the slave). When START SLAVE is
issued, the slave creates an I/O thread. The I/O thread connects to the
master and asks it to send the statements recorded in its binary logs. The
master creates a thread to send the binary log contents to the slave.
This thread can be identified as the Binlog Dump thread in the
output of SHOW PROCESSLIST on the master. The slave I/O thread
reads what the master Binlog Dump thread sends and simply copies
it to some local files in the slave's data directory called relay logs.
The third thread is the SQL thread, which the slave creates to read the
relay logs and execute the updates they contain.
In the preceding description there are three threads per slave. Thus, for a
master that has multiple slaves, it creates one thread for each currently
connected slave, and each slave has its own I/O and SQL threads.
For versions of MySQL before 4.0.2, replication involves only two threads
(one on the master and one on the slave). The slave I/O and SQL threads
are combined as a single thread, and no relay log files are used.
The advantage of using two slave threads is that statement reading and
execution are separated into two independent tasks. The task of reading
statements is not slowed down if statement execution is slow. For example,
if the slave server has not been running for a while, its I/O thread can
quickly fetch all the binary log contents from the master when the slave starts,
even if the SQL thread lags far behind and may take hours to catch up.
If the slave stops before the SQL thread has executed all the fetched
statements, the I/O thread has at least fetched everything so that a
safe copy of the statements is locally stored in the slave's relay logs
for execution when next the slave starts. This allows the binary logs to be
purged on the master, because it no longer need wait for the slave to
fetch their contents.
The SHOW PROCESSLIST statement provides information that tells you
what is happening on the master and on the slave regarding replication.
The following example illustrates how the three threads show up in
SHOW PROCESSLIST. The output format is that used by SHOW
PROCESSLIST as of MySQL version 4.0.15, when the content of the
State column was changed to be more meaningful compared to
earlier versions.
On the master server, the output from SHOW PROCESSLIST looks like this:
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost:32931
db: NULL
Command: Binlog Dump
Time: 94
State: Has sent all binlog to slave; waiting for binlog to
be updated
Info: NULL
Here, thread 2 is a replication thread for a connected slave. The information
indicates that all outstanding updates have been sent to the slave and that
the master is waiting for more updates to occur.
On the slave server, the output from SHOW PROCESSLIST looks like this:
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 10
User: system user
Host:
db: NULL
Command: Connect
Time: 11
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 11
User: system user
Host:
db: NULL
Command: Connect
Time: 11
State: Has read all relay log; waiting for the slave I/O
thread to update it
Info: NULL
This information indicates that thread 10 is the I/O thread that is
communicating with the master server, and thread 11 is the SQL thread that
is processing the updates stored in the relay logs. Currently, both threads
are idle, waiting for further updates.
Note that the value in the Time column can tell how late the slave
is compared to the master.
See section 6.9 Replication FAQ.
The following list shows the most common states you will see in the
State column for the master's Binlog Dump thread. If you
don't see any Binlog Dump threads on a master server, replication
is not running. That is, no slaves currently are connected.
Sending binlog event to slave
-
Binary logs consist of events, where an event is usually an update statement
plus some other information. The thread has read an event from the binary
log and is 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. It is idle, waiting for new events to appear in the
binary log resulting from new update statements being executed 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 will see in the
State column for a slave server I/O thread. Beginning with MySQL
4.1.1, this state also appears in the Slave_IO_State column displayed
by the SHOW SLAVE STATUS statement. This means that you can get a
good view of what is happening by using only SHOW SLAVE STATUS.
Connecting to master
-
The thread is attempting to connect to the master.
Checking master version
-
A very brief state that occurs just after the connection to the master
is established.
Registering slave on master
-
A very brief state that occurs just after the connection to the master
is established.
Requesting binlog dump
-
A very brief state that occurs just 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. The thread sleeps for
master-connect-retry seconds before retrying.
Reconnecting after a failed binlog dump request
-
Then the thread tries to reconnect to the master.
Waiting for master to send event
-
The thread has connected 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 will occur. At that point,
the thread will consider the connection to be broken and make an attempt
to reconnect.
Queueing master event to the relay log
-
The thread has read an event and is copying it to the relay log so 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
-
Then the thread tries to reconnect. When connection is established
again, the state will become
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 so much that their combined size exceeds this value.
The I/O thread is waiting until the SQL
thread frees enough space by processing relay log contents so that it can
delete some relay log files.
Waiting for slave mutex on exit
-
A very brief state that occurs as the thread is stopping.
The following list shows the most common states you will see in the
State column for a slave server SQL thread:
Reading event from the relay log
-
The thread has read an event from the relay log so that it can process it.
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 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 are named using filenames of the form
`host_name-relay-bin.nnn', where host_name is the name of the
slave server host, and nnn is a sequence number.
Successive relay log files are created using successive sequence numbers,
beginning with 001.
The slave keeps track of relay logs currently in use in an index file.
The default relay log index filename is
`host_name-relay-bin.index'.
By default these files are created in the slave's data directory.
The default filenames may be overridden with the --relay-log and
--relay-log-index server options.
See section 6.8 Replication Startup Options.
Relay logs have the same format as binary logs, so they can be read
with mysqlbinlog.
A relay log is automatically deleted by the SQL thread as soon as it
no longer needs it (that is, as soon as it has executed all its
events). There is no explicit mechanism for deleting relay logs, because
the SQL thread takes care of doing so. However, from MySQL 4.0.14,
FLUSH LOGS rotates relay logs, which will influence when
the SQL thread deletes them.
A new relay log is created under the following conditions:
-
When the I/O thread starts for the first time
after the slave server starts.
(In MySQL 5.0, a new relay log is created each time the I/O thread
starts, not just the first time.)
-
When the logs are flushed; for example, with
FLUSH LOGS or
mysqladmin flush-logs. (This creates a new relay log only as of MySQL
4.0.14.)
-
When
the size of the current relay log file becomes too large. The meaning of
``too large'' is determined as follows:
-
max_relay_log_size, if max_relay_log_size > 0
-
max_binlog_size, if max_relay_log_size = 0
or MySQL is older than 4.0.14
A slave replication server creates two additional small files in the
data directory. These are status files and are named `master.info'
and `relay-log.info' by default. They contain information like
that shown in the output of the SHOW SLAVE STATUS statement
(see section 14.6.2 SQL Statements for Controlling Slave Servers for a description of this statement).
As disk files they survive a slave server's shutdown. The next time the
slave starts up, it reads these files to determine how far it has proceeded
in reading binary logs from the master and in processing its own relay logs.
The `master.info' file is updated by the I/O thread.
The correspondence between the lines in the file and the
columns displayed by SHOW SLAVE STATUS is as follows:
| Line | Description
|
| 1 | Master_Log_File
|
| 2 | Read_Master_Log_Pos
|
| 3 | Master_Host
|
| 4 | Master_User
|
| 5 | Password (not shown by SHOW SLAVE STATUS)
|
| 6 | Master_Port
|
| 7 | Connect_Retry
|
The `relay-log.info' file is updated by the SQL thread.
The correspondence between the lines in the file and the
columns displayed by SHOW SLAVE STATUS is as follows:
| Line | Description
|
| 1 | Relay_Log_File
|
| 2 | Relay_Log_Pos
|
| 3 | Relay_Master_Log_File
|
| 4 | Exec_Master_Log_Pos
|
When you back up your slave's data, you should back up these two small files
as well, along with the relay log files. They are needed to resume
replication after you restore the slave's data. If you lose the relay logs
but still have the `relay-log.info' file, you can check it to determine
how far the SQL thread has executed in the master binary logs. Then you
can use CHANGE MASTER TO with the MASTER_RELAY_LOG and
MASTER_RELAY_POS options to tell the slave to re-read the binary
logs from that point. This requires that the binary logs still exist on
the master server.
If your slave is subject to replicating LOAD DATA INFILE statements,
you should also back up any `SQL_LOAD-*' files that exist in the
directory that the slave uses for this purpose. The slave needs these files
to resume replication of any interrupted LOAD DATA INFILE operations.
The directory location is specified using the --slave-load-tmpdir
option. Its default value if not specified is the value of the tmpdir
variable.
Here is a quick description of how to set up complete replication of
your current MySQL server. It assumes you want to replicate all
your databases and have not configured replication before. You will need
to shut down your master server briefly to complete the steps outlined
here.
The procedure is written in terms of setting up a single slave, but you can
use it to set up multiple slaves.
While this method is the most straightforward way to set up a slave,
it is not the only one. For example, if you already have a snapshot
of the master's data, and
the master already has its server ID set and binary logging enabled, you can
set up a slave without shutting down the master or even blocking updates to it.
For more details, please see section 6.9 Replication FAQ.
If you want to administer a MySQL replication setup, we suggest that
you read this entire chapter through and try all statements mentioned in
section 14.6.1 SQL Statements for Controlling Master Servers and section 14.6.2 SQL Statements for Controlling Slave Servers. You should
also familiarize yourself with replication startup options described in
section 6.8 Replication Startup Options.
Note that this procedure and some of the replication SQL statements
in later sections refer to the SUPER privilege. Prior to MySQL
4.0.2, use the PROCESS privilege instead.
-
Make sure you have a recent version of MySQL installed on the master
and and slaves, and that these versions are compatible according to the table
shown in section 6.5 Replication Compatibility Between MySQL Versions.
Please do not report bugs until you have verified that the problem is present
in the latest 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 you want to create an
account with a username of repl such that slave servers can use the
account to access the master server from any host in your domain using a
password of slavepass.
To create the account, this use GRANT statement:
mysql> GRANT REPLICATION SLAVE ON *.*
-> TO repl@'%.mydomain.com' IDENTIFIED BY 'slavepass;
For MySQL versions older than 4.0.2, the REPLICATION CLIENT privilege
does not exist. Grant the FILE privilege instead:
mysql> GRANT FILE ON *.*
-> TO repl@'%.mydomain.com' IDENTIFIED BY 'slavepass;
If you plan to use the LOAD TABLE FROM MASTER or LOAD DATA
FROM MASTER statements from the slave host, you will need to grant this
account additional privileges:
-
Grant the account the
SUPER and RELOAD global privileges.
-
Grant the
SELECT privilege for all tables that
you want to load. Any master tables from which the account cannot
SELECT will be ignored by LOAD DATA FROM MASTER.
-
If you are using only
MyISAM tables, flush all the tables and block
write statements by executing a FLUSH TABLES WITH READ LOCK statement.
mysql> FLUSH TABLES WITH READ LOCK;
Leave the client running 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,bar | foo,manual,mysql |
+---------------+----------+--------------+------------------+
The File column shows the name of the log, while Position shows
the offset. In this example, the binary log value is
mysql-bin.003 and the offset is 73. Record the values. You will need
to use them later when you are setting up the slave. The represent the
replication coordinates at which the slave should begin processing new updates
from the master.
After you have taken the snapshot and recorded the log name and offset,
you can re-enable write activity on the master:
mysql> UNLOCK TABLES;
If you are using InnoDB tables, ideally you should use the InnoDB Hot
Backup tool. It takes a consistent snapshot without acquiring any locks on
the master server, and records the log name and offset corresponding to the
snapshot to be later used on the slave. InnoDB Hot Backup is a non-free
additional tool which is not included in the standard MySQL distribution.
See the InnoDB Hot Backup homepage http://www.innodb.com/manual.php
for detailed information and screenshots.
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 datafiles, log files, and table definition files (.frm
files). To record the current log file name and offset, you should issue
the following statements before you shut down the server:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
Then record the log name and the offset from the output of SHOW MASTER
STATUS as was shown earlier. After recording the log name and the offset,
shut down the server without unlocking the tables to make sure the
server goes down with the snapshot corresponding to the current log file
and offset:
shell> mysqladmin -uroot shutdown
An alternative that works for both MyISAM and InnoDB
tables is to take an SQL dump of the master instead of a binary copy as
described in the preceding discussion. For this, you can use mysqldump
--master-data on your master and later load the SQL dump file into your
slave. However, this is slower than doing a binary copy.
If the master has been previously running without --log-bin enabled,
the log name and position values displayed by SHOW MASTER STATUS
or mysqldump will be empty. In that case, the values that you will
need to use later when specifying the slave's log file and position are
the empty string ('') and 4.
-
Make sure the
[mysqld] section of the `my.cnf' file on the
master host includes a log-bin option. The section should also have a
server-id=master_id option, where master_id must be a positive integer
value from 1 to 2^32 - 1. For example:
[mysqld]
log-bin
server-id=1
If those options are not present, add them and restart the server.
-
Stop the server that is to be used as a slave server and add the following to
its `my.cnf' file:
[mysqld]
server-id=slave_id
The slave_id value, like the master_id value, must
be a positive integer value from 1 to 2^32 - 1. In addition, it is very
important that the ID of the slave be different than the ID of the
master. For example:
[mysqld]
server-id=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 don't specify a server-id value, it will be set to 1 if
you have not defined master-host, else it will be set to 2. Note
that in the case of server-id omission, a master will refuse
connections from all slaves, and a slave will refuse to connect to a
master. Thus, omitting server-id is only good for backup with a
binary log.
-
If you made a binary backup of the master server's data, copy it to the
slave server's data directory before starting the slave. Make sure that the
privileges on the files and directories are correct. The user that the server
MySQL runs as must able to read and write the files, just as on the master.
If you made a backup using
mysqldump, start the slave first (see next
step).
-
Start the slave server. If it has been replicating previously,
start the slave server with the
--skip-slave-start option so that it
doesn't immediately try to connect to its master.
You also may want to start the slave server with the
--log-warnings option, to get more messages about
problems (for example, network or connection problems).
-
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 values within
<> with the actual values relevant to your system:
mysql> CHANGE MASTER TO
-> MASTER_HOST='<master hostname>',
-> MASTER_USER='<replication username>',
-> MASTER_PASSWORD='<replication password>',
-> MASTER_LOG_FILE='<recorded log file name>',
-> MASTER_LOG_POS=<recorded log offset>;
The following table shows the maximum string length for these variables:
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 server-id for the master, slaves will
not be able to connect to it.
If you have forgotten to set server-id for the slave, you will get
the following error in its error log:
Warning: one should set server_id to a non-0 value if
master_host is set. The server will not act as a slave.
You will also find error messages in the slave's error log if it
is not able to replicate for any other reason.
Once a slave is replicating, you will find in its data directory one file called
`master.info' and another called `relay-log.info'.
The slave uses these two files to keep track of how much
of the master's binary log it has processed. Do not remove or
edit these files, unless you really know what you are doing and understand
the implications. Even in that case,
it is preferred that you use the CHANGE MASTER TO statement.
Note: The content of `master.info' overrides some
options specified on the command line or in `my.cnf'
See section 6.8 Replication Startup Options for more details.
Once you have a snapshot, you can use it to set up other slaves by
following the slave portion of the procedure just described. You do not need to
take another snapshot of the master; you can use the same one for each slave.
As far as replication is concerned, any MySQL 4.1.x version and any 4.0.x
version are identical, because they all use the same binary log format.
Thus, any servers from these versions are compatible, and replication between
them should work seamlesslessly.
The exceptions to this compatibility is that versions from MySQL 4.0.0 to
4.0.2 were very early development versions that should not be used anymore.
(These were the alpha versions in the 4.0 release series. Compatibility for
them is still documented in the manual included with their distributions.)
The original binary log format was developed in MySQL 3.23. It changed in
MySQL 4.0, and again in MySQL 5.0. This has consequences when you upgrade
servers in a replication setup, as described in section 6.6 Upgrading a Replication Setup.
The following table indicates master/slave replication compatibility between
different versions of MySQL.
| | Master | Master | Master
|
| | 3.23.33 and up | 4.0.3 and up or any 4.1.x | 5.0.0
|
| Slave | 3.23.33 and up | yes | no | no
|
| Slave | 4.0.3 and up | yes | yes | no
|
| Slave | 5.0.0 | yes | yes | yes
|
As a general rule, we recommended using recent MySQL versions,
because replication capabilities are continually being improved.
We also recommend using same the version for both the master and the slave.
When you upgrade servers that participate in a replication setup, the
procedure for upgrading depends on the current server versions and the
version to which you are upgrading.
This section applies to upgrading replication from MySQL 3.23 to 4.0 or 4.1.
A 4.0 server should be 4.0.3 or newer, as mentioned in section 6.5 Replication Compatibility Between MySQL Versions.
When you upgrade a master from MySQL 3.23 to MySQL 4.0 or 4.1, you should
first ensure that all the slaves of this master are already 4.0 or 4.1. If
that is not the case, you should first upgrade your slaves: Shut down each
one, upgrade it, restart it, and restart replication.
The upgrade can safely be done using the following procedure, assuming you
have a 3.23 master to upgrade and the slaves are 4.0 or 4.1. Note that
after the master has been upgraded, you should not restart replication using
any old 3.23 binary logs, because this will unfortunately confuse the 4.0 or
4.1 slave.
-
Block all updates on the master by issuing a
FLUSH TABLES WITH READ LOCK
statement.
-
Wait until all the slaves have caught up with all changes from the master
server.
Use
SHOW MASTER STATUS on the master to obtain its current binary log
file and position. Then, for each slave, use those values with a SELECT
MASTER_POS_WAIT() statement. The statement will block on the slave and return
when the slave has caught up. Then run STOP SLAVE on the slave.
-
Stop the master server and upgrade it to MySQL 4.0 or 4.1.
-
Restart the master server and record the name `<name>' of its newly
created binary log. You can obtain the name of the file by issuing a
SHOW MASTER STATUS statement on the master. Then issue these
statements on each slave:
mysql> CHANGE MASTER TO MASTER_LOG_FILE='<name>',
-> MASTER_LOG_POS=4;
mysql> START SLAVE;
This section applies to upgrading replication from MySQL 3.23, 4.0, or 4.1
to 5.0.0. A 4.0 server should be 4.0.3 or newer, as mentioned in
section 6.5 Replication Compatibility Between MySQL Versions.
First, note that MySQL 5.0.0 is an alpha release. It is intended to work
better than older versions (easier upgrade, replication of some important
session variables such as sql_mode; see section C.1.2 Changes in release 5.0.0 (22 Dec 2003: Alpha)). However
it has not yet been extensively tested. So, as with any alpha release, we
recommend that you not use it in critical production environments yet.
When you upgrade a master from MySQL 3.23 or 4.0 or 4.1 to 5.0.0, you should
first ensure that all the slaves of this master are already 5.0.0. If that's
not the case, you should first upgrade your slaves. To upgrade each slave,
just shut it down, upgrade it to 5.0.0, restart it, and restart replication.
The 5.0.0 slave will be able to read its old relay logs that were written
before the upgrade and execute the statements they contain. Relay logs
created by the slave after the upgrade will be in 5.0.0 format.
After the slaves have been upgraded, shut down your master, upgrade it to
5.0.0 and restart it. The 5.0.0 master will be able to read its old binary
logs that were written before the upgrade and send them to the 5.0.0 slaves.
The slaves will recognize the old format and handle it properly. Binary logs
created by master after the upgrade will be in 5.0.0 format. These too will
be recognized by the 5.0.0 slaves.
In other words, there are no measures to take when upgrading to 5.0.0,
except that slaves must be 5.0.0 before you can upgrade the master to 5.0.0.
Note that downgrading from 5.0.0 to older versions does not work so
automatically: You must ensure that any 5.0.0 binary logs or relay logs have
been fully processed, so that you can remove them before proceeding with the
downgrade.
Here is an explanation of what is supported and what is not:
-
Replication will be done correctly with
AUTO_INCREMENT,
LAST_INSERT_ID(), and TIMESTAMP values.
-
The
USER(), UUID(), and LOAD_FILE() functions
are replicated without changes and will thus not work reliably on the
slave. This is also true for CONNECTION_ID() in slave versions
older than 4.1.1.
The new PASSWORD() function in MySQL 4.1, is well
replicated since 4.1.1 masters; your slaves must be 4.1.0 or above
to replicate it. If you have older slaves and need to replicate
PASSWORD() from your 4.1.x master, you must start your master
with the --old-password option, so that it uses the old implementation
of PASSWORD().
-
The
FOREIGN_KEY_CHECKS variable is replicated as of version 4.0.14.
The SQL_MODE, UNIQUE_CHECKS, SQL_AUTO_IS_NULL variables
are replicated as of 5.0.0. The SQL_SELECT_LIMIT and
TABLE_TYPE variables are not yet replicated.
-
You must use the same character set (
--default-character-set)
on the master and the slave. Otherwise, you may get duplicate-key errors on
the slave, because a key that is regarded as unique in the master character
set may not be unique in the slave character set. Character sets will
be replicated in 5.0.x.
-
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, you will have problems if
the slave is stopped in the middle of a BEGIN/COMMIT block, because
the slave will restart at the beginning of the BEGIN block.
This issue is on our TODO and will be fixed in the near future.
-
Update statements that make use of user variables (that is, variables of the
form
@var_name) are badly replicated in 3.23 and 4.0. This is fixed
in 4.1. Note that user variable names are case insensitive starting from
version 5.0. You should take this into account when setting up replication
between 5.0 and an older version.
-
The slave can connect to the master using SSL if the master and slave
are both 4.1.1 or newer.
-
If a
DATA DIRECTORY or INDEX DIRECTORY clause is used in a
CREATE TABLE statement on the master server, the clause is also used
on the slave. This can cause problems if no corresponding directory exists
in the slave host filesystem or exists but is not accessible to the slave
server. Starting from MySQL 4.0.15, there is a sql_mode option
called NO_DIR_IN_CREATE. If the slave server is run with its SQL mode
set to include this option, it will simply ignore the clauses before
replicating the CREATE TABLE statement. The result is that the
MyISAM data and index files are created in the table's database
directory.
-
Though we have never heard of it actually occurring, it is theoretically
possible for the data on the master and slave to become different if a
query is designed in such a way that the data modification is
non-deterministic, that is, left to the will of the query optimizer. (That
generally is not a good practice anyway, even outside of replication!)
For a detailed explanation of this issue, see
section 1.8.7.3 Open Bugs / Design Deficiencies in MySQL.
-
Before MySQL 4.1.1,
FLUSH, ANALYZE, OPTIMIZE, and REPAIR statements
are not written to the binary log and thus are
not replicated to the slaves. This is not normally a problem as
these statements do not modify table data. However, it can cause difficulties
under certain circumstances. If you
replicate the mysql privilege database and
update the MySQL privilege tables directly without using the
GRANT statement, you
you issue a FLUSH PRIVILEGES statement on your slaves to put
the new privileges into effect. Also if you use
FLUSH TABLES when renaming a MyISAM table that is part of a
MERGE table, you will have to issue FLUSH TABLES
manually on the slaves.
As of MySQL 4.1.1, these statements are written to the binary log
(unless you specify NO_WRITE_TO_BINLOG, or its alias LOCAL).
Exceptions are that FLUSH LOGS, FLUSH MASTER,
FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not logged in
any case. (Any of them may cause problems if replicated to a slave.)
For a syntax example, see section 14.5.4.2 FLUSH Syntax.
-
MySQL only supports one master and many slaves. Later we will
add a voting algorithm to automatically change master if something goes
wrong with the current master. We will also introduce ``agent'' processes
to help do load balancing by sending
SELECT queries to different
slaves.
-
When a server shuts down and restarts, its
HEAP tables become empty.
As of MySQL 4.0.18, the master replicates this effect as follows:
The first time that the master uses each HEAP table after startup,
it notifies slaves that the table needs to emptied by writing a
DELETE FROM for the table to its binary log.
See section 15.3 The MEMORY (HEAP) Storage Engine for more details.
-
Temporary tables are replicated with the exception of the case
that you shut down slave server (not just slave thread) and you have some
replicated temporary tables that are used in update statements that
have not yet been executed on the slave. (If you shut down the slave, the
temporary tables needed by those updates no longer are available when the
slave starts again.)
To avoid this problem, do not shut down the slave while it has temporary
tables open. Instead, use this procedure:
-
Issue a
STOP SLAVE statement.
-
Use
SHOW STATUS to check the value of the Slave_open_temp_tables
variable.
-
If the value is 0, issue a
mysqladmin shutdown command to
shut down the slave.
-
If the value is not 0, restart the slave threads with
START SLAVE.
-
Repeat the procedure later to see if you have better luck next time.
We have plans to fix this problem in the near future.
-
It is safe to connect servers in a circular
master/slave relationship with the
--log-slave-updates option specified.
Note, however, that many statements will not work correctly in this kind of
setup unless your client code is written to take care of the potential
problems that can occur from updates that occur in different sequence
on different servers.
This means that you can create a setup such as this:
A -> B -> C -> A
Server IDs are encoded in the binary log events. Server A will know when
an event that it reads was originally created by itself, and will not
execute the event. Thus, there will be no infinite loop. But this circular
setup will work only if you only if you perform no conflicting
updates between the tables. In other words, if you insert data in both
A and C, you should never insert a row in A that may have a key that
conflicts with with a row inserted in C. You should also not update
the same rows on two servers if the order in which the updates are
applied is significant.
-
If a statement on the slave produces an error, the slave SQL thread
terminates, and the slave writes a message to its error log. You should
then connect to the slave manually, fix the problem (for
example, a non-existent table), and then run
START SLAVE.
-
It is safe to shut down a master server and restart it later.
If a slave loses its connection to the master, the slave tries to reconnect
immediately. If that fails, the slave retries periodicallly. (The default is
to retry every 60 seconds. This may be changed with the
--master-connect-retry option.)
The slave will also be able to deal with
network connectivity outages. However, the slave will notice the
network outage only after receiving no data from the master for
slave_net_timeout seconds. So if your outages are short, you may want
to decrease slave_net_timeout.
See section 5.2.3 Server System Variables.
-
Shutting down the slave (cleanly) is also safe, as it keeps track of where
it left off. Unclean shutdowns might produce problems, especially if disk
cache was not flushed to disk before the system went down. Your system fault
tolerance will be greatly increased if you have a good uninterruptible power
supply (UPS).
-
Due to the non-transactional nature of
MyISAM tables, it is possible
to have a statement that will only partially update a table and return an
error code. This can happen, for example, on a multiple-row insert that has
one row violating a key constraint, or if a long update statement is killed
after updating some of the rows. If that happens on the master, the slave
thread will exit and wait for the DBA to decide what to do about it unless
the error code is legitimate and the statement execution results in the same
error code. If this error code validation behavior is not desirable, some or
all errors can be masked out (ignored) with the --slave-skip-errors
option. This option is available starting with MySQL Version 3.23.47.
-
If you update transactional tables from non-transactional tables inside a
BEGIN/COMMIT segment, updates to the binary log may be out of sync
if some thread changes the non-transactional table before the
transaction commits. This is because the transaction is written to the
binary log only when it's committed.
-
Before version 4.0.15, any update to a non-transactional table is written to
the binary log at once when the update is made, whereas transactional
updates are written on
COMMIT or not written at all if you use
ROLLBACK. You must take this into account when updating both
transactional tables and non-transactional tables within the same
transaction. (This is true not only for replication, but also if you are
using binary logging for backups.) In version 4.0.15, we changed the logging
behavior for transactions that mix updates to transactional and
non-transactional tables, which solves the problems (order of statements is
good in binary log, and all needed statements are written to the binary log
even in case of ROLLBACK). The problem that remains is when a second
connection updates the non-transactional table while the first connection's
transaction is not finished yet; wrong order can still occur, because the
second connection's update will be written immediately after it is done.
-
When a 4.x slave replicates a
LOAD DATA INFILE from a 3.23 master, the
values of the Exec_Master_Log_Pos and Relay_Log_Space columns of
SHOW SLAVE STATUS become incorrect.
The incorrectness of Exec_Master_Log_Pos will cause a problem when you
stop and restart replication; so it is a good idea to correct the value before
this, by doing FLUSH LOGS on the master.
These bugs are already fixed in MySQL 5.0.0 slaves.
The following table lists problems in MySQL 3.23 that are fixed in MySQL 4.0:
-
LOAD DATA INFILE is handled properly, as long as the datafile
still resides on the master server at the time of update
propagation.
-
LOAD DATA LOCAL INFILE is no longer skipped on the slave as it
was in 3.23.
-
In 3.23,
RAND() in updates does not replicate properly.
Use RAND(some_non_rand_expr) if you are replicating updates with
RAND(). You can, for example, use UNIX_TIMESTAMP() for the
argument to RAND(). This is fixed in 4.0.
On both the master and the slave, you must use the server-id option
to establish a unique replication ID for each server. You should pick a unique
positive integer in the range from 1 to 2^32 - 1 for each master and slave.
Example: server-id=3
The options that you can use on the master server for controlling binary
logging are all described in section 5.8.4 The Binary Log.
The following table describes the options you can use on slave replication servers.
You can specify them on the command line or in an option file.
Some slave server replication options are handled in a special way, in the
sense that they are ignored if a `master.info' file exists when the slave
starts and contains values for the options.
The following options are handled this way:
--master-host
--master-user
--master-password
--master-port
--master-connect-retry
If no `master.info' file exists when the slave server starts,
it uses values for those options that are specified in option files
or on the command line. This will occur when you start the server
as a replication slave for the very first time, or you have run
RESET SLAVE and shut down and restarted the slave server.
If the `master.info' file does exist when the slave server starts, the
server ignores those options. Instead, it uses the values found in the
`master.info' file.
If you restart the slave server with different values of the startup options
that correspond to values in the `master.info' file, the different
values have no effect, because the server continues to use the
`master.info' file. To use different values, you must either restart
after removing the `master.info' file or (preferably) use the
CHANGE MASTER TO statement to reset the values while the slave is
running.
Suppose that you specify this option in your `my.cnf' file:
[mysqld]
master-host=this_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, it still will have no effect. You should
use CHANGE MASTER TO instead.
As of MySQL 4.1.1, the following options also are handled specially:
--master-ssl
--master-ssl-ca
--master-ssl-capath
--master-ssl-cert
--master-ssl-cipher
--master-ssl-key
The `master.info' file format in 4.1.1 changed to include values
corresponding to those options. In addition, the 4.1.1 file format includes
as its first line the number of lines in the file. If you upgrade an older
server to 4.1.1, the new server upgrades the `master.info' file to the
new format automatically when it starts. However, if you downgrade a 4.1.1
or newer server to a version older than 4.1.1, you should manually remove
the first line before starting the older server for the first time. Note
that in this case, the downgraded server no longer can use an SSL connection
to communicate with the master.
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 14.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. We plan to fix this.
--log-slave-updates
-
Normally, updates received from a master server by a slave are not logged to
its binary log. This option tells the slave to log the updates performed by
its SQL thread to the slave's own binary log. For this option to have any
effect, the slave must also be started with the
--log-bin option to
enable binary logging. --log-slave-updates is used when you want to
chain replication servers. For example, you might want a setup like this:
A -> B -> C
That is, A serves as the master for the slave B, and B serves as the master
for the slave C. For this to work, B must be both a master and a slave.
You must start both A and B with --log-bin to enable binary logging,
and B with the --log-slave-updates option.
--log-warnings
-
Makes the slave print more messages about what it is doing. For
example, it will warn you that it succeeded in reconnecting after a
network/connection failure, and warn you about how each slave thread started.
This option is not limited to replication use only. It produces warnings
across a spectrum of server activities.
--master-connect-retry=seconds
-
The number of seconds the slave thread sleeps before retrying to connect to
the master in case the master goes down or the connection is lost. The
value in the `master.info' file takes precedence if it can be read. If
not set, the default is 60.
--master-host=host
-
Specify the hostname or IP address of the master replication server.
If this option is not given, the slave
thread will not be started. The value in `master.info'
takes precedence if it can be read.
Probably a better name for this options would have been something like
--bootstrap-master-host, but it is too late to change now.
--master-info-file=file_name
-
Specifies the name to use for the file in which the slave records
information about the master.
The default name is `mysql.info' in the data directory.
--master-password=password
-
The password of the account that the slave thread uses for authentication when
connecting to the master.
The value in the `master.info' file takes precedence if it can be read.
If not set, an empty password is assumed.
--master-port=port_number
-
The TCP/IP port the master is listening on.
The value in the `master.info' file takes precedence if it can be read.
If not set, the compiled-in setting is assumed. If you have not tinkered with
configure options, this should be 3306.
--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 described in
section 5.5.8.5 SSL Command-line Options.
The values in the `master.info' file take precedence if they can be read.
These options are operational as of MySQL 4.1.1.
--master-user=username
-
The username of the account that the slave thread uses for authentication when
connecting to the master. The account must have the
REPLICATION SLAVE privilege. (Prior to MySQL 4.0.2, it must have the
FILE privilege instead.)
The value in the `master.info' file takes precedence if it can be read.
If the master user is not set, user test is assumed.
--max-relay-log-size=#
-
To rotate the relay log automatically.
See section 5.2.3 Server System Variables.
This option is available as of MySQL 4.0.14.
--read-only
-
This option causes the slave to allow no updates except from slave threads
or from users with the
SUPER privilege. This can be useful to ensure
that a slave server accepts no updates from clients.
This option is available as of MySQL 4.0.14.
--relay-log=file_name
-
Specify the name for the relay log. The default name is the hostname with a
-relay-bin.nnn suffix; nnn indicates that relay logs are
created in numbered sequence.
You can specify the option to create hostname-independant 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 on some area
different from the data directory, or if you want to increase speed by
balancing load between disks.
--relay-log-index=file_name
-
Specify the location and name that should be used for the relay
log index file. The default name is the hostname with a suffix of
-relay-bin.index.
--relay-log-info-file=file_name
-
To give `relay-log.info' another name and/or to put it in another
directory than the data directory.
--relay-log-purge={0|1}
-
Disables or enables automatic purging of relay logs as soon as they are not
needed any more. The default value is 1 (enabled). This is a global
variable that can be changed dynamically with
SET GLOBAL
relay_log_purge=0|1.
This option is available as of MySQL 4.1.1.
--relay-log-space-limit=#
-
Place an upper limit on the total size of all relay logs on the slave (a
value of 0 means ``unlimited''). This is useful for a slave server host that
has limited disk space. When the limit is reached, the I/O thread stops
reading binary log events from the master server until the SQL thread has
caught up and deleted some now unused relay logs. Note that this limit is
not absolute: There are cases where the SQL thread needs more events to be
able to delete. In that case, the I/O thread will exceed the limit until it
becomes possible for the SQL thread to delete some relay logs. Not doing so
would cause a deadlock (which is what happens before MySQL 4.0.13). You
should not set
--relay-log-space-limit to less than twice the value
of --max-relay-log-size (or --max-binlog-size if
--max-relay-log-size is 0). In that case, there is a chance that the
I/O thread will wait for free space because --relay-log-space-limit
is exceeded, but the SQL thread will have no relay log to purge and be
unable to satisfy the I/O thread. This forces the I/O thread to temporarily
ignore --relay-log-space-limit.
--replicate-do-db=db_name
-
Tells the slave to restrict replication to statements where
the current database (that is, the one selected by
USE)
is db_name.
To specify more than one database, use the directive multiple
times, once for each database. Note that this will not replicate
cross-database statements such as UPDATE some_db.some_table
SET foo='bar' while having selected a different or no database. If you
need cross-database updates to work, make sure you have MySQL 3.23.28 or
later, and use --replicate-wild-do-table=db_name.%.
Please read the notes that follow this option list.
Example of what does not work as you might expect: If the slave is
started with --replicate-do-db=sales, and you do
USE prices; UPDATE sales.january SET amount=amount+1000;,
this statement will not be replicated.
If you need cross-database updates to work,
use --replicate-wild-do-table=db_name.% instead.
The main reason for this ``just-check-the-current-database''
behavior is that it's difficult from the statement
alone to know if it should be replicated or not (for example, if you
are using multiple-table DELETE or multiple-table UPDATE
statements
that go across multiple databases). It's also very fast to just check
the current database.
--replicate-do-table=db_name.tbl_name
-
Tells the slave thread to restrict replication to the specified table.
To specify more than one table, use the directive multiple times, once
for each table. This will work for cross-database updates, in
contrast to
--replicate-do-db.
Please read the notes that follow this option list.
--replicate-ignore-db=db_name
-
Tells the slave to not replicate any statement where the current
database (that is, the one selected by
USE)
is db_name. To specify more than one database to
ignore, use the directive multiple times, once for each database.
You should not use this directive if you are using cross-table updates
and you don't want these update to be replicated.
Please read the notes that follow this option list.
Example of what does not work as you might expect: If the slave is
started with --replicate-ignore-db=sales, and you do
USE prices; UPDATE sales.january SET amount=amount+1000;,
this statement will be replicated.
If you need cross-database updates to work,
use --replicate-wild-ignore-table=db_name.% instead.
--replicate-ignore-table=db_name.tbl_name
-
Tells the slave thread to not replicate any statement that updates the
specified table (even if any other tables might be updated by the same
statement). To specify more than one table to ignore, use the directive
multiple times, once for each table. This will work for cross-database
updates, in contrast to
--replicate-ignore-db.
Please read the notes that follow this option list.
--replicate-wild-do-table=db_name.tbl_name
-
Tells the slave thread to restrict replication to statements where any of
the updated tables match the specified database and table name patterns.
Patterns can contain the `%' and `_' wildcard characters, which have
the same meaning as for the
LIKE pattern-matching operator. To specify
more than one table, use the directive multiple times, once for each
table. This will work for cross-database updates.
Please read the notes that follow this option list.
Example: --replicate-wild-do-table=foo%.bar% will replicate only
updates that use a table where the database name starts with 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 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, depending on
your command interpreter, need to double the backslashes or quote the
option value. For example, with the bash shell, you would need to
type --replicate-wild-do-table=my\\_own\\%db.
--replicate-wild-ignore-table=db_name.tbl_name
-
Tells the slave thread to not replicate a statement where any table matches the
given wildcard pattern. To specify more than one table to ignore, use
the directive multiple times, once for each table. This will work for
cross-database updates.
Please read the notes that follow this option list.
Example:
--replicate-wild-ignore-table=foo%.bar% will 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-ignore-table option.
The rules for including literal wildcard characters in the option value
are the same as for
--replicate-wild-ignore-table as well.
--replicate-rewrite-db=from_name->to_name
-
Tells the slave to translate the current 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 like CREATE DATABASE and DROP DATABASE),
and only if from_name was the current database on the master.
This will not work for cross-database updates.
Note that the database name translation is done before --replicate-*
rules are tested.
Example: replicate-rewrite-db=master_db_name->slave_db_name
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"
--report-host=host
-
The hostname or IP number of the slave to be reported to the master during
slave registration. This value will appear 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
once the slave connects. Due to NAT and other routing issues, that IP
may not be valid for connecting to the slave from the master or other hosts.
This option is available as of MySQL 4.0.0.
--report-port=port_number
-
Port for connecting to the slave, to be reported to the
master during slave registration. Set it only if the slave is listening
on a non-default port or if you have a special tunnel from the master or
other clients to the slave. If not sure, leave this option unset.
This option is available as of MySQL 4.0.0.
--skip-slave-start
-
Tells the slave server not to start the slave threads when the server starts.
To start the threads later, use a
START SLAVE statement.
--slave_compressed_protocol=#
-
If 1, then use compression on the slave/client protocol if both the
slave and the master support it.
--slave-load-tmpdir=file_name
-
This option is by default equal to the value of the
tmpdir variable.
When the slave SQL thread replicates a LOAD DATA INFILE statement, it
extracts the to-be-loaded file from the relay log into temporary files,
then loads these into the table. If the file loaded on the master was
huge, the temporary files on the slave will be huge, too. Therefore, it might
be advisable to use this option to to tell the slave to put temporary files in
a directory located in some filesystem that has a lot of available space.
In that case, you may also use the --relay-log option,
as relay logs will be huge, too.
--slave-load-tmpdir should point to a disk-based filesystem, not a
memory-based one: The slave needs the
temporary files used to replicate LOAD DATA INFILE to survive a
machine's restart.
The directory also should not be one that is cleared by the operating system
during the system startup process.
--slave-net-timeout=#
-
The number of seconds to wait for more data from the master before aborting
the read, considering the connection broken and trying to reconnect. The first
retry occurs immediately after the timeout. The interval between retries is
controlled by the
--master-connect-retry option.
--slave-skip-errors= [err_code1,err_code2,... | all]
-
Normally, replication stops when an error occurs, which gives you the
opportunity to resolve the inconsistency in the data manually. This option
tells the slave SQL thread to continue replication when a statement returns
any of the errors listed in the option value.
Do not use this option unless you fully understand why you are getting the
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 will result in slaves becoming
hopelessly out of sync with the master, and you will have no idea why.
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. A full list
of error messages can be found in the source distribution in
`Docs/mysqld_error.txt'.
The server error codes also are listed at section 21.1 Error Returns.
You can (but should not) also use a very non-recommended value of all
which will ignore all error messages and keep barging along regardless of what
happens. Needless to say, if you use it, we make no promises regarding your
data integrity. Please do not complain if your data on the slave is not
anywhere close to what it is on the master in this case -- you have been
warned.
Examples:
--slave-skip-errors=1062,1053
--slave-skip-errors=all
The --replicate-* rules are evaluated as follows to determine whether a
statement will be executed by the slave or ignored:
-
Are there some
--replicate-do-db or --replicate-ignore-db
rules?
- Yes:
Test them as for
--binlog-do-db and --binlog-ignore-db
(see section 5.8.4 The Binary Log). What is the result of the test?
- Ignore the statement:
Ignore it and exit.
- Execute the statement:
Don't execute it immediately, defer the decision, go to the next step.
- No:
Go to the next step.
-
Are there some
--replicate-*-table rules?
- No:
Execute the query and exit.
- Yes:
Go to the next step. Only tables that are to be updated are compared
to the rules (
INSERT INTO sales SELECT * FROM prices: only
sales will be compared to rules). If several tables are to be
updated (multiple-table statement),
the first matching table (matching ``do'' or ``ignore'') wins.
That is, the first table is compared to the rules. Then, if no decision could
be mad, the second table is compared to the rules, and so forth.
-
Are there some
--replicate-do-table rules?
- Yes:
Does the table match any of them?
- Yes:
Execute the query and exit.
- No:
Go to the next step.
- No:
Go to the next step.
-
Are there some
--replicate-ignore-table rules?
- Yes:
Does the table match any of them?
- Yes:
Ignore the query and exit.
- No:
Go to the next step.
- No:
Go to the next step.
-
Are there some
--replicate-wild-do-table rules?
- Yes:
Does the table match any of them?
- Yes:
Execute the query and exit.
- No:
Go to the next step.
- No:
Go to the next step.
-
Are there some
--replicate-wild-ignore-table rules?
- Yes:
Does the table match any of them?
- Yes:
Ignore the query and exit.
- No:
Go to the next step.
- No:
Go to the next step.
-
No
--replicate-*-table rule was matched.
Is there another table to test against these rules?
- Yes:
Loop.
- No:
We have tested all tables to be updated, could not match any rule.
Are there
--replicate-do-table or --replicate-wild-do-table
rules?
- Yes:
Ignore the query and exit.
- No:
Execute the query and exit.
Q: How do I configure a slave if the master is already running
and I do not want to stop it?
A: There are several options. If you have taken a backup of the
master at some point and recorded the binary log name and offset (from the
output of SHOW MASTER STATUS ) corresponding to the snapshot, use
the following procedure:
-
Make sure the slave is assigned a unique server ID.
-
Execute the following statement on the slave, filling in appropriate values for each
parameter:
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host-name',
-> MASTER_USER='master_user_name',
-> MASTER_PASSWORD='master_pass',
-> MASTER_LOG_FILE='recorded_log_name',
-> MASTER_LOG_POS=recorded_log_pos;
-
Execute
START SLAVE on the slave.
If you do not have a backup of the master already, here is a quick way to
do it consistently:
-
FLUSH TABLES WITH READ LOCK
-
gtar zcf /tmp/backup.tar.gz /var/lib/mysql (or a variation of this)
-
SHOW MASTER STATUS - make sure to record the output - you will need it
later
-
UNLOCK TABLES
An alternative is to make an SQL dump of the master instead of a binary copy
as in the preceding procedure. To do this, you can use mysqldump
--master-data on your master and later load the SQL dump into your slave.
However, this is slower than makeing a binary copy.
No matter which of the two methods you use, afterward follow the
instructions for the case when you have a snapshot and have recorded the log
name and offset. You can use the same snapshot to set up several slaves.
Once you have the snapshot of the master, you can wait to set up a slave as
long as the binary logs of the master are left intact. The two practical
limitations on the length of time you can wait are the amount of disk space
available to retain binary logs and the master and the length of time it
will take the slave to catch up.
You can also use LOAD DATA FROM MASTER. This is a convenient
statement that transfers a snapshot to the slave and adjusts the log name
and offset all at once. In the future, LOAD DATA FROM MASTER will be
the recommended way to set up a slave. Be warned, however, that it works
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 at this time is still to make a binary snapshot
on the master server after executing FLUSH TABLES WITH READ LOCK.
Q: Does the slave need to be connected to the master all the time?
A: No, it does not. The slave can go down or stay
disconnected for hours or even days, then reconnect and catch up on the
updates.
For example, you can set up a master/slave relationship over a dial-up link
where the link is up only sporadically and
for short periods of time. The implication of this is that at any
given time the slave is not guaranteed to be in sync with the master
unless you take some special measures. In the future, we will have the
option to block the master until at least one slave is in sync.
Q: How do I know how late a slave is compared to the
master? In other words, how do I know the date of the last query
replicated by the slave?
A:
If the slave is 4.1.1 or newer, read the Seconds_Behind_Master column
in SHOW SLAVE STATUS. For older versions, the following applies.
This is possible only if SHOW PROCESSLIST on the slave shows that the
SQL thread is running (or for MySQL 3.23, that the slave thread is running),
and that the thread has executed at least one event from the master.
See section 6.3 Replication Implementation Details.
When the slave SQL thread executes an event read from the master, it
modifies its own time to the event's timestamp (this is why TIMESTAMP
is well replicated). So in the Time column in the output of
SHOW PROCESSLIST, the number of seconds displayed for the slave SQL
thread is the number of seconds between the timestamp of the last replicated
event and the real time of the slave machine. You can use this to determine
the date of the last replicated event. Note that if your slave has been
disconnected from the master for one hour, and then reconnects, you may
immediately see Time values like 3600 for the slave SQL thread in
SHOW PROCESSLIST. This would be because the slave is executing
statements that are one hour old.
Q: How do I force the master to block updates until the slave catches
up?
A: Use the following procedure:
-
On the master, execute these statements:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
Record the log name and the offset from the output of the SHOW
statement. These are the replication coordinates.
-
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 will block until the slave reaches the specified
log file and offset. At that point, the slave will be in sync with the master
and the statement will return.
-
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 will make the update of client A work differently than
it did on co-master 1. Thus, when the update of client A will make it
to co-master 2, it will produce tables that are different than
what you have on co-master 1, even after all the updates from co-master
2 have also propagated. So you should not co-chain two servers in a
two-way replication relationship, unless you are sure that your updates
can safely happen in any order, or unless you take care of mis-ordered
updates somehow in the client code.
You must also realize that two-way replication actually does not improve
performance very much (if at all), as far as updates are concerned. Both
servers need to do the same amount of updates each, as you would have
one server do. The only difference is that there will be a little less
lock contention, because the updates originating on another server will
be serialized in one slave thread. Even this benefit might be
offset by network delays.
Q: How can I use replication to improve performance of my system?
A: You should set up one server as the master and direct all
writes to it. Then configure as many slaves as you have the money and
rackspace for, and distribute the reads among the master and the slaves.
You can also start the slaves with --skip-bdb,
--low-priority-updates and --delay-key-write=ALL
to get speed improvements for the slave. In this case, the slave will
use non-transactional MyISAM tables instead of BDB tables
to get more speed.
Q: What should I do to prepare client code in my own applications
to use performance-enhancing replication?
A:
If the part of your code that is responsible for database access has
been properly abstracted/modularised, converting it to run with a
replicated setup should be very smooth and easy. Just change the
implementation of your database access to send all writes to the master,
and to send reads to either the master or a slave.
If your code does not have this level of abstraction,
setting up a replicated system will give you the opportunity and motivation
to it clean up.
You should start by creating a wrapper library or module with the following
functions:
-
safe_writer_connect()
-
safe_reader_connect()
-
safe_reader_query()
-
safe_writer_query()
safe_ in each function name means that the function will take care
of handling all the error conditions.
You can use different names for the
functions. The important thing is to have a unified interface for connecting
for reads, connecting for writes, doing a read, and doing a write.
You should then convert your client code to use the wrapper library.
This may be a painful and scary process at first, but it will pay off in
the long run. All applications that use the approach just described will be
able to take advantage of a master/slave configuration, even one involving
multiple slaves. The
code will be a lot easier to maintain, and adding troubleshooting
options will be trivial. You will just need to modify one or two
functions, for example, to log how long each query took, or which
query, among your many thousands, gave you an error.
If you have
written a lot of code already, you may want to automate the conversion
task by using the replace utility that comes with the
standard distribution of MySQL, or just write your own conversion script.
Ideally, your code will use consistent programming patterns. If not, then
you are probably better off rewriting it anyway, or at least going
through and manually regularizing it into a pattern.
Q: When and how much can MySQL replication improve the performance
of my system?
A: MySQL replication is most beneficial for a system
with frequent reads and infrequent writes. In theory, by using a
single-master/multiple-slave setup, you can scale the system by adding more
slaves until you either run out of network bandwidth, or your update
load grows to the point that the master cannot handle it.
In order to determine how many slaves you can get before the added
benefits begin to level out, and how much you can improve performance
of your site, you need to know your query patterns, and empirically
to determine by benchmarking the relationship between the throughput
on reads (reads per second, or max_reads) and on writes
(max_writes) on a typical master and a typical slave. The
example here 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 max_reads is 1200 - 2 * max_writes.
In other words, the system can do 1200 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 1 master and N slaves. Then we have for each
server (master or slave):
reads = 1200 - 2 * writes
reads = 9 * writes / (N + 1) (reads split, but writes go
to all servers)
9 * writes/(N + 1) + 2 * writes = 1200
writes = 1200 / (2 + 9/(N+1))
The last equation indicates that the maximum number of writes for N slaves,
given a maximum possible read rate of 1200 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 can get up to 184 writes per second.
-
If N = 8, we get up to 400.
-
If N = 17, 480 writes.
-
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 8 servers, we increased
it almost 4 times already.
Note that these computations assume infinite network bandwidth and
neglect several other factors that could turn out to be significant on
your system. In many cases, you may not be able to perform a computation
similar to the just shown that will accurately predict what will happen
on your system if you add N replication slaves. However, answering the
following questions should help you decide whether and how much
replication will improve the performance of your system:
-
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/high
availability?
A: With the currently available features, you would have to
set up a master and a slave (or several slaves), and write a script
that will monitor the
master to see whether it is up. Then instruct your applications and
the slaves to change master in case of failure. Some suggestions:
We are currently working on integrating an automatic master election
system into MySQL, but until it is ready, you will have to
create your own monitoring tools.
If you have followed the instructions, and your replication setup is not
working, first check the following:
-
Check the error log for messages. Many users have lost time
by not doing this early enough.
-
Is the master logging to the binary log? Check with
SHOW MASTER STATUS.
If it is, Position will be non-zero. If not, verify that you are
running the master with the log-bin and server-id options.
-
Is the slave 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.
-
If the slave is running, did it establish a connection to the master? Use
SHOW PROCESSLIST, find the I/O and SQL threads and check their
State column to see how they display.
See section 6.3 Replication Implementation Details.
If the I/O thread state says Connecting to master, verify the
privileges for the replication user on the master, master hostname, your
DNS setup, whether the master is actually running, whether it is reachable
from the slave.
-
If the slave was running before but now 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 modify the data on the slave outside of the slave thread. If it does,
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.11 Reporting Replication Bugs for instructions on how to report it.
-
If a statement that succeeded on the master refuses to run on the slave, and
it is not feasible to do a full database resynchronization (that is, to
delete the slave's database and copy a new snapshot from the master), try
the following:
-
Determine whether
the slave's table is different from the
master's. 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 = n;
mysql> START SLAVE;
The value of n should be 1 if the query does not use
AUTO_INCREMENT or LAST_INSERT_ID(). Otherwise, the value should
be 2. The reason for using a value of 2 for
queries 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 the slave started out perfectly synchronized with the master,
and no one has updated the tables involved outside of slave thread, then
presumably the discrepancy is the result of a bug. If you are running the most
recent version, please report the problem. If you are running an older
version, try upgrading.
When you have determined that there is no user error involved, and replication
still either does not work at all or is unstable, it is time to send
us a bug report. We need to get as much information as possible from you to be able
to track down the bug. Please do spend some time and effort preparing a good
bug report.
If you have a repeatable test case that demonstrates the bug, please
enter it into our bugs database
at http://bugs.mysql.com/. If you have a phantom problem (one that
you cannot duplicate ``at will''), use the following procedure:
-
Verify that no user error is involved. For example, if you update the
slave outside of the slave thread, the data will go out of sync, and you can
have unique key violations on updates. In this case, the slave thread will
stop and wait for you to clean up the tables manually to bring them in sync.
This is not a replication problem; it is a problem of outside interference
that causes replication to fail.
-
Run the slave with the
--log-slave-updates and --log-bin
options.
They will 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 have discovered the problem
-
The output of
SHOW SLAVE STATUS from the master at the time
you have 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 trouble query, for example:
shell> mysqlbinlog -j pos_from_slave_status \
/path/to/log_from_slave_status | head
Once you have collected the evidence for the phantom problem, try hard to
isolate it into a separate test case first. Then enter the problem into
our bugs database at http://bugs.mysql.com/ with as much
information as possible.
Go to the first, previous, next, last section, table of contents.
|
| Who's Online |
| Guest Users: 6 |
 |
Deliver First Class Web Sites: 101 Essential Checklists
Want to learn how to make your web sites usable and accessible? Want to ensure that your sites meet current best practice, without spending hours trawling through incomprehensible specifications and recommendations from dozens of different books, research papers, and web sites? Want to make sure that the sites you build are "right the first time," requiring no costly redevelopments? |
More Sample Chapters |
|
Free Sample Chapters