How to Setup MariaDB Replication: 2 Easy Methods

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.

Methods to Set Up the MariaDB Replication

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

Method 1: MariaDB Replication Using Hevo

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

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.

Step 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.

Step 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 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

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.

For further information on replication in MariaDB, you can check the MariaDB 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.

FAQ on MariaDB Replication