4.10.8 Replication FAQ

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

A: There are several options. If you have taken a backup of the master at some point and recorded the binlog name and offset ( from the output of SHOW MASTER STATUS ) corresponding to the snapshot, do the following:

• Make sure unique server id is assigned to the slave.
• Execute CHANGE MASTER TO MASTER_HOST='master-host-name', MASTER_USER='master-user-name', MASTER_PASSWORD='master-pass', MASTER_LOG_FILE='recorded-log-name', MASTER_LOG_POS=recorded_log_pos
• Execute SLAVE START

If you do not have a backup of the master already, here is a quick way to do it consistently:

• FLUSH TABLES WITH READ LOCK
• gtar zcf /tmp/backup.tar.gz /var/lib/mysql ( or a variation of this)
• SHOW MASTER STATUS - make sure to record the output - you will need it later
• UNLOCK TABLES

An alternative is 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. This is however slower than doing a binary copy.

No matter which of the two ways you used, 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 will take a snapshot, restore it to the slave, and adjust 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. You can have the slave go down or stay disconnected for hours or even days, then reconnect, catch up on the updates, and then disconnect or go down for a while again. So you can, for example, use master-slave setup over a dial-up link that is up only for short periods of time. The implications of that are 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 the slave is compared to the master? In other words, how do I know the date of the last query replicated by the slave?

A: This is possible only if the SQL slave thread exists (that is, if it shows up in SHOW PROCESSLIST, see section 4.10.3 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 SQL slave 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 SQL slave 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 SQL slave 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: Execute the following commands:

• Master: FLUSH TABLES WITH READ LOCK
• Master: SHOW MASTER STATUS - record the log name and the offset
• Slave: SELECT MASTER_POS_WAIT('recorded_log_name', recorded_log_offset) When the select returns, the slave is currently in sync with the master
• Master: UNLOCK TABLES - now the master will continue updates.

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 will be different from 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 you 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. This benefit, though, 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, and configure as many slaves as you have the money and rackspace for, distributing 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 my client code 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 the replicated setup should be very smooth and easy -- just change the implementation of your database access to read from some slave or the master, and to always write to the master. If your code does not have this level of abstraction, setting up a replicated system will give you an opportunity/motivation to it clean up. You should start by creating a wrapper library /module with the following functions:

• safe_writer_connect()
• safe_reader_connect()
• safe_reader_query()
• safe_writer_query()

safe_ means that the function will take care of handling all the error conditions.

You should then convert your client code to use the wrapper library. It may be a painful and scary process at first, but it will pay off in the long run. All applications that follow the above pattern will be able to take advantage of one-master/many slaves solution. 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 Monty's replace utility, which 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.

Note that, of course, you can use different names for the functions. What is important is having unified interface for connecting for reads, connecting for writes, doing a read, and doing a write.

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 not so frequent writes. In theory, by using a one master/many slaves setup you can scale 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 our imagined system.

Let's say our system load consists of 10% writes and 90% reads, and we have determined that max_reads = 1200 - 2 * max_writes, or in other words, our system can do 1200 reads per second with no writes, our average write is twice as slow as average read, and the relationship is linear. Let us suppose that our master and slave are of the same capacity, and we have N slaves and 1 master. Then we have for each server (master or slave):

reads = 1200 - 2 * writes (from bencmarks)

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)

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

Eventually as N approaches infinity (and our budget negative infinity), we can get very close to 600 writes per second, increasing system throughput about 5.5 times. However, with only 8 servers, we increased it almost 4 times already.

Note that our computations assumed infinite network bandwidth, and neglected several other factors that could turn out to be significant on your system. In many cases, you may not be able to make 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 decided whether and how much, if at all, the 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 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 which issue database writes and reads; clients which issue only database reads are not represented as they don't need to 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 (i.e. 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 procedure like 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.

User Comments

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

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 been reading the documentation on Replication. Is there a way to only replicate databases instead of servers? Have one server be a master and a slave at the same time without multiple instances of mysql running?

Aaron

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.

How can i make a keepalive funcation for slave ?

Add your own comment.