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  \
--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: 0

mysql> 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 end
end_lsn=139571560




--4.4 合并增量备份到全备

ls /backupinc/
2015-04-02_12-44-02  2015-04-02_12-46-48

ls /backup

2015-04-02_12-41-45


mysqlbackup --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 /