MySQL日志管理学习笔记day09
1.错误日志
1.1 作用
MySQL启动及工作过程中,状态\报错\警告\。
1.2怎么配置？
mysql> select @@log_error;
+---------------------------+
| @@log_error |
+---------------------------+
| /data/3306/data/mysql.log |
+---------------------------+
1 row in set (0.00 sec)
默认时在datadir=/data/3306/data/hostname.err
设置
vim /etc/my.cnf
然后加一行路径即可。
log_error=路径
然后重启生效
1.3如何查看错误日志
vim /data/3306/data/mysql.log 也可以用cat 、tail都行。
关注[ERROR]的上下文。
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/*
```vim里 ctrl c 锁住 ctrl q 解锁```
然后重启
二进制之日文件 可以用strings命令来查看版本号
strings mysql-bin.000001
2.3 二进制日志记录了什么？
2.2.1 引入
除了查询类的语句,都会记录。
所有数据库变更类的语句。
2.3.2 记录语句的种类
DDL
DCL
DML
2.3.4 不同语句的记录格式说明
DDL,DCL:直接以语句（statement）方式记录
DML语句: insert, update,delete
SBR : statement ,做什么记录什么。
RBR : row 记录数据行的变化 默认模式,记录的数据不会出现误差。
MBR：mixed 自动判断记录模式
面试题:说明SBR和RBR的区别？
SBR: statement ,做什么记录什么,记录的就是SQL,可读性较强,日志量相对较少,好处很多，日志记录可能会有误差，可能不准确。
RBR: row ，记录数据行的变化,默认模式，可读性差,日志量大,日志记录准确。也是我们推荐的模式。
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
2.3.5 binlog events(二进制日志事件)
（1）简介
二进制日志内容以事件为最小记录单元。
对于DDL和DCL,一个语句就是一个事件。
对于DML(标准的事务语句) : 只记录已提交的事务的DML语句
begin; 事件1
a 事件2
b �事件3
commit; 事件4
（2）事件的构成
查看二进制日志文件
mysqlbinlog mysql-bin000014
# at 941 事件开始的位置 （position）
#190904 19:19:30 事件发生的时间 （position）
server id 6 end_log_pos 1029 CRC32 0x0caff37c 事件结束的位置
create database hh 事件内容
2.3.6 二进制日志的基本查看
（1） 查看二进制日志的配置信息
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------+
| log_bin | ON |
| log_bin_basename | /data/binlog/mysql-bin |
| sql_log_bin | ON |
(2) 二进制日志基本信息
查看
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 692420 |
| mysql-bin.000003 | 546 |
| mysql-bin.000004 | 1517 |
| mysql-bin.000005 | 661 |
| mysql-bin.000006 | 2086 |
| mysql-bin.000007 | 397 |
| mysql-bin.000008 | 241 |
| mysql-bin.000009 | 241 |
| mysql-bin.000010 | 524 |
| mysql-bin.000011 | 964084 |
| mysql-bin.000012 | 217 |
| mysql-bin.000013 | 538 |
| mysql-bin.000014 | 1029 |
+------------------+-----------+
14 rows in set (0.01 sec)
查看当前正在使用的日志文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000014 | 1029 | | | 8d2ed0d5-b75a-11e9-ba1c-000c29222c92:1-87 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
(3) 查看二进制日志的事件信息
mysql> show binlog events in 'mysql-bin.000014';
2.4 内容查看和截取
[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 --start-position=xxx --stop-position=xxx /data/binlog/mysql-bin.000005>/data/bin.sql
mysqlbinlog --help
演练：
（1） 准备数据
创建一个库
mysql> create database binlog charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
use binlog;
创建一张表
mysql> create table t1(id int)engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.03 sec)
插入一条数据
insert into t1 values(1),(2)(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
commit;
mysql> insert into t1 values(11),(12),(13);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
(2) 变更数据
mysql> update t1 set id=10 where id>10;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
删除binlog数据库
mysql> drop database binlog;
Query OK, 1 row affected (0.04 sec)
(3) 数据恢复
确认起点和终点
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000014 | 3433 | | | 8d2ed0d5-b75a-11e9-ba1c-000c29222c92:1-97 |
+------------------+----------+--------------+------------------+-------------------------------------------+
查看事件内容
show binlog events in 'mysql-bin.000014';
找到起点 （建库语句）
| mysql-bin.000014 | 1666 | Query | 6 | 1782 | create database binlog charset utf8mb4
找到终点（drop）
| mysql-bin.000014 | 3335 | Query | 6 | 3433 | drop database binlog
（4）截取日志
注意记得加:--skip-gtids参数
[root@db01 ~]#mysqlbinlog --start-position=1666 --skip-gtids --stop-position=3335 /data/binlog/mysql-bin.000014>/data/bin.sql;
[root@db01 ~]#
确认下是不是我们要截取的日志
[root@db01 /data]# vim bin.sql
(5) 恢复日志
临时关闭当前会话的binlog记录
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
用source恢复
mysql> source /data/bin.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
然后查看数据库
show databases;
发现数据已恢复成功。
mysql>
(6)扩展
过滤库
mysqlbinlog -d binlog--start-position=1666 --skip-gtids --stop-position=3335 /data/binlog/mysql-bin.000014>/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配置、
show variables like '%gtid%';
gtid_mode = on #开关
enforce_gtid_consistency=true #强制GTID一致性
log_slave_updates=1 #主从复制中从库记录binlog,并统一GTID信息。
mysql> show variables like '%log_slave_updates%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | ON |
+-------------------+-------+
配置的话加入mysql配置文件中即可。
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）准备环境
mysql> create database gtid1 charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> use gtid1
Database changed
mysql> create table t1(id int) engine=innodb charset utf8mb4;
Query OK, 0 rows affected (0.00 sec)
插入几行数据
mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(11),(12),(13);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 12 |
| 13 |
+------+
(2) 搞破坏
mysql> drop database gtid1;
Query OK, 1 row affected (0.01 sec)
(找起点和终点(gtid))
mysql> show master status;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000015 | 1256 | | | 8d2ed0d5-b75a-11e9-ba1c-000c29222c92:1-108 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000015';
开始截取：
建库操作
mysql-bin.000015 | 194 | Gtid | 6 | 259 | SET @@SESSION.GTID_NEXT= '8d2ed0d5-b75a-11e9-ba1c-000c29222c92:104' |
不能截取删库操作 也就是截取到104-107
| mysql-bin.000015 | 1096 | Gtid | 6 | 1161 | SET @@SESSION.GTID_NEXT= '8d2ed0d5-b75a-11e9-ba1c-000c29222c92:108' |
| mysql-bin.000015 | 1161 | Query | 6 | 1256 | drop database gtid1
(4)截取日志 记得--skip-gtids
mysqlbinlog --skip-gtids --include-gtids='8d2ed0d5-b75a-11e9-ba1c-000c29222c92:104-107' /data/binlog/mysql-bin.000015>/data/gtid.sql;
(5)恢复
临时关闭输出
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
恢复
source /data/gtid.sql;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| binlog |
| gtid |
| gtid1 |
| hh |
| mysql |
| performance_schema |
| school |
| sys |
| test |
| wordpress |
| wordpress1 |
| world |
| wp |
| xuexiao |
+--------------------+
15 rows in set (0.00 sec)
mysql>
mysql> show tables;
+-----------------+
| Tables_in_gtid1 |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 12 |
| 13 |
+------+
6 rows in set (0.00 sec)
2.6二进制日志其他操作
2.6.1 自动清理日志
show variables like '%expire%';
expire_logs_days 0
自动清理时间,是要按照全备周期+1
set global expire_logs_days=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';
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000015 | 1303 |
| mysql-bin.000016 | 194 |
+------------------+-----------+
2 rows in set (0.00 sec)
从1开始
mysql> reset master; ###危险操作
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)注意:不要手动 rm binlog文件
- my.cnf binlog关闭掉,启动数据库
2.把数据库关闭,开启binlog,启动数据库
删除所有binlog,并从000001开始重新记录日志
*reset master; 主从关系中，主库执行此操作，主从环境必崩
2.6.3 binlog的滚动机制
2
flush logs;
重启数据库
select @@max_binlog_size;
备份时,某些参数会触发.
3.慢日志（slow-log）
简介
记录运行较慢的语句记录：slowlog
功能是辅助优化的工具日志
应激性的慢---> show processlist;
一段时间慢 --->slow记录，统计
3.1 配置
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
mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 0.100000 |
+-------------------+
1 row in set (0.00 sec)
3.3 慢语句模拟
set sql_log_bin=0;
source /tmp/t100w.sql;
set sql_log_bin=1;
mysql> select count(*) from t100w;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.32 sec)
随便模拟
select * from t100w where k1='aa' limit 10;
select * from t100w where k1!='aa' limit 10;
select * from t100w where k1!='aa' order by dt limit 10;
然后我们可以去cd /data/3306/data/ vim db01-slow.log文件查看记录的慢语句
分析处理慢语句
mysqldumpslow -s c -t 5 /data/3306/data/db01-slow.log
-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；
pt-query-digest工具
https://blog.csdn.net/xiaoweite1/article/details/80299754
分析慢日志，二进制日志，错误日志...
