How to configure MariaDB replication between two Ubuntu 24.04 servers

Create a primary database server and a replica server on Ubuntu 24.04 running MariaDB 11.4. Configure the primary to log transactions and the replica to read from the primary's binary log. Ensure both servers have network connectivity and that the MariaDB service is running on both nodes.

Prerequisites

• Two Ubuntu 24.04 LTS servers with network connectivity.
• MariaDB 11.4 installed on both servers.
• Root or sudo access on both machines.
• A static IP address configured on both servers.
• Firewall rules allowing traffic on ports 3306 and 3307.

Step 1: Configure the primary server binary log

Edit the main configuration file to enable binary logging and set the server ID. This allows the database to record all changes for the replica to read.

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

Add the following lines inside the [mysqld] section:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
gtid_mode = ON
enforce_gtid_consistency = ON

Restart the MariaDB service to apply these settings.

sudo systemctl restart mariadb

Step 2: Create a replication user on the primary

Create a dedicated user for the replica to connect to the primary. Grant the REPLICATION SLAVE privilege and ensure the user can connect from the replica's hostname or IP.

sudo mysql -u root -p

Run the following SQL commands inside the MySQL prompt:

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

Exit the MySQL prompt and verify the user was created.

exit

Step 3: Configure the replica server

On the second server, edit the configuration file to set a unique server ID and enable GTID mode. This ensures the replica knows it is not the primary and can read logs correctly.

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

Add the following lines inside the [mysqld] section:

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
gtid_mode = ON
enforce_gtid_consistency = ON
relay_log = /var/log/mysql/mysql-relay-bin.log

Restart the MariaDB service on the replica to load the new configuration.

sudo systemctl restart mariadb

Step 4: Start the replica and point it to the primary

Connect to the replica server and start the replication process. Use the SHOW MASTER STATUS command on the primary to get the current log file name and position.

sudo mysql -u root -p

Run the following SQL on the primary to get the log info:

SHOW MASTER STATUS\G

Note the File and Position values. Copy these values and run the following SQL on the replica:

CHANGE MASTER TO
  MASTER_HOST='primary_server_ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='StrongPassword123!',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=1234,
  MASTER_AUTO_POSITION=1;
START SLAVE;

Replace 'primary_server_ip' with the actual IP address of the primary server. The MASTER_AUTO_POSITION=1 flag allows the replica to use GTID for automatic position tracking.

exit

Verify the installation

Check the replica status to confirm it is running and catching up. Run this command on the replica server:

sudo mysql -u root -p -e "SHOW SLAVE STATUS\G"

You should see the following output indicating success:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Error: NULL
Seconds_Behind_Master: 0

Insert a test row on the primary and verify it appears on the replica.

sudo mysql -u root -p -h primary_server_ip -e "INSERT INTO test_table (id, name) VALUES (1, 'test');"
sudo mysql -u root -p -e "SELECT * FROM test_table;"

Troubleshooting

If replication stops, check the error log on the replica. Common issues include authentication failures or GTID conflicts.

Error: "Can't connect to MySQL server on 'primary_server_ip' (111)"

Fix: Ensure the firewall allows traffic on port 3306. Run sudo ufw allow 3306 on both servers.

Error: "Error 1045: Access denied for user 'repl_user'@'...'"

Fix: Verify the password on the primary matches the one used in the CHANGE MASTER command. Re-run the GRANT command if necessary.

Error: "Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs"

Fix: This indicates GTID mode is not enabled on the primary. Ensure gtid_mode = ON is set on both servers before starting replication.

If the replica falls behind, check the Seconds_Behind_Master value. A high value indicates the replica is slow to process events. Increase the replica's CPU or memory resources if needed.

To stop replication temporarily, run STOP SLAVE; on the replica. To reset the replica to a fresh state, run RESET SLAVE ALL; and reconfigure the master connection.