日志文件
日志的管理
1.错误日志
作用
排查MySQL运行过程的故障
配置
默认开启
默认路径和名字：datadir/hostname.err
查看方法：[ERROR]
slect @@log_error
修改路径
[mysqld]
log_error=/tmp/mysql3306.log
2二进制日志(binlog)
作用
a.主从要依赖二进制日志
b.数据恢复时需要依赖二进制日志
配置（默认没有开启）
开启二进制
server_id=1~65535
log_bin=/data/binlog/mysql-bin #存放路径，也可以使用1
说明:
目录首先要创建好
a. mkdir -p /data/binlog/
b.chown -R mysql.mysql /data/binlog/
c./data/binlog 提前定制好的目录，而且要有mysql.mysql的权限
d.mysql-bin 二进制文件名的前缀，列如:mysql-bin.000001, mysql-bin.000002
e.mysql-bin.index 二进制索引文件
binlog_format=row #5.7默认配置row
二进制日志记录了什么？
记录的数据库所有变更类的操作日志
DDL,DCL,DML
DDL和DCL 以语句的方式，原模原样的记录
DML
1.他记录的已提交的事务
2.DML记录格式（statement,row,mixed）,通过binlog_format=row参数控制
statement:SBR 语句模式记录日志，做什么命令记录什么命令
row :RBR 行模式，数据行的变化
mixed :MBR 混合模式
SBR和RBR什么区别？怎么选择
SBR：可读性强，对于范围操作日志量少 ，但是会出现记录不准确的情况
update t1 set name='zs' where id >5;
insert into t1 value(1,'zs',now()); 时间会出现问题
RBR: 可读性较弱，对于范围操作日志大，但不会出现记录错误
高可用环境中的新特性要依赖于RBR
二进制日志记录单元
event 事件
二进制日志的最小单元
DDL：create database sky; 事件
对于DDL等语句是每一个语句就是一个事件
DML：
begin; 事件1
a 事件2
b 事件3
commit; 事件4
一个事务包含了多个语句
event事件的开始和结束号码
作用：方便我们从日志中截取我们想要的日志事件
二进制日志的管理
查看二进制位置
show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------+
| log_bin | ON |
| log_bin_basename | /data/binlog/mysql-bin |
| log_bin_index | /data/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------------+
6 rows in set (0.05 sec)
查询已存在的二进制日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.02 sec)
滚动生成新的
mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 154 |
+------------------+-----------+
2 rows in set (0.00 sec)
查看正在使用的二进制日志
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
查看二进制日志事件
模拟点数据
mysql> create database binlog charset utf8mb4;
mysql> use binlog;
mysql> create table t1(id int);
mysql> insert into t1 values(1);
先不要commit
mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000004 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000004 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 219 | Query | 6 | 335 | create database binlog charset utf8mb4 |
| mysql-bin.000004 | 335 | Anonymous_Gtid | 6 | 400 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 400 | Query | 6 | 501 | use `binlog`; create table t1(id int) |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
6 rows in set (0.00 sec)
mysql>commit;
mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000004 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000004 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 219 | Query | 6 | 335 | create database binlog charset utf8mb4 |
| mysql-bin.000004 | 335 | Anonymous_Gtid | 6 | 400 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 400 | Query | 6 | 501 | use `binlog`; create table t1(id int) |
| mysql-bin.000004 | 501 | Anonymous_Gtid | 6 | 566 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 566 | Query | 6 | 640 | BEGIN |
| mysql-bin.000004 | 640 | Table_map | 6 | 687 | table_id: 109 (binlog.t1) |
| mysql-bin.000004 | 687 | Write_rows | 6 | 727 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000004 | 727 | Xid | 6 | 758 | COMMIT /* xid=17 */ |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
11 rows in set (0.00 sec)
截取事务
BEGIN开始 566 ----- commit 758
查看内容
# at 566
#191222 22:50:58 server id 6 end_log_pos 640 CRC32 0xe0267113 Query thread_id=2 exec_time=0 error_code=0
BEGIN
/*!*/;
# at 640
#191222 22:50:58 server id 6 end_log_pos 687 CRC32 0xac58fca0 Table_map: `binlog`.`t1` mapped to number 109
# at 687
#191222 22:50:58 server id 6 end_log_pos 727 CRC32 0xc46075ee Write_rows: table id 109 flags: STMT_END_F
BINLOG ' #行模式的记录
0oL/XRMGAAAALwAAAK8CAAAAAG0AAAAAAAEABmJpbmxvZwACdDEAAQMAAaD8WKw=
0oL/XR4GAAAAKAAAANcCAAAAAG0AAAAAAAEAAgAB//4BAAAA7nVgxA==
'/*!*/;
# at 727
#191222 22:55:46 server id 6 end_log_pos 758 CRC32 0x2cc81eb5 Xid = 17
COMMIT/*!*/;
翻译一下binlog日志
# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000004
### INSERT INTO `binlog`.`t1`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
日志的截取
举例：截取建库语句
mysql> show binlog evenmysql-bin.000004'；
| mysql-bin.000004 | 219 | Query | 6 | 335 | create database binlog charset utf8mb4 |
简单的截取
mysqlbinlog --start-position=219 --stop-position=335 mysql-bin.000004 >/tmp/a.sql
通过binlog回复数据
环境准备
create database sky charset utf8mb4;
create table t1(id int);
insert into t1 values(1);
commit;
模拟故障
drop database sky;
基于binlog恢复
首先确认用的那个日志
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 640 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
查看事件
mysql> 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 | 326 | create database sky charset utf8mb4 |
| mysql-bin.000002 | 326 | Anonymous_Gtid | 6 | 391 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 391 | Query | 6 | 486 | use `sky`; create table t1(id int) |
| mysql-bin.000002 | 486 | Anonymous_Gtid | 6 | 551 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 551 | Query | 6 | 640 | drop database sky |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
8 rows in set (0.00 sec)
起点 219 结束位置551
mysqlbinlog --start-position=219 --stop-position=551 /data/binlog/mysql-bin.000002 >/tmp/bin.sql
临时关闭恢复时产生的新日志
set sql_log_bin=0;
source /tmp/bin.sql
set sql_log_bin=1;
show databases;
只过滤某个库的二进制
mysqlbinlog -d sky
binlog的GTID记录模式的管理
GTID介绍
对于binlog中的每一个事务，都会生成一个GTID号码
DDL ,DCL 一个event 就是一个事务，就会有一个GTID号
DML语句来讲，begin到commit是一个事务，就是一个GTID号
GTID组成
server_uuid:TID
cat /data/auto.cnf
[auto]
server-uuid=598d70ef-1bda-11ea-85b7-000c29f4a942
TID是一个自增长的数据，从1开始
598d70ef-1bda-11ea-85b7-000c29f4a942：1-15
GTID的幂等性
如果拿有GTID的日志去恢复时，检查当前系统中是否有相同的GTID号，有相同的就自动跳过
会影响到binlog恢复和主从复制
GTID的开启和配置
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true
systemctl restart mysqld
查看GTID信息
首先创建个库
create database gtid charset utf8mb4;
show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000005 | 329 | | | c01ce874-0b98-11ea-8819-000c296fff83:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
mysql> use gtid;
在创建个表
mysql> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 | 491 | | | c01ce874-0b98-11ea-8819-000c296fff83:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
插入数据，如果没有commit 是不算事务的
mysql> insert into t1 values(1);
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 | 491 | | | c01ce874-0b98-11ea-8819-000c296fff83:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> commit;
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000005 | 744 | | | c01ce874-0b98-11ea-8819-000c296fff83:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
删除库
drop database gtid ；
基于GTID恢复
mysql> show binlog events in 'mysql-bin.000005';
cd /data/binlog/
mysqlbinlog --include-gtids='c01ce874-0b98-11ea-8819-000c296fff83:1-3' mysql-bin.000005 >/tmp/gtid.sql
mysql -uroot -p123456
set sql_log_bin=0;
source /tmp/gtid.sql 会报错
因为幂等性的检查，1-3事务已经做过了
正确的做法：
mysqlbinlog --skip-gtids --include-gtids='c01ce874-0b98-11ea-8819-000c296fff83:1-3' mysql-bin.000005 >/tmp/gtid.sql
--skip-gtids 作用：在导出时，忽略原有的gtid信息，恢复时生成最新的
set sql_log_bin=0;
source /tmp/lgitd.sql
set sql_log_bin=1;
select * from t1;
GTID相关参数
--skip-gtids 把原来的GTID抹掉
--include-gtids= 截取时
--exclude-gitds= 排除那些GTID（排除多个 'c01ce874-0b98-11ea-8819-000c296fff83:4'，'c01ce874-0b98-11ea-8819-000c296fff83：6' 连续的'c01ce874-0b98-11ea-8819-000c296fff83:6-9' ）
慢日志（slow-log）
作用：记录运行较慢的语句，优化过程中常用的工具日志
配置慢日志
开关
slow_query_log=1
文件位置及名字
slow_query_log_file=/data/mysql/slow.log
设置慢查询时间
long_query_time=0.1
没有走索引的语句也记录
log_queries_not_using_indexes
随便执行点慢查询
vim /data/mysql/slow.log
分析慢查询日志
mysqldumpslow -s c -t 10 /data/mysql/slow.log
-s 排序 c 次数 -t top
第三方工具
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慢查询可视化
二进制日志清理
自动
expire_logs_days=15 设置的依据 ：至少1轮全备周期长度的过期时间
写在配置文件中即可
手动
用的很少
help purge
删除前9个，到000010为止
PURGE BINARY LOGS TO 'mysql-bin.000010';
删除2008年-04-02 22:46:26之前的
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
reset master; 会从mysql-bin.000001开始，如果有主从复制 想好在弄，会崩
滚动日志
flush logs;
数据库重启
二进制文件最大大小(默认1G)
mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)
小总结：
开启binlog日志
log_bin
binlog_format=row
RBR
SBR
sync_binlog=1 每次事务提交都立马刷写binlog到磁盘
show master status；查看数据库使用的binlog日志
show binlog events in 'mysql-bin.000001' 查看某个binlog事件
show binary logs; 查看日志大小
非GTID
mysqlbinlog --start-postition= --stop-postition=
mysqlbinlog --base64-output=decode-rows -vvv
GTID
如果不记得参数
show variables like '%gtid%'；
--skip-gtids
--include-gitds=''
--exclude-gitds=''
set sql_log_bin=1,0 暂时不记录binlog日志
source ... 导入SQL语句
慢查询
show variables like 'slow_query_log%' 开启和日志位置
show variables like 'long%' 时间的阈值
show variables like '%using_indexes%' 记录出发条件
mysqldumpslow -s c -t 10 /xx/xx.000001
