Introduction to MySQL backup and recovery and using mysqldump to back up MySQL Databases

Different storage engines also have different backup support. MyISAM storage engine can use the LVM snapshot function to implement hot backup. If there is no LVM, only warm backup can be implemented.

Different storage engines also have different backup support. MyISAM storage engine can use the LVM snapshot function to implement hot backup. If there is no LVM, only warm backup can be implemented.

I. Backup Type and other description: 1.1 whether the server will continue to provide service points during Backup:

Hot Backup: read/write during Backup is not affected

Warm backup: only read operations can be performed during Backup

Cold backup: Also called offline backup. All read/write operations are aborted.

Different storage engines also have different backup support. MyISAM storage engine can use the LVM snapshot function to implement hot backup. If there is no LVM, only warm backup can be implemented. InnoDB fully supports hot backup, including xtrabackup and mysqldump.

From the perspective of data security, offline backup (cold backup) is the safest and fastest. However, the offline backup service needs to be stopped, affecting the business. If the MySQL server has a master-slave server, you can use the cold backup to stop the slave server, that is, to ensure the service and data security.

1.2 differentiate by backup operation:

Physical backup: Copies data files. Features: fast

Logical backup: exports data to text files. Features: slow speed and loss of floating point precision; convenient use of text processing tools for direct processing, strong portability Ability

1.3 difference between backing up all data or backing up only some data:

Full backup: Back up all the data to be backed up

Incremental Backup: only the data changed after the last full backup or Incremental backup is backed up.

Differential backup: only backup of the last full backup depends on the changed data

Generally, the backup policy is used in combination: Full + incremental; full + difference

1.4 other instructions:

Backup is used for restoration in the event of a disaster. Therefore, in order to ensure restoration, regular Recovery tests are required. In addition, the best backup and recovery policies need to be developed based on the actual situation.

What data does MySQL Backup need to back up?

It mainly includes data, configuration files, binary logs, and transaction logs.

II. Introduction of backup tools: 2.1 MySQL backup tools:

Mysqldump: Logical backup tool, MyISAM (temperature), InnoDB (hot backup)
Mysqlhotcopy: physical backup tools and warm backup

2.2 file system tools:

Cp: Cold backup

Lv: logical volume snapshot function, which can achieve almost hot backup. The backup process is as follows:

Mysql> flush tables;
Mysql> LOCK TABLES

Create a snapshot: Release the lock and then copy the data.

2.3 Third-party tools:

Ibbackup: A commercial tool that provides fast backup and supports hot backup, but it is also very expensive.
Xtrabackup: Open-Source Tool

Iii. Back up MySQL using mysqldump: 3.1 basic syntax: Back up a single data or a specified table in a single data:

Mysqldump [OPTIONS] database [tb1] [tb2]…

Back up multiple databases:

Mysqldump [OPTIONS] -- databases [OPTIONS] DB1 [DB2 DB3. ..]

Back up all databases:

Mysqldump [OPTIONS] -- all-databases [OPTIONS]

3.2 option [OPTIONS] Description: -- all-databases,-

Export all databases.

Mysqldump-uroot-p -- all-databases

-- All-tablespaces,-Y

Export all tablespaces.

Mysqldump-uroot-p -- all-databases -- all-tablespaces

-- No-tablespaces,-y

No tablespace information is exported.

Mysqldump-uroot-p -- all-databases -- no-tablespaces

-- Add-drop-database

Add the drop DATABASE statement before each database is created.

Mysqldump-uroot-p -- all-databases -- add-drop-database

-- Add-drop-table

Add the drop TABLE statement before creating each data table. (It is enabled by default. Use the -- skip-add-drop-table cancel option)

Mysqldump-uroot-p -- all-databases (the drop statement is added by default)

Mysqldump-uroot-p -- all-databases-skip-add-drop-table (cancel the drop Statement)

For more details, please continue to read the highlights on the next page:

Related reading:

Using mysqldump in Linux to back up a MySQL database as an SQL File

Use mysqldump in Linux to regularly back up MySQL Databases

Mysqldump missing-q Parameter causes MySQL to be killed by oom

Mysqldump and LVM logical volume Snapshot

MySQL backup solution --> (using mysqldump and binlog binary logs)