The backup and recovery functions of MySQL databases will enter a new era

Because MySQL Databases usually store the core data of applications, data backup and recovery functions are essential. Most Mature database management systems provide high-performance online

Because MySQL Databases usually store the core data of applications, data backup and recovery functions are essential. Most Mature database management systems provide high-performance online

Because MySQL Databases usually store the core data of applications, data backup and recovery functions are essential, most Mature database management systems provide high-performance online data backup and Point-in-time recovery. However, as the most popular open-source database product, MySQL has weak functions in this regard, even though it is far from enough compared with another well-known open-source database PostgreSQL. For a long time, MySQL only provides one mysqldump tool for data backup. This is a completely independent peripheral tool from the Database Core. Simply put, it executes the "SELECT *" Statement on the table to be backed up, selects each record, and stores it in the INSERT statement or CSV format. This method is easy to implement but obviously has poor performance, especially when data needs to be imported during recovery. If there is a large amount of data, it will be a very slow process, but a fault occurs, the system will not be able to provide services for a long time. However, this phenomenon will be completely changed after MySQL 6.0 is released at the end of this year (I guess it will jump.

MySQL 6.0 plans to introduce new online backup and recovery features, which will be provided by the Native Driver provided by various storage engines. Each storage engine can provide the data backup function in the most convenient and efficient form, the premise is that the backup data should correspond to the consistent state of the database at a certain time point (that is, the backup includes and only contains all committed transactions at that time (for non-transaction storage engines, all completed operations) modified data. There are two options for this time point: the backup start time or the backup end time. One backup may involve multiple storage engines (this is also the difficulty for MySQL to implement the online backup function. Currently, the poor MySQL backup function is estimated to be related to the features of MySQL that support multiple storage engines ), however, as long as each storage engine can provide consistent data corresponding to the backup at the beginning or end, MySQL can ensure the global consistency of the backup data. This is easy to achieve. MySQL will require the storage engines that provide consistent data at the backup end point to back up data first. After these storage engines are backed up, the database write operation will be stopped for a short time, then, start the backup process of the storage engine that provides consistent backup start point data.

In this way, the upper layer of MySQL only packs the data backed up by each storage engine (and also provides the backup data compression and encryption functions). The most important thing is the implementation of the local drive of each storage engine. Since the backup and recovery functions are provided by the local driver, theoretically it can provide much better backup and recovery performance than mysqldump, and achieve online backup. Taking MyISAM storage engine as an example, if you want to make a consistent backup for a MyISAM table and lock the table, you cannot perform write operations during the backup, affecting system services. In the future MySQL 6.0, according to the developer's assumption, the local driver of the MyISAM storage engine will back up data as follows: after the backup is started, the logging function will be enabled, record the data modification operations on the backup table during the backup process, and then copy the data. MYD and. MYI file, and finally copy the logs during the backup. In this way, the backup will be composed of the. MYD/MYI file and the update logs during the backup. When the backup is restored, the data will be restored and the logs will be played back to restore the data to the status at the end of the backup. In addition, because these logs are physical logs provided by the MyISAM local driver and are not logical binlogs, the recovery performance will be greatly improved.