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.

______________________

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState