MySQL Master/Slave Replication for On-Prem

Following is a handy guide to setup MySQL replication for on-premise installations.

A MySQL user is required on the master server (db1) to be used for replication.

Run the following commands to set up the MySQL user, updating the entries in brackets with strings or values you that you want to use with your setup.

# mysql -u root -p
mysql> grant replication slave on *.* TO [replication-username]@'[db2-ip]' identified by '[replication-password]';
mysql> flush privileges;
mysql> quit

Edit the /etc/my.cnf file and add the following entries:

bind-address=0.0.0.0
server-id=1
log-bin=mysql-bin
binlog-do-db=cloud9
binlog-ignore-db=mysql
binlog-ignore-db=test

After you have finished updating the /etc/my.cnf file, restart the MySQL service.
```
# service mysqld restart
```
Before starting replication, the data on each server (master and slave) must be the same. To accomplish this duplication, dump the data from the master (db1) server and add it to the slave (db2) server, as instructed in the following.

Use the following command to ensure that nothing can write to the master database during a database dump. Also, note the filename and position of the binary log because you will need these values to complete the replication configuration on db2.

# mysql -u root -p
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

+------------------+--------------------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+--------------------------+------------------+
| mysql-bin.000010 |        10 | cloud9       | mysql            |
+------------------+--------------------------+------------------+
1 row in set (0.00 sec)

Perform a database dump by using mysqldump as follows. list all the databases barring mysql and information_schema:
```
# mysqldump -u root -p --databases cloud9 > /root/db_dump.sql
```
After the database dump has completed, lift the read lock from the master (db01):
```
# mysql -u root -p
mysql> UNLOCK TABLES;
```
Copy the database dump file to the slave server so that it can be restored. You can use the scp command to accomplish this:
```
scp /root/db_dump.sql [db2-ip]:/root/
```

On db02, edit the /etc/my.cnf file and add the following entries:

bind-address=0.0.0.0
server-id=2
log-bin=mysql-bin
binlog-do-db=cloud9
binlog-ignore-db=mysql
binlog-ignore-db=test

Import the db_dump.sql file copied earlier and restart the MySQL service.
```
# mysql -u root -p < /root/db_dump.sql
# service mysqld restart
```
Complete the slave replication steps:
```
# mysql -u root -p
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='[db1-ip]',
MASTER_USER='[replication-username]',
MASTER_PASSWORD='[replication-password]',
MASTER_LOG_FILE='[file-listed-on-db1-status]',
MASTER_LOG_POS=[log-position-listed-on-db1-status];
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
```
The Slave_IO_State field should show “Waiting for master to send event”. If it shows “Connecting to Master” please check your MySQL log file. By default, it is /var/log/mysqld.log but it may be configured differently on your system. As always /etc/my.cnf will define the location of your log file.