image.png
1、错误日志
- 作用：记录启动\关闭\日常运行过程中,状态信息,警告,错误
错误日志配置
默认就是开启的: /数据路径下/hostname.err
手工设定:
Master [(none)]>select @@log_error;
vim /etc/my.cnf
log_error=/var/log/mysql.log
log_timestamps=system
重启生效
show variables like 'log_error';
2、binlog日志（二进制日志）
2.1 作用
(1)备份恢复必须依赖二进制日志
(2)主从环境必须依赖二进制日志
2.2 binlog配置(5.7必须加server_id)
注意：MySQL默认是没有开启二进制日志的。
# 基础参数查看:
开关:
[(none)]>select @@log_bin;
日志路径及名字
[(none)]>select @@log_bin_basename;
服务ID号:
[(none)]>select @@server_id;
二进制日志记录格式:
[(none)]>select @@binlog_format;
双一标准之二:
[(none)]>select @@sync_binlog;
# 创建日志目录
mkdir /data/mysql
chown -R mysql.mysql /data/mysql
# 修改配置文件
vim /etc/my.cnf
[mysqld]
log-bin=/data/mysql/mysql-bin //日志路径及日志文件前缀
binlog_format=row //日志记录格式
server_id=1 //serverID号
# 重启生效
2.3 二进制日志记录了什么？
mysql数据库所有变更类的sql语句，不记录查询语句
对于DDL、DCL：直接将语句记录下来
对于DML：已提交的DML
记录格式：
row ------>5.7版本默认
特点：记录数据行的变化，可读性不高，日志量较大，数据够精确
statement ------>5.6版本默认
特点：原封不动的记录语句。可读性较高，日志量较少，将来恢复可能会出现错误数据。
mixed ：以上两种模式的混合
2.4 event是什么？
二进制日志的最小记录单元
对于DDL、DCL,一个语句就是一个event
对于DML语句：例如以下例子，就被分为4个event
begin；
DML1
DML2
commit;
event的组成
开始位置（# at 157）+事件内容+结束位置(下一个# at 890，或者是end_log_pos 890)
2.5 日志文件查看
log_bin 参数设置的路径，可以找到二进制日志
show binary.logs; //查看所有的二进制文件的信息
show master status; //查看二进制日志使用情况
2.6 二进制日志内容查看
# 二进制日志的事件信息
show binlog events in 'msyql-bin.000002';
# 查看二进制日志内容
mysqlbinlog /data/mysql/mysql-bin.000002
两个at之间就是一个事件，/、*之类不用看是注释；去掉这些之外就是事件内容。
# 查看rows格式内容
mysqlbinlog --base64-output=decode-rows -vvvv /data/mysql/mysql-bin.000002
对于DML语句，二进制文件用rows格式，不可读
# 查看特定库的内容
mysqlbinlog -d testdb /data/mysql/mysql-bin.000002
2.7 日志截取
# 查看事件
show binlog events in 'msyql-bin.000002';
# 截取日志
mysqlbinlog --start-position=321 --stop-position=513 /data/mysql/mysql-bin.000002 >/tmp/binlog.sql
# 临时关闭此窗口的二进制日志
set sql_log_bin=0; //恢复会产生额外的二进制日志
# 将截取的二进制日志恢复
source /tmp/binlog.sql
# 回复完记得改回
set sql_log_bin=1;
# 无GTID ：
mysqlbinlog --start-position --stop-position mysql-bin.000002 > /tmp/binlog.sql
mysqlbinlog -d world --start-position --stop-position mysql-bin.000002 > /tmp/binlog.sql //查询特定库的binlog
mysqlbinlog --start-datetime --stop-datetime mysql-bin.000002 mysql-bin.000003> /tmp/binlog.sql //用时间点过滤多二进制日志文件
# 有GTID ：
mysqlbinlog --skip-gtids --include-gtids='xxxxxxxx:1-14' mysql-bin.000002 mysql-bin.000003> /tmp/binlog.sql
mysqlbinlog --skip-gtids --include-gtids='xxxxxxxx:1-5','xxxxxxxx:7-10' mysql-bin.000002 mysql-bin.000003> /tmp/binlog.sql //截取多段gtid日志
mysqlbinlog --skip-gtids --include-gtids='xxxxxxxx:1-14' --exclude-gtids='xxxxxxxx:6' mysql-bin.000002 mysql-bin.000003> /tmp/binlog.sql //截取1-14 gtid日志，排除第6个gtid日志
--skip-gtids //本地数据恢复，恢复到原来数据库
2.8 使用二进制日志恢复数据案例
- 只要有全量的binlog，可以把数据库恢复到任意时间点。
数据故障原因：
1、软件损坏
2、数据损坏
(1) 物理损坏
(2) 硬件损坏，数据文件数据页
(3) 逻辑损坏
(4) SQL导致误删除，误修改
故障恢复模拟：
# 建表语句
create database db charset utf8;
use db;
create table t1(id int);
insert into t1 values(1),(2),(3);
insert into t1 values(4),(5),(6);
commit;
update t1 set id=30 where id=4;
commit;
delete from t1 where id=4;
commit;
drop table t1;
# 进入db库，查找二进制文件名
show master status;
show binlog events in 'mysql-bin.000002';
# 找到建表时的位置点和删表之前时的位置点；
# 截取二进制日志：
mysqlbinlog --start-position=614 --stop-position=1470 /data/mysql/mysql-bin.000002 >/tmp/binlog.sql
# 临时关闭此窗口的二进制日志
set sql_log_bin=0; //恢复会产生额外的二进制日志
# 将截取的二进制日志恢复
source /tmp/binlog.sql
# 查看结果
select * from t1;
# 回复完记得改回
set sql_log_bin=1;
2.9 二进制日志其它操作
二进制日志自动清理时间，是按照全备周期+1;企业建议，至少保留15天。
# 查看日志过期天数
show variables like '%expire%';
expire_logs_days | 0
# 设置日志过期天数
set global expire_logs_days=8;
永久生效：
vim /etc/my.cnf
expire_logs_days=8; //重启生效
# 手工清理二进制日志
purge binary logs before now() - interval 3 day; //删除三天之前的所有二进制日志
purge binary logs to 'mysql-bin.000010'; //删除二进制日志第1-10号
# 注意：禁止手动 rm 删除二进制日志；
解决方案：
1、my.cnf 配置文件binlog 关闭，启动数据库
2、把数据库关闭，开启binlog，启动数据库
# 删除所有binlog，并从000001开始重新记录日志
reset master;
2.10 日志滚动
-
日志滚动的3种情况：
1、重启mysql 会自动滚动一个新的binlog文件
2、日志文件达到1G大小（max_binlog_size）
3、备份时，加入参数也可以自动滚动 -
手工滚动出来一个新的日志
flush logs;
3、慢日志（记录慢语句的日志）
# 开启慢日志的四个参数：
# 开关
slow_query-log=0
# 文件的位置及名字
slow_query_log_file=/data/mysql/slow.log
# 设定慢查询阈值,单位为秒
long_query_time=0.01
# 设定没走索引的语句都认为是慢语句
log_queries_not_using_indexes
# 配置慢日志
vim /etc/my.cnf
[mysqld]
slow_query-log=1
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.01
log_queries_not_using_indexes
# 重启
3.1 慢日志排序工具
1、mysqldumpslow
mysqldumpslow -s c -t 10 /data/mysql/slow.log
-s 排序方式 c 次数 t 时间 l 查询时间 r 返回记录数
ac、at、al、ar、表时相应的倒序。
-t top n的意思，即为返回前面多少条数据
-g 后面可以写一个正则匹配模式，大小写不敏感的
2、percona-toolkit
下载toolkit软件包
https://www.percona.com/downloads/percona-toolkit/LATEST/
下载perl包依赖
yum install -y perl perl-Time-HiRes perl-DBI perl-DBD-MySQL perl-IO-Socket-SS
toolkit工具命令：
cd /percona-toolkit-3.0.12/bin/
./pt-query-diagest /data/mysql/slow.log
网友评论