Selective recovery of MySQL databases

by Paddy Sreenivasan

With the use of MySQL in the enterprise applications and Web 2.0 applications, Backup and recovery of MySQL databases has become one of the important tasks for a database administrator. There are lots of methods to do application consistent MySQL database backups. The backup method used depends on MySQL configuration as well as the MySQL storage engines used by the MySQL database. These backup methods mostly deal with full
backup of the database.

MySQL maintains binary logs for supporting MySQL server replication. These binary logs can be used to selectively recover MySQL databases. MySQL binary logs contain all database events (SQL statements) that modify data or could have modified data. These binary logs can be used as incremental backups
of the MySQL server.

MySQL server does not create binary logs by default. It has to be enabled as a command line option to MySQL server. Enabling MySQL binary logging has minimal impact on the MySQL database application performance. MySQL provides mysqlbinlog tool to process binary logs. This tool can be used for selectively restoring database events based on when it occurred or position in the binary log.

Zmanda Recovery Manager(ZRM) for MySQL , a comprehensive MySQL backup and recovery manager, provides a mechanism to list the database events, time when the event occurred and the position in the binary log. This information can be used for selective recovery of database to any point in time between backups. I'm one of the ZRM for MySQL project developers. This is an open source project (GPL).

Selective recovery of databases can also be used for recovery from operator errors such as execution of DROP TABLE or DROP DATABASE statements or intentional sabotage of the database by an user. Of course, without regular backups, it is not possible to do selective recovery. It is also important to do restore audits of the backup images so that the backups are available when things go wrong.