MySQL database backup and recovery

In the process of using MySQL, we avoid data backup and recovery, database migration and other operations, for different situations, can choose different scenarios. Remember before the Gitlab deleted the library event? With so many cool engineers, the last 6 hours of data loss is the least. What a bitter lesson, but also for the operations engineer, database administrator and other friends of a classic case, so we not only have to formulate a reasonable data backup plan, but also to ensure the recoverability of the backup data, the whole process should be properly rehearsed. Avoid unexpected situations with backup mechanisms.

This article mainly introduces the principle and characteristics of different backup methods, such as physical backup, logical backup, online backup, offline backup, etc. Compare with each other, analyze and summarize, better choose the way of backup and plan for yourself.

Physical backup

  So-called physical backup is the original data on the operating system, what kind of form, back up is what, this way is purely I/O type of backup scheme, backup form is composed of database data files, configuration files, log files and other related files, copied to The other path or other host . Commonly used methods are (CP, rsync, etc.), generally need to stop the MySQL service or lock table, recovery is relatively fast.
Scenario : large, critical, and fast-to-recover scenarios in the event of a failure
features of physical backups :
     Backup data includes complete database directories and data files, and for MySQL, MySQL's data directory
     Physical backups are generally faster because the basic is I/O replication and there is no data conversion. The granularity of
     Backup is the entire database service level, and some storage engines can sometimes eat the updated table granularity, which also determines the granularity of recovery, and therefore has an impact on the backup strategy.
     Backup data can contain associated files, such as log files, configuration files, etc.
     for objects like memory engine tables, because their data is not stored on disk, it is stored in memory. Therefore, it is not backed up and only the related table structure can be backed up. However, you can back up data in memory tables with tool backups, such as the MySQL Enterprise Backup (corporate products) tool.
     Backup can easily be restored to a machine with similar configuration.
     In the case of physical backup operations, generally in the non-started state, if the MySQL service has been started, it is only appropriate to lock the relevant system objects, to ensure that the database data does not change, Otherwise the backed up data may not be complete, and after recovery, there may be a loss of data

How physical backups are performed
operating system layer : CP rsync SCP tar
third-party tools : Mysqlbackup MySQL Enterprise Backup MyISAM engine dedicated Mysqlhotcopy

Logical backup

The so-called logical backup is to convert the database definition language and all the data into SQL file storage, generally have mysqldump, and so on, this way is suitable for small data volume of the library and table, when the amount of data reached a certain amount, backup, recovery time, while the backup process locks the table, the impact is relatively large.
Scenario: Small to restore on other servers
Logical Backup features :
Backup set is the structure and content information obtained from the database based on the query MySQL service
Backup slower than physical backup
The data backup file is larger than the physical backup, because it is stored in a pure text, the data is not compressed, generally using gzip or tar compression
Backup and recovery granularity is more flexible, either at the service level (all databases) or some table in a library, or even some of the data in a table
The backup set does not include logs and configuration files or other database-related log files that need to be backed up manually
Backup sets are more versatile, not just for MySQL databases, but even for restoring backup files to other relational database platforms.
MySQL's original logical Backup tool includes mysqldump, and even select into outfile can be counted. These tools can support all of the storage engines and even memory, which are stored in the engine
Upon recovery, the backup of the SQL file can be imported with the MySQL command, or it can be loaded using the source command after logging in to MySQL. For a partial bounding file, you can use load data infile or Mysqlimport to import.
In addition to physical and logical backups, some file systems support the creation of snapshots, but MySQL is implemented through third-party solutions, such as VEritas LVM or ZFS.

MySQL database backup and recovery