MariaDB replication is a process that allows you to automatically copy data from one database server to another server. In this article, we are going to discuss how to configure MariaDB Master-Slave replication on an AlmaLinux platform through a comprehensive example.
Let us get started.
In this example, we are assuming that you have two servers running CentOS 8, which can communicate with each other over a private network. If your hosting provider doesn’t provide private IP addresses, you can use the public IP addresses and configure the firewall to allow traffic on
port 3306 only from trusted sources.
We can also create a private network between these servers.
The servers have the following IPs:
Master Server IP: 192.168.0.101
Slave Server IP: 192.168.0.102
Installation of MariaDB
Create a file in the
/etc/yum.repos.d/ folder and add these lines in the
cd /etc/yum.repos.d/ vim mariadb.repo [mariadb] name = MariaDB baseurl = [http://yum.mariadb.org/10.5/centos8-amd64](http://yum.mariadb.org/10.5/centos8-amd64) module_hotfixes=1 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
Then, save and exit.
Next, we will install
dnf install MariaDB-server -y. Once the installation is complete, start the MariaDB service and enable it to automatically start on boot with the following lines of code:
systemctl start mariadb systemctl enable mariadb
Once the boot code is in place, we will secure the MariaDB Database Server by using the
mariadb-secure-installation command as shown:
Note: Running all parts of this script is recommended for all MariaDB servers in production use. Go through them all carefully.
In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank.
Enter current password for root (enter for none): OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorization.
- Set root password? [Y/n]y New password: Re-enter new password: Password updated successfully! Reloading privilege tables... ... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove this before moving into a production environment.
- Remove anonymous users? [Y/n]y ... Success!
Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess the root password from the network.
- Disallow root login remotely? [Y/n]y ... Success!
By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing and should be removed before moving into a production environment.
- Remove test database and access to it? [Y/n]y
- Dropping test database... ... Success!
- Removing privileges on test database... ... Success!
Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
- Reload privilege tables now? [Y/n] y ... Success!
All done! If you've completed all of the above steps, your MariaDB installation should now be secure.
Thanks for using MariaDB!
Configure the Master Server.
You need to edit
/etc/my.cnf file. Add following lines in this file
[mysqld] bind-address=192.168.0.101 server-id=1 log_bin=mysql-bin binlog-format=ROW
Important Note: - server-id is important
Once done, restart the MariaDB service for changes to take effect
systemctl restart mariadb
The next step is to create a new replication user. Log in to the MariaDB server as the root user.
From inside the MariaDB prompt, run the following SQL queries that will create the
user and grant the
REPLICATION SLAVE privilege to the user.
CREATE USER 'khalid'@'192.168.0.102' IDENTIFIED BY 'strong_password'; GRANT REPLICATION SLAVE ON . TO 'khalid'@'192.168.0.102';
Make sure you change the IP with your slave IP address.
While still inside the MariaDB prompt, execute the following command that will print the binary filename and position.
`SHOW MASTER STATUS\G` Connection id: 9000633 Current database: *** NONE *** *************************** 1. row *************************** File: mysql-bin.000001 Position: 1021 Binlog_Do_DB: Binlog_Ignore_DB: test,information_schema,mysql 1 row in set (0.001 sec)
Take note of file name,
mysql-bin.000001 and Position
1021. You’ll need these values when configuring the slave server. These values will probably be different on your server.
Now we need to configure the Slave Server
Open the MariaDB configuration file and edit the following lines.
sudo vim /etc/my.cnf [mysqld] bind-address=192.168.0.102 server-id=2 log_bin=mysql-bin binlog-format=ROW
Restart the MariaDB service.
systemctl restart mariadb
The next step is to configure the parameters that the slave server will use to connect to the master server. Login to the MariaDB shell.
$ mysql -u root -p
First, stop the slave threads:
MariaDB [(none)]> STOP SLAVE;
Run the following query that will set up the slave to replicate the master:
MariaDB [(none)]> CHANGE MASTER TO MariaDB [(none)]> MASTER_HOST='192.168.0.101', MariaDB [(none)]> MASTER_USER='khalid', MariaDB [(none)]> MASTER_PASSWORD='strong_password', MariaDB [(none)]> MASTER_LOG_FILE='mysql-bin.000001', MariaDB [(none)]> MASTER_LOG_POS=1427;
Make sure you are using the correct IP address, user name, and password. The log file name and position must be the same as the values you obtained from the master server.
Once done, start the slave threads.
MariaDB [(none)]> START SLAVE;
Now, We have a working Master/Slave Replication Setup.
To verify that everything works as expected, we will create a new database on the master server.
Login into Master Server (192.168.0.101)
$ mysql -u root -p
MariaDB [(none)]> CREATE DATABASE geekyants;
Now to verify it, Login into Slave Server (192.168.0.102)
$ mysql -u root -p
MariaDB [(none)]> SHOW DATABASES;
You will notice that the database you created on the master server is replicated on the slave:
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | geekyants | | sys | +--------------------+ 5 rows in set (0.00 sec)
In this tutorial, we have shown you how to create a MariaDB Master/Slave replication on CentOS 8.
Some Important Notes on Binary Log file
The binary log file stored the data in binary format. That makes it quicker for writing log information. The binary log file only capture Data Changing information, it is also used for replication.
How to Enable Binary log:
In the case of MariaDB server, Please make changes to
[mysqld] log-bin=bin.log log-bin-index=bin-log.index max_binlog_size=100M binlog_format=row
How to Read binary log files:
sudo mysqlbinlog binary-log-file-name