MySQL Enterprise. For expert advice on replication, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
Questions
16.4.4.1: Must the slave be connected to the master all the time?
16.4.4.2: Must I enable networking on my master and slave to enable replication?
16.4.4.3: How do I know how late a slave is compared to the master? In other words, how do I know the date of the last statement replicated by the slave?
16.4.4.4: How do I force the master to block updates until the slave catches up?
16.4.4.5: What issues should I be aware of when setting up two-way replication?
16.4.4.6: How can I use replication to improve performance of my system?
16.4.4.7: What should I do to prepare client code in my own applications to use performance-enhancing replication?
16.4.4.8: When and how much can MySQL replication improve the performance of my system?
16.4.4.9: How can I use replication to provide redundancy or high availability?
16.4.4.10: How do I tell whether a master server is using statement-based or row-based binary logging format?
16.4.4.11: How do I tell a slave to use row-based replication?
16.4.4.12:
How do I prevent GRANT
and
REVOKE
statements from
replicating to slave machines?
16.4.4.13: Does replication work on mixed operating systems (for example, the master runs on Linux while slaves run on Mac OS X and Windows)?
16.4.4.14: Does replication work on mixed hardware architectures (for example, the master runs on a 64-bit machine while slaves run on 32-bit machines)?
Questions and Answers
16.4.4.1: Must the slave be connected to the master all the time?
No, it does not. The slave can go down or stay disconnected for hours or even days, and then reconnect and catch up on updates. For example, you can set up a master/slave relationship over a dial-up link where the link is up only sporadically and for short periods of time. The implication of this is that, at any given time, the slave is not guaranteed to be in synchrony with the master unless you take some special measures.
To ensure that catchup can occur for a slave that has been disconnected, you must not remove binary log files from the master that contain information that has not yet been replicated to the slaves. Asynchronous replication can work only if the slave is able to continue reading the binary log from the point where it last read events.
16.4.4.2: Must I enable networking on my master and slave to enable replication?
Yes, networking must be enabled on the master and slave. If
networking is not enabled, the slave cannot connect to the
master and transfer the binary log. Check that the
skip-networking
option has
not been enabled in the configuration file for either
server.
16.4.4.3: How do I know how late a slave is compared to the master? In other words, how do I know the date of the last statement replicated by the slave?
Check the Seconds_Behind_Master
column in
the output from SHOW SLAVE
STATUS
. See
Section 16.1.4.1, “Checking Replication Status”.
When the slave SQL thread executes an event read from the
master, it modifies its own time to the event timestamp.
(This is why TIMESTAMP
is
well replicated.) In the Time
column in
the output of SHOW
PROCESSLIST
, the number of seconds displayed for
the slave SQL thread is the number of seconds between the
timestamp of the last replicated event and the real time of
the slave machine. You can use this to determine the date of
the last replicated event. Note that if your slave has been
disconnected from the master for one hour, and then
reconnects, you may immediately see large
Time
values such as 3600 for the slave
SQL thread in SHOW
PROCESSLIST
. This is because the slave is
executing statements that are one hour old. See
Section 16.2.1, “Replication Implementation Details”.
16.4.4.4: How do I force the master to block updates until the slave catches up?
Use the following procedure:
On the master, execute these statements:
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;
Record the replication coordinates (the current binary
log file name and position) from the output of the
SHOW
statement.
On the slave, issue the following statement, where the
arguments to the
MASTER_POS_WAIT()
function are the replication coordinate values obtained
in the previous step:
mysql> SELECT MASTER_POS_WAIT('log_name
', log_pos
);
The SELECT
statement
blocks until the slave reaches the specified log file
and position. At that point, the slave is in synchrony
with the master and the statement returns.
On the master, issue the following statement to allow the master to begin processing updates again:
mysql> UNLOCK TABLES;
16.4.4.5: What issues should I be aware of when setting up two-way replication?
MySQL replication currently does not support any locking protocol between master and slave to guarantee the atomicity of a distributed (cross-server) update. In other words, it is possible for client A to make an update to co-master 1, and in the meantime, before it propagates to co-master 2, client B could make an update to co-master 2 that makes the update of client A work differently than it did on co-master 1. Thus, when the update of client A makes it to co-master 2, it produces tables that are different from what you have on co-master 1, even after all the updates from co-master 2 have also propagated. This means that you should not chain two servers together in a two-way replication relationship unless you are sure that your updates can safely happen in any order, or unless you take care of mis-ordered updates somehow in the client code.
You should also realize that two-way replication actually does not improve performance very much (if at all) as far as updates are concerned. Each server must do the same number of updates, just as you would have a single server do. The only difference is that there is a little less lock contention because the updates originating on another server are serialized in one slave thread. Even this benefit might be offset by network delays.
16.4.4.6: How can I use replication to improve performance of my system?
Set up one server as the master and direct all writes to it.
Then configure as many slaves as you have the budget and
rackspace for, and distribute the reads among the master and
the slaves. You can also start the slaves with the
--skip-innodb
,
--low-priority-updates
, and
--delay-key-write=ALL
options
to get speed improvements on the slave end. In this case,
the slave uses nontransactional MyISAM
tables instead of InnoDB
tables to get
more speed by eliminating transactional overhead.
16.4.4.7: What should I do to prepare client code in my own applications to use performance-enhancing replication?
See the guide to using replication as a scale-out solution, Section 16.3.3, “Using Replication for Scale-Out”.
16.4.4.8: When and how much can MySQL replication improve the performance of my system?
MySQL replication is most beneficial for a system that processes frequent reads and infrequent writes. In theory, by using a single-master/multiple-slave setup, you can scale the system by adding more slaves until you either run out of network bandwidth, or your update load grows to the point that the master cannot handle it.
To determine how many slaves you can use before the added
benefits begin to level out, and how much you can improve
performance of your site, you must know your query patterns,
and determine empirically by benchmarking the relationship
between the throughput for reads and 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 reads
and
writes
denote the number of reads and
writes per second, respectively.
Let's say that system load consists of 10% writes and 90%
reads, and we have determined by benchmarking that
reads
is 1200 – 2 ×
writes
. In other words, the system can do
1,200 reads per second with no writes, the average write is
twice as slow as the average read, and the relationship is
linear. Suppose that the master and each slave have the same
capacity, and that we have one master and
N
slaves. Then we have for each
server (master or slave):
reads = 1200 – 2 × writes
reads = 9 × writes /
(
(reads are
split, but writes replication to all slaves)
N
+ 1)
9 × writes / (
N
+
1) + 2 × writes = 1200
writes = 1200 / (2 +
9/(
N
+ 1))
The last equation indicates the maximum number of writes for
N
slaves, given a maximum
possible read rate of 1,200 per minute and a ratio of nine
reads per write.
This analysis yields the following conclusions:
If N
= 0 (which means we have
no replication), our system can handle about 1200/11 =
109 writes per second.
If N
= 1, we get up to 184
writes per second.
If N
= 8, we get up to 400
writes per second.
If N
= 17, we get up to 480
writes per second.
Eventually, as N
approaches
infinity (and our budget negative infinity), we can get
very close to 600 writes per second, increasing system
throughput about 5.5 times. However, with only eight
servers, we increase it nearly four times.
Note that these computations assume infinite network
bandwidth and neglect several other factors that could be
significant on your system. In many cases, you may not be
able to perform a computation similar to the one just shown
that accurately predicts what will happen on your system if
you add N
replication slaves.
However, answering the following questions should help you
decide whether and by how much replication will improve the
performance of your system:
What is the read/write ratio on your system?
How much more write load can one server handle if you reduce the reads?
For how many slaves do you have bandwidth available on your network?
16.4.4.9: How can I use replication to provide redundancy or high availability?
How you implement redundancy is entirely dependent on your application and circumstances. High-availability solutions (with automatic failover) require active monitoring and either custom scripts or third party tools to provide the failover support from the original MySQL server to the slave.
To handle the process manually, you should be able to switch from a failed master to a pre-configured slave by altering your application to talk to the new server or by adjusting the DNS for the MySQL server from the failed server to the new server.
For more information and some example solutions, see Section 16.3.6, “Switching Masters During Failover”.
16.4.4.10: How do I tell whether a master server is using statement-based or row-based binary logging format?
Check the value of the
binlog_format
system
variable:
mysql> SHOW VARIABLES LIKE 'binlog_format';
The value shown will be one of STATEMENT
,
ROW
, or MIXED
. For
MIXED
mode, row-based logging is
preferred but replication switches automatically to
statement-based logging under certain conditions; for
information about when this may occur, see
Section 5.2.4.3, “Mixed Binary Logging Format”.
16.4.4.11: How do I tell a slave to use row-based replication?
Slaves automatically know which format to use.
16.4.4.12:
How do I prevent GRANT
and
REVOKE
statements from
replicating to slave machines?
Start the server with the
--replicate-wild-ignore-table=mysql.%
option to ignore replication for tables in the
mysql
database.
16.4.4.13: Does replication work on mixed operating systems (for example, the master runs on Linux while slaves run on Mac OS X and Windows)?
Yes.
16.4.4.14: Does replication work on mixed hardware architectures (for example, the master runs on a 64-bit machine while slaves run on 32-bit machines)?
Yes.
User Comments
This section mentions two-way replication but does
not give any information about how to set it up. In
fact I can not find it anywhere on the site. Please
consider adding more information to the FAQ.
I have two master database server on two different machine and a slave on an another machine. Slave is now used for replication of one master database.
But I want to use slave for the both master database server. Can I use one slave for two master? If yes, how should I do that?
Just a word of caution in regards to the first FAQ on this page. I needed to get a fresh copy of one table from the master to the mirror. so I did a:
FLUSH TABLES WITH READ LOCK;
It caused some sort of dead lock, The command didn't return control to the console, and mysqld wasn't doing anything according to top, 0% cpu usage. I waited about 5 minutes then had to kill -9 it and restart it, very scary. This was with version 3.23.54.
The mysql replicatian freezing when MASTER and SLAVE servers
connected through FireWall and data not sending in 10 minuts.
The FireWall close the connecting becouse TCP/IP timeout for
connections has left, but MySQL don't know about it.
The MySQL can't send a keepalive packets to keep up connections when data is not sending to SLAVE.
The good idea to enable the keepalive packets when MySQL in
replication mode.
After I made S1 the Slave of M again, i get the following errors on S1:
030826 10:21:22 Got fatal error 1236: 'Could not open log file' from master when reading data from binary log
030826 10:21:22 Slave I/O thread exiting, read up to log 'cluster1-bin.002', position 79
STOP SLAVE, RESET SLAVE and a START SLAVE on S1 works fine for me - S1 starts replications again.
Will the changes we do in the slave`s database tables affect the master`s database tables also?Help me to understand better about replication..
Q. The binary log files have a 3 digit number appended to them to keep order (-bin.001, 002 .. 101, 102 .. etc.) What happens when the number reaches 999 and that file is rolled?
A. The binary log will roll up to -bin.1000.
This tip addresses two questions above:
1. Modifications made to a Slave database (for example, inserting data into a table on the Slave) will not be reflected in the Master. The Slave simply executes the same statements that the Master executes in order to stay in sync.
2. How to set up a Slave host to replicate multiple Master hosts
Some administrator might wish to use a single host to act as a Slave replication server for multiple Master hosts. Although the replication paradigm is "multiple slave hosts -> one master host", the "one slave host -> multiple master hosts" can be done.
A single MySQL server can act as a Slave for only one Master. The trick to get multiple Slaves running on one host is to run multiple MySQL servers on that host.
This is accomplished using some clever configuration in the /etc/my.cnf, and then starting up the multiple servers using /usr/bin/mysqld_multi.
Recommended reading:
http://dev.mysql.com/doc/mysql/en/Multiple_servers.html (5.10 Running Multiple MySQL Servers on the Same Machine)
Use "mysqld_multi -example" to generate a sample my.cnf to use when configuring multiple MySQL servers.
In addition to the configuration parameters that get generated in the sample, remember that a server-id parameter must be present for each individual mysqld being started, and the server-id value must be unique for each server.
I have developed a simple clustering solution using heartbeat and the guidelines in this FAQ which can be used to get a HA mysql replicated cluster running. I have a page at http://www.workboy.com/charles/mysql-heartbeat which discusses how to set it up. I welcome any comments/feedback via the email address on that page.
Charles
Add your own comment.