MySQL日志文件

2021/11/2 19:12:39

编程Tag： mysql 文件日志 id log gtid

本文主要是介绍MySQL日志文件，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

日志文件介绍

1. 错误日志
- 1.1 默认的路径
- 1.2 自己定义位置
2. 二进制日志
- 2.1 配置二进制文件
- 2.2 二进制文件的介绍
- 2.3 二进制文件的记录单元
- 2.4 二进制文件的管理
- 2.5 查看二进制内容
- 2.6 二进制文件的截取
- 2.7 通过二进制文件恢复
- - 2.7.1 模拟数据
  - 2.7.2 模拟故障
  - 2.7.3 分析截取binlog
  - 2.7.4 恢复
3. GTID
- 3.1 开启GTID
- 3.2 GTID的相关参数
- 3.3查看GTID信息
- 3.4 基于GTID，binlog的恢复
- - 3.4.1 日志的截取
  - 3.4.2 恢复
  - - 3.4.2.1 报错
4. 慢日志（slow_log）
- 4.1 配置慢日志
- 4.2 模拟慢查询
- 4.3 分析慢日志
- 4.4 第三方工具

1. 错误日志

1.1 默认的路径

默认就是开启的

datadir/hostname.err

1.2 自己定义位置

vim /etc/my.cnf
log_error=/tmp/mysql3306.log
重启
systemctl restart mysqld

查看
select @@log_error;

2. 二进制日志

作用：
1.主从依赖二进制文件
2.数据恢复的时候需要依赖日志文件

2.1 配置二进制文件

默认没有开启

mkdir /data/binlog
chown -R mysq.mysql/data/binlog

vim /etc/my.cnf
[mysqld]
log_bin=/data/binlog/mysql-bin
binglog_format=row 5.7默认是row，可以不用配置

重启生效
systemctl restart mysqld

2.2 二进制文件的介绍

记录数据库所有的变更类的操作日志
ddl dcl dml
dml记录已提交的事务
dml的记录格式

statement	srb 语句模式，做什么命令，记录什么
row	RBR 行模式，数据行的变化
mixed	MBR 混合模式

SBR和RBR什么区别?怎么选择?
SBR: 可读性较强,对于范围操作日志量少,但是可能会出现记录不准确的情况.
RBR: 可读性较弱,对于范围操作日志大,不会出现记录错误.

2.3 二进制文件的记录单元

最小单元 event 事件
对于ddl语句(create drop alter)，每一个语句就是一个事件
dml (insert delete update)一个事务包含多个语句

2.4 二进制文件的管理

查看
show variables like '%log_bin';

查看所有已经存在的
show binary logs;

flush logs;
show binary logs;

正在使用的
show master status;

查看二进制日志事件

create database yq;
use yq;
create table t1(id int);
insert into t1 values(1);

show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      561 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

show binlog events in 'mysql-bin.000004';
 
 +------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000004 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000004 | 123 | Previous_gtids |         6 |         154 |                                       |
| mysql-bin.000004 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 219 | Query          |         6 |         312 | use `yq`; create table t1(id int)     |
| mysql-bin.000004 | 312 | Anonymous_Gtid |         6 |         377 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 377 | Query          |         6 |         447 | BEGIN                                 |
| mysql-bin.000004 | 447 | Table_map      |         6 |         490 | table_id: 108 (yq.t1)                 |
| mysql-bin.000004 | 490 | Write_rows     |         6 |         530 | table_id: 108 flags: STMT_END_F       |
| mysql-bin.000004 | 530 | Xid            |         6 |         561 | COMMIT /* xid=12 */                   |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
9 rows in set (0.00 sec)

2.5 查看二进制内容

cd /data/binlog
mysqlbinlog mysql-bin.000004

mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000004 

mysqlbinlog -d haoge mysql-bin.000004

2.6 二进制文件的截取

mysqlbinlog --start-position=219 --stop-position=335 mysql-bin.000004 >/tmp/a.sql

2.7 通过二进制文件恢复

2.7.1 模拟数据

create database moni charset ut8mb4;
use moni
create table t1(id int);
insert into t1 values(1);
commit

2.7.2 模拟故障

drop database moni;

2.7.3 分析截取binlog

show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      901 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

show binlog events in 'mysql-bin.000005';

+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000005 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000005 | 123 | Previous_gtids |         6 |         154 |                                       |
| mysql-bin.000005 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000005 | 219 | Query          |         6 |         329 | create database moni charset utf8mb4  |
| mysql-bin.000005 | 329 | Anonymous_Gtid |         6 |         394 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000005 | 394 | Query          |         6 |         491 | use `moni`; create table t1(id int)   |
| mysql-bin.000005 | 491 | Anonymous_Gtid |         6 |         556 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000005 | 556 | Query          |         6 |         628 | BEGIN                                 |
| mysql-bin.000005 | 628 | Table_map      |         6 |         673 | table_id: 109 (moni.t1)               |
| mysql-bin.000005 | 673 | Write_rows     |         6 |         713 | table_id: 109 flags: STMT_END_F       |
| mysql-bin.000005 | 713 | Xid            |         6 |         744 | COMMIT /* xid=28 */                   |
| mysql-bin.000005 | 744 | Anonymous_Gtid |         6 |         809 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000005 | 809 | Query          |         6 |         901 | drop database moni                    |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
13 rows in set (0.00 sec)

找到终点起点截取

mysqlbinlog --start-position=219 --stop-position=809 /data/binlog/mysql-bin.000005 >/tmp/bin.sql

2.7.4 恢复

临时关闭恢复产生的新日志
set sql_log_bin=0;
source /tmp/bin.sql
set sql_log_bin=1;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| moni               |
| mysql              |
| performance_schema |
| sys                |
| yq                 |
+--------------------+
6 rows in set (0.00 sec)


mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

3. GTID

对于binlog的中的每一个事务，都会生成一个GTID号码
GTID是一个自增长的数据，从1开始
GTID的幂等性
当用GTID进行数据的恢复的时候，检查当前系统中有没有相同的GTID号，有相同的就自动跳过
会影响binlog的恢复和主从复制

3.1 开启GTID

vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true

重启

3.2 GTID的相关参数

GTID相关的参数
--skip-gtids 
--include-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:6','d60b549f-9e10-11e9-ab04-000c294a1b3b:8'
--exclude-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:6','d60b549f-9e10-11e9-ab04-000c294a1b3b:8'

3.3查看GTID信息

mysql> create database gtid charset utf8mb4;
mysql> show master status;
mysql> use gtid;
mysql> create table t1(id int);
mysql> show master status;
mysql> insert into t1 values(1);
mysql> commit;
mysql> show master status;
mysql> drop database gtid;

3.4 基于GTID，binlog的恢复

3.4.1 日志的截取

mysql> mysql> show binlog events in 'mysql-bin.000006';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000006 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4                             |
| mysql-bin.000006 | 123 | Previous_gtids |         6 |         154 |                                                                   |
| mysql-bin.000006 | 154 | Gtid           |         6 |         219 | SET @@SESSION.GTID_NEXT= '61deb43b-3b02-11ec-a22a-000c29d2f486:1' |
| mysql-bin.000006 | 219 | Query          |         6 |         329 | create database gtid charset utf8mb4                              |
| mysql-bin.000006 | 329 | Gtid           |         6 |         394 | SET @@SESSION.GTID_NEXT= '61deb43b-3b02-11ec-a22a-000c29d2f486:2' |
| mysql-bin.000006 | 394 | Query          |         6 |         508 | use `gtid`; create table t1(id int,name varchar(20))              |
| mysql-bin.000006 | 508 | Gtid           |         6 |         573 | SET @@SESSION.GTID_NEXT= '61deb43b-3b02-11ec-a22a-000c29d2f486:3' |
| mysql-bin.000006 | 573 | Query          |         6 |         645 | BEGIN                                                             |
| mysql-bin.000006 | 645 | Table_map      |         6 |         693 | table_id: 108 (gtid.t1)                                           |
| mysql-bin.000006 | 693 | Write_rows     |         6 |         736 | table_id: 108 flags: STMT_END_F                                   |
| mysql-bin.000006 | 736 | Xid            |         6 |         767 | COMMIT /* xid=13 */                                               |
| mysql-bin.000006 | 767 | Gtid           |         6 |         832 | SET @@SESSION.GTID_NEXT= '61deb43b-3b02-11ec-a22a-000c29d2f486:4' |
| mysql-bin.000006 | 832 | Query          |         6 |         924 | drop database gtid                                                |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
13 rows in set (0.00 sec)

截取
mysqlbinlog  --include-gtids='61deb43b-3b02-11ec-a22a-000c29d2f486:1-3' mysql-bin.000006 >/tmp/gtid.sql

3.4.2 恢复

set sql_log_bin=0
source /tmp/gtid.sql
set sql_log_bin=1;

3.4.2.1 报错

为什么报错，因为1-3的事务已经做过了
正确的做法

–skip-gtids 作用:在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息

mysqlbinlog --skip-gtids --include-gtids='61deb43b-3b02-11ec-a22a-000c29d2f486:1-3' mysql-bin.000006 >/tmp/gtid.sql

解决后

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| gtid               |
| moni               |
| mysql              |
| performance_schema |
| sys                |
| yq                 |
+--------------------+
7 rows in set (0.00 sec)

mysql> use gtid;
Database changed
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | cn   |
+------+------+
1 row in set (0.00 sec)

4. 慢日志（slow_log）

记录运行比较慢的语句，进行优化作用

4.1 配置慢日志

vim /etc/my.cnf
开关
slow_query_log=1 
文件位置及名字 
slow_query_log_file=/data/mysql/slow.log
设定慢查询时间
long_query_time=0.1
没走索引的语句也记录
log_queries_not_using_indexes


slow_query_log=1 
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes

4.2 模拟慢查询

4.3 分析慢日志

mysqldumpslow -s c -t 10 /data/mysql/slow.log

4.4 第三方工具

使用Anemometer基于pt-query-digest将MySQL慢查询可视化

https://www.cnblogs.com/xuanzhi201111/p/4128894.html
清华源

https://mirrors.tuna.tsinghua.edu.cn/percona/centos/7/RPMS/noarch/

wget --no-check-certificate  https://mirrors.tuna.tsinghua.edu.cn/percona/centos/7/RPMS/noarch/percona-toolkit-2.2.20-1.noarch.rpm

yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5

pt-query-digest  /data/mysql/slow.log

# Query 3: 0 QPS, 0x concurrency, ID 0x4B9BFC66C6A2320F at byte 831 ______
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2021-11-02T04:07:14
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         25       1
# Exec time     20   215us   215us   215us   215us   215us       0   215us
# Lock time     24   119us   119us   119us   119us   119us       0   119us
# Rows sent     27      11      11      11      11      11       0      11
# Rows examine  35      22      22      22      22      22       0      22
# Query size    31      32      32      32      32      32       0      32
# String:
# Databases    gtid
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `gtid` LIKE 't1'\G
#    SHOW CREATE TABLE `gtid`.`t1`\G
# EXPLAIN /*!50100 PARTITIONS*/
select name from t1  order by id\G

这篇关于MySQL日志文件的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！