MySQL full backup and recovery

the importance of database backup:
In a production environment, the security of data is critical, and any loss of data can have serious consequences. The importance of database backup is mainly reflected in:

1. Improve the system's high availability and disaster recoverability, when the database system crashes, no database backup can find data.
2. Restoring a database using a database backup is the best solution for minimizing the cost of data recovery when a database crashes, and it is too expensive to let users re-add data.
3. Without data There is nothing, database backup is a powerful means of disaster prevention.

There are several reasons for data loss during the use of the database:

Program error, human error, computer failure, disk failure, disaster (earthquake fire, etc.) and theft

Classification of database backups:




Example:

Create Database School;
CREATE TABLE info (ID int,name char (10));
Insert into info (id,name) VALUES (1, ' ZS ');
----------------------Full Backup----------------------------------------------------
Insert into info (id,name) VALUES (2, ' ls ');
------------------------Incremental Backup----------------------------------------------
Insert into info (id,name) VALUES (3, ' ww ');
-------------------------incremental and differential backups----------------------------------
Insert into info (id,name) VALUES (4, ' Zl ');
---------------------incremental and differential backups------------------------------

MySQL Full backup concept interpretation
MySQL's backup method mainly includes full backup and incremental backup. A full backup is a backup of the entire database, a database structure, and a file structure, which is the basis for incremental backups when the database is completed at backup time.
The advantage of a full backup is that backup and recovery operations are simple and easy, with the drawback that there is a lot of duplication of data, a large amount of backup space, and a long backup time.
In a production environment, both of these backup methods are used, and a reasonably efficient solution is needed to achieve the purpose of backing up data and to avoid serious consequences of data loss.
About backup here are two ways of
1. Use tar to package a folder backup : MySQL database files are stored in the installation directory by default in the Data folder, you can directly save the data folder, but the space is large, you can use the TAR packaging compression to save. Here are some related operations commands:

[[email protected] mysql]# Yum install xz-y//install XZ compression Format tool
[[Email protected] mysql]# Date +%f//specified compressed file date format
[Email protected] mysql]# tar jcvf/opt/mysql-$ (date +%f). Tar.xz/usr/local/mysql/data//Compress the database folder and package it under/OPT
[[email protected] ~]# du-sh/opt/mysql-2018-08-30.tar.xz//du View the amount of memory consumed after packaging
[[email protected] ~]# du-sh/usr/local/mysql/data//du View the amount of memory used by the original packaging folder
[[Email protected] ~]# tar jxvf/opt/mysql-2018-08-30.tar.xz/usr/local/mysql/data///restore the packaged backup file to the database folder

Specific operations such as:

2. Use the Mysqldump tool to back up : Use mysqldump to more flexibly control the contents of a backup, such as a few tables or libraries can be backed up separately. Here are some related operations commands:

(1) Backup table data:
Mysqldump-u User name-p password option database name table name >/backup path/backup file name
Mysqldump-u Root-p School Info >/opt/info.sql
(2) Single Library backup:
Mysqldump-u User name-p password option database name >/backup path/backup file name
Mysqldump-uroot-p School >/opt/school.sql #只会写库里所有表的创建和数据记录
(3) Multiple library backups:
Mysqldump-u User name-p password option--databases library name 1 Library Name 2 >/backup path/backup file name
Mysqldump-uroot-p--databases School KGC >/opt/school_kgc.sql
(4) Full backup of all libraries:
Mysqldump-u Username-p password option--all-databases >/backup path/backup file name
Mysqldump-uroot-p--all-databases >/opt/all.sql
(5) Backup table structure:
Mysqldump-u User name-p password-d database name table name >/backup path/backup file name
Mysqldump-u Root-p-D School Info >/opt/info-d.sql

MySQL data is fully restored
When you need to restore the database, you can use the source command and the MySQL command

(1) Use the source command to restore the backed-up library school to MySQL:
Mysqldump-u Root-p School >/opt/school.sql

(2) using the MySQL command to complete the library data, also in the library Chool in advance has been backed up in the case of recovery:
Mysqldump-u Root-p School >/opt/school.sql

(3) Mysqldump-u-root-p--databases School >/opt/school01.sql #连同库school本身和里面的表都会备份, Direct recovery can be:

Recovery table:

(1) Use the source command to restore the table and restore the data table if it has been backed up:
Mysqldump-u Root-p School Info >/opt/info.sql #备份表

(2) using MySQL to restore the data table, but also the data table has been backed up under the premise of recovery operations:
Mysqldump-u Root-p School Info >/opt/info.sql #备份表
Mysql-u User name-p password Library name </library Backup textbook path

This article supplements

The file after the backup is a SQL script with a fixed format at the end of SQL

MySQL full backup and recovery