MySQL Backup and Recovery
MySQL Backup:
I will show you here how to take backup of a database and backup of all database. In that example I will take backup of transactiondb database and restore it.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| transactiondb |
| mysql |
| performance_schema |3
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use transactiondb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_transactiondb |
+-------------------+
| retry_queue |
| retry_queue_entry |
+-------------------+
2 rows in set (0.00 sec)
Take backup of transactiondb.
[root@testdb1 ~]# mysqldump -u root -p transactiondb > /tmp/transactiondb_backup.sql
Enter password:
Encrypt the backup file
[root@testdb1 ~]# openssl enc -aes-256-cbc -in /tmp/transactiondb_backup.sql -out /tmp/transactiondb_backup.file
enter aes-256-cbc encryption password:
Verifying - enter aes-256-cbc encryption password:
[root@testdb1 ~]# ls -lrt /tmp/transactiondb_backup.file
-rw-r--r-- 1 root root 8752 Jul 17 00:01 /tmp/transactiondb_backup.file
Now keep the encrypted backup file and delete the unencrypted backup file
[root@testdb1 ~]# rm -f /tmp/transactiondb_backup.sql
Let’s drop the database so we can restore it.
[root@testdb1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.7.18 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop database transactiondb;
Query OK, 2 rows affected (0.01 sec)
Restore Database:
[root@testdb1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.7.18 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database transactiondb;
Query OK, 1 row affected (0.02 sec)
Decrypt the backup file.
[root@testdb1 ~]# openssl enc -aes-256-cbc -d -in /tmp/transactiondb_backup.file >/tmp/transactiondb_backup.sql
enter aes-256-cbc decryption password:
Restore the database.
[root@testdb1 ~]# mysql -u root -p transactiondb< /tmp/transactiondb_backup.sql
Enter password:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| transactiondb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use transactiondb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_transactiondb |
+-------------------+
| retry_queue |
| retry_queue_entry |
+-------------------+
2 rows in set (0.00 sec)
Backup all database:
[root@testdb1 ~]# mysqldump -u root -p --all-databases> /tmp/alldb_backup.sql
Enter password:
[root@testdb1 ~]# ls -lrt /tmp/alldb_backup.sql
-rw-r--r-- 1 root root 786249 Jul 17 01:03 /tmp/alldb_backup.sql