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 step-by-step with all the code scripts required to set up MariaDB master slave replication easily. Let’s get started?

Table of Contents

• What is MariaDB?
• What is Data Replication?
• Understanding Replication in MariaDB
• Methods to Set Up MariaDB Replication
  • Method 1: MariaDB Replication Using the Master-Slave Approach
  • Method 2: MariaDB Replication Using Hevo
• Conclusion

What is MariaDB?

MariaDB is one of the most popular open-source relational database management system (RDBMS). The original developers of MySQL designed MariaDB as an alternative to the MySQL database after concerns over the acquisition of MySQL by Oracle Corporations in 2009.

It transforms data into a structured and organized form and provides support for a variety of applications. It is fast, scalable, robust, and contains various plug-ins. Some of its notable users include Wikipedia, WordPress.com, Google, etc.

For further information on MariaDB, you can check the official website here.

What is Data Replication?

Data replication is a process of copying data from various databases, known as master databases or servers to multiple slave servers. The master server is known as such because it provides the data for replication. Replicated data can be a complete set of databases, a single database, or even data tables taken from the desired database.

In the master-slave configuration, any changes that are made to one of the slaves, automatically reflect in the master record in a matter of seconds, following which, all the slaves receive the data updations from the master server in a fully-automated manner.

Some key features of replication are:

• Scalability: Having one or more slave servers allows data reads to be done on them, thereby reducing the load on the master server, which only allows performing the write operation.
• Backup Assistance: This involves replicating data to a slave that you can use as backup data. This backup can then act as a stand-alone server in a stable state.
• Data Analysis: Data can be analyzed on the slave server without adding extra load to the master server with replication in place.
• Distribution of Data: With replication in place, you can work locally on this data without connecting to the master server. Upon subsequent connection, the updated data will get merged with the master.

Here’s another blog piece that sheds light on the different types of data replication.

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.

Methods to Set Up the MariaDB Replication

• Method 1: MariaDB Replication Using the Master-Slave Approach
• Method 2: MariaDB Replication Using Hevo

Method 1: 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.

Method 2: MariaDB Replication Using Hevo

Hevo Data, a no-code data pipeline, helps you replicate data from MariaDB to data warehouses, business intelligence tools, or any other destination of your choice in a completely hassle-free & automated manner. Hevo 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.

Check out what makes Hevo amazing:

• Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
• 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.
• Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations.
• 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.
• Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
• 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.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

With continuous real-time data movement, Hevo allows you to replicate your MariaDB data along with your other data sources and seamlessly load it to the destination of your choice with a no-code, easy-to-setup interface. Try our 14-day full-feature access free trial!

Get Started with Hevo for Free

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 Data provides an automated no-code data pipeline that empowers you to overcome the above-mentioned limitations. Hevo caters to 150+ data sources (including 40+ free sources) and can seamlessly perform MariaDB replication in real-time. Hevo’s fault-tolerant architecture ensures a consistent and secure replication of your MariaDB data. It will make your life easier and make data replication hassle-free.

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!