What’s MySQL Replication

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves).

So MySQL Master-Master Replication

In other words MySQL replication is the process by which data stored in a database, will be copied to a second server’s database. This is called MySQL master-slave replication. Today I’m going to focus on MySQL master-master replication where it allows data to be copied from either server. In other words perform reads or writes from either server.

 MySQL master-master replication, Image Courtesy : alexfilatov.com

Image Courtesy : alexfilatov.com

Below are the two server we are going to use, Server X: 192.168.X.28 Server Y: 192.168.Y.29

Step 1 – Install and Configure MySQL on Server X

sudo apt-get install mysql-server mysql-client

We need to edit /etc/mysql/my.cnf on Server X and add below lines.

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = sample
auto-increment-increment = 2
auto-increment-offset = 2
#bind-address = 127.0.0.1

After you save and quit that file, you need to restart mysql:

sudo service mysql restart

Open up the MySQL shell.

mysql -u root -p

We have to create a user where the user is used for the replication. User “replicator_user” & replace “password” with the password you wish.

create user 'replicator_user'@'%' identified by 'password';

Next, we need to give this user permissions to replicate the data:

grant replication slave on *.* to 'replicator_user'@'%' identified by 'password'; flush privileges;

The following command will output important information which will be used later.

show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 1005     | sample       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

lock the database to prevent any new changes

flush tables with read lock;

Let’s export the database using mysqldump

mysqldump -u root -p sample > sample.sql

unlock the databases (making it writeable again).

unlock tables;

Finish up by exiting the shell.

Step 2 – Install and Configure MySQL on Server Y

We need to repeat the same steps that we followed on Server X.

sudo apt-get install mysql-server mysql-client

Let’s start by editing the /etc/mysql/my.cnf file.

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = sample
auto-increment-increment = 2
auto-increment-offset = 1
#bind-address = 127.0.0.1

After you save and quit the file, you need to restart MySQL:

sudo service mysql restart

It’s time to go into the MySQL shell.

mysql -u root -p

Next, we need to create the database that we are going to replicate.

create database sample;

then exit

exit;

Import the database that you previously exported.

mysql -u root -p sample < /path/to/sample.sql

Let’s login the back to the shell. Just as on Server X, A user will be created which will be responsible for the replication.

create user 'replicator_user'@'%' identified by 'password';

permissions for replication

grant replication slave on *.* to 'replicator_user'@'%' identified by 'password'; flush privileges;

This will allow replication to begin. The following should be typed at the mysql shell

slave stop;
CHANGE MASTER TO MASTER_HOST = '192.168.X.28', MASTER_USER = 'replicator_user', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 1005;
slave start;

Now let’s make a note of the master log file and position to use to replicate in the other direction (from Server Y to Server X).

show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 433      | sample       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Step 3 – Completing Replication on Server X

Running this command will replicate all data from Server Y.

slave stop;
CHANGE MASTER TO MASTER_HOST = '192.168.Y.29', MASTER_USER = 'replicator_user', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 433;
slave start;

You be able to see the details of the replication by typing in this command. The \G rearranges the text to make it more readable.

show slave status\G;

As a verification that everything are working as expected examine the output of the above command and make sure that Slave_IO_Running and Slave_SQL_Running are both YES.

Tips & Tricks

Above mentioned configuration is the minimal configuration for a MySQL master-master replication. See the below for a full my.ini configurations.

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 = 2
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 = 1

Hope you got an idea how to do a MySQL master-master replication. If you have any questions on MySQL master-master replication let me know in the comments below. Your feedback is highly appreciated(happy-face).