1.错误日志
1.1 作用
排查MySQL运行过程的故障.
1.2 默认配置
默认就开启了.
默认路径和名字: datadir/hostname.err
查看方法: [ERROR]
1.3 人为定制位置
log_error=/tmp/mysql3306.log;
重启生效.
select @@log_error; #查看错误日志的路径
2.二进制日志(binlog)******
2.1 作用
(1) 主从要依赖二进制日志
(2) 数据恢复时需要依赖于二进制日志
2.2 如何配置?
(1)参数介绍
默认没有开启.
log_bin=on #开启二进制日志
/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-bin
binlog_format=row
sync_binlog=1 每次事务提交都立即刷写binlog到磁盘 ******
(3)创建目录和授权
mkdir -p /data/binlog/
chown -R mysql.mysql /data
(4)重启生效
/etc/init.d/mysqld restart
二进制日志记录了什么?
2.3.1 概括
记录对数据库所有变更类的操作日志.
这些语句DDL DCL DML 会改变数据库
2.3.2 DDL 和 DCL
记录DDL 和 DCL语句本身
2.3.3 DML
(1) 记录已提交的事务中的DML语句
(2) DML记录格式(statement,row,mixed),通过binlog_format=row参数控制
statement:SBR,语句模式记录日志,做什么命令,记录什么命令.
row :RBR,行模式,记录数据行的变化
mixed :MBR,混合模式
SBR和RBR什么区别?怎么选择?
SBR: 可读性较强,对于范围操作日志量少,但是可能会出现记录不准确的情况.
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 查看二进制日志位置
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中的命令)
show binlog events in 'mysql-bin.000004'; #列出的是所有的事件
2.5.5 查看二进制日志内容(不容易看懂)(在linux中使用的命令)
mysqlbinlog mysql-bin.000004
mysqlbinlog mysql-bin.000001 | grep -v '^SET' #过滤掉包含SET的日志
mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000004 #解码
mysqlbinlog -d database_name mysql-bin.000004 #获取某个数据库的日志
mysqlbinlog --help #内容多的话会显示的很长
mysqlbinlog --help | more #查看mysqlbinlog 的帮助信息，显示整个屏幕，回车往下滚动
2.5.6 截取二进制日志
mysqlbinlog --start-position=219 --stop-position=335 mysql-bin.000004 >/tmp/a.sql #导出为文件
2.5.7 通过binlog恢复数据
(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; --->改回来
2.6 binlog的gtid记录模式的管理 ****
2.6.1 GTID介绍global transaction ID
对于binlog中的每一个事务,都会生成一个GTID号码
DDL ,DCL 一个event就是一个事务,就会有一个GTID号.
DML语句来讲,begin到commit,是一个事务,就是一个GTID号
2.6.2 GTID的组成
GTID的格式：sever_uuid:TID
sever_uuid可以在auto.cnf文件中或show master status ;语句中看到
cat auto.cnf #该文件在/data/mysql/data/路径下
[auto]
server-uuid=d60b549f-9e10-11e9-ab04-000c294a1b3b
TID是一个:自增长的数据,从1开始
d60b549f-9e10-11e9-ab04-000c294a1b3b:1-15
2.6.3 GTID的幂等性
如果拿有GTID的日志去恢复时,mysql会检查当前系统中是否有相同GTID号,有相同的就自动跳过
会影响到binlog恢复和主从复制.
2.6.4 GTID的开启和配置
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
2.6.6 基于GTID,binlog恢复
(1) 截取日志
show binlog events in 'mysql-bin.000002'; #可以看到想要的GTID范围，方便截取的时候填上
cd /data/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事务，因为幂等性的检查，发现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'
排除6和8的两条
1.2 二进制日志清理
1.2.1 自动
expire_logs_days=15 #将该语句加到配置文件，重启后生效
设置的依据: 至少1轮全备周期长度的过期时间.
1.2.2 手工
mysql> help purge
PURGE BINARY LOGS TO 'mysql-bin.000032'; #删除32之前的所有日志
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
mysql> reset master ; #结果是删除全部日志，日志编号从1开始
1.3 日志如何滚动(即生成一个新的日志文件)
flush logs; #会滚动日志文件
数据库重启会滚动日志文件
max_binlog_size=1073741824 #该参数用来设置一个日志文件的最大容量，默认1G
3.慢日志(slow-log)
3.1 作用
记录运行较慢的语句,是优化过程中常用的工具日志.
3.2 如何配置
开关
select @@slow_query_log;#查看当前状态
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.4 分析慢日志
mysqldumpslow -s c -t 10 /data/mysql/slow.log #日志按指定方式显示
-s=sort c=count -t =top
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慢查询可视化