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 Backup) 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 is used 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, add a global read lock to ensure the consistency of Backup data.

Follow the backup file content:

Logical backup --> indicates that the content of the backup file is readable and usually a text file. The content is generally an SQL statement or the actual data in the table, for example, the mysqldump and SELECT * into outfile methods are 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 status (ibbackup, xtrabackup, or other tools), or in the stopped status, with a short recovery time.

The backup database can be divided:

Full backup: Full backup of the database

Incremental Backup: backs 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"