Configure a MySQL master-slave replication on Alibaba ECS instances

Replication gives you the ability to maintain multiple copies of your data on one MySQL database server (the master) and one or more MySQL database servers (the slaves). You can configure MySQL to replicate all databases, selected databases, or even selected tables within a database.

Replication can helpful for many reasons including:

  • High performance: the master server is dedicated to writes and updates whereas reads are distributed among multiple slaves.

  • Security: data backup can be done on the slave without interrupting the master.

  • Data analytics: the master records live data and the slave handle the data analysis which ensures the master’s high performance.

This tutorial will use two Alibaba Cloud ECS instances to demonstrate a simple scenario of MySQL replication — one master sends information to a single slave. It will use the traditional method of replication: replicating events from the master’s binary log, and synchronizing the log files between master and slave.

Step 1: Create 2 ECS instances

We will need to separate ECS instances to play the roles of master and slave. In this tutorial I have selected Ubuntu 16.04 as the operating system. You can following this guide to create the ECS instances.

Assume that IP address of the master server is 10.0.0.11

Install MySQL if you haven’t got it installed. You need sudo privileges to run the installation command:

$ sudo apt-get install mysql-server mysql-client

Step 2: Configure the master server

Setting up the replication master involves establishing a unique server ID and enabling binary logging. It can be done in the configuration file:

$ sudo nano /etc/mysql/my.cnf

Firstly, we need to set bind-address to the master IP address:

bind-address = 10.0.0.11

More info about bind-address is here.

Secondly, set the server-id which is located in the [mysqld] section.

server-id = 1

It can be any number provided that it must be unique within the replication topology. Usually, we pick number 1 for the master and following numbers for the slaves.

Next, we set the location of the binary log file. This file contains records of the data changes to be sent to slave databases. Whenever a replication slave connects to a master, the master will feed the slave with replication events from the binary log.

log_bin = /var/log/mysql/mysql-bin.log

Finally, save all the changes to the configuration file and restart the server:

$ sudo service mysql restart

Step 3: Creating a user for replication

This step will create a user account on the master which the slave will use to connect.Log in to the MySQL shell

$ mysql -u root –p

Then create a user and grant it the privileges required for replication:

mysql> CREATE USER ‘repl_user’@’%’ IDENTIFIED BY ‘your_password’;
mysql> GRANT REPLICATION SLAVE ON . TO ‘repl_user’@’’;
mysql > FLUSH PRIVILEGES;

In this example repl_user is the username of the account.

Step 4: Get the master binary log coordinates

This step involves checking the master’s current coordinates within its binary log so that the slave could start the replication process at the correct point. Firstly, flush all tables and block write statements

mysql> FLUSH TABLES WITH READ LOCK;

Secondly, view the current position and the current binary log file:

mysql > SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

In this example, the binary log file is mysql-bin.000003 and the position is 73. This is the replication coordinates at which the slave should begin processing new updates from the master.

Step 5: Configure the slave server

Now log in the second ECS instance (the slave) and install MySQL if necessary. Similar to what we did on the master, open the configuration file

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

and make the following changes

server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log

The slave reads binary log events from the master as they come in and copies them over to a local log file called relay log.

Save the changes and restart the server

$ sudo service mysql restart

Log in MySQL console

$ mysql -u root –p

then configure the slave database with the values we used in the previous step

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO
-> MASTER_HOST=‘10.0.0.11’,
-> MASTER_USER=‘repl_user’,
-> MASTER_PASSWORD=‘your_password’,
-> MASTER_LOG_FILE=’ mysql-bin.000003’,
-> MASTER_LOG_POS=73;
mysql> START SLAVE;

Summary

We have gone through the steps to set up a MySQL master-slave replication topology on two Alibaba Cloud ECS instance. This tutorial is just a brief overview to familiarize you with basic concepts of MySQL replication. There are a lot more options and capabilities of MySQL replication which you can learn in the MySQL manual and other tutorials.

2 Likes