High Availability Replication

MariaDB replication is a fundamental feature utilized by businesses and developers to maintain high availability, ensure data redundancy, and improve server performance. Replication involves having changes made on a server (master) replicated on one or more servers (slaves). These features make MariaDB favourable in many instances like high-traffic websites, large volume data analytics, and backups. This tutorial will take you through how to set up and use MariaDB high availability replication.

To get started with MariaDB replication, it’s crucial to understand its architecture. As mentioned, it consists of at least two MariaDB servers - a single master and one or multiple slaves. The master server records all changes occurring inside it in a binary log. The slaves then use these logs to sync themselves with the master server.

Setting up MariaDB Replication:

Before setting up MariaDB replication, ensure you have MariaDB installed on all servers that will be participating in the replication process.

1. Configuring the Master:

The master server needs to be set to allow binary logging which records all changes to it. This is done by editing the MariaDB configuration file (usually found in /etc/mysql/my.cnf or /etc/my.cnf) under the [mysqld] section:

[mysqld]
log-bin
server-id=1

Here, log-bin enables binary logging, while server-id sets the ID of the server in the replication setup. Each server should have a unique ID. After setting this, restart the MariaDB server.

sudo systemctl restart mariadb

To replicate a database, it needs to be locked and its data exported. Log into MariaDB and use FLUSH TABLES WITH READ LOCK to flush and lock all tables. To export data, use mysqldump:

FLUSH TABLES WITH READ LOCK;
mysqldump -u root -p my_database > my_database.sql

The tables can then be unlocked:

UNLOCK TABLES;

2. Configuring the Slave:

Edit the MariaDB configuration following the same methodology as for the master server:

[mysqld]
server-id=2

Remember to assign a unique server-id. After this, restart MariaDB, import the database from the master, and log into MariaDB.

Start the replication by indicating the master server’s details, the binary log file, and position:

CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='log_file_name', MASTER_LOG_POS=log_file_position;

Then start the slave threads:

START SLAVE;

3. Testing Replication:

To confirm if the replication is functional, create a dummy record on the master server and check if it’s replicated on the slave.

INSERT INTO test_table (test_column) VALUES ('test_value');

On the slave server, check if the record exists:

SELECT * FROM test_table;

If the replication process is working correctly, the record should be visible on the slave server.

MariaDB Replication Troubleshooting:

In some cases, the slave server might not be in sync with the master server. This can be checked using the command:

SHOW SLAVE STATUS\G;

If the output indicates “Yes” in the Slave_IO_Running and Slave_SQL_Running fields, then replication is functioning correctly. If “No” is indicated, an error is present that needs resolution.

In conclusion, setting up MariaDB replication is a straightforward process but can greatly enhance performance and data availability. With an understanding of the architecture and process of setting up replication, any user can set up and troubleshoot MariaDB replication for optimal server performance.