Caution

todo…

1 配置信息

my.cnf
server-id               = 1
log_bin                 = mysql-bin
+-----------------------------------------+---------------------+
| Variable_name               | Value                           |
+-----------------------------------------+---------------------+
| log_bin                     | ON                              |  -- 是否开启
| log_bin_basename            | /var/lib/mysql/mysql-bin        |
| log_bin_index               | /var/lib/mysql/mysql-bin.index  |
| max_binlog_size             | 104857600                       |  -- binlog文件的最大值,达到最大值,则重新生成一个新的
| binlog_format               | ROW                             |  -- 有三种 statement(记录sql语句), row(每一行数据) , mixed
+-----------------------------------------+---------------------+
show master status;
执行结果
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000001 |      154 |
+------------------+----------+

我们发现 mysql-bin.000001的文件大小 154 字节数就是上面的position

ll mysql-bin*
-rw-r----- 1 mysql mysql 154 Sep  3 14:36 mysql-bin.000001
-rw-r----- 1 mysql mysql  19 Sep  3 14:36 mysql-bin.index
查看binlog更具体的内容
show binlog events in 'mysql-bin.000001'\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.17-0ubuntu0.16.04.1-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info:
刷新日志,会重新生成一个二进制文件
flush binary logs;
show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000002 |      154 |
+------------------+----------+

2 binlog_format

format description
statement 记录操作的sql文
不支持不确定的sql语句,比如update .... limit 3
row 记录操作的每一行数据,每张表一定要有主键
mixed

3 查看binlog文件里的sql语句

#这个可以看到具体的sql文件
mysqlbinlog mysql-bin.000001 -vv
#-----------------------------------------------------
### INSERT INTO `test`.`mm`
### SET
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2=5 /* INT meta=0 nullable=1 is_null=0 */
#-----------------------------------------------------
@1 表示第一列
@2 表示第二列
mysqlbinlog mysql-bin.000001 --base64-output=decode-row -v
help show binlog events;
Back to top