mysqlbackup使用tts恢复指定表. *************************************************************4.恢复特定表*************************************************************--4.1新建测试环境create database `wind` default character set gbk ;use wind;create table t1(sid int not null ,sname varchar(100) not null)engine=innodb charset=gbk ; delimiter //create procedure proc1()begin declare i int default 0; set i=1 ;set autocommit=0; while i select count(*) from t1;+----------+| count(*) |+----------+| 1000000 |+----------+1 row in set (0.75 sec)mysql> select count(*) from t2;+----------+| count(*) |+----------+| 1000000 |+----------+1 row in set (0.68 sec)mysql> delete from t2 limit 10;query ok, 10 rows affected (0.00 sec)mysql> commit;query ok, 0 rows affected (0.00 sec)mysql> select count(*) from t2;+----------+| count(*) |+----------+| 999990 |+----------+1 row in set (0.80 sec)# rm -rf /backupinc && mkdir /backupinc--第一次增量备份#mysqlbackup --defaults-file=/usr/local/mysql/my.cnf \--host=127.0.0.1 --port=3306 --protocol=tcp \--user=root --password=123 --use-tts --include-tables='wind.t2' \--with-timestamp --log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index \--incremental --start-lsn=138866623 \--incremental-backup-dir=/backupinc backup # cat /backupinc/2015-04-02_12-44-02/meta/backup_variables.txt | grep 'end' end_lsn=138868639--第二次增量备份mysql> select count(*) from wind.t2;+----------+| count(*) |+----------+| 999990 |+----------+1 row in set (0.83 sec)mysql> desc t2 -> ;+-------+--------------+------+-----+---------+-------+| field | type | null | key | default | extra |+-------+--------------+------+-----+---------+-------+| sid | int(11) | no | | null | || sname | varchar(100) | no | | null | |+-------+--------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> update t2 set sname='ocpyang mysql test!' limit 5000;query ok, 5000 rows affected (0.24 sec)rows matched: 5000 changed: 5000 warnings: 0mysql> commit;query ok, 0 rows affected (0.00 sec)mysql> select count(*) from wind.t2 where sname='ocpyang mysql test!';+----------+| count(*) |+----------+| 5000 |+----------+1 row in set (0.86 sec)#mysqlbackup --defaults-file=/usr/local/mysql/my.cnf \--host=127.0.0.1 --port=3306 --protocol=tcp \--user=root --password=123 --use-tts --include-tables=wind.t2 \--with-timestamp --log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index \--incremental --start-lsn=138868639 \--incremental-backup-dir=/backupinc backup # cat /backupinc/2015-04-02_12-46-48/meta/backup_variables.txt | grep endend_lsn=139571560--4.4 合并增量备份到全备ls /backupinc/2015-04-02_12-44-02 2015-04-02_12-46-48ls /backup2015-04-02_12-41-45mysqlbackup --backup-dir=/backup/2015-04-02_12-41-45/ \--incremental-backup-dir=/backupinc/2015-04-02_12-44-02 \--log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index \apply-incremental-backup mysqlbackup --backup-dir=/backup/2015-04-02_12-41-45 \--incremental-backup-dir=/backupinc/2015-04-02_12-46-48 \--log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index \apply-incremental-backup --4.5 模拟删除指定表#cat /usr/local/mysql/my.cnf |grep datadirdatadir=/usr/local/mysql/datamysql> select count(*) from t2;+----------+| count(*) |+----------+| 999990 |+----------+1 row in set (0.80 sec)mysql> select count(*) from wind.t2 where sname='ocpyang mysql test!';+----------+| count(*) |+----------+| 5000 |+----------+1 row in set (0.86 sec)mysql> drop table t2;--4.6 还原指定表(使用mysql用户)chown -r mysql /backupchgrp -r mysql /backupchown -r mysql /usr/local/mysqlchgrp -r mysql /usr/local/mysql[mysql@mysql ~]$ whoami #避免权限问题mysql/***********************note:权限问题出现的错误 150402 13:29:26 mysqlbackup: info: importing table: wind.t2. mysqlbackup: error: mysql query: 'alter table wind.t2 import tablespace': internal error: cannot reset lsns in table 'wind.t2' : tablespace not found mysqlbackup: error: failed to import tablespace wind.t2.mysqlbackup failed with errors!*************************************************/mysqlbackup --host=127.0.0.1 --port=3306 --protocol=tcp \--user=root --password=123 \--datadir=/usr/local/mysql/data/ \--log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index \--backup-dir=/backup/2015-04-02_12-41-45/ \--include-tables='wind\.t2$' \copy-back[mysql@mysql ~]$ mysqlbackup --host=127.0.0.1 --port=3306 --protocol=tcp \> --user=root --password=123 \> --datadir=/usr/local/mysql/data/ \> --log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index \> --backup-dir=/backup/2015-04-02_12-41-45/ \> --include-tables='wind\.t2$' \> copy-backmysql enterprise backup version 3.12.0 linux-2.6.18-194.el5-x86_64 [2015/03/10] copyright (c) 2003, 2015, oracle and/or its affiliates. all rights reserved. mysqlbackup: info: starting with following command line ... mysqlbackup --host=127.0.0.1 --port=3306 --protocol=tcp --user=root --password=xxx --datadir=/usr/local/mysql/data/ --log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index --backup-dir=/backup/2015-04-02_12-41-45/ --include-tables=wind\.t2$ copy-back mysqlbackup: info: important: please check that mysqlbackup run completes successfully. at the end of a successful 'copy-back' run mysqlbackup prints mysqlbackup completed ok!.150402 13:38:25 mysqlbackup: info: meb logfile created at /backup/2015-04-02_12-41-45/meta/meb_2015-04-02.13-38-25_copy_back.log mysqlbackup: info: mysql server version is '5.6.23-enterprise-commercial-advanced-log'. mysqlbackup: info: got some server configuration information from running server.-------------------------------------------------------------------- server repository options:-------------------------------------------------------------------- datadir = /usr/local/mysql/data/ innodb_data_home_dir = /usr/local/mysql/innodb_data innodb_data_file_path = ibdata1:800m;ibdata2:800m:autoextend innodb_log_group_home_dir = /usr/local/mysql/mysql_logs/innodb_log/ innodb_log_files_in_group = 3 innodb_log_file_size = 2147483648 innodb_page_size = 16384 innodb_checksum_algorithm = innodb innodb_undo_directory = . innodb_undo_tablespaces = 0 innodb_undo_logs = 128-------------------------------------------------------------------- backup config options:-------------------------------------------------------------------- datadir = /backup/2015-04-02_12-41-45/datadir innodb_data_home_dir = /backup/2015-04-02_12-41-45/datadir innodb_data_file_path = ibdata1:800m;ibdata2:800m:autoextend innodb_log_group_home_dir = /backup/2015-04-02_12-41-45/datadir innodb_log_files_in_group = 3 innodb_log_file_size = 2147483648 innodb_page_size = 16384 innodb_checksum_algorithm = innodb mysqlbackup: info: creating 14 buffers each of size 16777216.150402 13:38:25 mysqlbackup: info: copy-back operation starts with following threads 1 read-threads 1 write-threads mysqlbackup: info: could not find binlog index file. binlogs will not be copied for this backup. point-in-time-recovery will not be possible. if this is online backup then server may not have started with --log-bin. you may specify its location with --log-bin-index option.150402 13:38:25 mysqlbackup: info: creating table: wind.t2.150402 13:38:25 mysqlbackup: info: copying /backup/2015-04-02_12-41-45/datadir/wind/t2.ibd.150402 13:38:26 mysqlbackup: info: completing the copy of all non-innodb files.150402 13:38:27 mysqlbackup: info: importing table: wind.t2.150402 13:38:28 mysqlbackup: info: analyzing table: wind.t2.150402 13:38:29 mysqlbackup: info: copy-back operation completed successfully.150402 13:38:29 mysqlbackup: info: finished copying backup files to '/usr/local/mysql/data'mysqlbackup completed ok!# ls -ll /usr/local/mysql/data/wind/total 57360-rw-rw----. 1 mysql mysql 61 apr 2 13:26 db.opt-rw-rw----. 1 mysql mysql 8590 apr 2 13:38 t2.frm-rw-rw-r--. 1 mysql mysql 58720256 apr 2 13:38 t2.ibd/*******alter table t2 discard tablespace;alter table t2 import tablespace;*********/ --4.7验证表恢复情况mysql> use wind;database changedmysql> show tables;+----------------+| tables_in_wind |+----------------+| t2 |+----------------+1 row in set (0.00 sec)mysql> select count(*) from t2;+----------+| count(*) |+----------+| 999990 |+----------+1 row in set (1.34 sec)mysql> select count(*) from wind.t2 where sname='ocpyang mysql test!';+----------+| count(*) |+----------+| 5000 |+----------+1 row in set (0.78 sec)