Introducing NetVault Backup Plug-in for MySQL
NetVault Backup Plug-in for MySQL: at a Glance
Key benefits
Feature summary
Target audience
Recommended additional reading
Installing and removing the plug-in
Installation prerequisites
Reviewing the recommended configuration
Installing or upgrading the plug-in
Removing the plug-in
Removing a specific MySQL Instance
Configuring the plug-in
Backing up data
Restoring data
Restoring data: an overview
Working with native MySQL replication
Reviewing the available restore methods for MySQL Standard/Community
Reviewing the available restore options for MySQL Enterprise Backup
Restoring data in MySQL
Selecting data for a restore
Setting restore options
Using advanced restore procedures for MySQL Standard/Community
Setting restore options for MySQL Standard/Community
Setting restore options for MySQL Enterprise Backup
Finalizing and submitting the job
Examples of restore scenarios for MySQL Standard/Community
Full Backup only restore scenarios
Full and Incremental Backup restore scenarios
Full and Differential Backup restore scenarios
PIT restore when the MIXED Binary Logging Format is used and cross-database updates are issued
Examples of restore scenarios for MySQL Enterprise Backup
Using the plug-in in a native environment: an overview
Enabling replication support
Backing up replication servers
Restoring replication servers
Using the plug-in in a Failover Cluster environment
MySQL Server Failover Clustering: an overview
Installing or upgrading the plug-in
Configuring the plug-in
Backing up data
Restoring data
Troubleshooting
Selecting data for a restore
|
1 |
In the Navigation pane, click Create Restore Job. |
|
2 |
On the Create Restore Job — Choose Saveset page, select Plug‑in for MySQL from the Plugin Type list. |
|
3 |
When you select a saveset, the following details are displayed in the Saveset Information area: Job ID, job title, server name, client name, plug-in name, saveset date and time, retirement setting, Incremental Backup or not, Archive or not, saveset size, and snapshot-based backup or not.
|
5 |
Click Next. |
|
6 |
On the Create Selection Set page, select the data that you want to restore. |
|
• |
Full or Individual Database/Table Copy Only Backups: The root node is listed as “All Databases” — because the actual database and table data was included in the backup. |
|
IMPORTANT: Although the root node is entitled “All Databases,” it does not account for all the databases that currently exist for a target MySQL Instance. Selecting this option only restores all the data items that were selected for the backup job; that is, by selecting this node for a restore, you are not performing a restore of all the databases that currently exist in a MySQL Instance — only those databases included in the backup. |
|
• |
Incremental or Differential Backups: The root node is listed as “Binary Logs” — because the transactions (Binary Logs) that occurred since the previous backup was performed are included in this form of backup. |
|
IMPORTANT: MySQL uses multiple file-formats to storage database information. Verify that you include the .frm files in the restore process to ensure that the restored database is functional. |
Setting restore options
The options displayed on the Options tab depend on whether you use the MySQL Standard/Community option or MySQL Enterprise Backup option.
Setting restore options for MySQL Standard/Community
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.
|
1 |
Use the following guidelines to select the applicable options on the Point-in-Time Recovery tab. |
|
• |
Perform PIT Recovery on Current Binary Logs: 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, select this option. After this option is enabled, all remaining options on this tab are made available. |
|
• |
Point In Time Type: In this section, select the applicable form of PIT Recovery: |
|
• |
Time Based PIT (default selection): To restore the selected data to a specified time, described in Time-based Point-in-Time (PIT) Recovery, select this option. With this option selected, the Time Based PIT Details section is made available. |
|
• |
Position Based PIT: 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, select this option. 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 before the unwanted transaction, select this option. If you select only this option, all transactions that occurred after the time specified here are 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 are 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 automatically stops and starts at the specified time and ignores 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, select the Enable Recovery Prior to … option and enter “11:00” - “29 Jan 2007” 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, are 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 before the unwanted transaction, select this option. If you select only this option, all transactions that occurred after the position specified here are lost. This option offers the following associated options: |
|
• |
Stop Position: Enter the position in the Binary Log before 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 you want a different file or the applicable file is not listed, select OTHER, and enter the applicable filename 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 are 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 you want a different file or the applicable file is not listed, select OTHER, and enter the applicable filename 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 filename. Only transactions that occurred between the positions specified in the Start Position and the Specific Position fields are 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, select the Enable Recovery Prior to … option and enter “805” as the Stop Position, and then configure its associated options to call out the Binary Log. In addition, select the Enable Recovery After … option and enter “810” as the Start Position, and then configure its associated options to call out the Binary Log. As a result, all transactions that were logged in the specified Binary Log between 805 and 810 are 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. |
|
2 |
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 uses the values set in the Configure dialog box. 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 allows access to the new instance. Also, enter the NetVault Backup name established for the new instance in the Instance Name field — this name is the name established as the MySQL Instance Name in the Configure dialog box ; for more information, see Configuring the plug-in. |
To restore either an Incremental or Differential Backup, perform the following steps.
|
1 |
Use the following guidelines to select the applicable options on the Point-in-Time Recovery tab. |
|
• |
Perform PIT Recovery: To perform a Point-in-Time form of restore of the selected data items, select this option. After this option is enabled, all remaining options on this tab are made available. |
|
• |
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 process is performed after any Binary Log transactions that were saved in the Incremental or 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 or Differential Backup to a temporary directory on the MySQL Server, that is, “NETVAULT_HOME/tmp/MySQL/,” select this option. This option lets you use the mysqlbinlog utility to review the recovered logs to identify the time and 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 process applies 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 process is 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, select the applicable form of PIT Recovery: |
|
• |
Time Based PIT (default selection): To restore the selected data to a specified time, described in Time-based Point-in-Time (PIT) Recovery select this option. With this option selected, the Time Based PIT Details section is made available. |
|
• |
Position Based PIT: 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 select this option. 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 before the unwanted transaction, select this option. If you select only this option, all transactions that occurred after the time specified here are 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 the unwanted transaction, select this option. If you select only this option, all transactions that occurred before the time specified here are 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. Enter the applicable stop time in the associated fields, using the 24-hour time format. |
|
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, select the Enable Recovery Prior to … option and enter “11:00” - “29 Jan 2007” as the Stop Date/Time. In addition, select the Enable Recovery After … option and enter “11:15” - “29 Jan 2007” as the Start Date/Time. As a result, all transactions that occurred between 11:00 and 11:15 on January 29, 2007, are 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 before the unwanted transaction, select this option. If you select only this option, all transactions that occurred after the position specified here are lost. This option offers the following associated options: |
|
• |
Stop Position: Enter the position in the Binary Log before 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 you want a different file or the applicable file is not listed, select OTHER, and enter the applicable filename in the text box. |
|
• |
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 position specified here are 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 you want a different file or the applicable file is not listed, select OTHER, and enter the applicable filename 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 filename. Only transactions that occurred between the positions specified in the Start Position and the Specific Position fields are 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, select the Enable Recovery Prior to … option and enter “805” as the Stop Position, and then configure its associated options to call out the Binary Log. In addition, select the Enable Recovery After … option and enter “810” as the Start Position, and then configure its associated options to call out the Binary Log. As a result, all transactions that were logged in the specified Binary Log between 805 and 810 are 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. |
|
2 |
Use the following guidelines to select the applicable options on the Restore Destination tab. |
This tab contains the Restore Destination Details section. Use the fields in this section to input account information to allow restore access to the target instance of MySQL. Based on the wanted restore type, use these options as follows:
|
• |
Restoring to the same MySQL Instance: If the restore targets the same instance that was originally backed up, leave these fields blank. NetVault Backup uses the values set in the Configure dialog box. 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 allows access to the new instance. Also, enter the NetVault Backup name established for the new instance in the Instance Name field — this name is the name established as the MySQL Instance Name in the Configure dialog box ; for more information, see Configuring the plug-in. |
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, verify 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 Client Settings, and then click General in the System and Security section. |
|
• |
Full Restore: Select the applicable options. |
|
• |
Restore, Extract Raw Full Backup… (default selection): To restore a Full Backup to a temporary location that mirrors the MySQL Server data repository directory hierarchy, select this option. 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: If you must list the contents of the backup to determine which backup you require to run the next option, select this option. |
|
• |
Extract Raw Full Backup from Temp File…: After you have used the results the preceding option to determine which backup you must restore, select this option. 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… (option available for standard Full Restores): When you are ready to shut down the MySQL Server and copy the restored contents from the temporary location back to the original location, select this option. |
|
• |
Copy Back Prepared Full Backup to MySQL Server Repository (option available for TTS Full Restores): If you want to copy the restored contents from the temporary location back to the original location, select this option. Two more options are also available: |
|
• |
Include Tables: If you want to perform a partial restore, enter a regular expression in this field to describe the naming pattern of the tables that you want to include in the restore. If you complete this field, the plug-in issues the “--include-tables” MySQL command. |
|
• |
Rename Table: If you completed the Include Tables field to specify which tables to restore, you can use this field to change the name of one of the specified tables. To rename a table, use the original_name to new_name expression. If you complete this field, the plug-in issues the “--rename” MySQL command. |
|
IMPORTANT: When you select a TTS Backup to restore using the Shutdown MySQL Server and Copy Back… option, the plug-in ignores items selected on the Restore Selections dialog box. The plug-in only restores the tables specified in the Include Tables field and the corresponding database. |
|
• |
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): To restore an Incremental Backup, select this option. 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: If you must list the contents of the backup to determine which backup you require to run the next option, select this option. |
|
• |
Extract Incremental Backup from Temp File…: After you have used the results the preceding option to determine which backup you must restore, select this option. |
|
• |
Shutdown MySQL Server and Copy Back…: When you are ready to shut down the MySQL Server and copy the restored contents from the temporary location back to the original location, select this option. |
|
• |
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. |