Prerequisites

Before you begin, ensure that you have the following:

• Two Debian 11 servers with MariaDB installed.
• Root or sudo access to both servers.
• Firewall rules that allow communication on port 3306 (MariaDB’s default port).
• A basic understanding of MariaDB commands.

Step 1: Update Your System

On both servers, start by updating the system packages:

sudo apt update && sudo apt upgrade -y

Keeping your system updated is important, especially if you are deploying it on a VPS Windows Servers platform.

Step 2: Install MariaDB on Both Servers

If MariaDB is not installed, install it on both servers by running:

sudo apt install mariadb-server -y

After installation, start and enable MariaDB on both servers:

sudo systemctl start mariadb
sudo systemctl enable mariadb

Step 3: Configure MariaDB on Server 1 (Master 1)

Log in to the MariaDB shell on the first server:

sudo mysql -u root -p

Create a replication user and grant replication privileges:

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

Exit the MariaDB shell, and then edit the MariaDB configuration file:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add or modify the following lines under the `[mysqld]` section:

[mysqld]
server-id=1
log_bin=/var/log/mysql/mariadb-bin
binlog_do_db=your_database_name
bind-address=0.0.0.0

Save and exit the file. Restart MariaDB:

sudo systemctl restart mariadb

Step 4: Configure MariaDB on Server 2 (Master 2)

Log in to the MariaDB shell on the second server:

sudo mysql -u root -p

Create the same replication user on this server:

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

Exit the MariaDB shell, and then edit the configuration file on the second server:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Modify the configuration to reflect the second server:

[mysqld]
server-id=2
log_bin=/var/log/mysql/mariadb-bin
binlog_do_db=your_database_name
bind-address=0.0.0.0

Save and exit the file. Restart MariaDB on the second server:

sudo systemctl restart mariadb

Step 5: Set Up Master-Master Replication

On the first server, find the current log position:

sudo mysql -u root -p -e 'SHOW MASTER STATUS\G'

Note the values of File and Position. You will need these when configuring the second server.

Now, on the second server, configure the replication with the following command:

CHANGE MASTER TO
MASTER_HOST='IP_of_first_server',
MASTER_USER='repl_user',
MASTER_PASSWORD='strong_password',
MASTER_LOG_FILE='File_from_first_server',
MASTER_LOG_POS=Position_from_first_server;

Start the replication:

START SLAVE;

Repeat the same steps on the first server, using the details from the second server.

CHANGE MASTER TO
MASTER_HOST='IP_of_second_server',
MASTER_USER='repl_user',
MASTER_PASSWORD='strong_password',
MASTER_LOG_FILE='File_from_second_server',
MASTER_LOG_POS=Position_from_second_server;

Start the replication on the first server:

START SLAVE;

Step 6: Verify Replication

To check the replication status on both servers, run:

SHOW SLAVE STATUS\G;

Ensure that Slave_IO_Running and Slave_SQL_Running are both set to "Yes".