Skip navigation links

Additional languages
14.11 Troubleshooting Replication »
Section Navigation      [Toggle]

14.10. Replication FAQ

Q: How do I configure a slave if the master is running and I do not want to stop it?

A: There are several possibilities. If you have taken a snapshot backup of the master at some point and recorded the binary log filename and offset (from the output of SHOW MASTER STATUS) corresponding to the snapshot, use the following procedure:

  1. Make sure that the slave is assigned a unique server ID.

  2. Execute the following statement on the slave, filling in appropriate values for each option: 

    mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='master_user_name',
    ->     MASTER_PASSWORD='master_pass',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

  3. Execute START SLAVE on the slave.

If you do not have a backup of the master server, here is a quick procedure for creating one. All steps should be performed on the master host.

  1. Issue this statement to acquire a global read lock: 

    mysql> FLUSH TABLES WITH READ LOCK;

  2. With the lock still in place, execute this command (or a variation of it): 

    shell> tar zcf /tmp/backup.tar.gz /var/lib/mysql

  3. Issue this statement and record the output, which you will need later: 

    mysql> SHOW MASTER STATUS;

  4. Release the lock: 

    mysql> UNLOCK TABLES;

An alternative to using the preceding procedure to make a binary copy is to make an SQL dump of the master. To do this, you can use mysqldump --master-data on your master and later load the SQL dump into your slave. However, this is slower than making a binary copy.

Regardless of which of the two methods you use, afterward follow the instructions for the case when you have a snapshot and have recorded the log filename and offset. You can use the same snapshot to set up several slaves. Once you have the snapshot of the master, you can wait to set up a slave as long as the binary logs of the master are left intact. The two practical limitations on the length of time you can wait are the amount of disk space available to retain binary logs on the master and the length of time it takes the slave to catch up.

Q: Does the slave need to be connected to the master all the time?

A: No, it does not. The slave can go down or stay disconnected for hours or even days, and then reconnect and catch up on updates. For example, you can set up a master/slave relationship over a dial-up link where the link is up only sporadically and for short periods of time. The implication of this is that, at any given time, the slave is not guaranteed to be in synchrony with the master unless you take some special measures.

Q: How do I know how late a slave is compared to the master? In other words, how do I know the date of the last statement replicated by the slave?

A: If the slave is 4.1.1 or newer, read the Seconds_Behind_Master column in SHOW SLAVE STATUS, which shows the number of seconds that the slave SQL thread is behind processing the master binary log. A high number (or an increasing one) can indicate that the slave is unable to cope with the large number of queries from the master.

A value of 0 for Seconds_Behind_Master can usually be interpreted as meaning that the slave has caught up with the master, but there are some cases where this is not strictly true. For example, this can occur if the network connection between master and slave is broken but the slave I/O thread has not yet noticed this — that is, slave_net_timeout has not yet elapsed.

It is also possible that transient values for Seconds_Behind_Master may not reflect the situation accurately. When the slave SQL thread has caught up on I/O, Seconds_Behind_Master displays 0; but when the slave I/O thread is still queuing up a new event, Seconds_Behind_Master may show a large value until the SQL thread finishes executing the new event. This is especially likely when the events have old timestamps; in such cases, if you execute SHOW SLAVE STATUS several times in a relatively short peiod, you may see this value change back and forth repeatedly between 0 and a relatively large value.

For versions of MySQL prior to 4.1.1, it is possible to determine how far behind the slave is only if SHOW SLAVE STATUS on the slave shows that the SQL thread is running (or for MySQL 3.23, that the slave thread is running), and that the thread has executed at least one event from the master. See Section 14.3, “Replication Implementation Details”.

When the slave SQL thread executes an event read from the master, it modifies its own time to the event timestamp. (This is why TIMESTAMP is well replicated.) In the Time column in the output of SHOW PROCESSLIST, the number of seconds displayed for the slave SQL thread is the number of seconds between the timestamp of the last replicated event and the real time of the slave machine. You can use this to determine the date of the last replicated event. Note that if your slave has been disconnected from the master for one hour, and then reconnects, you may immediately see Time values like 3600 for the slave SQL thread in SHOW PROCESSLIST. This is because the slave is executing statements that are one hour old.

Q: How do I force the master to block updates until the slave catches up?

A: Use the following procedure:

  1. On the master, execute these statements: 

    mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

    Record the replication coordinates (the log filename and offset) from the output of the SHOW statement.

  2. On the slave, issue the following statement, where the arguments to the MASTER_POS_WAIT() function are the replication coordinate values obtained in the previous step: 

    mysql> SELECT MASTER_POS_WAIT('log_name', log_offset);

    The SELECT statement blocks until the slave reaches the specified log file and offset. At that point, the slave is in synchrony with the master and the statement returns.

  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 makes the update of client A work differently than it did on co-master 1. Thus, when the update of client A makes it to co-master 2, it produces tables that are different from what you have on co-master 1, even after all the updates from co-master 2 have also propagated. This means that you should not chain two servers together in a two-way replication relationship unless you are sure that your updates can safely happen in any order, or unless you take care of mis-ordered updates somehow in the client code.

You should also realize that two-way replication actually does not improve performance very much (if at all) as far as updates are concerned. Each server must do the same number of updates, just as you would have a single server do. The only difference is that there is a little less lock contention, because the updates originating on another server are serialized in one slave thread. Even this benefit might be offset by network delays.

Q: How can I use replication to improve performance of my system?

A: You should set up one server as the master and direct all writes to it. Then configure as many slaves as you have the budget and rackspace for, and distribute the reads among the master and the slaves. You can also start the slaves with the --skip-innodb, --skip-bdb, --low-priority-updates, and --delay-key-write=ALL options to get speed improvements on the slave end. In this case, the slave uses non-transactional MyISAM tables instead of InnoDB and BDB tables to get more speed by eliminating transactional overhead.

Q: What should I do to prepare client code in my own applications to use performance-enhancing replication?

A: If the part of your code that is responsible for database access has been properly abstracted/modularized, converting it to run with a replicated setup should be very smooth and easy. Change the implementation of your database access to send all writes to the master, and to send reads to either the master or a slave. If your code does not have this level of abstraction, setting up a replicated system gives you the opportunity and motivation to it clean up. Start by creating a wrapper library or module that implements the following functions:

  • safe_writer_connect()

  • safe_reader_connect()

  • safe_reader_statement()

  • safe_writer_statement()

safe_ in each function name means that the function takes care of handling all error conditions. You can use different names for the functions. The important thing is to have a unified interface for connecting for reads, connecting for writes, doing a read, and doing a write.

Then convert your client code to use the wrapper library. This may be a painful and scary process at first, but it pays off in the long run. All applications that use the approach just described are able to take advantage of a master/slave configuration, even one involving multiple slaves. The code is much easier to maintain, and adding troubleshooting options is trivial. You need modify only one or two functions; for example, to log how long each statement took, or which statement among those issued gave you an error.

If you have written a lot of code, you may want to automate the conversion task by using the replace utility that comes with standard MySQL distributions, or just write your own conversion script. Ideally, your code uses consistent programming style conventions. If not, then you are probably better off rewriting it anyway, or at least going through and manually regularizing it to use a consistent style.

Q: When and how much can MySQL replication improve the performance of my system?

A: MySQL replication is most beneficial for a system that processes frequent reads and infrequent writes. In theory, by using a single-master/multiple-slave setup, you can scale the system by adding more slaves until you either run out of network bandwidth, or your update load grows to the point that the master cannot handle it.

To determine how many slaves you can use before the added benefits begin to level out, and how much you can improve performance of your site, you need to know your query patterns, and to determine empirically by benchmarking the relationship between the throughput for reads (reads per second, or reads) and for writes (writes) on a typical master and a typical slave. The example here shows a rather simplified calculation of what you can get with replication for a hypothetical system.

Let's say that system load consists of 10% writes and 90% reads, and we have determined by benchmarking that reads is 1200 – 2 × writes. In other words, the system can do 1,200 reads per second with no writes, the average write is twice as slow as the average read, and the relationship is linear. Let us suppose that the master and each slave have the same capacity, and that we have one master and N slaves. Then we have for each server (master or slave):

reads = 1200 – 2 × writes

reads = 9 × writes / (N + 1) (reads are split, but writes go to all servers)

9 × writes / (N + 1) + 2 × writes = 1200

writes = 1200 / (2 + 9/(N+1))

The last equation indicates the maximum number of writes for N slaves, given a maximum possible read rate of 1,200 per minute and a ratio of nine reads per write.

This analysis yields the following conclusions:

  • If N = 0 (which means we have no replication), our system can handle about 1200/11 = 109 writes per second.

  • If N = 1, we get up to 184 writes per second.

  • If N = 8, we get up to 400 writes per second.

  • If N = 17, we get up to 480 writes per second.

  • Eventually, as N approaches infinity (and our budget negative infinity), we can get very close to 600 writes per second, increasing system throughput about 5.5 times. However, with only eight servers, we increase it nearly four times.

Note that these computations assume infinite network bandwidth and neglect several other factors that could be significant on your system. In many cases, you may not be able to perform a computation similar to the one just shown that accurately predicts what will happen on your system if you add N replication slaves. However, answering the following questions should help you decide whether and by how much replication will improve the performance of your system:

  • What is the read/write ratio on your system?

  • How much more write load can one server handle if you reduce the reads?

  • For how many slaves do you have bandwidth available on your network?

Q: How can I use replication to provide redundancy or high availability?

A: With the currently available features, you would have to set up a master and a slave (or several slaves), and to write a script that monitors the master to check whether it is up. Then instruct your applications and the slaves to change master in case of failure. Some suggestions:

  • To tell a slave to change its master, use the CHANGE MASTER TO statement.

  • A good way to keep your applications informed as to the location of the master is by having a dynamic DNS entry for the master. With bind you can use nsupdate to dynamically update your DNS.

  • Run your slaves with the --log-bin option and without --log-slave-updates. In this way, the slave is ready to become a master as soon as you issue STOP SLAVE; RESET MASTER, and CHANGE MASTER TO statement on the other slaves. For example, assume that you have the following setup: 

           WC
        \
         v
 WC----> M
       / | \
      /  |  \
     v   v   v
    S1   S2  S3

    In this diagram, M means the master, S the slaves, WC the clients issuing database writes and reads; clients that issue only database reads are not represented, because they need not switch. S1, S2, and S3 are slaves running with --log-bin and without --log-slave-updates. Because updates received by a slave from the master are not logged in the binary log unless --log-slave-updates is specified, the binary log on each slave is empty initially. If for some reason M becomes unavailable, you can pick one of the slaves to become the new master. For example, if you pick S1, all WC should be redirected to S1, which will log updates to its binary log. S2 and S3 should then replicate from S1.

    The reason for running the slave without --log-slave-updates is to prevent slaves from receiving updates twice in case you cause one of the slaves to become the new master. Suppose that S1 has --log-slave-updates enabled. Then it will write updates that it receives from M to its own binary log. When S2 changes from M to S1 as its master, it may receive updates from S1 that it has already received from M

    Make sure that all slaves have processed any statements in their relay log. On each slave, issue STOP SLAVE IO_THREAD, then check the output of SHOW PROCESSLIST until you see Has read all relay log. When this is true for all slaves, they can be reconfigured to the new setup. On the slave S1 being promoted to become the master, issue STOP SLAVE and RESET MASTER.

    On the other slaves S2 and S3, use STOP SLAVE and CHANGE MASTER TO MASTER_HOST='S1' (where 'S1' represents the real hostname of S1). To CHANGE MASTER, add all information about how to connect to S1 from S2 or S3 (user, password, port). In CHANGE MASTER, there is no need to specify the name of S1's binary log or binary log position to read from: We know it is the first binary log and position 4, which are the defaults for CHANGE MASTER. Finally, use START SLAVE on S2 and S3.

    Then instruct all WC to direct their statements to S1. From that point on, all updates statements sent by WC to S1 are written to the binary log of S1, which then contains every update statement sent to S1 since M died.

    The result is this configuration: 

           WC
      /
      |
 WC   |  M(unavailable)
  \   |
   \  |
    v v
     S1<--S2  S3
      ^       |
      +-------+

    When M is up again, you must issue on it the same CHANGE MASTER as that issued on S2 and S3, so that M becomes a slave of S1 and picks up all the WC writes that it missed while it was down. To make M a master again (because it is the most powerful machine, for example), use the preceding procedure as if S1 was unavailable and M was to be the new master. During this procedure, do not forget to run RESET MASTER on M before making S1, S2, and S3 slaves of M. Otherwise, they may pick up old WC writes from before the point at which M became unavailable.

    Note that there is no synchronization between the different slaves to a master. Some slaves might be ahead of others. This means that the concept outlined in the previous example might not work. In practice, however, the relay logs of different slaves will most likely not be far behind the master, so it would work, anyway (but there is no guarantee).

Q: How do I prevent GRANT and REVOKE statements from replicating to slave machines?

A: Start the server with the --replicate-wild-ignore-table=mysql.% option.

Q: Does replication work on mixed operating systems (for example, the master runs on Linux while slaves run on Mac OS X and Windows)?

A: Yes.

Q: Does replication work on mixed hardware architectures (for example, the master runs on a 64-bit machine while slaves run on 32-bit machines)?

A: Yes.


User Comments

Posted by Fernando Ipar on September 19 2002 3:54pm[Delete] [Edit]

you can also check out this high availability project for mysql at
http://mysql-ha.sourceforge.net/
it is based on fake (from linux-ha) and some shells scripts,
and doesn't use dynamic dns (it uses a virtual cluster
IP instead).

Posted by Lou Scalpati on December 6 2002 7:02am[Delete] [Edit]

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.

Posted by Chinmoy Barua on March 2 2003 9:03pm[Delete] [Edit]

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?

Posted by Dan Edwards on March 17 2003 3:04pm[Delete] [Edit]

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.

Posted by Andrew Davydov on March 24 2003 1:01am[Delete] [Edit]

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.

Posted by Martin Rothenberger on August 25 2003 9:29pm[Delete] [Edit]

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.

Posted by SivarajaSudhakar S on September 15 2004 4:41am[Delete] [Edit]

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..

Posted by Jeremy Tinley on October 21 2004 12:46am[Delete] [Edit]

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.

Posted by Ken Zalewski on January 20 2005 5:13pm[Delete] [Edit]

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.

Posted by Charles Bennington on April 15 2005 6:17pm[Delete] [Edit]

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

Posted by Frank Buss on April 24 2008 12:55pm[Delete] [Edit]

How do I enable keepalive packets in MySQL? I had a problem with replication and dial-up connctions: Whenever the dialup connection was closed and opened again, the slave reported 0 seconds behind master, IO and SQL thread running "yes", but it doesn't replicate any more from the master. I fixed this by querying the master log position before every slave action and compare this with the slave log position. If they differ, a "slave stop", "slave start" reconnects to the master and replicates the latest data. Was not funny to fix the database manually, after some of the slaves overwrote some master records with old slave records.

Add your own comment.