MariaDB Replication Master Slave Method: 2 Easy Methods

MariaDB replication keeps your data protected and ensures high availability of data and ease of access. This will be helpful, especially in the event of any unexpected error such as a system crash, hardware or software-based error, etc. MariaDB replication can be done using various mechanisms such as a master-slave, master-master, start, and multi-source mechanism.

This article teaches you how to set up replication in MariaDB using the master-slave mechanism. 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?

Methods to Set Up the MariaDB Replication

We will go through 2 main methods in this section.

Method 1: MariaDB Replication Using Hevo

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations like data warehouses, but also transform & enrich your data, & make it analysis-ready.

Hevo also supports data ingestion for replication from MySQL servers via Binary logs (BinLog). A binary log is a collection of log files that records information about data modifications and data object modifications made on a MySQL server instance. Hevo utilizes the Binary logs to perform your MariaDB Data replication.

To learn more, check out Hevo’s documentation for MariaDB replication.

Get Started with Hevo for Free

Check out what makes Hevo amazing:

• Auto Schema Mapping: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data from MariaDB and replicates it to the destination schema without zero data loss.
• Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use for aggregation.
• Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.

Method 2: MariaDB Replication Using the Master-Slave Approach

You need the following entities to implement this approach:

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

You can use the following steps to configure a master and a slave server:

• Configuring Master Server in MariaDB
• Configuring Slave Server in MariaDB

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.

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 replication using the master-slave mechanism.

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

Image Source

Understanding Replication in MariaDB

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.

Types of Replication in MariaDB

MariaDB allows users to replicate data using a variety of methods:

• Master-slave replication.
• Master-master replication.
• Multi-source replication.
• Star replication.

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

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.

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. Hevo caters to 150+ data sources (including 40+ free sources) and can seamlessly perform MariaDB data replication in real-time. Hevo’s fault-tolerant architecture ensures a consistent and secure replication of your MariaDB data.

Visit our Website to Explore Hevo

Want to take Hevo for a spin? Sign up for a 14-day free trial and experience the feature-rich Hevo suite firsthand.

Why don’t you share your experience of setting up MariaDB replication in the comments? We would love to hear from you!