错误日志

排查MySQL运行过程的故障

  • 默认路径和名字: [数据目录]/[主机名].err

在my.cnf中加入参数指定错误日志位置,重启生效

log_error=/tmp/mysql3306.log

查看错误日志位置

select @@log_error;

二进制日志(binlog)

数据恢复和主从要依赖二进制日志,默认没有开启,需要修改my.cnf,重启生效

#5.7以后的版本开启二进制日志需要设置server_id,范围1~65535
server_id=6
#log_bin设置的目录需要拥有mysql的权限,并且该目录已经存在,设置这个参数会指定二进制日志存放目录
#mysql-bin二进制日志文件名的前缀
log_bin=/data/binlog/mysql-bin
#5.7版本默认配置是row,可以省略
#DML记录格式(statement,row,mixed),通过binlog_format参数控制
binlog_format=row

binlog_format的参数

  • statement : SBR,语句模式记录日志,做什么命令,记录什么命令
  • row : RBR,行模式,数据行的变化
  • mixed : MBR,混合模式,mysql会自行选择记录方式

面试题:SBR和RBR什么区别?怎么选择?

  • SBR : 可读性较强,对于范围操作日志量少,但是可能会出现记录不准确的情况,记录的是sql语句
  • RBR : 可读性较弱,对于范围操作日志大,不会出现记录错误,记录的是数据行的变化
  • 高可用环境中的新特性要依赖于RBR.如果公司对数据的严谨性要求较高,也用用到了新型的架构,选择RBR

二进制日志的内容

  • 记录的数据库所有变更类的操作日志,DDL,DCL,DML.其中DDL和DCL以语句的方式,原模原样的记录

二进制日志记录单元

  • 二进制日志的最小单元是event事件,对于DDL,DCL语句是每一个语句就是一个事件,DML一个完整的事务会包含多个事件,每个被记录的DML事务必然是提交成功的事务

DML: 一个事务包含了多个语句

  • 事件1:begin;
  • 事件2:a语句
  • 事件3:b语句
  • 事件4:commit;

event事件的开始和结束号码

  • 方便我们从日志中截取我们想要的日志事件

二进制日志相关命令

#查看二进制日志相关参数
show variables like '%log_bin%';
#查看log_bin_basename参数
select @@log_bin_basename;
#查看所有已存在的二进制日志
show binary logs;
#刷新日志,会创建一个新的二进制日志文件
flush logs;
#查看正在使用的二进制日志
show master status;

查看二进制日志事件

刚刚开启的数据库没有内容,所以手动创建一些内容,再查看二进制日志

#刷新日志
flush logs;
#执行操作
create database binlog charset utf8mb4;
use binlog;
create table t1(id int);
insert into t1 values(1);
#查看正在使用的二进制日志
show master status;
#查看日志,也可以加上limit字句配合
show binlog events in 'mysql-bin.000002';

+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                   |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.26-log, Binlog ver: 4  |
| mysql-bin.000002 | 123 | Previous_gtids |         6 |         154 |                                        |
| mysql-bin.000002 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 219 | Query          |         6 |         335 | create database binlog charset utf8mb4 |
| mysql-bin.000002 | 335 | Anonymous_Gtid |         6 |         400 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 400 | Query          |         6 |         501 | use `binlog`; create table t1(id int)  |
| mysql-bin.000002 | 501 | Anonymous_Gtid |         6 |         566 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 566 | Query          |         6 |         640 | BEGIN                                  |
| mysql-bin.000002 | 640 | Table_map      |         6 |         687 | table_id: 108 (binlog.t1)              |
| mysql-bin.000002 | 687 | Write_rows     |         6 |         727 | table_id: 108 flags: STMT_END_F        |
| mysql-bin.000002 | 727 | Xid            |         6 |         758 | COMMIT /* xid=15 */                    |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
11 rows in set (0.00 sec)

mysqlbinlog命令

#使用mysqlbinlog命令查看
mysqlbinlog /data/binlog/mysql-bin.000002
#逐行翻译内容并详细显示所有日志
mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000002
#查看有关binlog库相关的日志
mysqlbinlog -d binlog /data/binlog/mysql-bin.000002
#通过position号截取二进制日志从120到544到a.sql
mysqlbinlog --start-position=154 --stop-position=758 /data/binlog/mysql-bin.000002 > ~/a.sql

通过binlog恢复数据

通过二进制日志来恢复数据

创建模拟数据

create database haoge charset utf8mb4;
use haoge;
create table t1(id int);
begin;
insert into t1 values(1);
commit;
#模拟故障,删除haoge库
drop database haoge;

分析和截取binlog

#mysql中查看当前使用的二进制日志
show master status;
#mysql中查看事件,找到起点和终点,进行截取
show binlog events in 'mysql-bin.000002';

#命令行下截取
mysqlbinlog --start-position=758 --stop-position=1355 /data/binlog/mysql-bin.000002 > /tmp/bin.sql

#mysql中关闭日志记录并加载刚刚导出的日志
set sql_log_bin=0;
source /tmp/bin.sql
#mysql中开启日志记录,完成恢复操作
set sql_log_bin=1;

binlog的gtid记录模式的管理

对于binlog中的每一个事务,都会生成一个GTID号码,DDL,DCL每一个event事件就是一个事务,就会有一个GTID号.DML语句来讲,begin到commit,是一个事务,就是一个GTID号

  • GTID的组成为server_uuid:TID,server_uuid可以通过查看auto.cnf文件获得
  • TID是一个:自增长的数据,从1开始d60b549f-9e10-11e9-ab04-000c294a1b3b:1-15

GTID的幂等性

  • 如果拿有GTID的日志去恢复时,检查当前系统中是否有相同GTID号,有相同的就自动跳过,会影响到binlog恢复和主从复制

GTID的开启和配置

vim /etc/my.cnf
#添加以下参数重启后生效
gtid-mode=on
enforce-gtid-consistency=true

基于GTID的binlog恢复

通过GTID的二进制日志来恢复数据

创建模拟数据

create database gtid charset utf8mb4;
use gtid;
create table t1(id int);
begin;
insert into t1 values(1);
commit;
show master status;
#模拟故障,删除gtid库
drop database gtid;

通过gtid截取日志

#如果不忽略gtid直接抓取日志恢复,恢复时因为幂等性报错
mysqlbinlog --include-gtids='192ae722-6e3e-11ea-9164-002170ee9a77:1-3' /data/binlog/mysql-bin.000005 > /tmp/gtid.sql

#所以抓取日志应该忽略gtid
#--skip-gtids 在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息
#--include-gtids='192ae722-6e3e-11ea-9164-002170ee9a77:6','192ae722-6e3e-11ea-9164-002170ee9a77:8' 抓取时,包含6和8的日志
#--exclude-gtids='192ae722-6e3e-11ea-9164-002170ee9a77:6','192ae722-6e3e-11ea-9164-002170ee9a77:8' 抓取时,排除6和8的日志
mysqlbinlog --skip-gtids --include-gtids='192ae722-6e3e-11ea-9164-002170ee9a77:1-3' /data/binlog/mysql-bin.000005 > /tmp/gtid.sql

未忽略gtid恢复时报错

ERROR 1049 (42000): Unknown database 'gtid'
ERROR 1046 (3D000): No database selected

通过日志进行恢复

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

慢日志(slow-log)

记录运行较慢的语句,优化过程中常用的工具日志

配置my.cnf重启生效

#1为开启 0为关闭
slow_query_log=1
#文件位置及日志名称
slow_query_log_file=/data/mysql/slow.log
#设定慢查询时间,即大于0.1秒的语句会被记录
long_query_time=0.1
#没走索引的语句也记录
log_queries_not_using_indexes

分析慢日志

运行大量慢语句后使用mysqldumpslow进行分析

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

第三方工具

慢语句相关工具

  • percona-toolkit:下载地址
  • 下载后是rpm包可以直接安装
  • 需要安装以下依赖
  • Anemometer基于pt-query-digest将MySQL慢查询可视化
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