Patrick Galbraith (capttofu) wrote,
Patrick Galbraith
capttofu

MySQL Multi-Master Replication

Changing hats




Note: I have updated this article as of April 24, 2008 to indicate that you do not have to set auto_increment_increment or auto_increment_offset for the slaves. If you see this in the diagrams, please ignore.

The master sets the auto_increment value in it's binary log which the slave reads via replication. I may write an update to this article at some point soon.


I have put on the hat of a MySQL user, a customer, as opposed to a developer (Federated Storage Engine, DBD::mysql) these last couple months with my new employer, Grazr. One of the things we need is a whole replication set-up, with read-only slaves, and write masters. Thats master(s), with an 's', plural. We need a set-up where we have two data centers. In each of the data centers, there is a master and its slaves, all on the same network. That master needs to replicate to another slave, which is also a master, in its own data center, where it has its own slaves. The idea is that the web servers running mod_perl applications in each data center send all their read-only queries to slaves running on the same server, and all write-only queries to the master in that data center.

How does one go about setting up a multi-master replication scheme, also having slaves in the mix? There are several articles online pertaining to how to set up 'multi-master' replication. It seems though, that they are tailored for 'circular' or 'ring' replication. One of my favorites is Giuseppe Maxia's article at ONLamp which I followed in setting up our replication scheme.

First step: Set up MySQL multi-master replication (circular replication with two nodes)



My first step, as is the first step in Giuseppe's article, is to set up a two node circular replication. This was surprisingly easy, though I did have to learn more about replication than I thought I previously knew, to troubleshoot issues that I encountered.

This involved two main db servers, each in a different data center, one in Michigan, and one in New York. I installed MySQL 5.0 from source (I always like to use /BUILD/compile-* scripts to build). Next, I configured MySQL's my.cnf file to use the tuning setup from ./support-files, the my-medium.cnf, which should give us good tuning parameters for our load at this point. Then I added the pieces necessary for replication:


server-id = 1
log-bin = /usr/local/mysql/var/bin.log
log-slave-updates
log-bin-index = /usr/local/mysql/var/log-bin.index
log-error = /usr/local/mysql/var/error.log

relay-log = /usr/local/mysql/var/relay.log
relay-log-info-file = /usr/local/mysql/var/relay-log.info
relay-log-index = /usr/local/mysql/var/relay-log.index

auto_increment_increment = 10
auto_increment_offset = 1
master-host = <other master hostname>
master-user = <replication username>
master-password = <replication password>

replicate-do-db = somedbname1
replicate-do-db = somedbname2


I only wanted to replicate two databases, or in other terms, schemas, so I listed them as the example shows with replicate-do-db. Since I intend on allowing for various nodes to be added, I set auto_increment_increment to 10, which will allow for 10 different servers, all of which could be read-write masters if I wanted them to be. In this case, my plan is only for 2, so this will give me a lot of room to grow.

For the second master, I used the same settings except:


auto_increment_offset = 2
server-id = 2
master-host = <master hostname for firsts db>
master-user = <replication username>
master-password = <replication password>


I then started up both databases. At that point, I tried to create one of the schemas I planned to replicate from the first master server, but had already created it on the second master server, so saw that there was an error in 'show slave status' telling me that the database already existed on the second server. I simply stopped both databases, deleted all the binary and relay logs, as well as the master log index:

rm bin.00000* error.log log-bin.index master.info relay*

From within the data directory. This is the crude way you clear an error. For me, I was able to do this since I was setting up the replication scheme for the first time. There are probably more elegant ways to do this, but this is somewhat new to me because like I said, I was more of a developer vs. user! I then restarted both databases. Since both servers now already had the schema created, I was able to simply create all the tables I wanted to on the first server. I was overjoyed to see those tables replicated to the second server! I then created a simple test table


mysql> create table mytest (a int auto_increment not null primary key, b varchar(32));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into mytest (b) values ('database1, first insert');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mytest;
+---+-------------------------+
| a | b |
+---+-------------------------+
| 1 | database1, first insert |
+---+-------------------------+
1 row in set (0.00 sec)


I was happy to see the first value for 'a' column was 1. Now, to the second master


mysql> select * from mytest;

+---+-------------------------+
| a | b |
+---+-------------------------+
| 1 | database1, first insert |
+---+-------------------------+
1 row in set (0.00 sec)

mysql> insert into mytest (b) values ('database2, first insert');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mytest;
+---+-------------------------+
| a | b |
+---+-------------------------+
| 1 | database1, first insert |
| 2 | database2, first insert |
+---+-------------------------+
2 rows in set (0.00 sec)

mysql> insert into mytest (b) values ('database2, second insert');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mytest;
+----+--------------------------+
| a | b |
+----+--------------------------+
| 1 | database1, first insert |
| 2 | database2, first insert |
| 12 | database2, second insert |
+----+--------------------------+
3 rows in set (0.00 sec)



Hooray! Record from the first server was there, and subsequent inserts gave me auto_increment values that were correct! Again, to the first server


mysql> insert into mytest (b) values ('database1, second insert');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mytest;
+----+--------------------------+
| a | b |
+----+--------------------------+
| 1 | database1, first insert |
| 2 | database2, first insert |
| 12 | database2, second insert |
| 21 | database1, second insert |
+----+--------------------------+


Excellent! Multi-master replication is working! I feel elated!

Here is a image (omnigraph was crashing upon export, so I used grab)





Step 2: Add a slave



The next step, is to add a slave to replicate from the first server. I decided, since this is a slave of the first server, which is server-id 1, to use server-id 3 for this new slave. For any slaves I add to the second server, I'll then add even server-ids. This seems very logical and easy to keep track of, because I can make the server-id and offset the same. Even though these are read-only slaves, I'll still use the offset. So my my.cnf file for slave server-id 3 looks like this:


server-id = 3
master-host = <master hostname for firsts db>
master-user = <replication username>
master-password = <replication password>



I do a backup of the first master's data


mysqldump -u someuser dbtoreplicate> dbtoreplicate.sql


and record the bin log file name and position


mysql> show master status;
+------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| bin.000002 | 1105 | | |
+------------+----------+--------------+------------------+


Then, on the slave, start the db, then load the dump (which I scp from the first master to the slave)


mysql -usomeuser dbtoreplicate < dbtoreplicate.sql


Then set the slave to the correct binary log and position


mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to MASTER_HOST='firstmaster.mydomain.com', MASTER_USER='replicationuser', MASTER_PASSWORD='replicationpass', MASTER_LOG_FILE='bin.000002', MASTER_LOG_POS=1105;
Query OK, 0 rows affected (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status;

| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |

| Waiting for master to send event | firstmaster.mydomain.com | replicationuser | 3306 | 60 | bin.000002 | 1105 | relay.000002 | 229 | bin.000002 | Yes | Yes | dbtoreplicate | | | | | | 0 | | 0 | 1105 | 229 | None | | 0 | No | | | | | | 0 |



I then insert data from the first master


mysql> insert into mytest (b) values ('database1, third insert');
Query OK, 1 row affected (0.00 sec)


Check it on the slave


mysql> select * from mytest;
+----+--------------------------+
| a | b |
+----+--------------------------+
| 1 | database1, first insert |
| 2 | database2, first insert |
| 12 | database2, second insert |
| 21 | database1, second insert |
| 31 | database1, third insert |
+----+--------------------------+
5 rows in set (0.00 sec)


Hooray! It's working! Now to insert from the second master


mysql> insert into mytest (b) values ('database2, third insert');
Query OK, 1 row affected (0.00 sec)


Check it on the slave


mysql> select * from mytest;
+----+--------------------------+
| a | b |
+----+--------------------------+
| 1 | database1, first insert |
| 2 | database2, first insert |
| 12 | database2, second insert |
| 21 | database1, second insert |
| 31 | database1, third insert |
+----+--------------------------+
5 rows in set (0.00 sec)


What?! Where's the data from the second master? Look at the first master


mysql> select * from mytest;
+----+--------------------------+
| a | b |
+----+--------------------------+
| 1 | database1, first insert |
| 2 | database2, first insert |
| 12 | database2, second insert |
| 21 | database1, second insert |
| 31 | database1, third insert |
| 32 | database2, third insert |
+----+--------------------------+
6 rows in set (0.00 sec)


So, the first master at least has the data inserted from the second master. Why is the slave (server-id 3) not getting data from the second master? Time to look at the binary logs on the first master, which the slave uses to obtain data from

#070223 11:58:28 server id 1 end_log_pos 1105 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1172249908;
flush tables;
# at 1105
#070223 12:01:41 server id 1 end_log_pos 1133 Intvar
SET INSERT_ID=31;
# at 1133
#070223 12:01:41 server id 1 end_log_pos 1268 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1172250101;
insert into mytest (b) values ('database1, third insert');
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;


So, we see the insert query that we issued from first master that was successfully replicated to the slave is there, but why not the insert query that we issued from the second master? I look online and can't really find this out. After talking to Brian Aker, he finds the option "log-slave-updates", which ensures the slave's updates are logged to the binary log. The second master is a slave of the first master, so we need its updates. So, I enable this on both masters, adding it to the my.cnf of both.


log-slave-updates


On the masters, restart the servers with the new my.cnf setting, dump the data for the schema I want to replicate again. I check master status to get binary log name and position

mysql> show master status;
+------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| bin.000003 | 98 | | |
+------------+----------+--------------+------------------+


On the slave, I stop the slave

mysql> stop slave ;
Query OK, 0 rows affected (0.00 sec)


Load the data

mysql -usomeuser dbtoreplicate < dbtoreplicate.sql


Change the slave to the new log file and position and restart the slave

mysql> change master to MASTER_HOST='firstmaster.mydomain.com', MASTER_USER='remote', MASTER_PASSWORD='remote', MASTER_LOG_FILE='bin.000003', MASTER_LOG_POS=98;
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status;

| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |

| Waiting for master to send event | firstmaster.mydomain.com | replicationuser | 3306 | 60 | bin.000003 | 98 | relay.000002 | 229 | bin.000003 | Yes | Yes | dbtoreplicate | | | | | | 0 | | 0 | 98 | 229 | None | | 0 | No | | | | | | 0 |


mysql> select * from mytest;
+----+--------------------------+
| a | b |
+----+--------------------------+
| 1 | database1, first insert |
| 2 | database2, first insert |
| 12 | database2, second insert |
| 21 | database1, second insert |
| 31 | database1, third insert |
| 32 | database2, third insert |
+----+--------------------------+
6 rows in set (0.00 sec)


The next step is to make sure replication is running and hope that this new setting makes replication work to this slave from both masters

On the first master
mysql> insert into mytest (b) values ('database1, fourth insert');

On the second master
mysql> insert into mytest (b) values ('database2, fourth insert');

On the slave

mysql> select * from mytest;
+----+--------------------------+
| a | b |
+----+--------------------------+
| 1 | database1, first insert |
| 2 | database2, first insert |
| 12 | database2, second insert |
| 21 | database1, second insert |
| 31 | database1, third insert |
| 32 | database2, third insert |
| 41 | database1, fourth insert |
| 42 | database2, fourth insert |
+----+--------------------------+
8 rows in set (0.00 sec)


HOORAY! It works it works! He likes it he likes it he likie! So, it's the one magical setting - "log-slave-updates". Mark that in your book!

Here is the final diagram of the setup:





Summary



MySQL replication is quite simple, in the scope of all the various things I have dealt with in my career, to set up. There are just a couple things that could be stressed (perhaps they are and I missed it), like the issue of "log-slave-updates", which could have really saved time. I'm one who likes to see examples. It's one thing to read of how to do a basic setup, and how replication works, but examples, at least for me, are the best way to learn things. The other things I would like to know more about are tools for measuring how well replication is working, as well as monitoring. More reading to do!

Credit: Thanks to Giuseppe's article, Brian's help, this was possible!

So, now all I have to do it add slaves, and all should work, right? ;)
Tags: databases, feeds, grazr, log-slave-updates, multi-master replication, mysql, replication
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic
  • 51 comments