MySQL 5.7 Master-Slave Replication on CentOS 7

Master Server Prerequisite

• Install MySQL 5.7 server.
• Take backup on database use mysqldump

Install and configure MySQL 5.7 with master-slave replication.

Enable MySQL repository

yum localinstall -y https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

Install MySQL server

yum install -y mysql-community-server

Start MySQL server

systemctl start mysqld

Enable MySQL service

systemctl enable mysqld

Confirm MySQL service is enabled

systemctl is-enabled mysqld

MySQL Configuration

Get temporary password

grep 'temporary password' /var/log/mysqld.log | tail -1

Initialize secure installation

mysql_secure_installation

Master Server Setup

Open MySQL config file

nano /etc/my.cnf

Add the following lines (replace `database` with the name of your database)

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

Restart MySQL server

systemctl restart mysqld

Login to MySQL server

mysql -u root -p

Create slave user (replace `PASSWORD` with your password)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'PASSWORD';

Flush privileges

mysql> FLUSH PRIVILEGES;

Lock tables until we finish setting up the slave server

mysql> FLUSH TABLES WITH READ LOCK;

Show master status

mysql> SHOW MASTER STATUS;

You will need the master coordinate's `File` and `Position` for the slave server, so write them down

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      245 | database     |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Exit MySQL server

mysql> exit

Backup on Master Server

mysqldump -u root -p -B --events --routines --triggers dbname > /root/dbname.sql

Slave Server Prerequisite

• Install MySQL 5.7 server.

Open Slave MySQL config file

vim /etc/my.cnf

Add the following lines (replace `database` with the name of database to replicate)

server-id = 2
replicate-do-db=database
relay-log = mysql-relay-bin
log-bin = mysql-bin

Restart MySQL server

systemctl restart mysqld

Import master database dumped file if it was created

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

Login to MySQL server

mysql -u root -p

Stop Slave

mysql> STOP SLAVE;

Configure Slave using data created earlier on the Master (replace `PASSWORD` with slave password)

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

Start Slave

mysql> START SLAVE;

Check Slave status

mysql> SHOW SLAVE STATUS G;

If `Slave_IO_State` is `Waiting for master to send event`, then replication was setup successfully.

*************************** 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

Exit MySQL server

mysql> exit

Master Server Side

Login to MySQL server

mysql -u root -p

Unlock tables

mysql> UNLOCK TABLES;