Restore DPA alert history from a MySQL backup

11/2/2018 7:22 PM

12/9/2019 7:43 PM

If you have a MySQL database repository for DPA, you may lose your alert history when upgrading to DPA 11.0. This article provides examples for restoring the CON_ALERT_HISTORY table from a backup file to the DPA repository.

While some database engines store backup data in a non-readable format, the MySQL backup file is a standard text file with all SQL commands needed for recreating the database structure and data.

• The prerequisite for restoring data from a MySQL backup file is to have the backup created with the option "Dump Structure and Data" or at least "Dump Data Only". This article assumes you selected "Dump Structure and Data" for the backup.
• From a backup file, it is possible to just copy the section of the "code" corresponding to a given table. The only problem can be the length and size of the backup data file itself, and using a text editor capable of opening and editing such a large file.

The MySQL engine can be installed on Linux or Windows. Follow the instructions for the operating system of your DPA server.

Database Performance Analyzer

Linux 

Open the command line, and execute the following command:

sed -n -e '/LOCK TABLES.*`CON_ALERT_HISTORY`/,/UNLOCK TABLES;/p' mysql.dump >CON_ALERT_HISTORY.dump

Note: You must change file names and paths according to your environment.

In this example, the command uses SED to search the mysql.dump file for INSERT statements for the CON_ALERT_HISTORY table. It then moves these statements to the newly created file CON_ALERT_HISTORY.dump. To restore the backup data, all commands from the new file must then be copied and executed in the MySQL database schema holding the DPA repository.

Windows 

1. Locate your MySQL backup file, and open it with a text editor.
2. Find the section that begins with 

   LOCK TABLES 'CON_ALERT_HISTORY'

   and ends with the first occurrence of 

   UNLOCK TABLES

   This block of commands consists of all INSERT statements for the table.

   

3. Copy this section, paste it to the MySQL Workbench console, and execute it.

   Data should be successfully inserted in your table.

Alternative approach with MySQL Workbench 

This is an alternative approach that restores a complete backup into a new database schema, and then transfers data into the table that you intend to restore. This approach applies to Windows and Linux.

In this example, dpa_database is the DPA repository database name and temp_schema is a newly created schema for objects from the backup file. Change these values according to your environment.

Note: The complete DPA repository will be restored from the backup and you have to manually execute the SQL statement that moves the data.

1. Open MySQL Workbench.
2. In the Navigator, select Management > Data Import/Restore.
3. Under Data Import, select Import from Self-Contained File, and locate your MySQL backup file.
4. Next to Default Target Scheme, click New… to create a new schema (temp_schema) for objects from your backup file.

   

5. Click Start Import, and check whether the import was successful.

   

6. Refresh schemas in the Navigator to see the new schema and imported objects (tables…).
7. Open a new SQL tab and execute this statement:

   INSERT INTO dpa_database.CON_ALERT_HISTORY SELECT * FROM temp_schema.CON_ALERT_HISTORY;

8. Check the data in the dpa_database.CON_ALERT_HISTORY table.
9. Drop the temp_schema.

   Data should be successfully inserted in your table.

(Screenshots property of © 2019 Oracle Corporation and/or its affiliates)

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.