MySQL 日志管理

1.错误日志

1.1作用
排错 。MySQL 启动和工作过程状态，警告，错误。

1.2 配置
默认开启。存储位置， datadir/hostname.err
自己设置，在配置文件中添加
log_error=/data/mysql/data_3306/mysql.sql    #启动mysql的用户对这个文件必须有写的权限，该文件必须存在

1.3 查看
[ERROR]

2.二进制日志

2.1 作用
数据恢复，主从复制，记录的是所有变化类的操作（DDL,DML,DCL）

2.2 配置
server_id=N(N>0)
log_bin=/data/mysql/binlog_3306/mysql-bin        #把我们的所有二进制日志放到/data/mysql/binlog_3306/目录下，并以mysql-bin为前缀

2.3 binlog 对于不通语句的记录格式
（1）非事务型语句： DDL DCL
以 statement 语句模式记录（执行的命令会被全部记录）

（2）事务型语句（insert，update，delete）
begin; 操作记录;   commit；
只记录提交的事务。

记录格式，三种：
    1.RBR    ROW模式（行模式），记录数据行的变化，记录够准确
    2.SBR    以statement的方式记录的格式，（以语句方式记录DML语句），可能会有偏差
    3.MBR    Mixed 模式，混合模式，MySQL自己决定

（3） binlog 的内容格式
以event事件的方式记录所有的变化。binlog的最小记录单元为事件
DDL  DCL ，每条语句就是一个事件    
对于DML 来说    begin； 操作； commit; 一个事务有多个事件构成

event的结构：        #上一个event的结束点就是下一个事务的起始点，是连续的，必须连续记录的，不能出现断点
起始点：Position
事件内容
结束点：end_Position

2.4 查看二进制日志
mysql> show binary logs;

滚动日志
mysql> flush logs;

查看当前正在使用的二进制日志
mysql> show master status;

事件查看
mysql> show binlog events in 'mysql-bin.000003';


内容查看
[root@db01 binlog_3306]# mysqlbinlog /data/mysql/binlog_3306/mysql-bin.000003


详细查看
[root@db01 binlog_3306]# mysqlbinlog --base64-output=decode-rows -vvv /data/mysql/binlog_3306/mysql-bin.000003 

2.5 使用二进制日志进行恢复

1.步骤：
    1.截取日志
        起点：
        终点：

    2. 恢复日志
    source /tmp/bin.sql

2. 模拟
mysql> create database oldboy;
mysql> use oldboy
mysql> create table t1 (id int);
mysql> insert into t1 values(1);
mysql> commit;
mysql> insert into t1 values(2);
mysql> commit;
mysql> insert into t1 values(3);
mysql> commit;


mysqlbinlog --start-position=980  --stop-position=2083  /data/mysql/binlog_3306/mysql-bin.000004>/tmp/bin.sql

恢复日志 
	set sql_log_bin=0;
	source /tmp/bin.sql
	set sql_log_bin=1;

2.5.3 日志滚动

flush logs;
重启也会触发日志滚动

mysql> select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
|        1073741824 |
+-------------------+
1 row in set (0.00 sec)
当日志数据量到达我们设定的值时，也会触发日志滚动

2.5.4 binlog的删除

（1）自动删除
mysql> select @@expire_logs_days;
默认是零
自动删除机制，要根据全备周期+1；一般企业要求，至少2*全备周期+1；

（2） 手工删除
PURGE BINARY LOGS TO 'mysql-bin.000004';            #按照号进行删除，0004以前的全被删除，004保留
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';    #根据时间删除

（3） 全部清空
reset master;        #该操作危险，该部操作之前，做好全备，而且，主从必宕

2.5.5 扩展-基于GTID的日志记录

（1）GTID介绍
    5.6 版本的新特性，5.7进行了增强，建议开启
    全局事务ID号。
    特点：对于每一个事务，都会有一个唯一性的编号。
    GTID具备幂等性，执行过的GTID事务会自动跳过

（2）格式
    server_uuid:TID

(3) 开启GTID
gtid-mode=on
enforce-gtid-consistency=true

(4) 基于GTID的数据恢复
模拟数据
create database bgx;
use bgx
create table t1 (id int);
insert into t1 values(1);
commit;
insert into t1 values(2);
commit;
insert into t1 values(3);
commit;
drop database bgx;

恢复步骤
    1.截取日志
        起点：
| mysql-bin.000004 |  310 | Gtid           |         6 |         375 | SET @@SESSION.GTID_NEXT= 'cc926921-1fe5-11ea-9a5c-000c29aeae9d:2' |
| mysql-bin.000004 |  375 | Query          |         6 |         466 | create database bgx  
        终点：
| mysql-bin.000004 | 1380 | Gtid           |         6 |        1445 | SET @@SESSION.GTID_NEXT= 'cc926921-1fe5-11ea-9a5c-000c29aeae9d:7' |
| mysql-bin.000004 | 1445 | Query          |         6 |        1534 | drop database bgx                                                 |


[root@db01 binlog_3306]# mysqlbinlog --skip-gtids --include-gtids='cc926921-1fe5-11ea-9a5c-000c29aeae9d:2-6'  /data/mysql/binlog_3306/mysql-bin.000004>/tmp/gtid.sql

2. 恢复日志
set sql_log_bin=0;
source /tmp/gtid.sql
set sql_log_bin=1;

注意： 开启GTID之后可以有两种方式截取日志。但是都要在截取是加入 --skip-gtids
面试可以使用的故障案例： gtid模式下的binlog截取及恢复。

3.慢日志

1.记录MySQL中较慢的日志，排查数据库语句问题的日志
2.配置
mysql> show variables like '%slow%';
slow_query_log=on                                         #开启
slow_query_log_file=/data/mysql/data_3306/db01-slow.log    #存放路径
long_query_time=0.1                #定制慢语句时间为0.1秒
log_queries_not_using_indexes=1    #开启不走索引检查

mysql> select @@long_query_time;                    #定制时间

3 模拟慢语句
mysql> select * from t100w limit 10;
mysql> select * from t100w order by  k1 limit 10;
mysql> select * from t100w order by  k1 limit 10;
mysql> select * from t100w order by  k2 limit 10;
mysql> select * from t100w order by  k1,k2 limit 10;
mysql> select * from t100w order by  k1,k2,dt limit 10;
mysql> select * from t100w order by  k1,k2 desc  limit 10;

2.4 分析慢日志

mysqldumpslow -s -c -t 3 db01-slow.sql    #查看前三慢的操作

select 查询优化思路：
    1.看执行计划
    2.索引
    3.语句

数据库慢：
    1.应急性  （当前什么也做不了了） ------- 使用 shwo full processlist; 进行查看，是什么语句导致的
    2. 间歇性 -------》 使用 slow_log 慢日志 进行监控