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

To set up MariaDB master slave replication, you first need to install MariaDB on both your main (master) and secondary (slave) servers. This involves running a few commands in your Ubuntu terminal to get the necessary server and client packages installed and then making sure the service is running correctly.

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

Configuring the master server is crucial for MariaDB master slave replication because it holds the primary data. You'll need to edit a specific configuration file using a text editor to set it up correctly. This involves making changes to settings like the server ID and enabling binary logging, which is essential for replication.

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

Setting up the slave server is the next step in MariaDB master slave replication, and it involves making it aware of the master. You'll access its configuration file and make specific changes to connect it to the master server. This includes defining its own unique server ID and telling it where to find the master's data.

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]