MySQL 日志管理
1. 错误日志
1.1 作用：
记录MySQL从启动以来，所有的状态、警告、错误。
为我们定位数据库问题，提供帮助。
1.2 配置方法
默认：开启状态，/datadir/hostname.err mysql> select @@datadir; +-------------+ | @@datadir | +-------------+ | /data/3306/ | +-------------+ 1 row in set (0.00 sec) [root@master1 ~]# hostname master1 [root@master1 ~]# ll /data/3306/master1.err -rw-r-----. 1 mysql mysql 54169 Aug 2 20:46 /data/3306/master1.err
定制方法：
vim /etc/my.cnf log_error=/tmp/mysql.log 说明：日志目录必须得提前有，并且mysql有权限写入。 重启生效
# 判断数据库启动不了的方法，把信息打到屏幕上，可看到所有启动输出
[root@master ~]$ mysqld &
1.3 怎么看错误日志
[ERROR] 上下文
2. binlog二进制日志
2.1 作用：
主要记录数据库变化（DDL，DCL，DML）性质的日志。是逻辑层性质日志。
数据恢复，主从复制中应用
2.2 如何配置
默认：8.0版本以前，没有开启。我们建议生产开启。 配置方法： vim /etc/my.cnf server_id=6 # 主机编号。主从中使用，5.7以后开binlog要加此参数 log_bin=/data/binlog/mysql-bin # 日志存放目录+日志名前缀 例如：mysql-bin.000001 sync_binlog=1/0 # binlog日志刷盘策略，双一中的第二个1。每次事务提交立即刷写binlog到磁盘 binlog_format=row # binlog的记录格式为row模式 expire_logs_days
说明：一定要和数据盘分开。 例如： /dev/sdb --/data/3306 /dev/sdc --/data/binlog [root@db01 ~]$ mkdir -p /data/binlog [root@db01 ~]$ chown -R mysql:mysql /data/* [root@db01 ~]$ /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@db01 ~]$ cd /data/binlog/ [root@db01 binlog]$ ll 总用量 12 -rw-r----- 1 mysql mysql 177 7月 14 19:42 mysql-bin.000001 -rw-r----- 1 mysql mysql 154 7月 14 19:42 mysql-bin.000002 -rw-r----- 1 mysql mysql 60 7月 14 19:42 mysql-bin.index
2.3 binlog记录内容详解
2.3.0 引入
binlog是SQL层的功能。记录的是变更SQL语句，不记录查询语句。
2.3.1 记录SQL语句种类
DDL ：原封不动的记录当前DDL(statement语句方式)。
DCL ：原封不动的记录当前DCL(statement语句方式)。
DML ：只记录已经提交的事务DML（insert，update，delete）
2.3.2 DML三种记录方式
binlog_format（binlog的记录格式）参数影响 （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 ：可读性很低，日志量大，足够严谨
update t1 set xxx=xxx where id>1000 ? -->一共500w行，row模式怎么记录的日志 为什么row模式严谨？ id name intime insert into t1 values(1,'zs',now()) 我们建议使用：row记录模式
2.4 event（事件）是什么?
2.4.1 事件的简介
二进制日志的最小记录单元
对于DDL,DCL语句：一个语句就是一个event
对于DML语句来讲：只记录已提交的事务。
例如以下列子,就被分为了4个event position start stop begin; 120 - 340 DML1 340 - 460 DML2 460 - 550 commit; 550 - 760
2.4.2 event的组成
三部分构成: (1) 事件的开始标识 (2) 事件内容 (3) 事件的结束标识
Position: 开始标识: at 194 结束标识: end_log_pos 254 194? 254? 某个事件在binlog中的相对位置号 位置号(position)的作用是什么？ 为了方便我们截取事件
2.5 binlog的查看
2.5.1 查看开启情况
mysql> select @@log_bin; mysql> select @@log_bin_basename;
2.5.2 文件查看
[root@db01 ~]$ ls -l /data/binlog 总用量 12 -rw-r----- 1 mysql mysql 177 7月 14 19:42 mysql-bin.000001 -rw-r----- 1 mysql mysql 154 7月 14 19:42 mysql-bin.000002 -rw-r----- 1 mysql mysql 60 7月 14 19:42 mysql-bin.index [root@db01 ~]$ cat /data/binlog/mysql-bin.index /data/binlog/mysql-bin.000001 /data/binlog/mysql-bin.000002 [root@db01 ~]$ file /data/binlog/mysql-bin.000001 /data/binlog/mysql-bin.000001: MySQL replication log
2.5.3 二进制内置查看命令
（1）查看目前有几个日志文件
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 177 | | mysql-bin.000002 | 154 | +------------------+-----------+ 2 rows in set (0.00 sec)
（2）查看当前在用的binlog
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
（3）查看二进制日志事件
mysql> create database oldguo1 charset utf8mb4; mysql> show binlog events in 'mysql-bin.000002'; mysql> grant all on *.* to root@'10.0.0.%' identified by 'mysql'; mysql> show binlog events in 'mysql-bin.000002'; mysql> use world mysql> begin; mysql> delete from city where id<10; mysql> commit; mysql> show binlog events in 'mysql-bin.000002';
====================================================================================
1. binlog 文件内容查看及数据恢复
1.1 事件查看
（1）
mysql> show binlog events in 'mysql-bin.000001'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 | | mysql-bin.000001 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000001 | 154 | Stop | 6 | 177 | | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 3 rows in set (0.00 sec)
Log_name ：binlog文件名 Pos ：开始的position ***** Event_type ：事件类型 Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息 Server_id ：mysql服务号标识 End_log_pos：事件的结束位置号 ***** Info ：事件内容***** 补充: SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
（2）
[root@db01 ~]$ mysql -uroot -pmysql -e "show binlog events in 'mysql-bin.000002'" | grep DROP
1.2 内容查看
（1）普通查看
[root@db01 ~]$ mysqlbinlog /data/binlog/mysql-bin.000002 >/tmp/a.sql [root@db01 ~]$ vim /tmp/a.sql
DDL： # at 219 #200714 21:17:31 server id 6 end_log_pos 338 create database oldguo1 charset utf8mb4 # at 338
DML： # at 690 BEGIN # at 763 #200714 21:33:54 server id 6 end_log_pos 821 # at 821 #200714 21:33:54 server id 6 end_log_pos 1107 hg== QrQNXyAGAAAAHgEAAFMEAAAAAGwAAAAAAAEAAgAF/+ACAAAAB29sZGdpcmwDQUZHCFFhbmRhaGFy vJ8DAOADAAAAB29sZGdpcmwDQUZHBUhlcmF0sNkCAOAEAAAAB29sZGdpcmwDQUZHBUJhbGtoOPMB AOAFAAAAB29sZGdpcmwDTkxEDU5vb3JkLUhvbGxhbmRAKAsA4AYAAAAHb2xkZ2lybANOTEQMWnVp ZC1Ib2xsYW5kqQ0JAOAHAAAAB29sZGdpcmwDTkxEDFp1aWQtSG9sbGFuZES6BgDgCAAAAAdvbGRn aXJsA05MRAdVdHJlY2h0U5MDAOAJAAAAB29sZGdpcmwDTkxEDU5vb3JkLUJyYWJhbnRzFAMA3Wvl RA== # at 1107 #200714 21:33:57 server id 6 end_log_pos 1138 COMMIT/*!*/; # at 1138
（2）详细查看DML
[root@db01 ~]$ mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000002 >/tmp/b.sql [root@db01 ~]$ vim /tmp/b.sql delete * from city where id<10; ### DELETE FROM `world`.`city` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='oldgirl' /* STRING(140) meta=65164 nullable=0 is_null=0 */ ### @3='AFG' /* STRING(12) meta=65036 nullable=0 is_null=0 */ ### @4='Herat' /* STRING(80) meta=65104 nullable=0 is_null=0 */ ### @5=186800 /* INT meta=0 nullable=0 is_null=0 */
（3）查看参数
[root@db01 ~]$ mysqlbinlog --help --base64-output=name Determine when the output statements should be base64-encoded BINLOG statements: 'never' disables it and works only for binlogs without row-based events; 'decode-rows' decodes row events into commented pseudo-SQL statements if the --verbose option is also given; 'auto' prints base64 only when necessary (i.e., for row-based events and format description events). If no --base64-output[=name] option is given at all, the default is 'auto'.
1.3 日志截取恢复
日志恢复案例：
（1）刷新出一个新的binlog文件/滚动一个新的日志 ----》flush logs mysql> show binary logs; mysql> flush logs; mysql> show binary logs;
（2）模拟数据环境： mysql> show binary logs; mysql> create database bindb charset utf8mb4; mysql> use bindb mysql> create table t1(id int); mysql> begin; mysql> insert into t1 values(1),(2),(3); mysql> commit; mysql> begin; mysql> insert into t1 values(11),(22),(33); mysql> commit; mysql> begin; mysql> insert into t1 values(111),(222),(333); mysql> commit; mysql> select * from t1; mysql> drop database bindb; mysql> show databases; （3）数据恢复 分析binlog 起点： mysql> show master status; mysql> show binlog events in 'mysql-bin.000003'; | 219 | 332 | create database bindb charset utf8mb4 | 终点： | 1356 | 1451 | drop database bindb | 截取日志 [root@db01 ~]$ mysqlbinlog --start-position=219 --stop-position=1356 /data/binlog/mysql-bin.000003 >/tmp/bin.sql; 恢复日志 mysql> set sql_log_bin=0; mysql> source /tmp/bin.sql mysql> set sql_log_bin=1; 验证数据 mysql> show databases; mysql> use bindb; mysql> select * from t1; 或 mysql> select * from bindb.t1;
思考一个问题：如果是生产环境中，此种恢复手段会有什么弊端？
1. binlog记录不单单一个数据库的操作，可能对其他数据库重复操作？ mysqlbinlog -d bindb --start-position=219 --stop-position=1356 /data/binlog/mysql-bin.000003 >/tmp/bin.sql; 2. 需要的日志在多个文件中分布 起点：加入在1号文件，假如，mysql-bin.000001, 4600 终点：一般是最后一个文件，假如，mysql-bin.000002, 980 mysqlbinlog --start-datetime= --stop-datetime=1356 /data/binlog/mysql-bin.000001 /data/binlog/mysql-bin.000002 >/tmp/bin.sql; 3. 创建了几年，期间一直在用的数据库，插入数据的操作从bin_log。000001到bin_log。000001234之中都有的库，被删除了，怎么恢复？ 4. 数据多了咋办，数据行多？ 假设：每周六做全备份23:00，binlog每天备份23:00。 故障点 周三 10点 drop操作 binlog实际上是我们数据恢复是配合备份一起恢复数据的手段。
==============================================================
2. binlog 维护操作
2.1 日志滚动
（1）mysql> flush logs; （2）mysql> select @@max_binlog_size; （3）[root@db01 ~]$ mysqladmin -uroot -pmysql fulsh-logs （4）[root@db01 binlog]$ mysqldump -F （5）重启数据库自动滚动 以上为日志触发情景。
2.2 日志的删除
注意：不要使用rm命令删除日志。
2.2.1 自动删除机制
mysql> select @@expire_logs_days; 默认是0，单位是天，代表永不删除。 问题: 到底设置多少天合适？阈值？ 一个全备份周期。7+1天一般生产一般建议，最少2个全备周期+1
2.2.2 手工删除
mysql> help purge; mysql> help purge binary logs; Examples: PURGE BINARY LOGS TO 'mysql-bin.010'; PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26'; mysql> show binary logs; mysql> purge binary logs to 'mysql-bin.000006'; mysql> show binary logs;
2.2.3 全部清空
mysql> reset master;
比较危险，在主库执行此操作，主从必宕。
======================================================================
3. binlog的GTID模式管理
3.1 GTID 介绍
5.6 版本新加的特性,5.7 8.0中做了加强 5.6 中不开启,没有这个功能. 5.7 中的GTID,即使不开也会有自动生成 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
3.2 GTID(Global Transaction ID)
是对于一个已提交事务的编号，并且是一个全局唯一的编号。 它的官方定义如下： GTID = server_uuid ：transaction_id 7E11FA47-31CA-19E1-9E56-C43AA21293967:29
3.3 重要参数介绍：
vim /etc/my.cnf gtid-mode=on enforce-gtid-consistency=true
DDL： mysql> select @@gtid_mode; mysql> show master status; mysql> create database gtid_text; mysql> show master status; mysql> show binlog events in 'mysql-bin.000002'; mysql> create database gtid_text1; mysql> show binlog events in 'mysql-bin.000002'; mysql> create database gtid_text2; mysql> show binlog events in 'mysql-bin.000002'; mysql> select @@server_uuid;
DML：（事务） mysql> begin; mysql> use world mysql> delete from city where id=1; mysql> commit; mysql> show binlog events in 'mysql-bin.000002';
3.4 基于GTID进行查看binlog
具备GTID后,截取查看某些事务日志: --include-gtids --exclude-gtids mysqlbinlog --skip-gtids --include-gtids='066d65da-b0a8-11ea-affd-000c29682dd4:5-11' mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 >/tmp/gtdb1.sql mysqlbinlog --skip-gtids --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4' /data/binlog/mysql-bin.000004
例子：（跨多文件的）
# 第一波命令 mysql> show master status; mysql> create database gtdb charset utf8mb4; mysql> use gtdb mysql> create table t1(id int); mysql> begin; mysql> insert into t1 values(1),(2),(3); mysql> commit; mysql> flush logs; mysql> show master status; # 第二波命令 mysql> create table t2(id int); mysql> begin; mysql> insert into t2 values(1),(2),(3); mysql> commit; mysql> flush logs; mysql> show master status; # 第三波命令 mysql> create table t3(id int); mysql> begin; mysql> insert into t3 values(1),(2),(3); mysql> commit; mysql> show master status; mysql> drop database gtdb; # 截取日志 起点： mysql> show binlog events in 'mysql-bin.000002'; ++++++++++++++++++++++++++++++++++++++++++++++++ SET @@SESSION.GTID_NEXT= '066d65da-b0a8-11ea-affd-000c29682dd4:5' | create database gtdb charset utf8mb4 ++++++++++++++++++++++++++++++++++++++++++++++++ 终点： mysql> show master status; mysql> show binlog events in 'mysql-bin.000004'; ++++++++++++++++++++++++++++++++++++++++++++++++ SET @@SESSION.GTID_NEXT= '066d65da-b0a8-11ea-affd-000c29682dd4:12' | drop database gtdb ++++++++++++++++++++++++++++++++++++++++++++++++ 得出结果： gtid：5-11 文件：mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 # 截取： [root@db01 ~]$ cd /data/binlog/ [root@db01 binlog]$ mysqlbinlog --include-gtids='066d65da-b0a8-11ea-affd-000c29682dd4:5-11' mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 >/tmp/gtdb.sql mysql> set sql_log_bin=0; mysql> source /tmp/gtdb.sql mysql> show databases; 未成功
3.5 GTID的幂等性
开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了 就想恢复?怎么办? --skip-gtids [root@db01 binlog]$ mysqlbinlog --skip-gtids --include-gtids='066d65da-b0a8-11ea-affd-000c29682dd4:5-11' mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 >/tmp/gtdb1.sql [root@db01 binlog]$ vimdiff /tmp/gtdb.sql /tmp/gtdb1.sql
mysql> set sql_log_bin=0; mysql> source /tmp/gtdb1.sql mysql> set sql_log_bin=1; mysql> show databases; mysql> use gtdb mysql> show tables; mysql> select * from t1; mysql> select * from t2; mysql> select * from t3; 成功！
3.6 查看server_uuid
[root@db01 ~]$ cat /data/3306/auto.cnf [auto] server-uuid=066d65da-b0a8-11ea-affd-000c29682dd4 mysql> select @@server_uuid; +--------------------------------------+ | @@server_uuid | +--------------------------------------+ | 066d65da-b0a8-11ea-affd-000c29682dd4 | +--------------------------------------+ 1 row in set (0.00 sec)
4. slowlog 慢日志
4.1 作用：
记录MySQL运行过程中较慢的语句，通过一个文本的文件记录下来。
帮助我们进行语句优化工具日志。
4.2 如何配置
默认慢日志没有开启。 配置参数： mysql> select @@slow_query_log; # 是否开启 mysql> select @@slow_query_log_file; # 文件存放位置 mysql> select @@long_query_time; # 慢语句评估/认定时间阈值 mysql> select @@log_queries_not_using_indexes; # 不走索引的语句也记录
vim /etc/my.cnf slow_query_log=1 slow_query_log_file=/data/3306/db01-slow.log long_query_time=0.1 log_queries_not_using_indexes=1 重启数据库生效。 [root@db01 ~]$ cd /data/3306 -rw-r----- 1 mysql mysql 187 7月 15 09:08 db01-slow.log
4.3 模拟慢语句（t100w表）
mysql> set sql_log_bin=0; mysql> source /root/t100w.sql mysql> set sql_log_bin=1; [root@db01 ~]$ cd /data/3306 [root@db01 3306]$ > db01-slow.log -rw-r----- 1 mysql mysql 0 7月 15 09:16 db01-slow.log
select * from t100w limit 500000,10; select * from t100w limit 600000,10; select * from t100w limit 600000,1; select * from t100w limit 600000,2; select id,count(num) from t100w group by id limit 10; select id,count(num) from t100w group by id limit 5; select id,count(num) from t100w group by id limit 2; select id,count(num) from t100w group by id limit 2; select id,count(k1) from t100w group by id limit 1; select id,count(k2) from t100w group by id limit 1; select k2,sum(id) from t100w group by k2 limit 1; select k2,sum(id) from t100w group by k2,k1 limit 1; select k2,sum(id) from t100w group by k2,k1 limit 1; select k1,sum(id) from t100w group by k2,k1 limit 1; select k1,count(id) from t100w group by k1 limit 10;
4.4 慢语句分析
[root@db01 3306]$ vim db01-slow.log [root@db01 3306]$ mysqldumpslow -s c -t 5 /data/3306/db01-slow.log Reading mysql slow query log from /data/3306/db01-slow.log Count: 4 Time=0.29s (1s) Lock=0.00s (0s) Rows=5.8 (23), root[root]@localhost select * from t100w limit N,N Count: 4 Time=3.51s (14s) Lock=0.00s (0s) Rows=4.8 (19), root[root]@localhost select id,count(num) from t100w group by id limit N Count: 2 Time=1.91s (3s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost select k2,sum(id) from t100w group by k2,k1 limit N Count: 1 Time=3.07s (3s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost select id,count(k2) from t100w group by id limit N Count: 1 Time=1.90s (1s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost select k1,sum(id) from t100w group by k2,k1 limit N
4.5 课后作业
自己扩展：可视化展示 slow-log
pt-query-digest + Amemometer
浙公网安备 33010602011771号