Recover a MySQL Table with Zmanda Recovery Manager
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.
|Raspi-Sump||Dec 16, 2014|
|diff -u: What's New in Kernel Development||Dec 12, 2014|
|Non-Linux FOSS: Don't Type All Those Words!||Dec 10, 2014|
|Computing without a Computer||Dec 08, 2014|
|Autokey: Shorthand for Typists||Dec 04, 2014|
|How Can We Get Business to Care about Freedom, Openness and Interoperability?||Dec 03, 2014|
- Cooking with Linux - Serious Cool, Sysadmin Style!
- Readers' Choice Awards 2014
- diff -u: What's New in Kernel Development
- How Can We Get Business to Care about Freedom, Openness and Interoperability?
- Synchronize Your Life with ownCloud
- Days Between Dates?
- Computing without a Computer
- Non-Linux FOSS: Don't Type All Those Words!
- The Awesome Program You Never Should Use
Editorial Advisory Panel
Thank you to our 2014 Editorial Advisors!
- Jeff Parent
- Brad Baillio
- Nick Baronian
- Steve Case
- Chadalavada Kalyana
- Caleb Cullen
- Keir Davis
- Michael Eager
- Nick Faltys
- Dennis Frey
- Philip Jacob
- Jay Kruizenga
- Steve Marquez
- Dave McAllister
- Craig Oda
- Mike Roberts
- Chris Stark
- Patrick Swartz
- David Lynch
- Alicia Gibb
- Thomas Quinlan
- Carson McDonald
- Kristen Shoemaker
- Charnell Luchich
- James Walker
- Victor Gregorio
- Hari Boukis
- Brian Conner
- David Lane