# MySQL 5.7 Master-Slave Replication on CentOS 7

Install and configure MySQL 5.7 with master-slave replication. The benefits of this include high availability, backups, disaster recovery and reporting.

## Install MySQL 5.7

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

### Prerequisite

- Install MySQL 5.7 server by following instructions above
- Create a `database` or import your database mysqldump

Open MySQL config file

```
vim /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
```

If the `database` you want to replicate is not empty, create a dump from the `database`

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

Transfer the dumped file to the slave server

```
rsync -Waq -e ssh /root/database.sql 123.456.789.2:/root
```

## Slave Server

### Prerequisite

- Install MySQL 5.7 server by following instructions above

Open 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/database.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

Login to MySQL server

```
mysql -u root -p
```

Unlock tables

```
mysql> UNLOCK TABLES;
```

Exit MySQL server

```
mysql> exit
```