How to Setup MariaDB Master Slave Replication on Ubuntu
MariaDB master-slave replication on Ubuntu synchronizes data between two MariaDB database servers, effectively creating a real-time backup and improving performance.
In this setup, one server acts as the master, capturing all data changes. Slave servers then automatically mirror these changes from the master’s binary logs, ensuring data consistency across your database infrastructure.
You’ll typically implement this on Ubuntu 20.04 LTS or later by configuring your primary MariaDB instance as the master. Then, you’ll set up secondary instances as slaves to continuously receive and apply events from the master’s binary log.
Setup MariaDB master-slave replication by configuring the master server’s `/etc/mysql/mariadb.conf.d/50-server.cnf` to enable binary logging and setting a `server_id`. Then, configure the slave server’s `50-server.cnf` with its own `server_id` and restart both services.
Server Preparation
You need two servers for this. One will be the master and the other will be the slave.
Primary = 192.168.1.1
Slave = 192.168.1.2
Install MariaDB
Setting up MariaDB master slave replication starts with installing MariaDB on both your servers.
sudo apt update
sudo apt-get install mariadb-server mariadb-client
After the installation, use these commands to make sure the service is running correctly.
For Ubuntu 16.04:
sudo systemctl stop mysql.service
sudo systemctl start mysql.service
sudo systemctl enable mysql.service
For Ubuntu 18.04:
sudo systemctl stop mariadb.service
sudo systemctl start mariadb.service
sudo systemctl enable mariadb.service
Next, secure your database by running this command. Note: This step requires admin privileges.
sudo mysql_secure_installation
Follow the prompts to set a root password and remove test settings. To check if it works, log in with this command:
sudo mysql -u root -p
Configure the Master Server
Your master server holds the main data, so the first step in MariaDB master slave replication is configuring its settings.
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Update the file with these settings:
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
log-bin
bind-address = 192.168.1.1
server_id = 1
log-basename = master1
Restart the server to apply the changes:
sudo systemctl restart mariadb.service
sudo systemctl restart mysql.service
Now, log in to create a user for replication. Note: This step requires admin privileges.
sudo mysql -u root -p
Create the user account:
CREATE USER 'replication_user'@'192.168.1.2' IDENTIFIED BY 'new_password_here';
Grant permission to the user:
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'192.168.1.2';
View the master details:
SHOW MASTER STATUS;
You will see output similar to this:
MariaDB [(none)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.000001 | 315 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
Make sure to write down the File and Position numbers. You will need them for the next part.
Setup the Slave Server
To set up the slave server for MariaDB master slave replication, you’ll log in and change its configuration file to link with the master.
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Update the file with these settings:
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
log-bin
bind-address = 192.168.1.2
server_id = 2
Restart the slave server:
sudo systemctl restart mariadb.service
sudo systemctl restart mysql.service
Log in to the slave server:
sudo mysql -u root -p
Stop the slave process:
STOP SLAVE;
Link the slave to the master using the details you wrote down earlier:
CHANGE MASTER TO
MASTER_HOST='192.168.1.1',
MASTER_USER='replication_user',
MASTER_PASSWORD='type_replication_user_password',
MASTER_LOG_FILE='master1-bin.000001',
MASTER_LOG_POS=315;
Start the slave process:
START SLAVE;
Test the connection with these commands:
SHOW SLAVE STATUS G
You should see confirmation like this:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Summary
You’ve now set up MariaDB master slave replication, linking a slave server to your master so data copies over automatically.
[Y/n]
[Y/n]
[Y/n]
[Y/n]
[Y/n]
[mysqld]
[mysqld]
Was this guide helpful?
About the Author
Richard
Tech Writer, IT Professional
Richard, a writer for Geek Rewind, is a tech enthusiast who loves breaking down complex IT topics into simple, easy-to-understand ideas. With years of hands-on experience in system administration and enterprise IT operations, he’s developed a knack for offering practical tips and solutions. Richard aims to make technology more accessible and actionable. He's deeply committed to the Geek Rewind community, always ready to answer questions and engage in discussions.
No comments yet — be the first to share your thoughts!