08/12/06
The updated source of this document is available at:
Source: http://durrett.net/MySQL_master_master_administration.html
Overview
For a production service you pretty much need at least N+1 on any critical server. Perhaps the most critical role is your database. Here are some tips to help configure MySQL for backups and fail over There are many approaches to this, so look around for what suits your needs.
The basic setup requires two servers that use Master-Master replication (each server considers the other it master). This means all writes to the master are relayed to the slave, so the slave will have an identical copy of the database. A master can have many slaves, but any slave can only have one master. A server can be both a master and a slave. You can use more that two servers, but I will stick with two to keep the examples simple.
Getting a Snapshot
Replication is pretty simple to setup but for some reason difficult to get right. One of the most annoying parts about setting up replication is getting a clean snapshot of the server... if you are starting with an empty database you save yourself this hassle. If not, you need an exact copy of the data, which can be done in a few ways but to get an exact copy pretty much every method will lock the database, which makes this a bad option in a production environment.
A pretty simple way to get a snapshot is with mysqldump. Make sure you include the "--master-data" options as this will add important replication info to the output. The following command should get a clean snapshot of your databases:
# mysqldump --add-locks --create-options --disable-keys --extended-insert --master-data --quick --lock-tables -A > snapshot.sql
If you are lucky enough to have all of the binlogs from the time you initially setup your database you can skip the snapshot process (see "Starting Replication", below).
MySQL Configuration
I am going to use "Server A" and "Server B" for my examples – these refer to the MySQL instance, most likely running on separate pieces of hardware (if not, I am not sure why you are reading this).
Of great importance, make sure Server A and Server B have different server ids in the my.cnf file. The server id is used to identify where a query originated and prevents replication from being an infinite loop (i.e. if, via replication, Server A gets a query that originated from its server id, it discards the update rather than executing the query).
So, Server A has a my.cnf like this:
server-id = 10
and Server B has a my.cnf like this:
server-id = 20
Other settings for the my.cnf file on both servers:
# The next two lines ignore replication queries for the mysql and
# test databases... you probably want this is you want to manage permissions
# on each server separately (I will identify the importance of this in the
# fail over section, later
replicate-ignore-db = mysql
replicate-ignore-db = test
# Make sure you have binlogs. Otherwise, when restoring, you will lose
# all data added since the backup snapshot
log-bin = /var/log/mysql/mysql-bin.log
# Normally slaves do not add the queries they receive via replication
# to their binary logs. If you want to restore from backups you will want
# these updates in the binlogs.
log-slave-updates
Make sure you restart your mysql instances after you change the my.cnf settings.
Loading the Database
For loading from a snapshot, I will assume that Server A is the source and Server B is the new server you are setting up. If you are not loading from a snapshot, jump ahead to "Starting Replication"
Take your snapshot from earlier and load it on Server B. Assuming Server B is a new install of MySQL with only default databases, this should work (assuming the root password is blank):
# mysql ["less than" symbol my blog keeps destroying] snapshot.sql
Once this completes Server B should be an exact copy of Server A at the time the snapshot was taken.
Starting Replication
Next you need to start replication. Once replication is started, all queries written to a master will be sent to the slaves, ideally keeping an exact copy.
Before a slave can replicate from a master, you need to grant permissions on the master. The following would grant replication to the mysql user 'rep' from any machine (please take the appropriate password / firewall precautions before you do this). As we will be running with a master-master setup, this needs to be done on both Server A and Server B:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%';
Next you need to tell each slave how to connect to the master. If you took a snapshot, the information you need is in the snapshot.sql file. In the first 50 or so lines you should see a statement like:
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000321', MASTER_LOG_POS=8675309;
If you don't see this, you probably did not use the '--master-data' option when doing a mysqldump (bummer).
If you did not use a snapshot because you have all of the binlogs from the initial setup of the other database, you should assume that MASTER_LOG_FILE is the name of the oldest binlog on your system and MASTER_LOG_POS=4.
With that information in hand, you can now tell the slave how to connect to the master. So, on Server B, issue the following command:
mysql> CHANGE MASTER TO MASTER_HOST='[hostname or IP of Server A]', MASTER_USER='rep', MASTER_PASSWORD='[hopefully you set a password]', MASTER_LOG_FILE='[MASTER_LOG_FILE]', MASTER_LOG_POS=[MASTER_LOG_POS];
So, using my example snapshot.sql from above (and assuming Server A has an IP address of 192.168.99.10) this would be:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.99.10', MASTER_USER='rep', MASTER_PASSWORD='sekrit', MASTER_LOG_FILE='mysql-bin.000321', MASTER_LOG_POS=8675309;
You can double check your work like this:
mysql> SHOW SLAVE STATUS \G
Once all of this is working, you can start the replication on the slave with the following command:
mysql> START SLAVE;
After that, do another:
mysql> SHOW SLAVE STATUS \G
If all went well you should see "Slave_IO_Running: Yes", "Slave_SQL_Running: Yes" and "Seconds_Behind_Master:" should be some numeric value, ideally "0". If all did not go well, it is beyond the scope of this document.
At this point Server B is a slave of Server A. Important: do not execute any queries on Server B that will result in an update. Now we want to make Server A a slave of Server B.
On Server B, execute the following:
mysql> SHOW MASTER STATUS;
You will use these values to setup replication on Server A. If you execute this more than once you may see the values are updating – this is fine so long as you are not executing update queries on Server B yet – the updating values reflect updates to Server A being replicated to Server B.
On Server A, execute the following:
mysql> CHANGE MASTER TO MASTER_HOST='[hostname or IP of Server B]', MASTER_USER='rep', MASTER_PASSWORD='[hopefully you set a password]', MASTER_LOG_FILE='[MASTER_LOG_FILE]', MASTER_LOG_POS=[MASTER_LOG_POS];
So assuming Server B has an IP address of 192.168.99.20, this might look like:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.99.20', MASTER_USER='rep', MASTER_PASSWORD='sekrit', MASTER_LOG_FILE='mysql-bin.0000011', MASTER_LOG_POS=94703;
Again on Server A, start the slave, confirm it is working and pat yourself on the back:
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G
Congratulations, you now have Master/Master replication working.
Using Your Databases
So you may think, cool... I now have twice the capacity on my database. Not really. For one, all writes done to one database also have to be done to the other. You may be able to take advantage of more read capacity, but if your application requires the read capacity of both servers, you don't have N+1 redundancy – when one server dies all reads will go to the remaining server, probably overloading it. But neither of these are the really big problem...
When using replication in MySQL, writes to multiple servers are not necessarily written in the order they were intended. For example, lets say you do an insert on Server A and, at the same time, you do an insert on Server B. If the insert was done to a table that has an auto_increment primary key, it is possible that both inserts will get the same id because they were not aware of the other insert at the time they were written. You can write your application to avoid this, but if you are starting with most off-the-shelf or open source solutions, they probably are not written to deal with this situation.
Once you have duplicate keys your replication will fail because the slave will not be able to perform the insert it received from the master. At that point your replication is inconsistent... very bad. For this reason, you probably never want to be writing to both servers at the same time.
So instead I recommend that you have one server setup as your "primary" and one setup as your "standby". Don't assign these names to your servers because they will switch during a fail over.. think of "primary" and "standby" as a pointer to the server. All reads and writes are sent to the primary server and your standby is used for getting clean backups without blocking your application and can become the primary when the primary server fails. I like to enforce this restriction with MySQL permissions... the application only has write permission to the primary server and read (or no) permission to the standby.
Backups
Backups are simple at this point – simply use the mysqldump example from above on your standby server. You can also stop the server and copy the binary database data – you are on your own though. During this backups your standby database will not update so it will fall behind the primary database (you can see how many seconds behind it is with the "SHOW SLAVE STATUS" command). This leaves you a little more vulnerable to failure during backups – if this is too risky, you can setup a third slave that is only used for backups.
Note: when using the "--master-data" command in mysqldump, the output is the pointer to the master data for the machine performing the dump, not for that machine's master! So if your standby server is Server B and you do a mysqldump on it, to restore from that snapshot you point to Server B as the master, not Server A. This is also why it is important to have "log-slave-updates" enabled in the my.cnf file – the master data from the backup snapshot needs to point at binlogs on Server B. If you are not logging slave updates, these binlogs will be empty because all writes on Server B are from replication.
So to recap, in order to restore this database with no data loss, you need both the snapshot and binlogs from the server you use for backups (your standby). If you just have your snapshot, you will lose all updates that occurred between the snapshot and the failure. When I update this document I will add some tricks to get around this.
Fail over
Fail over is relatively simple – make your application stop writing to the primary server and start writing to the standby server (and now think of "standby" as "primary" and "primary" as "standby". In practice there are a few precautions you may want to take:
*
If the primary database is accessible, disable write access to it to that you avoid the duplicate key issues. In many failure cases you will have to skip this step.
*
Before starting to write on the standby, make sure that it is '0' seconds behind the master. Otherwise you risk duplicate key issues.
*
If you have automated backup scripts, you may want to point them at the new standby... otherwise they will lock your new primary database when they kick-in.
Recovery
Recovery works pretty much exactly like the initial setup of the servers – load the snapshot and start replication. There is one huge gotcha that I don't usually see identified... If you need to do a full restore of what was previously your primary server you are likely to that you get errors in replication... this is because when replicating from the restore it is ignoring the updates it performed prior to its failure. Here is an example scenario:
- A snapshot is taken from standby Server B at 6:00 AM
- At 11:30 AM, primary Server A fails and we redirect application to Server B
- Server A is re-installed from scratch with the 6:00 AM Server B snapshot
- Server A is told to replicate from Server B starting at the log position of the snapshot
- Since all binlogs from 6:00 AM until 11:30 AM originated from Server A, it ignores these statements and actually starts replication at 11:30 AM, when Server B was the origin of the updates.
The quick fix (hack) is giving Server A a different server id until it catches-up to Server B and then it can get its old server id back.
Comments:
08/25/06 @ 21:20
06/29/08 @ 02:10 
