尽量将日志文件与数据文件放到不同位置存放。

1 错误日志

1.1 作用

记录启动、关闭、日常运行过程中，状态信息、警告、错误

1.2 错误日志配置

默认是开启的，路径在/数据路径/hostname.err
查看错误日志位置

select @@log_error;

手工设定在my.cnf文件中

[mysqld]
...
log_error=/var/log/mysql.log

1.3 日志内容查看

主要关注[ERROR]，看上下文

2 binlog(binary log)二进制日志

2.1 作用

（1）备份恢复必须依赖二进制日志
（2）主从环境必须依赖二进制日志

2.2 binlog配置（5.7必须加server_id）

2.2.1 查看binlog配置

默认没有开启二进制日志

相关参数查看：
查看二进制日志是否开启

select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+

查看二进制日志的文件位置

select @@log_bin_basename;
+------------------------+
| @@log_bin_basename     |
+------------------------+
| /data/binlog/mysql-bin |
+------------------------+

查看服务ID号

select @@server_id;

查看二进制日志格式

select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+

查看双1标准之2

select @@sync_binlog;

2.2.2 配置binlog

2.2.2.1 创建日志目录

mkdir /data/binlog
chown -R mysql:mysql /data/binlog

2.2.2.2 修改配置文件

vim /etc/my.cnf
[mysqld]
.....
server_id=6                        # 使用主从时必须设置server_id
log_bin=/data/binlog/mysql-bin     # mysql-bin是文件前缀，生成的日志文件是mysql-bin.0000X格式
binlog_format=row                  # binlog格式，有三种statement、row、mixed
sync_binlog=1                      #双1标准之2

2.2.2.3 重启数据库生效

systemctl restart mysqld

2.2.2.4 双1标准之2：sync_binlog=1 or N

默认情况下，并不是每次写入时都将binlog日志文件与磁盘同步。因此如果操作系统或服务器崩溃，有可能binlog中最后的语句丢失。
为了防止这种情况，你可以使用“sync_binlog”全局变量（1是最安全的值，但也是最慢的），使binlog在每N次binlog日志文件写入后与磁盘同步。

2.3 binlog记录了什么？

binlog是SQL层的功能，记录的是变更SQL语句，不记录查询语句

2.3.1 记录SQL语句种类

DDL：原封不动的记录当前DDL（statement方式）
DCL：原封不动的记录当前DCL（statement方式）
DML：只记录已经提交的事务DML

2.3.2 DML三种记录方式

对应binlog_format参数
（1）statement(5.6默认) SBR(statement based replication)：语句模式原封不动的记录当前DML语句
（2）ROW(5.7 默认) RBR(row based replication)：记录数据行的变化(用户看不懂，需要工具分析)
（3）mixed(混合) MBR(mixed based replication)：以上两种模式的混合

2.3.3 SBR和RBR模式的比较

STATEMENT：可读性较高，日志量少，但是不够严谨
ROW ：可读性很低，日志量大，足够严谨

例子1：

update t1 set xxx=xxx where id<=1000   

statement模式只记录这行语句
row模式会逐行记录1000行数据的具体变化

例子2：

insert into t1 values(1,'zs',now())

statement模式只记录语句，还原时调用now()函数，将会造成数据不一致。
所以强烈建议使用row模式

2.4 event（事件）是什么？

2.4.1 事件的简介

事件是二进制日志的最小记录单元
对于DDL、DCL，一个语句就是一个event
对于DML语句，只记录已经提交的事务
例如下面的例子，被分为4个event

begin  120 - 340
DML1   340 - 460 
DML2   460 - 550
commit 550 - 760

2.4.2 event的组成

分三部分构成：
(1)事件的开始标识
(2)事件的内容
(3)事件的结束标识

在binlog文件中是用position号来表示事件的开始和结束，相邻两个事件的开始和结束position号是一样的，上一个事件的结束是下一个事件的开始：
开始标识：at XXX
结束标识：end_log_pos XXX

2.5 日志文件总体情况查看

2.5.1 查看日志的开启情况

select @@log_bin;
select @@log_bin_basename;

2.5.2 查看一共有多少个binlog

show binary logs;

[(none)]>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |      2478 |
| mysql-bin.000003 |       154 |
+------------------+-----------+
3 rows in set (0.00 sec)
# File_size 是日志记录的最后的事件结束position号

2.5.3 查看正在使用的binlog文件

show master status;

[(none)]> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
# Position是日志记录的最后的事件结束position号

2.5.4 刷新使用一个新的binlog文件

flush logs;

2.6 日志内容查看

2.6.1 event查看

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

[(none)]>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.20-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 |         313 | create database test                                          |
| mysql-bin.000002 |  313 | Anonymous_Gtid |         6 |         378 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                          |
| mysql-bin.000002 |  378 | Query          |         6 |         501 | use `test`; create table t1(id int)engine=innodb charset=utf8 |
| mysql-bin.000002 |  501 | Anonymous_Gtid |         6 |         566 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                          |
| mysql-bin.000002 |  566 | Query          |         6 |         638 | BEGIN                                                         |
| mysql-bin.000002 |  638 | Table_map      |         6 |         683 | table_id: 229 (test.t1)                                       |
| mysql-bin.000002 |  683 | Write_rows     |         6 |         723 | table_id: 229 flags: STMT_END_F                               |
| mysql-bin.000002 |  723 | Xid            |         6 |         754 | COMMIT /* xid=20 */                                           |

参数解释：
Log_name：binlog文件名
Pos：事件开始的position
Event_type：事件类型
Server_id：mysql服务号标识
End_log_pos：事件的结束position
Info：事件的内容

为了更方便查看，可以在操作系统下使用mysql -e 'show binlog events ....' | grep "create|drop "

2.6.2 binlog文件内容详细查看

mysqlbinlog binlog文件

[root@node1 ~]$ mysqlbinlog /data/binlog/mysql-bin.000002

binlog的头几行会固定显示一些环境信息，5.7版本为position 4~position 154

将binlog中的DML加密部分以更易读的方式显示
mysqlbinlog --base64-output=decode-rows -vvv binlog文件

只显示特定库的binlog
mysqlbinlog -d 库名 binlog文件

显示特定时间段的binlog
mysqlbinlog --start-datetime='年-月-日 时:分:秒' --stop-datetime='年-月-日 时:分:秒' binlog文件

mysqlbinlog --start-datetime='2020-11-27 13:42:17' --stop-datetime='20-11-27 13:43:23' /data/binlog/mysql-bin.000003

显示特定position之间的binlog
mysqlbinlog --start-position=起点号码 --stop-position=终点号码 binlog文件

mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003

2.6.3 基于binlog的数据恢复

binlog日志文件是基于sql层的日志记录，可以直接用于数据恢复。核心在于找到要恢复的事件的起点和终点。
恢复步骤如下：
(1)找到当前使用的binlog日志

[(none)]>show master status；

(2)查看事件信息

[(none)]>show binlog events in 'mysql-bin.000002';

(3)拷贝出binlog日志

[root@node1 ~]$ mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 > /tmp/binlog.sql

(4)防止恢复时重复生成binlog，临时关闭当前窗口binlog记录sql_Log_bin=0

[(none)]>set sql_Log_bin=0;

(5)进行数据恢复source

[(none)]>source /tmp/binlog.sql

2.7 binlog日志的GTID特性

2.7.1 GTID（Global Transaction ID）介绍

是对于一个已提交的事务的编号，并且是一个全局唯一的编号。
GTID=server_uuid + transaction_id

b581db4b-228f-11eb-a061-000c294f5f22:1
server_uuid : transaction_id

server_uuid每次数据库初始化后，也可以在数据目录下的auto.cnf文件中查看

[(none)]>system cat /data/mysql/auto.cnf
[auto]
server-uuid=b581db4b-228f-11eb-a061-000c294f5f22

transaction_id从1开始每次事务加1，DDL、DCL每条语句算1个事务，begin和commit之间的多条DML语句打包算1个事务。

5.6版本中新加入的特性，5.7中做了加强
5.6中不开启，没有这个功能
5.7中即使不开启，也会自动生成ANONYMOUS的GTID

2.7.2 开启GTID

编辑my.cnf配置文件

vim /etc/my.cnf
[mysqld]
...
gtid-mode=on
enforce-gtid-consistency=true

开启后，show mater status 和 show binlog events能看到GTID

show mater status
show binlog events

2.7.3 基于GTID进行查看binlog

mysqlbinlog --include-gtids='GTID x-y' --exclude-gtids='GTID a, GTID b...' binlog文件

mysqlbinlog --include-gtids='b581db4b-228f-11eb-a061-000c294f5f22:3-8' --exclude-gtids='b581db4b-228f-11eb-a061-000c294f5f22:7' /data/binlog/mysql-bin.000005

2.7.4 GTID的幂等性

开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了,为了使用binlog恢复数据，导出binlog时需要加入--skip-gtids参数
例如：

mysqlbinlog --skip-gtids --include-gtids='b581db4b-228f-11eb-a061-000c294f5f22:3-8' --exclude-gtids='b581db4b-228f-11eb-a061-000c294f5f22:7' /data/binlog/mysql-bin.000005 > /tmp/bin.sql

2.7.5 开启GTID后基于binlog恢复数据

(1)找到当前使用的binlog日志

[(none)]>show master status；

(2)查看事件信息

[(none)]>show binlog events in 'mysql-bin.000002';

(3)拷贝出binlog日志

mysqlbinlog --skip-gtids --include-gtids='b581db4b-228f-11eb-a061-000c294f5f22:3-8' --exclude-gtids='b581db4b-228f-11eb-a061-000c294f5f22:7' /data/binlog/mysql-bin.000005 > /tmp/bin.sql

(4)防止恢复时重复生成binlog，临时关闭当前窗口binlog记录sql_Log_bin=0

[(none)]>set sql_Log_bin=0;

(5)进行数据恢复source

[(none)]>source /tmp/bin.sql

2.8 二进制日志其他操作

2.8.1 自动清理日志

参数expire_logs_days控制二进制日志的过期时间，过期的日志将被自动清理，默认expire_logs_days=0，日志永不过期。
一般建议设置为全备周期+1天，例如每周全备一次，则日志过期时间设置为8

set global expire_logs_days=8;

生产中建议至少保留两个全备周期+1的binlog，在my.cnf中设置

vim /etc/my.cnf
[mysqld]
...
expire_logs_days=15;

2.8.2 手工清理日志

按时间清理n天之前的日志
PURGE BINARY LOGS BEFORE now() - INTERVAL n day;
按日志文件编号，清理到0000x编号之前
PURGE BINARY LOGS TO 'mysql-bin.0000x';
删除所有binlog，并从000001开始重新记录日志，慎用！！主从关系中，主库执行此操作，主从环境必崩
reset master;

注意:不要手工 rm binlog文件

2.8.3 日志滚动

日志滚动就是使用一个新的binlog，并将日志编号加1
(1)自动滚动

• 重启mysql会自动滚动一个新的
• 日志文件达到1G大小(max_binlog_size)，一般建议将max_binlog_size设置为128M，防止单个日志文件过大
• 备份时,加入参数也可以自动滚动

(2)手动滚动
flush logs;一般建议在crontab中添加flush logs，定时滚动日志，防止单个文件过大

3 slow_log 慢日志

3.1 作用

记录慢SQL语句的日志，定位低效SQL语句的工具日志，默认没有开启

3.2 开启慢日志

编辑my.cnf配置文件
开关：

slow_query_log=1 

文件位置及名字 :

slow_query_log_file=/data/mysql/slow.log

设定慢查询时间：
单位是秒，默认是10s

long_query_time=0.1

记录没走索引的语句：

log_queries_not_using_indexes=1

3.3 分析慢日志

一般先针对出现频率最高的语句进行优化，使用mysqldumpslow命令

[root@node1 ~]$ mysqldumpslow -s c -t 10 /data/mysql/slow.log
Reading mysql slow query log from /data/mysql/slow.log
Count: 2  Time=0.02s (0s)  Lock=0.00s (0s)  Rows=5.0 (10), root[root]@localhost
  select * from t_100w where id >N limit N

Count: 1  Time=1.34s (1s)  Lock=0.00s (0s)  Rows=990001.0 (990001), root[root]@localhost
  select * from t_100w where id >N

-s c 优先使用出现频率最多的语句排序，默认再使用平均查询时间排序
-t 10 显示top10

参数解释
  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time