MySql日志&二进制日志binlog

MySQL服务层日志

二进制日志: 记录更改数据的语句
慢查询日志:记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询

　　MySQL默认不开启慢查询日志

　　慢查询相关的参数 log_queries_not_using_indexes：如果SQL没有使用索引，则会被记录到慢日志查询中。

通用日志 :记录建立的客户端连接和执行的语句
中继(relay)日志:从节点复制主服务器接收的数据更改
DDL日志（元数据日志）：元数据操作由DDL语句执行
错误日志：错误日志记录了MySQL的启动、运行、关闭过程进行了记录。方便定位问题，如果mysql起不来，首先就应该去这个日志文件来看。

　　binlog都记录哪些内容？

　　binlog主要记录了所有对MySQL数据库的修改事件，包括增删改事件以及对表结构的修改事件，不包括 select 和 show 之类的操作（这部分会记到查询日志中）。注意的一点：只有成功执行的才回被记录到binlog中，那些执行出错或者已经回滚的数据，是不会被记录到binlog中的。

　　binlog 的主要目的是主从复制和数据恢复

在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
数据恢复：通过使用 mysqlbinlog工具来使恢复数据

　　什么时候写binlog？

　　InnoDB （支持事务的存储引擎），必须要提交了事务才会记录binlog。binlog 什么时候刷新到磁盘取决于参数 sync_binlog

如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；
如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。

　　如果 sync_binlog=0 或 sync_binlog大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。

　　建议设置为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。

　 binlog日志包括两类文件

二进制日志索引文件（文件名后缀为.index）用于记录所有有效的的二进制文件
二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML语句事件

　　何时会生成新的binlog

MySQL服务器停止或重启时
使用 flush logs 命令；
当 binlog 文件大小超过 max_binlog_size 变量的值

　　以上三种情况，MySQL会重新生成一个新的日志文件，文件序号递增。

　　注： max_binlog_size 的最小值是4096字节，最大值和默认值是 1GB (1073741824字节)。

　　事务被写入到binlog的一个块中，它不会在几个二进制日志之间被拆分。因此，如果有很大的事务，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的日志都记录到当前日志文件中，直到事务结束，所以有的时候看到binlog文件大于 max_binlog_size 的情况。

　　binlog的格式

　　二进制日志中的事件的格式取决于二进制记录格式。支持三种格式类型：

STATEMENT：基于SQL语句的复制（statement-based replication, SBR）
ROW：基于行的复制（row-based replication, RBR）
MIXED：混合模式复制（mixed-based replication, MBR)

　　Version >= MySQL 5.7.7 + ，默认值是 ROW

　　基于行的格式-Row

　　基于行的日志格式 binlog_format=ROW

　　举个例子，假设有一个SQL修改了1万条数据，基于段Statement的日志格式仅仅会记录这个SQL，而基于Row的日志会有1万条记录分别记录每一行的数据修改。

优点：记录每一条数据的变更，因此使得MySQL主从复制更加安全。对每一行数据的修改比基于段的复制高效。还可以用来数据恢复（比对数据的变更）
缺点：因为要记录每一条的变更，因此记录日志量较大

　　日志内容的控制参数 binlog_row_image

binlog_row_image = FULL | MINIMAL | NOBLOB  （3个选项，默认FULL）

　　一个表中有 20 列(20个字段) ，3个参数的区别如下

FULL 全部字段都记录
MINIMAL 仅记录变更的字段数据
NOBLOB ：和full类似，只是不记录BLOB类型的字段，其他全记录

　 mysqlbinlog -vv 命令查看ROW格式的日志

常用的binlog操作

## binlog相关的命令 

```sql
# 查看是否开启binlog
mysql>show binary logs;

#查看binlog格式：
mysql>show variables like 'binlog_format';

#获取binlog文件列表：
mysql>show binary logs;

#查看当前正在写入的binlog文件：
mysql>show master status;

#查看master上的binlog：
mysql>show master logs;

#只查看第一个binlog文件的内容：
mysql>show binlog events;

#查看指定binlog文件的内容：
mysql>show binlog events in 'mysql-bin.000045';


#清空所有的bin-log：
mysql>reset master;

#生成一个新的binlog：
mysql>flush logs;

　　建议 binlog_format=mixed 或者 row，如果用row的话，最好binlog_row_image=minimal ,减少binlog的大小，占用更少的网络I/O 和磁盘I/O

什么是redolog和binglog？

为什么redolog和binlog要进行二阶段提交？

　　 commit①步骤是属于begin…commit②语句中的一个步骤，且是最后一个步骤，两个commit是包含的关系。如果redo log持久化并进行了提交，而binlog未持久化数据库就crash了，则从库从binlog拉取数据会少于主库，造成不一致。因此需要内部事务来保证两种日志的一致性。

　　prepare：redolog写入log buffer，并fsync持久化到磁盘，在redolog事务中记录2PC的XID，在redolog事务打上prepare标识
　　commit：binlog写入log buffer，并fsync持久化到磁盘，在binlog事务中记录2PC的XID，同时在redolog事务打上commit标识
　　其中，prepare和commit阶段所提到的“事务”，都是指内部XA事务，即2PC

　　如果redolog和binlog顺序提交，则具体步骤会如下：

redolog刷入cache并fsync刷盘，并打上commit标识
binlog刷入cache并fsync刷盘

　　如果redolog进行了commit，而此时数据库crash导致binlog刷盘失败，redolog无法回滚，会造成redolog和binlog不一致。通过事务可以同时提交redolog和binlog，两者落盘之后都会记录2PC事务的XID（redolog和binlog中事务落盘的标识），若中途数据库crash，通过XID关联两者并在恢复时决定commit和rollback与否。

恢复步骤？
　　redolog中的事务如果经历了二阶段提交中的prepare阶段，则会打上prepare标识，如果经历commit阶段，则会打上commit标识（此时redolog和binlog均已落盘）。

　　Step1. 按顺序扫描redolog，如果redolog中的事务既有prepare标识，又有commit标识，就直接提交（复制redolog disk中的数据页到磁盘数据页）

　　Step2 .如果redolog事务只有prepare标识，没有commit标识，则说明当前事务在commit阶段crash了，binlog中当前事务是否完整未可知，此时拿着redolog中当前事务的XID（redolog和binlog中事务落盘的标识），去查看binlog中是否存在此XID

　　 a. 如果binlog中有当前事务的XID，则提交事务（复制redolog disk中的数据页到磁盘数据页）

　　b. 如果binlog中没有当前事务的XID，则回滚事务（使用undolog来删除redolog中的对应事务）

posted on 2020-12-13 19:31 溪水静幽阅读(191) 评论(0) 收藏举报