走向DBA之日志管理
2019-06-25 本文已影响0人
国王12
开局先放一张图
pppppboard.png
一、日志作用：
排错、数据恢复、优化
1.1错误日志：（默认就是开启状态）
1.1.1查看MySQL错误日志的路径
mysql> select @@log_error;
+---------------+
| @@log_error |
+---------------+
| ./mysql52.err |
+---------------+
1 row in set (0.00 sec)
mysql> select @@datadir;
+-------------------+
| @@datadir |
+-------------------+
| /data/mysql/data/ |
+-------------------+
1 row in set (0.00 sec)
1.1.2错误日志默认路径：
数据路径下/主机名.err /data/mysql/data/mysql52.err
1.1.3修改错误日志路径
vim /etc/my.cnf
log_error=xxxx
重启MySQL生效
1.1.4错误日志使用：
主要查看错误日志中[error]字段的上下文
1.2数据恢复 binlog(binary logs 二进制日志*****)
1.2.1作用：
数据恢复不可缺少的日志文件（默认不开启）
主从复制
1.2.2配置二进制日志说明
log_bin 打开二进制开关和设定存放位置
server_id 5.6单机不需要。5.7必须要加。
注意：二进制日志要和数据分开存放
（日志是用来恢复数据的，你把日志和数据放一块，数据丢了，那日志也就丢了）
（分开放不是放不同的目录，是放在两块磁盘上）
1.2.3具体配置二进制日志
mkdir -p /data/binlog
vim /etc/my.cnf
log_bin=/data/binlog/mysql-bin
server_id=6
重启MySQL生效
注意：mysql-bin可以随便起，作为日志的前缀
server_id 主要作为主从复制时MySQL的唯一标识
1.2.4如何查看配置
show variables like '%log_bin%';
1.2.5binlog记录了什么？
记录了数据库中所有变更类的操作
DDL 定义类语言 原封不动的记录当前DDL语句
DCL 控制类语言 原封不动的记录当前DCL语句
DML (IUD)
前提：已经提交的事务IUD
1.2.6关于binlog记录格式
(1)ROW(5.7 默认) : RBR(ROW based replication) ：行记录模式，记录数据行的变化(用户看不懂，需要工具分析)
(2)statement(5.6默认) : SBR(statement based replication) ：语句记录模式，语句模式原封不动的记录当前DML。
(3)mixed（混合）MBR(mixed based replication)模式 ：以上两种模式的混合
面试题：RBR和SBR的区别
RBR,逐行记录日志，日志量很大，可读性差，但是够严谨，不会出现记录错误
SBR,只记录语句本身，日志量很小，可读性较强，对于函数类的操作，将来恢复时会造成错误
5.7 版本 默认RBR ，是企业建议模式
1.2.7查看当前binlog记录格式
mysql> select @@binlog_format;
5.7默认RBR
1.3二进制日志事件
3.1事件说明
事件是二进制日志的最小记录单元
对于DDL,DCL,一个语句就是一个event
对于DML语句来讲:只记录已提交的事务。
例如以下列子,就被分为了4个event
position号码(为了后期截取日志使用)
begin; 120 - 340
DML1 340 - 460
DML2 460 - 550
commit; 550 - 760
四条语句完成了一个事务，一条语句就是一个event(事件)
3.2event构成
三部分构成:
(1) 事件的开始标识
(2) 事件内容
(3) 事件的结束标识
Position:
开始标识: at 194
结束标识: end_log_pos 254
194? 254?
某个事件在binlog中的相对位置号
位置号的作用是什么？
为了方便我们截取事件
查看二进制日志所在位置
mysql> show variables like '%log_bin%';
[root@mysql52 /data/binlog]# ll 这个路径在my.cnf里设置
total 8
-rw-r----- 1 mysql mysql 154 Jun 25 09:34 mysql-bin.000001 这个编号或者说文件每次重启数据库都会生成新的文件，编号加一
-rw-r----- 1 mysql mysql 30 Jun 25 09:34 mysql-bin.index
用的比较多的查看二进制日志的方法：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 322 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 322 |
+------------------+-----------+
2 rows in set (0.00 sec)
有两个(可以有无限个)
log_name： 目前MySQL存在的二进制日志名字
file_size: 目前MySQL用到了哪个position号
查看二进制日志内容
D3L$_6IDZOWP9Q1BO_LQFOG.png
查看二进制日志事件 (event)
show master status; 确认当前在用的binlog
show binlog events in '日志名' 查看x号binlog的事件
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 6 | 154 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
说明
log_name: 日志名
Pos : 事件开始的position****
Event_type : 事件类型
Server_id : 发生在哪台机器上的事件
End_log_pos ： 事件结束的位置号*****
Info : 事件内容*****
前154是5.7默认头格式，不能删除
查看二进制日志内容
mysqlbinlog mysql-bin.000002 |grep -v "SET" >/tmp/aa.txt
把二进制日志排除set行（set行无用）打进到一个文件，然后看文件。
mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002
第二种查看方法（细看）
基于二进制日志数据恢复演练
如何按需截取日志
1.基于position号的截取*****
1.基于position号的截取*****
--start-position=xxx
--stop-position=xxx
截取二进制日志的核心在于找起点和终点
mysql> create database oldboy1; 建库
Query OK, 1 row affected (0.00 sec)
mysql> show binlog events in 'mysql-bin.000002'; 查看二进制日志找到创建语句的号
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000002 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 219 | Query | 6 | 322 | create database oldboy1 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
4 rows in set (0.00 sec)
mysqlbinlog --start-position=219 --stop-position=322 /data/binlog/mysql-bin.000002
mysqlbinlog --start-position=219 --stop-position=322 /data/binlog/mysql-bin.000002 >/tmp/bin.sql
截取对应日志的号到一个文件
mysql> drop database oldboy1; 删库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
+--------------------+
10 rows in set (0.00 sec)
mysql> set sql_log_bin=0; 先关闭记录，就是不记录下边恢复的日志
mysql> source /tmp/bin.sql 恢复
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| oldboy1 |
+--------------------+
11 rows in set (0.00 sec)
mysql> set sql_log_bin=1; 恢复到记录模式
2. 基于时间点的截取(了解)
--start-datetime
--stop-datetime
for example: 2004-12-25 11:25:56
binlog日志的GTID新特性
什么是GTID（全局事务编号）
5.6 版本新加的特性,5.7中做了加强
5.6 中不开启,没有这个功能.
5.7 中的GTID,即使不开也会有自动生成
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
是对于一个已提交事务的编号，并且是一个全局唯一的编号。
它的官方定义如下：
GTID = source_id ： transaction_id
27a3ae28-8dac-11e9-8e94-000c297eff65 : 29
说明：
DDL DCL ，一条语句（事件）就是一个事务，占一个GTID号
DML语句，一个完整的事务（begin --->commint），占一个GTID号
GTID参数配置（开启GTID）
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
重启生效
查看GTID前半部分：
[root@mysql52 /data/mysql/data]# cat auto.cnf
[auto]
server-uuid=27a3ae28-8dac-11e9-8e94-000c297eff65
模拟查看GTID号增长
mysql> create databases gg; 创建一个库
mysql> show master status; 查看GTID对应的事务值
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000003 | 307 | | | 27a3ae28-8dac-11e9-8e94-000c297eff65:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
mysql-bin.000003 这个东西每次重启数据库都会增加一
最后一列表示GTID号和开启GTID之后，总共发生了多少事务
mysql> use gg 进入
mysql> create table t1 (id int); 建表插入数据
mysql> insert into t1 values(1);
mysql> commit;
mysql> insert into t1 values(2);
mysql> commit;
mysql> insert into t1 values(3);
mysql> commit;
mysql> mysql> show master status; 查看GTID发生了多少事务
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 | 1213 | | | 27a3ae28-8dac-11e9-8e94-000c297eff65:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000003';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000003 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= '27a3ae28-8dac-11e9-8e94-000c297eff65:1' |
| mysql-bin.000003 | 219 | Query | 6 | 307 | create database gg |
| mysql-bin.000003 | 307 | Gtid | 6 | 372 | SET @@SESSION.GTID_NEXT= '27a3ae28-8dac-11e9-8e94-000c297eff65:2' |
| mysql-bin.000003 | 372 | Query | 6 | 466 | use `gg`; create table t1 (id int) |
| mysql-bin.000003 | 466 | Gtid | 6 | 531 | SET @@SESSION.GTID_NEXT= '27a3ae28-8dac-11e9-8e94-000c297eff65:3' |
| mysql-bin.000003 | 531 | Query | 6 | 601 | BEGIN |
| mysql-bin.000003 | 601 | Table_map | 6 | 644 | table_id: 108 (gg.t1) |
| mysql-bin.000003 | 644 | Write_rows | 6 | 684 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000003 | 684 | Xid | 6 | 715 | COMMIT /* xid=15 */ |
| mysql-bin.000003 | 715 | Gtid | 6 | 780 | SET @@SESSION.GTID_NEXT= '27a3ae28-8dac-11e9-8e94-000c297eff65:4' |
| mysql-bin.000003 | 780 | Query | 6 | 850 | BEGIN |
| mysql-bin.000003 | 850 | Table_map | 6 | 893 | table_id: 108 (gg.t1) |
| mysql-bin.000003 | 893 | Write_rows | 6 | 933 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000003 | 933 | Xid | 6 | 964 | COMMIT /* xid=17 */ |
| mysql-bin.000003 | 964 | Gtid | 6 | 1029 | SET @@SESSION.GTID_NEXT= '27a3ae28-8dac-11e9-8e94-000c297eff65:5' |
| mysql-bin.000003 | 1029 | Query | 6 | 1099 | BEGIN |
| mysql-bin.000003 | 1099 | Table_map | 6 | 1142 | table_id: 108 (gg.t1) |
| mysql-bin.000003 | 1142 | Write_rows | 6 | 1182 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000003 | 1182 | Xid | 6 | 1213 | COMMIT /* xid=19 */ |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
21 rows in set (0.00 sec)
结尾就会出现GTID号和对应的事务号
删除库
mysql> drop database gg;
截取事务号，准备恢复
[root@mysql52 ~]# mysqlbinlog --include-gtids='27a3ae28-8dac-11e9-8e94-000c297eff65:1-5' /data/binlog/mysql-bin.000003 >/tmp/ceshi.sql
坑
注意，以上截取的日志文件不能直接恢复
幂等性，GTID检查自己有1-5号事务，就不操作了。
把这些文件拿到别的机器，可以恢复，因为GTID内没有发生过这些事务
正确的截取方法是：
一定要加--skip-gtids参数，不然数据无法恢复
mysqlbinlog --skip-gtids --include-gtids='27a3ae28-8dac-11e9-8e94-000c297eff65:1-5' /data/binlog/mysql-bin.000003 >/tmp/gtid.sql
--skip-gtids 这个参数避免的幂等性，就是告诉GTID不要检查历史事务了，直接操作就行了
临时关闭日志记录功能
mysql> set sql_log_bin=0;
恢复
恢复
mysql> source/tmp/gtid.sql;
别忘了恢复日志记录功能
mysql> set sql_log_bin=1;
截取一到五事务，跳过2和4.
连续跳过的可以写成x-x，不连续只能一个一个写，如下操作
参数是--exclude-gtids
跳过某些gtid不截取(跳过2和4)
[root@db01 ~] mysqlbinlog --skip-gtids --include-gtids='27a3ae28-8dac-11e9-8e94-000c297eff65:1-5' --exclude-gtids='27a3ae28-8dac-11e9-8e94-000c297eff65:2,27a3ae28-8dac-11e9-8e94-000c297eff65:4' /data/binlog/mysql-bin.000003 >/tmp/gtid.sql
二进制日志其他操作
管理二进制日志记录
临时关闭二进制日志记录（会话级别）： set sql_log_bin=0
一般用户恢复数据前，恢复数据之后，改回来，或者退出当前会话即可恢复 =1也行
自动清理：
过期 日志 天数
mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
设置日期至少是一个全备周期+1 企业建议是至少两个全备周期+1
设置方法：
临时的，退出重进生效，重启MySQL失效
set global expire_logs_days=8;
永久生效：
vim /etc/my.cnf
expire_logs_days=8
手工清理
注意千万不要rm删除，否则数据库容易起不来
手工清理
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
PURGE BINARY LOGS TO 'mysql-bin.000010'; 000010之前的日志文件全部删除、删除到1000010为止
reset master; 彻底删除所有binlog,从00001开始，超级危险，不要用。。。
日志滚动：
mysql> show variables like '%max_binlog_size%';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)
查看日志达到多大自动滚动（000001满了，变成000002），一般可以设置为128M自动滚动
手动滚动：让日志编号（00001变成00002.。。）
1.重启数据库
2.flush logs
3.mysqladmin -uroot -p123456 flush-logs
slow_log 慢日志
作用
记录慢SQL语句的日志,定位低效SQL语句的工具日志
开启慢日志(默认没开启)
查看是否开启：
mysql> select @@slow_query_log;
开启慢日志记录：
slow_query_log=1 写入配置文件永久生效 单位是秒，根据具体情况具体定义
set global slow_query_log=1; 临时生效全局会话
查看慢日志默认位置：
mysql> select @@slow_query_log_file;
查看慢日志时间判断（多久查询到算慢语句）
mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
| 10.000000 |
+-------------------+
1 row in set (0.00 sec)
关于慢日志设置写入配置文件
slow_query_log=1 开启慢日志记录
slow_query_log_file=/data/mysql/slow.log 慢日志路径
long_query_time=0.1 0.1秒以上为慢语句进行记录
log_queries_not_using_indexes 记录没有走索引的语句
分析慢日志
mysqldumpslow -s c -t 10 /data/mysql/slow.log
第三方工具
https://www.percona.com/downloads/percona-toolkit/LATEST/
yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5
toolkit工具包中的命令:
./pt-query-diagest /data/mysql/slow.log
Anemometer基于pt-query-digest将MySQL慢查询可视化
