MySQL easy to learn data backup and recovery

Backup of database is divided into physical backup logical backup

Physical backups are divided into cold and hot backups
Cold backup: The database needs to be shut down for backup.
Hot backup: You can back up without shutting down the database.

Logical backup
Backup of logical components of the database, libraries, tables, etc.

Backups can be divided into full backups, differential backups, incremental backups
Full backup: Backing up the entire database
Incremental backup: Adds a table or library for the last incremental or full backup operation, or for a period of time, for an incremental backup of the contents of the last backup.
Differential backup: Ignores incremental backups and only backs up all operations after the last full backup.

方法一、完全备份

1. Backup with compression tool

Yum Install Xz-y

2. Compress the database for backup

Tar jcvf/root/abc/mysql-$ (date +%f). Tar.xz/usr/local/mysql/data
Interpretation: Date +%f

3. Effect view

(Generate) Compressed files

4. Restore the database

Tar jxvf root/abc/mysql-2018-08-30.tar.xz-c/usr/local/mysql/data/

方法二、使用mysqldump工具进行备份

Experiment template using School Library

1. Backup School Library

Mysqldump-uroot-p123123 School >/root/abc/school.sql
Can look up a backup file that ends in. sql

2. Restore the database

Backing up in this form requires creating the specified database before restoring.
！ The following prompts are restored when the specified database is not created

You will be prompted not to find the database school library
！ After you create the specified database first
Create Database School;
Execute the command again
Mysqldump-uroot-p123123 School >/root/abc/school.sql;

or into the database.
Use school;
Source/root/abc/school.sql
Data can be recovered using either of these methods
Complete Database Recovery!

《备份及恢复扩展》

You need to create the specified database before you can restore the database
1. Do not create the specified database recovery data as follows:

Mysqldump-u root-p123123--databases School >/opt/school.sql
Adding--databases based on the original command does not require pre-creation of the specified database when recovering

2. Data recovery
mysql-uroot-p123123 </root/abc/school.sql

Or
Source/root/abc/school.sql
Both methods are available, eliminating the need to pre-create the database.

备份表

The Backup table method is similar to the above

Mysqldump-u Root-p School Info >/root/abc/info.sql

To restore, you only need to specify the name of the library

Mysql-u root-p123123 School </root/abc/info.sql

备份表的结构

Sometimes we just need a table structure to use, or we can back up the structure of the table.

Mysqldump-u Root-p-D School Info >/opt/infos.sql

The end~

MySQL easy to learn data backup and recovery