MariaDB Master Slave Replication on VM Instances
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.