Submit a ticketCall us

Have You Auto Renewed? If not, you're missing out.
The SolarWinds Renewal Program comes with a host of benefits including the most recent product updates, 24/7 technical support, virtual instructor-led training and more. Experience all of this with the convenience of Auto Renewal, and never worry about missing any of these great benefits. Learn More.

Home > Success Center > Database Performance Analyzer (DPA) > Restore DPA alert history from a MySQL backup

Restore DPA alert history from a MySQL backup

Updated February 2, 2017

Overview

If you have a MySQL database repository for DPA, you many 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.

Environment

  • DPA 11.0

Steps

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.

    1-win.png

  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 transers 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.

    2-workbench-1.png

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

    3-workbench-2.png

  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.

 

Last modified

Tags

Classifications

Public