Before moving to the how-to section, let’s briefly mention the requirements.
- We need at least 2 physical or virtual machines. In this article, we will use two virtual machines running Debian 10. We highly recommend using the same distributions. The same MySQL version must be installed on both virtual machines.
- Firewall configuration for ufw.
- An NTP server that is installed and synchronized must be present on both master and slave machines for communication.
Now that we have briefly talked about the requirements, we can move on to the configuration part.
1. NTP Configuration
Our first step will be to configure the same NTP configuration on both machines. If you haven’t installed it yet, you can set up the NTP server using the command below.
apt -y install ntp
Now we will configure the NTP servers we installed to be synchronized with each other. We will use Asia NTP servers in this article.
*nano /etc/ntp.conf*
server 0.asia.pool.ntp.org iburst
server 1.asia.pool.ntp.org iburst
server 2.asia.pool.ntp.org iburst
server 3.asia.pool.ntp.org iburst
After editing the conf file, save it and restart the NTP service.
* /etc/init.d/ntp restart *
2. Our Machines Must Recognize Each Other
All the servers we are using must recognize each other and communicate without issues. If available, you can use local DNS. In our example, since we do not own a DNS, we will configure the hosts file.
*nano /etc/hosts*
master_server ip master_server_hostname
slave_server_ip slave_server_hostname
Remember that all the configurations we will make must be done accurately on all servers. DO NOT FORGET.
3. Firewall Configuration
If you haven’t installed it at the beginning of this article, you should first install ufw using the command below.
apt -y install ufw
If you are connecting via SSH, you should allow the SSH port first after installation. Otherwise, you might lose your SSH connection when you start the ufw service.
ufw allow 22
ufw allow from slave_server_ip to any port 3306
systemctl enable ufw
systemctl start ufw
ufw enable
We first allowed port 22, which is the SSH port, and the rule we wrote is for port 3306, which is used by MySQL. Don't forget to replace slave_server_ip with the IP address of your slave machine.
If everything so far is complete, we can now begin the configuration of our master server.
4. Master Server Configuration
In Debian 10, the MySQL server configuration file is under the /etc/mysql/mysql.conf.d/ folder with the name **mysqld.cnf**. First, we open it in the **nano** editor on our master server. You can use any other editor you prefer.
nano /etc/mysql/mysql.conf.d/mysqld.cnf
After opening the file, the first thing we do is edit the bind address. If the line is not there, add it as shown below.
bind-address = master_server_ip
After editing the bind-address, find the **server-id** line. If it's not there, add it as above. You can set it to 1; this value should be unique for each server.
server-id = 1
After completing this step, we need to specify the location of the MySQL binary log file. If this path is not specified, binary logging will be inactive, and your slave server will not know what to replicate since it cannot read the log file. We specify the path as follows.
log_bin = /var/log/mysql/mysql-bin.log
Lastly, we need to specify the database or databases to be replicated.
binlog_do_db = testdb
After making the changes, save the file by pressing CTRL + X, Y, and ENTER in sequence.
After exiting, we restart the MySQL service.
systemctl restart mysql
We have completed the configuration section of our master server, but before moving to our slave server, there are a few small tasks we need to perform here. We need to create a user that the slave server can connect with and grant it permissions. Let's proceed by entering the MySQL shell.
mysql -u root -p
CREATE USER 'slave_user'@'slave_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';
Note that this command specifies that the slave user will use the mysql native password authentication plugin. It is also possible to use MySQL's default authentication mechanism, caching_sha2_password, but this requires an encrypted connection between the master and slave server. Such a setup would be ideal for production environments, but the encrypted connection configuration is not present in this tutorial.
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'slave_server_ip';
FLUSH PRIVILEGES;
With this, we have completed the configuration of the master server. Although our configuration is complete, do not exit the MySQL shell just yet. In the next step, we will obtain important information related to the binary log file of our database.
MySQL performs replication by copying database events from the source's binary log file line by line and applying each event on the slave server. Therefore, while configuring on the slave server side, we must also specify the name of the binary log file from our master server and the coordinates that detail a specific position in this file.
To ensure that no user has modified any data while obtaining the coordinates, we need to briefly lock the database.
FLUSH TABLES WITH READ LOCK;
After that, run the code below to view the status of the binary log files.
SHOW MASTER STATUS;
```
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 899 | testdb | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
```
You will receive an output similar to the one above.
If there is no database yet in your master server, first unlock it and create the database.
UNLOCK TABLES;
CREATE DATABASE testdb;
After this step, we are done with our work on the master server. Do not forget the information about the binary log file we obtained from the output above. We will use it shortly on our slave server.
5. Slave Server Configuration
In this section, we change the configuration file similarly to how we did on the master server. Since the steps are already explained above, we will quickly go through them.
nano /etc/mysql/mysql.conf.d/mysqld.cnf
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = testdb
Lastly, we add the relay-log directive to define the path of the relay log file on our slave server.
relay-log = /var/log/mysql/mysql-relay-bin.log
After finishing the changes, we also restart the MySQL service here.
systemctl restart mysql
We are now ready to test the replication process. We log in to the MySQL shell on our slave server.
mysql -u root -p
Now it’s time for the replication process. We enter the information we need to provide for the slave server connected to the master server.
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='master_server_ip',
SOURCE_USER='slave_user',
SOURCE_PASSWORD='password',
SOURCE_LOG_FILE='mysql-bin.000001',
SOURCE_LOG_POS=899;
START REPLICA;
If all the steps were carried out correctly, from this point onward, all changes made in the **testdb** database will also be sent to our slave server.
SHOW REPLICA STATUS\G
We can view the replica status on our slave server with the command above.
```
*************************** 1. row ***************************
Replica_IO_State: Waiting for master to send event
Source_Host: 'slave_server_ip'
Source_User: slave_user
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.000001
Read_Source_Log_Pos: 1273
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 729
Relay_Source_Log_File: mysql-bin.000001
```
You will get an output similar to the one above. Now we have MySQL Replication created with a Master-Slave relationship. You can test by adding any table to your database on the master server.
