
Introduction
Replication in MariaDB allows you to create copies of your database on multiple servers. This is useful for load balancing, redundancy, and disaster recovery. In this guide, we will walk through setting up a simple master-slave replication configuration on MariaDB 10.x.
If you are configuring Replication on Production servers STOP here and please follow below Article for Prod Server Replication configuration.
Prerequisites
- Two or more servers running MariaDB 10.x
- Root access to these servers
- Basic knowledge of MariaDB and SQL
Step 1: Install MariaDB on Both Servers
First, ensure that MariaDB is installed on both the master and slave servers.
On Ubuntu/Debian:
sudo apt update
sudo apt install mariadb-server
On CentOS/RHEL:
sudo yum install mariadb-server
Step 2: Configure the Master Server
1. Edit the MariaDB Configuration File
Open the MariaDB configuration file, typically located at /etc/mysql/mariadb.conf.d/50-server.cnf
or /etc/my.cnf
depending on your distribution.
sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
2. Add the Following Configuration
In the [mysqld]
section, add or update the following lines:
[mysqld]
log-bin
server-id=1
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
log-bin
enables binary logging, which is required for replication.server-id
should be a unique identifier for each server. The master and slave must have different IDs.binlog-ignore-db
excludes certain databases from replication.
3. Create a Replication User
Log into MariaDB as root and create a replication user.
sudo mysql -u root -p
Then run the following SQL commands:
CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;
Replace 'password'
with a strong password.
4. Get the Master Status
While still logged in as root, get the current binary log file and position.
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
Take note of the File
and Position
values. You will need these later.
5. Release the Lock
After noting the master status, release the lock.
UNLOCK TABLES;
Step 3: Configure the Slave Server
1. Edit the MariaDB Configuration File
Open the MariaDB configuration file on the slave server.
sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
2. Add the Following Configuration
In the [mysqld]
section, add or update the following lines:
[mysqld]
server-id=2
relay-log=relay-bin
log-bin
server-id
should be unique and different from the master server’s ID.relay-log
specifies the relay log file name.
3. Restart MariaDB
Restart the MariaDB service to apply the changes.
sudo systemctl restart mariadb
4. Set Up the Slave
Log into MariaDB as root on the slave server.
sudo mysql -u root -p
Run the following SQL commands, replacing MASTER_HOST
, MASTER_USER
, MASTER_PASSWORD
, MASTER_LOG_FILE
, and MASTER_LOG_POS
with the appropriate values from your setup.
CHANGE MASTER TO
MASTER_HOST='master_server_ip',
MASTER_USER='replica',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='log-bin.000001',
MASTER_LOG_POS=154;
Start the slave process:
START SLAVE;
5. Verify the Slave Status
Check the status of the slave to ensure it is running correctly.
SHOW SLAVE STATUS\G
Look for the following lines to ensure there are no errors:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Step 4: Testing the Replication
To test the replication, create a new database or table on the master server and check if it appears on the slave server.
On the Master Server:
CREATE DATABASE replication_test;
USE replication_test;
CREATE TABLE test_table (id INT PRIMARY KEY, data VARCHAR(100));
INSERT INTO test_table (id, data) VALUES (1, 'Replication Test');
On the Slave Server:
SHOW DATABASES;
USE replication_test;
SELECT * FROM test_table;
You should see the replication_test
database and the test_table
with the inserted data.
Conclusion
You have successfully set up replication on MariaDB 10.x. This setup allows your slave server to continuously replicate data from the master server, providing redundancy and load balancing. You can further expand this setup to include multiple slave servers or even a multi-master configuration depending on your needs.
Remember to monitor your replication setup regularly to ensure it remains in sync and performs as expected.