MySQL full backup and recovery

MySQL full backup and restore full backup: Backup of the entire database, database structure and file structure, the database is saved at the time of preparation, is the basis of incremental backup. A backup of the MySQL database can be done in two ways, because the database is actually a file, the database folder is packaged directly, or the mysqldump can be backed up with a dedicated backup tool. MySQL full backup 1. Use tar to package a folder backup

MySQL database files by default are saved in the installation directory of the Data folder, you can directly save the data folder, but occupy a large space, you can use the TAR packaging compression to save.

(1) The database file is large, you can use the compression ratio of the larger XZ format compression, by default, if there is no need to install the XZ compression format tool.

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

(2) The database folder/usr/local/mysql/data/to the package operation.

[[email protected] mysql]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/[[email protected] ~]# cd /opt/[[email protected] opt]# lsmysql-2018-07-02.tar.xz  rh    //备份文件//

(3) If the database file corruption data is lost, you can decompress the backup file, equivalent to do the data recovery work.

[[email protected] opt]# tar Jxvf /opt/mysql-2018-07-02.tar.xz /usr/local/mysql/data/

2 Using the Mysqldump tool to back up

The previous method of compressing the entire database directory for MySQL is to back up all the content in the database. With Mysqldup, you have more flexibility in controlling the content of your backups, such as a few tables or libraries that can be backed up separately.

(1) using the mysqldump command to back up the table info in the library school, the backed up file is/opt/info.sql

[[email protected] opt]# mysqldump -u root -p school info > /opt/info.sql[[email protected] opt]# lsinfo.sql  mysql-2018-07-02.tar.xz  

(2) using the mysqldump command to make a full backup of a single library, the backup file is/opt/school.sql.

[[email protected] opt]# mysqldump -u root -p school > /opt/school.sqlEnter password:      //root登录密码//[[email protected] opt]# lsinfo.sql  mysql-2018-07-02.tar.xz  rh  school.sql

(3) using the mysqldump command to back up multiple libraries, the backup file is/opt/school-mysql.sql.

[[email protected] opt]# mysqldump -u root -p --databases school mysql > /opt/school-mysql.sql[[email protected] opt]# lsinfo.sql  mysql-2018-07-02.tar.xz  rh  school-mysql.sql  school.sql

(4) Use the mysqldump command to make a full backup of all libraries, and the backup file is All.sql.

[[email protected] opt]# mysqldump -u root -p --all-databases > /opt/all.sql[[email protected] opt]# lsall.sql  info.sql  mysql-2018-07-02.tar.xz  rh  school-mysql.sql  school.sql

(5) using the mysqldump command can also directly back up the table structure, backup file is/opt/desc-info.sql.

[[email protected] opt]# mysqldump -u roou -p school info > /opt/desc-info.sql[[email protected] opt]# lsall.sql  desc-info.sql  info.sql  mysql-2018-07-02.tar.xz  rh  school-mysql.sql  school.sql

MySQL Full recovery

1. Restore the entire library operation
(1) First backup the library school

[[email protected] opt]# mysqldump -u root -p school > /opt/school.sql

(2) If the data is corrupted, delete the database school.

[[email protected] opt]# mysql-u root-p//Login Mysql//enter Password://Login password//welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 17Server version:5.7.17 Source distributioncopyright (c) $, Oracle and/or its Affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help.     Type ' \c ' to clear the current input statement.mysql> show databases; See all library//+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | School | |     SYS |+--------------------+5 rows in Set (0.00 sec) mysql> drop Database School; Delete School library//query OK, 0 rows Affected (0.00 sec) mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | performance_sChema | | SYS |+--------------------+4 rows in Set (0.00 sec)

(3) Do not log in to MySQL, use the MySQL command to restore the library school.

At this time the library school has been deleted, you need to create and then restore operations, or will error.

[[email protected] opt]# mysql -u root -pmysql> create database school;   //创建库//Query OK, 1 row affected (0.00 sec)mysql> quit   //退出//Bye[[email protected] opt]# mysql -u root -p school < /opt/school.sql      //恢复school库//[[email protected] opt]# mysql -u root -p  //登录mysql数据库//mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || school             || sys                |+--------------------+5 rows in set (0.00 sec)

2. Recovering a table using the source command
(1) First backup of table info

[[email protected] opt]# mysqldump -u root -p school info > /opt/info.sql

(2) If the data is corrupted, delete the table info in database school.

mysql> use school;   //进入school库//Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;           //查看表//+------------------+| Tables_in_school |+------------------+| info             |+------------------+1 row in set (0.00 sec)mysql> select * from info;   //查看数据记录//+----------+-------+| name     | score |+----------+-------+| zhangsan | 88.00 || lisi     | 70.00 |+----------+-------+2 rows in set (0.00 sec)mysql> drop table info;    //删除info表//Query OK, 0 rows affected (0.02 sec)mysql> show tables;       Empty set (0.00 sec)      //无表//

(3) Log in to MySQL and use the source command to restore the table.

[[email protected] opt]# mysql -u root -pmysql> use school;             //进入库//mysql> source /opt/info.sql   //恢复info表//Query OK, 0 rows affected (0.00 sec)mysql> show tables;+------------------+| Tables_in_school |+------------------+| info             |+------------------+1 row in set (0.00 sec)mysql> select * from info;+----------+-------+| name     | score |+----------+-------+| zhangsan | 88.00 || lisi     | 70.00 |+----------+-------+2 rows in set (0.00 sec)

(4) You can also use MySQL for recovery

[[email protected] opt]mysqldump -u root -p school info > /opt/school-info.sql  //备份//[[email protected] opt]mysql -u root -p school < /opt/school-info.sql //恢复//

MySQL full backup and recovery