走向DBA之日志管理

开局先放一张图

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慢查询可视化

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,684评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 87,143评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,214评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,788评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,796评论 5赞 368
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,665评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,027评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,679评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 41,346评论 1赞 299
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,664评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,766评论 1赞 331
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,412评论 4赞 321
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,015评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,974评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,203评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,073评论 2赞 350
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,501评论 2赞 343

走向DBA之日志管理

开局先放一张图

一、日志作用：

1.1错误日志：（默认就是开启状态）

1.1.1查看MySQL错误日志的路径

1.1.2错误日志默认路径：

1.1.3修改错误日志路径

1.1.4错误日志使用：

1.2数据恢复 binlog(binary logs 二进制日志*****)

1.2.1作用：

1.2.2配置二进制日志说明

1.2.3具体配置二进制日志

1.2.4如何查看配置

1.2.5binlog记录了什么？

1.2.6关于binlog记录格式

面试题：RBR和SBR的区别

1.2.7查看当前binlog记录格式

1.3二进制日志事件

3.1事件说明

3.2event构成

查看二进制日志所在位置

用的比较多的查看二进制日志的方法：

查看二进制日志内容

查看二进制日志事件 (event)

查看二进制日志内容

基于二进制日志数据恢复演练

如何按需截取日志

1.基于position号的截取*****

2. 基于时间点的截取(了解)

binlog日志的GTID新特性

什么是GTID（全局事务编号）

说明：

GTID参数配置（开启GTID）

查看GTID前半部分：

模拟查看GTID号增长

删除库

截取事务号，准备恢复

坑

正确的截取方法是：

临时关闭日志记录功能

恢复

别忘了恢复日志记录功能

截取一到五事务，跳过2和4.

二进制日志其他操作

管理二进制日志记录

自动清理：

日志滚动：

手动滚动：让日志编号（00001变成00002.。。）

slow_log 慢日志

作用

开启慢日志(默认没开启)

查看是否开启：

开启慢日志记录：

查看慢日志默认位置：

查看慢日志时间判断（多久查询到算慢语句）

关于慢日志设置写入配置文件

分析慢日志

第三方工具

推荐阅读更多精彩内容