MySQL 5.7.26日志管理

1.错误日志
1.1 作用
排查MySQL运行过程的故障.
1.2 默认配置
默认开启
默认路径和名字: datadir/hostname.err

错误日志文件名及查看方法: 方括号中有ERROR的---->[ERROR]

1.3 人为定制位置vi /etc/my.cnf
log_error=/tmp/mysql3306.log
重启生效.

2.二进制日志(binlog)
2.1 作用
(1) 主从要依赖二进制日志
(2) 数据恢复时需要依赖于二进制日志

2.2 配置
(1)参数介绍
默认没有开启.
server_id=6 ####集群标识
log_bin=/data/binlog/mysql-bin
说明:
/data/binlog : 提前定制好的目录,而且要有mysql.mysql的权限
mysql-bin : 二进制日志文件名的前缀
例如: mysql-bin.000001 ,mysql-bin.000002 …
binlog_format=row —> 5.7版本默认配置是row,可以省略.

(2) 参数配置
server_id=6
log_bin=/data/binlog/mysql-bin
binlog_format=row

(3)创建目录和授权
mkdir -p /data/binlog/
chown -R mysql.mysql /data

(4)重启生效

2.3 二进制日志
2.3.1 概括
记录的数据库所有变更类的操作日志.
DDL
DCL
DML

2.3.2 DDL 和 DCL
以语句的方式,原模原样的记录.

2.3.3 DML
(1) 他记录的已提交的事务
(2) DML记录格式(statement,row,mixed),通过binlog_format=row参数控制
说明:
statement-------SBR,语句模式记录日志,做什么命令,记录什么命令.
row ---------------RBR,行模式,数据行的变化(推荐)
mixed ------------MBR,混合模式

SBR和RBR区别
SBR: 可读性较强,对于范围操作日志量少,但是可能会出现记录不准确的情况.
RBR: 可读性较弱,对于范围操作日志大,不会出现记录错误，高可用环境中的新特性要依赖于RBR

2.4 二进制日志记录单元
2.4.1 event 事件
二进制日志的最小单元
DDL :
对于DDL等语句是每一个语句就是一个事件
DML: 一个事务包含了多个语句
begin; -------- 事件1
a ---------------事件2
b ---------------事件3
commit; ------事件4

2.4.2 event事件的开始和结束号码
作用,方便我们从日志中截取我们想要的日志事件.

2.5 二进制日志的管理
2.5.1 查看二进制日志位置
mysql> show variables like ‘%log_bin%’;

2.5.2 查看所有已存在的二进制日志
mysql> show binary logs;

mysql> flush logs;
mysql> show binary logs;

2.5.3 查看正在使用的二进制日志
mysql> show master status ;

2.5.4 查看二进制日志事件
mysql> create database binlog charset utf8mb4;
mysql> use binlog
mysql> create table t1(id int);

mysql> insert into t1 values(1);

mysql> show master status ;

mysql> show binlog events in ‘mysql-bin.000004’;

2.5.5 查看二进制日志内容
[[email protected] binlog]# mysqlbinlog mysql-bin.000007

[[email protected] binlog]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000007
#简洁一点显示

[[email protected] binlog]# mysqlbinlog -d binlog mysql-bin.000007
#指定binlog数据库显示

2.5.6 截取二进制日志
[[email protected] binlog]# mysqlbinlog --start-position=219 --stop-position=335 mysql-bin.000007 >/tmp/seven.sql

2.5.7 通过binlog恢复数据
(1) 模拟数据
mysql> create database haoge charset utf8mb4;
mysql> use haoge;
mysql> create table t1(id int);
mysql> insert into t1 values(1);
mysql> commit;

(2)模拟故障
mysql> drop database haoge;

(3)分析和截取binlog
mysql> show master status ; ---------------------------------->确认使用的是哪一个日志

mysql> show binlog events in ‘mysql-bin.000008’ ; ------>查看事件

说明: 找到起点和终点,进行截取
mysqlbinlog --start-position=219 --stop-position=816 /data/binlog/mysql-bin.000008 >/tmp/eight.sql

(4)恢复binlog
mysql> set sql_log_bin=0; —>临时关闭恢复时产生的新日志
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1; —>改回来

2.6 binlog的gtid记录模式的管理
2.6.1 gtid介绍
对于binlog中的每一个事务,都会生成一个gtid号码
DDL ,DCL 一个event就是一个事务,就会有一个gtid号.
DML语句来讲,begin到commit,是一个事务,就是一个gtid号
2.6.2 gtid的组成
server-uuid:TID
[[email protected] data]# cat auto.cnf
[auto]
server-uuid=d60b549f-9e10-11e9-ab04-000c294a1b3b
TID是一个:自增长的数据,从1开始
d60b549f-9e10-11e9-ab04-000c294a1b3b:1-15

2.6.3 GTID的幂等性
如果拿有GTID的日志去恢复时,检查当前系统中是否有相同GTID号,有相同的就自动跳过
会影响到binlog恢复和主从复制.

2.6.4 GTID的开启和配置
vim /etc/my.cnf
gtid-mode=on ####开启
enforce-gtid-consistency=true ####强制一致性

2.6.5 查看GTID信息
mysql> create database gtid charset utf8mb4;
mysql> show master status;
mysql> use gtid;
mysql> create table t1(id int);
mysql> show master status;
mysql> insert into t1 values(1);
mysql> commit;
mysql> show master status;
mysql> drop database gtid;

2.6.6 基于GTID,binlog恢复
(1) 截取日志
[[email protected] data]# cd /data/binlog/
[[email protected] binlog]# mysqlbinlog --include-gtids=‘d60b549f-9e10-11e9-ab04-000c294a1b3b:1-3’ mysql-bin.000005 >/tmp/gtid.sql

(2)恢复
mysql> set sql_log_bin=0;
mysql> source /tmp/gtid.sql
mysql> set sql_log_bin=1;
(3) 报错
ERROR 1049 (42000): Unknown database ‘gtid’
Query OK, 0 rows affected (0.00 sec)
ERROR 1046 (3D000): No database selected
为什么报错?
因为幂等性的检查,1-3事务已经做过了.

(4)正确的做法
mysqlbinlog --skip-gtids --include-gtids=‘d60b549f-9e10-11e9-ab04-000c294a1b3b:1-3’ mysql-bin.000005 >/tmp/gtid.sql
–skip-gtids 作用:在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息

(5) 恢复
mysql> set sql_log_bin=0;
mysql> source /tmp/gtid.sql
mysql> set sql_log_bin=1;

2.6.7 GTID相关的参数
–skip-gtids
–include-gtids=‘d60b549f-9e10-11e9-ab04-000c294a1b3b:6’,‘d60b549f-9e10-11e9-ab04-000c294a1b3b:8’
–exclude-gtids=‘d60b549f-9e10-11e9-ab04-000c294a1b3b:6’,‘d60b549f-9e10-11e9-ab04-000c294a1b3b:8’

3.慢日志(slow-log)
3.1 作用
记录运行较慢的语句,优化过程中常用的工具日志.
3.2 如何配置
#开关
slow_query_log=1
#文件位置及名字
slow_query_log_file=/data/mysql/slow.log
#设定慢查询时间
long_query_time=0.1
#没走索引的语句也记录
log_queries_not_using_indexes
######################
vim /etc/my.cnf
slow_query_log=1
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes