How to Setup MariaDB Master and Slave Replication on Ubuntu Linux

|

|

The tutorial provides step-by-step instructions on setting up MySQL/MariaDB master and slave replication on Ubuntu 16.04 and 18.04 servers, which is crucial for security, fault tolerance, and scalability. The process involves setting server details, installing MariaDB on both servers, creating and configuring master and slave servers, and testing the setup. Changes made on the master…

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

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

This setup is necessary to scale and 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 must have a unique server-id value that differs from that of the master and any other slaves.

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

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 set up two servers. (primary and slave)

Primary = 192.168.1.1
Slave = 192.168.1.2

Install MariaDB on Both Master / Slave

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

Install MariaDB on the master and slave servers 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 stop, start, and enable the service to start 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 the 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 the 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 log into the MariaDB server.

sudo mysql -u root -p

Then, type the password you created above to sign on. If successful, you should see a MariaDB welcome message.

Configure MariaDB Master Server

First, we want to create our master server in this topology. This is the primary server, and all changes 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 the MariaDB server.

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

After configuring and restarting the master server, log on to it and create a user account that will be used for replication. This account will have a username and password and be used by the slave servers.

Run the commands below to log on to the 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.

Setup the Slave Server

Now that the master server is set up and configured switch to the please 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 the MariaDB server.

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

When you’re done, log on 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 communicate 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 changes are made on the primary master will be replicated to the slave automatically.

This is how one sets up Master / Slave synchronous replication using the MariaDB server. This should also work with using MySQL servers.

Like this:



One response to “How to Setup MariaDB Master and Slave Replication on Ubuntu Linux”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.