mysql使用mysqlbinlog 恢复数据库

1.查看当前日志文件情况：
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+-------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                      |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         106 | Server ver: 5.1.73-log, Binlog ver: 4     |
| mysql-bin.000001 | 106 | Query       |         1 |         198 | use `test`; insert into ran values(1,'a') |
| mysql-bin.000001 | 198 | Query       |         1 |         290 | use `test`; insert into ran values(1,'a') |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------+
3 rows in set (0.00 sec)

2.导出日志文件：
[root@mail mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000001  > /tmp/log01.txt

追加导出：
[root@mail mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000002  >> /tmp/log01.txt 

按指定位置导出：
mysqlbinlog /var/lib/mysql/mysql-bin.000001  --start-position=4 --stop-position=106 > /tmp/log01.txt
mysqlbinlog /var/lib/mysql/mysql-bin.000001  --start-position=4  > /tmp/log01.txt 从4的位置开始导出
mysqlbinlog /var/lib/mysql/mysql-bin.000001  --stop-position=106  > /tmp/log01.txt 导出位置198之前的

按照时间导出：
mysqlbinlog /var/lib/mysql/mysql-bin.000001  --start-datetime="2014-09-16 07:38:17" --stop-datetime="2014-09-16 07:38:18" > /tmp/log01.txt
导出可以用管道过滤

3.恢复数据库：
按日志文件恢复：
mysqlbinlog /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p
mysqlbinlog binlog.[0-9]* | mysql -u root -p

按位置恢复：
mysqlbinlog /var/lib/mysql/mysql-bin.000001 --start-position=4 --stop-position=106 | mysql -uroot -p

按时间恢复：
mysqlbinlog /var/lib/mysql/mysql-bin.000001  --start-datatime="2014-09-16 07:38:17" --stop-datatime="2014-09-16 07:38:18" | mysql -uroot -p

先导出再恢复：
shell> mysqlbinlog binlog.000001 >  /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
shell> mysql -u root -p -e "source /tmp/statements.sql"

具体用法man mysqlbinlog