How to Setup MariaDB Replication: 2 Easy Methods

Unlock the full potential of your MariaDB data by performing MariaDB Replication. With Hevo’s automated pipeline, get data flowing effortlessly—watch our 1-minute demo below to see it in action!

MariaDB replication keeps your data protected and ensures high availability of data and ease of access.

1. This will be helpful, especially in the event of any unexpected error such as a system crash, hardware or software-based error, etc.
2. MariaDB replication can be done using various mechanisms such as a master-slave, master-master, start, and multi-source mechanism.
3. We will present the methods for MariaDB master-slave replication step by step with all the code scripts required to set up MariaDB master-slave replication easily.

Let’s get started.

Overview of MariaDB

MariaDB is an open-source relational database management system(RDBMS) that was created as fork of MySQL. It is developed by the original developers of MySQL. It is designed to offer enhanced performance, improved scalability and data processing capabilities. It maintains its compatibility with MySQL.

Key Features of MariaDB

• MySQL Compatibility: It was designed fully compatible with MySQL, thus the migration from one to another is pretty easy without major application or data changes.
• High Performance and Scalability: MariaDB is designed to efficiently serve hundreds of millions of records. It’s replication and multiple storage engines enable MariaDB to scale as your application does.
• Security and Flexibility: Some of the advanced security features in MariaDB include encryption and user roles. Advanced SQL functions support complex queries and give you enormous flexibility in managing your data.

You can also check MariaDB vs MySQL and compare the two to get a better understanding of how MariaDB functions.

Understanding MariaDB Replication

• MariaDB allows you to either replicate the entire database as a whole or select a specific amount of data from your database.
• Replication in MariaDB uses a master-slave configuration and enables the binlog on the master server, where all data updations are done.
• The master server uses a global transaction-id (GTID) for every transaction and writes it to the binary log.
• The global transaction-id (GTID) makes it easy to uniquely identify the same binlog events on different servers that replicate each other.
• The binary log contains a record of all the changes made to the database, both data, and structure, and how long it took each statement to execute.
• Slaves read the binary log (binlog) from each master to access the data for replication. On the slave server, a relay log is created using the same format as the binary log, and this is used to perform the replication.

For further information on replication in MariaDB, you can check the MariaDB official documentation here.

Method 1: MariaDB Replication Setup Using Hevo

• Use Hevo with real-time ELT No-code Data Pipeline platform. 
• Helps in data ingestion for replication from MySQL servers via Binary logs (BinLog).
• A binary log is a collection of log files that records information
• Includes data modifications & data object modifications made on a MySQL server instance.
• Utilizes the Binary logs to perform MariaDB Data replication.

Step 1.1: Select MariaDB as your Source

You can refer to the official documentation for more details on how you can setup your MariaDB source.

Step 1.2: Select your Desired Destination

You can view the list of destinations where you can replicate your MariaDB data seamlessly.

You can check out how you can easily move data from MariaDB to MySQL in just 2 steps to take a look at how you can replicate your MariaDB data.

Method 2: MariaDB Master-Slave Replication Approach

You need the following entities to implement MariaDB Master Slave Replication:

• Working knowledge of MariaDB.
• A MariaDB account.
• A general idea of using the MySQL command-line.

Step 2.1 – Configuring Master Server in MariaDB

To configure the master server, enable the binary log for your MariaDB instance. Once you have enabled it, you now need to provide a unique server-id for your master server.

You can select any number from 1 to (2^32-1). Ensure that each server has a unique number for that replicating group. Specify a unique name for your replication logs by using the log-basename parameter as follows:

 --log-basename

If you do not specify a unique name, MariaDB will automatically take up your hostname. Using your hostname can cause problems in the replication process, especially if the hostname gets updated later on.

You can do this, by adding the following lines of code in your configuration file (my.cnf):

[mariadb]
Log-bin
server_id=1
log-basename=master1
binlog-format=mixed

Restart your database. The slave servers will now require permission to establish a connection and start replicating your server. To do this, create a dedicated user for your slave server and permit them to start replicating your data. Use the following SQL command in the MySQL command-line:

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'bigs3cret';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

To prevent any changes from occurring in the data while viewing the binary log options, you will need to flush privileges and lock tables on the master. You can do this by using the following command:

FLUSH PRIVILEGES; FLUSH TABLES WITH READ LOCK.

Check the status of your binary log using the show command as follows:

SHOW MASTER STATUS;

This will generate the following output:

Note down the file and position details for your MariaDB instance. You will need this to configure your slave server to set replication with MariaDB.

Once your entire data has been copied, you now need to unlock the master by using the unlock tables command as follows:

UNLOCK TABLES; 

This is how you can configure the master server in MariaDB.

You can also take a look at how MySQL Master-Slave Replication works to get a better understanding of this approach.

Step 2.2 – Configuring Slave Server in MariaDB

To configure the slave server, you need to provide a unique server-id for your slave server. You can select any number from 1 to (2^32-1). Ensure that each server has a unique number for that replicating group.

Once you’ve provided a unique name, you now need to restart your slave server for the changes to come into effect. Modify the configurations file for your MariaDB instance as follows:

[mysqld]
server-id = 2
replicate-do-db=masterdb

Once your data has been imported, start the replication process by configuring your slave server by using the following command:

CHANGE MASTER TO;

Ensure that the Master_Log_File and Master_Log_Pos values match the values you had carefully noted down after calling the master status command earlier. You can use the change master command as follows:

CHANGE MASTER TO
  MASTER_HOST='master.domain.com',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='bigs3cret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000096',
  MASTER_LOG_POS=568,
  MASTER_CONNECT_RETRY=10;

You can include the global transaction-id (GTID) by adding the Master_Use_GTID option to the change master statement as follows:

CHANGE MASTER TO MASTER_USE_GTID = slave_pos

Start your slave server and check if the replication is working properly. If replication is set up correctly, the parameters Slave_IO_Running and Slave_SQL_Running will have the same value as yes. You can check it using the following command:

CHECK SLAVE STATUSG;

This should produce the following output on your screen:

This is how you can set up MariaDB Master-Slave Replication.

If you’re interested in learning more about MariaDB and its related concepts, like MariaDB foreign key, take a look at our resources.

Before we wrap up, here’s a table that describes replication compatibility between the different MariaDB Server versions. As such, you must ensure that the replica is at least equivalent in version to the primary.

Cross-Version Replication Compatibility

Benefits and Use Cases of MariaDB Replication

Benefits of MariaDB Replication

1. High Availability: By replicating data across multiple servers, MariaDB replication minimizes downtime, ensuring your applications stay available even if one server fails.
2. Load Balancing: Replication allows you to distribute read requests across multiple servers, reducing the load on the main database and improving performance.
3. Data Backup: Replicating data creates additional copies, making it easier to back up data and recover it quickly in case of unexpected issues.

Use Cases of MariaDB Replication

1. Reporting and Analytics: By directing analytical and reporting queries to replica databases, you can prevent these intensive queries from slowing down your main production database.
2. Disaster Recovery: Replication enables you to set up standby servers that can quickly take over if the primary server goes down, keeping your data safe and accessible.
3. Geographically Distributed Applications: Replication allows businesses to have database copies closer to users in different regions, improving access speed and user experience.

Conclusion

• This article teaches you how to set up MariaDB replication with ease and answers all your queries regarding it.
• It provides a brief introduction of various concepts related to it & helps the users understand them better and use them to perform data replication & recovery in the most efficient way possible.
• While you can use the Master-Slave method to set up MariaDB replication as described in this post, it is quite effort-intensive and requires in-depth technical expertise.

FAQ on MariaDB Replication