日志文件介绍
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