In this tutorial we will show you how to setup basic MySQL replication on two Centos server, but the same steps should work on all RPM based systems. We will use two test CentOS 7 virtual servers for this tutorial with the following IP addresses:

MASTER: 192.168.10.1
SLAVE: 192.168.10.2

Master Server:

Login to the master server as user root

and install MySQL server if it is not already installed

yum install mysql-server mysql

Start the MySQL server and enable it to start at boot time

systemctl start mysql
systemctl enable mysql

Next, open the MySQL configuration file with a text editor and add the following lines

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

Don’t forget to replace ‘database‘ with the actual name of the database that you want to replicate to the slave server.

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

systemctl restart mysql

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

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’s coordinates that are going to be used for the slave server, so write them down.

File: mysql-bin.000001
Position: 245

Replace ‘PASSWORD‘ with an actual strong password.

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

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

and transfer the dump file to the slave server

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

Slave Server:

Next step is to configure the slave server. Login to the slave server via SSH as user root and install MySQL server as we did on the master server.

After the MySQL server is installed and started, open its configuration file and add the following lines:

vi /etc/my.cnf

server-id = 2
master-host=192.168.10.1
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 for the changes to change effect.

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 user root stop the slave and configure it where to look for the ‘master log file’

mysql -u root -p

mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.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 we created on the master server.

You can check the slave status by executing the following MySQL command:

mysql> show slave status;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.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’ the replication is successful.