NetVault Plug-in for MySQL 4.4

On the Create Selection Set page, click Edit Plugin Options, and configure the applicable parameters on the Point-in-Time Recovery and Restore Destination tabs. The options displayed depend on the type of backup selected for restore.

•

Full or Individual database restore options

•

Incremental or Differential database restore options

Full or Individual database restore options

To restore either a Full Backup or an Individual Database/Table Copy Only Backup

Use the following guidelines to select the applicable options on the Point-in-Time Recovery tab.

•

Perform PIT Recovery on Current Binary Logs – Select this option to perform a Point-in-Time form of restore of the selected data objects using the Binary Logs currently residing in the MySQL Binary Log directory on the MySQL server. After this option is enabled, all remaining options on this tab will be made available.

•

Point In Time Type – In this section, select the applicable form of PIT Recovery:

•

Time Based PIT (default selection) – Select this option to restore the selected data to a specified time (described in Time-based Point-in-Time (PIT) Recovery). With this option selected, the Time Based PIT Details section is made available.

•

Position Based PIT – Select this option to restore the selected data to a specific stop position that exists right before an unwanted transaction (described in Position-based Point-in-Time (PIT) Recovery). With this option selected, the Position Based PIT Details section is made available.

•

Time Based PIT Details – If you selected Time Based PIT, select the applicable options:

•

Enable Recovery Prior to Erroneous/Bad SQL Statement(s) – To restore all transactions that occurred prior to the unwanted transaction, select this option. If you select only this option, all transactions that occurred after the time specified here will be lost. Using a 24-hour time format, specify the applicable date and time in the associated Stop Date/Time fields.

•

Enable Recovery After Erroneous/Bad SQL Statement(s) – To restore all transactions that occurred after to the unwanted transaction, select this option. If you select only this option, all transactions that occurred before the time specified here will be lost. Using a 24-hour time format, specify the applicable date and time in the associated Start Date/Time fields. With a specific start date and time selected, you can also set a stop date and time for transactions:

•

None (default selection) – Leave this option selected if you want to recover all transactions that occurred after the specified date and time.

•

Specific Date – If you only want to include transactions that occurred during a specific range of time, select this option, and enter the applicable stop time in the associated fields (using the 24-hour time format).


	IMPORTANT: When PIT Recovery is enabled on both restored and current Binary Logs, you do not have to determine whether the stop time is located in the restored binary logs or the current binary logs. MySQL will automatically stop and start at the specified time and ignore all the Binary Logs after the specified stop time. You can use both of these options, especially if there is a specific time range in which unwanted transactions occurred. For example, if data that was collected between 11:00 A.M. and 11:15 A.M. on January 29, 2007, was not wanted, the Enable Recovery Prior to … option would be enabled, and “11:00” - “29 Jan 2007” would be input as the Stop Date/Time. In addition, the Enable Recovery After … option would be enabled and “11:15” - “29 Jan 2007” would be input in as the Start Date/Time. As a result, all transactions that occurred between 11:00 and 11:15 on January 29, 2007, would be omitted from the restore.

•

Position Based PIT Details – If you selected Position Based PIT, select the applicable options:

•

Enable Recovery Prior to Erroneous/Bad SQL Statement(s) – To restore all transactions that occurred prior to the unwanted transaction, select this option. If you select only this option, all transactions that occurred after the position specified here will be lost. This option offers the following associated options:

•

Stop Position – Enter the position in the Binary Log prior to the unwanted transaction. For example, if the position of the unwanted transaction is 805, enter 804.

•

Binary Log Containing Stop Position – Use this list to select the specific Binary Log that contains the stop position specified in the Stop Position. If a different file is desired (or the applicable file is not listed), select OTHER, and enter the applicable file name in the text box.

•

Enable Recovery After Erroneous/Bad SQL Statement(s) – To restore all transactions that occurred after the unwanted transaction, select this option. If you select only this option, all transactions that occurred before the position specified here will be lost. This option also offers the following associated options:

•

Start Position – Enter the position in the Binary Log after the unwanted transaction. For example, if the position of the unwanted transaction is 805, enter 806.

•

Binary Log Containing Start Position – Use this list to select the specific Binary Log that contains the start position specified in the Start Position. If a different file is desired (or the applicable file is not listed), select OTHER, and enter the applicable file name in the text box.

•

Stop Position: None (default selection)– Leave this option selected if you want all transactions recovered that occurred after the specified Start Position.

•

Stop Position: Specific Position – If you only want to include transactions that occurred between a specific range of Binary Log positions, select this option, enter the applicable stop position, and select the applicable Binary Log in the Binary Log Containing Stop Position list (if a different file should be used, select OTHER, and enter the file name). Only transactions that occurred between the positions specified in the Start Position and the Specific Position fields will be included in the restore.


	IMPORTANT: You can use both of these options, especially if there is a specific range of positions in which unwanted transactions occurred. For example, if data that was collected between position 805 and 810 contained unwanted transactions, the Enable Recovery Prior to … option would be enabled, and “805” would be input as the Stop Position (and its associated options would be configured to call out the Binary Log). In addition, the Enable Recovery After … option would be enabled and “810” would be input in as the Start Position (and its associated options would be configured to call out the Binary Log). As a result, all transactions that were logged in the specified Binary Log between 805 and 810 would be omitted from the restore. Also, 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.

Use the following guidelines to select the applicable options on the Restore Destination stab.

•

Restoring to the same MySQL Instance – If the restore targets the same instance that was originally backed up, leave these fields blank. NetVault Backup will use the values set in the Configure dialog (for more information, see Configuring the plug-in).

•

Restoring to a different MySQL Instance – If you intend to relocate a restore of the selected data to a different instance, enter the applicable information in the Username and Password fields that will allow access to the new instance. Additionally, enter the NetVault Backup name established for the new instance in the Instance Name field (this is the name established as the MySQL Instance Name in the Configure dialog ; for more information, see Configuring the plug-in).


	IMPORTANT: Before attempting a relocation restore to a different MySQL Instance, review Recovering to an alternate MySQL Server.

Incremental or Differential database restore options

To restore either an Incremental or Differential Backup

Use the following guidelines to select the applicable options on the Point-in-Time Recovery tab.

•

Perform PIT Recovery – Select this option to perform a Point-in-Time form of restore of the selected data items. After this option is enabled, all remaining options on this tab will be made available.

Incremental and Differential Restores use the Binary Logs to complete a restore. Therefore, when restoring this form of backup, you must determine how the Binary Logs associated with the selected databases are to be recovered. Select one of the following methods:

•

Restore and Apply Binary Logs (Used when Time or Position is already known) – If the time or position at which corruption occurred is known, select this option to restore the Binary Logs from the backup device and apply the recorded transactions in one restore job. If you also want to perform a PIT Recovery on the Binary Logs currently residing in the MySQL Binary Log directory, select the Include Current Binary Logs check box. This will be performed after any Binary Log transactions that were saved in the Incremental/Differential Backup are restored and applied.

•

Restore Logs to Temporary Directory to Identify Time or Position – To restore only the Binary Logs associated with the selected Incremental/Differential Backup to a temporary directory on the MySQL Server (that is, “NETVAULT_HOME/tmp/MySQL/”), select this option. This lets you use the mysqlbinlog utility to review the recovered logs to identify the time/position of the data corruption.

•

Apply Binary Logs from Temporary Directory – If you previously used the Restore Logs to Temporary Directory to Identify Time or Position option, and you used the mysqlbinlog utility to identify the corrupted data that is to be omitted from the restore, select this option. This will apply the Binary Logs that were restored to the temporary directory. If you also want to perform a PIT Recovery on the Binary Logs currently residing in the MySQL Binary Log directory, select the Include Current Binary Logs check box. This will be performed after the Binary Log transactions that exist in the temporary directory are restored and applied.

•

Point In Time Type – With the Perform PIT Recovery option enabled, you must select the applicable form of PIT Recovery:

•

Time Based PIT Details – If you selected Time Based PIT, select the applicable options:

•

Enable Recovery After Erroneous/Bad SQL Statement(s) – To restore all transactions that occurred after the unwanted transaction select this option. If you select only this option, all transactions that occurred before the time specified here will be lost. Using a 24-hour time format, specify the applicable date and time in the associated Start Date/Time fields. With a specific start date and time selected, you can also set a stop date and time for transactions:

•

None (default selection) – Leave this option selected if you want to recover all transactions that occurred after the specified date and time.

•


	IMPORTANT: You can use both of these options, especially if there is a specific time range in which unwanted transactions occurred. For example, if data that was collected between 11:00 A.M. and 11:15 A.M. on January 29, 2007, was not wanted, the Enable Recovery Prior to … option would be enabled, and “11:00” - “29 Jan 2007” would be input as the Stop Date/Time. In addition, the Enable Recovery After … option would be enabled and “11:15” - “29 Jan 2007” would be input in as the Start Date/Time. As a result, all transactions that occurred between 11:00 and 11:15 on January 29, 2007, would be omitted from the restore.

•

Position Based PIT Details – If you selected Position Based PIT, select the applicable options:

•

Enable Recovery Prior to Erroneous/Bad SQL Statement(s) – To restore all transactions that occurred prior to the unwanted transaction, select this option. If you select only this option, all transactions that occurred after the position specified here will be lost. This option offers the following associated options:

•

Stop Position – Enter the position in the Binary Log prior to the unwanted transaction. For example, if the position of the unwanted transaction is 805, enter 804.

•

Start Position – Enter the position in the Binary Log after the unwanted transaction. For example, if the position of the unwanted transaction is 805, enter 806.

•

Stop Position: None (default selection) – Leave this option selected if you want all transactions recovered that occurred after the specified Start Position.

•


	IMPORTANT: You can use both of these options, especially if there is a specific range of positions in which unwanted transactions occurred. For example, if data that was collected between position 805 and 810 contained unwanted transactions, the Enable Recovery Prior to … option would be enabled, and “805” would be input as the Stop Position (and its associated options would be configured to call out the Binary Log). In addition, the Enable Recovery After … option would be enabled and “810” would be input in as the Start Position (and its associated options would be configured to call out the Binary Log). As a result, all transactions that were logged in the specified Binary Log between 805 and 810 would be omitted from the restore. Also, 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.

Use the following guidelines to select the applicable options on the Restore Destination tab.

This tab contains the Restore Destination Details section which is comprised of three fields. these fields are used to input account information to allow restore access to the target instance of MySQL. Based on the desired restore type, use these options as follows:

•


	IMPORTANT: Before attempting a relocation restore to a different MySQL Instance, review Recovering to an alternate MySQL Server.

Setting restore options for MySQL Enterprise Backup

On the Create Selection Set page, click Edit Plugin Options, and configure the applicable parameters on the Options tab:


	IMPORTANT: Before you perform a restore, make sure that the default NetVault Backup Temporary Directory has sufficient space to accommodate (at least temporarily) all the data included in a Full Backup that was created using the MySQL Enterprise Backup option. You can use the General option to change the default setting to a location that provides sufficient space; you can even use a mapped drive, network file system (NFS), or SMB mount. In the Navigation pane, click Change Settings, click Server Settings, and then click General in the System and Security section.

•

Full Restore – Select the applicable options.

•

Restore, Extract Raw Full Backup… (default selection) – Select this option to restore a Full Backup to a temporary location that mirrors the MySQL Server data repository directory hierarchy. This option assumes that you know which backup to restore; if you do not, you can use the next two options.

•

Restore Full Backup Image to Temp File – Select this option if you need to list the contents of the backup to determine which backup you need to execute the next option.

•

Extract Raw Full Backup from Temp File… – Select this option after you have used the results the preceding option, Restore Full Backup Image to Temp File, to determine which backup you need to restore. This option restores the Full Backup to a temporary location that mirrors the MySQL Server data repository directory hierarchy.

•

Shutdown MySQL Server and Copy Back… – Select this option when you are ready to shut down the MySQL Server and copy the restored contents from the temporary location back to the original location.

•

Validate Backup Image – To instruct the plug-in to run the validate command against the extracted data, select this check box.

•

List Backup Image – To list the contents of the backup in the output log, select this check box.

•

Incremental Restore – Select the applicable options.

•

Restore, Extract Incremental Backup… (default selection) – Select this option to restore an Incremental Backup. This option assumes that you know which backup to restore; if you do not, you can use the next two options.

•

Restore Incremental Backup Image to Temp File – Select this option if you need to list the contents of the backup to determine which backup you need to execute the next option.

•

Extract Incremental Backup from Temp File… – Select this option after you have used the results the preceding option, Restore Incremental Backup Image to Temp File, to determine which backup you need to restore.

•

Validate Backup Image – To instruct the plug-in to run the validate command against the extracted data, select this check box.

•

List Backup Image – To list the contents of the backup in the output log, select this check box.

Finalizing and submitting a job

The final steps include setting additional options on the Schedule, Source Options, and Advanced Options pages, submitting the job, and monitoring the progress via the Job Status and View Logs pages. These pages and options are common to all NetVault Backup Plug-ins. For more information, refer to the Dell NetVault Backup Administrator’s Guide.

To finalize and submit a restore job

Click Ok to save the settings, and then click Next.

In Job Name, specify a name for the job if you do not want to use the default setting.

Assign a descriptive title that lets you easily identify the job for monitoring its progress. The job name can contain alphanumeric and non-alphanumeric characters, but it cannot include non-English characters. On Linux, the names can have a maximum of 200 characters. On Windows, there is no length restriction. However, a maximum of 40 characters is recommended on all platforms.


	IMPORTANT: Do not use special characters that are not supported in a file name on the target OS. For example, the characters /,\,*,@, and so on, should not be used on Windows. (This is because Plug‑in for MySQL tries to create a folder with the same name as the Job Title for restoring data temporarily.)

In the Target Client list, select the machine on which you want to restore the data.


	TIP: You can also click Choose, and then locate and select the applicable client in the Choose the Target Client dialog.

Use the Schedule, Source Options, and Advanced Options lists to configure the any additional required options.

Click Submit to submit the job for scheduling.

You can monitor progress on the Job Status page and view the logs on the View Logs page. For more information, refer to the Dell NetVault Backup Administrator’s 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 in regards to data selected for restore and options available on the Options tab. The following sections offer examples of various types of recovery and cover the specific options required.

•

Full Backup only restore scenarios

•

Full and Incremental Backup restore scenarios

•

Full and Differential Backup restore scenarios

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.

Full Backup restore & time-based Point-in-Time Recovery

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

Method 1: Recovery prior to erroneous statement

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

Select the Full Restore from Sunday night – On the Create Restore Job - Choose Saveset page, the DBA selects the backup saveset that corresponds to Sunday’s Full Backup.

Set specific options on the restore-related Options tab – The DBA sets the following options:

•

Perform PIT Recovery on Current Binary Logs – Selected to enable this form of restore and all associated options.

•

Time Based PIT – Selected as the type.

•

Enable Recovery Prior to Erroneous/Bad SQL Statement(s) – Selected this option, and set the Stop Date/Time to “5:59” and “8 Jan 2007” (that is, one minute prior to 6:00 A.M. on Monday).

Submit the job.

Method 2: Recovery prior to and after erroneous statement

Select the Full Restore from Sunday night – On the Create Restore Job - Choose Saveset page, the DBA selects the backup saveset that corresponds to Sunday’s Full Backup.

Set specific options on the restore-related Options tab – The DBA sets the following options:

•

Perform PIT Recovery on Current Binary Logs – Selected to enable this form of restore all associated options.

•

Time Based PIT – Selected as the type.

•

Enable Recovery Prior to Erroneous/Bad SQL Statement(s) – Selected this option, and set the Stop Date/Time to “5:59” and “8 Jan 2007” (that is, one minute prior to 6:00 A.M. on Monday).

•

Enable Recovery After Erroneous/Bad SQL Statements – Selected to recover transactions that occurred after the Order table was dropped, and entered a later time and date in the Start Date/Time. Finally, because the recovery is to be performed to the end of the named Binary Log, the None option was selected for the Stop Date/Time.

Full restore and position-based Point-in-Time Recovery

Method 1: Recovery prior to erroneous statement

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, the DBA must restore Sunday’s Full Backup and perform a PIT Recovery on the current Binary Logs.

Use the mysqlbinlog utility against the current Binary Logs – This 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 on this utility and process, refer to 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.

Select the Full Restore from Sunday night – On the Create Restore Job - Choose Saveset page, the DBA selects the backup saveset that corresponds to Sunday’s Full Backup.

Set specific options on the restore-related Options tab – The DBA sets the following options:

•

Perform PIT Recovery on Current Binary Logs – Selected to enable this form of restore and all associated options.

•

Position Based PIT – Selected as the type.

•

Enable Recovery Prior to Erroneous/Bad SQL Statement(s) – Selected this option, and set the Stop Position to “804” (the position before the one identified using mysqlbinlog). Set Binary Log Containing Stop Position to OTHER FILE, and entered the name of the target binary file in the text box (for example, “MYSQLSVR-bin.000009”).


	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.

Submit the job.

Method 2: Recovery prior to and after erroneous statement

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 will ensure 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, the DBA must restore Sunday’s Full Backup and perform a PIT Recovery on the current Binary Logs.

Select the Full Restore from Sunday night – On the Create Restore Job - Choose Saveset page, the DBA selects the backup saveset that corresponds to Sunday’s Full Backup.

Set specific options on the restore-related Options tab – The DBA sets the following options:

•

Perform PIT Recovery on Current Binary Logs – Selected to enable this form of restore and all associated options.

•

Position Based PIT – Selected as the type.

•

Enable Recovery After to Erroneous/Bad SQL Statement(s) – Selected this option, and set the Start Position to “806” (the position after the one identified using mysqlbinlog). Set Binary Log Containing Start Position to OTHER FILE, and entered the name of the target binary file in the text box (for example, “MYSQLSVR-bin.000009”). Finally, because the recovery is to be performed to the end of the named Binary Log, the None option was selected for the Stop Position.


	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.

Submit the job.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

NetVault Plug-in for MySQL 4.4 - User Guide

Setting restore options

Setting restore options for MySQL Enterprise Backup

Finalizing and submitting a job

Examples of restore scenarios for MySQL Standard/Community