MySQL底层概述—2.InnoDB磁盘结构三

大纲

7.二进制日志(binlog)

8.新版本结构演变

7.二进制日志(binlog)

(1)binlog的基本概念

(2)binlog日志的三种模式

(3)如何选择binlog的模式

(4)binlog写入机制

(5)redo log和binlog的区别

(6)binlog命令操作

(1)binlog的基本概念

binlog是一个二进制格式的文件，用于记录用户对数据库更新操作。例如更改库和表的SQL语句都会记录到binlog里，但binlog不会记录select和show这类操作。

一.binlog的特点

特点一：binlog在MySQL的Server层实现(存储引擎共用)

特点二：binlog为逻辑日志，记录的是一条SQL语句的原始逻辑

特点三：binlog不限制大小，追加写入，不会覆盖以前的日志

特点四：默认情况下，binlog日志是二进制格式的。不能使用查看文本工具的命令查看，而要使用mysqlbinlog解析查看

二.开启binlog的场景

场景一：主从复制

在主库中开启binlog功能，这样主库就可以把binlog传递给从库，从库拿到binlog后实现数据恢复达到主从数据一致性。

场景二：数据恢复

通过mysqlbinlog工具来恢复数据。

(2)binlog日志的三种模式

一.ROW模式

日志中会记录每一行数据被修改的情况，然后在Slave端对相同的数据进行修改。

优点是可以清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。缺点是批量操作会产生大量日志，尤其是alter table会让日志暴涨。

二.STATMENT模式

日志中会记录每一条修改数据的SQL语句。注意批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句。Slave的SQL进程会解析成和原来Master执行过的相同的SQL再次执行，简称SQL语句复制。

优点是日志量小减少磁盘IO，提升存储和恢复速度。缺点是在某些情况下会导致主从数据不一致，如last_insert_id()、now()。

三.MIXED模式

以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog。MySQL会根据执行的SQL语句选择写入模式。

(3)如何选择binlog的模式

一.如果使用MySQL的特殊功能相对少(存储过程、触发器、函数)，可以选择默认的语句模式，Statement模式。

二.如果使用MySQL的特殊功能较多的，可以选择Mixed模式。

三.如果使用MySQL的特殊功能较多，又希望数据最大化一致，此时最好Row模式，但是要注意，该模式的binlog日志量增长非常快。

(4)binlog写入机制

一.binlog文件结构

binlog文件中记录的是对数据库的各种修改操作。binlog中用来表示修改操作的日志记录的数据结构是Log Event，不同的修改操作对应的不同的Log Event。

比较常用的Log Event有：Query Event、Row Event、Xid Event等，binlog文件的内容就是各种Log Event的集合。

二.binlog落盘策略

binlog写入顺序：Binlog Cache -> (write) OS Cache -> (fsync) Disk。其中，write表示写入操作系统缓存，fsync表示持久化到磁盘。

binlog刷盘由参数sync_binlog进行配置：

sync_binlog = 0，表示每次提交事务都只write，不fsync。

sync_binlog = 1，表示每次提交事务都会执行fsync。

sync_binlog = N，表示每次提交事务都write，累积N个事务后才fsync。

注意：不建议将sync_binlog参数设成0。比较常见的是将sync_binlog设置为100 ~ 1000中的某个数值。如果设置成0，主动重启丢失的数据不可控制。如果设置成1，效率低下。如果设置成N，则宕机最多造成N个事务的binlog丢失，可控且性能较高。

mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.00 sec)

三.binlog写入流程

步骤1：根据记录模式和操作触发Event事件生成Log Event。

步骤2：事务执行过程中，先把日志(Log Event)写到Binlog Cache；事务提交时，再把Binlog Cache写到binlog文件中。

步骤3：系统会为每个线程分配一块Binlog Cache内存，每个线程都有自己的Binlog Cache，共用一份binlog文件。

步骤4：事务提交时，会把Binlog Cache里完整的事务写入binlog文件中，然后清空Binlog Cache。

(5)redo log和binlog的区别

区别一：redo log是InnoDB引擎特有的，binlog是MySQL的Server层实现的，所有引擎都可以使用。

区别二：redo log是物理日志，记录的是"在XXX数据页上做了XXX修改"。binlog是逻辑日志，记录的是原始逻辑，其记录是对应的SQL语句。

区别三：redo log是循环写，空间一定会用完，需要WritePos和CheckPoint搭配；binlog是追加写，写到一定大小会切换到下一个，不会覆盖以前的日志。

区别四：redo log作为服务器异常宕机后事务数据自动恢复使用，binlog可以作为主从复制和数据恢复使用，binlog没有CrashSafe(崩溃恢复)能力。

什么是CrashSafe？CrashSafe指MySQL服务器宕机重启后，能够保证：已提交的事务的数据仍然存在 + 没提交的事务的数据自动回滚。

(6)binlog命令操作

一.启用binlog

$ vim /etc/my.cnf
增加下面几个参数
binlog-format=ROW
log-bin=mysqlbinlog
server-id=1

binlog-format：文件模式。

log-bin：MySQL会根据这个配置自动设置log_bin为on状态，自动设置log_bin_index文件为指定的文件名后跟.index。

server-id=1：随机指定一个不能和其他集群中机器重名的字符串，如果只有一台机器，那就可以随便指定了。

二.重启mysql

$ systemctl restart mysqld

三.启动成功后登陆查看配置是否起作用

mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| log_bin                         | ON                               |
| log_bin_basename                | /var/lib/mysql/mysqlbinlog       |
| log_bin_index                   | /var/lib/mysql/mysqlbinlog.index |
| log_bin_trust_function_creators | OFF                              |
| log_bin_use_v1_row_events       | OFF                              |
| sql_log_bin                     | ON                               |
+---------------------------------+----------------------------------+

log_bin：是否开启binlog日志；
log_bin_basename：基础文件名；
log_bin_index：binlog文件的索引文件，管理所有binlog文件；
sql_log_bin：表示当前会话是否记录binlog，默认值ON；
当sql_log_bin关闭后，主库上的改动不记录binlog，不会复制到从库；

四.查看binlog文件列表

mysql> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mysqlbinlog.000001 |       177 |
| mysqlbinlog.000002 |       177 |
| mysqlbinlog.000003 |       154 |
+--------------------+-----------+

五.查看正在写入的binlog文件

mysql> show master status;

六.查看binlog文件信息

使用show binlog events命令查询到的每一行数据就是一个binlog管理事件。

mysql> show binlog events;
+--------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name           | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+--------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysqlbinlog.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.30-log, Binlog ver: 4 |
| mysqlbinlog.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysqlbinlog.000001 | 154 | Stop           |         1 |         177 |                                       |
+--------------------+-----+----------------+-----------+-------------+-----------

Log_name：当前事件所在的binlog文件名称；
Pos：当前事件的开始位置，每个事件都占用固定的字节大小，结束位置End_log_position减去Pos，就是这个事件占用的字节数。第一个事件位置并不是从0开始，而是从4开始。MySQL通过文件中的前4个字节，来判断这是不是一个binlog文件，很多格式文件如pdf、doc等都会用前几个特定字符判断文件是否合法；
Event_type：表示事件的类型；
Server_id：表示产生这个事件的MySQL的server_id，my.cnf中配置；
End_log_position：下一个事件的开始位置；
Info：当前事件的描述信息；

七.查看指定binlog文件内容

mysql> show binlog events in 'mysqlbinlog.000001'\G;
*************************** 1. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.30-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
*************************** 3. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 154
 Event_type: Stop
  Server_id: 1
End_log_pos: 177
       Info: 
3 rows in set (0.00 sec)

八.使用mysqlbinlog命令查看binlog文件

[root@localhost mysql]# mysqlbinlog mysqlbinlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220125  6:40:35 server id 1  end_log_pos 123 CRC32 0x1e570724  Start: binlog v 4, server v 5.7.30-log created 220125  6:40:35 at startup
ROLLBACK/*!*/;
BINLOG '
4wvwYQ8BAAAAdwAAAHsAAAAAAAQANS43LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADjC/BhEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ASQHVx4=
'/*!*/;
# at 123
#220125  6:40:35 server id 1  end_log_pos 154 CRC32 0x43fa19f1  Previous-GTIDs
# [empty]
# at 154
#220125  6:41:17 server id 1  end_log_pos 177 CRC32 0x205de899  Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

输出指定position位置的binlog日志：

$ mysqlbinlog --start-position='154' --stop-position='177'

输出指定position位置的binlog日志到指定文件中：

$ mysqlbinlog --start-position='154' --stop-position='177' mysqlbinlog.000001 > test.log

输出指定position位置的binlog日志到压缩文件中：

$ mysqlbinlog --start-position="指定开始位置" --stop-position="指定结束位置" binlog文件|gzip > 压缩文件名

输出指定开始时间的binlog日志：

$ mysqlbinlog --start-datetime="yyyy-MM-dd HH:mm:ss" binlog文件

8.新版本结构演变

(1)MySQL 5.7版本

一.将undo日志表空间从共享表空间ibdata文件中分离出来，但需要在安装MySQL时就要指定文件大小和数量才能分离出来。

二.增加了temporary临时表空间，存储临时表或临时查询结果集的数据。

三.Buffer Pool大小可以动态修改，无需重启数据库实例。

(2)MySQL 8.x版本

一.将InnoDB表的数据字典和undo从共享表空间ibdata中彻底分离出来，以前需要ibdata中数据字典与独立表空间ibd文件中数据字典一致才行，8.x版本就不需要了。

二.temporary临时表空间也可以配置多个物理文件，而且均为InnoDB存储引擎并能创建索引，这样加快了处理的速度。

三.用户可以像Oracle数据库那样设置一些表空间，每个表空间对应多个物理文件，每个表空间可以给多个表使用，但一个表只能存储在一个表空间中。

四.将Double Write Buffer从共享表空间ibdata中也分离出来了。