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
Top comments (0)