Table of Contents
Table of Contents
Introduction
Replication in MariaDB allows for data redundancy and load balancing by copying data from one database server (the master) to another (the slave). This guide outlines the steps to set up replication on a live production server without significant downtime.
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.
