Setup MariaDB Master / Slave Replication on Ubuntu 16.04 | 18.04

For security and fault tolerance reasons, setting up MySQL / MariaDB master and slave replication is a way to go… In a master and slave setup, one database server serves are the primary or master and the other(s) as slave in this topology…

Changes made on the master server are replicated to the slave servers… the replication is asynchronous and automatic..

This setup is necessary in situations where you want to scale, provide live backups for disaster recovery and fault-tolerance..

Each replication slave must have a unique server ID… If you are setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from any of the other slaves.

This brief tutorial shows students and new users how to setup MySQL / MariaDB master and slave replication on Ubuntu 16.04 | 18.04 servers..To get started, follow the steps below:

Step 0: Server Preparation

For replication to work, you’ll need two or more servers… One server will serve as the primary and the others as slaves… For this tutorial, we’ll setup two servers.. (primary and slave)

Primary = 192.168.1.1
Slave = 192.168.1.2

Step 1: Install MariaDB on Both Master / Slave

To install MariaDB on Ubuntu, use the commands below… The setup also works for MySQL server.. To use MySQL instead, change the server name below to mysql-server and mysql-client

Install MariaDB on both master and slave server by running the commands below on each….

sudo apt update
sudo apt-get install mariadb-server mariadb-client

After installing MariaDB, the commands below can be used to stop, start and enable MariaDB service to always start up when the server boots..

Run these on Ubuntu 16.04 LTS

sudo systemctl stop mysql.service
sudo systemctl start mysql.service
sudo systemctl enable mysql.service

Run these on Ubuntu 18.04 LTS

sudo systemctl stop mariadb.service
sudo systemctl start mariadb.service
sudo systemctl enable mariadb.service

After that, run the commands below to secure MariaDB server by creating a root password and disallowing remote root access.

sudo mysql_secure_installation

When prompted, answer the questions below by following the guide.

Enter current password for root (enter for none): Just press the Enter
Set root password? [Y/n]: Y
New password: Enter password
Re-enter new password: Repeat password
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]: Y

Restart MariaDB server

To test if MariaDB is installed, type the commands below to logon to MariaDB server

sudo mysql -u root -p

Then type the password you created above to sign on… if successful, you should see MariaDB welcome message

Step 3: Configure MariaDB Master Server

First, we want to create our master server in this topology.. This is the primary server and all changes on this server will be replicated onto the slave(s)…

To configure the master server, open the configuration file below and make the highlighted changes… then save the file and exit.

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Change / add the highlighted line below and save..

[mysqld]

#
# * 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

After making the changes, restart MariaDB server…

sudo systemctl restart mariadb.service
sudo systemctl restart mysql.service

After configuring and restarting the master server, logon to it and create a user account that will be used for replication… These account will have a username and password and use by the slave servers….

Run the commands below to logon to MariaDB master server…

sudo mysql -u root -p

After logging on, run the commands below to create a new account for replication… this account name will be replication_user with a new password…

CREATE USER 'replication_user'@'192.168.1.2' IDENTIFIED BY 'new_password_here';

Next, run the commands below to grant the replication_user full access to the slave server..

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'192.168.1.2';

Next, run the commands below to show the master server details…

SHOW MASTER STATUS;

It should print out something similar to the content below:

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)]>

Take notes of the File and Position details of the master server… You will need these when configuring the slave server later…

Step 4: Setup the Slave Server

Now that the master server is setup and configured switch to the slave server and run open its configuration file…

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Next, change the highlighted lines in the file and save…

[mysqld]

#
# * 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…

After making the changes above on the slave server, restart MariaDB server…

sudo systemctl restart mariadb.service
sudo systemctl restart mysql.service

When you’re done, logon to the slave server…

sudo mysql -u root -p

We want to configure the slave server to communicate with the primary server.. To do that, stop the slave server by running the commands below:

STOP SLAVE;

Next, run the following commands to configure the slave to communication with the master server using the account created for the slave 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;

Remember to use the correct information from above… After running the commands above, run the commands below to start the slave…

START SLAVE;

That should complete the setup…

Test by running the SQL commands below:

SHOW SLAVE STATUS \G

You should see the lines below:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If everything is set up correctly, whatever change made on the primary master will be replicated to the slave automatically…

This is how one setup Master / Slave synchronous replication using MariaDB server.. This should also work with using MySQL servers…