How to Setup Mariadb Replication tutorial. Data Replication is very crucial when it comes to protecting and ensuring high availability data and the ease of access during unexpected error like system crash, hardware or software-based error, etc. One of the data replication, MariaDB is a replication that can take place in various mechanisms that includes master-slave, master-master, and start and multi-source mechanism.
What is MariaDB?
MariaDB is the popular open-source Relational Database Management System (RDMS). It was designed by the original developer of MySQL as an alternative after businesses got concerned with the acquisition of MySQL by Oracle Corporations in 2009.
Â
MariaDB works by transforming data into a structured and organized form and gives support to multiple applications. It is the fastest, scalable, sturdy, and encompasses several plug-ins. Some of its significant users are Wikipedia, Google, WordPress.com, and a lot more.
How MariaDB Replication Works
With MariaDB, you can either replicate the entire database or select a subsequent amount of data from the database. MariaDB replication utilizes an enormous slave configuration and enables the binlog on the master server. This master server uses a global transaction ID to write every transaction to the binary log.
Â
With Global Transaction ID, you can effortlessly determine the same binlog on separate servers replicating each other. The binary log has all the records concerning the changes done in the database to both the data and structure. It also takes care of the time taken to execute each statement. Furthermore, slave servers read this binary log from the master server when the replication of data is required. It, as a whole, makes MariaDB an excellent data replication software.
Features Of MariaDB Replication
Copying data from multiple databases is known as replication. The databases that are to be copied are known as master databases or servers. The replicated data might include multiple or single databases or data tables used from the desired database.
Â
The primary features of MariaDB Replication are:
Â
- Scalability – When you have one or more slave servers, you can read data on them. It, thereby, reduces the load on the master server in which only accurate operations can be performed.
- Data Analysis – You can effortlessly analyze data on the slave server, thereby reducing the burden on the master server when MariaDB replication is updated in place.
- Backup Assistant – Backup Assistance allows you to replicate data, which can be used as backup data. This backup data further acts as stand-alone data in a stable state.
- Distribution Of Data – When you have MariaDB replication in place, you tend to work locally on this data without even connecting to the master server. By connecting subsequently, you can merge the updated data with the master data.
In this post, we will show you how to set up MariaDB replication on Ubuntu 20.04.
Deploy MariaDB in the Cloud (2-Click Deployment)
Try setting up MariaDB replication on Azure, AWS or GCP and test it out for yourself.
Step 1 - Install MariaDB on All Nodes
First, you will need to install the MariaDB server package on both Master and Slave nodes. You can install it by running the following command:
apt-get install mariadb-server -y
After installing the MariaDB server, start the MariaDB server service and enable it to start after system reboot:
systemctl start mariadb
systemctl enable mariadb
Next, you will need to secure the MariaDB installation and set a MariaDB root password. You can do it by running the mysql_secure_installation script:
mysql_secure_installation
Answer all the questions as shown below:
Enter current password for root (enter for none): Enter
Set root password? [Y/n] Y
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y
Once you are finished, you can proceed to configure the Master node.
Step 2 - Prepare the Master Node
MariaDB server uses a binary log file to perform the replication. By default, the binary log is disabled in the MariaDB default configuration. So you will need to edit the MariaDB configuration file and enable the binary log.
nano /etc/mysql/mariadb.conf.d/50-server.cnf
First, change the bind-address from localhost to 0.0.0.0:
bind-address = 0.0.0.0
Next, add the following lines at the end of the file to enable the binary log:
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
Save and close the file when you are finished then restart the MariaDB service to apply the changes:
systemctl restart mariadb
At this point, MariaDB is configured and listens on port 3306. You can check it with the following command:
ss -antpl | grep mysql
You should get the following output:
LISTEN 0 80 0.0.0.0:3306 0.0.0.0:* users:(("mysqld",pid=3425,fd=22))
Step 3 - Create a Replication User on Master Node
Next, you will need to create a replication user on the Master node. The Slave node will use this user to connect to the Master server and request binary logs.
To create a replication user, connect to the MariaDB with the following command:
mysql -u root -p
Provide your MariaDB root password and hit Enter. Once you are connected, you should get the following shell:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1-log Ubuntu 20.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
Next, create a replication user and set a password:
MariaDB [(none)]> CREATE USER 'replication'@'%' identified by 'securepassword';
Next, grant replication slave privilege to the user with the following command:
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
Next, flush the privileges to apply the changes:
MariaDB [(none)]> FLUSH PRIVILEGES;
Next, verify the Master status using the following command:
MariaDB [(none)]> SHOW MASTER STATUS;
You should get the binary log file name and position in the following output:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 786 | | |
+------------------+----------+--------------+------------------+
Finally, exit from the MariaDB console using the following command:
MariaDB [(none)]> EXIT;
Note: Please remember File and Position value from the above output. You will need this value on the Slave server.
Step 4 - Prepare the Slave Node for Replication
Next, you will need to enable the relay log and replication on the Slave node. You can do it by editing the MariaDB main configuration file:
nano /etc/mysql/mariadb.conf.d/50-server.cnf
First, change the bind-address from localhost to 0.0.0.0:
bind-address = 0.0.0.0
Next, add the following lines at the end of the file to enable relay log and replication:
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
Save and close the file then restart the MariaDB service to apply the changes:
systemctl restart mariadb
Next, you will need to set up the Slave node to replicate the Master node.
First, connect to the MariaDB with the following command:
mysql -u root -p
Once you are connected, stop the Slave with the following command:
MariaDB [(none)]> STOP SLAVE;
Next, set up the slave to replicate the master with the following command:
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST = '69.87.220.86', MASTER_USER = 'replication', MASTER_PASSWORD = 'securepassword', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 786;
Where:
Â
- 69.87.220.86 is the IP address of the Master node.
- replication is the replication user.
- securepassword is the replication user password.
- mysql-bin.000001 is the binary log file name.
- 786 is the binary log file position.
Next, start the Slave with the following command:
MariaDB [(none)]> START SLAVE;
Next, exit from the MariaDB with the following command:
MariaDB [(none)]> EXIT;
Step 5 - Verify MariaDB Replication
At this point, MariaDB Master and Slave node is configured. Now, you will need to test whether the replication is working or not.
First, go to the Master node and connect to the MariaDB console:
mysql -u root -p
Once you are connected, create a database named schooldb:
MariaDB [(none)]> CREATE DATABASE schooldb;
Next, switch the database to schooldb and create a table named students:
MariaDB [(none)]> USE schooldb;
MariaDB [schooldb]> CREATE TABLE students (id int, name varchar(20), surname varchar(20));
Next, insert some data into students table:
MariaDB [schooldb]> INSERT INTO students VALUES (1,"hitesh","jethva");
MariaDB [schooldb]> INSERT INTO students VALUES (2,"jayesh","jethva");
Now, verify the inserted data with the following command:
MariaDB [schooldb]> SELECT * FROM students;
You should get the following output:
+------+--------+---------+
| id | name | surname |
+------+--------+---------+
| 1 | hitesh | jethva |
| 2 | jayesh | jethva |
+------+--------+---------+
Now, go to the Slave node and connect to the MariaDB with the following command:
mysql -u root -p
Once you are connected, check the Slave status with the following command:
MariaDB [(none)]> SHOW SLAVE STATUS \G
If everything is fine, you should get the following output:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 69.87.220.86
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1491
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 1260
Relay_Master_Log_File: mysql-bin.000001
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: 1491
Relay_Log_Space: 1569
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 2
Now, list all databases using the following command:
MariaDB [(none)]> SHOW DATABASES;
You can see that the schooldb database is replicated from the Master node:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| schooldb |
+--------------------+
Now, switch the database to schooldb and list all tables using the following command:
MariaDB [schooldb]> USE schooldb;
MariaDB [schooldb]> SHOW TABLES;
You should see the tables named students which we have created on the Master node:
+--------------------+
| Tables_in_schooldb |
+--------------------+
| students |
+--------------------+
You can also verify the tables data using the following command:
MariaDB [schooldb]> SELECT * FROM students;
You should see that table’s data also replicated from the Master node:
+------+--------+---------+
| id | name | surname |
+------+--------+---------+
| 1 | hitesh | jethva |
| 2 | jayesh | jethva |
+------+--------+---------+
Setting Up MariaDB Replication Conclusion
In the above guide, we explained how to set up MariaDB replication on Ubuntu 20.04 server. You can now implement this setup in the production environment to simplifies recovery upon one of the master nodes failures. MariaDB replication is the best backup solution to backup a database from one Master database server to one or more Slave servers.
Just remark. No more such script as mysql_secure_installation, it has new name in MariaDB: mysql-secure-installation