利用binlog实现备份-恢复

1.前提条件

• 一个库（如：dbtest）
• 一个表（表里有上百条数据，如：dbtable）

2.查看日志

mysql>show binlog events in 'mysql-bin.000001';

或

mysqlbinlog mysql-bin.000001

3.备份

[root@localhost data]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 > /dumpdb/full/mysql-bin.000001.sql

删除几条插入的数据，刷新日志，在创建几条数据。查看如下

mysql> select * from dbtest.dbtable;
+------+-------+------+
| id   | name  | sex  |
+------+-------+------+
|    1 | lisi  | 20   |
|    3 | lisi3 | 30   |
+------+-------+------+
2 rows in set (0.00 sec)

[root@localhost data]# mysqlbinlog --stop-position=287 /usr/local/mysql/data/mysql-bin.000002 > /dumpdb/287.sql
[root@localhost data]# mysqlbinlog --start-position=425 /usr/local/mysql/data/mysql-bin.000002 > /dumpdb/425.sql

mysql> drop database dbtest;
Query OK, 1 row affected (0.11 sec)
 

4.恢复

[root@localhost data]# mysql -u root -p123.com < /dumpdb/full/mysql-bin.000001.sql 
[root@localhost data]# mysql -u root -p123.com < /dumpdb/287.sql 
[root@localhost data]# mysql -u root -p123.com < /dumpdb/425.sql 

mysql> select * from dbtest.dbtable;
+------+-------+------+
| id   | name  | sex  |
+------+-------+------+
|    1 | lisi  | 20   |
|    2 | lisi2 | 20   |
|    3 | lisi3 | 30   |
+------+-------+------+
3 rows in set (0.00 sec)
恭喜你成功了！