Database Events

Database events report

The Database Events page lets you find and view all the database events logged and copied during incremental backups. This is useful when you need to find a particular malicious or erroneous transaction so you can roll back the unintended change. Once you find the problem event, you can easily launch a restore operation that will roll back all changes to the moment before the damage occurred. To back up and restore at the transaction level requires that binary logging is enabled on the MySQL server.

It is possible to restore selected events or undo selective events from the database. You can also search for errors and save the search query for future searches. This will make identification of erroneous events easier.
ReportDBEvents-ZRM-3.2.png

The left panel displays a calender control that lets you select a backup icon from dates on which single or multiple backups where processed.

The right panel features controls that help you locate events within the selected backup. When you open the page, the right panel displays events from the selected backup date for the selected backup set. Binary log backups are stored as part of full and log incremental backups.

Parsing of binary logs from the backup images can take time (up to few minutes).  To refresh the report page using Parse Events button at the bottom of the page (as shown below) The backup images are parsed again and report is re-populated.

ReportDBEvents-ButtonBar-ZRM-3.2.png

 

 

Selecting Backup Date

ReportDBEvents-Calendar-3.4.jpeg

 

There two methods for selecting a backup:

  • You can click a backup icon on the calendar.
  • You can enter the backup date (mm/dd/yy[yy]) at the top of the left panel and click Go to select a backup date.

The calendar shows the dates on which full backup (level 0) and incremental backups (level 1) were performed. If the multiple backup runs happened on a calendar day, it is shown with a different icon. You can select any date that has full or incremental backups.

The binary logs in the backups are parsed and displayed in the database events window. When user selects a backup date, the backups done on that date are parsed.  You can use Parse Events button to parse the backup image again. Page refresh will not parse backups again.

Note: If no backup exists for the selected date, then no data is displayed. This does not necessarily mean that database event logs do not exist for that date; they may be available from a binary log that was backed up subsequently. In other words, the Database Events log includes all data from a given backup to the next backup that included logged events.

Database Event Viewer

After you have selected a backup that includes log data, the events viewer lists the events it contains in a summary list. You can click on the more link to get complete description of the event as shown below.

ReportDBevents-details-3.4.jpeg
 
 

Database Event Search

ReportDBevents-search-3.4.jpeg

You can enter a text string to search for specific database events. You can use MySQL fulltext search Boolean operators to further control the search. For example, you can quote entire phrases to find them, and use plus (+) operator to refine searches on individual words.

  • + A leading plus sign indicates that this word must be present in each row that is returned.
  • - A leading minus sign indicates that this word must not be present in any of the rows that are returned. You can use both + and - together
  • The asterisk serves as the truncation (or wildcard) operator. Unlike theother operators, it should be appended to the word to be affected. Words match if they begin with the word preceding the * operator.
  • A phrase that is enclosed within double quote (“"”) characters matchesonly rows that contain the phrase literally, as it was typed. Search "open source backup" we will do the search for exact that phrase only.

All the search results are selected. The search results are also highlighted. It is possible to use the left and right arrow buttons next to the search box to go from one search result to another.

Search queries can be named and saved for future searches (Use Save button next to the search box). Clicking Openbutton next to the search box to use saved search queries. Saving search queries can help in performing the same queries on multiple backup sets or multiple backup dates.

Launching Restores from the Database Events Reports

ReportDBevents-restore-selection-3.4.jpeg

After selecting the event(s) using the checkbox next to each event, you can restore to a selected event (of course, only one event should be selected) or restore only the selected events or restore everything except the selected event (undo selected events). Multiple events can be selected for restoration or undo the effects of the selected events. Restoring to a selected event is equivalent of point-in-time recovery i.e, all database events starting from last full backup to the specified event are restored.

ReportDBEvents-ShowSelectedEvents-ZRM-3.2.png
 

Show selected events button shows all the selected events in one page as shown above. This can be used to review selected events and confirm the restoration process. Clicking Go in the Database event report or Restore in the selected events page takes you to Restore What page to continue with restoration process.

Selective restores should be performed carefully. Otherwise, there can be restoration failures or data loss. Few important points that user must be aware of:

  1. ZRM does not have capability of verifying if the selected events for restoration or the undo operation makes sense from the database or the database application. It is up to the user confirm that the restoration of events will keep the database consistent from application point of view and there will be loss of data.
  2. While performing selective restore, database constraints should be met after selective restoration. Selective restore can fail due to database constraints.
  3. The mysql user used for restoration (defined in Backup What page) should have privileges to perform the selected events being restored. The privileges should be granted to the user before attempting restoration.
  4. Selective restoration depends on the time to be in sync between the ZRM server and MySQL server in case of remote MySQL servers. Otherwise, correct events may not be restored.