OSETC TECH

How to Setup MySQL Master-Slave Replication on Ubuntu 16.04 or 18.04

osetc

This post will guide you how to configure MySQL Master-Slave replication on your Ubuntu Linux 18.04 or 16.04. How do I setup MySQL or MariaDB Replication on your Ubuntu Linux system. How to configure Master-Slave to replicate data in your MySQL server.

What is MySQL Replication?

MySQL or MariaDB database is one of the most popular open source relational database management system now. And MySQL Replication is a process that allows your data from one Master database to be copied to others Slaver database servers. So it will maintain two or more copies of a MySQL data. The Master-Slave will have only one master server which takes the write requests and other slave servers will be used for read only purpose.

There is another way to replicate data in MySQL database, it is Master-Master.
This post will guide you how to configure a basic way of replicating data with one master server and one slave server on Ubuntu Linux server. And the below steps is also valid for MariaDB.

Prerequisites

• You need to have two separate servers running on Ubuntu 16.04 or 18.04 Linux Operating System,
• You also need to have a non-root user with sudo-privileges for both Ubuntu system
• Two IP addresses: 192.168.3.42 (master server), 192.168.3.53(slave server)

Step1: Install MySQL On Both Master and Slaver Nodes

The first step is installing MySQL or MariaDB package on your both Master and Slave server with the following apt command, type:

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

Outputs:

devops@devops-VirtualBox:~$ sudo apt install mysql-server mysql-client
Reading package lists... Done
Building dependency tree
Reading state information... Done
mysql-server is already the newest version (5.7.26-0ubuntu0.18.04.1).
The following NEW packages will be installed:
mysql-client
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 9,820 B of archives.
After this operation, 110 kB of additional disk space will be used.
Do you want to continue? [Y/n]
Get:1 http://mirrors.aliyun.com/ubuntu bionic-security/main amd64 mysql-client all 5.7.26-0ubuntu0.18.04.1 [9,820 B]
Fetched 9,820 B in 0s (37.5 kB/s)
Selecting previously unselected package mysql-client.
(Reading database ... 168082 files and directories currently installed.)
Preparing to unpack .../mysql-client_5.7.26-0ubuntu0.18.04.1_all.deb ...
Unpacking mysql-client (5.7.26-0ubuntu0.18.04.1) ...
Setting up mysql-client (5.7.26-0ubuntu0.18.04.1) ...

Note: you need to press Y key and press Enter key when prompted to continue the installation of MySQL server on your Ubuntu system. You’d better to install the same version of MySQL-server package on both Master and Slave servers(the latest stable version of MySQL server is 5.7).

Step2: Configure the Master MySQL Server

You need to modify the configuration file of MySQL located /etc/mysql/mysql.conf.d/mysqld.cnf on Master Server(192.168.3.42) with your text editor vi or vim, type:

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

Then you need to find the line bind-address, and replace the standard ip address with the IP address of master server(192.168.3.42)

bind-address = 192.168.3.42

Next, you need to set the unique server ID, set it in the [mysqld] section, and you can set any number for this variable, but the number must be unique. For example, you can set the server ID as number 1.

server-id=1

Then add the below lines at the end of the configuration file:

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

Save and close the file.

Once done, and you need to restart the MySQL service with the following command:

$ sudo systemctl restart mysql

Step3: Create New User for Replication on Master Server

You need to create a replication user on Master Server for Replication data, just login to Mysql server with the following command:

$ mysql -u root -p

Executing the following SQL statements on the MySQL prompt to create the replication user:

create user 'replica'@'%' identified by 'password#';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
flush privileges;

Note: you need to replace “password#” with your own password.

Outputs:

mysql> create user 'replica'@'%' identified by 'password#';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;

Then you need to get the information about the File name of the Master server and Position number, just type the following sql statement:

Show master status;

Outputs:

mysql> show master status
-> ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 753 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

For the above outputs, you should see that the file name is “mysql-bin.000001” and the position number is 753. You will use those values when configurating the salve server.

Step4: Configure the Slaver MySQL Server

You can start to modify the configuration file of MySQL on Slave server, the configuration file is also located in /etc/mysql/mysql.conf.d/mysqld.cnf. Using vi or vim text editor to open up that configuration file, and look for the line bind-address and change the standard IP address to the IP address of Slave server(192.168.3.53).

bind-address=192.168.3.53

Set the Unique Server ID for slave server:

server-id=2

then add the below lines at the end of the file:

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

save and close the file.

You now need to restart the MySQL server on Slave server for the changes to take effect with the following command:

$ sudo systemctl restart mysql

Then you need to configure the parameters that the slave server will use to connect to the Master server. Open up the MySQL Shell on Slave server with the following command:

$ mysql -u root -p

Enter the root password of MySQL server, and press Enter key to login. Then you need to execute the following SQL statements.

mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_HOST = '192.168.3.42', MASTER_USER = 'replica', MASTER_PASSWORD = 'PASSWORD', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 753;
mysql>start slave;
mysql>quit;

Note: you need to replace MASTER_LOG_FILE and MASTER_LOG_POS with the information that you get it in the Step 3. You also need to make sure that you are using the correct IP address of Master server, Master user and Master password.

Step5: Test the Configuration

So far, you should setup a Master-Slave of MySQL replication on Ubuntu server. If you want to check if the replication works well, you just need to create a new database on the master server, then check if this database can be listed on Slave server.
On Master server, executing the following SQL statement to create a new database called “replicaData”:

Mysql>create database replicaData;

Outputs:

mysql> create database replicaData;
Query OK, 1 row affected (0.01 sec)

Then login to the Slave server, check the databases with the following SQL statement:

Mysql>show databases;

Outputs:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| joomla |
| mysql |
| performance_schema |
| replicaData |
| sys |
+--------------------+
6 rows in set (0.01 sec)

You should see that the database that you created in Master server has been replicated to Slave server.

Conclusion

You should know that how to setup MySQL Master-Slave server to replicate data on two separate systems running on Ubuntu or Debian Linux.