How to Setup MariaDB Master Slave Replication on Ubuntu

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

MariaDB installation on Ubuntu requires updating your package list and installing 💿 the server and client packages on both your master and slave servers. Run the update command followed by the package installation command in your terminal to get MariaDB ready for replication.

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

MariaDB master server configuration requires editing the main configuration file to assign a unique server ID and turn on binary logging. Open the configuration file in a text editor to set up these mandatory parameters before starting the replication process.

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

MariaDB slave server setup involves editing its configuration file to add a unique server ID and point the server to your master database. Open the configuration file on your secondary server to define these connection settings and establish the replication link.

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]