Replication involves synchronizing data across multiple separate databases, which can be highly beneficial when working with databases. Having multiple backups of your data can provide redundancy in case one of the database servers fails or becomes flooded, and it can improve the availability, scalability, and overall performance of your database.
MySQL is an open-source relational database management system that has become the most widely used RDBMS in the world today. It offers built-in replication features that enable you to keep multiple copies of your data.
This article will provide you with a step-by-step guide on how to set up replication bewtween two MySQL 5.7 servers on Ubuntu 20.04 LTS.
Prerequisites
To complete this guide, you will need:
Two servers running Ubuntu 20.04.
MySQL installed on each server.
If MySQL 5.7 is not yet installed on your server, you can download it from this repository. To install it — navigate to the mysql folder and obtain a copy of the signature.key file (which can also be found here) — as well as the install_sql script. After obtaining these files, run the script. Upon completion, a message confirming a successful installation should be displayed.1
mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper
Alternatively, use:
$ sudo apt-get install mysql-server
Please note that the process described in this guide involves selecting the MySQL installation on a specific server as the MASTER(primary) database, and then configuring the MySQL installation on a second server to act as a SLAVE(replica) of the primary/master. Master-Slave Replication
To set up the master replication node, a global read lock is imposed on the database, rendering it read-only and blocking any data writes. Plan for downtime if working on a live database server.
Replication — MASTER Setup
Step 1 — Adjust MASTER(primary) server firewall
If UFW is enabled on your primary server, it may block connection attempts from the replica. To allow the connection, add a UFW rule on the source server using the following command:
primary:~$ sudo ufw allow from replica_server_ip to any port 3306
Be sure to replace replica_server_ip with your replica server’s actual IP address. If the rule was added successfully you’ll see the output: Rule added.
Step 2 — Update MASTER MySQL Configuration
On the primary server, open the MySQL configuration file /etc/mysql/mysql.conf.d/mysqld.cnf with your preferred text editor and update with the following configurations:
Comment out the
bind-addressparameter to allow external connections.Assign a unique identifier with the
server-iddirective. Choose a unique number as this directive’s value for each server. Default is 1.Define the binary log file with
log_bindirective.Use the database name for
binlog_do_dbdirective to replicate an existing database.
. . .
# bind-address = 127.0.0.1
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = db
. . .
After making these changes, save and close the file, and restart the MySQL service by running the following command:
sudo systemctl restart mysql
Step 3 — Creating a Replication(SLAVE) User
Create a dedicated account with REPLICATION SLAVE privilege for the slave(replica) to connect to the master(primary).
primary:~$ sudo mysql
mysql> CREATE USER 'replica_user'@'replica_server_ip' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'replica_server_ip';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
NOTE: Use this command to connect to MySQL if you configured a dedicated user with password authentication:
mysql -uroot -p
Step 4 — Get Binary Log Coordinates From MASTER
To make sure that no users change any data while you retrieve the coordinates, which could lead to problems, you’ll need to lock the database.
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
-- get the master's log file status
mysql> SHOW MASTER STATUS;
Output:
Output
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 899 | db | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
This is the position from which the replica will start copying database events. Record and note the Filename and the Position value, will be needed later.
Note: the table lock is removed either by exiting MySQL CLI*client or by issuing*
UNLOCK TABLES. Do not unlock yet.
Step 6 — Backup the MASTER(primary) DB
Open a new terminal window/tab to create a locked-table database snapshot without unlocking MySQL. Then, establish another SSH session to your primary MySQL instance from the new terminal.
primary:~$ ssh user@primary_server_ip
primary:~$ sudo mysqldump -u root db > db.sql
Include the name of your own database in place of db.
From the MySQL prompt, unlock the databases to make them writable again:
mysql> UNLOCK TABLES;
mysql> exit
You can now send your snapshot file to your replica server. This can be done securely using scp if you’ve configured SSH keys on both your servers.
scp db.sql user@replica_server_ip:/tmp/
Replication — SLAVE setup
After sending the snapshot to the replica server, SSH into it and open up the MySQL shell:
replica:~$ sudo mysql
mysql> CREATE DATABASE db;
mysql> exit;
Step 1 — Import db Snapshot
replica:~$ sudo mysql db < /tmp/db.sql
Step2 — Update REPLICA MySQL Configuration
Like the primary mysql configuration, open the file/etc/mysql/mysql.conf.d/mysqld.cnf and set the following:
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = db
# location of the replica’s relay log file
relay-log = /var/log/mysql/mysql-relay-bin.log
After making these changes, save and close the file. Then restart MySQL on the replica to implement the new configuration:
replica:~$ sudo systemctl restart mysql
Final step — Starting and Testing Replication
Both MySQL instances are now ready for replication. To start replicating data from your master(primary), open up the the MySQL shell on your replica server:
Be sure to replace primary_server_ip with your primary server’s IP address. Likewise, replica_user and password should align with the replication user you created in Step 3; and mysql-bin.000001 and 899 should reflect the binary log coordinates you obtained in Step 4.
replica:~$ sudo mysql
mysql> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST='primary_server_ip',
-> MASTER_USER='replica_user',
-> MASTER_PASSWORD='password',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=899;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
If details are correct, the instance will start to replicate changes made to the db database on the primary. To view the replica’s current state, run the operation.
mysql> SHOW REPLICA STATUS\G;
Conclusion
Your MySQL replica is now successfully replicating data from the primary database. Any modifications made to the primary/master database will be immediately reflected on the replica instance. You can confirm this by creating a test table on the master database and verifying that it is successfully replicated on the replica/slave instance.
If you have any questions or encounter any issues during the setup process, please leave a comment below. Thank you for reading!
You can support me by following me on this blog, and also following me on Twitter.
References / More Resources
Here are some additional resources i found useful.
https://www.digitalocean.com/community/tutorials/how-to-set-up-replication-in-mysql
https://dev.mysql.com/doc/refman/5.7/en/replication-snapshot-method.html