DEV Community

Cover image for Remote database replication
Waji
Waji

Posted on

Remote database replication

#linux #database

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Adding the mysql service in the firewall

firewall-cmd --permanent --add-service=mysql
success
firewall-cmd --reload
success
Enter fullscreen mode Exit fullscreen mode

We can confirm

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

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]>
Enter fullscreen mode Exit fullscreen mode

Now inside the DBMS

MariaDB [mysql]> create user remote_user@'%' identified by 'waji';
Enter fullscreen mode Exit fullscreen mode

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        |
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

To finalize the above settings,

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

Setting up HeidiSQL

We need a new session inside HeidiSQL

Session

💡 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

We can also utilize different query options available within the tool

queries

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]
Enter fullscreen mode Exit fullscreen mode

Implementing the mysqldump command

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

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
Enter fullscreen mode Exit fullscreen mode

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

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

We can restore that using

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

Checking for the 'test' database

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

Physical Backup

There are several ways to physically backup the database

Full Backup 

mysqldump -u root -p --all-databases > all_Backup.sql
Enter fullscreen mode Exit fullscreen mode

Specific DB Backup 

mysqldump -u root -p --databases Test > Test_Backup.sql
Enter fullscreen mode Exit fullscreen mode

Specific table Backup 

mysqldump -u root -p Test test1 test2 test3 > TB_Backup.sql
Enter fullscreen mode Exit fullscreen mode

Restore 

mysql -u root -p < all_Backup.sql
Enter fullscreen mode Exit fullscreen mode

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

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

💡 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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Restarting mariadb

systemctl restart mariadb
Enter fullscreen mode Exit fullscreen mode

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 |              |                  |
+------------------+----------+--------------+------------------+
Enter fullscreen mode Exit fullscreen mode

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

firewall-cmd --permanent --add-service=mysql
success
firewall-cmd --reload
success
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

👉 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'
Enter fullscreen mode Exit fullscreen mode

After saving the file, we need to restart the mariadb

systemctl restart mariadb
Enter fullscreen mode Exit fullscreen mode

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

scp 192.168.1.128:/root/all_Backup.sql ./
Enter fullscreen mode Exit fullscreen mode

Restoring the database data from the backup file

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

We can connect to the DBMS

mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Finally, restarting the mariadb service

systemctl restart mariadb
Enter fullscreen mode Exit fullscreen mode

👉 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
.
.
.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)

Read next

codingashish_dev profile image

Step-by-Step Guide to Installing PostgreSQL on Arch Linux

Ashish Prajapati -

giulianaolmos profile image

Technical Interview Questions - Part 4 - Git + SQL vs noSQL

Giuliana Olmos -

drupaladmin profile image

Simple Ways to Identify the MySQL Port

Roman Agabekov -

afiqiqmal profile image

Preventing Duplicate Requests in Laravel: The AtomicLockMiddleware

Hafiq Iqmal -