mysql backup table_mysqlbackup恢复特定表

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<=1000000 DO

INSERT INTO t1 values(i,'mysql测试');

set i=i+1;

END WHILE;

commit;

set autocommit=1;

END

//

DELIMITER ;

call proc1;

create table t2

as

select * 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 \