Introduction
Replication in MySQL is a powerful feature that allows data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). It’s an essential strategy for data redundancy, fault tolerance, and scaling out read queries. In this tutorial, we’ll walk through the steps of creating a replica of a MySQL 8 database using the mysqldump utility.
Prerequisites
- A running MySQL 8 database server instance that will act as the master.
- A fresh MySQL 8 server instance that will be configured as the replica.
- Appropriate network settings to allow communication between the master and replica servers.
- Basic knowledge of MySQL server administration.
Step 1: Configure The Master Server
Before creating a dump of the master database, we should configure the master server to be replication-aware. Edit the MySQL configuration file typically located at /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf.
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=row
expire_logs_days=7
Restart the MySQL service to apply changes:
sudo systemctl restart mysql.service
Next, we need to create a replication user:
mysql> CREATE USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'replica_password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
Step 2: Lock the Master Database
To create a consistent backup, we must prevent changes from occurring during the dump. Enter the MySQL monitor on the master:
mysql> FLUSH TABLES WITH READ LOCK;
Keep this session open to maintain the lock. In a new session, check the master’s binary log coordinates:
mysql> SHOW MASTER STATUS;
Record the values of File and Position; these will be needed when setting up the replica.
Step 3: Create the Dump
While the master is locked, perform the dump using the mysqldump tool:
mysqldump --all-databases --master-data --single-transaction --user=root --password > master_db_dump.sql
This will create a dump file named master_db_dump.sql with data necessary to set up replication.
Step 4: Import the Dump on the Replica
Transfer the dump file to the replica server and import it:
mysql -u root -p < master_db_dump.sql
Step 5: Configure The Replica Server
Edit the replica’s MySQL configuration file, setting the server-id to a unique value:
[mysqld]
server-id = 2
relay-log = mysql-relay-bin.log
gtid_mode = ON
enforce_gtid_consistency = ON
Restart the MySQL service on the replica:
sudo systemctl restart mysql.service
Step 6: Set up Replication
In the MySQL shell on the replica, execute the following commands with the correct master log file name and position recorded previously:
mysql> CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='replica', MASTER_PASSWORD='replica_password', MASTER_LOG_FILE='recorded_log_file', MASTER_LOG_POS=recorded_log_position;
mysql> START SLAVE;
Check the replication status:
mysql> SHOW SLAVE STATUS\G
You should see Slave_IO_Running and Slave_SQL_Running values as ‘Yes’. If there are issues, they will need to be addressed based on the error messages provided.
Conclusion
In this tutorial, we’ve covered the basic steps to create a replica of a MySQL 8 database using the mysqldump tool. Creating a replica ensures data redundancy and can improve the performance of your database system by distributing the read load across servers. Regularly monitor and maintain your replication setup to ensure that it continues to function correctly and efficiently.
