This article focus on how to setup database replication between two MySQL 5.7 servers on Ubuntu 16.04 LTS server. Database replication is mostly used for backups, improved availability and some also use it to run analytic queries and reports.

If you don’t already have MySQL 5.7 installed on your server, the following command will install MySQL 5.7 on your server since Ubuntu 16.04 LTS repositories come with MySQL 5.7.

$ sudo apt-get install mysql-server

Having setup the two identical servers running MySQL, which can communicate with each other on a private network and that the servers have the IPs:

master IP: 10.10.10.146
slave IP:  10.10.10.147

Replication Master Setup

First, let’s setup the master replication node. Because this process takes a consistent snapshot of the database on the server; there will be a global read lock on the database. This means the database will not accept any data or the database will be read-only and all writing to the database will be blocked for the period of the lock. If you are working on a live database server, you’ll have to plan for this downtime.

Step 1: Update MySQL Configuration:

In the file /etc/mysql/mysql.conf.d/mysqld.cnf make sure the following are uncomment or set:

bind-address = 10.10.10.146
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

These three lines of command make the replication master listen on the private IP 10.10.10.146, sets its server-id to 1, which should be unique for each database servers on the replication and binary log is also enable

Step 2: Restarting MySQL Service for Changes to take effect:

sudo systemctl restart mysql

Step 3: Create a User for Replication:

Let’s set a dedicated account for the slave to connect to the master. The account will be given the REPLICATION SLAVE privilege. Here we are using the username slave_repl.

$ mysql -u root -p
Password:

mysql> CREATE USER 'slave_repl'@'10.10.10.147' IDENTIFIED BY 'slavereplicationpassword';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_repl'@'10.10.10.147';
Query OK, 0 rows affected (0.00 sec)

Step 4: Lock the Master:

Next, we lock the master database to get a consistent snapshot to get started with the slave.

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

Note: this lock is removed either by exiting MySQL CLI client or by issuing UNLOCK TABLES. Make the lock remains until we backup the database in step 6.

Step 5: Note down the Master Replication Log File and Position:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      813 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

However, keep the log information for the initialization of the slave replication steps.

Step 6: Backup the Master DB:

$ mysqldump -u root -p --all-databases --master-data > dbname.sql

Note: mysqld can be stopped and then transfer the db files to the slave server. This page has more details.

Step 7: Unlock the Master Databases:

You can unlock the master database by exiting MySQL CLI shell or by running:

mysql> UNLOCK TABLES;

Step 8: Copy the File to the Slave:

Transfer or copy the dump file securely to the slave server, for e.g., using scp:

$ scp dbname.sql 10.0.0.157:/tmp

Replication Slave Setup

Step 1: UPDATE THE CONFIGURATION:

In file /etc/mysql/mysql.conf.d/mysqld.cnf, make sure the following are uncomment or set:

bind-address = 10.10.10.147
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log

Like for the master above, the slave listens on the private IP 10.10.10.147, sets its server_id to 2, and enable binary logging.

Step 2: RESTART THE MYSQL SERVICE FOR CHANGES TO TAKE EFFECT:

sudo systemctl restart mysql

Step 3: IMPORT THE DB DUMP:

Import the dump database file copied from the master to this slave:

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

Step 4: Setup the Slave to talk to the Master:

$ mysql -u root -p
Password:

mysql> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.10.10.146',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='slavereplicationpassword',
    -> MASTER_LOG_FILE='mysql-bin.000006',
    -> MASTER_LOG_POS=813;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

Now, the slave is in sync with the master and ready to replication changes from the master. The MASTER_LOG_FILE and MASTER_LOG_POS value mysql-bin.000006 and 813 respectively come from the output of the SHOW MASTER STATUS that we ran earlier.

Conclusion

Finally, if there is no error after these steps. Test the replication by creating a new table in the master database. The change should reflect in the slave database instantly.