MariaDB is an open-source relational database management system (RDBMS) that emerged as a fork of MySQL after concerns over its acquisition by Oracle.
Developed by the original creators of MySQL, MariaDB retains compatibility with MySQL while offering additional features and enhancements.
It supports ACID-compliant transactions, is known for its performance and scalability, and is widely used in various applications for managing and organizing structured data
As a community-driven project, MariaDB is committed to open-source principles, fostering collaboration, and providing a reliable and accessible database solution for developers and organizations.
This tutorial will explain how to install MariaDB on a Debian 12 server as a Master-Slave Replication configuration and verify that it is running and has a safe initial configuration
Here’s a step-by-step guide for setting up a MariaDB Master-Slave replication on Debian 12:
Prerequisites
- Two Debian 12 servers, one for the Master and one for the Slave.
- MariaDB installed on both servers.
- SSH access to both servers.
Step 1: Configure the Master Server
- Edit the MariaDB configuration file on the Master server:
|
1 |
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf |
- Configure replication settings by adding or enabling the following under the
[mysqld]section:
|
1 2 3 4 5 |
bind-address = 0.0.0.0 # Allow connections from any IP server-id = 1 # Unique server ID for Master log_bin = /var/log/mysql/mysql-bin.log # Enable binary logging binlog_do_db = your_database_1 # Datatabase to replicate binlog_do_db = your_database_2 # Database to replicate |
- Replace
your_database_ with the name of the database you want to replicate.
- Restart the MariaDB service on the Master:
|
1 |
sudo systemctl restart mariadb |
- Create a replication user with the necessary permissions:
|
1 |
mysql -u root -p |
In the MariaDB shell, execute:
|
1 2 3 |
CREATE USER 'replica_user'@'%' IDENTIFIED BY 'replica_password'; GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%'; FLUSH PRIVILEGES; |
- Replace
replica_userandreplica_passwordwith your preferred username and password for replication.
- Get the Master status to record the current binary log position:
|
1 |
SHOW MASTER STATUS; |
- Note down the values of
FileandPosition, as these will be needed for configuring the Slave.
|
1 2 3 4 5 |
+------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB| Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 12345 | database1 | | | +------------------+----------+--------------+------------------+-------------------+ |
- Allow the Slave server’s IP in the firewall settings if applicable:
|
1 |
sudo ufw allow from slave_server_ip to any port 3306 |
- Replace
slave_server_ipwith the IP address of your Slave server.
Step 2: Configure the Slave Server
- Edit the MariaDB configuration file on the Slave server:
|
1 |
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf |
- Configure replication settings by adding the following under the
[mysqld]section:
|
1 2 |
server-id = 2 # Unique server ID for Slave relay_log = /var/log/mysql/mysql-relay-bin.log |
- Restart the MariaDB service on the Slave:
|
1 |
sudo systemctl restart mariadb |
- Set up the replication on the Slave server: Log into MariaDB on the Slave server:
|
1 |
mysql -u root -p |
In the MariaDB shell, execute:
|
1 2 3 4 5 6 |
CHANGE MASTER TO MASTER_HOST='master_server_ip', MASTER_USER='replica_user', MASTER_PASSWORD='replica_password', MASTER_LOG_FILE='mysql-bin.000001', # Use the File value from the Master status MASTER_LOG_POS= 12345; # Use the Position value from the Master status |
- Replace
master_server_ipwith the IP address of the Master server. - Replace
replica_userandreplica_passwordwith the replication user credentials created on the Master. - Set
MASTER_LOG_FILEandMASTER_LOG_POSbased on the values recorded from the Master.
- Create the database that need to replicate as Slave:
- your_database_1
- your_database_2
- Start the Slave replication process:
|
1 |
START SLAVE; |
- Verify the Slave status:
|
1 |
SHOW SLAVE STATUS\G |
Look for the following indicators in the output:
Slave_IO_State: Waiting for master to send eventSlave_IO_Running: YesSlave_SQL_Running: YesIf bothSlave_IO_RunningandSlave_SQL_RunningshowYes, the replication is working correctly.
Step 3: Test Replication
- On the Master server, create a test table or insert data into the
your_databasedatabase:
|
1 2 3 |
USE your_database_1; CREATE TABLE replication_test (id INT PRIMARY KEY, message VARCHAR(255)); INSERT INTO replication_test VALUES (1, 'Replication Test'); |
- On the Slave server, verify if the data appears:
|
1 2 |
USE your_database_1; SELECT * FROM replication_test; |
If the table and data are present, your replication is successfully set up.
Step 4: Enable Automatic Start on Boot
- Ensure MariaDB starts automatically on both servers:
|
1 |
sudo systemctl enable mariadb |
Done, MariaDB Master-Slave replication setup is now complete!