mysql日志管理
1、错误日志 ***
1.1 作用
排查MySQL运行过程的故障.
1.2 默认配置
默认就开启了. 默认路径和名字: datadir/hostname.err 查看方法: [ERROR]
1.3 人为定制位置
vim /my.cnf log_error=/tmp/mysql3306.log --放到服务端配置块 重启生效. select @@log_error;
2 、二进制日志(binlog)**
2.1 作用
(1) 主从要依赖二进制日志 (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) 参数配置 server_id=6 log_bin=/data/binlog/mysql-bi binlog_format=row
(3)创建目录和授权 mkdir -p /data/binlog/ chown -R mysql.mysql /data (4)重启生效 [root@db01 tmp]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
2.3 二进制日志记录了什么?
2.3.1 概括
记录的数据库所有变更类的操作日志. DDL DCL DML
2.3.2 DDL 和 DCL
以语句的方式,原模原样的记录.
2.3.3 DML
(1) 他记录的已提交的事务 (2) DML记录格式(statement,row,mixed),通过binlog_format=row参数控制 说明: statement:SBR,语句模式记录日志,做什么命令,记录什么命令. row :RBR,行模式,数据行的变化 mixed :MBR,混合模式
面试问题: SBR和RBR什么区别?怎么选择? SBR: 可读性较强,对于范围操作日志量少,但是可能会出现记录不准确的情况. RBR: 可读性较弱,对于范围操作日志大,不会出现记录错误.
sync_binlog=1 每次事务提交立即刷到binlog磁盘
高可用环境中的新特性要依赖于RBR 我们公司对数据的严谨性要求较高,也用用到了新型的架构,所以选择RBR
2.4 二进制日志记录单元
2.4.1 event 事件
二进制日志的最小单元 DDL : create database oldguo; 事件1
对于DDL等语句是每一个语句就是一个事件 DML: 一个事务包含了多个语句 begin; 事件1 a 事件2 b 事件3 commit; 事件4
2.4.2 event事件的开始和结束号码
作用,方便我们从日志中截取我们想要的日志事件.
2.5 二进制日志的管理
2.5.1 查看二进制日志位置
mysql> show variables like '%log_bin%';
2.5.2 查看所有已存在的二进制日志
mysql> show binary logs; mysql> flush logs; mysql> show binary logs;
2.5.3 查看正在使用的二进制日志
mysql> show master status ;
2.5.4 查看二进制日志事件
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.5.5 查看二进制日志内容
[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.5.6 截取二进制日志
[root@db01 binlog]# mysqlbinlog --start-position=219 --stop-position=335 mysql-bin.000004 >/tmp/a.sql
2.5.7 通过binlog恢复数据
(1) 模拟数据 mysql> create database haoge charset utf8mb4; mysql> use haoge; mysql> create table t1(id int); mysql> insert into t1 values(1); mysql> commit;
(2)模拟故障 mysql> drop database haoge;
(3)分析和截取binlog mysql> show master status ; --->确认使用的是哪一个日志 mysql> show binlog events in 'mysql-bin.000004' ; --->查看事件 说明: 找到起点和终点,进行截取 mysqlbinlog --start-position=823 --stop-position=1420 /data/binlog/mysql-bin.000004 >/tmp/bin.sql
(4)恢复binlog
mysql> set sql_log_bin=0; --->临时关闭恢复时产生的新日志 mysql> source /tmp/bin.sql mysql> set sql_log_bin=1; set sql_log_bin=1; --->改回来
2.6 binlog的gtid记录模式的管理
2.6.1 GTID介绍
对于binlog中的每一个事务,都会生成一个GTID号码 DDL ,DCL 一个event就是一个事务,就会有一个GTID号. DML语句来讲,begin到commit,是一个事务,就是一个GTID号
2.6.2 GTID的组成
severi_uuid:TID severi_uuid? [root@db01 data]# cat auto.cnf [auto] server-uuid=d60b549f-9e10-11e9-ab04-000c294a1b3b
TID是一个:自增长的数据,从1开始 d60b549f-9e10-11e9-ab04-000c294a1b3b:1-15
2.6.3 GTID的幂等性
如果拿有GTID的日志去恢复时,检查当前系统中是否有相同GTID号,有相同的就自动跳过 会影响到binlog恢复和主从复制.
2.6.4 GTID的开启和配置
vim /etc/my.cnf gtid-mode=on enforce-gtid-consistency=true
2.6.5 查看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;
2.6.6 基于GTID,binlog恢复
(1) 截取日志 [root@db01 data]# cd /data/binlog/ [root@db01 binlog]# mysqlbinlog --include-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:1-3' mysql-bin.000005 >/tmp/gtid.sql
(2)恢复 mysql> set sql_log_bin=0; mysql> source /tmp/gtid.sql mysql> set sql_log_bin=1; (3) 报错 ERROR 1049 (42000): Unknown database 'gtid' Query OK, 0 rows affected (0.00 sec) ERROR 1046 (3D000): No database selected 为什么报错? 因为幂等性的检查,1-3事务已经做过了.
(4)正确的做法? mysqlbinlog --skip-gtids --include-gtids='d60b549f-9e10-11e9-ab04-000c294a1b3b:1-3' mysql-bin.000005 >/tmp/gtid.sql --skip-gtids 作用:在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息
(5) 恢复
set sql_log_bin=0; source /tmp/gtid.sql set sql_log_bin=1;
2.6.7 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.慢日志(slow-log)
3.1 作用
记录运行较慢的语句,优化过程中常用的工具日志.
3.2 如何配置
开关
slow_query_log=1
文件位置及名字
slow_query_log_file=/data/mysql/slow.log
设定慢查询时间
long_query_time=0.1
没走索引的语句也记录
log_queries_not_using_indexes
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
3.3 模拟慢查询
略.
3.4 分析慢日志
mysqldumpslow -s c -t 10 n =/data/mysql/slow.log
3.5 第三方工具(自己扩展)
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慢查询可视化
