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 [
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_LOG_FILE=’ mysql-bin.000003’,
mysql> START SLAVE;
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.