1、如何开启mysql-binlog：

my.ini文件添加：

[mysqld]
log-bin="C:\ProgramData\MySQL\MySQL Server 5.1\data\mysql-bin.log"

执行命令：

C:\ProgramData\MySQL\MySQL Server 5.1\data>mysqladmin  flush-logs -u root -p

重启mysql即可

2、查看mysql-binlog文件：

切换到二进制目录文件下：
cd C:\ProgramData\MySQL\MySQL Server 5.1\data

查看二进制：

C:\ProgramData\MySQL\MySQL Server 5.1\data>mysqlbinlog mysql-bin.000001 -d test -u root -p
Enter password: *****
;
;
DELIMITER ;
# at 4
#131020 21:51:56 server id 1  end_log_pos 106   Start: binlog v 4, server v 5.1.28-rc-community-log created 131020 21:51:56 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK;
BINLOG '
/N9jUg8BAAAAZgAAAGoAAAABAAQANS4xLjI4LXJjLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD832NSEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
';
# at 106
#131020 21:57:12 server id 1  end_log_pos 207   Query   thread_id=1     exec_time=0     error_code=0
use test;
SET TIMESTAMP=1382277432;
SET @@session.pseudo_thread_id=1;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=1344274432;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8;
SET @@session.lc_time_names=0;
SET @@session.collation_database=DEFAULT;
insert into test01 values(19,"daskfj")
;
DELIMITER ;
# End of log file
ROLLBACK ;
">COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

C:\ProgramData\MySQL\MySQL Server 5.1\data>mysqlbinlog mysql-bin.000001 -d test -u root -p
Enter password: *****
;
;
DELIMITER ;
# at 4
#131020 21:51:56 server id 1  end_log_pos 106   Start: binlog v 4, server v 5.1.28-rc-community-log created 131020 21:51:56 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK;
BINLOG '
/N9jUg8BAAAAZgAAAGoAAAABAAQANS4xLjI4LXJjLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD832NSEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
';
# at 106
#131020 21:57:12 server id 1  end_log_pos 207   Query   thread_id=1     exec_time=0     error_code=0
use test;
SET TIMESTAMP=1382277432;
SET @@session.pseudo_thread_id=1;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=1344274432;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8;
SET @@session.lc_time_names=0;
SET @@session.collation_database=DEFAULT;
insert into test01 values(19,"daskfj")
;
DELIMITER ;
# End of log file
ROLLBACK ;
">COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

3、查看主日志文件状态：

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       207 |
+------------------+-----------+
1 row in set (0.00 sec)

4、查看二进制日志文件：

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       250 |
| mysql-bin.000002 |       106 |
+------------------+-----------+
2 rows in set (0.02 sec)

查看二进制log的状态： 

mysql> show binlog events\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 106
       Info: Server ver: 5.1.28-rc-community-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 106
 Event_type: Query
  Server_id: 1
End_log_pos: 207
       Info: use `test`; insert into test01 values(19,"daskfj")
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
        Pos: 207
 Event_type: Rotate
  Server_id: 1
End_log_pos: 250
       Info: mysql-bin.000002;pos=4
3 rows in set (0.00 sec)

ERROR:
No query specified

 

5、查看当前使用的二进制日志：

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.06 sec)

 

6、清除二进制日志方法：

切换日志：

mysql >flush logs; 

Query OK, 0 rows affected (0.39 sec)


mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |       150 |

| mysql-bin.000002 |       107 |

+------------------+-----------+

2 rows in set (0.00 sec)


mysql >reset master;  ------清楚所有的二进制文件，相当于重置，以前的日志删除。

Query OK, 0 rows affected (0.38 sec)


mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000001 |       107 |

+------------------+-----------+

1 row in set (0.00 sec)


7、通过bin-log恢复数据：

按照恢复点恢复数据：

C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\bin-log>mysqlbinlog --stop-position=749 mysql-bin.000002 | mysql -uroot -p

Enter password:

按照时间点恢复数据：

C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\bin-log>mysqlbinlog --stop-datetime="2013-11-24 12:44:29" mysql-bin.000002 | mysql -uroot -p

Enter password:

8、 binlog的删除

binlog的删除可以手工删除或自动删除

自动删除binlog
通过binlog参数（expire_logs_days ）来实现mysql自动删除binlog
mysql> show binary logs;
mysql> show variables like 'expire_logs_days';
mysql> set global expire_logs_days=3;

手工删除binlog
mysql> reset master;   //删除master的binlog
mysql> reset slave;    //删除slave的中继日志
mysql> purge master logs before '2012-03-30 17:20:00';  //删除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000002';   //删除指定日志文件的日志索引中binlog日志文件
或者直接用操作系统命令直接删除