作者：xluren
mysql在默认安装的情况下是不打开mysqlbinlog的，
登录mysql执行以下命令结果如下所示：
mysql> show binlog events; Empty set (0.00 sec) mysql> 返回结果为空
所以要修改配置文件，打开这个选项
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin=mysqlbin-log 很简单，log-bin=mysqlbin-log ,名字自定义，至于这里有啥坑，目前还不知道，我一直用这个名字
然后执行show binlog events
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show binlog events; +---------------------+------+-------------+-----------+-------------+------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------------+------+-------------+-----------+-------------+------------------------------------------------------------------+ | mysqlbin-log.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.33-log, Binlog ver: 4 | | mysqlbin-log.000001 | 107 | Query | 1 | 174 | BEGIN | | mysqlbin-log.000001 | 174 | Intvar | 1 | 202 | INSERT_ID=3 | | mysqlbin-log.000001 | 202 | Query | 1 | 317 | use `url`; insert into urlinfo(url,info) values("sogou","hello") | | mysqlbin-log.000001 | 317 | Query | 1 | 385 | COMMIT | | mysqlbin-log.000001 | 385 | Query | 1 | 452 | BEGIN | | mysqlbin-log.000001 | 452 | Query | 1 | 544 | use `url`; delete from urlinfo where id=3 | | mysqlbin-log.000001 | 544 | Query | 1 | 612 | COMMIT | | mysqlbin-log.000001 | 612 | Query | 1 | 679 | BEGIN | | mysqlbin-log.000001 | 679 | Intvar | 1 | 707 | INSERT_ID=4 | | mysqlbin-log.000001 | 707 | Query | 1 | 822 | use `url`; insert into urlinfo(url,info) values("sogou","hello") | | mysqlbin-log.000001 | 822 | Query | 1 | 890 | COMMIT | | mysqlbin-log.000001 | 890 | Query | 1 | 957 | BEGIN |后面还有很多，省略了，可以看到精确的记录了我的操作记录
同时查看mysql数据所在的目录会增加了mysqlbin-log
[root@Git mysql]# ll total 28700 -rw-rw----. 1 mysql mysql 18874368 Dec 16 20:16 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Dec 16 20:16 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Aug 13 04:21 ib_logfile1 drwx------. 2 mysql mysql 4096 Aug 13 04:21 mysql -rw-rw---- 1 mysql mysql 2448 Dec 16 20:14 mysqlbin-log.000001 -rw-rw---- 1 mysql mysql 107 Dec 16 20:16 mysqlbin-log.000002 -rw-rw---- 1 mysql mysql 44 Dec 16 20:16 mysqlbin-log.index srwxrwxrwx 1 mysql mysql 0 Dec 16 20:16 mysql.sock drwx------ 2 mysql mysql 4096 Dec 6 06:09 mywebsite drwx------. 2 mysql mysql 4096 Aug 13 04:21 test drwx------. 2 mysql mysql 4096 Aug 13 04:35 url这里对比测试做的不是很好，其实mysqlbin-log.000001和mysqlbin-log.000002是新增加的，记录了操作日志，同时这里需要注意的是，每重启一次mysqld，mysqlbing-log会在原来的基础上+1，命名。
同时你还会发现，为什么 show binlog events 怎么没有mysqlbin-log.000002。OK执行下面的这条命令就会有了
摘自官方：
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] Shows the events in the binary log. If you do not specify 'log_name', the first binary log is displayed.
mysql> show binlog events in 'mysqlbin-log.000002'; +---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+ | mysqlbin-log.000002 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.33-log, Binlog ver: 4 | | mysqlbin-log.000002 | 107 | Query | 1 | 174 | BEGIN | | mysqlbin-log.000002 | 174 | Intvar | 1 | 202 | INSERT_ID=5 | | mysqlbin-log.000002 | 202 | Query | 1 | 318 | use `url`; insert into urlinfo(url,info) values("sogou1","hello") | | mysqlbin-log.000002 | 318 | Query | 1 | 386 | COMMIT | +---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+ 5 rows in set (0.00 sec) 下面进入数据恢复
数据库及时间如下所示：
mysql> select * from urlinfo; +----+----------------+-------+ | id | url | info | +----+----------------+-------+ | 1 | www.baidu.com | 0 | | 2 | www.google.com | 0 | | 4 | sogou | hello | | 5 | sogou1 | hello | +----+----------------+-------+ 4 rows in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2013-12-16 20:30:18 | +---------------------+ 1 row in set (0.00 sec) mysql> 插入一条数据
mysql> insert into urlinfo(url,info) values("soso","hello"); Query OK, 1 row affected (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2013-12-16 20:31:25 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from urlinfo; +----+----------------+-------+ | id | url | info | +----+----------------+-------+ | 1 | www.baidu.com | 0 | | 2 | www.google.com | 0 | | 4 | sogou | hello | | 5 | sogou1 | hello | | 6 | soso | hello | +----+----------------+-------+ 5 rows in set (0.00 sec) mysql> 然后把新插入的数据删除
mysql> delete from urlinfo where url='soso'; Query OK, 1 row affected (0.05 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ |2013-12-16 20:35:29 | +---------------------+ 1 row in set (0.01 sec) mysql> select * from urlinfo; +----+----------------+-------+ | id | url | info | +----+----------------+-------+ | 1 | www.baidu.com | 0 | | 2 | www.google.com | 0 | | 4 | sogou | hello | | 5 | sogou1 | hello | +----+----------------+-------+ 4 rows in set (0.00 sec) mysql>
操作记录查看
mysql> show binlog events in 'mysqlbin-log.000002'; +---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+ | mysqlbin-log.000002 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.33-log, Binlog ver: 4 | | mysqlbin-log.000002 | 107 | Query | 1 | 174 | BEGIN | | mysqlbin-log.000002 | 174 | Intvar | 1 | 202 | INSERT_ID=5 | | mysqlbin-log.000002 | 202 | Query | 1 | 318 | use `url`; insert into urlinfo(url,info) values("sogou1","hello") | | mysqlbin-log.000002 | 318 | Query | 1 | 386 | COMMIT | | mysqlbin-log.000002 | 386 | Query | 1 | 453 | BEGIN | | mysqlbin-log.000002 | 453 | Intvar | 1 | 481 | INSERT_ID=6 | | mysqlbin-log.000002 | 481 | Query | 1 | 595 | use `url`; insert into urlinfo(url,info) values("soso","hello") | | mysqlbin-log.000002 | 595 | Query | 1 | 663 | COMMIT | | mysqlbin-log.000002 | 663 | Query | 1 | 730 | BEGIN | | mysqlbin-log.000002 | 730 | Query | 1 | 828 | use `url`; delete from urlinfo where url='soso' | | mysqlbin-log.000002 | 828 | Query | 1 | 896 | COMMIT | +---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------+ 12 rows in set (0.00 sec) 然后把那条数据恢复起来，也就是说数据应该是2013-12-16 20:35:29之前的数据
执行下面这条命令：
[root@Git mysql]# mysqlbinlog --database=url --start-datetime="2013-12-16 20:29:00" --stop-datetime="2013-12-16 20:34:00" mysqlbin-log.000002|mysql -h localhost -u root -phello -f [root@Git mysql]# 然后
mysql> select * from urlinfo; +----+----------------+-------+ | id | url | info | +----+----------------+-------+ | 1 | www.baidu.com | 0 | | 2 | www.google.com | 0 | | 4 | sogou | hello | | 5 | sogou1 | hello | | 6 | soso | hello | +----+----------------+-------+ 5 rows in set (0.00 sec)删除的数据就出现了，我这是最最最简答的一个入门
关于mysqlbinlog的使用说明,可用的简单的命令参数：
mysqlbinlog支持下面的选项：
· ---help，-？
显示帮助消息并退出。
· ---database=db_name，-d db_name
使用该选项，如果mysqlbinlog读它不能识别的二进制日志事件，它会打印警告，忽略该事件并继续。没有该选项，如果mysqlbinlog读到此类事件则停止。
· --host=host_name，-h host_name
获取给定主机上的MySQL服务器的二进制日志。
· --password[=password]，-p[password]
当连接服务器时使用的密码。如果使用短选项形式(-p)，选项和 密码之间不能有空格。如果在命令行中--password或-p选项后面没有 密码值，则提示输入一个密码。
· --port=port_num，-P port_num
用于连接远程服务器的TCP/IP端口号。
--start-datetime=datetime
从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。该值格式应符合DATETIME或TIMESTAMP数据类型。例如：
shell> mysqlbinlog --start-datetime="2004-12-25 11:25:56" binlog.000003该选项可以帮助点对点恢复。
· --stop-datetime=datetime
从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。关于datetime值的描述参见--start-datetime选项。该选项可以帮助及时恢复
简单的入门了解，然后自己继续构造数据进行试验