mysql 错误日志，二进制日志的三种形式，截取及清理, 初识gtid
mysql提供的工具类日志种类：
1.错误日志(log_error)
用来记录启动\关闭\日常运行过程中,状态信息,警告,错误。默认是开启的
1.1 错误日志配置
1 默认就是开启的: /数据路径下/hostname.err 2 查看错误日志位置：select @@log_error; 3 4 手工指定位置: 5 vim /etc/my.cnf 6 log_error=/var/log/mysql.log 7 log_timestamps=system 8 重启生效 9 10 11 show variables like 'log_error';
1.2 日志内容查看
1 主要关注[ERROR],看上下文
2. binlog(binary logs):二进制日志
2.1 作用
1 备份恢复必须依赖二进制日志 2 主从环境必须依赖二进制日志 3 5.x以上版本默认都没有开启二进制日志，需要手动配置来启用
2.2 binlog配置 (5.7必须加server_id)
1 注意：MySQL默认是没有开启二进制日志的。 2 3 基础参数查看: 4 开关状态: select @@log_bin; 5 日志路径及名字：select @@log_bin_basename; 6 服务ID号: select @@server_id; 7 二进制日志格式: select @@binlog_format; 8 双一标准之二: select @@sync_binlog;
2.2.1 创建日志目录
1 mkdir /data/binlog 2 chown -R mysql.mysql /data/binlog
2.2.2 修改配置文件
1 vim /etc/my.cnf 2 3 4 server_id=6 (取值1-65535) 5.6中，单机可以不需要此参数 5 6 # log_bin有两种配置方式，如下讲解 7 log_bin=1 只打开二进制日志开关，文件存放在默认的位置 8 log_bin=/data/binlog/mysql-bin 开启二进制日志，按照路径生成二进制文件，mysql-bin为指定的文件名前缀。 9 指定路径后会在其下额外生成个mysql-bin.index文件，其内存放的是二进制文件的名，便于统计 10 11 binlog_format=row 5.7默认的配置，可省略
2.2.3 重启数据库生效
2.2.4 参数说明
server_id=3306 主要是在主从复制过程中必须要加的,但是在5.7版本中,要用以下参数(log_bin),开启binlog日志,即使是单机也是必加的 log_bin=/data/binlog/mysql-bin (1)开启二进制日志功能 (2)设置二进制日志目录及名称前缀
binlog_format=row binlog中记录dml语句的记录格式
2.3 binlog记录了什么?
2.3.0 引入
1 binlog是SQL层的功能。记录的是变更SQL语句，不记录查询语句。
2.3.1 记录SQL语句种类
DDL ：原封不动的记录当前DDL(statement语句方式)。
DCL ：原封不动的记录当前DCL(statement语句方式)。
DML ：只记录已经提交的事务DML
2.3.2 DML三种记录格式，仅对dml语句有效
binlog_format=xx（binlog的记录格式）参数影响,取值如下： （1）statement（5.6默认）SBR(statement based replication) ：语句模式原封不动的记录当前DML。 （2）ROW(5.7 默认值) RBR(ROW based replication) ：记录数据行的变化(用户看不懂，需要工具分析) （3）mixed（混合）MBR(mixed based replication)模式 ：以上两种模式的混合
2.3.3 三种记录格式如何选取：
1 SBR与RBR模式的对比： 2 3 4 STATEMENT(SBR)：可读性较高，日志量少，但是不够严谨 5 ROW（RBR） ：可读性很低，日志量大，足够严谨，一些高可用环境中的新特性要依赖RBR模式 6 7 示例： 8 update t1 set xx=xx where id>10 9 SBR会把update语句记录下来， 10 RBR会把所有受影响的行的变化状态给记录下来 11 12 解析： 13 为什么说SBR不严谨或某些情况下不准确呢？ 14 insert into t1 values(1,'zs',now())这种场景下使用now获取时间场景 15 16 17 我们建议使用：row记录模式
2.4 event（事件）是什么?
2.4.1 事件的简介
1 二进制日志的最小记录单元 2 3 4 对于DDL,DCL,一个语句就是一个event 5 对于DML语句来讲:只记录已提交的事务。 6 7 例如以下列子,就被分为了4个event 8 begin; 120 - 340 事件1 9 DML1 340 - 460 事件2 10 DML2 460 - 550 事件3 11 commit; 550 - 760 事件4
2.4.2 event的组成
1 三部分构成: 2 (1) 事件的开始标识 3 (2) 事件内容 4 (3) 事件的结束标识 5 Position: 6 开始标识: at 194 7 结束标识: end_log_pos 254 8 194? 254? 9 某个事件在binlog中的相对位置号 10 位置号的作用是什么？ 11 为了方便我们截取事件
2.5 日志文件查看
2.5.1 查看日志的开启情况
log_bin参数设置的路径,可以找到二进制日志
1 Master [(none)]>show variables like '%log_bin%'; 2 +---------------------------------+------------------------------+ 3 | Variable_name | Value | 4 +---------------------------------+------------------------------+ 5 | log_bin | ON | 6 | log_bin_basename | /data/binlog/mysql-bin | 7 | log_bin_index | /data/binlog/mysql-bin.index | 8 | log_bin_trust_function_creators | OFF | 9 | log_bin_use_v1_row_events | OFF | 10 | sql_log_bin | ON | 11 +---------------------------------+------------------------------+ 12 6 rows in set (0.01 sec)
2.5.2 查看一共多少个binlog
1 Master [(none)]>show binary logs; 2 +------------------+-----------+ 3 | Log_name | File_size | 4 +------------------+-----------+ 5 | mysql-bin.000001 | 154 | 6 +------------------+-----------+ 7 1 row in set (0.01 sec) 8 9 10 # 手动创建出（刷出更多二进制日志文件） 11 Master [(none)]>flush logs; 12 Query OK, 0 rows affected (0.03 sec) 13 14 Master [(none)]>flush logs; 15 Query OK, 0 rows affected (0.01 sec) 16 17 Master [(none)]>show binary logs; 18 +------------------+-----------+ 19 | Log_name | File_size | 20 +------------------+-----------+ 21 | mysql-bin.000001 | 201 | 22 | mysql-bin.000002 | 201 | 23 | mysql-bin.000003 | 154 | 24 +------------------+-----------+ 25 3 rows in set (0.00 sec)
2.5.3 查看mysql正在使用的日志文件
1 Master [(none)]>show master status; 2 +------------------+----------+--------------+------------------+-------------------+ 3 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 4 +------------------+----------+--------------+------------------+-------------------+ 5 | mysql-bin.000003 | 154 | | | | 6 +------------------+----------+--------------+------------------+-------------------+ 7 Master [(none)]>
file：当前MySQL正在使用的文件名
Position：最后一个事件的结束位置号
2.6 日志内容查看
2.6.1 event查看
1 Master [binlog]>show binlog events in 'mysql-bin.000003'; 2 +------------------+-----+----------------+-----------+-------------+----------------------------------------+ 3 | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | 4 +------------------+-----+----------------+-----------+-------------+----------------------------------------+ 5 | mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 | 6 | mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | | 7 | mysql-bin.000003 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | 8 | mysql-bin.000003 | 219 | Query | 6 | 319 | create database binlog | 9 | mysql-bin.000003 | 319 | Anonymous_Gtid | 6 | 384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | 10 | mysql-bin.000003 | 384 | Query | 6 | 486 | use `binlog`; create table t1 (id int) | 11 +------------------+-----+----------------+-----------+-------------+----------------------------------------+ 12 前两行不用关注，是mysql固定的头格式，用于表示版本等信息。
13 13 Log_name：binlog文件名 14 Pos：开始的position ***** 15 Event_type：事件类型 16 Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息 17 Server_id：mysql服务号标识 18 End_log_pos：事件的结束位置号 ***** 19 Info：事件内容***** 20 补充: 21 SHOW BINLOG EVENTS 22 [IN 'log_name'] 23 [FROM pos] 24 [LIMIT [offset,] row_count] 25 [root@db01 binlog]# mysql -e "show binlog events in 'mysql-bin.000004'" |grep drop
2.6.2 binlog文件内容详细查看
普通查看：
mysqlbinlog /data/mysql/mysql-bin.000006
格式化查看，也难以读懂，但比普通查看好多了 mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000003
使用-d参数来指定库名来从日志中查看内容，binlog是库名 mysqlbinlog -d binlog /data/binlog/mysql-bin.000003 [root@db01 binlog]# mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00' /data/binlog/mysql-bin.000004
2.7 基于Position号进行日志截取
核心就是找截取的起点和终点 --start-position=321 --stop-position=513 mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql 案例: 使用binlog日志进行数据恢复 模拟: 1. [(none)]>create database binlog charset utf8; 2. [(none)]>use binlog; [binlog]>create table t1(id int); 3. [binlog]>insert into t1 values(1); [binlog]>commit; [binlog]>insert into t1 values(2); [binlog]>commit; [binlog]>insert into t1 values(3); [binlog]>commit; 4. [binlog]>drop database binlog; 恢复: [(none)]>show master status ; [(none)]>show binlog events in 'mysql-bin.000004'; [root@db01 binlog]# mysqlbinlog --start-position=1227 --stop-position=2342 /data/binlog/mysql-bin.000004 >/tmp/bin.sql [(none)]>set sql_Log_bin=0; 临时关掉二进制log功能，不记录以下sql, 用完记得再打开， set sql_log_bin=1 [(none)]>source /tmp/bin.sql 面试案例: 1. 备份策略每天全备,有全量的二进制日志 2.业务中一共10个库,其中一个被误drop了 3. 需要在其他9个库正常工作过程中进行数据恢复
2.7.1 思考：
以上的恢复手段缺陷很大，因为我们很容易的就能找到建库到删库的所有起始号码，
实际生产中我们库很可能是很多年前就创建了，并且多个库下表的记录都是混杂在一起的，
此时用以上手段很难恢复，需要大量的时间去整理该库的所有二进制日志，显然不现实。
解决：
我们可以先使用备份把库恢复到最近一次备份的时间点，然后再配合二进制的恢复手段来
恢复出上个时间点到删库之前的数据。
mysql没有提供指定表查看二进制记录的功能，但提供有指定库名来查看该库中所有表的二进制记录，命令参考： 2.6.2 binlog文件内容详细查看
2.8 binlog日志的GTID新特性
2.8.1 GTID 介绍
5.6 版本新加的特性,5.7中做了加强 5.6 中不开启,没有这个功能. 5.7 中的GTID,即使不开也会有自动生成 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
2.8.2. GTID(Global Transaction ID)
是对于一个已提交事务进行编号，并且是一个全局唯一的编号。
DDL和DCL每一条语句就是一个事务， 就会有一个对应的编号。
GTID组成形式： server_uuid:TID
7E11FA47-31CA-19E1-9E56-C43AA21293967:1-3
server_uuid在数据库初始化完成，第一次启动的时候就会自动生成，存放于数据目录中的auto.cnf文件中
TID是一个从1开始的递增数字，其具有幂等性，幂等性见2.8.4
GTID的配置和开启
vim /etc/my.cnf gtid-mode=on 开启gtid模式 enforce-gtid-consistency=true 强制gtid一致性
GTID开启只影响后续发生的事务，开启之前的不受影响。配置好后启动生效
查看GTID的表现
重启第一次show master status时最后一列的值为空，该列就是gtid的编号
Master [(none)]>create database gtid charset utf8; Query OK, 1 row affected (0.01 sec) Master [(none)]>show master status ; +------------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+----------------------------------------+ | mysql-bin.000004 | 326 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1 | +------------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec) Master [(none)]>use gtid Database changed Master [gtid]>create table t1 (id int); Query OK, 0 rows affected (0.01 sec) Master [gtid]>show master status ; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000004 | 489 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-2 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) Master [gtid]>create table t2 (id int); Query OK, 0 rows affected (0.01 sec) Master [gtid]>create table t3 (id int); Query OK, 0 rows affected (0.02 sec) Master [gtid]>show master status ; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000004 | 815 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-4 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) Master [gtid]>begin; Query OK, 0 rows affected (0.00 sec) Master [gtid]>insert into t1 values(1); Query OK, 1 row affected (0.00 sec) Master [gtid]>commit; Query OK, 0 rows affected (0.00 sec) Master [gtid]>show master status ; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000004 | 1068 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-5 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) Master [gtid]>begin; Query OK, 0 rows affected (0.00 sec) Master [gtid]>insert into t2 values(1); Query OK, 1 row affected (0.00 sec) Master [gtid]>commit; Query OK, 0 rows affected (0.01 sec) Master [gtid]>show master status ; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000004 | 1321 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-6 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec)
2.8.3. 基于GTID进行查看binlog
具备GTID后,截取查看某些事务日志: --include-gtids --exclude-gtids
mysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' /data/binlog/mysql-bin.000004
mysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4' /data/binlog/mysql-bin.000004
使用--exclude-gtids可指定需要排除的gtid号，可一次排除多个，只需使用逗号隔开多个gtid号即可。include-gtids也可这样使用
2.8.4 GTID的幂等性
开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了
幂等性会影响binlog恢复和主从复制
就想恢复?怎么办? --skip-gtids 可用来忽略gtid幂等性，注意，只要开启了gtid不管是否使用incluede还是position号来截取二进制日志，都需要加这个参数。
mysqlbinlog --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:4' /data/binlog/mysql-bin.000004 /data/binlog/mysql-bin.000004 set sql_log_bin=0; source /tmp/binlog.sql set sql_log_bin=1;
故障恢复演练
创建库 创建表 写入数据 递交 删除库 show master status;
show binlog events in 'mysql-bin.000004'; 查看从创建库到删除库的gtid号，以下以1-6来演示 mysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' /data/binlog/mysql-bin.000004 > /xx/back.sql
登陆数据库先禁用二进制日志，然后source截取出的文件
set sql_log_bin=0
source /xx/back.sql 此处会报错提示找不到database
思考为什么失败
我们导出的恢复文件就是从系统中导出的，系统中记录有1-6的事务，结合GTID的幂等性就可明白了，因为系统认为1-6号事务已执行过，就不会再执行了。
解决办法，我们可在导出二进制日志时让其忽略幂等性，
--skip-gtids 在导出时忽略原有的gtid，恢复时生成新的gtid
mysqlbinlog --skip-gtids --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' /data/binlog/mysql-bin.000004 > /xx/back.sql
使用新导出的back.sql再次到数据库执行即可。
2.9二进制日志其他操作
2.9.1 自动清理日志
show variables like '%expire%'; expire_logs_days 0 0表示永不清理 自动清理时间,是要按照全备周期+1 set global expire_logs_days=15; 永久生效: my.cnf expire_logs_days=15; 企业建议,至少保留两个全备周期+1的binlog
2.9.2 手工清理
# 清除3天前的
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
# 清除到10之前的，也就是删掉了1-9 PURGE BINARY LOGS TO 'mysql-bin.000010'; 注意:不要手工 rm binlog文件 1. my.cnf binlog关闭掉,启动数据库 2.把数据库关闭,开启binlog,启动数据库 删除所有binlog,并从000001开始重新记录日志
# 删掉二进制文件后，后缀标号也不会从1开始，如果6位数用满了可使用如下命令重新从1开始。主从时千万不要使用
reset master; 主从关系中，主库执行此操作，主从环境必崩
2.9.3 日志是怎么滚动
1. flush logs; 2. 重启mysql也会自动滚动一个新的 3. 日志文件达到1G大小(max_binlog_size) max_binlog_size | 1073741824 4. 备份时,加入参数也可以自动滚动
