.::: Test Result Backup from Database Encrypted & Restore Database Non Encrypted and otherwise MariaDB, MySQL :::.

A. Environment Encrypted<teguhth01> to Database Non Encrypted <teguhth02>

SELECT  NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID,@@hostname,@@version FROM information_schema.INNODB_TABLESPACES_ENCRYPTION;
 

SELECT  @@hostname,@@version;

SELECT  NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID,@@hostname,@@version FROM information_schema.INNODB_TABLESPACES_ENCRYPTION;
 

B. Backup from Database Encrypted & Restore Database Non Encrypted ->> result fail & cannot restore

1. Backup Encripted database
mysqldump -u root -p secretdb  --single-transaction --skip-lock-tables > db_teguhthencripted.sql ;
mysqldump -u root -p secretdb barang > table_barangencripted.sql ;

[root@teguhth data]# mysqldump -u root -p secretdb  --single-transaction --skip-lock-tables > db_teguhthencripted.sql ;
Enter password:
[root@teguhth data]# mysqldump -u root -p secretdb barang > table_barangencripted.sql ;
Enter password:
[root@teguhth data]# ls
db_teguhthencripted.sql  table_barangencripted.sql
[root@teguhth data]#

2. copy database/table encrypted to teguhth02
[root@teguhth data]# sftp root@teguhth02:/data
root@teguhth02's password:
Connected to teguhth02.
Changing to: /data
sftp> ls
sftp> put *
Uploading db_teguhthencripted.sql to /data/db_teguhthencripted.sql
db_teguhthencripted.sql                                                                           100% 3455     6.0MB/s   00:00
Uploading table_barangencripted.sql to /data/table_barangencripted.sql
table_barangencripted.sql                                                                         100% 2221     4.6MB/s   00:00
sftp> ls
db_teguhthencripted.sql          table_barangencripted.sql
sftp> exit
[root@teguhth data]#

[root@teguhth data]# ls
db_teguhthencripted.sql  table_barangencripted.sql
[root@teguhth data]#

3. Restore from Database Encrypted to Database Non Encrypted

mysql -u root -proot nonsecretdb < table_barangencripted.sql;
mysql -u root -proot nonsecretdb < db_teguhthencripted.sql;

[root@teguhth02 data]# mysql -u root -proot nonsecretdb < table_barangencripted.sql;
ERROR 1005 (HY000) at line 25: Can't create table `nonsecretdb`.`barang` (errno: 140 "Wrong create options")
[root@teguhth02 data]# mysql -u root -proot nonsecretdb < db_teguhthencripted.sql;
ERROR 1005 (HY000) at line 25: Can't create table `nonsecretdb`.`barang` (errno: 140 "Wrong create options")
[root@teguhth02 data]#

4. result fail & cannot restore
 

C. Backup from Database Non Encrypted & Restore Database Encrypted ->> result success & can restore
1. create database
MariaDB [secretdb]> create database nonsecretdb;
Query OK, 1 row affected (0.000 sec)

MariaDB [secretdb]>
MariaDB [secretdb]> use nonsecretdb;
Database changed
MariaDB [nonsecretdb]>
 

2. Backup Non Encripted database
mysqldump -u root -p teguhth barang > db_nonencriptedbarang.sql ;
mysqldump -u root -p teguhth  --single-transaction --skip-lock-tables > db_nonencripted.sql ;

[root@teguhth02 data]# mysqldump -u root -p teguhth barang > db_nonencriptedbarang.sql ;
Enter password:
[root@teguhth02 data]# mysqldump -u root -p teguhth  --single-transaction --skip-lock-tables > db_nonencripted.sql ;
Enter password:
[root@teguhth02 data]# ls
db_nonencriptedbarang.sql  db_nonencripted.sql  db_teguhthencripted.sql  table_barangencripted.sql
[root@teguhth02 data]#

3. copy database/table non encrypted to encrypted
[root@teguhth data]# ls
db_teguhthencripted.sql  table_barangencripted.sql
[root@teguhth data]# sftp root@teguhth02:/data
root@teguhth02's password:
Connected to teguhth02.
Changing to: /data
sftp> ls
db_nonencripted.sql              db_nonencriptedbarang.sql        db_teguhthencripted.sql          table_barangencripted.sql
sftp> get db_non*
Fetching /data/db_nonencripted.sql to db_nonencripted.sql
/data/db_nonencripted.sql                                                                         100% 8002     7.2MB/s   00:00
Fetching /data/db_nonencriptedbarang.sql to db_nonencriptedbarang.sql
/data/db_nonencriptedbarang.sql                                                                   100% 2204     2.7MB/s   00:00
sftp> exit
[root@teguhth data]# ls
db_nonencriptedbarang.sql  db_nonencripted.sql  db_teguhthencripted.sql  table_barangencripted.sql
[root@teguhth data]#

4. Restore from Database Non Encrypted to Database Encrypted
mysql -u root -p nonsecretdb < db_nonencriptedbarang.sql;
mysql -u root -p nonsecretdb < db_nonencripted.sql;

[root@teguhth data]# mysql -u root -p nonsecretdb < db_nonencriptedbarang.sql;
Enter password:
[root@teguhth data]# mysql -u root -p nonsecretdb < db_nonencripted.sql;
Enter password:
[root@teguhth data]#

5. check after restore
MariaDB [secretdb]> use nonsecretdb;
Database changed
MariaDB [nonsecretdb]> show tables;
Empty set (0.000 sec)

MariaDB [nonsecretdb]> select @@hostname,@@version;
+------------+-----------------+
| @@hostname | @@version       |
+------------+-----------------+
| teguhth    | 10.5.20-MariaDB |
+------------+-----------------+
1 row in set (0.000 sec)

MariaDB [nonsecretdb]> show tables;
+-----------------------+
| Tables_in_nonsecretdb |
+-----------------------+
| barang                |
| customer              |
| pasok                 |
| pembelian             |
| suplier               |
+-----------------------+
5 rows in set (0.000 sec)

MariaDB [nonsecretdb]> desc barang;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| KODE_BARANG   | char(6)      | NO   | PRI | NULL    |       |
| NAMA_BARANG   | varchar(25)  | YES  |     | NULL    |       |
| SATUAN_BARANG | varchar(20)  | YES  |     | NULL    |       |
| STOK_BARANG   | decimal(4,0) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
4 rows in set (0.001 sec)

MariaDB [nonsecretdb]>


MariaDB [nonsecretdb]> show create table nonsecretdb.barang;

| barang | CREATE TABLE `barang` (
  `KODE_BARANG` char(6) NOT NULL,
  `NAMA_BARANG` varchar(25) DEFAULT NULL,
  `SATUAN_BARANG` varchar(20) DEFAULT NULL,
  `STOK_BARANG` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`KODE_BARANG`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
 

6. other check
MariaDB [nonsecretdb]> SELECT  NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID,@@hostname,@@version FROM information_schema.INNODB_TABLESPACES_ENCRYPTION;
+----------------------------+-------------------+----------------+------------+-----------------+
| NAME                       | ENCRYPTION_SCHEME | CURRENT_KEY_ID | @@hostname | @@version       |
+----------------------------+-------------------+----------------+------------+-----------------+
| innodb_system              |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| mysql/innodb_table_stats   |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| mysql/innodb_index_stats   |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| mysql/transaction_registry |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| mysql/gtid_slave_pos       |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| teguhth/barang             |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| teguhth/suplier            |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| teguhth/customer           |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| teguhth/pasok              |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| teguhth/pembelian          |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| secretdb/barang            |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| secretdb/suplier           |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| nonsecretdb/barang         |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| nonsecretdb/customer       |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| nonsecretdb/pasok          |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| nonsecretdb/pembelian      |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
| nonsecretdb/suplier        |                 1 |              1 | teguhth    | 10.5.20-MariaDB |
+----------------------------+-------------------+----------------+------------+-----------------+
17 rows in set (0.000 sec)

MariaDB [nonsecretdb]>