NetVault Plug-in for MySQL 11.2 - User Guide

Examples of restore scenarios for MySQL Standard/Community

To recover successfully from a failure or data corruption, various settings must be made when setting up the job regarding data selected for restore and options available on the Options tab. The following topics offer examples of various types of recovery and cover the specific options required.

Full Backup only restore scenarios

In the following examples, the MySQL DBA has established a backup policy in which Full Backups are performed daily at 11:00 P.M.

On Monday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 6:00 A.M. on Monday before the DBA’s arrival at work.

The DBA decides to recover up to the time right before the Drop Table command was issued. This decision means that the DBA must restore the Sunday’s Full Backup, and perform PIT Recovery on the current Binary Logs.

The DBA decides to recover up to the time right before the Drop Table command was issued. The DBA also wants to recover the transactions that occurred to the remaining tables from the time after the erroneous statement was issued, and up until the end of the current Binary Logs. This decision ensures that he has recovered as many of the transactions as feasible, in addition to recovering the dropped table.

On Monday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 6:00 A.M. on Monday before the DBA’s arrival at work.

The DBA decides to recover up to the time right before the Drop Table command was issued. Furthermore, because the DBA wants a more precise recovery than estimating the time at which the developer dropped the table, the DBA chooses to use a position-based recovery. To accomplish this process, the DBA must restore Sunday’s Full Backup and perform a PIT Recovery on the current Binary Logs.

IMPORTANT: Stop and Start positions must be actual positions listed in a Binary Log, not arbitrary numbers that are greater than the position of the unwanted transaction.

The DBA decides to recover up to the time right before the Drop Table command was issued. The DBA also wants to recover the transactions that occurred to the remaining tables from the time after the Orders table was dropped, and up until the end of the current Binary Logs. This decision ensures that he has recovered as many of the transactions as feasible, in addition to recovering the dropped table. Furthermore, the DBA wants a more precise recovery, so he decides to use a position-based recovery. To accomplish this process, the DBA must restore Sunday’s Full Backup and perform a PIT Recovery on the current Binary Logs.

IMPORTANT: Stop and Start positions must be actual positions listed in a Binary Log, not arbitrary numbers that are greater than the position of the unwanted transaction.

Full and Incremental Backup restore scenarios

The DBA has established a backup policy in which Full Backups are performed every Sunday at 11:00 P.M. and Incremental Backups are performed Monday through Saturday, at 11:00 P.M. Because the DBA is performing Incremental Backups, the Binary Logs are deleted after each Incremental Backup. This process makes the overall backup faster, but requires more time and steps when performing the restore.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors for the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped the table sometime early Thursday, before the DBA’s arrival at work.

The DBA decides to perform a complete recovery up to the point of the last Incremental Backup — the backup performed on Wednesday night.

In the following examples, a Full and Incremental Backup scenario is in place, and the DBA wants to recover data to a specific time.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 8:00 P.M. on Wednesday.

The DBA must perform a recovery that restores the database up to the time right before the developer dropped the table at 8:00 P.M. on Wednesday. Therefore, the following phases would be performed:

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 8:00 P.M. on Wednesday.

The DBA decides to recover up to the time right before the Drop Table command was issued at 8:00 P.M. The DBA also wants to recover the transactions that occurred to the remaining tables from the time after the Orders table was dropped, and up until the end of the backed-up Binary Logs. This decision ensures that he has recovered as many of the transactions as feasible, in addition to recovering the dropped table. Therefore, the following phases would be performed:

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 6:00 A.M. on Thursday.

The DBA must perform a recovery that restores the database up to the time right before the developer dropped the table at 6:00 A.M. on Thursday.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 6:00 A.M. on Thursday.

The DBA decides to recover up to the time right before the Drop Table command was issued. The DBA also wants to recover the transactions that occurred to the remaining tables from the time after the Orders table was dropped, and up until the end of the current Binary Logs. This decision ensures that he has recovered as many of the transactions as feasible, in addition to recovering the dropped table. Therefore, the following phases would be performed:

In the following examples, a Full and Incremental Backup scenario is in place, and the DBA wants to recover data to a specific time, but use a more definitive method to define the time. This recovery is done using identified “position values” that exist in the MySQL Binary Logs.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 8:00 P.M. on Wednesday.

The DBA decides to recover up to the time right before the Drop Table command was issued. Furthermore, the DBA wants a more precise recovery, so he decides to use a position-based recovery. To accomplish this process, the DBA must restore Sunday’s Full Backup and the subsequent Incrementals performed on Monday and Tuesday, and then perform a position-based PIT Recovery using Wednesday’s Incremental Backup. The following phases illustrate this process:

In this phase, only the Binary Logs recorded in Wednesday night’s Incremental Backup are restored to a temporary location. This process lets the DBA locate the specific position in the log that marks when the Orders table was dropped.

Use the mysqlbinlog utility against the restored Binary Logs: This step is performed outside of NetVault Backup to identify the position of the Drop Table command that the DBA does not want to restore. (For information about this utility and process, see the MySQL Reference Guide.) In this process, the DBA identified the Drop Table command as log position “805” in the “MYSQLSVR-bin.000009” Binary Log that was restored to the temporary location on the MySQL Server, and both values were noted.

With the position identified from the restored Binary Log, a PIT restore is then performed using the Wednesday’s Incremental Backup.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 8:00 P.M. on Wednesday.

The DBA decides to recover up to the time right before the Drop Table command was issued. The DBA also wants to recover the transactions that occurred to the remaining tables from the time after the Orders table was dropped, and up until the end of the backed-up Binary Logs. Furthermore, the DBA wants a more precise recovery, so he decides to use a position-based recovery. To accomplish this process, the DBA must restore Sunday’s Full Backup and the subsequent Incrementals performed on Monday and Tuesday, and then perform a position-based PIT Recovery using Wednesday’s Incremental Backup. The following phases illustrate this process:

In this phase, only the Binary Logs recorded in Wednesday night’s Incremental Backup are restored to a temporary location. This step lets the DBA locate the specific position in the log that marks when the Orders table was dropped.

Use the mysqlbinlog utility against the restored Binary Logs: This step is performed outside of NetVault Backup to identify the position of the Drop Table command that the DBA does not want to restore. (For information about this utility and process, see the MySQL Reference Guide.) In this process, the DBA identified the Drop Table command as log position “805” in the “MYSQLSVR-bin.000009” Binary Log that was restored to the temporary location on the MySQL Server, and both values were noted.

With the position identified from the restored Binary Logs, the PIT restore is then performed using Wednesday’s Incremental Backup.

IMPORTANT: Stop and Start positions must be actual positions listed in a Binary Log, not arbitrary numbers that are greater than the position of the unwanted transaction.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors for the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 6:00 A.M. on Thursday.

The DBA must perform a recovery that restores the database up to the time right before the developer dropped the table at 6:00 A.M. on Thursday. Furthermore, the DBA wants a more precise recovery, so he decides to use a position-based recovery. To accomplish this process, the DBA must restore Sunday’s Full Backup and the subsequent Incrementals performed on Monday and Tuesday, and then perform a position-based PIT Recovery using Wednesday’s Incremental Backup. The following phases illustrate this process:

Use the mysqlbinlog utility against the current Binary Logs: This step is performed outside of NetVault Backup to identify the position of the Drop Table command that the DBA does not want to restore. (For information about this utility and process, see the MySQL Reference Guide.) In this process, the DBA identified the Drop Table command as log position “805” in the current Binary Log, “MYSQLSVR-bin.000009.”

With the position identified from the restored Binary Logs, the PIT restore is then performed using Wednesday’s Incremental Backup.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors for the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 6:00 A.M. on Thursday.

The DBA must perform a recovery that restores the database up to the time right before the developer dropped the table at 6:00 A.M. on Thursday. Furthermore, the DBA wants a more precise recovery, so he decides to use a position-based recovery. To accomplish this process, the DBA must restore Sunday’s Full Backup and the subsequent Incrementals performed on Monday and Tuesday, and then perform a position-based PIT Recovery using Wednesday’s Incremental Backup. The following phases illustrate this process:

Use the mysqlbinlog utility against the current Binary Logs: This step is performed outside of NetVault Backup to identify the position of the Drop Table command that the DBA does not want to restore. (For information about this utility and process, see the MySQL Reference Guide.) In this process, the DBA identified the Drop Table command as log position “805” in the current Binary Log, “MYSQLSVR-bin.000009.”

With the position identified from the restored Binary Logs, the PIT restore is then performed using Wednesday’s Incremental Backup.

IMPORTANT: Stop and Start positions must be actual positions listed in a Binary Log, not arbitrary numbers that are greater than the position of the unwanted transaction.

Full and Differential Backup restore scenarios

The DBA has established a backup policy in which Full Backups are performed every Sunday at 11:00 P.M. and Differential Backups are performed Monday through Saturday, at 11:00 P.M. Because the DBA is performing Differential Backups, the Binary Logs are kept after each form of this backup — which creates a longer backup, but allows for a faster overall restore.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it sometime early Thursday before the DBA’s arrival at work.

The DBA decides to perform a complete recovery up to the point of the last Differential Backup — the backup performed on Wednesday night.

IMPORTANT: The DBA does not have to restore Monday and Tuesday night’s Differential Backups. By choosing to perform Differential Backups, each night’s backup is cumulative, back to Sunday night’s Full Backup; that is, Wednesday night’s backup includes all the Binary Logs that were generated on Monday, Tuesday, and Wednesday — back to Sunday’s Full Backup.

In the following examples, a Full and Differential Backup scenario is in place, and the DBA wants to recover data to a specific time.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 8:00 P.M. on Wednesday.

The DBA must perform a recovery that restores the database up to the time right before the developer dropped the table at 8:00 P.M. on Wednesday. Therefore, the following phases would be performed:

IMPORTANT: The DBA does not have to restore Monday and Tuesday night’s Differential Backups. By choosing to perform Differential Backups, each night’s backup is cumulative, back to Sunday night’s Full Backup; that is, Wednesday night’s backup includes all the Binary Logs that were generated on Monday, Tuesday, and Wednesday — back to Sunday’s Full Backup.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 8:00 P.M. on Wednesday.

The DBA decides to recover up to the time right before the Drop Table command was issued at 8:00 P.M. The DBA also wants to recover the transactions that occurred to the remaining tables from the time after the Orders table was dropped, and up until the end of the backed-up Binary Logs. This decision ensures that he has recovered as many of the transactions as feasible, in addition to recovering the dropped table. Therefore, the following phases would be performed:

IMPORTANT: The DBA does not have to restore Monday and Tuesday night’s Differential Backups. By choosing to perform Differential Backups, each night’s backup is cumulative, back to Sunday night’s Full Backup; that is, Wednesday night’s backup includes all the Binary Logs that were generated on Monday, Tuesday, and Wednesday — back to Sunday’s Full Backup.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 6:00 A.M. on Thursday.

The DBA must perform a recovery that restores the database up to the time right before the developer dropped the table at 6:00 A.M. on Thursday.

IMPORTANT: The DBA does not have to restore Monday and Tuesday night’s Differential Backups. By choosing to perform Differential Backups, each night’s backup is cumulative, back to Sunday night’s Full Backup; that is, Wednesday night’s backup includes all the Binary Logs that were generated on Monday, Tuesday, and Wednesday — back to Sunday’s Full Backup.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 6:00 A.M. on Thursday.

The DBA decides to recover up to the time right before the Drop Table command was issued. The DBA also wants to recover the transactions that occurred to the remaining tables from the time after the Orders table was dropped, and up until the end of the current Binary Logs. This decision ensures that he has recovered as many of the transactions as feasible, in addition to recovering the dropped table. Therefore, the following phases would be performed:

IMPORTANT: The DBA does not have to restore Monday and Tuesday night’s Differential Backups. By choosing to perform Differential Backups, each night’s backup is cumulative, back to Sunday night’s Full Backup; that is, Wednesday night’s backup includes all the Binary Logs that were generated on Monday, Tuesday, and Wednesday — back to Sunday’s Full Backup.

In the following examples, a Full and Incremental Backup scenario is in place, and the DBA wants to recover data to a specific time, but use a more definitive method to define the time. This process is done using identified “position values” that exist in the MySQL Binary Logs.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 8:00 P.M. on Wednesday.

The DBA decides to recover up to the time right before the Drop Table command was issued. Furthermore, the DBA wants a more precise recovery, so he decides to use a position-based recovery. The following phases illustrate this process:

In this phase, only the Binary Logs recorded in Wednesday night’s Differential Backup are restored to a temporary location. This process lets the DBA locate the specific position in the log that marks when the Orders table was dropped.

Use the mysqlbinlog utility against the restored Binary Logs: This step is performed outside of NetVault Backup to identify the position of the Drop Table command that the DBA does not want to restore. (For information about this utility and process, see the MySQL Reference Guide.) In this process, the DBA identified the Drop Table command as log position “805” in the “MYSQLSVR-bin.000009” Binary Log that was restored to the temporary location on the MySQL Server, and both values were noted.

With the position identified from the restored Binary Log, a PIT restore is then performed using Wednesday’s Differential Backup.

IMPORTANT: The DBA does not have to restore Monday and Tuesday night’s Differential Backups. By choosing to perform Differential Backups, each night’s backup is cumulative, back to Sunday night’s Full Backup; that is, Wednesday night’s backup includes all the Binary Logs that were generated on Monday, Tuesday, and Wednesday — back to Sunday’s Full Backup.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors on the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 8:00 P.M. on Wednesday.

The DBA decides to recover up to the time right before the Drop Table command was issued. The DBA also wants to recover the transactions that occurred to the remaining tables from the time after the Orders table was dropped, and up until the end of the backed-up Binary Logs. Furthermore, the DBA wants a more precise recovery, so he decides to use a position-based recovery. The following phases illustrate this process:

In this phase, only the Binary Logs recorded in Wednesday night’s Incremental Backup are restored to a temporary location. This process lets the DBA locate the specific position in the log that marks when the Orders table was dropped.

Use the mysqlbinlog utility against the restored Binary Logs: This step is performed outside of NetVault Backup to identify the position of the Drop Table command that the DBA does not want to restore. (For information about this utility and process, see the MySQL Reference Guide.) In this process, the DBA identified the Drop Table command as log position “805” in the “MYSQLSVR-bin.000009” Binary Log that was restored to the temporary location on the MySQL Server, and both values were noted.

With the position identified from the restored Binary Logs, the PIT restore is then performed using Wednesday’s Incremental Backup.

IMPORTANT: The DBA does not have to restore Monday and Tuesday night’s Differential Backups. By choosing to perform Differential Backups, each night’s backup is cumulative, back to Sunday night’s Full Backup; that is, Wednesday night’s backup includes all the Binary Logs that were generated on Monday, Tuesday, and Wednesday — back to Sunday’s Full Backup.

IMPORTANT: Stop and Start positions must be actual positions listed in a Binary Log, not arbitrary numbers that are greater than the position of the unwanted transaction.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors for the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 6:00 A.M. on Thursday.

The DBA must perform a recovery that restores the database up to the time right before the developer dropped the table at 6:00 A.M. on Thursday. Furthermore, the DBA wants a more precise recovery, so he decides to use a position-based recovery. The following phases illustrate this process:

Use the mysqlbinlog utility against the current Binary Logs: This step is performed outside of NetVault Backup to identify the position of the Drop Table command that the DBA does not want to restore. (For information about this utility and process, see the MySQL Reference Guide.) In this process, the DBA identified the Drop Table command as log position “805” in the current Binary Log, “MYSQLSVR-bin.000009”.

With the position identified from the restored Binary Logs, the PIT restore is then performed using Wednesday’s Differential Backup.

IMPORTANT: The DBA does not have to restore Monday and Tuesday night’s Differential Backups. By choosing to perform Differential Backups, each night’s backup is cumulative, back to Sunday night’s Full Backup; that is, Wednesday night’s backup includes all the Binary Logs that were generated on Monday, Tuesday, and Wednesday — back to Sunday’s Full Backup.

On Thursday at 9:00 A.M., the DBA learns that users are encountering “table not found” errors for the Orders table. The DBA then learns that the table no longer exists because a developer unknowingly dropped it at 6:00 A.M. on Thursday.

The DBA decides to recover up to the time right before the Drop Table command was issued. The DBA also wants to recover the transactions that occurred to the remaining tables from the time after the Orders table was dropped, and up until the end of the current Binary Log. Furthermore, the DBA wants a more precise recovery, so he decides to use a position-based recovery. The following phases illustrate this process:

Use the mysqlbinlog utility against the current Binary Logs: This step is performed outside of NetVault Backup to identify the position of the Drop Table command that the DBA does not want to restore. (For information about this utility and process, see the MySQL Reference Guide.) In this process, the DBA identified the Drop Table command as log position “805” in the current Binary Log, “MYSQLSVR-bin.000009.”

With the position identified from the restored Binary Logs, the PIT restore is then performed using Wednesday’s Differential Backup.

IMPORTANT: The DBA does not have to restore Monday and Tuesday night’s Differential Backups. By choosing to perform Differential Backups, each night’s backup is cumulative, back to Sunday’s Full Backup; that is, Wednesday night’s backup includes all the Binary Logs that were generated on Monday, Tuesday, and Wednesday — back to Sunday’s Full Backup.

IMPORTANT: Stop and Start positions must be actual positions listed in a Binary Log, not arbitrary numbers that are greater than the position of the unwanted transaction.