How to set up MySQL replication on CentOS

In this tutorial, we’ll show you how to set up MySQL master replication on two CentOS 7 servers, but the same steps should work on all RPM based systems. We will be using two test CentOS 7 virtual servers for this tutorial with the following IP addresses:

MASTER: 192.168.1.2 SLAVE: 192.168.1.3

Master Server:

Logging into the master server as root

ssh [email protected]_Address

and install MySQL server if not already installed

yum install mysql-server mysql

Start the MySQL server and add it to startup

systemctl start mysql
systemctl enable mysql

Then open the MySQL configuration file with a text editor and add the following lines to the file

vi /etc/my.cnf

server-id = 1
binlog-do-db=database
relay-log = mysql-relay-bin
relay-log-index = mysql-relay-bin.index
log-bin = mysql-bin

Remember to replace “database” with the actual name of the database you want to copy to the slave server.

Save the configuration file and restart the MySQL server for the changes to take effect

systemctl restart mysql

Login to MySQL server as root user and create a user to be used by the slave server

mysql -u root -p

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY 'PASSWORD'
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      245 | database     |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> exit

We will need the master coordinates that are going to be used for the slave, so write them down.

File: MySQL-bin.000001 Position: 245

Replace ‘PASSWORD’ with an actual strong password.

Now if the database you want to transfer is not empty, create a dump from the database

mysqldump -u root -p --master-data database > /root/database.sql

and pass the dump file to the slave server

rsync -Waq -e ssh /root/database.sql 192.168.1.3:/root

Slave Server:

The next step is to set up a slave server. Login to the slave server via SSH as the root user and install the MySQL server as we did on the master server.

After installing and starting the MySQL server, open the config file and add the following lines:

vi /etc/my.cnf

server-id = 2
master-host=191.168.1.2
master-connect-retry=60
master-user=slaveuser
master-password=PASSWORD
replicate-do-db=database
relay-log = mysql-relay-bin
log-bin = mysql-bin

Save the file and restart the MySQL server after the change to update the settings.

systemctl restart mysql

Import the MySQL dump file we created on the master server

mysql -u root -p < /root/database.sql

Login to MySQL as root user, stop slave and configure it

mysql -u root -p

mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST='123.456.789.1', MASTER_USER='slaveuser', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;
mysql> slave start;

Use the MySQL username and password that we created on the master server.

You can check the status of the slave by running the following MySQL command:

mysql> show slave status;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 123.456.789.1
                  Master_User: slaveuser
                  Master_Port: 3306
              Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: database

If Slave_IO_State reports “Waiting for master to send event ‘, replication is successful.

That’s all. It is so simple. If you need more information on how to configure the replication wizard you can find it on the MySQL official website.

PS. If you liked this post, please share it with your friends on social media using the buttons on the left side of the post, or just leave a comment below. Thank you in advance.

Sidebar