Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to back up and restore data in the use of MySQL, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

In the process of using MySQL, we can not avoid data backup and recovery, database migration and other operations, according to different situations, we can choose different schemes. Remember the GitLab deletion event before? With so many powerful engineers trying to turn the tide, the final loss of 6 hours of data is already the smallest. What a painful lesson is also a classic case for operation and maintenance engineers, database administrators and other friends, so we should not only make a reasonable data backup plan, but also ensure the recoverability of the backup data, and the whole process should be properly rehearsed. Avoid unexpected situations in the backup mechanism.

This paper mainly introduces the principles and characteristics of different backup methods, such as physical backup, logical backup, online backup, offline backup and so on. Compare with each other, analyze and summarize, and better choose the backup method and plan that suits you.

Physical backup

The so-called physical backup is the form in which the original data is presented on the operating system, and the way it is backed up is purely an I / O backup scheme. in the form of backup, the data files, configuration files, log files and other related files that make up the database are copied to other paths or other hosts. The common methods are (cp, rsync, etc.). In general, you need to stop the mysql service or lock the table, and the recovery is relatively fast.

Applicable scenarios: large, important scenarios that require quick recovery in the event of a failure

Characteristics of physical backups:

The backup data includes the complete database directory and data files. For MySQL, it refers to the data directory of MySQL.

Physical backups are generally faster, because they are basically Imax O replication, and there is no data conversion.

The granularity of backup is the entire database service level, some storage engines can sometimes eat the updated table granularity, backup granularity also determines the recovery granularity, so it will also have an impact on the backup strategy.

Backup data can contain associated files, such as log files, configuration files, etc.

For objects such as MEMORY engine tables, because their data is not saved on disk, but in memory, it is not backed up, only the relevant table structure can be backed up. However, you can use tool backups, such as the MySQL Enterprise Backup (Enterprise Product) tool, to back up data in memory tables.

Backups can be easily restored to machines with similar configurations.

For physical backup operations, it is generally carried out in a state that is not started. If the MYSQL service has been started, the relevant system objects can only be properly locked to ensure that the data in the database will not be changed, otherwise the backed up data may not be complete, and the data may be lost after recovery.

How to perform a physical backup

Operating system layer: cp rsync scp tar

Third-party tools: mysqlhotcopy for mysqlbackup MySQL Enterprise Backup MyISAM engine

Logical backup

The so-called logical backup is to convert the database definition language and all data into SQL files for storage. Generally, there are mysqldump and other methods, which are more suitable for libraries and tables with a small amount of data. When the amount of data reaches a certain amount, the backup and recovery time will be long, and the table will be locked in the backup process, which will have a greater impact.

Applicable scenarios: scenarios that are small enough for recovery on other CVMs

Logical backup features:

Backup set is to obtain the structure and content information of the database based on querying MySQL services.

Backup is slower than physical backup

The data backup file is larger than the physical backup, because it is stored in plain text, and the data is not compressed. It is generally compressed by means of gzip or tar.

The granularity of backup and recovery is flexible, either at the service level (all databases), some tables in a library, or even part of the data in a table

The backup set does not include log and configuration files or other database-related log files, which need to be backed up manually

Backup sets are versatile, not only for MySQL databases, but also for recovery of backup files on other relational database platforms.

MySQL's original logical backup tools include mysqldump and even select into outfile. These tools can support all storage engines and even Memory, which is stored in memory.

When restoring, the backed up SQL files can be imported with the mysql command or loaded with the source command after logging in to mysql. You can import some delimited files using load data infile or mysqlimport.

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.

Read the above about how to back up and restore data in the use of MySQL, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.