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()BEGINDECLARE i int DEFAULT 0;set i=1 ;set autocommit=0; WHILE i<=1000000 DOINSERT INTO t1 values(i,'mysql测试');set i=i+1; END WHILE; commit;set autocommit=1;END //DELIMITER ;call proc1;create table t2asselect * from t1;–4.2.全备# rm -rf /backup && mkdir /backup#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 –backup-dir=/backup \–log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysql-bin.index \backup-and-apply-log# cat /backup/2015-04-02_12-41-45/meta/backup_variables.txt | grep 'end'end_lsn=138866623–4.3 增量备份mysql> 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 mysqlbackupprints "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 threads1 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 mysql61 Apr 2 13:26 db.opt-rw-rw—-. 1 mysql mysql8590 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)
，还要高声歌唱。那歌声，一定是响遏流云的，
