MySQL master-slave replication is a process that automatically copies data from one MySQL database server (master) to one or more MySQL database servers (slaves).
MySQL supports various replication methods, but Master-Slave is the best of them.
- Master-slave replication
- Master-master replication
- Multi-source replication
The master-slave replication can be made using the following two methods:
- Binary Log File Position Based Replication Configuration
- Replication with Global Transaction Identifiers (GTIDs)
The traditional method is based on replicating events from the master’s binary log, and the log files and positions in them must be synchronized between master and slave.
The newer method based on global transaction identifiers (GTIDs), which does not relay on the log file. When using GTIDs, each transaction can be identified and tracked as it is committed on the originating server and applied by any slaves.
Replication in MySQL supports different types of synchronization. The original type of synchronization is one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves.
You can replicate all databases, selected databases, or even selected tables within a database based on your needs.
In this tutorial, we will explain how to configure a MySQL Master/Slave replication with one master and one slave server on CentOS 7.
The same procedure works for other distributions.
- Configure the Master Server
- Configure the Slave Server
- Finally Test this Set up
The server details are as follows:
Master IP: 192.168.1.113 Slave IP: 192.168.1.114
Why Do You Need to Implement This?
This setup provides redundancy and fault tolerance for databases, which helps you retrieve them based on your needs.
Prerequisites for MySQL Master/Slave Replication
Make sure that the master and slave should use the same version of MySQL, because replication between different versions may cause problems.
If you haven’t already installed MySQL on your system, use the below yum command to install it.
# yum update && yum install MariaDB-server MariaDB-client
For CentOS/RHEL 8 use the dnf command to install it.
# dnf -y install @mysql
Allow the database service through the firewall using the below commands.
# firewall-cmd --add-service=mysql --permanent # firewall-cmd --reload
1) How to Configure the Master Server for MySQL Master/Slave Replication
To do so, edit the “my.cnf” or “my.ini” file and configure the binary log and server ID options. Also, change the “loopback” address to match the IP address of the Master node.
# vi /etc/my.cnf [mysqld] server-id = 1 bind-address = 192.168.1.113 log-bin = mysql-bin
Make a note: The server ID must be a unique number and it will not be used again.
Once you make changes, restart the MySQL service for the changes to take effect.
$ sudo systemctl restart mysqld
Next, log in to MySQL and create a replica user. This user will be used by Slave Server to request binary logs from the master.
# mysql -u root -p Enter Password : *****
Run the commands below to create a user and grant the replication slave privilege. For demonstration purposes, we have added a username called “replica_user” and you can change it according to your needs.
Make sure you have given the user a strong password.
mysql> CREATE USER 'replica_user'@'192.168.1.114' IDENTIFIED BY 'your_password'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'192.168.1.114'; Query OK, 0 rows affected (0.00 sec)
Flush the privileges for changes take effect.
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
Run the command below to determine the current binary log file name and their position. These details are required in the slave configuration.
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 336
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> quit;
Now you have successfully configured the Master server.
2) How to Configure the Slave Server for MySQL Master/Slave Replication
Add the necessary changes to the slave like the master, but the values must be changed accordingly.
# vi /etc/my.cnf [mysqld] server-id = 2 bind-address = 192.168.1.114 log-bin = mysql-bin
Once you made the changes, restart the MySQL service for changes to take effect.
$ sudo systemctl restart mysqld
To configure the slave to communicate with the master for replication, configure the slave with the necessary connection information as follows:
Login to MySQL shell.
# mysql -uroot -p
First, stop the slave threads:
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.113,
-> MASTER_USER='replica_user',
-> MASTER_PASSWORD='your_password',
-> MASTER_LOG_FILE='mysql-bin.000006',
-> MASTER_LOG_POS=336;
Once done, start the slave threads.
mysql> START SLAVE;
Now, check the slave server status.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.113
Master_User: replica_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 336
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 1269
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1354
Relay_Log_Space: 1424
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
3) How to Test the Configuration
To validate this, we are going to create a test database on the Master Node.
# mysql -uroot -p mysql> CREATE DATABASE replica_test;
Now, check this on the slave by executing the following command.
# mysql -uroot -p mysql> SHOW DATABASE; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | replica_test | | sys | +--------------------+ 5 rows in set (0.00 sec)
Conclusion
In this tutorial, you learned how to configure MySQL master-slave replication in CentOS 7. This process works seamlessly on the new system setup, but you need to make additional configuration changes if you want to replicate an existing database.We will cover this in an upcoming article.
