How to setup a MySQL Master-Slave Replication on CentOS 7

MySQL replication is a technique by which a master database will be automatically copied to one or more slave databases, making data backup, recovery, and analysis a much easier prospect. The following guide will walk you through a basic MySQL replication setup using just two servers, one master and one slave. We’ll also be using MariaDB, a popular drop-in replacement for MySQL.

Getting Started

Before you begin, make sure you have the following:
• 2 servers (Cloud Server or Dedicated Server), both running a fresh installation of CentOS 7
• Root access to both machines

Tutorial

Note: these are the local IP addresses our servers will be using for the purposes of this guide.
• Server Master IP: 10.0.0.179
• Server Slave IP: 10.0.0.180

Before starting, we need to make sure that Selinux is disabled on each of our servers.

setenforce 0
sed -i 's/enforcing/disabled/' /etc/sysconfig/selinux
sed -i 's/enforcing/disabled/' /etc/selinux/config

Let’s also install MariaDB on each server, making sure to secure the installation.

yum install mariadb-server mariadb -y
systemctl start mariadb.service
mysql_secure_installation

Now we can begin the setup, focusing first on the master server. Go to my.cnf on the master server and add these 3 lines:

nano /etc/my.cnf

[mysqld]
...
server_id=1
log-bin
replicate-do-db=globotech

Replace globotech with the name of the database that you want to replicate.

To save the changes, you will have to restart MariaDB.

systemctl restart mariadb.service

Now let’s load up MariaDB itself so that we can better configure the replication.

mysql -u root -p

MariaDB [(none)]> grant replication slave on *.* to 'slave_user'@'%' identified by 'globopassword';
MariaDB [(none)]> flush privileges;

Replace slave_user with the slave username and globopassword with its password.
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 | 475 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Make a note of the log file “mariadb-bin.000001” and the log position “475” for later on in the guide.
MariaDB [(none)]> exit

The next step is to configure with the slave server. Like before, open up its my.cnf and add these 2 lines:
[mysqld]
...
server_id=2
replicate-do-db=globotech

Of course, replace globotech with the name of your own database.

Next, restart MariaDB so the changes will be recognized.

systemctl restart mariadb.service

We will now configure the slave to connect to the master.

mysql -u root -p

MariaDB [(none)]> stop slave;
MariaDB [(none)]> change master to master_host='10.0.0.179' , master_user='slave_user' , master_password='globopassword' , master_log_file='mariadb-bin.000001' , master_log_pos=475;
MariaDB [(none)]> start slave;

Replace 10.0.0.179 with the IP address for your master server, and add in the log file and log position that you noted earlier.

We’re almost done. Let’s check the replication status.

MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.179
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 475
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 531
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: globotech
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: 475
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)

Finally, let’s test that replication is actually occurring. Log in to the master server and enter MariaDB.

mysql -u root -p

Create the database globotech and insert a table.
MariaDB [(none)]> create database globotech;
MariaDB [(none)]> use globotech;
MariaDB [globotech]> create table staff (c int);
MariaDB [globotech]> insert into staff (c) values (3);
MariaDB [globotech]> select * from staff;
+------+
| c |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

Once logging into the slave server, you should see the same database and table.
mysql -u root -p

MariaDB [(none)]> use globotech;
MariaDB [globotech]> select * from staff;
+------+
| c |
+------+
| 3 |
+------+
1 row in set (0.00 sec)

Conclusion

With MySQL (or in this case, MariaDB) replication, you’ll be able to better protect your databases in the case of failure, and safely analyse data without worrying about corrupting your production copy. If you found this article helpful, feel free to share it with your friends and let us know in the comments below!