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.

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:

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:

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:

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.

Step 4: Lock the Master:

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

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:

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

Step 6: Backup the Master DB:

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:

Step 8: Copy the File to the Slave:

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

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:

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:

Step 3: IMPORT THE DB DUMP:

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

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

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.