Table of Contents
Introduction
Setting up replication on MariaDB might sound intimidating, but it’s one of the most effective ways to ensure your data is always safe, synchronized, and scalable. Whether you’re a system admin, a developer, or running a production database, replication allows you to maintain real-time backups, improve performance, and prepare for disaster recovery. In this practical, experience-backed guide, I’ll walk you through how to setup replication on MariaDB on Production Server from configuring your primary and replica servers to verifying your setup with confidence.
If you are configuring Replication on New servers Stop here and please follow below Article.
Prerequisites
- Two servers running MariaDB (one will be the master, and the other will be the slave).
- Root access to both servers.
- Basic knowledge of MariaDB and SQL.
- Ensure that both MariaDB servers are running the same version.
Step 1: Prepare the Master Server
1.1. Create a Replication User
First, create a replication user on the master server. This user will be used by the slave server to connect to the master.
sudo mysql -u root -p
Then execute the following SQL commands:
CREATE USER 'replica'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;
Replace 'your_password' with a strong password.
1.2. Configure the Master Server
Edit the MariaDB configuration file to enable binary logging and set a unique server ID.
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Add the following lines under the [mysqld] section:
[mysqld]
log-bin
server-id=1
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
log-binenables binary logging, which is required for replication.server-idmust be unique for each server in the replication setup.binlog-ignore-dbexcludes certain databases from replication.
1.3. Restart MariaDB
Restart the MariaDB service to apply the changes.
sudo systemctl restart mariadb
1.4. Obtain the Current Binary Log Coordinates
Lock the tables to ensure consistency and get the current binary log file and position.
sudo mysql -u root -p
Execute the following SQL commands:
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
Note the File and Position values from the output. You will need these for the slave configuration.
1.5. Backup the Database
While the tables are locked, create a backup of the database.
mysqldump -u root -p --all-databases --master-data > /path/to/backup.sql
Once the backup is complete, unlock the tables:
UNLOCK TABLES;
Step 2: Set Up the Slave Server
2.1. Transfer the Backup to the Slave Server
Copy the backup file to the slave server using scp or any other secure method.
scp /path/to/backup.sql user@slave_server:/path/to/backup.sql
2.2. Restore the Backup on the Slave Server
Log into the slave server and restore the backup.
mysql -u root -p < /path/to/backup.sql
2.3. Configure the Slave Server
Edit the MariaDB configuration file on the slave server.
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Add the following lines under the [mysqld] section:
[mysqld]
server-id=2
relay-log=relay-bin
log-bin
server-idmust be unique and different from the master server’s ID.relay-logspecifies the relay log file name.
2.4. Restart MariaDB
Restart the MariaDB service to apply the changes.
sudo systemctl restart mariadb
2.5. Set Up the Slave to Replicate from the Master
Log into MariaDB as root on the slave server.
sudo mysql -u root -p
Run the following SQL commands, replacing the placeholders with the actual values obtained from the master server:
CHANGE MASTER TO
MASTER_HOST='master_server_ip',
MASTER_USER='replica',
MASTER_PASSWORD='your_password',
MASTER_LOG_FILE='log-bin.000001',
MASTER_LOG_POS=154;
Start the slave process:
START SLAVE;
2.6. 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: YesSlave_SQL_Running: Yes
Step 3: 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 a running MariaDB production server. This setup allows the slave server to continuously replicate data from the master server, providing redundancy and load balancing. Regularly monitor the replication setup to ensure it remains in sync and performs as expected.
Setting up replication in MariaDB is a practical way to enhance your database’s scalability and reliability—whether you’re planning for failover, reporting, or load balancing. While this guide walks you through the core steps with hands-on insights, it’s always a good idea to explore the official documentation for advanced configurations, edge cases, or the latest updates.
👉 For more in-depth and up-to-date details, check out the official MariaDB replication documentation:
https://mariadb.com/kb/en/standard-replication/
