美文网首页
走向DBA之日志管理

走向DBA之日志管理

作者: 国王12 | 来源:发表于2019-06-25 21:24 被阅读0次

开局先放一张图

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

相关文章

  • 走向DBA之日志管理

    开局先放一张图 一、日志作用： 1.1错误日志：（默认就是开启状态） 1.1.1查看MySQL错误日志的路径 1....

  • 走向DBA之基础管理(用户、权限)

    一、用户管理 1.用户作用： 登录，管理数据库对象（库、表..） 2.用户定义： 用户名@'白名单（允许登录的IP...

  • 走向DBA之索引

    前言：如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车...

  • DBA与MySQL简介

    1、MySQL-DBA工作职责 开发DBA 运维DBA 初级 安装、搭建 中级 数据库管理员、用户权限、索引管理、...

  • 走向DBA之存储引擎(1)

    一、存储引擎介绍： 二、功能了解： 三、存储引擎种类： 3.1Oracle的MySQL 3.2MySQL支持的其他...

  • 走向DBA之存储引擎（2）

    六、InnoDB存储引擎物理存储结构 以上文件具体的存放的东西或作用： 6.1表空间(Tablespace) 6....

  • 走向DBA之SQL语句介绍

    一、SQL介绍：（Structured Query Language） 结构化查询语言SQL标准：SQL92（92...

  • 走向DBA之SQL语句（DQL）

    select 查看 一、作用： 获取MySQL中的数据行信息 二、单独使用select 2.1获取参数信息 语法：...

  • 无锡DBA工商管理博士选择什么学校

    无锡DBA工商管理博士选择什么学校 DBA就是工商管理博士，代表工商管理方向的最高学位，现在市面上DBA学校鱼龙混...

  • 走向DBA之基础管理（连接、初始化配置）

    一、自带客户端工具连接和远程图形化连接 一、自带客户端连接工具： mysql命令连接，参数介绍： 二、远程客户端图...

网友评论

      本文标题：走向DBA之日志管理

      本文链接：https://www.haomeiwen.com/subject/hynxcctx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|走向DBA之日志管理|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！