Synthetic differential backups creation for a database using binary log conversion

• US 8,041,679 B1
• Filed: 06/04/2008
• Issued: 10/18/2011
• Est. Priority Date: 06/04/2008
• Status: Active Grant

• Current Assignee: Veritas Technologies, LLC (Whitehouse Group Ltd.)
• Original Assignee: Symantec Operating Corporation (NortonLifeLock Inc.)
• Inventors: Narayanan, Priyesh
• Primary Examiner(s): Wong; Leslie
• Application Number:
  US12/133,286
• Time in Patent Office: 1,231 Days
• Field of Search: 707/604, 707641-648, 707/678, 707/999.202, 707/999.204, 711/162
• US Class Current: 707/645
• CPC Class Codes: G06F 11/1446 Point-in-time backing up or restoration of persistent data
• CPC Class Codes: G06F 11/1458 Management of the backup or restore process
• CPC Class Codes: G06F 11/1469 Backup restoration techniques
• CPC Class Codes: G06F 11/1471 involving logging of persistent data for recovery
• CPC Class Codes: G06F 16/20 of structured data, e.g. relational data
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 2201/80 Database-specific techniques

Abstract

Creating a synthetic database backup using binary logs. A full backup of a database may be stored, followed by one or more incremental backups. The one or more incremental backups may include first binary log files. A first synthetic differential backup may be created using the first binary log files. Creating the first synthetic differential backup may include translating the first binary log files into a first plurality of SQL statements. The first plurality of SQL statements may be executable to recreate the changes in the database since the full backup. Translating each of the first binary log files into the first plurality of SQL statements may be performed before performing a recovery process to decrease recovery time.

Claims

1. A method for creating a synthetic database backup, comprising:

storing a full backup of a database at a first time;

performing a first plurality of incremental backups after the first time, wherein the first plurality of incremental backups comprise a first plurality of binary log files;

creating a first synthetic differential backup using the first plurality of binary log files, wherein said creating the first synthetic differential backup comprises translating the first plurality of binary log files into a first plurality of SQL statements, wherein the first plurality of SQL statements are executable to recreate changes in the database since the first time, wherein the first synthetic differential backup represents the changes to the database reflected by the first plurality of incremental backups;

wherein said translating the first plurality of binary log files into the first plurality of SQL statements is performed before performing a recovery process of the database.

2. The method of claim 1, further comprising:

performing a second one or more incremental backups after said performing the first plurality of incremental backups, wherein the second one or more incremental backups comprise a second one or more binary log files;

creating a second plurality of SQL statements from the second one or more binary log files, wherein the second plurality of SQL statements are executable to recreate the changes since the first plurality of incremental backups; and

appending the second plurality of SQL statements to the first synthetic differential backup to create an updated synthetic differential backup, wherein the SQL statements of the updated synthetic differential backup are executable to recreate the changes in the database since the first time.

3. The method of claim 1, wherein said performing the first plurality of incremental backups is performed by a database server and a backup server, and wherein said creating the first synthetic differential backup is performed by the backup server.

4. The method of claim 1, wherein the database comprises a MySQL database.

5. The method of claim 1, wherein the backup of the database is a logical backup of the database, wherein the logical backup of the database comprises a plurality of SQL statements, wherein the SQL statements are executable to recreate the database during a restore process.

6. The method of claim 1, wherein said performing the first plurality of incremental backups comprises copying the first binary log files and deleting the first plurality of binary log files from the database.

7. The method of claim 1, further comprising:

performing recovery of the database, wherein said performing recovery comprises;

restoring the full backup of the database; and

executing the first plurality of SQL statements.

8. The method of claim 1, further comprising:

creating a full synthetic backup which incorporates the full backup of the database and the first synthetic differential backup, wherein the full synthetic backup comprises a second plurality of SQL statements that are executable to recreate the database including the changes since the first time.

9. A non-transitory computer accessible memory medium storing program instructions for creating a synthetic database backup, wherein the program instructions are executable to:

store a full backup of a database at a first time;

perform a first plurality of incremental backups after the first time, wherein the first plurality of incremental backups comprise a first plurality of binary log files;

create a first synthetic differential backup using the first plurality of binary log files, wherein said creating the first synthetic differential backup comprises translating the first plurality of binary log files into a first plurality of SQL statements, wherein the first plurality of SQL statements are executable to recreate changes in the database since the first time, wherein the first synthetic differential backup represents the changes to the database reflected by the first plurality of incremental backups;

wherein said translating the plurality of first binary log files into the first plurality of SQL statements is performed before performing a recovery process of the database.

10. The non-transitory computer accessible memory medium of claim 9, wherein the program instructions are further executable to:

perform a second one or more incremental backups after said performing the first plurality of incremental backups, wherein the second one or more incremental backups comprise a second one or more binary log files;

create a second plurality of SQL statements from the second one or more binary log files, wherein the second plurality of SQL statements are executable to recreate the changes since the first plurality of incremental backups; and

append the second plurality of SQL statements to the first synthetic differential backup to create an updated synthetic differential backup, wherein the SQL statements of the updated synthetic differential backup are executable to recreate the changes in the database since the first time.

11. The non-transitory computer accessible memory medium of claim 9, wherein the database comprises a MySQL database.

12. The non-transitory computer accessible memory medium of claim 9, wherein the backup of the database is a logical backup of the database, wherein the logical backup of the database comprises a plurality of SQL statements, wherein the SQL statements are executable to recreate the database during a restore process.

13. The non-transitory computer accessible memory medium of claim 9, wherein said performing the first plurality of incremental backups comprises copying the first plurality of binary log files and deleting the first plurality of binary log files from the database.

14. The non-transitory computer accessible memory medium of claim 9, wherein the program instructions are further executable to:

perform recovery of the database, wherein said performing recovery comprises;

restoring the full backup of the database; and

executing the first plurality of SQL statements.

15. The non-transitory computer accessible memory medium of claim 9, wherein the program instructions are further executable to:

create a full synthetic backup which incorporates the full backup of the database and the first synthetic differential backup, wherein the full synthetic backup comprises a second plurality of SQL statements that are executable to recreate the database including the changes since the first time.

16. A server, comprising:

a processor; and

a memory medium coupled to the processor, wherein the memory medium stores program instructions for creating a synthetic database backup of a database, wherein the memory medium also stores a full backup of the database at a first time, and wherein the program instructions are executable to;

perform a first plurality of incremental backups after the first time, wherein the first plurality of incremental backups comprise a first plurality of binary log files;

create a first synthetic differential backup using the first plurality of binary log files, wherein said creating the first synthetic differential backup comprises translating the first plurality of binary log files into a first plurality of SQL statements, wherein the first plurality of SQL statements are executable to recreate the changes in the database since the first time, wherein the first synthetic differential backup represents the changes to the database reflected by the first plurality of incremental backups;

wherein said translating the first plurality of binary log files into the first plurality of SQL statements is performed before performing a recovery process of the database.

17. The server of claim 16, wherein the program instructions are further executable to:

perform a second one or more incremental backups after said performing the first plurality of incremental backups, wherein the second one or more incremental backups comprise a second one or more binary log files;

create a second plurality of SQL statements from the second one or more binary log files, wherein the second plurality of SQL statements are executable to recreate the changes since the first plurality of incremental backups; and

append the second plurality of SQL statements to the first synthetic differential backup to create an updated synthetic differential backup, wherein the SQL statements of the updated synthetic differential backup are executable to recreate the changes in the database since the first time.

18. The server of claim 16, wherein the backup of the database is a logical backup of the database, wherein the logical backup of the database comprises a plurality of SQL statements, wherein the SQL statements are executable to recreate the database during a restore process.

19. The server of claim 16, wherein the program instructions are further executable to:

perform recovery of the database, wherein said performing recovery comprises;

restoring the full backup of the database; and

executing the first plurality of SQL statements.

20. The server of claim 16, wherein the program instructions are further executable to:

create a full synthetic backup which incorporates the full backup of the database and the first synthetic differential backup, wherein the full synthetic backup comprises a second plurality of SQL statements that are executable to recreate the database including the changes since the first time.