.::: Backup & Restore MariaDB MySQL using remote from another Server/ Client :::.

1. check from destination (sever teguhth-lab02 database teguhth02_source )

mysql -uroot -p
select @@hostname,@@version;
show databases like '%teguhth02%';
select * from teguhth02_source.barang;
select * from teguhth02_destination.barang;

log

[root@teguhth-lab02 ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.6.12-MariaDB MariaDB Server

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)]> select @@hostname,@@version;
+---------------+-----------------+
| @@hostname    | @@version       |
+---------------+-----------------+
| teguhth-lab02 | 10.6.12-MariaDB |
+---------------+-----------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show databases like '%teguhth02%';
+------------------------+
| Database (%teguhth02%) |
+------------------------+
| teguhth02_destination  |
| teguhth02_source       |
+------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> select * from teguhth02_source.barang;
+-------------+-------------+---------------+-------------+
| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG |
+-------------+-------------+---------------+-------------+
| ELK-01      | RICE COOKER | BUAH          |          20 |
| ELK-02      | LEMARI ES   | UNIT          |           8 |
| ELK-03      | TELEVISI    | UNIT          |          30 |
| ELK-04      | RADIO/TAPE  | BUAH          |          35 |
| ELK-05      | KOMPUTER    | UNIT          |          28 |
| ELK-06      | KIPAS ANGIN | BUAH          |          38 |
+-------------+-------------+---------------+-------------+
6 rows in set (0.000 sec)

MariaDB [(none)]> select * from teguhth02_destination.barang;
ERROR 1146 (42S02): Table 'teguhth02_destination.barang' doesn't exist
MariaDB [(none)]>
 

2. backup from server 1
mysql -hteguhth-lab02 -uroot -proot -e "select @@hostname,@@version;"
mysqldump -hteguhth-lab02 -uroot -proot teguhth02_source > teguhth02_source.sql

[root@teguhth script]# ls
backup_full.sh  backup_incremental.sh  backup_transaction.sh  remote_backup_full.sh  teguhth.sql  test.sh
[root@teguhth script]#
[root@teguhth script]# mysql -hteguhth-lab02 -uroot -proot -e "select @@hostname,@@version;"
+---------------+-----------------+
| @@hostname    | @@version       |
+---------------+-----------------+
| teguhth-lab02 | 10.6.12-MariaDB |
+---------------+-----------------+
[root@teguhth script]#
[root@teguhth script]# mysqldump -hteguhth-lab02 -uroot -proot teguhth02_source > teguhth02_source.sql
[root@teguhth script]# ls
backup_full.sh  backup_incremental.sh  backup_transaction.sh  remote_backup_full.sh  teguhth02_source.sql  teguhth.sql  test.sh
[root@teguhth script]#
[root@teguhth script]# ls -l teguhth02_source.sql
-rw-r--r-- 1 root root 8015 Jul 27 14:43 teguhth02_source.sql
[root@teguhth script]#
 

3. Restore to server 2 (sever teguhth-lab02 database teguhth02_destination )

mysql -hteguhth-lab02 -uroot -proot teguhth02_destination < teguhth02_source.sql

[root@teguhth script]# mysql -hteguhth-lab02 -uroot -proot teguhth02_destination < teguhth02_source.sql
[root@teguhth script]#

4. check secondary
[root@teguhth-lab02 ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 24
Server version: 10.6.12-MariaDB MariaDB Server

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)]> select @@hostname,@@version;
+---------------+-----------------+
| @@hostname    | @@version       |
+---------------+-----------------+
| teguhth-lab02 | 10.6.12-MariaDB |
+---------------+-----------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show databases like '%teguhth02%';
+------------------------+
| Database (%teguhth02%) |
+------------------------+
| teguhth02_destination  |
| teguhth02_source       |
+------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> select * from teguhth02_source.barang;
+-------------+-------------+---------------+-------------+
| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG |
+-------------+-------------+---------------+-------------+
| ELK-01      | RICE COOKER | BUAH          |          20 |
| ELK-02      | LEMARI ES   | UNIT          |           8 |
| ELK-03      | TELEVISI    | UNIT          |          30 |
| ELK-04      | RADIO/TAPE  | BUAH          |          35 |
| ELK-05      | KOMPUTER    | UNIT          |          28 |
| ELK-06      | KIPAS ANGIN | BUAH          |          38 |
+-------------+-------------+---------------+-------------+
6 rows in set (0.000 sec)

MariaDB [(none)]> select * from teguhth02_destination.barang;
+-------------+-------------+---------------+-------------+
| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG |
+-------------+-------------+---------------+-------------+
| ELK-01      | RICE COOKER | BUAH          |          20 |
| ELK-02      | LEMARI ES   | UNIT          |           8 |
| ELK-03      | TELEVISI    | UNIT          |          30 |
| ELK-04      | RADIO/TAPE  | BUAH          |          35 |
| ELK-05      | KOMPUTER    | UNIT          |          28 |
| ELK-06      | KIPAS ANGIN | BUAH          |          38 |
+-------------+-------------+---------------+-------------+
6 rows in set (0.000 sec)

MariaDB [(none)]>