MySQL日志管理学习笔记day09

作者: 我要笑 | 来源:发表于2019-09-28 20:20 被阅读0次

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
分析慢日志，二进制日志，错误日志...