Remote database replication

Remote Access ✨

Remote accessing a database allows us to access it from a different location, often over the internet or a private network. This can be useful for accessing the database from different locations, enabling collaboration, backup and disaster recovery, and monitoring and maintenance of the database. It provides flexibility, efficiency, and business continuity.

👉 I am using HeidiSQL tool today. You can find more details regarding this tool over here

Setting up the DB server

👉 3306 TCP is the default port. But we can change the port number from the settings file /etc/my.cnf

Checking current databases;

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| userdb             |
+--------------------+
5 rows in set (0.00 sec)

Adding a table into the test database

MariaDB [test]> create table test_table (name varchar(50));
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_table     |
+----------------+
1 row in set (0.00 sec)

Adding the mysql service in the firewall

firewall-cmd --permanent --add-service=mysql
success
firewall-cmd --reload
success

We can confirm

netstat -antp | grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      1366/mysqld

The mysql process is on "LISTEN" status

Now we have to login into the MariaDB monitor to set remote access account

mysql -u root -p mysql

MariaDB [mysql]>

Now inside the DBMS

MariaDB [mysql]> create user remote_user@'%' identified by 'waji';

This will allow the remote user to be able to access the database from anywhere

Confirming the user creation

MariaDB [mysql]> select host, user from user;
+-----------+-------------+
| host      | user        |
+-----------+-------------+
| %         | remote_user |
| 127.0.0.1 | root        |
| ::1       | root        |

So now we want the remote user to have access to the test database

MariaDB [mysql]> grant all privileges on test.* to remote_user@'%';
Query OK, 0 rows affected (0.00 sec)

We can confirm the privileges

MariaDB [mysql]> show grants for remote_user;
+------------------------------------------------------------------------------------------------------------+
| Grants for remote_user@%                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'remote_user'@'%' IDENTIFIED BY PASSWORD '*455821E7AB13BA1D48FAC4F8CAAF95262F30CFBB' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'remote_user'@'%'                                                      |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

To finalize the above settings,

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

Setting up HeidiSQL

We need a new session inside HeidiSQL

💡 In the advanced settings, we can also activate SSL/TLS for advanced security. Just a note that activating that will surely encrypt the data however it will make the write, read and other functions slower

Once we are done with the setup, we can connect to see

We can also utilize different query options available within the tool

Backup ✨

A database backup is a copy of a database used to restore data if the original database is lost or corrupted

There are two types of database backups:

Physical backup
Logical backup

Physical backup is a byte-level copy of the database files and objects, while logical backup is a logical representation of the database in the form of SQL statements

Physical backups are quicker and used for larger databases, while logical backups are slower and used for smaller databases or specific database objects

Logical Backup

We can utilize the command

mysqldump --help
mysqldump  Ver 10.14 Distrib 5.5.68-MariaDB, for Linux (x86_64)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

Implementing the mysqldump command

mysqldump -u root -p --all-databases > 202230220_all_Backup.sql
Enter password:

We can also check the details inside the .sql file

vi 202230220_all_Backup.sql

-- MySQL dump 10.14  Distrib 5.5.68-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version       5.5.68-MariaDB

Now, if we drop the 'test' database from our MariaDB

MariaDB [mysql]> drop database test;
Query OK, 1 row affected (0.00 sec)

We can restore that using

mysql -u root -p < 202230220_all_Backup.sql 
Enter password:

Checking for the 'test' database

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| userdb             |
+--------------------+

Physical Backup

There are several ways to physically backup the database

Full Backup

mysqldump -u root -p --all-databases > all_Backup.sql

Specific DB Backup

mysqldump -u root -p --databases Test > Test_Backup.sql

Specific table Backup

mysqldump -u root -p Test test1 test2 test3 > TB_Backup.sql

Restore

mysql -u root -p < all_Backup.sql

Replication ✨

Database replication is the process of creating and maintaining one or more copies of a database, keeping them synchronized with the original database

Replication improves data availability, scalability, and reliability by distributing database access across multiple servers or locations

Replication can be set up in various ways, such as master-slave, master-master, or multi-master replication, depending on the specific use case and requirements

Master and Slave DBMS

👉 Master server => The master server is the primary server that handles all data modifications and updates to the database. Whenever a change is made to the master database, it is recorded in a transaction log.

👉 Slave server => The slave server is a secondary server that receives updates from the master server and applies them to its local copy of the database. The slave server cannot make any modifications to the database, and it is read-only.

The replication process in master-slave replication typically works as follows:

A change is made to the master database.
The change is recorded in a transaction log on the master server.
The slave server reads the transaction log from the master server and applies the changes to its local copy of the database.
The slave server periodically polls the master server for new updates and applies them to its local copy of the database.

💡 I am using 2 MariaDB servers for this hands on

First, from the first server,

MariaDB [mysql]> create database TestDB;
Query OK, 1 row affected (0.00 sec)

Also creating and granting permission for the new 'waji' user

MariaDB [mysql]> grant all privileges on TestDB.* to waji@'%' identified by 'waji';
Query OK, 0 rows affected (0.00 sec)

Now, we will create a slave user that we will use for replication db

MariaDB [mysql]> grant replication slave on *.* to rep_user@'%' identified by 'waji';
Query OK, 0 rows affected (0.00 sec)

We now have to add some lines in the /etc/my.cnf config file

vi /etc/my.cnf

character-set-server=utf8
log-bin=mysql-bin
server-id=1

💡 The server-id part defines that this will be master server

Restarting mariadb

systemctl restart mariadb

We can confirm the master database status

mysql -u root -p -e "show master status"
Enter password: 
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      245 |              |                  |
+------------------+----------+--------------+------------------+

Finally, we will add the mysql service in our firewall settings

firewall-cmd --permanent --add-service=mysql
success
firewall-cmd --reload
success

Backing up the database

mysqldump -u root -p --all-databases > all_Backup.sql

ls -ld all_Backup.sql 
-rw-r--r-- 1 root root 514857  2월 20 13:12 all_Backup.sql

👉 Moving to the 2nd DB Server (Slave Server)

We will add these lines to the /etc/my.cnf file

[mysqld]
character-set-server=utf8
server-id=2
replicate-do-db='TestDB'

After saving the file, we need to restart the mariadb

systemctl restart mariadb

Also, copying the backup file that we created from the master to the slave server

scp 192.168.1.128:/root/all_Backup.sql ./

Restoring the database data from the backup file

mysql -u root -p < all_Backup.sql 
Enter password:

We can connect to the DBMS

mysql -u root -p

Inside the DBMS, we need to enter the following command

MariaDB [(none)]> change master to master_host='192.168.1.128', master_user='rep_user', master_password='waji', master_log_file='mysql-bin.000001', master_log_pos=245;

Finally, restarting the mariadb service

systemctl restart mariadb

👉 On both Servers

We will want confirm the master and slave event list

From the Master

mysql -u root -p -e "show processlist\G"
Enter password: 
*************************** 1. row ***************************
      Id: 4
    User: rep_user
    Host: 192.168.1.129:42556
      db: NULL
 Command: Binlog Dump
    Time: 111
   State: Master has sent all binlog to slave; waiting for binlog to be updated
    Info: NULL
Progress: 0.000
*************************** 2. row ***************************
      Id: 5
    User: root
    Host: localhost
      db: NULL
 Command: Query
    Time: 0
   State: NULL
    Info: show processlist
Progress: 0.000

From Slave

mysql -u root -p -e "show processlist\G"
Enter password: 
*************************** 1. row ***************************
      Id: 1
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 223
   State: Slave has read all relay log; waiting for the slave I/O thread to update it
    Info: NULL
Progress: 0.000
*************************** 2. row ***************************
      Id: 2
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 223
   State: Waiting for master to send event
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 5
    User: root
    Host: localhost
      db: NULL
 Command: Query
    Time: 0
   State: NULL
    Info: show processlist
Progress: 0.000

We can also use the following to see the slave status

mysql -u root -p -e "show slave status\G"
Enter password: 
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.128
                  Master_User: rep_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 245
.
.
.

Now, let's actually update from the Master server

👉 From the Master Server

Accessing the 'TestDB' as the 'waji' user

mysql -u waji -p TestDB

Creating a test table

MariaDB [TestDB]> create table TestTB(ID int, Name varchar(20));
Query OK, 0 rows affected (0.00 sec)

MariaDB [TestDB]> desc TestTB;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | int(11)     | YES  |     | NULL    |       |
| Name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Inserting a test data into the table

MariaDB [TestDB]> insert into TestTB value (100, "Test_1");
Query OK, 1 row affected (0.00 sec)

MariaDB [TestDB]> select * from TestTB;
+------+--------+
| ID   | Name   |
+------+--------+
|  100 | Test_1 |
+------+--------+
1 row in set (0.00 sec)

We can confirm the same entries from our Slave server

👉 From the Slave Server

mysql -u waji -p TestDB

MariaDB [TestDB]> desc TestTB;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | int(11)     | YES  |     | NULL    |       |
| Name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [TestDB]> select * from TestTB;
+------+--------+
| ID   | Name   |
+------+--------+
|  100 | Test_1 |
+------+--------+
1 row in set (0.00 sec)

This concludes setting up the master to slave server replication

💡 We can also set up master to master by just setting up slave settings over the current master server and setting up a master server over the current slave server

Blog