mysql之日志管理

日志管理

1. 错误日志

1.1 作用

MySQL 启动及工作过程中,状态\报错\警告.

1.2 怎么配置?

oldguo[(none)]>select @@log_error;

默认是在datadir=/data/3306/data/hostname.err

自定义错误日志目录

vim /etc/my.cnf
log_error=/data/3306/data/mysql.log
重启生效.

1.3 如何查看错误日志?

关注[ERROR]的上下文.
如 grep -C 5 'ERROR' /data/3306/data/hostname.err

2. 二进制日志 *****

2.1 作用

数据恢复必备的日志
主从复制依赖的日志

2.2 怎么配置

2.2.1 修改配置文件

vim /etc/my.cnf 
server_id=6  ##哪台数据库实例与其他实例不一样即可
log_bin=/data/binlog/mysql-bin  ##二进制日志路径

2.2.2 创建目录授权

mkdir -p /data/binlog -p 
chown -R mysql.mysql /data/*

2.2.3 重启数据库

[root@db01 ~]# /etc/init.d/mysqld restart

2.3 二进制日志记录了什么?

2.3.1 引入

除了查询类的语句,都会记录.
所有数据库变更类的语句.

2.3.2 记录语句的种类

DDL 
DCL 
DML

2.3.4 不同语句的记录格式说明

DDL,DCL :直接以语句(statement)方式记录
DML语句 : insert ,update, delete

oldguo[(none)]>select @@binlog_format;
SBR : statement , 做什么记录什么.
RBR : row       , 记录数据行的变化. 默认模式,也是我们推荐的.
MBR : mixed     , 自动判断记录模式. 

面试题: 说明SBR和RBR的区别?

SBR : statement , 做什么记录什么,记录的就是SQL.可读性较强.日志量相对较少.日志记录可能不准确.
RBR : row       , 记录数据行的变化. 默认模式,也是我们推荐的. 可读性差,日志量大,日志记录准确.

2.3.5 binlog events(二进制日志事件)

(1) 简介

二进制日志内容以事件为最小记录单元.
对于DDL和DCL,一个DDL语句就是一个事件.
对于DML(标准的事务语句):  只记录已提交的事务的DML语句
begin ;    事件1
a          事件2
b          事件3
commit;    事件4

(2) 事件的构成 (为了截取日志)

mysqlbinlog mysql-bin.000002
# at 219               事件开始的位置(position)
end_log_pos 319        事件结束的位置(position)
#190814 18:46:35       事件发生的时间
create database oldboy 事件内容

2.3.6 二进制日志的基本查看
(1) 查看二进制日志的配置信息

oldguo[(none)]>show variables like '%log_bin%';
| log_bin                         | ON                           |
| log_bin_basename                | /data/binlog/mysql-bin  
| sql_log_bin                     | ON   

(2) 二进制日志基本信息

oldguo[(none)]>show binary logs; ##查看二进制日志文件个数
oldguo[(none)]>show master status ;  ##查看当前使用的二进制日志

(3) 查看二进制日志的事件信息

oldguo[(none)]>show master status ; ##使用的是哪个日志
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      319 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
oldguo[(none)]>show binlog events in 'mysql-bin.000002'; ##查看二进制日志文件内容

2.4 内容查看和截取

2.4.1 二进制日志内容查看命令

[root@db01 ~]# mysqlbinlog /data/binlog/mysql-bin.000005  ##这个命令查看出来的二进制日志
[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv   /data/binlog/mysql-bin.000005  ##查看解码后的二进制日志

2.4.2 日志的截取

--start-position 
--stop-position

语法:

mysqlbinlog --startposition=xxx  --stop-position=xxx /data/binlog/mysql-bin.000005>/data/bin.sql

演练:
(1) 准备数据

oldguo[(none)]>create database binlog charset utf8mb4;
oldguo[(none)]>use binlog;
oldguo[binlog]>create table t1(id int)engine=innodb charset=utf8mb4;
oldguo[binlog]>insert into t1 values(1),(2),(3);
oldguo[binlog]>commit;
oldguo[binlog]>insert into t1 values(11),(12),(13);
oldguo[binlog]>commit;
oldguo[binlog]>update t1 set id=10 where id>10;
oldguo[binlog]>commit;
oldguo[binlog]>select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|   10 |
|   10 |
+------+

(2) 搞破坏

oldguo[binlog]>drop database binlog;

(3) 确认起点和终点:

oldguo[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |     1510 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
oldguo[(none)]>show binlog events in 'mysql-bin.000006';

起点:
| mysql-bin.000006 |  219 | Query          |         6 |         335 | create database binlog charset utf8mb4 
终点:
| mysql-bin.000006 | 1412 | Query          |         6 |        1510 | drop database binlog     

(4) 截取日志

mysqlbinlog --start-position=219  --stop-position=1412 /data/binlog/mysql-bin.000006>/data/bin.sql

(5) 恢复日志

oldguo[(none)]>set sql_log_bin=0;   ##  临时关闭当前会话的binlog记录,不影响其他会话日志记录
oldguo[binlog]>source /data/bin.sql
oldguo[(none)]>set sql_log_bin=1;  

(6) 扩展

mysqlbinlog -d binlog --start-position=219  --stop-position=1412 /data/binlog/mysql-bin.000006>/data/bin.sql 

可以借助中间库

2.5 基于gtid的binlog管理(扩展.)

2.5.0 引入

5.6 版本以后,binlog加入了新的日志记录方式,GTID .

主要作用:
简化binlog截取
提供在主从复制中的高级功能
5.7 版本之后,进行了GTID增强
主从性能,高可用环境,集群

2.5.1 什么是gtid (Global Transaction ID)

全局唯一的事务编号.
幂等性
GTID:Server-uuid:Tx_id
545fd699-be48-11e9-8f0a-000c2980e248:1-10

2.5.2 配置

gtid_mode=on                  # 开关
enforce_gtid_consistency=true   # 强制GTID一致性
log_slave_updates=1          # 主从复制中从库记录binlog,并统一GTID信息

2.5.3 查看gtid日志信息

DDL ,DCL 一个操作就是GTID 
DML , 一个完整的事务就是一个GTID         
    begin;
    xxx 
    xxx
    commit
=================
oldguo[db1]>show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000009 |      906 |              |                  | 545fd699-be48-11e9-8f0a-000c2980e248:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

oldguo[db1]>show binlog events in 'mysql-bin.000009';
| mysql-bin.000009 | 154 | Gtid           |         6 |         219 | SET @@SESSION.GTID_NEXT= '545fd699-be48-11e9-8f0a-000c2980e248:1' 

2.5.4 基于gtid截取日志

--include-gtids= 
--exclude-gtids=
--skip-gtids

截取1-3号事务:

[root@db01 ~]# mysqlbinlog --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-3' /data/binlog/mysql-bin.000009>/data/gtid.sql

截取 1-10 gtid事务,跳过6号和8号事务.

[root@db01 ~]# mysqlbinlog --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-10' --exclude-gtids='545fd699-be48-11e9-8f0a-000c2980e248:6,545fd699-be48-11e9-8f0a-000c2980e248:8'    /data/binlog/mysql-bin.000009>/data/gtid.sql

2.5.5 演练

(1) 准备环境

oldguo[(none)]>create database gtid charset utf8mb4;
oldguo[(none)]>use gtid
oldguo[gtid]>create table t1 (id int) engine=innodb charset=utf8mb4;
oldguo[gtid]>insert into t1 values(1),(2),(3);
oldguo[gtid]>commit;
oldguo[gtid]>insert into t1 values(11),(22),(33);
oldguo[gtid]>commit;
oldguo[gtid]>select * from t1 ;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   11 |
|   22 |
|   33 |
+------+

(2) 搞破坏

oldguo[gtid]>drop database gtid;

(3) 找起点和终端(gtid)

oldguo[(none)]>show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000009 |     1957 |              |                  | 545fd699-be48-11e9-8f0a-000c2980e248:1-8 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

oldguo[(none)]>show binlog events in 'mysql-bin.000009';

| mysql-bin.000009 |  906 | Gtid           |         6 |         971 | SET @@SESSION.GTID_NEXT= '545fd699-be48-11e9-8f0a-000c2980e248:4'  |
| mysql-bin.000009 |  971 | Query          |         6 |        1081 | create database gtid charset utf8mb4    


| mysql-bin.000009 | 1800 | Gtid           |         6 |        1865 | SET @@SESSION.GTID_NEXT= '545fd699-be48-11e9-8f0a-000c2980e248:8'  |
| mysql-bin.000009 | 1865 | Query          |         6 |        1957 | drop database gtid     


(4) 截取日志 (仅供参考)
mysqlbinlog --skip-gtids --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:4-7' /data/binlog/mysql-bin.000009>/data/gtid.sql

(5) 恢复数据
oldguo[(none)]>set sql_log_bin=0;
oldguo[(none)]>source /data/gtid.sql

2.6二进制日志其他操作

2.6.1 自动清理日志

show variables like '%expire%';
expire_logs_days  0  

自动清理时间,是要按照全备周期+1

set global expire_logs_days=8;

这个周期设置可以通过备份周期来设置，一周一全备份，可以设置为8天。

永久生效:

my.cnf
expire_logs_days=15;

企业建议,至少保留两个全备周期+1的binlog

2.6.2 手工清理

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000009';

注意:不要手工 rm binlog文件

1. my.cnf binlog关闭掉,启动数据库
2.把数据库关闭,开启binlog,启动数据库
删除所有binlog,并从000001开始重新记录日志
*reset master;     主从关系中，主库执行此操作，主从环境必崩

2.6.3 binlog的滚动机制

flush logs; ##立马生成一个新的binlog日志文件
重启数据库 ##每次重启数据库都会新建一个日志文件
select @@max_binlog_size; ##设置binlog日志文件大小，达到限制后自动新建一个新的日志文件
备份时,某些参数会触发.

3. 慢日志(slow-log)

3.1 简介

记录运行较慢的语句记录slowlog中.
功能是辅助优化的工具日志.
应激性的慢 ----> show processlist;
一段时间慢 ----> slow记录,统计

3.2 配置

oldguo[(none)]>show variables like '%slow%';
oldguo[(none)]>select @@long_query_time;
oldguo[(none)]>show variables like '%not_using_indexes%';

slow_query_log=1 ##开启慢日志
slow_query_log_file=/data/3306/data/db01-slow.log ##设置慢日志路径
long_query_time=0.1 ##设置多久被认为慢
log_queries_not_using_indexes ##不使用索引的

配置完成重启生效.

3.3 慢语句模拟

set sql_log_bin=0;
source /tmp/t100w.sql; 
set sql_log_bin=1;

3.4 分析处理慢语句

[root@db01 ~]# mysqldumpslow -s c -t 5  /data/3306/data/db01-slow.log 

-s 排序出现次数多的，c 表示出现次数 -t 表示显示5个

3.5 自己扩展一下

pt-query-digest    /data/3306/data/db01-slow.log 
集成: pt-query-digest+Anemometer=WEB方式:(分析慢日志,二进制日志,错误日志...)

周六记得解决