Backup and Recovery solution for MySQL with Zmanda

 Backup Capabilities of Zmanda
    * ZRM for MySQL can backup multiple MySQL databases that are managed by the MySQL server.
    * It can backup multiple databases hosted on multiple MySQL servers.
    * It can also backup tables in a single database.
    * It can perform hot backups of the databases.
    * It supports multiple backup methods depending on the storage engine used by MySQL tables.
    * It has two levels of backups : full and incremental database backups.
    * It can use mysqldump, mysqlhotcopy, snapshots(Linux LVM/Solaris ZFS) and MySQL replication as various backup methods.
    * It creates consistent backup of the database irrespective of the storage engines used by the databases tables.
    * It supports SSL authentication between the local ZRM for MySQL and remote MySQL server to allow secure backups over Internet or across firewalls.
    * It can verify backed up data images.
    * Backup images can be compressed as well as encrypted using standard tool such as gzip, GPG, etc .
    * System administrator can abort backup jobs.

Recovery Capabilities
    * ZRM for MySQL makes it easy to recover backed up data.
    * It supports the use of an backup index that stores information about each backup run.
    * It has a reporting tool that can be used for browsing the index.
    * It can recover full and incremental database backups.
    * It does selective incremental restores based on binary log position or at a point in time . This permits recovery from database operator errors.
          o Such a point could be a point in time or a point in the binary logs of the Database.
          o ZRM for MySQL provides an easy way to filter in / filter out database events from the binary logs.
          o This helps in deciding what to restore and what to keep out.
    * Depending on the type of backups you have been doing, the backed up data could be recovered on to the same machine or to an entirely different machine.

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.