MySQL master-master replication (by damian)

Master-Master Replication with MySQL

November 12, 2011

Most people are familiar with the concept of master-slave replication, where the master database keeps a log of all the statements it gets (selects, inserts, updates, and deletes), and the slave follows along by replaying those statements. And as such, the two databases are kept in sync.

A master-master setup is an extension of that concept, it’s your standard master-slave setup, but the master database is made to be a slave to the original slave database — so each master is also a slave.

This allows us to write to either database and have it automatically replicate to the other. Reads are also replicated so the caches will be hot on both machines.

Why am I writing this?

There’s lots of information out there on MySQL replication, but when trying to figure out exactly how to do it, I still had trouble piecing all of it together. I couldn’t find a single point of reference that walked through the entire thing step-by-step in a clear fashion.

I’m hoping that this will be helpful to anyone looking to do master-master MySQL replication.

Let’s do it!

Let’s say we have two databases: A and B. Database A is currently in production serving requests, and we want to master-master it up with database B, a brand new database we just created. See my extremely detailed diagram at the beginning of this post!

Here’s the overview of how we’ll accomplish this:

Enable binary logging for both databases
Export data from database A
Import data into database B
Setup an SSH tunnel between database A and B
Setup replication permissions
Make B a slave of A
Make A a slave of B

Quick note: In the steps below, I’ve used a$ to indicate a shell command to be issued on the host for database A. Likewise, b$ is for shell commands on database B. a> is a SQL command on database A and b> is a SQL command for database B.

Step 1: Enable Binary Logging

We need to adjust the database configuration (/etc/mysql/my.cnf) for databases A and B to enable what’s called binary logging. The database’s binary log can be thought of as a log of all the SQL statements it has received.

Here’s what we want in my.cnf for database A:

server_id = 1 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 100M log_slave_updates = 1 auto-increment-increment = 2 auto-increment-offset = 1

And here’s what we want for database B:

server_id = 2 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 100M log_slave_updates = 1 auto-increment-increment = 2 auto-increment-offset = 2

Note that they each have a unique server_id. That’s important because when these replication logs are flying all over the place, MySQL needs to know from which database the statement originated (to avoid duplication and things like that).

The auto-increment-offset and auto-increment-increment are also very important. If we’re going to allow writes to each database, we don’t want collisions for auto-incrementing ID fields. To avoid this, we set each server to increment by 2 instead of 1, and set database A to only use odd IDs and database B to use even ones.

Changes to my.cnf require a database restart to take effect. So go ahead and restart your databases.

Step 2: Export data from database A

You’ve probably used mysqldump before to export data from MySQL. This is no different, except we want to record the position in the binary log when we do it.

The reason this is important is that when we slave up database B to database A, we need to tell it where in database A’s binary log to start working from.

To do this we will lock the database, dump the data, record the position in the binary log, and release the lock. The lock is necessary so that we’re assured binary log position doesn’t change while we’re exporting the data. Without this lock, pesky users could be inserting and updating rows during our data dump!

Ok, here we go. Login to mysql on database A and issue these commands to lock the database:

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

Record the output of the last statement. It’s the binary log position! It will look something like this:

+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 106 | | | +------------------+----------+--------------+------------------+

Now in another terminal on database A, dump the data:

a$ mysqldump -u root -p database > ~/my_database.sql

As soon as that dump is complete, release the read lock:

a> UNLOCK TABLES;

Great. Now we have the export of the database and the exact position in the binary log to which it corresponds.

Step 3: Import data into database B

If the dataset is reasonably large and we’re sending it over the public internet, we should probably compress it first so it doesn’t take forever to transport.

a$ gzip my_database.sql

Now let’s copy it to the host database B lives on:

a$ scp my_database.sql.gz user@database-b:~/my_database.sql.gz

Now we switch over to database B for the import. Let’s first unzip the file:

b$ gunzip database.sql.gz

Now let’s make the database and import the data:

b$ mysqladmin -uroot -p create my_database b$ pv my_database.sql | mysql -uroot -p my_database

If you haven’t used pv before, I highly recommend it. It will allow you to see the speed and progress of the import.

Ok, so now we’ve got the snapshot of database A imported into database B. Next, we need to setup an SSH tunnel between hosts A and B so the two databases can talk.

Step 4: Setup the SSH tunnel

If your two database machines are both on a private network that you trust, you can skip this step and just have them talk directly over that network. If your databases are only connected via the public internet, you’ll want to setup an SSH tunnel so people can’t snoop on your information and database passwords.

We want to setup the SSH tunnel such that on database A we can connect to localhost:13306 and it will forward to port 3306 on database B, and vice versa.

In this way, if we’re on database A, we can connect to database B via localhost:13306 and if we’re on database B we can connect to database A via localhost:13306.

Setting this up is actually really easy. On the database A host, do this:

a$ sudo ssh -N -f -L13306:127.0.0.1:3306 -R13306:127.0.0.1:3306 user@database-b

Note that you’ll want to change 127.0.0.1 to the IP that each of your MySQL servers are listening on. So if database A isn’t listening on localhost, but instead listening on 10.0.0.10 and database B is listening on 192.168.1.10, the command will look like this:

a$ sudo ssh -N -f -L13306:10.0.0.10:3306 -R13306:192.168.1.10:3306 user@database-b

As part of the next step, we’ll be setting up a special database user for replication and we can use those users to test the tunnel.

Step 5: Setup replication permissions

In order to slave up each database, we’ll need to create a replication user on each database and give it the proper permissions.

First, on database A, create a replication user so database B will have access:

a> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'127.0.0.1' IDENTIFIED BY 'replication_password';

Second, on database B, create a replication user so A will have access:

b> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'127.0.0.1' IDENTIFIED BY 'replication_password';

Note, just like in the last step with the SSH tunnel, you’ll need to change 127.0.0.1 to the IP address that each of the databases are listening on.

Now that this is setup, we can test our SSH tunnel and replication users. Let’s first connect from database A to database B:

a$ mysql -ureplication -p -h 127.0.0.1 -P 13306

Now let’s try from B to A:

b$ mysql -ureplication -p -h 127.0.0.1 -P 13306

Hopefully that worked like a charm. If it didn’t, double check that your SSH tunnel is setup correctly and that you have all your IPs correct.

Step 6: Slave database B to A

Now we’re ready to instruct database B to replicate database A. Let’s show database B who its master is with the following SQL command. You’ll want to make sure you use the binary log position that we recorded in Step 2.

b> CHANGE MASTER TO master_host='127.0.0.1', master_port=13306, master_user='replication', master_password='replication_password', master_log_file='mysql-bin.000001', master_log_pos=106;

Now let’s start slaving!

b> START SLAVE; b> SHOW SLAVE STATUS\G

The output of the show slave status command will tell you what it’s up to. If your database doesn’t get tons of traffic, it should sync up almost instantly. Hooray!

Step 7: Slave database A to B

We’re almost there. This is the final step.

Let’s get a position in database B’s binary log, so we can tell A to start replicating from there. On database B in MySQL:

Now on database A, we issue these commands to slave it to database B. Substitute the binary log position we just recorded as appropriate.

a> CHANGE MASTER TO master_host='127.0.0.1', master_port=13306, master_user='replication', master_password='replication_password', master_log_file='mysql-bin.000001', master_log_pos=480764; a> START SLAVE; a> SHOW SLAVE STATUS\G

Now we have master-master replication between database A and B! Give it a try by inserting a row on database A and watch it show up on database B and vice versa.

It’s time for a well deserved beer. Let’s celebrate!

Tags:
#cluster #database #dual #dual-master #howto #linux #master #master-master #mysql #replication

Info:

Attribute(s):	Public
Created:	11.03.2013 11:48	Total Views:	446
Last Changed:	05.04.2013 13:04	Total Changes:	3