In this guide, I’ll show you how to configure MariaDB Master-Slave replication on Ubuntu and Debian server. MariaDB is a community-developed fork of the MySQL relational database management system, that has a huge community behind its development, security, and improvements.
The MariaDB replication process allows you to maintain multiple
copies of MySQL data. All data in the master is synced to Slave servers
in an automated process and if you have a disaster, you can easily
promote Slave to a Master for commit operations. The main role of
replication is to spread read and write workloads across multiple
servers for easy scalability.
We have other tutorials which cover the installation and configuration of single node MariaDB server on both Ubuntu and Debian.
Step 1: Install MariaDB on Ubuntu / Debian
I have two nodes which will be used for setting up MariaDB
Master-Slave replication. The first node will act as Master node, while
the second being a Slave.
Node 1: 192.168.18.40
Node 2: 192.168.18.41Install MariaDB on Ubuntu / Debian
Use the commands below to have MariaDB server installed on Ubuntu / Debian server. Add the repository using the commands below.
curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s --Then install required packages:
sudo apt update
sudo apt install mariadb-server mariadb-client
Set root password when prompted
$ sudo mariadb-secure-installation
Switch to unix_socket authentication [Y/n] n
Change the root password? [Y/n] y
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] yTest to confirm password is required to access the database console.
$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.11.3-MariaDB-1:10.11.3+maria~ubu2204 mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Step 2: Configure MariaDB Master Server
Once MariaDB is installed on both servers, login to Node 1 (Master node) via ssh and change Listening address to the actual IP address of the server. Edit the file /etc/mysql/my.cnf and add the following line under mysqld section.
#bind-address = 127.0.0.1
bind-address = 192.168.18.40Set the server ID which will be a unique identifier of the master server.
server-id = 100Create a database replication user
$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 50
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant replication slave on *.* to mysql_replica@'%' identified by 'StrongPassword';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> exit
Bye
Restart the MariaDB server for changes to take effect.
sudo systemctl restart mysqlCheck status using ss or netstat command.
# ss -tunelp | grep 3306
tcp LISTEN 0 70 192.168.18.40:3306 0.0.0.0:* users:(("mysqld",pid=16877,fd=22)) uid:111 ino:48116 sk:4 <->If you have a firewall running, open port 3306
sudo ufw allow 3306Step 3: Configure MariaDB Slave Server
Login to the slave server/servers and configure MariaDB:
sudo vim /etc/mysql/my.cnf
Set below values under [mysqld] section.
[mysqld]
bind-address = 192.168.18.41
server-id = 101
log_bin = /var/log/mysql/mariadb-bin
read_only = 1
report-host = mariadb-slave1
expire-logs-days = 7read_only = 1: This sets the slave to read-only
mode. Only users with the SUPER privilege and the replication slave
thread will be able to modify data on it. This ensures there are no
applications that can accidentally modify data on the slave instead of
master.
server-id = 101: This is a Unique server identification number. It will default to 1 if “master-host” is not set.
log_bin = /var/log/mysql/mariadb-bin: This enables
binary logging. This is required for acting as a MASTER in a replication
configuration. You also need the binary log if you need the ability to
do point in time recovery from your latest backup.
Restart mariadb after the change.
sudo systemctl restart mysqlStep 4: Initialize Replication process
We should be ready to start Replication process on the slave server. Start by checking Status on the master:
MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
File: mariadb-bin.000003
Position: 344
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.000 sec)Take a note of current Master log file and position. Then configure
Slave server with details obtained from the master status command.
Login to MariaDB Slave server as root user and configure connection to the Master server
$ mysql -u root -p
CHANGE MASTER TO MASTER_HOST='192.168.18.40',
MASTER_USER='mysql_replica',
MASTER_PASSWORD='StrongPassword',
MASTER_LOG_FILE='mariadb-bin.000003',
MASTER_LOG_POS=344;Then start replication on the slave:
mysql> start slave;
Query OK, 0 rows affected (0.002 sec)To check slave status, use:
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.18.40
Master_User: mysql_replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000003
Read_Master_Log_Pos: 344
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: mariadb-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 344
Relay_Log_Space: 867
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.001 sec)
Slave IO and SQL should indicate running state:
Slave_IO_Running: Yes
Slave_SQL_Running: YesCheck of process list on the master should also display connections from slave servers.
MariaDB [(none)]> select ID,user,host,db,command,time,state from information_schema.processlist order by time desc limit 5;
+----+---------------+---------------------+------+-------------+------+------------------------------------------------------------------+
| ID | user | host | db | command | time | state |
+----+---------------+---------------------+------+-------------+------+------------------------------------------------------------------+
| 38 | mysql_replica | 192.168.18.41:51522 | NULL | Binlog Dump | 988 | Master has sent all binlog to slave; waiting for binlog to be up |
| 2 | system user | | NULL | Daemon | 0 | InnoDB purge worker |
| 5 | system user | | NULL | Daemon | 0 | InnoDB shutdown handler |
| 1 | system user | | NULL | Daemon | 0 | InnoDB purge coordinator |
| 4 | system user | | NULL | Daemon | 0 | InnoDB purge worker |
+----+---------------+---------------------+------+-------------+------+------------------------------------------------------------------+
5 rows in set (0.000 sec)If you’re a MySQL user, check Configure MySQL 8.0 Master Slave Replication on Ubuntu
Database books to read:
