Replication in MySQL
====================
It
serves as a reference to the options available with replication. You
will be introduced to replication and learn how to implement it.
Toward that end, there are some frequently asked questions, descriptions
of problems, and how to solve them.
We suggest that you visit our website at `http://www.mysql.com/' often
and read updates to this section. Replication is constantly being
improved, and we update the manual frequently with the most current
information.
Introduction
------------
(*note Binary log::). It also maintains an index file of the binary
logs to keep track of log rotation. Each slave, upon connecting,
informs the master where it left off since the last successfully
propagated update, catches up any updates that have occurred since
then, and then blocks and waits for the master to notify it of new
updates.
A slave can also serve as a master if you set up chained replication
servers.
Note that when you are using replication, all updates to the tables
that are replicated should be performed on the master server.
Otherwise, you must always be careful to avoid conflicts between
updates that users make to tables on the master and updates that they
make to tables on the slave.
One-way replication has benefits for robustness, speed, and system
administration:
* 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.
* The extra speed is achieved by splitting the load for processing
client queries between the master and slave servers, resulting in
better client response time. `SELECT' queries may be sent to the
slave to reduce query processing load of the master. Queries that
modify data should still be sent to the master so that the master
and slave to not get out of sync. This load-balancing strategy if
effective if non-updating queries dominate, but that is the normal
case.
* Another benefit of using replication is that one can get
non-disturbing backups of the system by doing a backup on a slave
instead of doing it on the master. *Note Backup::.
Replication Implementation Overview
-----------------------------------
the
saved queries that the master has recorded in its binary log, so that
the slave can execute the same queries on its copy of the data.
It is *very important* to realise that the binary log is simply a
record starting from a fixed point in time (the moment you enable binary
logging). Any slaves that you set up will need copies of the databases
on your master as they existed at the moment you enabled binary logging
on the master. If you start your slaves with data that is not the same
as what was on the master *when the binary log was started*, your
slaves may fail.
The following table indicates master/slave replication compatibility
between different versions of MySQL.
*Master* *Master**Master**Master*
*3.23.33 *4.0.0* *4.0.1* *4.0.3 and
and up* up*
*Slave* *3.23.33 yes no no no
and up*
*Slave* *4.0.0* no yes no no
*Slave* *4.0.1* yes no yes no
*Slave* *4.0.3 and yes no no yes
up*
As a general rule, it's always recommended to use recent MySQL versions,
because replication capabilities are continually being improved. With
regard to version 4.0, we recommend using same version for both the
master and the slave, with the exception that MySQL 4.0.2 is not
recommended for replication.
can
safely be done this way, assuming you have a 3.23 master to upgrade and
you have 4.0 slaves:
1. Block all updates on the master (`FLUSH TABLES WITH READ LOCK').
2. Wait until all the slaves have caught up all changes from the
master (use `SHOW MASTER STATUS' on the master, and `SELECT
MASTER_POS_WAIT()' on the slaves). Then run `STOP SLAVE' on the
slaves.
3. Shut down MySQL on the master and upgrade the master to MySQL 4.0.
4. Restart MySQL on the master. Record the name `' of the
master's newly created binary log. You can obtain the name of the
file by issuing `SHOW MASTER STATUS' on the master. Then issue
these commands on each slave:
mysql> CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=4;
mysql> START SLAVE;
If you also must upgrade your slaves from 3.23 to 4.0, you should first
upgrade your slaves: Shut down each one, upgrade it, and restart it.
Then upgrade the master as just described.
Starting from 4.0.0, you can use `LOAD DATA FROM MASTER' to set up a
slave. Be aware that `LOAD DATA FROM MASTER' currently works only if
all the tables on the master are `MyISAM' type. Also, this statement
acquires a global read lock, so no writes are possible while the tables
are being transferred from the master. When we implement lock-free hot
table backup (in MySQL 5.0), this global read lock will no longer be
necessary.
Due to these limitations, we recommend that at this point you use
`LOAD DATA FROM MASTER' only if the dataset on the master is relatively
small, or if a prolonged read lock on the master is acceptable. While
the actual speed of `LOAD DATA FROM MASTER' may vary from system to
system, a good rule of thumb for how long it is going to take is 1
second per 1 MB of the datafile. You will get close to the estimate if
both master and slave are equivalent to 700 MHz Pentium and are
connected through a 100 MBit/s network. Of course, this is only a
rough estimate.
Once a slave is properly configured and running, it will simply connect
to the master and wait for updates to process. If the master goes away
or the slave loses connectivity with your master, it will keep trying to
connect periodically until it is able to reconnect and resume listening
for updates. The retry interval is controlled by the
`--master-connect-retry' option. The default is 60 seconds.
Each slave keeps track of where it left off. The master server has no
knowledge of how many slaves there are or which ones are up-to-date at
any given time.
Replication Implementation Details
----------------------------------
the queries
recorded in its binlogs. Then one thread is created on the master to
send these binlogs. This thread is identified by `Binlog Dump' in
`SHOW PROCESSLIST' output on the master. The I/O thread reads what the
master `Binlog Dump' thread sends and simply copies it to some local
files in the slave's data directory called relay logs. The last
thread, the SQL thread, is created on the slave; it reads the relay
logs and executes the queries it contains.
Note that the master has one thread for each currently connected slave
server.
With `SHOW PROCESSLIST' you can know what is happening on the master
and on the slave as regards replication.
column was changed to be more meaningful compared to earlier
versions.
On the master server, the output looks like this:
mysql>
Time: 94
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
On the slave server, the output looks like this:
mysql> 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 I/O slave thread to update it
Info: NULL
the
master (*note 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
this thread on a master server, replication is not running.
`Sending binlog event to slave'
Binlogs consist of events, where an event is usually a query plus
some other information. The thread has read an event from the
binlog and is sending it to the slave.
`Finished reading one binlog; switching to next binlog'
The thread has finished reading a binlog 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 binary log files and is idle. It is
waiting for new events to appear in the binary log as a result of
new update queries being executed on the master.
`Waiting to finalize termination'
A very brief state that happens as the thread is stopping.
Here are the most common states you will see in the `State' column for
the I/O thread of a slave server. Beginning with MySQL 4.1.1, this
state also appears in the `Slave_IO_State' column of `SHOW SLAVE
STATUS' output. This means that you can get a good view of what is
happening by using only `SHOW SLAVE STATUS'.
`Connecting to master'
The thread is attempting to connect to the master.
`Checking master version'
A very brief state that happens just after the connection to the
master is established.
`Registering slave on master'
A very brief state that happens just after the connection to the
master is established.
`Requesting binlog dump'
A very brief state that happens just after the connection to the
master is established. The thread sends to the master a request
for the contents of its binlogs, starting from the requested
binlog filename and position.
`Waiting to reconnect after a failed binlog dump request'
If the binlog 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 binlog 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.
to reconnect.
`Waiting for the SQL slave 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 happens as the thread is stopping.
Here are the most common states you will see in the `State' column for
the SQL thread of a slave server:
`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 I/O slave 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 happens as the thread is stopping.
The `State' column for the I/O thread may also show a query string.
This indicates that the thread has read an event from the relay log,
extracted the query from it and is executing the query.
Before MySQL 4.0.2, the slave I/O and SQL threads were combined as a
single thread, and no relay log files were used. The advantage of
using two threads is that it separates query reading and query
execution into two independent tasks, so the task of reading queries is
not slowed down if query execution is slow. For example, if the slave
server has not been running for a while, its I/O thread can quickly
fetch all the binlog contents from the master when the slave starts,
even if the SQL thread lags far behind and may take hours to catch up.
If the slave stops before the SQL thread has executed all the fetched
queries, the I/O thread has at least fetched everything so that a safe
copy of the queries is locally stored in the slave's relay logs for
execution when next the slave starts. This allows the binlogs to be
purged on the master, because it no longer need wait for the slave to
fetch their contents.
By default, relay logs are named using filenames of the form
`host_name-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.
has
executed all its events). There is no command to delete relay logs,
because the SQL thread takes care of doing so. However, from MySQL
4.0.14, `FLUSH LOGS' rotates relay logs, which will influence when the
SQL thread deletes them.
A new relay log is created under the following conditions:
* The first time the I/O thread starts after the slave server starts.
(In MySQL 5.0, a new relay log will be created each time the I/O
thread starts, not just the first time.)
* A `FLUSH LOGS' statement is issued (4.0.14 and up only).
* The size of the current relay log file becomes too big. The
meaning of "too big" 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
the output of the `SHOW SLAVE STATUS' statement (*note Replication
Slave SQL:: for a description of this command). As disk files they
survive slave's shutdown. The next time the slave starts up, it can
read these files to know how far it has proceeded in reading binlogs
from the master and in processing its own relay logs.
The `master.info' file is updated by the I/O thread. The
correspondance between the lines in the file and the columns displayed
by `SHOW SLAVE STATUS' is as follows:
*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
correspondance 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 2 small
files as well, along with the relay log files. because they are needed
to resume replication after you restore the slave's data. If you lose
the relay logs but still have the `relay-log.info' file, you can check
it to determine how far the SQL thread has executed in the master
binlogs. Then you can use `CHANGE MASTER TO' with the
`MASTER_RELAY_LOG' and `MASTER_RELAY_POS' options to tell the slave to
re-read the binlogs from that point. This requires that the binlogs
still exist on the master server, of course.
If your slave is subject to replicating `LOAD DATA INFILE' statements,
you should also backup the `SQL-LOAD*' files that may exist in the
directory that the slave uses for this purpose. The slave needs these
files to resume replication of any interrupted `LOAD DATA INFILE'
statements. (The directory location is specified using the
`--slave-load-tmpdir' option. Its default value if not specified is
the value of the `tmpdir' variable.)
How to Set Up Replication
-------------------------
Here is a quick description of how to set up complete replication on
your current MySQL server. It assumes you want to replicate all your
databases and have not configured replication before. You will need to
shut down your master server briefly to complete the steps outlined
here.
The procedure is written in terms of setting up a single slave, but you
can use it to set up multiple slaves.
While this method is the most straightforward way to set up a slave, it
is not the only one. For example, if you already have a snapshot of the
master's data, and the master already has its server ID set and binary
logging enabled, you can set up a slave without shutting down the
master or even blocking updates to it. For more details, please see
*Note Replication FAQ::.
If you want to administer a MySQL replication setup, we suggest that you
read this entire chapter through and try all commands mentioned in
*Note Replication Master SQL:: ans *Note Replication Slave SQL::. You
should also familiarise yourself with replication startup options in
`my.cnf' in *Note Replication 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.
1. Make sure you have a recent version of MySQL installed on the
master and slave(s), and that these versions are compatible
according to the table shown in *Note Replication Implementation::.
Please do not report bugs until you have verified that the problem
is present in the latest release.
2. Set up an account on the master server that the slave server can
use to connnect. This account must be given the `REPLICATION
SLAVE' privilege. (If MySQL versions older than 4.0.2, give the
account the `FILE' privilege instead.) If the account is only for
replication (which is recommended), you don't need to grant any
additional privileges.
The hostname in the account name should be such that each of the
slave servers can use the account to connect to the master. For
example, to create a user named `repl' which can access your
master from any host, you might use this command:
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY '';
For MySQL versions older than 4.0.2, use this command instead:
mysql> GRANT FILE ON *.* TO repl@'%' IDENTIFIED BY '';
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 to the account the `SUPER' and `RELOAD' global
privileges.
3. If you are using MyISAM tables, flush all the tables and block
write queries by executing `FLUSH TABLES WITH READ LOCK' command.
mysql> FLUSH TABLES WITH READ LOCK;
and then take a snapshot of the data on your master server.
The easiest way to create a snapshot is to simply use an archiving
program (`tar' on Unix, `PowerArchiver', `WinRAR', `WinZIP' or any
similar software on Windows) to produce an archive of the
databases in your master's data directory. For example, 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
shell> tar -xvf /tmp/mysql-snapshot.tar
You may not want to replicate the `mysql' database. If not, you can
exclude it from the archive. You also need not include any log
files in the archive, or the `master.info' or `relay-log.info'
files.
While the read lock placed by `FLUSH TABLES WITH READ 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 |
+---------------+----------+--------------+------------------+
1 row in set (0.06 sec)
need to use them later when you are setting up the slave.
Once you have taken the snapshot and recorded the log name and
offset, you can re-enable write activity on the master:
mysql> UNLOCK TABLES;
a consistent snapshot without acquiring any locks on the master
server, and records the log name and offset corresponding to the
snapshot to be later used on the slave. More information about the
tool is avalaible at `http://www.innodb.com/hotbackup.html'.
Without the Hot Backup tool, the quickest way to take a snapshot
of InnoDB tables is to shut down the master server and copy the
InnoDB datafiles and logs, and the table definition files
(`.frm'). To record the current log file name and offset, you
should do the following before you shut down the server:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
And then record the log name and the offset from the output of
`SHOW MASTER STATUS' as was shown earlier. Once you have recorded
the log name and the offset, shut down the server without
unlocking the tables to make sure it goes down with the snapshot
corresponding to the current log file and offset:
shell> mysqladmin -uroot shutdown
An alternative for both MyISAM and InnoDB tables is to take an SQL
dump of the master instead of a binary copy like above; for this
you can use `mysqldump --master-data' on your master and later run
this SQL dump into your slave. However, this is slower than doing
a binary copy.
If the master has been previously running without `--log-bin'
enabled, the log name and position values displayed by `SHOW MASTER
STATUS' or `mysqldump' will be empty. In that case, record empty
string (") for the log name, and 4 for the offset.
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 an
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.
5. 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 an
integer value from 1 to 2^32 - 1. In addition, it is very
important that the ID of the slave be different than the ID of the
master. For example:
[mysqld]
server-id=2
If you are setting up multiple slaves, each one must have a
`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.
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.
6. 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 which MySQL runs as needs to be able to read
from and write to them, just as on the master.
If you made a backup using `mysqldump', start the slave first (see
next step).
7. Start the slave server. If it has been replicating previously,
start the slave server with the `--skip-slave-start' option. You
also may want to start the slave server with the `--log-warnings'
option. That way, you will get more messages about problems (for
example, network or connection problems).
8. 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
9. Execute the following command on the slave, replacing the values
within `<>' with the actual values relevant to your system:
mysql> CHANGE MASTER TO
-> MASTER_HOST='',
-> MASTER_USER='',
-> MASTER_PASSWORD='',
-> MASTER_LOG_FILE='',
-> MASTER_LOG_POS=;
The following table lists the maximum string length for these
variables:
`MASTER_HOST' 60
`MASTER_USER' 16
`MASTER_PASSWORD' 32
`MASTER_LOG_FILE' 255
10. 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.
binary log it has processed. *Do not* remove or edit these files,
unless you really know what you are doing and understand the
implications. Even in that case, it is preferred that you use `CHANGE
MASTER TO' command.
*NOTE*: The content of `master.info' overrides some options specified
on the command-line or in `my.cnf' See *Note Replication 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.
Replication Features and Known Problems
---------------------------------------
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()' 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 option `--old-password'.
* The `sql_mode', `FOREIGN_KEY_CHECKS', and `table_type' variables
are not 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.
* If you are using transactional tables on the master and
non-transactional tables (for the same tables) on the slave, you
will get problems if the slave is stopped in the middle of a
`BEGIN/COMMIT' block, as the slave will later start at the
beginning of the `BEGIN' block. This issue is on our TODO and
will be fixed in the near future.
* Update queries that use user variables 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, so you should take
this into account when setting up replication between 5.0 and a
previous version.
* The slave can connect to the master using SSL, if the master and
slave are both 4.1.1 or newer.
* 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 optimiser (which generally is not a good practice,
even outside of replication!). For a detailed explanation, see
*Note Open bugs::.
* Before MySQL 4.1.1, `FLUSH', `ANALYZE', `OPTIMIZE', `REPAIR'
commands are not stored in the binary log and thus are not
replicated to the slaves. This is not normally a problem as these
commands don't change anything. However, it does mean that if you
update the MySQL privilege tables directly without using the
`GRANT' statement and you replicate the `mysql' privilege
database, you must do a `FLUSH PRIVILEGES' on your slaves to put
the new privileges into effect. Also if you use `FLUSH TABLES'
when renaming a `MyISAM' table involved in a `MERGE' table, you
will have to issue `FLUSH TABLES' manually on the slave. Since
MySQL 4.1.1, these commands are written to the binary log (except
`FLUSH LOGS', `FLUSH MASTER', `FLUSH SLAVE', `FLUSH TABLES WITH
READ LOCK') unless you specify `NO_WRITE_TO_BINLOG' (or its alias
`LOCAL'). For a syntax example, see *Note `FLUSH': FLUSH.
* 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.
* 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:
1. Issue a `STOP SLAVE' statement.
2. Use `SHOW STATUS' to check the value of the
`Slave_open_temp_tables' variable.
3. If the value is 0, issue a `mysqladmin shutdown' command to
shut down the slave.
4. If the value is not 0, restart the slave threads with `START
SLAVE'.
5. 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 `log-slave-updates' enabled. Note, however,
that many queries will not work correctly in this kind of setup
unless your client code is written to take care of the potential
problems that can happen from updates that occur in different
sequence on different servers.
This means that you can do a setup like the following:
A -> B -> C -> A
Server IDs are encoded in the binary log events. A will know when
an event it reads had originally been created by A, so A will not
execute it and there will be no infinite loop. But this circular
setup will work only if you only if you perform no conflicting
updates between the tables. In other words, if you insert data in
A and C, you should never insert a row in A that may have a
conflicting key with a row insert in C. You should also not update
the same rows on two servers if the order in which the updates are
applied matters.
* If a query on the slave gets an error, the slave SQL thread will
terminate, and a message will appear in the slave error log. You
should then connect to the slave manually, fix the cause of the
error (for example, non-existent table), and then run `START
SLAVE'.
* If the connection to the master is lost, the slave will try to
reconnect immediately. If that fails, the slave will retry every
`master-connect-retry' seconds (default 60). Because of this, it
is safe to shut down the master, and then restart it after a
while. The slave will also be able to deal with network
connectivity outages. However, the slave will notice the network
outage only after receiving no data from the master for
`slave_net_timeout' seconds. So if your outages are short, you may
want to decrease `slave_net_timeout'. *Note `SHOW VARIABLES':
SHOW 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 synced before the system died.
Your system fault tolerance will be greatly increased if you have
a good UPS.
* Due to the non-transactional nature of MyISAM tables, it is
possible to have a query that will only partially update a table
and return an error code. This can happen, for example, on a
multi-row insert that has one row violating a key constraint, or
if a long update query 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 query execution results in the same error code.
If this error code validation behaviour 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 commited.
* 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 while
transactional updates are written on `COMMIT' or not written at
all if you use `ROLLBACK'; you have to take this into account when
updating both transactional tables and non-transactional tables in
the same transaction if you are using binary logging for backups or
replication. In version 4.0.15, we changed the logging behaviour
for transactions that mix updates to transactional and
non-transactional tables, which solves the problems (order of
queries is good in binlog, and all needed queries are written to
the binlog even in case of `ROLLBACK'). The problem which remains
is when a second connection updates the non-transactional table
while the first connection's transaction is not finished yet
(wrong order can still occur, because the second connection's
update will be written immediately after it is done).
The following table lists problems in MySQL 3.23 that are fixed in
MySQL 4.0:
* `LOAD DATA INFILE' is handled properly, as long as the data file
still resides on the master server at the time of update
propagation.
* `LOAD LOCAL DATA INFILE' will be skipped.
argument to `RAND()'. This is fixed in 4.0.
Replication Startup Options
---------------------------
On both the master and the slave you need to use the `server-id' option
to establish a unique replication ID for each server. You should pick a
unique integer in the range from 1 to 2^32 - 1 for each master and
slave. Example: `server-id=3'
The options that you can use on the master server for controlling binary
logging are all described in *Note Binary log::.
The following table describes the options you can use on slave servers.
You can specify them on the command line or in an option file.
*NOTE*: Replication handles the following options in a special way:
* `--master-host'
* `--master-user'
* `--master-password'
* `--master-port'
* `--master-connect-retry'
a
replication slave for the very first time, or you have run `RESET
SLAVE' and shut down and restarted the slave server.
However, if the `master.info' file exists when the slave server starts,
it uses the values in the file and *IGNORES* any values specified for
those options in option files or on the command line.
Suppose you specify this option in your `my.cnf' file:
[mysqld]
master-host=this_host
The first time you start the server as a replication slave, it will
read and use that option from the `my.cnf' file. The server will then
record that value in the `master.info' file. The next time you start
the server, it will read the master host value from the `master.info'
file only. If you modify the `my.cnf' file to specify a different
master host, it will have no effect. You must use `CHANGE MASTER TO'
instead.
As of MySQL 4.1.1, the following options also are handled specially:
* `--master-ssl'
* `--master-ssl-ca'
* `--master-ssl-capath'
* `--master-ssl-cert'
* `--master-ssl-cipher'
* `--master-ssl-key'
to
4.1.1, the `master.info' will be upgraded to the new format
automatically when the new server starts. (If you downgrade a 4.1.1 or
newer server to a version older than 4.1.1, you should manually remove
the first line before starting the older server for the first time.)
Because the server gives an existing `master.info' file precedence over
the startup options just described, you might prefer not to use startup
options for these values at all, and instead specify them by using the
`CHANGE MASTER TO' statement. See *Note `CHANGE MASTER TO': CHANGE
MASTER TO.
This example shows a more extensive use of startup options to configure
a slave server:
[mysqld]
server-id=2
master-host=db-master.mycompany.com
master-port=3306
master-user=pertinax
master-password=freitag
master-connect-retry=60
report-host=db-slave.mycompany.com
The following list describes startup options for controlling
replication:
slave must be started with binary
logging enabled (`--log-bin' option). `--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, where B is both a
master and a slave, you must start B with the
`--log-slave-updates' option. A and B must both be started with
binary logging enabled.
`--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.
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.
(prior to MySQL 4.0.2, it
must have the `FILE' privilege instead). If the master user is not
set, user `test' is assumed. The value in `master.info' takes
precedence if it can be read.
`--master-password=password'
The password of the account that the slave thread uses for
authentication when connecting to the master. If not set, an
empty password is assumed. The value in `master.info' takes
precedence if it can be read.
`--master-port=port_number'
The port the master is listening on. If not set, the compiled
setting of `MYSQL_PORT' is assumed. If you have not tinkered with
`configure' options, this should be 3306. The value in
`master.info' takes precedence if it can be read.
`--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. Default is 60. The value in `master.info'
takes precedence if it can be read.
`--master-info-file=filename'
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-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
*Note SSL options::.
These options are operational as of MySQL 4.1.1.
`--max-relay-log-size=#'
To rotate the relay log automatically. *Note `SHOW VARIABLES':
SHOW VARIABLES.
`--relay-log=filename'
To specify the location and name that should be used for relay
logs. You can use this to have 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=filename'
To specify the location and name that should be used for the relay
logs index file.
`--relay-log-info-file=filename'
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/enables automatic purging of relay logs as soon as they
are not needed any more. This is a global variable that can be
dynamically changed with `SET GLOBAL RELAY_LOG_PURGE=0|1'. The
default value is 1.
This option is available as of MySQL 4.1.1.
`--relay-log-space-limit=#'
To put an upper limit on the total size of all relay logs on the
slave (a value of 0 means "unlimited"). This is useful if you have
a small hard disk on your slave machine. When the limit is
reached, the I/O thread pauses (does not read the master's binlog)
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 overgo the limit until deletion
becomes possible. Not doing so would cause a deadlock (which
happens before MySQL 4.0.13). Users 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) because in that case there are
chances that when the I/O thread waits for free space because
`--relay-log-space-limit' is exceeded, the SQL thread has no relay
log to purge and so cannot satisfy the I/O thread, forcing the I/O
thread to temporarily ignore `--relay-log-space-limit'.
`--replicate-do-table=db_name.table_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-table=db_name.table_name'
Tells the slave thread to not replicate any command that updates
the specified table (even if any other tables may be update by the
same command). 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.table_name'
Tells the slave thread to restrict replication to queries where
any of the updated tables match the specified wildcard pattern.
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 uses a table in any databases that start with `foo'
and whose table names start with `bar'.
Note that if you do `--replicate-wild-do-table=foo%.%' then the
rule will be propagated to `CREATE DATABASE' and `DROP DATABASE',
that is, these two statements will be replicated if the database
name matches the database pattern (`foo%' here) (this magic is
triggered by `%' being the table pattern).
`--replicate-wild-ignore-table=db_name.table_name'
Tells the slave thread to not replicate a query 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 do
updates to tables in databases that start with `foo' and whose
table names start with `bar'.
Note that if you do `--replicate-wild-ignore-table=foo%.%' then the
rule will be propagated to `CREATE DATABASE' and `DROP DATABASE',
that is, these two statements will not be replicated if the
database name matches the database pattern (`foo%' here) (this
magic is triggered by `%' being the table pattern).
`--replicate-do-db=database_name'
Tells the slave to restrict replication to commands where the
current database (that is, the one selected by `USE') is
`database_name'. To specify more than one database, use the
directive multiple times, once for each database. Note that this
will not replicate cross-database queries such as `UPDATE
some_db.some_table SET foo='bar'' while having selected a
different or no database. If you need cross database updates to
work, make sure you have 3.23.28 or later, and use
`--replicate-wild-do-table=db_name.%'. Please read the notes that
follow this option list.
Example of what does not work as you could expect it: if the slave
is started with `--replicate-do-db=sales', and you do `USE prices;
UPDATE sales.january SET amount=amount+1000;', this query will not
be replicated.
If you need cross database updates to work, use
`--replicate-wild-do-table=db_name.%' instead.
multi-table-delete or multi-table-update commands that go across
multiple databases. It's also very fast to just check the current
database.
`--replicate-ignore-db=database_name'
Tells the slave to not replicate any command where the current
database (that is, the one selected by `USE') is `database_name'.
To specify more than one database to ignore, use the directive
multiple times, once for each database. You should not use this
directive if you are using cross table updates and you don't want
these update to be replicated. Please read the notes that follow
this option list.
Example of what does not work as you could expect it: if the slave
is started with `--replicate-ignore-db=sales', and you do `USE
prices; UPDATE sales.january SET amount=amount+1000;', this query
will be replicated.
If you need cross database updates to work, use
`--replicate-wild-ignore-table=db_name.%' instead.
`--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 may be affected (`CREATE
DATABASE', `DROP DATABASE' won't), and only if `from_name' was the
current database on the master. This will not work for
cross-database updates. Note that the translation is done before
`--replicate-*' rules are tested.
Example: `replicate-rewrite-db=master_db_name->slave_db_name'
`--report-host=host'
The hostname or IP number of the slave to be reported to the
master during slave registration. Will appear in the output of
`SHOW SLAVE HOSTS'. Leave unset if you do not want the slave to
register itself with the master. Note that it is not sufficient
for the master to simply read the IP number of the slave from the
TCP/IP socket once the slave connects. Due to `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 slave 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 on server
startup. The user can start them later with `START SLAVE'.
`--slave_compressed_protocol=#'
If 1, then use compression on the slave/client protocol if both
slave and master support this.
`--slave-load-tmpdir=filename'
This option is by default equal to the value of the `tmpdir'
variable. When the slave SQL thread replicates a `LOAD DATA
INFILE' command, it extracts the to-be-loaded file from the relay
log into temporary files, then loads these into the table. If the
file loaded on the master was huge, the temporary files on the
slave will be huge, too; therefore you may wish/have to tell the
slave to put the temporary files on some large disk different from
`tmpdir', using this option. In that case, you may also use the
`--relay-log' option, as relay logs will be huge, too.
`--slave-load-tmpdir' should point to a disk-based filesystem; not
a memory-based one. Because the slave needs the temporary files
used to replicate `LOAD DATA INFILE') to survive a machine's
reboot.
`--slave-net-timeout=#'
Number of seconds to wait for more data from the master before
aborting the read, considering the connection broken and retrying
to connect. The first retry occurs immediately after timeout. The
interval between retries is controlled by the
`--master-connect-retry' option.
`--slave-skip-errors= [err_code1,err_code2,... | all]'
Tells the slave SQL thread to continue replication when a query
returns an error from the provided list. Normally, replication
will discontinue when an error is encountered, giving the user a
chance to resolve the inconsistency in the data manually. 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, you should never get
an abort with error. Indiscriminate use of this option will result
in slaves being hopelessly out of sync with the master and you
having no idea how the problem happened.
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 *Note 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. 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
Some of these options, like all `--replicate-*' options, can only be
set at the slave server's startup, not on-the-fly. We plan to fix this.
rules?
* Yes: test them like for `--binlog-do-db' and
`--binlog-ignore-db' (*note Binary log::). What is the result
of the test?
* ignore the query: ignore it and exit.
* execute the query: don't execute it immediately, defer
the decision, go to step below.
* No: go to step below.
2. Are there some `--replicate-*-table' rules?
* No: execute the query and exit.
tables are to be updated (multi-table statement), the first
matching table (matching "do" or "ignore") wins (that is, the
first table is compared to rules, then if no decision could
be taken the second table is compared to rules, etc).
3. Are there some `--replicate-do-table' rules?
* Yes: does the table match any of them?
* Yes: execute the query and exit.
* No: go to step below.
* No: go to step below.
4. Are there some `--replicate-ignore-table' rules?
* Yes: does the table match any of them?
* Yes: ignore the query and exit.
* No: go to step below.
* No: go to step below.
5. 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 step below.
* No: go to step below.
6. 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 step below.
* No: go to step below.
7. No `--replicate-*-table' rule was matched. Is there another table
to test against these rules?
* Yes: loop.
and exit.
* No: execute the query and exit.
SQL Statements for Controlling Master Servers
---------------------------------------------
servers.
`PURGE MASTER LOGS'
...................
PURGE {MASTER|BINARY} LOGS TO 'log_name'
PURGE {MASTER|BINARY} LOGS BEFORE 'date'
now
becomes the first.
Example:
PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';
If you have an active slave that is currently reading one of the logs
you are trying to delete, this command does nothing and fails with an
error. However, if you have a dormant slave, and happen to purge one of
the logs it wants to read, the slave will be unable to replicate once it
comes up. The command is safe to run while slaves are replicating. You
do not need to stop them.
You must first check all the slaves with `SHOW SLAVE STATUS' to see
which log they are reading, then do a listing of the logs on the master
with `SHOW MASTER LOGS', find the earliest log among all the slaves (if
all the slaves are up to date, this will be the last log on the list),
backup all the logs you are about to delete (optional) and purge up to
the target log.
`RESET MASTER'
..............
RESET MASTER
Deletes all binary logs listed in the index file, resetting the binlog
index file to be empty.
This statement was named `FLUSH MASTER' before MySQL 3.23.26.
`SET SQL_LOG_BIN'
.................
SET SQL_LOG_BIN = {0|1}
Disables or enables binary logging for the current connection
(`SQL_LOG_BIN' is a session variable) if the client connects using an
account that has the `SUPER' privilege. The statement is ignored if
the client does not have that privilege.
`SHOW BINLOG EVENTS'
....................
SHOW BINLOG EVENTS [ IN 'log_name' ] [ FROM pos ] [ LIMIT [offset,] row_count ]
Shows the events in the binary log. If you do not specify
`'log_name'', the first binary log will be displayed.
This statement is available as of MySQL 4.0
`SHOW MASTER STATUS'
....................
SHOW MASTER STATUS
Provides status information on the binlog of the master.
`SHOW MASTER LOGS'
..................
SHOW MASTER LOGS
Lists the binary logs on the master. You should use this command before
using `PURGE MASTER LOGS' to find out how far you should go.
`SHOW SLAVE HOSTS'
..................
SHOW SLAVE HOSTS
Displays a list of slaves currently registered with the master.
SQL Statements for Controlling Slave Servers
--------------------------------------------
Replication can be controlled through the SQL interface. This section
discusses statements for managing slave replication servers. *Note
Replication Master SQL:: discusses statements for managing master
servers.
`CHANGE MASTER TO'
..................
CHANGE MASTER TO master_def [, master_def] ...
master_def =
MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = count
| MASTER_LOG_FILE = 'master_log_name'
| MASTER_LOG_POS = master_log_pos
| RELAY_LOG_FILE = 'relay_log_name'
| RELAY_LOG_POS = relay_log_pos
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
`CHANGE MASTER' *is a "brutal" command. Before using it in production,
you should read this entire description.*
Changes the parameters that the slave server uses for connecting to and
communicating with the master server. The possible `master_def' values
are shown above.
The relay log options (`RELAY_LOG_FILE' and `RELAY_LOG_POS') are
available beginning with MySQL 4.0.
even on slaves that are compiled without SSL support. They will be
saved to the `master.info' file but ignored until you use a server that
has SSL support enabled.
For example:
mysql> CHANGE MASTER TO
-> MASTER_HOST='master2.mycompany.com',
-> MASTER_USER='replication',
-> MASTER_PASSWORD='bigs3cret',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master2-bin.001',
-> MASTER_LOG_POS=4,
-> MASTER_CONNECT_RETRY=10;
mysql> CHANGE MASTER TO
-> RELAY_LOG_FILE='slave-relay-bin.006',
-> RELAY_LOG_POS=4025;
master server
is different than before. (This is true even if you specify a host or
port value value that is the same as the current value.) In this case,
the old values of master binlog name and position are considered no
longer applicable, so if you do not specify `MASTER_LOG_FILE' and
`MASTER_LOG_POS' in the command, `MASTER_LOG_FILE=''' and
`MASTER_LOG_POS=4' are silently appended to it.
(see below),
these are also the coordinates from which the slave SQL thread will
begin executing the next time it starts.
global
variable will silently be set to 0). `CHANGE MASTER TO' updates
`master.info' and `relay-log.info'.
then as
`CHANGE MASTER' deletes relay logs containing these non-executed
queries, and so replication then restarts *from the coordinates of the
I/O thread*, *the SQL thread will have "leapt" over the non-executed
queries*. Therefore, unless these queries were not important, you
should do one of the following before issuing `CHANGE MASTER':
* Ensure that the slave SQL thread has read all existing relay logs.
You can achieve this by stopping only the slave I/O thread (`STOP
SLAVE IO_THREAD'), then monitoring the progress of the running
slave SQL thread with `SHOW SLAVE STATUS' and `SELECT
MASTER_POS_WAIT()', until it has caught up. This way there will be
no leap for the slave SQL thread.
* Run `STOP SLAVE', check where the slave SQL thread is in the
master's binlog (using `SHOW SLAVE STATUS', columns
`Relay_Master_Log_File' and `Exec_master_log_pos'), and add a
specification of these coordinates to the `CHANGE MASTER' command
(`MASTER_LOG_FILE=..., MASTER_LOG_POS=...'). This way, you will
instruct the slave I/O thread to start replication from the former
coordinates of the slave SQL thread, so there will be no leap for
the slave SQL thread.
If you don't take care of this issue, even a simple `STOP SLAVE; CHANGE
MASTER TO MASTER_USER='repl'; START SLAVE;' run in the middle of a
highly-loaded replication could break this replication and spoil the
slave's data.
`CHANGE MASTER' is useful for setting up a slave when you have the
snapshot of the master and have recorded the log and the offset on the
master that the snapshot corresponds to. You can run `CHANGE MASTER TO
MASTER_LOG_FILE='log_name_on_master',
MASTER_LOG_POS=log_offset_on_master' on the slave after restoring the
snapshot.
second example, less frequently used, is
when the slave has relay logs which, for some reason, you want the
slave to execute again; to do this the master needn't be reachable, you
just have to do `CHANGE MASTER TO' and start the SQL thread (`START
SLAVE SQL_THREAD'). You can even use this out of a replication setup,
on a standalone, slave-of-nobody server, to recover after a crash.
Suppose your server has crashed and you have restored a backup. You
want to replay the server's own binlogs (not relay logs, but regular
binary logs), supposedly named `myhost-bin.*'. First make a backup copy
of these binlogs in some safe place, in case you don't exactly follow
the procedure below and accidentally have the server purge the binlogs.
If using MySQL 4.1.1 or newer, do `SET GLOBAL RELAY_LOG_PURGE=0' for
additional safety. Then start the server without `log-bin', with a new
(different from before) server ID, with `relay-log=myhost-bin' (to make
the server believe that these regular binlogs are relay logs) and
`skip-slave-start', then issue these statements:
mysql> CHANGE MASTER TO
-> RELAY_LOG_FILE='myhost-bin.153',
-> RELAY_LOG_POS=410,
-> MASTER_HOST='some_dummy_string';
mysql> START SLAVE SQL_THREAD;
and
restart the server with its original options. For the moment,
specifying `MASTER_HOST' (even with a dummy value) is compulsory to
make the server think it is a slave, and giving the server a new,
different from before, server ID is also compulsory otherwise the
server will see events with its ID and think it is in a circular
replication setup and skip the events, which is unwanted. In the future
we plan to add options to get rid of these small constraints.
`LOAD DATA FROM MASTER'
.......................
LOAD DATA FROM MASTER
table and
database exclusion rules specified with `replicate-*' options.
Use of this statement is subject to the following conditions:
* It works only with `MyISAM' tables.
* It acquires a global read lock on the master while taking the
snapshot, which prevents updates on the master during the load
operation.
In the future, it is planned to make this statement work with `InnoDB'
tables and to remove the need for global read lock by using the
non-blocking online backup feature.
If you are loading big tables, you may have to increase the values of
`net_read_timeout' and `net_write_timeout' on both your master and
slave. See *Note `SHOW VARIABLES': SHOW VARIABLES.
Note that `LOAD DATA FROM MASTER' does *NOT* copy any tables from the
`mysql' database. This is to make it easy to have different users and
privileges on the master and the slave.
All
master's tables on which the user has no `SELECT' privilege will be
ignored by `LOAD DATA FROM MASTER'; this is because the master will
hide them to the user: `LOAD DATA FROM MASTER' calls `SHOW DATABASES'
to know the master databases to load, but `SHOW DATABASES' returns only
databases on which the user has some privilege. See *Note Show
database info::. On the slave's side, the user which issues `LOAD DATA
FROM MASTER' should have grants to drop and create the involved
databases and tables.
`LOAD TABLE tbl_name FROM MASTER'
.................................
LOAD TABLE tbl_name FROM MASTER
have
`RELOAD' and `SUPER' privileges on the master, and `SELECT' on the
master table to load. On the slave's side, the user which issues `LOAD
TABLE FROM MASTER' should have grants to drop and create the table.
Please read the timeout notes in the description of `LOAD DATA FROM
MASTER' below; they apply here, too.
`MASTER_POS_WAIT()'
...................
SELECT MASTER_POS_WAIT('master_log_file', master_log_pos)
This is a function, not a command. It is used to ensure that the slave
has reached (read and executed up to) a given position in the master's
binlog. See *Note Miscellaneous functions:: for a full description.
`RESET SLAVE'
.............
RESET SLAVE
Makes the slave forget its replication position in the master's binlogs.
This statement deletes the `master.info' and `relay-log.info' files,
all the relay logs, and starts a new relay log. *Note:* All relay logs
are deleted, even if they had not been totally executed by the slave
SQL thread. (This is a condition likely to exist on a replication
slave that is highly-loaded, or if you have issued a `STOP SLAVE'
statement.) Connection information stored in the `master.info' file is
immediately reset to the values specified in the corresponding startup
options, if they were specified. This information includes values such
as master host, master port, master user, and master password.
This statement was named `FLUSH SLAVE' before MySQL 3.23.26.
`SET GLOBAL SQL_SLAVE_SKIP_COUNTER'
...................................
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n
Skip the next `n' events from the master. This is useful for recovering
from replication stops caused by a statement.
This statement is valid only when the slave thread is not running.
Otherwise, it produces an error.
Before MySQL 4.0, omit the `GLOBAL' keyword from the statement.
`SHOW SLAVE STATUS'
...................
SHOW SLAVE STATUS
more
readable vertical layout:
mysql> Master_User: root
Master_Port: 3306
Connect_retry: 3
Master_Log_File: gbichot-bin.005
Read_Master_Log_Pos: 79
Relay_Log_File: gbichot-relay-bin.005
Relay_Log_Pos: 548
Relay_Master_Log_File: gbichot-bin.005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_do_db:
Replicate_ignore_db:
Last_errno: 0
Last_error:
Skip_counter: 0
Exec_master_log_pos: 79
Relay_log_space: 552
Until_condition: None
Until_Log_File:
Until_Log_pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_behind_master: 8
Depending on your version of MySQL, you may not see all the fields just
shown. In particular, several fields are present only as of MySQL 4.1.1.
The fields displayed by `SHOW SLAVE STATUS' have the following meanings:
`Slave_IO_State'
A copy of the `State' column of the output of `SHOW PROCESSLIST'
for the slave I/O thread; will tell you if this thread is trying
to connect to the master, waiting for events from the master,
reconnecting to the master, etc. Possible states are listed in
*Note Replication Implementation::. Looking at this column is
necessary because, for example, the thread can be running but
unsuccessfully trying to connect to the master: only this column
will make you aware of the connection problem. On the opposite,
the state of the SQL thread is not copied, because things are
simpler for this thread: if it's running, there is no problem; if
it's not, you will find the error in the `Last_error' column
(described below).
This field is present beginning with MySQL 4.1.1.
`Master_Host'
The current master host.
`Master_User'
The current user used to connect to the master.
`Master_Port'
The current master port.
`Connect_Retry'
The current value of `master-connect-retry'.
`Master_Log_File'
The name of the master's binlog file from which the I/O thread is
currently reading.
`Read_Master_Log_Pos'
The position which the I/O thread has read up to in this master's
binlog.
`Relay_Log_File'
The name of the relay log file from which the SQL thread is
currently reading and executing.
`Relay_Log_Pos'
The position which the SQL thread has read and executed up to in
this relay log.
`Relay_Master_Log_File'
The name of the master's binlog file that contains the last event
executed by the SQL thread.
`Slave_IO_Running'
Tells whether or not the I/O thread is started.
`Slave_SQL_Running'
Tells whether or not the SQL thread is started.
`Replicate_do_db, Replicate_ignore_db'
The lists of the databases that were specified with the
`--replicate-do-db' and `--replicate-ignore-db' options, if any
`--replicate-wild-do-table', and `--replicate-wild-ignore_table'
options, if any
These fields are present beginning with MySQL 4.1.1.
`Last_errno'
The error number returned by the most recently executed query. A
value of 0 means "no error".
`Last_error'
The error message returned by the most recently executed query.
For example:
Last_errno: 1051
Last_error: error 'Unknown table 'z'' on query 'drop table z'
to
copy the table to the slave when setting up replication.)
The empty string means "no error". If the `Last_error' value is
not empty, it will also appear as a message in the slave's error
log.
`Skip_counter'
The last used value for `SQL_SLAVE_SKIP_COUNTER'.
`Exec_master_log_pos'
The position in the master's binlog (`Relay_Master_Log_File') of
the last event executed by the SQL thread.
((`Relay_Master_Log_File',`Exec_master_log_pos') in the master's
binlog corresponds to (`Relay_Log_File',`Relay_Log_Pos') in the
relay log).
`Relay_log_space'
The total combined size of all existing relay logs.
`Until_condition, Until_Log_File, Until_Log_pos'
The values specified in the `UNTIL' clause of the `START SLAVE'
statement.
`Until_condition' has these values:
* `None' if no `UNTIL' clause was specified
* `Master' if the slave is reading until a given position in
the master's binlogs
* `Relay' if the slave is reading until a given position in its
relay logs
`Until_Log_File' and `Until_Log_pos' indicate the log filename and
position values that define the point at which the SQL thread will
stop executing.
These fields are present beginning with MySQL 4.1.1.
`Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_CA_Path, Master_SSL_Cert, Master_SSL_Cipher, Master_SSL_Key'
These fields show the The SSL parameters used by the slave to
connect to the master, if any.
`Master_SSL_Allowed' has these values:
* `Yes' if an SSL connection to the master is allowed
* `No' if an SSL connection to the master is not allowed
* `Ignored' if an SSL connection is allowed by the slave server
does not have SSL support enabled
The values of the other fields correspond to the values of the
`--master-ca', `--master-capath', `--master-cert',
`--master-cipher', and `--master-key' options.
These fields are present beginning with MySQL 4.1.1.
executed yet, or after `CHANGE
MASTER' and `RESET SLAVE'. This column can be used to know how
"late" your slave is. It will work even though your master and
slave don't have identical clocks.
This field is present beginning with MySQL 4.1.1.
`START SLAVE'
.............
START SLAVE [thread_name [, thread_name] ... ]
START SLAVE [SQL_THREAD] UNTIL
MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
START SLAVE [SQL_THREAD] UNTIL
RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
thread_name = IO_THREAD | SQL_THREAD
`START SLAVE' with no options starts both of the slave threads. The
I/O thread reads queries from the master server and stores them in the
relay log. The SQL thread reads the relay log and executes the queries.
Note that if `START SLAVE' succeeds in starting the slave threads it
will return without any error. But even in that case it might be that
slave threads start and then later stop (because they don't manage to
connect to the master or read his binlogs or any other problem). `START
SLAVE' will not warn you about this. You must check your slave's error
log for error messages generated by the slave threads, or check that
these are running fine with `SHOW SLAVE STATUS'.
As of MySQL 4.0.2, you can add `IO_THREAD' or `SQL_THREAD' options to
the statement to name which of the threads to start.
As of MySQL 4.1.1, an `UNTIL' clause may be added to specify that the
slave should start until the SQL thread reaches a given point in the
master binlogs or in the slave relay longs. When the SQL thread reaches
that point, it stops. If the `SQL_THREAD' option is specified in the
statement, it starts only the SQL thread. Otherwise, it starts both
slave threads. If the SQL thread is already running, the `UNTIL'
clause is ignored and a warning is issued.
With an `UNTIL' clause, you must specify both a log filename and
position. Do not mix master and relay log options.
Any `UNTIL' condition is reset by a subsequent `STOP SLAVE' statement,
or a `START SLAVE' statement that includes no `UNTIL' clause, or a
server restart.
if an unwise
`DROP TABLE' statement was executed on the master, you can use `UNTIL'
to tell the slave to execute up to that point but no farther. To find
what the event is, use `mysqlbinlog' with the master logs or relay
logs, or by using a `SHOW BINLOG EVENTS' statement.
If you are using `UNTIL' to have the slave process replicated queries in
sections, it is recommended that you start the slave with the
`--skip-slave-start' option to prevent the SQL thread from running when
the slave starts. It's probably best to use this option in an option
file rather than on the command line, so that an unexpected server
restart does not cause it to be forgotten.
The `SHOW SLAVE STATUS' statement includes output fields that display
the current values of the `UNTIL' condition.
This command is called `SLAVE START' before MySQL 4.0.5. For the
moment, `SLAVE START' is still accepted for backward compatibility, but
is deprecated.
`STOP SLAVE'
............
STOP SLAVE [thread_name [, thread_name] ... ]
thread_name = IO_THREAD | SQL_THREAD
Stops the slave threads. Like `START SLAVE', this statement may be
used with the `IO_THREAD' and `SQL_THREAD' options to name the thread
or threads to stop.
This command is called `SLAVE STOP' before MySQL 4.0.5. For the
moment, `SLAVE STOP' is still accepted for backward compatibility, but
is deprecated.
Replication FAQ
---------------
*Q*: How do I configure a slave if the master is already running and I
do not want to stop it?
do the
following:
1. Make sure the slave is assigned a unique server ID.
2. 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;
3. 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:
1. `FLUSH TABLES WITH READ LOCK'
2. `gtar zcf /tmp/backup.tar.gz /var/lib/mysql' (or a variation of
this)
3. `SHOW MASTER STATUS' - make sure to record the output - you will
need it later
4. `UNLOCK TABLES'
An alternative is taking an SQL dump of the master instead of a binary
copy like above; for this you can use `mysqldump --master-data' on your
master and later run this SQL dump into your slave. However, this is
slower than makeing a binary copy.
No matter which of the two methods you use, afterwards follow the
instructions for the case when you have a snapshot and have recorded
the log name and offset. You can use the same snapshot to set up
several slaves. As long as the binary logs of the master are left
intact, you can wait as long as several days or in some cases maybe a
month to set up a slave once you have the snapshot of the master. In
theory the waiting gap can be infinite. The two practical limitations
is the diskspace of the master getting filled with old logs, and the
amount of time it will take the slave to catch up.
You can also use `LOAD DATA FROM MASTER'. This is a convenient command
that takes a snapshot, restores it to the slave, and adjusts the log
name and offset on the slave all at once. In the future, `LOAD DATA
FROM MASTER' will be the recommended way to set up a slave. Be warned,
howerver, that the read lock may be held for a long time if you use
this command. It is not yet implemented as efficiently as we would like
to have it. If you have large tables, the preferred method at this time
is still with a local `tar' snapshot after executing `FLUSH TABLES WITH
READ LOCK'.
*Q*: Does the slave need to be connected to the master all the time?
*A*: No, it does not. The slave can go down or stay disconnected for
hours or even days, then reconnect and catch up on the updates. For
example, you can set up a master/slave relationship over a dial-up link
where the link is up only sporadically and for short periods of time.
The implication of this is that at any given time the slave is not
guaranteed to be in sync with the master unless you take some special
measures. In the future, we will have the option to block the master
until at least one slave is in sync.
*Q*: How do I know how late a slave is compared to the master? In other
words, how do I know the date of the last query replicated by the slave?
is, if it shows up in `SHOW PROCESSLIST', *note Replication
Implementation Details::) (in MySQL 3.23: if the slave thread exists,
that is, shows up in `SHOW PROCESSLIST'), and if it has executed at
least one event from the master. Indeed, when the slave SQL thread
executes an event read from the master, this thread modifies its own
time to the event's timestamp (this is why `TIMESTAMP' is well
replicated). So in the `Time' column in the output of `SHOW
PROCESSLIST', the number of seconds displayed for the slave SQL thread
is the number of seconds between the timestamp of the last replicated
event and the real time of the slave machine. You can use this to
determine the date of the last replicated event. Note that if your
slave has been disconnected from the master for one hour, then
reconnects, you may immediately see `Time' values like 3600 for the
slave SQL thread in `SHOW PROCESSLIST'... This would be because the
slave is executing queries that are one hour old.
*Q*: How do I force the master to block updates until the slave catches
up?
*A*: Use the following procedure:
1. On the master, execute these commands:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
Record the log name and the offset from the output of the `SHOW'
statement.
2. On the slave, issue this command, where the replication
coordinates that are the arguments to the `MASTER_POS_WAIT()'
function are the values recorded in the previous step:
mysql> SELECT MASTER_POS_WAIT('log_name', log_offset);
3. 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 realise 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 serialised in one slave thread. Even so, this benefit might be
offset by network delays.
*Q*: How can I use replication to improve performance of my system?
*A*: You should set up one server as the master and direct all writes
to it. Then configure as many slaves as you have the money and
rackspace for, and distribute the reads among the master and the slaves.
You can also start the slaves with `--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 the the
master, and to send reads to either the master or a slave. If your
code does not have this level of abstraction, setting up a replicated
system will give you the opportunity and motivation to it clean up.
You should start by creating a wrapper library or module with the
following functions:
* `safe_writer_connect()'
* `safe_reader_connect()'
* `safe_reader_query()'
* `safe_writer_query()'
unified
interface for connecting for reads, connecting for writes, doing a
read, and doing a write.
You should then convert your client code to use the wrapper library.
This may be a painful and scary process at first, but it will pay off in
the long run. All applications that use the approach just described
will be able to take advantage of a master/slave configuration, even
one involving multiple slaves. The code will be a lot easier to
maintain, and adding troubleshooting options will be trivial. You will
just need to modify one or two functions, for example, to log how long
each query took, or which query, among your many thousands, gave you an
error.
If you have written a lot of code already, you may want to automate the
conversion task by using the `replace' utility that comes with the
standard distribution of MySQL, or just write your own Perl script.
Hopefully, your code follows some recognisable pattern. If not, then
you are probably better off rewriting it anyway, or at least going
through and manually beating it into a pattern.
*Q*: When and how much can MySQL replication improve the performance of
my system?
*A*: MySQL replication is most beneficial for a system with frequent
reads and infrequent writes. In theory, by using a
single-master/multiple-slave setup, you can scale the system by adding
more slaves until you either run out of network bandwidth, or your
update load grows to the point that the master cannot handle it.
In order to determine how many slaves you can get before the added
benefits begin to level out, and how much you can improve performance
of your site, you need to know your query patterns, and empirically
(by benchmarking) determine the relationship between the throughput on
reads (reads per second, or `max_reads') and on writes (`max_writes')
on a typical master and a typical slave. The example here will show you
a rather simplified calculation of what you can get with replication
for a hypothetical system.
the
average write is twice as slow as average read, and the relationship is
linear. Let us suppose that the master and each slave have the same
capacity, and that we have 1 master and N slaves. Then we have for each
server (master or slave):
`reads = 1200 - 2 * writes' (from benchmarks)
`reads = 9* writes / (N + 1) ' (reads split, but writes go to all
servers)
`9*writes/(N+1) + 2 * writes = 1200'
`writes = 1200/(2 + 9/(N+1)'
This analysis yields the following conclusions:
* If N = 0 (which means we have no replication), our system can
handle 1200/11, about 109 writes per second (which means we will
have 9 times as many reads due to the nature of our application).
* 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.
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 one above that will accurately predict what will happen
on your system if you add N replication slaves. However, answering the
following questions should help you decide whether and how much
replication will improve the performance of your system:
* What is the read/write ratio on your system?
* How much more write load can one server handle if you reduce the
reads?
* How many slaves do you have bandwidth available for 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 if it is up, and instruct your applications
and the slaves of the master change in case of failure. Some
suggestions:
* To tell a slave to change the master, use the `CHANGE MASTER TO'
command.
* 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.
* You should run your slaves with the `--log-bin' option and without
`--log-slave-updates'. This way the slave will be ready to become a
master as soon as you issue `STOP SLAVE'; `RESET MASTER', and
`CHANGE MASTER TO' on the other slaves. For example, consider you
have the following setup ("M" means the master, "S" the slaves,
"WC" the clients that issue database writes and reads; clients
that issue only database reads are not represented, because they
need not switch):
WC
\
v
WC----> M
/ | \
/ | \
v v v
S1 S2 S3
S1 (like S2 and S3) is a slave running with `--log-bin' and
without `--log-slave-updates'. As the only writes executed on S1
are those replicated from M, the binary log on S1 is *empty*
(remember, S1 runs without `--log-slave-updates'). Then, for some
reason, M becomes unavailable, and you want S1 to become the new
master (that is, direct all WC to S1, and make S2 and S3 replicate
S1). No WC accesses M. Instruct all WC to direct their queries to
S1. From now on, all queries sent by WC to S1 are written to the
binary log of S1. The binary log of S1 contains exactly every
writing query sent to S1 since M died. On S2 (and S3) do `STOP
SLAVE', `CHANGE MASTER TO MASTER_HOST='S1'' (where `'S1'' is
replaced by the real hostname of S1). To `CHANGE MASTER', add all
information about how to connect to S1 from S2 or S3 (user,
password, port). In `CHANGE MASTER', no need to specify the name
of S1's binary log or binary log position to read from: we know it
is the first binary log, from position 4, and these are the
defaults of `CHANGE MASTER'. Finally do `START SLAVE' on S2 and
S3, and now you have this:
WC
/
|
WC | M(unavailable)
\ |
\ |
v v
S1<--S2 S3
^ |
+-------+
When M is up again, you just have to issue on it the same `CHANGE
MASTER' as the one issued on S2 and S3, so that M becomes a slave
of S1 and picks all the WC writes it has missed while it was down.
Now to make M a master again (because it is the most powerful
machine, for example), follow the preceding procedure as if S1 was
unavailable and M was to be the new master; then during the
procedure don't forget to run `RESET MASTER' on M before making
S1, S2, S3 slaves of M, or they may pick old WC writes from before
M's unavailibility.
We are currently working on integrating an automatic master election
system into MySQL, but until it is ready, you will have to create your
own monitoring tools.
Troubleshooting Replication
---------------------------
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.
`server-id'.
* Is the slave running? Do `SHOW SLAVE STATUS' and check that the
`Slave_IO_Running' and `Slave_SQL_Running' values are both `Yes'.
If not, verify slave options
* If the slave is running, did it establish a connection with the
master? Do `SHOW PROCESSLIST', find the I/O and SQL threads (*note
Replication Implementation Details:: to see how they display), and
check their `State' column. If it says `Connecting to master',
verify the privileges for the replication user on the master,
master host name, 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 query 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; read below
on how to report it.
* If a query on that succeeded on the master refuses to run on the
slave, and it does not feasible to do a full database resync (that
is, to delete the slave's database and copy a new snapshot from
the master), try the following:
- First see if the slave's table was different from the
master's. Understand how it happened (it may be a bug: read
the Changelogs in the online MySQL manual as
`http://www.mysql.com/documentation' to check if this is a
known bug and if it is fixed yet). Then make the slave's
table identical to the master's and run `START SLAVE'.
- If the above does not work or does not apply, try to
understand if it would be safe to make the update manually
(if needed) and then ignore the next query from the master.
- If you have decided you can skip the next query, 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.
- Make sure you are not running into an old bug by upgrading to
the most recent version.
- If you are sure the slave started out perfectly in sync with
the master, and no one has updated the tables involved
outside of slave thread, report the bug.
Reporting Replication Bugs
--------------------------
as
possible from you to be able to track down the bug. Please do spend
some time and effort preparing a good bug report.
following procedure:
1. 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.
2. Run the slave with the `--log-slave-updates' and `--log-bin'
options. They will cause the slave to log the updates that it
receives in its own binlogs.
3. Save all evidence before resetting the replication state. If we
have no information or only sketchy information, it will take us
longer 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 on the slave
4. Use `mysqlbinlog' to examine the binary logs. The following should
be helpful to find the trouble query, for example:
mysqlbinlog -j pos_from_slave_status /path/to/log_from_slave_status | head
Once you have collected the evidence for the phantom problem, try hard
to isolate it into a separate test case first. Then enter the problem
into our bugs database at `http://bugs.mysql.com/' with as much
information as possible.
[Назад] [Содержание] [Вперед]
| Главная |