Recover a MySQL Table with Zmanda Recovery Manager

 in

If somebody accidentally drops a critical table in MySQL, the application no longer works. The solution to this problem is to utilize the (open source) Zmanda Recovery Manager.

You are a MySQL database administrator. You take regular backups of your MySQL database. Somebody drops a table critical to the MySQL application (for example, the "accounts" table in a SugarCRM application). The MySQL application no longer works. How can you recover from the situation?

The answer is MySQL binary logs. Binary logs track all updates to the database with minimal impact on database performance. MySQL binary logs have to be enabled on the server. You can use the mysqlbinlog MySQL command to recover from the binary logs.

A more comprehensive solution is to use the Zmanda Recovery Manager for MySQL. The mysql-zrm tool allows users to browse the binary logs and selectively restore the database from incremental backups:

      # mysql-zrm --action parse-binlogs   --source-directory=/var/lib/mysql
      /sugarcrm/20060915101613
      Log filename                 | Log Position | Timestamp         | Event Type |    Event
      /var/lib/mysql/my-bin.000015 | 11013        | 06-09-12 06:20:03 | Xid = 4413 | COMMIT;
      /var/lib/mysql/my-bin.000015 | 11159        | 06-09-12 06:20:03 | Query      | DROP TABLE IF EXISTS `accounts`;

Here we're doing selective recovery for incremental backups without the DROP customer table from the SugarCRM database. Do two selective restore commands to restore from the incremental backup done on Sept 15, 2006, without executing the database event DROP TABLE at log position 11159:

      # mysql-zrm --action restore  --backup-set sugarcrm \
         --source-directory=/var/lib/mysql/ sugarcrm/20060915101613/ \
         --stop-position 11014
      # mysql-zrm --action restore   --backup-set sugarcrm \
         --source-directory=/var/lib/mysql/ sugarcrm/20060915101613/ \
         --start-position 11160

See the Zmanda Recovery Manager for MySQL for more information: http://mysqlbackup.zmanda.com.

______________________

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix