Mysql知识点整理Binlog

共计 4943 个字符，预计需要花费 13 分钟才能阅读完成。

	redolog	binlog
日志作用	保护脏数据	数据库备份恢复使用
引擎支持	只适合 InnoDB 引擎	所有引擎
日志格式	物理日志	逻辑日志,SQL 语句
提交方式	快速提交	提交时一次性写入
保存形式	会被循环覆盖	长期保存

redolog 记录的是对于每个页的修改
binlog 只是记录 DML、DDL、DCL，不记录 SELECT

修改配置文件 /etc/my.cnf，在 [mysqld] 下添加：

log-bin = …/mydata5470/mysql-bin.log

mysql> show variables like 'log_bin%';
+---------------------------------+--------------------------------------------------------+
| Variable_name                   | Value                                                            |
+---------------------------------+--------------------------------------------------------+
| log_bin                         | ON                                                               
| log_bin_basename                | .../mydata5470/mysql-bin            
| log_bin_index                   | .../mydata5470/master-log-bin.index 
| log_bin_trust_function_creators | ON                                                               
| log_bin_use_v1_row_events       | OFF                                                              
+---------------------------------+--------------------------------------------------------+

1、重启数据库，每次重启都会新切 binlog
2、mysql> flush logs;

mysqlbinlog -vv mysql-bin.000005 | more

show variables like 'binlog_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+

所有未提交的二进制日志文件会被记录到用户工作空间的 binlog cache 中，等该事务提交时直接将缓冲区中的 binlog 写入二进制日志文件里。

show global status like 'Binlog_cache_disk_use';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 10    |
+-----------------------+-------+

判断 binlog_cache_size 是否设置过小的依据，如果 Binlog_cache_disk_use>0(次数)，说明事务未提交，binlog 在用户工作空间存放不下，需要借用 tmp 目录。

–log-bin [=file_name]：设置此参数表示启用 binlog 功能，并指定路径名称，生产中都要开启 binlog。

重要：会话关闭

sql_log_bin：会话级别的 binlog 开关控制，默认是开启的，可以在当前会话级别动态修改临时关闭 binlog（主从延迟优化），set session sql_log_bin=0；注意：SESSION 级别的只会当前会话不记录 binlog

重要：全局关闭

set global sql_log_bin=0：所有会话都会生效，但是当前所有已经连接上的会话不会生效。再次连接就生效了。

// 同步 binlog 的方式

0：默认，提交同步到文件系统缓存

1：commit，通过 fsync 方式，直接写入 disk 的 binlog 文件中(最安全)，与 redo 的双一模式。>1：sync_binlog=N，如果 N >1，在意外发生的时候，就表示会有 N - 1 个 dml 没有被写入 binlog 中，有可能就会发生主从数据不一致的情况。

//binlog 文件大小，默认 1G

如果是 row 模式，需要增加 binlog 文件的大小，因为行模式产生的日志量相对较大。如果超过了该值，就会产生新的日志文件，后缀名 +1，并且记录到.index 文件里面。

//row、statement、mixed，设置 binlog 记录的模式：行模式、语句模式、mixed 模式。动态参数，可以会话级别修改

表示需要写入或者忽略写入哪些库的日志，默认为空，表示可以将所有库的日志写入到二进制文件里面。

启用从机服务器上的 slave 日志功能，使这台计算机可以用来构成一个镜像链(A->B->C)，可以让从库上面产生二进制日志文件，在从库上再挂载一个从库

便于定位执行过的 SQL 语句

binlog 过期清理时间，默认是 0：不自动清除

binlog 的删除策略，假设 expire_logs_days=5，表示系统保留 5 天 binlog，第六天到来时会删除

1、STATEMENT

  每一条会修改数据的 sql 都会记录在 binlog 中。优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO，提高性能。只需要记录在 master 上所执行的语句的细节，以及执行语句时候的上下文的信息。缺点：由于记录的只是执行语句，为了这些语句能在 slave 上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在 slave 得到和在 master 端执行时候相同的结果。像一些特定函数功能，slave 可与 master 上要保持一致会有很多相关问题 (如 sleep() 函数，rand()函数等会出现问题 warning)。2、ROW

  不记录 sql 语句上下文相关信息，仅保存哪条记录被修改，也就是说日志中会记录成每一行数据被修改的形式，然后在 slave 端再对相同的数据进行修改。优点：binlog 中可以不记录执行的 sql 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以 rowlevel 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或 function，以及 trigger 的调用和触发无法被正确复制的问题。缺点：在 row 模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。3、MIXED

  是以上两种 level 的混合使用，一般的语句修改使用 statment 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存 binlog，MySQL 会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式，也就是在 Statement 和 Row 之间选择一种；新版本的 MySQL 中对 row 模式也被做了优化，并不是所有的修改都会以 rowl 来记录，像遇到表结构变更的时候就会以 statement 模式来记录。至于 update 或者 delete 等修改数据的语句，还是会记录所有行的变更。

二进制日志两个最重要的使用场景
- MySQL replication 在 master 端开启 binlog，master 把它的二进制日志传递给 slaves 来达到 master-slave 数据一致的目的，也就是主从备份。
- 数据恢复，通过使用 mysqlbinlog 工具来使恢复数据
常用 binlog 日志操作命令
- 查看所有 binlog 日志列表mysql> show master logs;
- 查看 master 状态，即最后 (最新) 一个 binlog 日志的编号名称，及其最后一个操作事件 pos 结束点 (Position) 值mysql> show master status; // 结合上述 binlog 文件，进行 binlog 生成速度监控
- 刷新 log 日志，自此刻开始产生一个新编号的 binlog 日志文件mysql> flush logs; // 注：每当 mysqld 服务重启时，会自动执行此命令，刷新 binlog 日志；在 mysqldump 备份数据时加 -F 选项也会刷新 binlog 日志；
- 重置 (清空) 所有 binlog 日志mysql> reset master;
查看 binlog 日志内容
- OS 层面查看 binlog 文件shell> mysqlbinlog -vv mysql-bin.000001
- 数据库层面 show binlog events
  mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
  
  选项解析：

1、in ‘log_name’：指定要查询的 binlog 文件名(不指定就是第一个 binlog 文件)

2、from pos：指定从哪个 pos 起始点开始查起(不指定就是从整个文件首个 pos 点开始算)

3、limit [offset,]：偏移量(不指定就是 0)

4、row_count：查询总条数(不指定就是所有行)

从 binlog 日志恢复语法

1、恢复语法格式：# mysqlbinlog [选项] mysql-bin.0000xx | mysql - u 用户名 - p 密码 数据库名

  2、常用选项：--start-position=953 起始 pos 点

    --stop-position=1437 结束 pos 点

    --start-datetime="2017-6-8 13:18:54" 起始时间点

    --stop-datetime="2017-6-8 13:21:53" 结束时间点

    --database=TEST 指定只恢复 TEST 数据库(一台主机上往往有多个数据库，只限本地 log 日志)

  3、不常用选项：-u --user=name #Connect to the remote server as username. 连接到远程主机的用户名

    -p --password[=name] #Password to connect to remote server. 连接到远程主机的密码

    -h --host=name #Get the binlog from server. 从远程主机上获取 binlog 日志

    --read-from-remote-server #Read binary logs from a MySQL server. 从某个 MySQL 服务器上读取 binlog 日志

使用小结：实际是将读出的 binlog 日志内容，通过管道符传递给 mysql 命令。这些命令、文件尽量写成绝对路径；所谓恢复，就是让 MySQL 将保存在 binlog 日志中指定段落区间的 SQL 语句逐个重新执行一次而已。

关于双一模式

1、redolog、binlog：双 1(绝对安全)

1、innodb_flush_log_at_trx_commit=1

2、sync_binlog=1

2、innodb_support_xa=1：分布式事务，默认开启 ON

1、保证 binlog 里面存在的事务一定在 redo log 里面存在

2、保证 binlog 里面事务顺序与 redo log 事务顺序一致性

3、commit，要么成功要么失败，防止出现主从不一致

所以在双一的情况下，也要配合开启 innodb_support_xa，更安全。

1 REDOLOG / BINLOG

2 BINLOG 参数

2.1 开启 binlog

2.2 存储 binlog

2.3 手工切换 binlog

2.4 查看 binlog 日志文件

2.5 binlog_cache_size

2.6 临时关闭 BINLOG

2.7 sync_binlog

2.8 max_binlog_size

2.9 binlog_format

2.10 –binlog-do-db、–binlog-ingore-db

2.11 log-slave-updates

2.12 binlog_rows_query_log_events

2.13 expire_logs_days

3 binlog_format

4 使用场景

Just My Socks（注册教程内含优惠码）