mysql binlog 数据介绍

1.binlog 基础介绍

binlog 文件包含控制事件 ：Format_description（文件头） 事件跟 Rotate 事件（文件尾）以及一些事件组，slave 在复制事件的时候，只会完整的复制一个事件

每个 binlog 事件由 4 部分组成：

通用头：包含事件的基本信息，其中最重要的字段的是事件类型和事件的大小

提交头：提交头与特定的事件类型有关，对于不同的事件类型，该字段的存储信息不同。与通用头一样，给定一个 binlog 文件，提交头的大小是相同的，事件类型的大小由 Format_description 事件给出

事件体：事件体存储事件的主要数据，因事件类型不同而异。事件的通用头中列出了事件体的大小和结束位置。

校验和：32位整型数，用于检查事件写入后是否有损坏

2.关于校验和

校验和通过三个选择来控制复制事件

binlog-checksum=type        其中 type 包含：1）CRC32，使用 ISO-3309 CRC-32 校验和；2）NONE，即关闭校验和。默认是 CRC32

master-verify-checksum=boolean    

slave-sql-verify-checksum=boolean

如果二进制日志或中继日志损坏，可以使用带有 --verify-binlog-checksum 选项的 mysqlbinlog 发现坏的校验和

mysqlbinlog --verify-binlog-checksum mysql-bin.000001

3.将语句写入日志

1）写入DML语句

写入 dml 语句，保证表上的锁在释放之前，提交的时候将该语句写入二进制日志，保证对表的修改一致

2）写入 DDL 语句

DDL 语句影响数据模式，比如创建和销毁临时表的时候，大量的临时表的创建跟销毁会影响系统的性能

3）写入查询

查询事件的上下文：

i:当前数据库：向Query 事件添加一个特殊字段，记录当前数据库。

ii:当前时间：NOW,CURDATE,CURTIME,UNIX_TIMESTAMP和SYSDATE。其中前面四种返回开始执行语句的时间，而 SYSDATE 返回函数执行时的时间

iii:上下文事件：包含User_var（@foo=12;），Rand（RAND()），Intvar（Auto_Increment 字段）

iv:线程ID：当不同的会话创建同名临时表的时候，临时表的命名规范就是：服务器的进程ID+创建表的线程 ID + 一个线程计数器，其中线程 ID 也会作为一个独立字段存储在每个Query 事件中

v:LOAD DATA INFILE 语句：在binlog 日志文件中会以 Begin_load_query,Append_block,execute_load_query 组成，其中Begin_load_query为开始记录，多个Append_block事件组成整个LOAD DATA INFILE 语句的数据块，execute_load_query为结束语句，其中这三个日志事件公用一个 file_id

4.二进制日志过滤器

过滤就是 binlog-ignore-db 跟 binlog-do-db 等配置，所谓的过滤针对的是当前数据库，怎么理解呢？

现在有三个语句：

use bad;insert into t1 values...

use bad;insert into good.t2 values...

use bad;update good.t1,ugly.t2 set a=b;

此时如果 ignore bad 那么上面的语句都不会被写入 binlog，原因是如果过滤的是 good 数据库，那么第三条语句就会因为 ugly 数据库的操作导致这条语句的混乱，因为此时若只留下 ugly ，那么 服务器会陷入自我矛盾

因此，为避免这种情况出现，语句都要先 use database; 

5.触发器，事件和存储例程

触发器：

CREATE TRIGGER tr_name AFTER INSERT ON employee

FOR EACH ROW

    INSERT INTO log(.....)

        VALUES(NEW.email, 'OK', CONCAT('Adding employee', NEW.name));

CREATE TRIGGER tr_name AFTER DELETE ON employee

FOR EACH ROW

    INSERT INTO log(.....)

        VALUES(OLD.email, 'OK', 'Removing employee');

其中 employee 表的执行使用 员工管理账户，存储过程的执行用的是 DBA的权限，因为 log 表只有 DBA才能执行

关于触发器的复制：如果 master 跟 slave 上都有一样的触发器，那么 复制 insert 语句就需要 insert id

存储过程：

delimiter $$    --定义存储过程块，使得遇到 ; 不会立即结束语句

create procedure employee_add(p_name CHAR(64), p_email CHAR(64), p_password CHAR(64)) --创建存储过程

    MODIFIES SQL DATA    --修改数据

BEGIN        --begin_end 块

    DECLARE l_pass CHAR(64);    --定义变量

    SET l_pass = PASSWORD(p_password);    --设置变量的值

    INSERT INTO employee(name, email, password)

        VALUES(p_name, p_email, p_password);

END $$

CREATE PROCEDURE employee_password(p_email CHAR(64), p_password CHAR(64))

    MODIFIES SQL DATA

BEGIN

    DECLARE l_pass CHAR(64);

    SET l_pass = PASSWORD(l_password);

    UPDATE employee SET password = l_pass

        WHERE email = p_email;

END $$

CREATE PROCEDURE employee_del(p_name CHAR(64))

    MODIFIES SQL DATA

BEGIN

    DELETE FROM employee WHERE name = p_name;

END $$

delimiter ;

存储过程当 insert 的时候会先插入一个 insert_id （用来从的复制），但是不会在二进制日志中记录 call 语句，会修改变量的值，改成 name_const 来进行存储

存储函数：

delimiter $$

CREATE FUNCTION employee_email(p_name CHAR(64))

    RETURNS CHAR(64)

    DETERMINISTIC    --指定特征

BEGIN

    DECLARE l_email CHAR(64);

    SELECT email INTO l_email FROM employee WHERE name = p_name;

    RETURN l_email;

END $$

delimiter ;

[NOT] DETERMINISTIC 表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为[NOT] DETERMINISTIC 

CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA：指明子程序使用SQL语句的限制。

CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；

NO SQL表明子程序不包含SQL语句；

READS SQL DATA：说明子程序包含读数据的语句；

MODIFIES SQL DATA表明子程序包含写数据的语句。

默认情况下，系统会指定为CONTAINS SQL

使用存储函数：insert into collection(name,emial) values('chuck', employee_email('chuck'));