Sunday, June 14, 2015

MySQL Multi-Master Two-Way Replication

Now, you should already be familiar with load balancing traffic across two servers, at this point. This article is going to introduce you to the same basic concept using two MySQL servers. Since MySQL 5.1.18, we've had the ability to implement a MySQL cluster with multi-cluster replication, and that's what we'll be doing here.

Here's what the basic architecture looks like:

Prerequisites: You'll want to be familiar with setting up VirtualBox with Vagrant and load balancing. This guide was written for Mac OS X users.

Replication

MySQL replication is a means of enabling one database server (master) to be copied to one or more master or slave servers. Because MySQL replication is asynchronous, the secondary servers do not need to permanently be connected to the master in order to receive updates. This allows us to stop the slave thread on the slave server and count on the data to synchronize as soon as we start it back up.

We'll be opting for a master-master setup, which enables data to be duplicated from either server, allowing us to execute reads and writes both ways, as well. This also makes accessing the data more efficient.

When you've reached the end of this guide, make sure to move on to this guide to set up a load balancer for these instances. Let's begin.

Provisioning Our MySQL Instances

To spin up the two MySQL instances, we're going to be cloning a repo that contains a Vagrantfile and Ansible playbook that allows us to provision two local virtual machines with mysql-server installed. This is the only step of the tutorial that's going to be automated for you. The rest of the steps will need to be performed manually.

# clone the repo
git clone https://github.com/roblayton/vagrant-ansible-mysql-python mysqllb
cd mysqllb

vi Vagrantfile

We're using this repo because it comes with an Ansible playbook for MySQL. We just need to update the Vagrantfile so that our instances are leveraging that playbook.

After you're finished updating the Vagrantfile with the above content, run the following command:

# spin up the instances
vagrant up

You should now have two running VMs with MySQL installed on each. For mysql1 and mysql2, we'll be carrying out the following additional steps, manually.

Configuring Our MySQL Instances

MYSQL1

# ssh in
vagrant ssh mysql1

# update the mysql config
sudo vi /etc/mysql/my.cnf 

# update the following lines to this
server-id              = 1
log_bin                = /var/log/mysql/mysql-bin.log
binlog_do_db           = include_database_name
#bind-address            = 127.0.0.1

# then save the file and restart mysql
sudo service mysql restart

Now we're going to make some more changes in the mysql shell.

# login to mysql
mysql -uroot

# create a replicator user
CREATE USER 'replicator'@'%' IDENTIFIED BY 'password'; 

# allow replicator to replicate
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%'; 

Finally, we check the status of our master. Take note of the File and Position, which we'll be using later.

SHOW MASTER STATUS;

+------------------+----------+-----------------------+------------------+
| file             | position | binlog_do_db          | binlog_ignore_db |
+------------------+----------+-----------------------+------------------+
| mysql-bin.000001 |      107 | include_database_name |                  |
+------------------+----------+-----------------------+------------------+
1 row in set (0.00 sec)

# exit out of mysql
exit

# exit out of the vm
exit

MYSQL2

# ssh in
vagrant ssh mysql2

# update the mysql config
sudo vi /etc/mysql/my.cnf 

# update the following lines to this
server-id              = 2
log_bin                = /var/log/mysql/mysql-bin.log
binlog_do_db           = test
#bind-address            = 127.0.0.1

# then save the file and restart mysql
sudo service mysql restart

Notice we've set server-id to 2 and binlog_do_db to reference the database we created on mysql1, this time. we then repeat a lot of the same steps:

# login to mysql
mysql -uroot

# create a replicator user
CREATE USER 'replicator'@'%' IDENTIFIED BY 'password'; 

# create a database to replicate across all instances
CREATE DATABASE test; 

# allow replicator to replicate
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%'; 

Now, we want to use the ip and status information from our mysql1 instance, and use them with the following commands to allow for one-way replication.

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = '192.168.50.11', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;
START SLAVE;

To allow on two-way replication, we'll have to check the status information of the mysql2 instance and apply that to mysql1. Run the following commands, next, and take note of the values:

SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

# exit out of mysql
exit

# exit out of the vm
exit

Two-Way Replication

Finalizing Two-way Replication on MYSQL1

# ssh in
vagrant ssh mysql1

# login to mysql
mysql -uroot

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = '192.168.50.12', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;
START SLAVE;

Testing Master-Master Replication

Now, while we're in mysql1, let's create a database and table to see if it was replication in mysql2.

# create db
CREATE DATABASE test;

# create tables
CREATE TABLE test.names (`id` varchar(10), `name` varchar(255));
CREATE TABLE test.roles (`id` varchar(10), `name` varchar(255));

Open up a new terminal window on your host machine, navigate to the mysqldb folder and run the following commands:

# ssh in
vagrant ssh mysql2

# login to mysql
mysql -uroot

# verify
SHOW DATABASES;
USE test;
SHOW TABLES;
DESCRIBE test.names;

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | varchar(10)  | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Verifying Two-way Replication on MYSQL2

# from mysql2
DROP TABLE names;

# from mysql1
SHOW TABLES;

+---------------------+
| Tables_in_test      |
+---------------------+
| roles               |
+---------------------+

No comments:

Post a Comment