MySQL Master Slave Replication for database load balancing

MySQL replication is a method of storing data on many systems, and this data is automatically copied from one database (Master) to another database (Slave). If one server goes down, the data can still be accessed from the other servers (Slave) database.

In this article, we’ll look at how to set up MySQL Master-Slave replication in CentOS 7. This method will work on all Linux distributions, including RHEL, CentOS, Ubuntu, and openSUSE etc. All you need to know is how to install MySQL in the specific distribution you use

MySQL Replication Types

There are three types of replication, depending on how data transfer is managed when transferred from the master to the slaves.

Asynchronous – slaves do not need to be connected permanently to receive updates from the master. By default, MySQL Replication is asynchronous.

Semi-synchronous – With semi-synchronous replication, a commit performed on the master side is held until at least one slave acknowledges that it has received and logged the events for the transaction.

Synchronous–  Slaves must acknowledge receipt from the master.

Please go through the link for detailed information.

MySQL Replication Setup

We will be using the following two systems for this tutorial. But you can use any Linux distribution to set up MySQL replication. Only the installation of MySQL will differ for different Linux Operating Systems. 

Installing LAMP Stack on Linux

Here is the testing systems details:

MySQL Master: CentOS 7 64bit Minimal Server

Master IP Address: 10.0.6.25

MySQL Slave: CentOS 7 64bit Minimal Server

Slave IP Address: 10.0.6.64

Configure the MySQL Master Server for Replication 

1.The first step is to allow MySQL default port “3306” through Firewall or Router.

As we use CentOS 7, we can allow the port as shown below.

firewall-cmd --permanent --add-port=3306/tcp

Reload firewall rules using command:

firewall-cmd --reload

2. Edit /etc/my.cnf file,

vi /etc/my.cnf

For testing the replication, we are going to set up a Sample MySQL Database from github.

git clone https://github.com/datacharmer/test_db
cd test_db/
mysql -u root -p 

2.1 And add the following lines under [mysqld] section

server-id = 1
binlog_do_db = employees
log_bin = /var/log/mysql/mysql-bin.log

2.2 (Optional) If you are using a MariaDB database server, then you should add the following lines to the my.cnf file.

[mysqld]
server_id=1
log-basename=master
log-bin
binlog-format=row

Here “employees” is the database name to be replicated to the Slave system.

P.S. Remove the binlog_do_db and binlog_ignore_db configuration options from the master my.cnf configuration

The above parameter should not be included in case of a single DB replication as it may cause sync issues and the filtering should be done on the slaves.

3. Once you are done, restart the MySQL service using command:

systemctl restart MySQL

Now login to MySQL as root user:

mysql -u root -p

4. Create a Slave user and password. For example, we will use replication as Slave username and “nKHnhZXa2SY82jm” as password:

MySQL [(none)]> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'nKHnhZXa2SY82jm';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

MySQL [psa]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File           	| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| MySQL-bin.000001 |  	803 | employees	|              	|
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MySQL [psa]> exit
Bye

Note down the file (MySQL-bin.000001) and position number (803), you may need these values later.

5. Before start configuring the slave, backup and copy the data from the master to the slave.

mysqldump -u root -p employees > employees-backup.sql
scp employees-backup.sql root@null10.0.6.64:/root/

Configure MySQL Slave for Replication

We have done Master side installation. Now we have to start on Slave side. 

Install MySQL packages on Slave server as stated in the MySQL installation section. Furthermore, don’t forget to allow the port “3306” through the firewall/router.

1. Edit file /etc/my.cnf

vi /etc/my.cnf

And add the following entries under [mysqld] section as shown below.

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

1.1 (Optional) If you are using a MariaDB database server, then you should add the following lines to the my.cnf file.

[mysqld]
server-id = 2
Replicate-do-db = employees

Here, “employees” is the database name of Master server. Be mindful that you should use different server-id for both master and slave servers.

Save and exit the file.

2.Import the master database using command:

mysql -u root -p 

3. Now, log in to MySQL as root user and run the below Type the following SQL command to activate replication.

MySQL [psa]> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MySQL [psa]> CHANGE MASTER TO MASTER_HOST='10.0.6.25', 
MASTER_USER='replication', MASTER_PASSWORD='nKHnhZXa2SY82jm', 
MASTER_LOG_FILE='MySQL-bin.000001', MASTER_LOG_POS=803;
Query OK, 0 rows affected (0.01 sec)

Now start the slave with the START SLAVE command and if you want to check the status of slave use SHOW SLAVE STATUS command:

MySQL [psa]> SLAVE START;
Query OK, 0 rows affected (0.01 sec)

MySQL [psa]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
           Slave_IO_State: Waiting for master to send event
           Master_Host: 10.0.6.25
           Master_User: replication
           Master_Port: 3306
           Connect_Retry: 60
           Master_Log_File: MySQL-bin.000003
      	   Read_Master_Log_Pos: 1117
           Relay_Log_File: MySQL-relay-bin.000006
           Relay_Log_Pos: 531
           Relay_Master_Log_File: MySQL-bin.000003
           Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
           Replicate_Do_DB: employees
      	   Replicate_Ignore_DB:
       	   Replicate_Do_Table:
      	   Replicate_Ignore_Table:
      	   Replicate_Wild_Do_Table:
           Replicate_Wild_Ignore_Table:
           Last_Errno: 0
           Last_Error:
           Skip_Counter: 0
      	   Exec_Master_Log_Pos: 1117
           Relay_Log_Space: 827
           Until_Condition: None
           Until_Log_File:
           Until_Log_Pos: 0
       	   Master_SSL_Allowed: No
       	   Master_SSL_CA_File:
       	   Master_SSL_CA_Path:
           Master_SSL_Cert:
           Master_SSL_Cipher:
           Master_SSL_Key:
    	   Seconds_Behind_Master: 0
           Master_SSL_Verify_Server_Cert: No
           Last_IO_Errno: 0
           Last_IO_Error:
           Last_SQL_Errno: 0
           Last_SQL_Error:
           Replicate_Ignore_Server_Ids:
           Master_Server_Id: 1
1 row in set (0.00 sec)

Test MariaDB or MySQL Replication

In the master server, add a record to the employees table using the following command:

MySQL [employees]> INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) 
VALUES (500003, '1900-01-01', 'Ankur', 'Barman', 'M', '2000-01-01');
Query OK, 1 row affected (0.00 sec)

MySQL [employees]>

After adding some records in master server, go to slave server and verify if the data is replicated or not :

MySQL [employees]> SELECT * FROM employees.employees WHERE emp_no=500003;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 500003 | 1900-01-01 | Ankur  	| Barman	| M  	| 2000-01-01 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

MySQL [employees]>

Monitor MySQL master slave replication with a Shell script

Here is a simple shell script to notify secondary read-only replica (slave) MySQL server health status failure via email.

Set up a cron job as follows to monitor the MySQL slave server health. This will run the script for every 10 minutes and send a health report via email.

*/10 * * * * root /root/bin/check-mysql-slave-server-health

Conclusion on MySQL replication

In this guide, we have taken you through the steps of setting up a Master-Slave configuration for your MySQL databases on two separate Linux VPS instances. We also have proven the concept by adding a sample database and some records.

Here are some links to our recommended blog posts for more helpful tips and howtos: 

VPS Hosting – The most asked questions and answers

The post How to Configure MySQL Master Slave Replication in CentOS 7 appeared first on Diadem Technologies.