MySQL数据库-日志管理一看就懂

今天给伙伴们分享一下MySQL数据库-日志管理，希望看了有所收获。

我是公众号「想吃西红柿」「云原生运维实战派」的作者，对云原生运维感兴趣，也保持时刻学习，后续会分享工作中用到的运维技术，在运维的路上得到支持和共同进步！

如果伙伴们看了文档觉得有用，欢迎大家关注我的公众号，获取相关文档。爱运维，爱生活。

MariaDB/MySQL中，主要有5种日志文件：

• 错误日志(error log)：记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。
• 查询日志(general log)：记录建立的客户端连接和执行的语句。
• 二进制日志(bin log)：记录所有更改数据的语句，可用于数据复制。
• 慢查询日志(slow log)：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。select name
• 中继日志(relay log)：主从复制时使用的日志。

1、MySQL 错误日志

1、错误日志作用

• 记录MySQL启动及工作过程中，状态、报错、警告。

2、设置错误日志

1、修改配置文件，并重启MySQL

1、配置错误日志

[root@edenluo.com ~]# vim /etc/my.cnf
log_error=/data/3306/data/mysql.log   #这里的路径和文件名称可以随便定义

2、重启MySQL生效

[root@edenluo.com ~]# systemctl restart mysqld 

2、查看错误日志

mysql> select @@log_error;
+---------------------------+
| @@log_error               |
+---------------------------+
| /data/3306/data/mysql.log |
+---------------------------+
1 row in set (0.00 sec)

注意： 查看错误日志关注[ERROR]的上下文.

2、MySQL 二进制日志

1、二进制日志作用

• 数据恢复必备的日志。

• 主从复制依赖的日志。

2、MySQL 二进制日志设置

1、修改配置文件

[root@edenluo.com ~]# vim /etc/my.cnf
server_id=6
log_bin=/data/3306/binlog/mysql-bin  

• 配置说明
  • server_id 是5.7之后开二进制日志必加的参数
  • log_bin= 打开二进制功能
  • /data/3306/binlog/ 指定存放路径
  • mysql-bin 文件名前缀

2、创建目录并授权

[root@edenluo.com 3306]# mkdir -p /data/3306/binlog/
[root@edenluo.com 3306]# chown -R mysql.mysql /data/3306/*

3、重启数据库

[root@edenluo.com 3306]# systemctl restart mysqld  

[root@edenluo.com binlog]# ll binlog/
total 8
-rw-r----- 1 mysql mysql 768 Aug 14 20:02 mysql-bin.000001
-rw-r----- 1 mysql mysql  35 Aug 14 18:18 mysql-bin.index

• 配置说明
  • mysql-bin 是在配置文件配置的前缀
  • 000001 MySQL每次重启，重新生成新的 或者 flush logs 命令后重新生成日志。

3、二进制日志内容

• 除了查询类的语句，都会记录，即所有数据库变更类的语句。

1、记录语句的种类

• DDL（数据定义语言）：create、drop
• DCL（数据控制语言）：grant 、revoke
• DML（数据操作语言）：insert、update、delete
• 插入了10条数据 就有10个变化， 删除两条 优化成插入 8条 恢复10条 数据变化过程

2、不同语句的记录格式说明

• DDL、DCL直接以sql语句（statement）方式记录 .

• DML 语句有三种模式：SBR、RBR、MBR

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)

• 配置说明

  • statement---->SBR：做什么记录什么，即SQL语句
  • row---------->RBR：记录数据行的变化（默认模式，记录每行数据的变化，行级别的。推荐）
  • mixed-------->MBR：自动判断记录模式，混合模式，自动选择STATEMENT和ROW两种格式

• SBR 和 RBR 的区别

区别项	SBR	RBR（默认、推荐）
记录内容	SQL语句	记录数据行的变化
可读性	较强	差
日志量	小	大
日志记录准确性	数据误差	没有误差

3、二进制日志三种模式的区别

1、ROW: 基于行的复制

优点：所有的语句都可以复制，不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了

缺点：binlog 大了很多，复杂的回滚时 binlog 中会包含大量的数据；主服务器上执行update语句时，所有发生变化的记录都会写到 binlog 中；比如有这样一条update语句：update product set owner_member_id=‘d’ where owner_member_id=‘a’,执行之后，日志中记录的不是这条update语句所对应的事件(mysql是以事件的形式来记录bin-log日志)，而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的很多事件。自然，bin-log日志的量会很大。

2、Statement: 基于sql语句的复制

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能

缺点：由于它是记录的执行语句，所以为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是,由于mysql现在发展比较快，很多的新功能加入，使mysql的复制遇到了不小的挑战,自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement level下，目前已经发现的就有不少情况会造成mysql的复制问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如sleep()在有些版本就不能正确复制。

3、mixed模式 ：row 与 statement 结合

实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选一种。新版本中的statement level还是和以前一样，仅仅记录执行的语句。而新版本的mysql中对row level模式被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete 等修改数据的语句，那么还是会记录所有行的变更。

4、二进制日志事件

4、二进制日志工作模式

1、配置二进制日志工作模式

1、修改配置文件

[root@edenluo.com ~]# vim /etc/my.cnf
[mysqld]
binlog_format='ROW' 

2、重启数据库

[root@edenluo.com ~]# systemctl restart mysqld 　

2、查看二进制日志工作模式

mysql> show  variables like "binlog%";
+--------------------------------------------+--------------+
| Variable_name                              |