MySQL full backup and recovery

MySQL full backup and Recovery experiment Introduction

Database backups can be divided into physical and logical backups from a physical and logical perspective. From the database backup policy perspective, can be divided into full backup, differential backup, incremental backup. This experiment mainly introduces full backup, the advantage of full backup is simple and convenient backup and recovery operation, the disadvantage is that there is a lot of duplication of data, occupy a lot of backup space, backup time is long.

MySQL full backup 1, using the TAR Package folder backup

MySQL database files are saved by default in the installation directory under the Data folder, you can directly save the data folder. However, it takes up a lot of space and can be saved using tar package compression. Because the database file is very large, you can directly use the compression ratio of the large XZ format compression, so the first to install the XZ compression format tool.

[[email protected] opt]# yum install xz -y

Then, the database folder/usr/local/mysql/data/is packaged. Note that this is packaged using the Tar tool, preferably with a relative path, so switch to the/usr/local/mysql directory first and then package.

[[email protected] opt]# cd /usr/local/mysql/[[email protected] mysql]# tar Jcf /opt/mysql-$(date +%F).tar.xz data/[[email protected] opt]# lsmysql-2018-07-02.tar.xz  mysql-5.7.17  test.sql

2. Backup with Mysqldump tool

(1) Use the mysqldump command to fully back up some tables, such as a library with test in the database, with a YX table.

mysql> show tables;+----------------+| Tables_in_test |+----------------+| yx             |+----------------+1 row in set (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || test               |+--------------------+5 rows in set (0.00 sec)

Make a backup of the YX table in the test library.

[[email protected] opt]# mysqldump -u root -p test yx > /opt/yx1.sqlEnter password: [[email protected] opt]# lsmysql-2018-07-02.tar.xz  mysql-5.7.17  test.sql  yx1.sql

(2) Full backup of a single library using the mysqldump command

[[email protected] opt]# mysqldump -u root -p test > /opt/test.sqlEnter password: [[email protected] opt]# lsmysql-2018-07-02.tar.xz  mysql-5.7.17  test.sql  yx1.sql

(3) Full backup of multiple libraries using the mysqldump command

[[email protected] opt]# mysqldump -u root -p --databases test mysql > /opt/test-mysql.sqlEnter password: [[email protected] opt]# lsmysql-2018-07-02.tar.xz  mysql-5.7.17  test-mysql.sql  test.sql  yx1.sql

(4) Full backup of all databases using the mysqldump command

[[email protected] opt]# mysqldump -u root -p --all-databases > /opt/all.sqlEnter password: [[email protected] opt]# lsall.sql  mysql-2018-07-02.tar.xz  mysql-5.7.17  test-mysql.sql  test.sql  yx1.sql

(5) Backing up the table structure directly using the mysqldump command

[[email protected] opt]# mysqldump -u root -p -d test yx > /opt/desc.sqlEnter password: [[email protected] opt]# lsall.sql  desc.sql  mysql-2018-07-02.tar.xz  mysql-5.7.17  test-mysql.sql  test.sql  yx1.sql

MySQL Full recovery

You can use the source command and the MySQL command when you need to restore the library

1, the source command to restore the whole library

We have already backed up the test library, and now we have deleted the test library, and notice that when we restore the library, we have to create a library with the same name and then restore it, otherwise it will be an error.

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || test               |+--------------------+5 rows in set (0.00 sec)mysql> drop database test;Query OK, 1 row affected (0.01 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+mysql> create database test;Query OK, 1 row affected (0.00 sec)mysql> use test;Database changedmysql> show tables;Empty set (0.00 sec)mysql> use test;Database changedmysql> source /opt/test.sqlmysql> show tables;+----------------+| Tables_in_test |+----------------+| yx             |+----------------+1 row in set (0.00 sec)

The test library was restored successfully.

2. mysql command to restore the whole library

Before using the MySQL command to restore the library, you must first create an empty database that has been deleted, otherwise you will get an error, and then use the following command.

[[email protected] opt]# mysqldump -u root -p test < /opt/test.sqlmysql> show tables;+----------------+| Tables_in_test |+----------------+| yx             |

MySQL full backup and recovery