PDA

View Full Version : table recovery



lovewadhwa
May 4th, 2007, 10:14 PM
hi
i have taken full backup of my databases .can i do selective tables recovery from backup in ZRM.I have been able to do database recovery but do tell me if there is selective table recovery option.Plz help.
Hopin a quick and positive reply from ur side.

paddy
May 5th, 2007, 07:45 PM
You can selective recovery of a database from a backup image containing backups of multiple databases.

See http://mysqlbackup.zmanda.com/index.php/Selective_Recovery#Restoring_One_or_More_Databases _from_a_Full_or_Incremental_Backup

Paddy

lovewadhwa
May 7th, 2007, 04:19 AM
hi
i can do selective database recovery but i need to have selective table recovery without extracting the database.

Sachin
May 7th, 2007, 09:00 PM
You can do selective table recovery and even select sql statement execution using bin-logs.

If you have backup with bin-logging enabled, sql statement level of granularity can be achieved.

Here is a small example for selective table recovery, for a level 1 backup with bin-logging enabled. In this example I have restored the table except for the last insert operation.

sachin:~ # mysql-zrm --action backup --mysql-binlog-path /var/lib/mysql/ --backup-level 1
backup:INFO: ZRM for MySQL - version built from source
BackupSet1:backup:INFO: backup-set=BackupSet1
BackupSet1:backup:INFO: backup-date=20070508092242
BackupSet1:backup:INFO: host=localhost
BackupSet1:backup:INFO: backup-date-epoch=1178596362
BackupSet1:backup:INFO: mysql-zrm-version=ZRM for MySQL - version built from source
BackupSet1:backup:INFO: mysql-version=5.0.26-log
BackupSet1:backup:INFO: backup-directory=/var/lib/mysql-zrm/BackupSet1/20070508092242
BackupSet1:backup:INFO: backup-level=1
BackupSet1:backup:INFO: incremental=mysql-bin.[0-9]*
BackupSet1:backup:INFO: next-binlog=mysql-bin.000005
BackupSet1:backup:INFO: last-backup=/var/lib/mysql-zrm/BackupSet1/20070508092042
BackupSet1:backup:INFO: /var/lib/mysql-zrm/BackupSet1/20070508092242/mysql-bin.000004=5b0a325a0978b5107ddf86e312081035
BackupSet1:backup:INFO: backup-size=0.00 MB
BackupSet1:backup:INFO: read-locks-time=00:00:00
BackupSet1:backup:INFO: flush-logs-time=00:00:00
BackupSet1:backup:INFO: backup-time=00:00:00
BackupSet1:backup:INFO: backup-status=Backup succeeded
BackupSet1:backup:INFO: Backup succeeded
sachin:~ #
sachin:~ # mysql -e "use test; drop table t5"
sachin:~ # mysqlshow test
Database: test
+--------+
| Tables |
+--------+
| t1 |
| t2 |
| t3 |
| t4 |
+--------+
sachin:~ # mysql-zrm --action parse-binlogs --source-directory /var/lib/mysql-zrm/BackupSet1/20070508092242/
parse-binlogs:INFO: ZRM for MySQL - version built from source
------------------------------------------------------------
Log filename | Log Position | Timestamp | Event Type | Event
------------------------------------------------------------
/var/lib/mysql-zrm/BackupSet1/20070508092242/mysql-bin.000004 | 4 | 07-05-08 09:20:42 | Start: binlog v 4, server v 5.0.26-log created 070508 9:20:42 |
/var/lib/mysql-zrm/BackupSet1/20070508092242/mysql-bin.000004 | 98 | 07-05-08 09:22:07 | Query | use test; /*!\C latin1 */; create table t5(id varchar(10));
/var/lib/mysql-zrm/BackupSet1/20070508092242/mysql-bin.000004 | 192 | 07-05-08 09:22:22 | Query | insert into t5(id) values ('90');
/var/lib/mysql-zrm/BackupSet1/20070508092242/mysql-bin.000004 | 287 | 07-05-08 09:22:24 | Query | insert into t5(id) values ('50');
/var/lib/mysql-zrm/BackupSet1/20070508092242/mysql-bin.000004 | 382 | 07-05-08 09:22:29 | Query | insert into t5(id) values ('55');
/var/lib/mysql-zrm/BackupSet1/20070508092242/mysql-bin.000004 | 477 | 07-05-08 09:22:42 | Rotate to mysql-bin.000005 pos: 4 |
------------------------------------------------------------
sachin:~ # mysql-zrm --action restore --start-position 98 --stop-position 382 --source-directory /var/lib/mysql-zrm/BackupSet1/20070508092242/
restore:INFO: ZRM for MySQL - version built from source
BackupSet1:restore:INFO: Incremental restore done
BackupSet1:restore:INFO: Restore done in 0 seconds.
sachin:~ #
sachin:~ # mysql -e "use test;select * from t5"
+------+
| id |
+------+
| 90 |
| 50 |
+------+
sachin:~ #

If binlogging is disabled, and if frequent table only restore is done, I suggest to you to have a backupset for each table, hence each table would be backed up separately and restored separately. Since in most cases, tables are related, it would be more beneficial to restore a database than just a table.