Results 1 to 4 of 4

Thread: table recovery

  1. #1

    Default table recovery

    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.

  2. #2
    Join Date
    Oct 2005
    Posts
    1,559

    Default

    You can selective recovery of a database from a backup image containing backups of multiple databases.

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

    Paddy

  3. #3

    Default

    hi
    i can do selective database recovery but i need to have selective table recovery without extracting the database.

  4. #4
    Join Date
    Apr 2006
    Posts
    29

    Default

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •