How to Setup MariaDB Master Slave Replication on Ubuntu

Setting up a master and slave database system helps keep your data safe. It also helps your website handle more traffic. In this setup, the master server handles all the main data. The slave server automatically copies any changes made on the master. This process is called replication.

Why do this?

You do this to create a backup system. If your main server fails, your data is already safe on the slave server. It also helps you scale your database for better performance.

What happens when done?

Once finished, any information you add to the master server will instantly appear on the slave server without you needing to do anything manually.

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

You must install MariaDB on both servers. Note: This step requires admin privileges.

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

The master server is the source of truth. Open the configuration file to make changes. Note: This step requires admin privileges.

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

Log in to your slave server and open its configuration file. Note: This step requires admin privileges.

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 have successfully configured MariaDB replication. By following these steps, you have linked a slave server to a master server. Any data written to the master will now be automatically copied to the slave, ensuring your data is protected against failure. For further reading, check out %%LNK0%% and %%LNK1%%.

[Y/n]

[Y/n]

[Y/n]

[Y/n]

[Y/n]

[mysqld]

[mysqld]