MySQL错误日志及二进制日志管理
欢迎来到 来到大浪涛天的博客 ！
MySQL日志管理在整个数据库体系中都非常重要，错误日志用于排查故障，而二进制日志通常用来做备份回放。
一、MySQL日志管理
1. 错误日志
1-1. 错误日志的作用
排查MySQL运行过程的故障。
1-1-1. 默认配置
默认就开启了.
默认路径和名字: datadir/hostname.err
查看方法:查看带有[ERROR]的错误信息进行必要的判断1-1-2. 人为定制位置
log_error=/tmp/mysql3306.log
重启生效.
select @@log_error;2. 二进制日志(binlog)
2-1. 二进制日志作用
(1) 主从要依赖二进制日志
(2) 数据恢复时需要依赖于二进制日志2-2. 二进制日志的配置
2-2-1. 二进制日志参数介绍
默认没有开启.
server_id=6
log_bin=/data/binlog/mysql-bin
说明:
/data/binlog : 提前定制好的目录,而且要有mysql.mysql的权限
mysql-bin : 二进制日志文件名的前缀
例如: mysql-bin.000001 ,mysql-bin.000002 ......
binlog_format=row ---> 5.7版本默认配置是row,可以省略.2-2-2. 二进制日志参数配置
server_id=6
log_bin=/data/binlog/mysql-bin
binlog_format=row
创建目录和授权
mkdir -p /data/binlog/
chown -R mysql.mysql /data
重启生效
[root@db01 tmp]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS! 2-3. 二进制日志记录
2-3-1. 二进制日志记录的概括
记录的数据库所有变更类的操作日志.
DDL
DCL
DML2-3-2. 二进制日志记录方式
1. DDL 和 DCL
以语句的方式,原模原样的记录.
2. DML
(1)他记录的已提交的事务
(2)DML记录格式(statement,row,mixed),通过binlog_format=row参数控制
说明:
statement:SBR,语句模式记录日志,做什么命令,记录什么命令.
row :RBR,行模式,数据行的变化
mixed :MBR,混合模式
SBR和RBR什么区别?怎么选择?
SBR: 可读性较强,对于范围操作日志量少,但是可能会出现记录不准确的情况.
RBR: 可读性较弱,对于范围操作日志大,不会出现记录错误.
高可用环境中的新特性要依赖于RBR
我们公司对数据的严谨性要求较高,也用用到了新型的架构,所以选择RBR2-3-3. 二进制日志记录单元
2-3-3-1. event 事件
二进制日志的最小单元
1. DDL :
create database oldguo; 事件1
对于DDL等语句是每一个语句就是一个事件
2. DML: 一个事务包含了多个语句
begin; 事件1
a 事件2
b 事件3
commit; 事件42-3-3-2. event事件的开始和结束号码
作用,方便我们从日志中截取我们想要的日志事件.
2-3-3-2-1. 查看二进制日志位置
mysql> show variables like '%log_bin%';2-3-3-3. 查看所有已存在的二进制日志
mysql> show binary logs;
mysql> flush logs;
mysql> show binary logs;2-3-3-4. 查看正在使用的二进制日志
mysql> show master status ;2-3-3-5. 查看二进制日志事件
mysql> create database binlog charset utf8mb4;
mysql> use binlog
mysql> create table t1(id int);
mysql> insert into t1 values(1);
mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 501 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000004';
mysql> show binlog events in 'mysql-bin.000004' limit 5;2-3-3-6. 查看二进制日志内容
[root@db01 binlog]# mysqlbinlog mysql-bin.000004
[root@db01 binlog]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000004
[root@db01 binlog]# mysqlbinlog -d haoge mysql-bin.000004
2-3-3-7. 截取二进制日志
[root@db01 binlog]# mysqlbinlog --start-position=219 --stop-position=335 mysql-bin.000004 >/tmp/a.sql2-3-3-8. 通过binlog恢复数据
(1) 模拟数据
mysql> create database test02 charset utf8mb4;
mysql> use test02;
mysql> create table t2(id int);
mysql> insert into t2 values(1) ... ...;
mysql> commit;
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 264 |
+----------+
(2)模拟故障
mysql> drop database test02;
(3)分析和截取binlog
mysql> show master status; --->确认使用的是哪一个日志
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 11081 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> show binlog events in 'mysql-bin.000001' ; --->查看事件
截取日志：找到起点和终点,进行截取
[root@test02 binlog]# mysqlbinlog --start-position 934 --stop-position 10918 mysql-bin.000001 >/tmp/test02.sql
(4)恢复binlog
mysql> set sql_log_bin=0; --->临时关闭恢复时产生的新日志
mysql> source /tmp/test02.sql
mysql> set sql_log_bin=1; --->改回来2-4. binlog的gtid记录模式的管理
2-4-1. GTID介绍
1. 对于binlog中的每一个事务,都会生成一个GTID号码
2. DDL,DCL 一个event就是一个事务,就会有一个GTID号.
3. DML语句来讲,begin到commit,是一个事务,就是一个GTID号2-4-2. GTID的组成
1. severi_uuid:TID
root@test02 data]# cat auto.cnf
[auto]
server-uuid=9f5d3fcf-e215-11ea-9d2c-001c42063d8f
2. TID是一个:自增长的数据,从1开始
9f5d3fcf-e215-11ea-9d2c-001c42063d8f:1-152-4-3. GTID的幂等性
如果拿有GTID的日志去恢复时,检查当前系统中是否有相同GTID号,有相同的就自动跳过
会影响到binlog恢复和主从复制。2-4-4. GTID的开启和配置
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true2-4-5. 查看GTID信息
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 | 946 | | | 9f5d3fcf-e215-11ea-9d2c-001c42063d8f:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+2-4-6. 基于GTID，binlog恢复
1. 模拟并确认数据
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;
2. 根据MySQL event确认gtid的起始号和结束号
mysql> show binlog events in 'mysql-bin.000002';
3. 基于gtid截取日志
通过event我们知道gtid是1-3号，但是由于幂等性的检查,1-3事务已经做过了，系统上存在1-3号的，因此我们必须在此命令的基础上加上--skip-gtids参数，在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息
[root@test02 binlog]# mysqlbinlog --skip-gtids --include-gtids='9f5d3fcf-e215-11ea-9d2c-001c42063d8f:1-3' mysql-bin.000002 >/tmp/gtid.sql;
4. 恢复数据
set sql_log_bin=0;
source /tmp/gtid.sql
set sql_log_bin=1;
2-4-7. GTID相关的参数
跳过的gtid号
--skip-gtids
包含的gtid号
--include-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:6','d60b549f-9e10-11e9-ab04-000c294a1b3b:8'
排除的gtid号
--exclude-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:6','d60b549f-9e10-11e9-ab04-000c294a1b3b:8'3. 慢日志(slow-log)
3-1. 慢日志的作用
记录运行较慢的语句,优化过程中常用的工具日志.3-2. 慢日志的配置
1. 打开慢日志
slow_query_log=1
2. 文件位置及名字
slow_query_log_file=/data/mysql/slow.log
3. 设定慢查询时间
long_query_time=0.1
4. 没走索引的语句也记录
log_queries_not_using_indexes
5. 在配置文件中设置
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_indexes3-3. 模拟慢查询
在test02库中的t100w表上执行一些不走索引的语句，如
select id,num,k1,k2 from t100w where id=1 and id=2;
select id,num,k1,k2 from t100w where k1='bb';
select id,num,k1,k2 from t100w where k1='bb' and k2='bb';
select id,num,k1,k2 from t100w where k1='bb' and k2='cc';
select id,num,k1,k2 from t100w where k1='bb' or k2='cc';
select id,num,k1,k2 from t100w where k1='bb' or k2='cc' or id=3;
select id,num,k1,k2 from t100w where k1='bb' or k2 !='cc' and id=3;
select id,num,k1,k2 from t100w where k1 !='bb' or k2 !='cc' and id=3;3-4. 分析慢日志
如果不带参数的话是默认按时间的顺序显示慢日志，但是如果加参数可以按执行的时间排序来观察
[root@test02 mysql]# mysqldumpslow -s -c -t 10 slow.log
Reading mysql slow query log from slow.log
Count: 1 Time=0.44s (0s) Lock=0.03s (0s) Rows=290.0 (290), root[root]@localhost
select id,num,k1,k2 from t100w where k1='S' or k2='S' or id=N
Count: 1 Time=1.08s (1s) Lock=0.02s (0s) Rows=288.0 (288), root[root]@localhost
select id,num,k1,k2 from t100w where k1='S'
Count: 1 Time=0.47s (0s) Lock=0.01s (0s) Rows=288.0 (288), root[root]@localhost
select id,num,k1,k2 from t100w where k1='S' or k2='S'
Count: 1 Time=0.43s (0s) Lock=0.00s (0s) Rows=290.0 (290), root[root]@localhost
select id,num,k1,k2 from t100w where k1='S' or k2 !='S' and id=N
Count: 1 Time=1.50s (1s) Lock=0.00s (0s) Rows=1030057.0 (1030057), root[root]@localhost
select id,num,k1,k2 from t100w where k1 !='S' or k2 !='S' and id=N
Count: 1 Time=0.01s (0s) Lock=0.02s (0s) Rows=20.0 (20), root[root]@localhost
select * from t100w limit N
Count: 1 Time=0.44s (0s) Lock=0.01s (0s) Rows=288.0 (288), root[root]@localhost
select id,num,k1,k2 from t100w where k1='S' or k2='S' and id=N
3-4-1. 第三方工具 percona来查看确认慢语句
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慢查询可视化