How to Set Up MySQL Replication as a Backup Mechanism?
MySQL replication is a powerful tool for ensuring fault tolerance and creating a database backup mechanism. With replication, you can create a copy of the primary database that is automatically updated when changes occur in the primary database.
Setting up MySQL replication as a backup mechanism may seem like a complex task, but with the right approach, it can be done without much difficulty. In this article, we will look at the basic steps to set up MySQL replication.
Step 1: Creating a User for Replication
The first step is to create a user that will be used for data replication. To do this, run the following command:
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'password';
Remember that the password must be complex enough to ensure the security of your database.
Step 2: Configuring the Primary Server
Configure the primary server for data replication by adding the following parameters to the MySQL configuration file:
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog-do-db = my_database
Remember to replace «my_database» with the name of your database. After making changes, restart the MySQL server.
Step 3: Configuring the Replication Server
Now configure the replication server by adding the following parameters to its configuration file:
server-id = 2
master-host = основной_сервер
master-user = replication_user
master-password = пароль
replicate-do-db = my_database
Here, replace «основной_сервер» with the IP address or domain name of your primary server, and also specify the username and password that you created in step 1. After making changes, restart the MySQL server on the replication server.
Congratulations! You have successfully set up MySQL replication as a backup mechanism. Now your database will be more reliable and protected from data loss.