MySQL full backup and recovery

Importance of Backup:

• In the production environment, our database may encounter a variety of accidents resulting in data loss, probably divided into the following several.
• Hardware failure
• Software failure
• Natural disasters
• xxxxxx
• Error operation (maximum ratio)
  Backup type:
  Physical and logical angles:
  Physical Backup---Refers to a backup of the physical files of the database operating system
  Logical backup---Refers to a backup of the logical components of a database
  Backup Policy angle:
  Full backup---Back up the entire data set
  Differential backup---Files that have been modified since the last full backup
  Incremental backup---data that has changed since the last backup (incremental or complete)

Physical Backup Walkthroughs
1. Installing the XZ compression format tool

Yum Install-y XZ

2, backup (that is, the database folder/usr/local/mysql/data packaging)

Tar jcvf/opt/mysql-$ (date +%f). tar.xz/usr/local/mysql/data/

3, delete, restore operation (Restore the backup to the database folder can be)

Tar jxvf/opt/mysql-2018-08-30.tar.xz/usr/local/mysql/data/

Logical Backup Walkthrough
The preparatory work is as follows:
(a) First to add a database of non-login operations, after which the demonstration will be displayed in this way;

Mysql-uroot-pabc123-e ' show databases; ' #单条信息输入

Mysql-uroot-pabc123-e ' show databases;use school;show tables; ' #多条信息输入, note the order

(b) The following is the school database created before backup and restore, and the next operations are for the school database

First, a full backup of the database
Method 1: Only the tables are created, recorded, and data must be created in advance of recovery

Backup: mysqldump-uroot-pabc123 School >/opt/school.sql

Recovery: mysql-uroot-pabc123 School </opt/school.sql

Method 2: Add--databases restore without building a library

Backup: mysqldump-uroot-pabc123--databases School >/opt/school.sql

Recovery: mysql-uroot-pabc123 </opt/school1.sql

Method 3:source Statement---must be executed in the MySQL environment

Backup: mysqldump-uroot-pabc123--databases School >/opt/school.sql

Recovery: Mysql>source/opt/school.sql

Second, a full backup of the tables in the database

Backup---mysqldump-uroot-pabc123 school info >/opt/info.sql
Recovery---mysql-uroot-pabc123 School </opt/sinfo.sql

Third, a full backup of the table structure

mysqldump-uroot-pabc123-d School Info >/opt/school.sql #增加-d parameter

Iv. full backup of all libraries

mysqldump-uroot-pabc123--all-databases >/opt/databases.sql

MySQL full backup and recovery