Recover a MySQL Table with Zmanda Recovery Manager

May 12th, 2008 by Paddy Sreenvasan in

Your rating: None Average: 2 (1 vote)

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


Special Magazine Offer -- Free Gift with Subscription
Receive a free digital copy of Linux Journal's System Administration Special Edition as well as instant online access to current and past issues. CLICK HERE for offer

Linux Journal: delivering readers the advice and inspiration they need to get the most out of their Linux systems since 1994.

Post new comment

Please note that comments may not appear immediately, so there is no need to repost your comment.
The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <pre> <ul> <ol> <li> <dl> <dt> <dd> <i> <b>
  • Lines and paragraphs break automatically.

More information about formatting options

Newsletter

Each week Linux Journal editors will tell you what's hot in the world of Linux. You will receive late breaking news, technical tips and tricks, and links to in-depth stories featured on www.linuxjournal.com.
Sign up for our Email Newsletter

Tech Tip Videos

From the Magazine

December 2009, #188

If last month's Infrastrucuture issue was too "big" for you then try on this month's Embedded issue. Find out how to use Player for programming mobile robots, build a humidity controller for your root cellar, find out how to reduce the boot time of your embedded system, and if you're new to embedded systems find out the basics that go into one. You can also read about the Beagle Board, the Mesh Potato and a spate of other interestingly named items. And along with our regular columns don't miss our new monthly column: Economy Size Geek.


Read this issue