深入MySQL日志体系：从二进制日志到性能调优的实战指南
于MySQL数据库平常的运维以及性能优化里头，日志系统起着极其关键的作用。
它不仅是数据恢复的“后悔药”，更是主从复制的“传输带”。
此文将要深度剖析MySQL里头两大核心日志，也就是二进制日志以及慢查询日志，从底层的原理开始，一直到实战的配置，助力你全面地掌握数据库的“黑匣子”。
一、二进制日志：数据安全的基石
由二进制日志构成的Binary Log，它所记录的是所有针对数据库执行更改的操作过程，其中包含了DDL和DML语句，不过SELECT、SHOW等查询操作是被排除在外的。
它是以事件，也就是Event这种形式予以存储的，并且还附带了语句执行时那种时间戳，它可是数据恢复以及主从同步的核心所依赖的东西。
1. 核心参数配置与优化
处于实际的生产环境里，以二进制形式存在的日志，它的增长速率极其迅速，所以对于它的参数进行合理配置这件事是非常关键重要的：
设置此参数表示启用binlog功能，并制定二进制日志的存储目录
log-bin=/home/mysql/binlog/
#mysql-bin.*日志文件最大字节（单位：字节）
#设置最大100MB
max_binlog_size=104857600
#设置了只保留7天BINLOG（单位：天）
expire_logs_days = 7
#binlog日志只记录指定库的更新
#binlog-do-db=db_name
#binlog日志不记录指定库的更新
#binlog-ignore-db=db_name
#写缓冲多少次，刷一次磁盘，默认0
sync_binlog=0max_binlog_size：默认值为1GB。
在当日之时，若日志文件已然达到了这般上限的情况，那么MySQL便会自行去创建全新的二进制日志文件，举例来说，像是mysql-bin.000001、mysql-bin.000002这样的文件。
得留意，要是去执行大型事务，那其中单个事务有可能会跨越好几个文件，进而使得单个文件超出所设定的数值。
同步二进制日志，它用于对二进制日志写入频率予以控制，而这直接对数据安全性起着决定性作用。
对于sync_binlog=0这种情况，是由操作系统来决定在什么时候把日志缓存刷入到磁盘里，其具备最佳的性能，然而在出现宕机状况时，有可能会丢失最后几秒内的事务。
关于sync_binlog=1，这是一种最为安全的设置方式，在每次事务进行提交之前，都会强制性地进行刷盘操作，进而确保事务不会出现丢失的情况，然而这种设置会在一定程度上略微增加I/O的开销。
sync_binlog=N：每N个事务提交之后进行刷盘一次操作，在此刷盘操作中于性能以及安全之间获取平衡状态。
2. 二进制日志的查看与管理
要对二进制日志的详细内容展开查看，必须运用官方已然提供的那个叫做mysqlbinlog的工具。
show binary logs; #查看binlog列表
show master status; #查看最新的binlog
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 179 | No |
| mysql-bin.000002 | 156 | No |
+------------------+-----------+-----------+
2 rows in set (0.00 sec)这个工具 ，并非只是单纯用于本地日志分析解读 ，它还能够对副本之上的中继日志予以分析解读 ，而这种中继日志的格式 ，和二进制日志是趋同一致的。
mysqlbinlog mysql-bin.000001
经由解析得出之结果，数据库管理员能够明晰确切地瞧见每一条更改语句的精准执行时间以及SQL内容，以此为数据回放以及误操作恢复供给依据。
3. 二进制日志格式与压缩
shell> mysqlbinlog log_file | mysql -h server_nameMySQL支持三种日志格式，以适应不同场景：
记录原始的SQL语句，日志量是比较小的，然而某些并非确定性的函数比如NOW()可能致使主从数据出现不一致的情况。
ROW：记录每一行数据的实际变更，安全性最高但日志量大。
MIXED：也就是那种多种不同态势相互掺合交融的模式，在常规状况下会按照STATEMENT的方式来进行运用，而一旦碰到并非具有确定性的语句之时，便会自然而然地转变切换成为ROW这种模式。
从MySQL 8.0.20开始，有一个功能被引入且它是二进制日志事务压缩。
启动之后（凭借binlog_transaction_compression=ON），MySQL运用zstd算法去收缩事务负担，并且朝着日志里写入单独一个事件。
可设置为1（轻量级）到22（最大压缩）的是压缩级别（binlog_transaction_compression_level），没错吧。
mysql> show global variables like '%binlog_format%';
+---------------------------------+---------+
| Variable_name | Value |
+---------------------------------+---------+
| binlog_format | MIXED |
| default_week_format | 0 |
| information_schema_stats_expiry | 86400 |
| innodb_default_row_format | dynamic |
| require_row_format | OFF |
+---------------------------------+---------+
5 rows in set (0.01 sec)
需要对压缩比以及CPU消耗进行权衡，级别越高，所节省的存储以及网络带宽就越多，然而主库的CPU开销同样也越大。
二、慢查询日志：SQL性能优化的利器
执行时间超出预设阈值的SQL语句，会被慢查询日志记载下来，它是用于定位数据库性能瓶颈的首选工具呢。
1. 配置与启用
默认情况下，慢查询日志处于关闭状态。
开启方式有两种：
配置文件（my.cnf）：
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = ON
long_query_time这个，是阈值，其单位是以妙来进行计算的，而且这个单位中是可以设置成为小数的，就像0.5这样的情况。
设置为0可记录所有SQL，但需谨慎。
这个名为log_queries_not_using_indexes的东西，它的作用是记录那些没有使用索引的查询，而这些记录对于索引的优化而言，有着极其重要的价值。
动态命令（无需重启）：
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
2. 日志分析与工具
位于慢查询日志里面，每一条记录都是以“#”作为开头的，其中涵盖着执行时间，还有锁时间，另外还包含返回行数等相关信息。
手工分析效率低下，推荐使用mysqldumpslow工具：
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
mysql> show variables like '%query%';
+------------------------------+--------------------------------+
| Variable_name | Value |
+------------------------------+--------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | NO |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_prealloc_size | 8192 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/seandeMacBook-Pro-slow.log |
+------------------------------+--------------------------------+
8 rows in set (0.00 sec)此命令依据查询时间进行排序设置，将其展现为最慢的10条SQL之呈现状态，以此助力DBA能够快速地对问题予以定位。
三、对比辨析：Redo Log与Binary Log的异同
好些开发者常常把Redo Log跟Binary Log弄混，这二者诚然都对变更进行记录，然而它们的职责却绝对不一样：
归属于InnoDB存储引擎层面的Redo Log（重做日志），记载的是物理层面的修改情况，即“在某个数据页的某偏移量处做了何种更改”，其主要作用在于崩溃恢复（Crash Recovery），以此确保事务的持久性。
强大的二进制日志，它归属于MySQL Server层，所记录的是那种经过严谨逻辑梳理的SQL，又或者是行的变更情况，旨在达成时间被精准定位后的数据恢复，以及主从之间精确无误的复制。
简要来讲，Redo Log属于引擎内部的那个“急救箱”，然而Binary Log却是跨实例的那个“同步工具”。
四、实战建议：日志策略的最佳实践
场景为数据恢复时，Binary Log必须得开启，而后要设置sync_binlog等于1，与此同时，日志得定期作出备份，备份的地方是异地存储。
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/var/localhost-slow.log
long_query_time=0
log-queries-not-using-indexes = 1
// 配置好后，重启mysql服务2. 一种场景叫做主从复制，在此场景下，建议选用ROW格式，目的是保证数据的一致性，并且将日志压缩功能开启，以此来节省带宽。
3. 性能调优所涉及的场景为，长时间持续开启慢查询日志，将其阈值设定为一秒，再借助诸如pt-query-digest等第三方工具展开周期性的分析。
4. 针对磁盘空间管理，借助expire_logs_days或者binlog_expire_logs_seconds来设定日志自动清理周期，如此一来可防止磁盘遭遇爆满情况。
// 这里就简单些一个配置项就行了，其他的配置项均按照此方法配置
mysql> set slow_query_log=ON;
// 如果执行失败，可能是由于没有添加global
mysql> set global slow_query_log=ON;熟稔MySQL日志体系，不只是DBA所需具备的技能，更是开发者于构架高并发且高可用之情境时的关键考量因素，此为重要之点。
只有对其工作机制进行深入的理解，才可以在数据安全以及性能之间寻觅到最佳的平衡点。
Comments NOTHING