Remote database replication
Tags: linux, database
Published: 2023-02-20 04:53:21

## 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](https://www.heidisql.com/)

---

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

```bash
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

```bash
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

```bash
firewall-cmd --permanent --add-service=mysql
success
firewall-cmd --reload
success
```
We can confirm 

```bash
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

```bash
mysql -u root -p mysql

MariaDB [mysql]> 
```

Now inside the DBMS

```bash
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

```bash
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

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

We can confirm the privileges

```bash
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,

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

## Setting up HeidiSQL

We need a new session inside HeidiSQL


![Session](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q4iwrioezt9gd2b257zg.png)


> 💡 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

![Connected](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u4e326tj7twj1rmba1h8.png)

We can also utilize different query options available within the tool


![queries](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/imuupujpxhonk8u6nca3.png)


---

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

```bash
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

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

We can also check the details inside the `.sql` file

```bash
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 

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

We can restore that using

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

Checking for the 'test' database

```bash
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

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

### Specific DB Backup

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

### Specific table Backup

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

### Restore

```bash
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.

![Master-Slave](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y7vrr2j1nko524zba5ie.png)

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

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

![Master-Slave Exp](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o1lp8h5zizpnobwb19qa.png)

> 💡 I am using 2 MariaDB servers for this hands on 

First, from the first server,

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

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

```bash
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

```bash
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

```bash
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

```bash
systemctl restart mariadb
```

We can confirm the master database status

```bash
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

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

Backing up the database


```bash
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

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

After saving the file, we need to restart the mariadb

```bash
systemctl restart mariadb
```

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

```bash
scp 192.168.1.128:/root/all_Backup.sql ./
```

Restoring the database data from the backup file

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

We can connect to the DBMS

```bash
mysql -u root -p
```

Inside the DBMS, we need to enter the following command

```bash
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

```bash
systemctl restart mariadb
```

👉 On both Servers

We will want confirm the master and slave event list

> From the Master
```bash
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

```bash
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

```bash
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

```bash
mysql -u waji -p TestDB
```

Creating a test table 

```bash
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

```bash
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

```bash
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