Mysql full backup and recovery
Introduction to Mysql full backup and recovery experiment
From the physical and logical point of view, database backup can be divided into physical backup and logical backup. From the point of view of database backup strategy, it can be divided into full backup, differential backup and incremental backup. This experiment mainly introduces full backup. The advantage of full backup is that the backup and recovery operation is simple and convenient, but the disadvantage is that there are a lot of duplicate data, occupy a lot of backup space, and take a long backup time.
Mysql full backup 1. Backup using tar packaged folders
The database files of Mysql are saved by default in the data folder under the installation directory, and you can save the data folder directly. But it takes up a lot of space and can be saved using tar packaging and compression. Because the database file is very large, you can directly use the xz format with a high compression ratio, so first install the xz compression format tool.
[root@localhost opt] # yum install xz-y
Then, package the database folder / usr/local/mysql/data/. Note that you use the tar tool to package here, and it's best to use a relative path, so change to the / usr/local/mysql directory before you package.
[root@localhost opt] # cd / usr/local/mysql/ [root@localhost mysql] # tar Jcf / opt/mysql-$ (date +% F) .tar.xz data/ [root@localhost opt] # lsmysql-2018-07-02.tar.xz mysql-5.7.17 test.sql2, backup using the mysqldump tool
(1) use the mysqldump command to fully back up some tables, such as the following figure, there is a test database in the database, and there is a yx table in it.
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)
Back up the yx table in the test library.
[root@localhost opt] # mysqldump-u root-p test yx > / opt/yx1.sqlEnter password: [root@localhost opt] # lsmysql-2018-07-02.tar.xz mysql-5.7.17 test.sql yx1.sql
(2) use mysqldump command to make a full backup of a single library
[root@localhost opt] # mysqldump-u root-p test > / opt/test.sqlEnter password: [root@localhost opt] # lsmysql-2018-07-02.tar.xz mysql-5.7.17 test.sql yx1.sql
(3) use mysqldump command to make a full backup of multiple libraries
[root@localhost opt] # mysqldump-u root-p-databases test mysql > / opt/test-mysql.sqlEnter password: [root@localhost opt] # lsmysql-2018-07-02.tar.xz mysql-5.7.17 test-mysql.sql test.sql yx1.sql
(4) use mysqldump command to make a full backup of all databases
[root@localhost opt] # mysqldump-u root-p-all-databases > / opt/all.sqlEnter password: [root@localhost opt] # lsall.sql mysql-2018-07-02.tar.xz mysql-5.7.17 test-mysql.sql test.sql yx1.sql
(5) use mysqldump command to back up the table structure directly.
[root@localhost opt] # mysqldump-u root-p-d test yx > / opt/desc.sqlEnter password: [root@localhost opt] # lsall.sql desc.sql mysql-2018-07-02.tar.xz mysql-5.7.17 test-mysql.sql test.sql yx1.sqlMysql full recovery
You can use the source command and the mysql command when you need a recovery library
1. Source command to restore the whole library
We have backed up the test library above, and now we delete the test library. When restoring the library, we must first create a library with the same name, and then restore it, otherwise an error will be reported.
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 is restored successfully.
2. Mysql command to restore the whole library.
Before using the Mysql command to restore the library, also create an empty database that has been deleted, otherwise an error will be reported, and then use the following command.
[root@localhost opt] # mysqldump-u root-p test
< /opt/test.sqlmysql>Show tables;+-+ | Tables_in_test | +-+ | yx |
