MariaDB Master Slave Replication on VM Instances

6-minute read
MariaDB
MariaDB • Master Slave • Google Cloud

Introduction

One of the main features of MariaDB is the Master Slave Replication. As explained in documentation:

Replication is a feature allowing the contents of one or more servers (called masters) to be mirrored on one or more servers (called slaves).

We are going to create a basic two-node Master Slave Replication using Virtual Machine Instances on Google Cloud Platform.

Create MariaDB Instances

Using our customized MariaDB VM Instance Image and Template, create two instances.

Create the Master instance patomariadb from the Cloud Console:
On Google Cloud Platform - Compute Engine - VM Instances - Create Instance - New VM Instance from template

  • Select template: patomariadb-template
  • Name: patomariadb
  • Networking
    • Hostname: patomariadb.databases

and to test the command option, create the Slave instance patomariarp using the Cloud Shell:

$ gcloud compute instances create patomariarp --hostname patomariarp.databases --source-instance-template patomariadb-template

Created [https://www.googleapis.com/compute/v1/projects/databases-20202/zones/us-central1-f/instances/patomariarp].

Validate both instances are up and running.

$ gcloud compute instances list
NAME         ZONE           MACHINE_TYPE  PREEMPTIBLE  INTERNAL_IP  EXTERNAL_IP    STATUS
patomariadb  us-central1-f  f1-micro                   10.128.0.40  34.69.159.12   RUNNING
patomariarp  us-central1-f  f1-micro                   10.128.0.41  35.192.48.122  RUNNING

Master Slave Replication

Configure Replication on Master

Access via ssh to your Master instance and generate the configuration file, defining the unique server_id and the name and format of the binary log:

pato@patomariadb:~$ sudo nano /etc/mysql/conf.d/replication.cnf

# MariaDB Replication Configuration
[mariadb]
server_id=1
log_bin=master-bin
binlog-format=mixed
log_slave_updates=on

Connect to the database and create a user for the replication

pato@patomariadb:~$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.3.22-MariaDB-0ubuntu0.19.10.1 Ubuntu 19.10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replicador'@'%' IDENTIFIED BY '********';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

Finally restart the database for the new configuration to be loaded:

pato@patomariadb:~$ sudo systemctl restart mariadb

Configure Replication on Slave

Access via ssh to your Slave instance and generate the configuration file, defining the unique server_id and the name and format of the binary log:

pato@patomariarp:~$ sudo nano /etc/mysql/conf.d/replication.cnf

# MariaDB Replication Configuration
[mariadb]
server_id=11
log_bin=slave-bin
binlog-format=mixed
log_slave_updates=on

Finally restart the database for the new configuration to be loaded:

pato@patomariarp:~$ sudo systemctl restart mariadb

Generate test data and objects

Connect to Master database and generate a table and insert a row:

pato@patomariadb:~$ mysql

MariaDB [(none)]> create database taller ;
Query OK, 1 row affected (0.132 sec)

MariaDB [(none)]> use taller;
Database changed
MariaDB [taller]> create table r1 (i1 int auto_increment primary key, c1 varchar(20), d1 datetime) ;
Query OK, 0 rows affected (0.142 sec)

MariaDB [taller]> insert into r1 (c1,d1) values ('Before Master Slave',now());
Query OK, 1 row affected (0.012 sec)

MariaDB [taller]> commit ;
Query OK, 0 rows affected (0.000 sec)

MariaDB [taller]> select * from r1;
+----+---------------------+---------------------+
| i1 | c1                  | d1                  |
+----+---------------------+---------------------+
|  1 | Before Master Slave | 2020-05-04 18:15:49 |
+----+---------------------+---------------------+
1 row in set (0.000 sec)

Initial Data Copy

Freeze any activity in Master database:

pato@patomariadb:~$ mysql

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.048 sec)

Then backup the Master database:

pato@patomariadb:~$ mysqldump --all-databases > masterfull.sql
pato@patomariadb:~$

Get the binary log coordinates for the starting point of replication:

MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      898 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

Unfreeze activity in Master:

MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.000 sec)

Transfer Backup from the Master node to the Slave node:

pato@patomariadb:~$ gcloud compute scp masterfull.sql patomariarp:/home/pato
masterfull.sql                                100%  469KB  45.9MB/s   00:00

Restore the backup in the Slave:

pato@patomariarp:~$ mysql < masterfull.sql
pato@patomariarp:~$

Start Replication

Configure Slave database with the binlog position master-bin.000001 - 898 from the initial copy and then modify the replication to use GTID position:

pato@patomariarp:~$ mysql

MariaDB [(none)]> CHANGE MASTER TO
    ->    MASTER_HOST='patomariadb',
    ->    MASTER_USER='replicador',
    ->    MASTER_PASSWORD='********',
    ->    MASTER_PORT=3306,
    ->    MASTER_LOG_FILE='master-bin.000001',
    ->    MASTER_LOG_POS=898,
    ->    MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.040 sec)

MariaDB [(none)]> CHANGE MASTER TO MASTER_USE_GTID = slave_pos ;
Query OK, 0 rows affected (0.011 sec)

Start the Slave and validate the status:

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.012 sec)

MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: patomariadb
                   Master_User: replicador
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: master-bin.000001
           Read_Master_Log_Pos: 898
                Relay_Log_File: mysqld-relay-bin.000002
                 Relay_Log_Pos: 1198
         Relay_Master_Log_File: master-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
...
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 0-1-3
...
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
...
1 row in set (0.000 sec)

OK our Master Slave replication is set and running!

Test Master Slave Replication

In order to validate that the replication is working we are going to create one more table and data:

pato@patomariadb:~$ mysql

MariaDB [(none)]> use taller
Database changed

MariaDB [taller]> create table r2 (i2 int auto_increment primary key, c2 varchar(20), d2 datetime) ;
Query OK, 0 rows affected (0.095 sec)

MariaDB [taller]> insert into r2 (c2,d2) values ('After Master Slave',now());
Query OK, 1 row affected (0.004 sec)

MariaDB [taller]> commit;
Query OK, 0 rows affected (0.000 sec)

MariaDB [taller]> select * from r2;
+----+--------------------+---------------------+
| i2 | c2                 | d2                  |
+----+--------------------+---------------------+
|  1 | After Master Slave | 2020-05-04 18:40:46 |
+----+--------------------+---------------------+
1 row in set (0.000 sec)

Verify that the data and objects were replicated from the Master to the Slave, the ones from the initial copy table r1 - Before Master Slave and from the replication table r2 - After Master Slave:

pato@patomariarp:~$ mysql

MariaDB [(none)]> use taller
Database changed

MariaDB [taller]> select * from r1;
+----+---------------------+---------------------+
| i1 | c1                  | d1                  |
+----+---------------------+---------------------+
|  1 | Before Master Slave | 2020-05-04 18:15:49 |
+----+---------------------+---------------------+
1 row in set (0.001 sec)

MariaDB [taller]> select * from r2;
+----+--------------------+---------------------+
| i2 | c2                 | d2                  |
+----+--------------------+---------------------+
|  1 | After Master Slave | 2020-05-04 18:40:46 |
+----+--------------------+---------------------+
1 row in set (0.001 sec)

OK the data and table were replicated from the Master to the Slave!

Conclusion

We successfully generated and configured a Master Slave Replication cluster on Virtual Machine Instances from Google Cloud and validated it’s working.
As you can see the basic setup is quite straightforward. After this you can try more functionalities like filtering tables, failover and switchover, and multiple slaves.