Backing up and restoring a single table

Hi,

Sorry for my bad English.

I have a database system with one master and slave , about 1TB data ><
System not yet backup before.

Now , I want to use xtrabackup to backup single table .
I read Partial Backup but I have 4 problem as the following:

  1. Is it Ok if I run fullbackup with data 1TB?

  2. Is it nesscessary to run fullbackup or full database before backup a single table ( I guest no need but…)

  3. ALL of my table is innodb but when run backup , the message on screen alway show information ALL TABLE IS LOCKED AND FLUSHED TO DISK.
    it is ok?

  4. When I run as the introduction at "partial backup " with single table , on step IMPORT TABLESPACE, it alway occur error like that

140120 19:25:33 InnoDB: Error: tablespace id and flags in file ‘./test_restore/trn_users.ibd’ are 321 and 0, but in the InnoDB
InnoDB: data dictionary they are 327 and 0.

whats happening?

Please help me!!!

Thanks in advance.

  1. yes, it’s absolutely fine, i saw xtrabackup running without any problems over terabytes of dataset.
  2. No.
  3. Xtrabackup requires FLUSH TABLES WITH READ LOCK (FTWRL) to copy non-innodb tables and table structure files (.frm files). If all your tables are innodb and you don’t issue any DDL during course of problem you can avoid FTWRL by using –no-lock option to avoid tables being locked. As –no-lock describes use this option if you don’t care backup binary log position so backup doesn’t contains xtrabackup_binlog_info file if backup is created with –no-lock option because –no-lock prevents FTWRL which is required to get consistent positions for binary log. However, FTWRL is normally for short period of times if all your tables are InnoDB. You can read more about it here http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/improved_ftwrl.html
  4. It’s probably when copying non-innodb and tables structure files (.frm files) so it should be ok.
  5. What is source MySQL version from where you back up ? Can you please post your steps for partial backup and attach backup log too.

Thanks for your reply!! ^^

1> Could you estimate How long time neccessary to backup with 1tb ?
And I want excute with hot backup, my service can’t maintenance or something like that
I see that althought backup one table but the ibdata and ib_logfile data also copy to backup folder.

2>
Mysql source version
5.5.8-log
Mysql destination vesion
5.5.34-32.0-log

I copied data from Mysql source version to Mysql destination version and insert to database Test_Database_Backup.

Steps executed:

  1. Backup one table
    innobackupex --no-lock --include=’^test_database_backup[.]trn_users’ --no-timestamp /tmp/test_single_back

  2. Confirmed directory /tmp/test_single_back

3.Execute prepare step before restoring
innobackupex --apply-log --export /tmp/test_single_back

4.Confirm /tm/test_single/back/test_database_backup had file
trn_users.frm
trn_users.ibd
trn_users.exp
trn_users.cfg

  1. Log in to Mysql server ( the same server with TEST_DATABASE_BACKUP)

  2. Create a database to restore with name : TEST_RESTORE

7.create table trn_users with the same schema of Test_Database_Backup
CREATE TABLE trn_users (
id bigint(20) NOT NULL COMMENT ‘ユーザID’,
as_id varchar(255) DEFAULT NULL,
user_name varchar(100) NOT NULL COMMENT ‘ユーザ名’,
user_status tinyint(4) NOT NULL DEFAULT ‘2’ COMMENT ‘ユーザ状態 : 1:チュートリアル完了\n2:チュートリアル未完了\n3:不正アクセス’,
user_category tinyint(4) DEFAULT NULL COMMENT ‘ユーザ属性’,
tutorial_num varchar(50) DEFAULT NULL COMMENT ‘チュートリアル済番号’,
tutorial_finish_datetime datetime DEFAULT NULL COMMENT ‘チュートリアル完了日時’,
last_login_datetime datetime DEFAULT NULL COMMENT ‘最終ログイン日時’,
continue_login_day smallint(6) DEFAULT NULL COMMENT ‘連続日数’,
af varchar(255) DEFAULT NULL,
create_datetime datetime DEFAULT NULL COMMENT ‘作成日時’,
PRIMARY KEY (id),
KEY condition1 (last_login_datetime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘ユーザテーブル’;

  1. Discard tablespace of trn_users;
    ALTER TABLE TEST_RESTORE.trn_users DISCARD TABLESPACE;

  2. Copy trn_users.ibd.exp FROM /tm/test_single/back/test_database_backup to /var/lib/mysql/test_restore
    and change owner to mysql user

10.Import Tablespace
ALTER TABLE TEST_RESTORE.trn_users IMPORT TABLESPACE;

  1. [ Got error -1 from storage engine] occured

  2. Confirm error log file
    140121 11:38:22 InnoDB: Error: tablespace id and flags in file ‘./test_restore/trn_users.ibd’ are 321 and 0, but in the InnoDB
    InnoDB: data dictionary they are 329 and 0.
    InnoDB: Have you moved InnoDB .ibd files around without using the
    InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
    InnoDB: Please refer to
    InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
    InnoDB: for how to resolve the issue.
    140121 11:38:22 InnoDB: cannot find or open in the database directory the .ibd file of
    InnoDB: table test_restore.trn_users
    InnoDB: in ALTER TABLE … IMPORT TABLESPACE

Thanks so much!!!

I so urgent , please help me.

I edit ibd file with hex editor, but It also error occured…
When I excute Alter table, below error :
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…

ibdata1 is shared tablespace and it contains data dictionary information, undo information etc and it should be copied during backup regardless it’s full backup or partial backup. For partial backup the destination server should be percona server or at minimum it works for Oracle MySQL 5.6 and i can see your destination server is 5.5.34-32.0-log is that percona server or stock oracle mysql ?
Further on destination server (percona server) innodb_import_table_from_xtrabackup should be enabled prior to table import. Please check here for details http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/partial_backups_innobackupex.html and http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/restoring_individual_tables_ibk.html

Also, you may want to check this thread about it http://www.percona.com/forums/questions-discussions/percona-xtrabackup/8315-restore-one-table-from-xtrabackup-s-full-backup

This is my destination server information
Server version: 5.5.34-32.0-log Percona Server (GPL),

[COLOR=#252C2F]Thank you very much Mirfan. After add innodb_import_table_from_xtrabackup[COLOR=#252C2F] = 1 to my.cnf , restore worked!!!

Thanks for your support!!!

Glad to hear that :slight_smile: