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 |