MySQL日志体系介绍

以你之姓@ 2022-04-15 04:49 279阅读 0赞

MySQL提供了几种日志功能，如下：

<table> 
 <thead> 
  <tr> 
   <th>日志类型</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Error log</td> 
   <td>记录mysqld在启动、关闭、运行时出现的错误、告警等</td> 
  </tr> 
  <tr> 
   <td>General query log</td> 
   <td>记录MySQL执行的所有SQL</td> 
  </tr> 
  <tr> 
   <td>Binary log</td> 
   <td>数据库变更记录，类似一个账本，记着流水账</td> 
  </tr> 
  <tr> 
   <td>Relay log</td> 
   <td>只在从库存在，内容就是主库的binlog</td> 
  </tr> 
  <tr> 
   <td>Slow query log</td> 
   <td>慢查询日志</td> 
  </tr> 
  <tr> 
   <td>DDL log</td> 
   <td>DDL操作日志</td> 
  </tr> 
 </tbody> 
</table>

默认情况下，  
1.以上的日志功能都是关闭的（DDL log除外，这种日志是自动生成的，不能干预）；  
2.如果没有特别指定，所有开启的日志功能，输出信息都写在数据库数据目录下。

**一、Error log**  
错误日志会在MySQL启动和关闭的时候记录相关信息。它包含如error，warnings，note等诊断信息。例如当mysqld进程异常终止时，mysqld\_safe会自动将其拉起，并在错误日志里记录相关信息。  
linux下是用–log-error指定将信息定向到标准输出还是文件中。  
如果–log-error没指定，mysqld会将错误信息输出到标准输出；  
除非–log-error指定绝对路径和文件名，否则会将错误信息输出到数据目录下的host\_name.err文件。  
–log-error可以在启动数据库时命令行指定，也可以在my.cnf文件中\[mysqld\]，\[server\]或\[mysqld\_safe\]中指定。  
同时，可以通过sys\_log选项，将错误日志输出到syslog中。

**二、Binary log**  
简称binlog，用事件（event）的方式来记录数据库的变更过程。binlog会记录那些有可能会更改数据的SQL，或者以数据行的方式去记录，例如一条delete语句，虽然可能删除了0行，但binlog中还是会有记录。但select或show等不导致数据变更的操作不会记录。开启binlog会使得数据库性能轻微地下降，但是其所带来的功能，是绝对物超所值的。  
binlog有两个重要的作用：  
1.在主从复制中，从库通过应用主库的binlog来达成同步；  
2.在备份恢复中，通过binlog来完成指定时间点的不完整恢复。  
binlog通过–log-bin参数开启或指定具体路径，默认名称是pid-file选项内容，默认路径是数据目录。–log-bin参数如果指定了.xx之类的后缀，会被忽略，而在实际生成时，MySQL会自动在–log-bin之后加上递增的数字后缀。  
在MySQL重启或执行flush logs时，会自动生产新的binlog，后缀加一。一般情况下，binlog文件大小不会超过max\_binlog\_size，但如果有一个大事务，在文件达到max\_binlog\_size时还没写完，则文件会扩展至能写完这个事务为止。  
跟binlog成双成对的还有一个文件，名称是binlog+.index后缀，里面记录这当前的所有binlog。  
不要手动rm binlog，MySQL有专门的语法去清理。如果想一次性清理全部binlog，可以使用reset master，但要注意检查当前从库是否已经抓取了所有binlog。也可以利用purge binary log to ''的方式，将binlog清理至指定的文件或日志。  
binlog是二进制格式的，可以利用mysqlbinlog工具查看其内容。常用命令为  
mysqlbinlog --no-defaults -v mysql-bin.00001  
详细用法可–help查看帮助。  
前面说到binlog可以用来进行时间点的不完整恢复，大致用法如下  
mysqlbinlog mysql-bin.00001|mysql -u -p  
binlog与事务：  
在事务执行完成，锁释放或事务提交前，要先完成binlog的写入。对于非事务性表的操作，执行完成后立即写入binlog。所以在一个事务中，如果混合了事务表和非事务表，在事务异常需要回滚时，由于非事务表不能回滚，会导致数据的不一致。  
在一个事务提交前，事务的binlog会先缓存在binlog cache buffer里，在完成提交前再将整个事务的binlog刷入文件。  
binlog不得不说的一个关键点是binlog\_format：  
1.statement，语句格式，即binlog中直接记录执行的SQL语句；  
2.row，行格式，binlog中记录的是数据行所发生的变化，例如一个update语句更新一百行数据，那么binlog中将记录那一百行数据的变化，而不是那条update语句；  
3.mixed，混合格式，即以上两种格式的混合，一般情况下是statement格式，在一定的条件下会自动转变为row格式。  
statement格式的优点是节省空间，缺点是存在不确定因素；row格式的优点是精准的记录数据变化，缺点是耗费空间。例如一条update语句更新了一百万行数据，那么statement格式只需记录一条SQL，而row格式就要记录一百万行数据的变化。但考虑一种情况，SQL是update tb set time=now() where id=10，那么以statement格式记录的binlog在一段时间之后用来搭建从库或者做恢复，那么now()就是当前的时间了，也就造成了数据的不可还原；而如果是row格式的binlog，由于记录的是数据行的变化，所以不受时间函数的影响。  
一般情况下都推荐使用row格式，极少数情况根据实际而定。  
设定binlog格式语句如下：  
set global binlog\_format=‘row|statement|mixed’;

**三、General query log**  
全局查询日志记录所有MySQL执行的命令，包括用户连接/断开在内的所有客户端发送过来的命令。  
mysqld会按照接收命令的顺序原样记录在全局查询日志中，这个顺序可能跟执行的顺序不太一样，所以跟binlog里的SQL顺序也是有区别的。  
需要注意的是，当binlog\_format是row时，由于更新语句的记录都是数据行的格式，所以这些更新操作是不会记录到查询日志里的。当binlog\_format是mixed时同理。  
开启general log：  
set global general\_log=1;  
set global general\_log\_file=file\_path;  
关闭general log:  
set global general\_log=0;

**四、Slow query log**  
慢查询日志，顾名思义就是记录慢查询的SQL了。参数long\_query\_time设置慢查询阈值，min\_examined\_row\_limit设置检索数据的条目，一个SQL两个指标都超过了就会记录在慢查询日志里。  
开启/关闭慢查询的方式：  
–slow\_query\_log=0|1  
–slow\_query\_log\_file=file-name  
general query log和slow query log都可以通过–log-output=table|file来指定日志存放在库表还是文件中，可以用逗号隔开同时采用两种方式。  
慢查询还可以监控没使用到索引的SQL，通过log\_queries\_not\_using\_indexes开启。但可能这样的SQL会非常多，很容易就导致磁盘空间爆满，那么可以通过log\_throttle\_queries\_not\_using\_indexes来设定一分钟内记录多少个没使用索引的SQL。

**五、DDL log**  
DDL日志，记录由DDL操作产生的元数据操作，例如drop table和alter table，如果这些操作发生异常终端，MySQL利用DDL 日志来进行恢复。  
DDL日志记录在数据目录的ddl\_log.log，内容是二进制格式的，不要对其进行人工编辑。  
DDL日志在大部分正常运行的情况下是不会出现的，只有在需要用来记录一些元数据操作的时候才会产生，而且在MySQL启动后，会自动清理DDL日志。  
需要注意的是，DDL日志最大只能达到4GB，如果达到上限，需要将其清理后，才能继续进行DDL操作。