MySQL Backup Type

For DBAs, the most basic task is to back up and restore the database. In case of exceptions (such as server downtime or disk damage), the data department should be lost, or, the loss is minimized.

MySQL database provides many tools (such as mysqldump, ibbackup, and replication) that can be used to complete backup, and can also be completed using third-party tools, such as xtrabackup and LVM snapshot backup. DBAs should design a backup policy that minimizes losses and has minimal impact on the database according to their business requirements.

 

Backup and recovery:

Backup method:Hot Backup (Hot Standby)Cold backup(Cold backup)Warm backup(Warm backup)

Hot Backup: Direct backup during database operation, which has no impact on the running database. (Online backup) official manual for online backup

Cold backup: Offline backup refers to offline backup when the database is stopped.

Warm backup: Backup is also performed when the database is running, but it will affect the operation of the current database. For example, you can add a global read lock to ensure the consistency of the backup data.

 

Follow the backup file content:

Logical backup--> The content of the backup file is readable, usually text files. The content is generally an SQL statement or actual data in the table, such as mysqldump and select * into OUTFILE, it is generally applicable to database upgrades and migration, and recovery takes a long time

 

Raw file backup--> Copy the physical files of the database. The database can be in the running state (ibbackup, xtrabackup, or other tools), or in the stopped state. The recovery time is short.

 

The backup database can be divided:

Full backup: Complete database backup

Incremental Backup: Back up the updated data based on the last full backup (xtrbackup)

Log backup: Binary log backup, master-slave Replication

Excerpt from the author's book "MySQL technology insider-InnoDB Engine"