
Business Intelligence as a Service
Try PlusClouds Eaglet service and find high quality B2B hot leads and opportunites with AI support.
Before moving to the how-to section, let’s briefly mention the requirements.
Now that we have briefly talked about the requirements, we can move on to the configuration part.
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 *
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.
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.
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';
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.
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.
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.
If you are a journalist, who has desire to dig deep in topics related to businesses and sectors, we want to work with you!
With the AutoQuill tool that PlusClouds has started to offer to its affiliate partners, content creators will be able to sell with a single click. Let us show you how you can get your share of this revolution.