MySQL日志管理

最新推荐文章于 2024-09-24 09:50:23 发布

一个憨豆

最新推荐文章于 2024-09-24 09:50:23 发布

阅读量155

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_52636854/article/details/114762308

版权

数据库专栏收录该内容

17 篇文章 0 订阅

订阅专栏

日志管理

在这里插入图片描述

一、错误日志

1.作用

记录MySQL从启动以来，所有的状态，警告，错误。
为我们定位数据库问题，提供帮助

2.配置方法

默认：开启状态
位置：datadir

#查看存放位置：
mysql> select @@datadir;
+----------------------+
| @@datadir            |
+----------------------+
| /service/mysql/data/ |
+----------------------+

#定制方法：
--永久生效：
vim/etc/my.cnf 
log_error=/service/mysql/data/mysql.err
重启数据库生效
--说明：日志目录必须提前有，并且mysql有权限写入
--注意：如果写了之后不能重启数据库，解决方法有两种
方法一：用mysqld去启动，页面会显示错误信息
方法二：可能是跟其他的文件重名，更改错误日志的文件名

3.错误日志的查看方法：

2021-03-04T12:20:54.159500Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

二、binlog二进制日志（复制日志）

1.作用

主要记录数据库变化（DDL，DCL，DML）性质的日志
数据恢复，主从复制中应用

2.如何配置

默认：
--8.0版本以前，没有开启，建议是要打开的
--8.0以后，都是自动开启的

#配置方法：
	vim /etc/my.cnf
	server_id=6                            #主机编号，主从复制中使用，5.7以后开binlog要加入这个参数
	log-bin=/service/mysql/data/mysql-bin  #日志存放目录+日志名前缀，例如：mysql—bin.0001
	sync_binlog=1                          #binlog日志刷新策略，双一中的第二个一，每次事务提交立即刷写到磁盘
	binlog_format=row                      #binlog的记录格式为row模式
重启生效

#配置文件（vim /etc/my.cnf）
[mysqld]
user=mysql
basedir=/service/mysql
datadir=/service/mysql/data
server_id=6
port=3306
socket=/service/mysql/mysql.sock
log-error=/service/mysql/data/mysql.err
log-bin=/service/mysql/data/mysql-bin
sync_binlog=1
binlog_format=row
secure-file-priv=/tmp
explicit_defaults_for_timestamp=true

[mysql]
socket=/service/mysql/mysql.sock

#说明：一定要和数据盘分开
例如：
/dev/sdb ---> /data/3306
/dev/sdc ---> /data/binlog

3.binlog记录内容详解

3.1引入

binlog是SQL层的功能。记录的是变更SQL语句，不记录查询语句。

3.2记录SQL语句种类

DDL ：原封不动的记录当前DDL(statement语句方式)。
DCL ：原封不动的记录当前DCL(statement语句方式)。
DML ：只记录已经提交的事务DML（insert，update，delete）

3.3 DML三种记录方式

binlog_format（binlog的记录格式）参数影响
（1）statement（5.6默认）SBR(statement based replication) ：语句模式原封不动的记录当前DML。
（2）ROW(5.7 默认值) RBR(ROW based replication) ：记录数据行的变化(用户看不懂，需要工具分析)
（3）mixed（混合）MBR(mixed based replication)模式  ：以上两种模式的混合

面试题

SBR与RBR模式的对比
STATEMENT：可读性较高，日志量少，但是不够严谨
ROW      ：可读性很低，日志量大，足够严谨
update t1 set xxx=xxx where id>1000   ? -->一共500w行，row模式怎么记录的日志
--逐行记录
    
#为什么row模式严谨？
id  name    intime
insert into t1 values(1,'zs',now())
我们建议使用：row记录模式

4.event（事件）是什么?

4.1事件的简介

二进制日志的最小记录单元
对于DDL,DCL,一个语句就是一个event
对于DML语句来讲:只记录已提交的事务。

#例如以下列子,就被分为了4个event
begin;      120  - 340
DML1        340  - 460
DML2        460  - 550
commit;     550  - 760

4.2 event的组成

三部分构成:
(1) 事件的开始标识
(2) 事件内容
(3) 事件的结束标识

Position:
开始标识: at 194
结束标识: end_log_pos 254

#194 254
某个事件在binlog中的相对位置号

#位置号的作用是什么？
为了方便我们截取事件

file：当前MySQL正在使用的文件名
Position：最后一个事件的结束位置号

5.binlog日志的查看

5.1查看日志的开启情况

#log_bin参数设置的路径,可以找到二进制日志
mysql> show variables like '%base%';
+------------------------+------------------------------------+
| Variable_name          | Value                              |
+------------------------+------------------------------------+
| basedir                | /service/mysql/                    |
| character_set_database | latin1                             |
| collation_database     | latin1_swedish_ci                  |
| log_bin_basename       | /service/mysql/data/mysql-bin      |
| relay_log_basename     | /service/mysql/data/db02-relay-bin |
| skip_show_database     | OFF                                |
+------------------------+------------------------------------+
  
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+
#结果是1，代表已经开启

mysql> select @@log_bin_basename;
+-------------------------------+
| @@log_bin_basename            |
+-------------------------------+
| /service/mysql/data/mysql-bin |
+-------------------------------+

5.2 文件查看

/service/mysql/data

-rw-r----- 1 mysql mysql      177 3月   4 20:02 mysql-bin.000001
-rw-r----- 1 mysql mysql      201 3月   4 20:07 mysql-bin.000002
-rw-r----- 1 mysql mysql      445 3月   4 20:20 mysql-bin.000003
-rw-r----- 1 mysql mysql      177 3月   4 20:20 mysql-bin.000004
-rw-r----- 1 mysql mysql   691154 3月   9 21:22 mysql-bin.000005
-rw-r----- 1 mysql mysql      177 3月  10 10:44 mysql-bin.000006
-rw-r----- 1 mysql mysql      177 3月  10 10:45 mysql-bin.000007
-rw-r----- 1 mysql mysql      154 3月  10 10:45 mysql-bin.000008
-rw-r----- 1 mysql mysql      296 3月  10 10:45 mysql-bin.index

5.3二进制内置查看命令

5.3.1 查看目前有几个日志文件

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       445 |
| mysql-bin.000004 |       177 |
| mysql-bin.000005 |    691154 |
| mysql-bin.000006 |       177 |
| mysql-bin.000007 |       177 |
| mysql-bin.000008 |       154 |
+------------------+-----------+

5.3.2 查看当前在使用的binlog

mysql> mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

5.3.3

3. 查看二进制事件
mysql> show binlog events in 'mysql-bin.000008';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000008 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000008 | 123 | Previous_gtids |         6 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

#例子：
mysql> use world
mysql> begin;
mysql> delete from city where id=10;
mysql> commit;
mysql> show binlog events in 'mysql-bin.000008';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000008 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000008 | 123 | Previous_gtids |         6 |         154 |                                       |
| mysql-bin.000008 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000008 | 219 | Query          |         6 |         292 | BEGIN                                 |
| mysql-bin.000008 | 292 | Table_map      |         6 |         350 | table_id: 108 (world.city)            |
| mysql-bin.000008 | 350 | Delete_rows    |         6 |         420 | table_id: 108 flags: STMT_END_F       |
| mysql-bin.000008 | 420 | Xid            |         6 |         451 | COMMIT /* xid=15 */                   |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
        
 Log_name：binlog文件名
Pos：开始的position    *****
Event_type：事件类型
Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息
Server_id：mysql服务号标识
End_log_pos：事件的结束位置号 *****
Info：事件内容*****
补充:
SHOW BINLOG EVENTS
   [IN 'log_name']
   [FROM pos]
   [LIMIT [offset,] row_count]
[root@db01 binlog]# mysql -e "show binlog events in 'mysql-bin.000004'" |grep drop

5.4 binlog文件内容详细查看

[root@db01 data]# mysqlbinlog mysql-bin.000008 >/tmp/a.sql
[root@db01 data]# vim /tmp/a.sql 

#针对于DDL：
上一个at和下一个at之间的内容
# at 292
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;

#针对于DML：
# at 292
#210310 11:01:48 server id 6  end_log_pos 350 CRC32 0x8393069d  Table_map: `world`.`city` mapped to number 108
# at 350
#210310 11:01:48 server id 6  end_log_pos 420 CRC32 0xe3c95c9a  Delete_rows: table id 108 flags: STMT_END_F

BINLOG '
nDZIYBMGAAAAOgAAAF4BAAAAAGwAAAAAAAEABXdvcmxkAARjaXR5AAUD/v7+Awb+af4J/jwAnQaT
gw==
nDZIYCAGAAAARgAAAKQBAAAAAGwAAAAAAAEAAgAF/+AKAAAAB1RpbGJ1cmcDTkxEDU5vb3JkLUJy
YWJhbnTW8gIAmlzJ4w==
'/*!*/;
# at 420
#210310 11:01:54 server id 6  end_log_pos 451 CRC32 0xa18a8a54  Xid = 15
COMMIT/*!*/;

[root@db02 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000008 >/tmp/b.sql
[root@db02 data]# vim /tmp/b.sql

6.binlog日志恢复案例

6.1日志截取恢复

#1.刷新滚动一个新的日志
mysql> flush logs;

#2.模拟数据环境（没有备份的情况）
1）环境准备
mysql> create database hhh charset utf8mb4;
mysql> use hhh
mysql> create table h1(id int);
mysql> begin;
mysql> insert into h1 values(1),(2),(3);
mysql> commit;
mysql> begin;
mysql> insert into h1 values(66),(88),(99);
mysql> commit;
mysql> select * from h1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   66 |
|   88 |
|   99 |
+------+

2）模拟删库
mysql> drop database hhh;

3）数据恢复
--分析binlog
mysql> show master status;
mysql-bin.000010            |                  |     
--起点:
mysql> show binlog events in 'mysql-bin.000010';
6 |         326 | create database hhh charset utf8mb4 
--终点：
6 |        1162 | drop database hhh       

4)截取日志
[root@db01 data]# mysqlbinlog --start-position=326 --stop-position=1162 mysql-bin.000010 > /tmp/c.sql 

5）恢复日志
mysql> set sql_log_bin=0;              #在此窗口临时不记录binlog
mysql> source /tmp/c.sql
mysql> set sql_log_bin=1;

6）验证数据
mysql> show database;
mysql> select * from hhh.h1;

问题

1.binlog记录不单单一个数据的操作，可能对其他数据库重复操作？
#解决方法可以用-d参数
mysqlbinlog -d --start-position=326 --stop-position=1162 mysql-bin.000010 > /tmp/c.sql 

2.需要的日志在多个文件中分布
起点：假如，mysql-bin.000001,5200
终点：一般是最后一个文件，例如：mysql-bin.000009   666
mysqlbinlog --start-datetime=  --stop-datetime=   mysql-bin.000001  mysql-bin.000009 

3.创建了几年，期间一直在用的数据，插入数据的操作总bin_log.000001-bin_log.00000N之中都有的库，被删了要怎么恢复，数据行多

假设：每周六23:59做全备份，binlog每天备份23:00
故障点： 周三 10点 drop操作
binlog实际上是我们数据恢复配合备份一起恢复数据的手段

7. binlog维护操作

7.1 日志滚动

1）flush logs;
2）mysql> select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
|        1073741824 |
+-------------------+
3）mysqladmin -u root -p 123 flush-logs;
4）mysqldump -F
5）重启数据库自动滚动

7.2 日志的删除之自动删除和手动删除

***注意：不要使用rm命令删除日志
1）自动删除机制
mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  0 |
+--------------------+

***默认是0，单位是天，代表永不删除
#问题：设置多少天比较合适？阈值？
一个全备周期。
7+1天
一般生产建议最少2个全备周期+1

2）手工删除
例：
mysql> pure binary logs to 'mysql-bin.000005';
mysql> show binary logs;

3)全部清空
mysql> reset master；
比较危险，在主库执行此操作，主从必宕

8.binlog的GTID模式管理

8.1GTID 介绍

5.6 版本新加的特性,5.7中做了加强
5.6 中不开启,没有这个功能.
5.7 中的GTID,即使不开也会有自动生成
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'

8.2 GTID(Global Transaction ID)

是对于一个已提交事务的编号，并且是一个全局唯一的编号。
它的官方定义如下：

GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29

8.3重要参数介绍：

vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
重启数据库生效

#开启gtid
mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON          |
+-------------+

#例子：
mysql> show master status;
mysql> create database gtid_txt;
mysql> show master status;
mysql>  select@@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 7bcd54ca-7ce1-11eb-91ef-000c296d3dea |
+--------------------------------------+

8.4基于GTID进行查看binlog

具备GTID后,截取查看某些事务日志:
--include-gtids
--exclude-gtids
--skip-gtids
    
mysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4'  /data/mysql-bin.000004

#例子：
--第一波命令：
mysql> show master status;
mysql> create database gtab charset utf8mb4;
mysql> use gtab;
mysql> create table h1 (id int);
mysql> begin;
mysql> insert into h1 values (66),(88),(99);
mysql> commit;
mysql> flush logs;
mysql> show master status;
--第二波命令
mysql> create table h2(id int);
mysql> begin;
mysql> insert into h2 values(12),(18),(20);
mysql> commit;
mysql> flush logs;
mysql> show master status;
--第三波命令
mysql> create table h3(id int);
mysql> begin;
mysql> insert into h3 values(22),(33),(44);
mysql> commit;
mysql> show master status;
mysql> drop database gtab;
--截取日志
mysql> show master status;
mysql-bin.000011
---起点：
| mysql-bin.000011 | 2081 | Query          |         6 |        2191 | create database gtab charset utf8mb4                               |
| mysql-bin.000011 | 2191 | Gtid           |         6 |        2256 | SET @@SESSION.GTID_NEXT= '7bcd54ca-7ce1-11eb-91ef-000c296d3dea:11' |

---终点：
mysql> show binlog events in 'mysql-bin.000013';
| mysql-bin.000013 | 619 | Gtid           |         6 |         684 | SET @@SESSION.GTID_NEXT= '7bcd54ca-7ce1-11eb-91ef-000c296d3dea:17' |
| mysql-bin.000013 | 684 | Query          |         6 |         776 | drop database gtab

--gtid：11-16
--文件：mysql-bin:mysql-bin.000011-mysql-bin.000013

#截取
[root@db01 data]# mysqlbinlog --skip-gtids='7bcd54ca-7ce1-11eb-91ef-000c296d3dea:11-16' mysql-bin.000011 mysql-bin.000012 mysql-bin.000013 > /tmp/h.sql

8.5 GTID的幂等性

开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了
就想恢复?怎么办?
--skip-gtids

mysqlbinlog --skip-gtids=' 7bcd54ca-7ce1-11eb-91ef-000c296d3dea:11-16' mysql-bin.000011 mysql-bin.000012 mysql-bin.000013 > /tmp/h.sql
set sql_log_bin=0;
source /tmp/binlog.sql
set sql_log_bin=1;

#查看uuid
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid                        |
+--------------------------------------+
| 7bcd54ca-7ce1-11eb-91ef-000c296d3dea |
+--------------------------------------+

三、slow慢日志

1.作用

记录MySQL运行过程中较慢语句的日志,通过一个文本的文件记录下来
帮助我们进行语句优化工具日志

2.如何配置

默认慢日志没有开启
#1.配置参数：
--开关：（0是关闭，1是开启）
mysql> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
|                0 |
+------------------+

--文件位置及名字： 
mysql> select @@slow_query_log_file;
+-----------------------------------+
| @@slow_query_log_file             |
+-----------------------------------+
| /service/mysql/data/db01-slow.log |
+-----------------------------------+

#慢语句认定时间阈值
mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
|         10.000000 |
+-------------------+

#不走索引的语句也会记录下来
mysql> select @@log_queries_not_using_indexes;
+---------------------------------+
| @@log_queries_not_using_indexes |
+---------------------------------+
|                               0 |
+---------------------------------+


#2.调整参数
[root@db01 ~]# vim /etc/my.cnf
slow_query_log=1 
slow_query_log_file= /service/mysql/data/db01-slow.log 
long_query_time=0.1
log_queries_not_using_indexes=1
重启数据库生效

3. 模拟慢语句

#先清空慢日志文件
[root@db01 data]# rm -rf db01-slow.log/*

#第三方工具：mysqldumpslow
-s    排序
-t    top

#例如：
[root@db02 data]# mysqldumpslow -s c -t 5 /service/mysql/data/db01-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
toolkint工具包中的命令:

--可视化展示slow-log
pt-query-gigest + Amemometer
Anemometer基于pt-query-digest将MySQL慢查询可视化

4.慢语句分析

/service/mysql/bin/mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /service/mysql/mysql.sock
Time                 Id Command    Argument
~                                                                           
#可自己自行测试查看分析