MySQL 第六章 日志管理

1.错误日志

1.1作用

MySQL 启动及工作过程中，状态、报错、警告。

1.2配置

命令行：

vim /etc/my.cnf
log_error=/data/3306/data/mysql.log(路径)

数据库操作、查看语句：

mysql[(none)]>select @@log_error;
+---------------------------+
| @@log_error               |
+---------------------------+
| /data/3306/data/mysql.log |
+---------------------------+
1 row in set (0.00 sec)

排错方法，查看日志，[ERROR]中括号报错日志

[root@db01 ~]# tail -5 /data/3306/data/mysql.log 
2019-08-14T17:58:33.363554Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/3306/data/ib_buffer_pool
2019-08-14T17:58:33.365008Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190815  1:58:33
2019-08-14T17:58:33.370941Z 0 [Note] Event Scheduler: Loaded 0 events
2019-08-14T17:58:33.371046Z 0 [Note] /application/mysql/bin/mysqld: ready for connections.
Version: '5.7.26'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

2.二进制日志

2.1作用

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

2.2配置

修改配置文件

vim /etc/my.cnf
server_id=6
log_bin=/data/binlog/mysql-bin

log_bin详解：
/data/bin_log/     路径 
/mysql-bin   文件前缀

创建目录授权

[root@db01 ~]# mkdir -p /data/binlog
[root@db01 ~]# chown -R mysql.mysql /data/binlog

[root@db01 /data]# ls -l binlog
total 8
-rw-r----- 1 mysql mysql 154 Aug 15 02:16 mysql-bin.000001
-rw-r----- 1 mysql mysql  30 Aug 15 02:16 mysql-bin.index

重启数据库

  [root@db01 ~]# systemctl restart mysqld

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

2.3.1引入

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

2.3.2记录语句的种类

DDL ，DCL ，DML

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

DDl，DCL：直接以语句（statement）的方式记录
DML：insert，update，delete（三种模式）

mysql[(none)]>select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)
模式 解
SBR： statement，做什么记录什么
RBR： row，记录数据行的变化，默认模式，推荐
MBR： mixed，自动判断记录模式

面试题：说明SBR和RBR的区别？

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

2.3.5 binlog events（二进制日志事件）

1）简介
二进制日志内容以事件为最小记录单元
对于DDL和DCL，一个DDL语句就是一个事件
对于DML（标准的事务语句）：只记录已提交的事务的DML语句.
事件1：begin;
事件2：a
事件3：b
事件4：commit;
2）事件的构成

[root@db01 binlog]# mysqlbinlog mysql-bin.000001 
#at 219                    事件开始的位置(position)
#190814 18:46:58           事件发生的时间
create database xinixn     事件内容
# End of log file          事件结束的位置
>>中间的set可以省略

2.3.6二进制信息的基本查看

1）二进制日志的配置信息

mysql[(none)]>show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name                   | Value                        |
+---------------------------------+------------------------------+
| log_bin (开关)                  | ON                           |
| log_bin_basename  （位置 ）      | /data/binlog/mysql-bin       |
| log_bin_index                   | /data/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF                          |
| log_bin_use_v1_row_events       | OFF                          |
| sql_log_bin                     | ON                           |
+---------------------------------+------------------------------+
6 rows in set (0.01 sec)

2）二进制日志的基本信息

mysql[(none)]>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       343 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

3）当前正在使用的二进制

mysql[(none)]>show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4）查看二进制日志的事件信息

mysql[(none)]>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 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

2.4 内容查看和截取

2.4.1 内容查看命令
简单查看
[root@db01 /data/binlog]# mysqlbinlog /data/binlog/mysql-bin.000003
人类可读查看
[root@db01 /data/binlog]# mysqlbinlog --base64-output=decode-rows -vv /data/binlog/mysql-bin.000003
查询帮助
[root@db01 /data/binlog]#  mysqlbinlog --help
2.4.2日志的截取
  --start-position
  --stop-position

语法：

mysqlbinlog --start-position=xx  --stop-position=xx /data/binlog/mysql-bin.000003 >/tmp/m.sql

演练：
(1) 准备数据

oldguo[(none)]>create database binlog charset utf8mb4;
oldguo[(none)]>use binlog;
oldguo[binlog]>create table t1(id int)engine=innodb charset=utf8mb4;

mysql[binlog]>insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql[binlog]>insert into t1 values(4),(5),(6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql[binlog]>select  * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0.00 sec)

(2)搞破坏

oldguo[binlog]>drop database binlog;

(3）确认起点和终点:

查看当前二进制文件
mysql[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1320 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
查看事件
oldguo[(none)]>show binlog events in 'mysql-bin.000006';
起点
| mysql-bin.000003 |  219 | Query          |         6 |         335 | create database binlog charset utf8mb4    
终点
| mysql-bin.000003 | 1222 | Query          |         6 |        1320 | drop database binlog

(4)截取日志

[root@db01 /data/binlog]# mysqlbinlog --start-position=219  --stop-position=1222 /data/binlog/mysql-bin.000003 >/tmp/bin.sql

(5)恢复日志

[root@db01 /data/binlog]# set sql_log_bin=0;  ##  临时关闭当前会话的binlog记录

mysql[(none)]>source /tmp/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)
......

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
b9a89249-b7e5-11e9-a416-000c29a21234:1

2.5.2配置
  enforce_gtid_consistency=true  ## 强制GTID一致性
  gtid_mode=on  ##  开关
  log_slave_updates=1  ## 主从复制中从库记录logbin，并同意GTID信息
2.5.3查看gtid信息

DDL,DCL 一个操作就是GTID
DML 一组事务一个GTID

mysql[(none)]>show master status ;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000006 |      300 |              |                  | b9a89249-b7e5-11e9-a416-000c29a21234:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
2.5.4基于gtid截取日志

--include-gtids= 截取
--exclude-gtids=排除
--skip-gtids 跳过已记录的gtid信息

截取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)创建库，表，添加信息
2）查看记录信息


mysql[(none)]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000006 |     1748 |              |                  | b9a89249-b7e5-11e9-a416-000c29a21234:1-8 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql[(none)]>show binlog events in 'mysql-bin.000006';
| mysql-bin.000006 |  876 | Gtid           |         6 |         941 | SET @@SESSION.GTID_NEXT= 'b9a89249-b7e5-11e9-a416-000c29a21234:5' |
| mysql-bin.000006 |  941 | Query          |         6 |        1051 | create database gtid charset utf8mb4                              |
| mysql-bin.000006 | 1051 | Gtid           |         6 |        1116 | SET @@SESSION.GTID_NEXT= 'b9a89249-b7e5-11e9-a416-000c29a21234:6' |
| mysql-bin.000006 | 1116 | Query          |         6 |        1229 | use `gtid`; create table gtid (id int)engine=innodb               |
| mysql-bin.000006 | 1229 | Gtid           |         6 |        1294 | SET @@SESSION.GTID_NEXT= 'b9a89249-b7e5-11e9-a416-000c29a21234:7' |
| mysql-bin.000006 | 1294 | Query          |         6 |        1366 | BEGIN                                                             |
| mysql-bin.000006 | 1366 | Table_map      |         6 |        1413 | table_id: 110 (gtid.gtid)                                         |
| mysql-bin.000006 | 1413 | Write_rows     |         6 |        1463 | table_id: 110 flags: STMT_END_F                                   |
| mysql-bin.000006 | 1463 | Table_map      |         6 |        1510 | table_id: 110 (gtid.gtid)                                         |
| mysql-bin.000006 | 1510 | Write_rows     |         6 |        1560 | table_id: 110 flags: STMT_END_F                                   |
| mysql-bin.000006 | 1560 | Xid            |         6 |        1591 | COMMIT /* xid=29 */                                               |
| mysql-bin.000006 | 1591 | Gtid           |         6 |        1656 | SET @@SESSION.GTID_NEXT= 'b9a89249-b7e5-11e9-a416-000c29a21234:8' |
| mysql-bin.000006 | 1656 | Query          |         6 |        1748 | drop database gtid

3)恢复日志

[root@db01 /tmp]# set sql_log_bin=0; 

[root@db01 /tmp]# mysqlbinlog --skip-gtids --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:5-7' /data/binlog/mysql-bin.000006>/data/gtid.sql

mysql[(none)]>source /data/gtid.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)
................

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';

注意:不要手工 rm binlog文件

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

oldguo[(none)]>flush logs;

2.6.4 binlog的滚动机制

flush logs;
重启数据库
select @@max_binlog_size;
备份时,某些参数会触发.

3.慢日志（llow-log）

简介

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

配置

slow_query_log=1
slow_query_log_file =/data/3306/data/db01-slow.log
long_query_time=10.000000(默认)
log_queries_not_using_indexes

分析处理慢语句

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

Reading mysql slow query log from /data/3306/data/db01-slow.log
Count: 4  Time=0.84s (3s)  Lock=0.00s (0s)  Rows=10.0 (40), root[root]@localhost
  select * from t100w where k2 !='S' order by   num desc limit N

Count: 1  Time=0.91s (0s)  Lock=0.00s (0s)  Rows=10.0 (10), root[root]@localhost
  select * from t100w where k2 !='S' order by   num limit N

Died at /application/mysql/bin/mysqldumpslow line 161, <> chunk 5.
扩展

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

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

推荐阅读更多精彩内容

  • mysql日志管理
    日志文件对于一个服务器来说是非常重要的，它记录着服务器的运行信息，许多操作都会写日到日志文件，通过日志文件可以监视...
    时待吾阅读 1,010评论 0赞 5
  • Mysql 日志管理(学习笔记九)
    mysql日志——记录着mysql数据库运行期间的操作和信息 日志作用 (1)当数据库遭到意外损害，可以通过日志文...
    SkTj阅读 626评论 0赞 1
  • 2019-06-25 Day09～Mysql~日志管理及恢复
    1. 上节遗留问题 2.重点参数 innodb_flush_log_at_trx_commit =======...
    Ffvc阅读 652评论 0赞 0
  • mysql日志管理
    mysql日志(默认存放在datadir): 同大多数关系型数据库一样，日志文件是MySQL数据库的重要组成部分。...
    运维阿文阅读 1,025评论 0赞 0
  • MySQL 日志管理
    一、 错误日志   没做特殊说明，所有配置都在my.cnf文件下的 [mysqld] 域下  包含了当mysqld...
    w也不知道阅读 163评论 0赞 0