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

Comments