mysql的binlog

　　binlog是简写，全称Binary Log，中文叫二进制日志。binlog是mysql用来记录DML（Data Manipulation Language）操作的，主要是INSERT、UPDATE、DELETE操作。它类似于oracle的redolog，以及redis的AOF模式下的增量命令。binlog是一件数据修复利器。如果你不小心delete了、drop了，用它可以很轻松的把误操作补救回来。

　　可以用命令看下mysql是否开启了binlog：

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.00 sec)

　　log_bin、sql_log_bin这个说明了binlog现在是打开的。log_bin_basename指定binlog所在目录（/var/lib/mysql）和日志文件前缀（binlog），log_bin_index指定binlog索引文件。如果当前的binlog没有开启，可以去mysql的配置文件（/etc/my.cnf）设置，vi打开后添加：

log_bin=ON
log_bin_basename=/var/lib/mysql/mysql-binlog
log_bin_index=/var/lib/mysql/mysql-binlog.index

　　重启mysql即可生效。我是5.7版本，my.cnf没有配置默认就开启了。

　　接下来我们可以看下当前的binlog位置（position）：

mysql> show master status;
+---------------+-----------+--------------+------------------+-------------------+
| File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+-----------+--------------+------------------+-------------------+
| binlog.000003 | 104926565 |              |                  |                   |
+---------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

　　我这里只有一个主节点，没有从节点，所以只需查master的状态即可。查出来是当前位置在binlog.000003这个日志文件中的104926565这个点。如果想更详细的看下binlog.000003文件里的105398225位置后面的操作：

mysql> show binlog events in 'binlog.000003' from 105398225;
+---------------+-----------+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos       | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+-----------+----------------+-----------+-------------+--------------------------------------+
| binlog.000003 | 105398225 | Anonymous_Gtid |         1 |   105398304 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000003 | 105398304 | Query          |         1 |   105398379 | BEGIN                                |
| binlog.000003 | 105398379 | Table_map      |         1 |   105398502 | table_id: 149 (test.t_mwsorder)      |
| binlog.000003 | 105398502 | Write_rows     |         1 |   105398785 | table_id: 149 flags: STMT_END_F      |
| binlog.000003 | 105398785 | Query          |         1 |   105398861 | COMMIT                               |
+---------------+-----------+----------------+-----------+-------------+--------------------------------------+
5 rows in set (0.00 sec)

　　这里的Event_type是事件类型，Query事件包含增删改，Table_map告诉我们操作的是哪张表，Write_rows告诉我们现在是insert，此外还有Delete_rows、Update_rows。我们再看后面几行：

mysql> show binlog events in 'binlog.000003' from 105398225 limit 20;
+---------------+-----------+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos       | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+-----------+----------------+-----------+-------------+--------------------------------------+
| binlog.000003 | 105398225 | Anonymous_Gtid |         1 |   105398304 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000003 | 105398304 | Query          |         1 |   105398379 | BEGIN                                |
| binlog.000003 | 105398379 | Table_map      |         1 |   105398502 | table_id: 149 (test.t_mwsorder)      |
| binlog.000003 | 105398502 | Write_rows     |         1 |   105398785 | table_id: 149 flags: STMT_END_F      |
| binlog.000003 | 105398785 | Query          |         1 |   105398861 | COMMIT                               |
| binlog.000003 | 105398861 | Anonymous_Gtid |         1 |   105398940 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000003 | 105398940 | Query          |         1 |   105399024 | BEGIN                                |
| binlog.000003 | 105399024 | Table_map      |         1 |   105399089 | table_id: 151 (test.t_task)          |
| binlog.000003 | 105399089 | Update_rows    |         1 |   105399211 | table_id: 151 flags: STMT_END_F      |
| binlog.000003 | 105399211 | Xid            |         1 |   105399242 | COMMIT /* xid=2116067 */             |
| binlog.000003 | 105399242 | Anonymous_Gtid |         1 |   105399321 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000003 | 105399321 | Query          |         1 |   105399396 | BEGIN                                |
| binlog.000003 | 105399396 | Table_map      |         1 |   105399519 | table_id: 149 (test.t_mwsorder)      |
| binlog.000003 | 105399519 | Write_rows     |         1 |   105399789 | table_id: 149 flags: STMT_END_F      |
| binlog.000003 | 105399789 | Query          |         1 |   105399865 | COMMIT                               |
| binlog.000003 | 105399865 | Anonymous_Gtid |         1 |   105399944 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000003 | 105399944 | Query          |         1 |   105400019 | BEGIN                                |
| binlog.000003 | 105400019 | Table_map      |         1 |   105400142 | table_id: 149 (test.t_mwsorder)      |
| binlog.000003 | 105400142 | Write_rows     |         1 |   105400414 | table_id: 149 flags: STMT_END_F      |
| binlog.000003 | 105400414 | Query          |         1 |   105400490 | COMMIT                               |
+---------------+-----------+----------------+-----------+-------------+--------------------------------------+
20 rows in set (0.00 sec)

　　如果不知道位置，那就全量查，慢慢找：

mysql> show binlog events in 'mysql-binlog.000053';