1-日志管理
1. 错误日志
1.1 作用
记录数据库启动以来，状态、警告、报错。诊断数据库报错问题。
1.2 配置
默认： 开启状态。存放在数据目录下(/data/3306/data),名字：主机名.err 。
查询配置：
mysql> select @@log_error;
+-------------+
| @@log_error |
+-------------+
| ./db01.err |
+-------------+
1 row in set (0.00 sec)
mysql> select @@datadir;
+------------------+
| @@datadir |
+------------------+
| /data/3306/data/ |
+------------------+
1 row in set (0.00 sec)
2 修改配置：
修改配置：
[root@db01 ~]# mkdir -p /data/3306/logs
[root@db01 ~]# chown -R mysql.mysql /data/*
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#添加：
log_error=/data/3306/logs/mysql.err
3-重启报错
重启报错
[root@db01 ~]# /etc/init.d/mysqld restart
++++++++++++++++++++++
Shutting down MySQL.. SUCCESS!
Starting MySQL.2020-05-09T06:51:36.457401Z mysqld_safe error: log-error set to '/data/3306/logs/mysql.err', however file don't exists. Create writable for user 'mysql'.
ERROR! The server quit without updating PID file (/data/3306/data/db01.pid).
++++++++++++++++++++++
[root@db01 ~]# touch /data/3306/logs/mysql.err
[root@db01 ~]# chown -R mysql.mysql /data/*
重新启动
[root@db01 ~]# /etc/init.d/mysqld restart
4-查看日志
模拟故障
[root@db01 ~]# chown -R root.root /data/3306/data/ibdata1
[root@db01 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. ERROR! The server quit without updating PID file (/data/3306/data/db01.pid).
[root@db01 ~]#
# 排查思路 :
[ERROR] 行：
2020-05-09T06:56:09.226056Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable5-切割：
cp mysql.err mysql.err_`date +%F`
echo > mysql.err2.二进制日志（binlog）
作用
数据恢复。
主从复制。
2.1记录的内容介绍
记录修改类操作(逻辑日志，类似于SQL记录)。
DML： insert update delete
DDL: create drop alter trucate
DCL: grant revoke2.2配置方法
默认： 未开启 。
基础参数查询：
mysql> select @@log_bin;
mysql> select @@log_bin_basename;
mysql> select @@server_id;
设置基础参数：
vim /etc/my.cnf
server_id=6 # 主机ID，在主从复制会使用
log_bin=/data/3306/logs/mysql-bin # 开关+文件路径+文件名前缀 最终格式： mysql-bin.000001
# 重启生效
[root@db01 logs]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS! 2.3binlog内容的记录格式
2.4.1 事件（event）的方式记录
最小的记录单元。
# 每个事件：
(1)事件描述 ： 事件戳、server_id、加密方式、开始的位置（start_pos）、结束位置点（end_pos）
(2)事件内容 : 修改类的操作：SQL 语句 或者 数据行变化。
# 重点关注：
开始的位置（start_pos） ： 1000字节
结束位置点（end_pos） ：
事件内容
2.4二进制日志事件内容格式
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+2.41作用：
对于DDL、DCL语句，直接将SQL本身记录到binlog中
对于DML ： insert、update、delete 受到binlog_format参数控制。
SBR ： Statement : 语句模式。之前版本，默认模式
RBR ： ROW ： 行记录模式。5.7以后，默认模式
MBR ： miexd : 混合模式。2.5.SBR、RBR区别：
update t1 set name='zhangsan' where id<100;
SBR: 记录SQL本身 。RBR: 100个数据行的变化。
SBR日志量少，RBR日志量大。
SBR记录不够准确，RBR记录够准确。
2.6.binlog 的应用
查询
# 日志文件情况查询 :
## 查看所有的日志文件信息
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
## 刷新一个新的日志
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 201 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 201 |
| mysql-bin.000005 | 154 |
+------------------+-----------+
## 当前数据库使用的二进制日志
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2-内容查询
mysql> create database ku charset utf8mb4;
mysql> use ku
Database changed
mysql> create table biao (id int);
mysql> insert into biao values(1);
mysql> commit;2.1-查看日志事件
mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000005 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000005 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 219 | Query | 6 | 323 | create database ku charset utf8mb4 |
| mysql-bin.000005 | 323 | Anonymous_Gtid | 6 | 388 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 388 | Query | 6 | 484 | use `ku`; create table biao (id int) |
| mysql-bin.000005 | 484 | Anonymous_Gtid | 6 | 549 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 549 | Query | 6 | 619 | BEGIN |
| mysql-bin.000005 | 619 | Table_map | 6 | 664 | table_id: 108 (ku.biao) |
| mysql-bin.000005 | 664 | Write_rows | 6 | 704 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000005 | 704 | Xid | 6 | 735 | COMMIT /* xid=24 */ |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
mysql>
## 查看日志内容
[root@db01 logs]# mysqlbinlog /data/3306/logs/mysql-bin.000005
#### create table 日志内容
# at 388
#200509 15:49:59 server id 6 end_log_pos 484 CRC32 0x8b05dfaf Query thread_id=3 exec_time=0 error_code=0
use `ku`/*!*/;
create table biao (id int)
/*!*/;
# at 484
#### insert 操作的日志内容
# at 549
#200509 15:50:36 server id 6 end_log_pos 619 CRC32 0x515eec96 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1589010636/*!*/;
BEGIN
/*!*/;
# at 619
#200509 15:50:36 server id 6 end_log_pos 664 CRC32 0xedfb82fb Table_map: `ku`.`biao` mapped to number 108
# at 664
#200509 15:50:36 server id 6 end_log_pos 704 CRC32 0x5762132e Write_rows: table id 108 flags: STMT_END_F
BINLOG '
zGC2XhMGAAAALQAAAJgCAAAAAGwAAAAAAAEAAmt1AARiaWFvAAEDAAH7gvvt
zGC2Xh4GAAAAKAAAAMACAAAAAGwAAAAAAAEAAgAB//4BAAAALhNiVw=='
/*!*/;
# at 704
#200509 15:50:40 server id 6 end_log_pos 735 CRC32 0xb06a212b Xid = 24
COMMIT/*!*/;
[root@db01 logs]# mysqlbinlog --base64-output=decode-rows -vv /data/3306/logs/mysql-bin.000005
.....略。
BEGIN
/*!*/;
# at 619
#200509 15:50:36 server id 6 end_log_pos 664 CRC32 0xedfb82fb Table_map: `ku`.`biao` mapped to number 108
# at 664
#200509 15:50:36 server id 6 end_log_pos 704 CRC32 0x5762132e Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `ku`.`biao`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
# at 704
#200509 15:50:40 server id 6 end_log_pos 735 CRC32 0xb06a212b Xid = 24
COMMIT/*!*/;
2.2-binlog日志截取及恢复演练
2.21-故障模拟
mysql> drop database ku;
Query OK, 1 row affected (0.01 sec)
# 需求恢复ku的所有数据到删库之前。
思路：
1. 截取从建库以来到删库之前的所有binlog。
### 起点： 建库的位置点（position）。
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 886 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000005';
| mysql-bin.000005 | 219 | Query | 6 | 323 | create database ku charset utf8mb4
### 终点：删库的位置点（position）。
| mysql-bin.000005 | 800 | Query | 6 | 886 | drop database ku
[root@db01 logs]# mysqlbinlog --start-position=219 --stop-position=800 /data/3306/logs/mysql-bin.000005 >/tmp/bin.sql
2. 将截取的日志进行回放。
mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql;
mysql> set sql_log_bin=1;3.彩蛋：场景模拟
思考一下：如果生产中会有什么痛点？
- 需要的日志在多个文件中，怎么截取？
场景模拟：
mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 886 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> create database tongdian charset=utf8mb4;
mysql> use tongdian
mysql> create table t1 (id int);
mysql> flush logs;
mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
mysql> flush logs;
mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
mysql> create table t2(id int);
mysql> insert into t2 values(1),(2),(3);
mysql> commit;
mysql> flush logs;
mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 154 | | | |
mysql> insert into t2 values(11),(22),(33);
mysql> commit;
mysql> drop database tongdian;3.1恢复方法：
方法1： 分段截取
--start-position --stop-position
方法2: 时间戳截取3.2找起点 ：建库的时间戳
（1） 起点： postion 号
mysql> show binlog events in 'mysql-bin.000005';
| mysql-bin.000005 | 951 | Query | 6 | 1073 | create database tongdian charset=utf8mb4 |
（2） 通过position 过滤时间戳
[root@db01 logs]# mysqlbinlog --start-position=951 --stop-position=1073 mysql-bin.000005 |grep -A 1 '^\#\ at\ 951'
# at 951
#200509 17:11:23 server id 6 end_log_pos 1073 CRC32 0x220759ef Query thread_id=8 exec_time=0 error_code=0
3.3找终点
[root@db01 logs]# mysql -e "show binlog events in 'mysql-bin.000008'"
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000008 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000008 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000008 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000008 | 219 | Query | 6 | 295 | BEGIN |
| mysql-bin.000008 | 295 | Table_map | 6 | 344 | table_id: 112 (tongdian.t2) |
| mysql-bin.000008 | 344 | Write_rows | 6 | 394 | table_id: 112 flags: STMT_END_F |
| mysql-bin.000008 | 394 | Xid | 6 | 425 | COMMIT /* xid=114 */ |
| mysql-bin.000008 | 425 | Anonymous_Gtid | 6 | 490 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000008 | 490 | Query | 6 | 594 | drop database tongdian |
mysqlbinlog mysql-bin.000008
[root@db01 logs]# mysqlbinlog mysql-bin.000008
#200509 17:13:52
3.4截取日志
[root@db01 logs]# mysqlbinlog --start-datetime="2020-05-09 17:11:23" --stop-datetime="2020-05-09 17:14:01" mysql-bin.000005 mysql-bin.000006 mysql-bin.000007 mysql-bin.000008 >/tmp/data.sql
10个文件 ：
1 和 10 pos
中间
mysqlbinlog start-pos 1 >/tmp/1.sql
mysqlbinlog 2、9 > /tmp/2.sql
mysqlbinlog stop-pos 10 >/tmp/3.sql
3-gtid取日志
1-日志截取
1-只截取某个库
binlog属于全局日志，日志中有其他库的操作，怎么排除掉？
1-只截取某个库
mysqlbinlog -d oldboy mysql-bin.000008 > /tmp/bin.sql
2-pager grep ‘drop’ 过滤
binlog中100w个事件，怎么快速找到drop database的位置点？
[root@db01 ~]# mysql -e "show binlog events in 'mysql-bin.000014'" |less
[root@db01 ~]# mysql -e "show binlog events in 'mysql-bin.000014'" |grep
比如删除的库，建库是在2年前操作的。这种情况怎么办？ 每天全备，binlog完好的。 可以使用 全备+binlog方式实现恢复数据故障之前。
2-基于GTID的binlog应用
1-GTID 全局事务ID。
对每个事务，进行单独编号。连续不断进行增长。
2.6.2 表示方式
server_uuid:N2-配置
# 查看参数
mysql> show variables like '%GTID%';
# 设置参数
vim /etc/my.cnf
gtid_mode=ON #开关
enforce_gtid_consistency=ON #强制GTID一致性
log_slave_updates=ON #强制从库更新binlog
# 建议： 5.7版本以后，都开启GTID。最好是搭建环境就开启。
3-应用
3.1-模拟环境
mysql> create database gtdb charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> show master status ;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000016 | 329 | | | 202628e9-9265-11ea-b4a0-000c29248f69:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
mysql> use gtdb;
mysql> create table t1(id int);
mysql> show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000016 | 491 | | | 202628e9-9265-11ea-b4a0-000c29248f69:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
mysql> begin;
mysql> insert into t1 values(1);
mysql> insert into t1 values(2);
mysql> insert into t1 values(3);
mysql> commit;
mysql> show master status ;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000016 | 914 | | | 202628e9-9265-11ea-b4a0-000c29248f69:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
-----------4-通过GTID方式截取日志
错误的截取
[root@db01 data]# mysqlbinlog --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:1-3' /data/3306/logs/mysql-bin.000016 >/tmp/gt.sql
补充： 为什么报错？
gtid有“幂等性”检查。GTID的生成，通过Set gtid_next命令实现的。
例如：
SET @@SESSION.GTID_NEXT= '202628e9-9265-11ea-b4a0-000c29248f69:1'
执行Set命令时，自动检查当前系统是否包含这个GTID信息，如果有就跳过。
4.1-正确的方式：
[root@db01 data]# mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:1-3' /data/3306/logs/mysql-bin.000016 >/tmp/gt1.sql
--skip-gtids 必须开启 跳过幂等性检测思考问题： 开了GTID方式之后，是否可以pos方式截取？需要不需要加skip-gtids? 可以。需要加--skip-gtids
4.2-扩展用法
需求： gtid ： 1-10 跳过第5个
需求： gtid ： 1-10 跳过第5个
mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:1-10' --exclude-gtids='202628e9-9265-11ea-b4a0-000c29248f69:5'
需求： 跨文件截取，bin001 bin002 bin003 202628e9-9265-11ea-b4a0-000c29248f69:1-10
mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:1-10' bin001 bin002 bin003
3- 日志滚动
1-命令触发
mysql> flush logs;
shell# mysqladmin flush-logs
shell# mysql -e "flush logs"
shell# mysqldump -F
2-自动触发
mysql> select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
| 1073741824 |
+-------------------+
重启数据库，会触发刷新
4-日志删除方式
默认：不自动清理。直到空间写满。
1- 配置自动清理
mysql> select @@expire_logs_days;
最少设置多少天合适？
参考全备时间周期。
expire_logs_days=8
例如： 全备周期是7天。可以保留8天。一般生产中保留两轮备份周期的日志，15天。
2-手工清理
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';
purge binary logs to 时间或者3-删除
删除 到 10 10 不会删除
db01_5.7 [(none)]>purge binary logs to 'mysql-bin.000010';4-慢日志 slwlog
作用： 记录MySQL工作过程中较慢的语句
默认没有开启。按需求打开。
1-如何配置
1.1在线配置
mysql> select @@slow_query_log; # 开关
mysql> set global slow_query_log=1; # 在线改
mysql> select @@slow_query_log_file; # 文件位置。离线改。
mysql> select @@long_query_time; # 慢查询时间设定。
mysql> set global long_query_time=0.1; # 在线设置，最低微秒级别。
mysql> select @@log_queries_not_using_indexes #如果没走索引会被记录
mysql> set global log_queries_not_using_indexes=1; #在线设置
1.2-永久生效：
vim /etc/my.cnf
slow_query_log=1
slow_query_log_file=/data/3306/logs/slow.log
long_query_time=0.1
log_queries_not_using_indexes=1
#重启生效。随机模拟慢语句
自行创出
2-慢日志分析
···
mysqldumpslow -s c -t 5 slow.log -s 排序 - c 语句发生频次 -t 5 最慢的5 条语句
Reading mysql slow query log from slow.log
Count: 7 Time=0.01s (0s) Lock=0.00s (0s) Rows=177.1 (1240), root[root]@localhost
select * from t100w as a join t100w as b limit N
Count: 6 Time=0.57s (3s) Lock=0.00s (0s) Rows=33.7 (202), root[root]@localhost
select k1,count(*) from t100w where id<N group by k1 having count(*)>N
Count: 5 Time=0.59s (2s) Lock=0.00s (0s) Rows=893.0 (4465), root[root]@localhost
select k1,count(*) from t100w where num<N group by k1,k2
Count: 5 Time=0.61s (3s) Lock=0.00s (0s) Rows=37.0 (185), root[root]@localhost
select * from t100w where id<N order by num desc
Count: 4 Time=0.61s (2s) Lock=0.00s (0s) Rows=496.0 (1984), root[root]@localhost
select k1,count(*) from t100w where id<N group by k1,k2
