An efficient database backup and recovery scheme using write-ahead logging

Many cloud services perform periodic database backup to keep the data safe from failures such as sudden system crashes. In the database system, two techniques are widely used for data backup and recovery: A physical backup and a logical backup. The physical backup uses raw data by copying the files in the database, whereas the logical backup extracts data from the database and dumps it into separated files as a sequence of query statements. Both techniques support a full backup strategy that contains data of the entire database and incremental backup strategy that contains changed data since a previous backup. However, both strategies require additional I/O operations to perform the backup and need a long time to restore a backup. In this paper, we propose an efficient backup and recovery scheme by exploiting write-ahead logging (WAL) in database systems. In the proposed scheme, for backup, we devise a backup system to use log data generated by the existing WAL to eliminate the additional I/O operations. To restore a backup, we utilize and optimize the existing crash recovery procedure of WAL to reduce recovery time. For example, we divide the recovery range and applying the backup data for each range independently via multiple threads. We implement our scheme in MySQL, a popular database management system. The experimental result demonstrates that the proposed scheme provides instant backup while reducing recovery time compared with the existing schemes.