版权声明：署名，允许他人基于本文举行创作，且必须基于与原先允许协议类似的允许协议分发本文 （Creative Commons）

前言

  一、错误日记

  二、慢查询日记

  三、查询日记

  四、二进制binlog日记

  五、redo log(InnoDB 引擎日记文件)

binlog 与 redolog的区别

  六、Mysql 实行一条更新操纵的团体流程

前言

  对于Myql来说，其所包罗的文件有多种，紧张有如下几种：

  参数文件：存储了Mysql启动与运行时设置信息相干的属性；如 my.cnf设置文件存储了Mysql 相应的属性。

  日记文件：记载了Mysql在运行时产生的日记信息，可用于对操纵日记审计或规复数据等。通常所说的日记文件有：错误日记、二进制日记文件、慢查询日记文件、查询日记文件等。

  pid 文件：Mysql 实例历程的 ID 文件。

  Mysql 表布局文件：用于存放表布局的界说文件。

  存储引擎文件：Mysql的引擎对应的是表，每个引擎都有本身存储数据对应的文件格式。这些引擎文件存储了真正的数据与索引信息。

  本文重点先容Mysql的日记架构，因此下文偏重先容Mysql的日记文件。

  笔者整理后，紧张有以下几种日记文件：binlog 、redolog、error log、show query log、log。如下图所示：

一、错误日记

 存储内容：错误日记文件对MySQL的启动、运行、关闭过程举行了记载。默以为开启。

•  1. mysql 错误日记存储路径（大概直接检察设置文件my.cnf）：

mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| log_error     | ./dn02.err |
+---------------+------------+
1 row in set (0.00 sec)

 如上可知：错误日记存储路径为 ./dn02.err，此中dn02为笔者主机映射域名，/usr/local/mysql/data为笔者mysql安装路径，故绝对路径为：/usr/local/mysql/data/dn02.err

• 2. 检察错误日记内容

  重启Mysql服务器，检察错误日记如下

2019-06-30T06:16:34.170958Z 0 [Warning] [MY-010909] [Server] /usr/local/mysql/bin/mysqld: Forcing close of thread 1715  user: 'root'.
2019-06-30T06:16:35.756124Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.11)  MySQL Community Server - GPL.
2019-06-30T06:16:36.363776Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2019-06-30T06:16:36.363923Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.11) starting as process 117663
2019-06-30T06:16:36.382442Z 1 [Warning] [MY-012197] [InnoDB] InnoDB: Unable to open './gzdc/TBL_PERSON.ibd'
2019-06-30T06:16:36.382542Z 1 [Warning] [MY-012197] [InnoDB] InnoDB: Unable to open './gzdc/TBL_ORG.ibd'
2019-06-30T06:16:36.382566Z 1 [Warning] [MY-012197] [InnoDB] InnoDB: Unable to open './gzdc/EX_GONGSHANG_01V3_KYDJ.ibd'
2019-06-30T06:16:43.610735Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-06-30T06:16:43.636304Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.11'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server - GPL.

  由于错误日记记载了Mysql启动相干的信息，因此当mysql出现与启动或设置属性等服务相干的错误时，第一个应该检察的就是这个错误日记。

二、慢查询日记

 存储内容：记载了查询过程中实行比力慢的SQL语句。默以为关闭。

 前面提到可以通不对误日记得到一些关于数据库优化的信息，而慢查询日记 (slow_log)可资助DBA定位大概存在标题的SQL语句，从而举行SQL语句层面的优化。

  比方，可以在MySQL启动时设一个阈值，将运行时间凌驾该值的全部SQL语句都记载到慢查询日记文件中。DBA每天或每过一段时间对其举行查抄，确认是否有SQL语句须要举行优化。

   该阈值可以通过参数long-query_time来设置，默认值为10，代表 10秒。在默认环境下，MySQL数据库并不启动慢查询日记，用户须要手工将这个参数设为ON。因此该功能紧张是在对SQL语句举行调优时再打开。

检察慢查询日记默认阈值如下：

mysql> SHOW VARIABLES LIKE 'long_query_time'\G;  
*************************** 1. row ***************************
Variable_name: long_query_time
        Value: 10.000000
1 row in set (0.00 sec)

慢查询日记启动：

mysql> SET GLOBAL slow_query_log='on'; 
Query OK, 0 rows affected (0.00 sec)

Mysql 5.1后，如开启了慢查询日记可支持将对应的日记记载于slow_log表中，表布局如下图所示：

mysql> SHOW CREATE TABLE mysql.slow_log\G;
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumblob NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.25 sec)

 但是，慢查询默认的日记照旧记载于文件中，如下所示：

mysql> SHOW VARIABLES LIKE 'log_output'\G;
*************************** 1. row ***************************
Variable_name: log_output
        Value: FILE
1 row in set (0.00 sec)

修改成存储到表：

mysql> SET GLOBAL log_output='TABLE';

测试：

mysql> select sleep (11)\G;
*************************** 1. row ***************************
sleep (11): 0
1 row in set (11.00 sec)

  这条查询语句实行时间凌驾10秒，按理来说会被记载到slow_log 慢查询日记中。

  验证：

mysql> SELECT * FROM mysql.slow_log\G;
*************************** 1. row ***************************
    start_time: 2019-06-30 15:19:50.519724
     user_host: root[root] @ localhost []
    query_time: 00:00:11.000336
     lock_time: 00:00:00.000000
     rows_sent: 1
 rows_examined: 0
            db: 
last_insert_id: 0
     insert_id: 0
     server_id: 1
      sql_text: select sleep (11)
     thread_id: 14
1 row in set (0.00 sec)

三、查询日记

存储内容：记载了全部对数据库的哀求，无论实行哀求是否得到准确效果。默以为关闭。

 也可跟慢查询日记一样将记载信息记载到表中。

• 1. 查询是否开启查询日记功能：

mysql> SHOW VARIABLES LIKE '%general_log%'\G;   
*************************** 1. row ***************************
Variable_name: general_log
        Value: OFF
*************************** 2. row ***************************
Variable_name: general_log_file
        Value: /usr/local/mysql/data/dn02.log
2 rows in set (0.01 sec)

• 2. 开启查询日记

SET GLOBAL general_log='on';

 注：由于在前面设置了log存储到表中，因此全部的日记便直接输出到对应的表中了；

开启后没过几分钟便记载下几百条数据，部门样比方下：

*************************** 231. row ***************************
  event_time: 2019-06-30 15:42:20.242534
   user_host: smartbi[smartbi] @ dn02.bmsoft.com.cn [10.194.186.118]
   thread_id: 8
   server_id: 1
command_type: Query
    argument: SET autocommit=1
*************************** 232. row ***************************
  event_time: 2019-06-30 15:42:20.945496
   user_host: root[root] @ localhost []
   thread_id: 14
   server_id: 1
command_type: Query
    argument: select * from general_log
232 rows in set (0.01 sec)

测试完，为了减小数据频仍操纵，照旧乖乖把它关了为好。

mysql> SET GLOBAL general_log='off';
Query OK, 0 rows affected (0.00 sec)

四、二进制binlog日记

  存储内容：该日记记载了对Mysql实行更新的全部操纵，比方 update/delete等，有一点须要留意的是，该记载原则是实行了更新语句便会进入记载，如里实行update操纵，但实际没有发生数据的更新也一样会被记载进来。

别的，二进制日记还包罗了实行数据库更改操纵的时间等其他额外信息。总的来说，二进制日记紧张有以下几种作用。

1. 规复(recovery)：某些数据的规复须要二进制日记，比方，在一个数据库全备文件规复后，用户可以通过二进制日记举行point-in-time的规复。
2. 复制(replication)：其原理与规复类似,通过复制和实行二进制日记使一台长途的MySQL数据库(一样平常称为slave或standby) 与一台MySQL数据库 (一样平常称为master或primary) 举行及时同步。
3. 审计 (audit)：用户可以通过二进制日记中的信息来举行审计，判断是否有对数据库举行注入的攻击。

   在默认环境下，二进制日记并不是在每次写的时间同步到磁盘(用户可以明白成缓冲写)。

   因此，当数据库地点操纵体系发生宕机时，大概会有末了一部门数据没有写入二进制日记文件中，这会给规复和复制带来标题。管理这一点可以引入redolog日记。

• 1. 可以通过例子感性地认识该日记

mysql> update test.user_t set name='Hadoop' where id=1;    
Query OK, 1 row affected (0.08 sec)

  由上述运行效果可知，实在并没有数据发生更新；检察现在该更新操纵记载的binlog文件名

mysql> show master status\G;
*************************** 1. row ***************************
             File: binlog.000017
         Position: 485
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

 binlog日记记载于 binlog.000017；此中485为文件巨细为

-rw-r----- 1 mysql mysql      485 Jun 30 16:51 binlog.000016

 注：binlog.index 中记载了这些归档日记对应的索引名（简单明白为文件名）；

• 2. 检察具体日记：

 发生的修改后的日记文件如下：

mysql> SHOW BINLOG EVENTS IN 'binlog.000017' \G ;
*************************** 1. row ***************************
   Log_name: binlog.000017
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 124
       Info: Server ver: 8.0.11, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: binlog.000017
        Pos: 124
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 155
       Info: 
*************************** 3. row ***************************
   Log_name: binlog.000017
        Pos: 155
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 230
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: binlog.000017
        Pos: 230
 Event_type: Query
  Server_id: 1
End_log_pos: 310
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: binlog.000017
        Pos: 310
 Event_type: Table_map
  Server_id: 1
End_log_pos: 375
       Info: table_id: 62 (test.user_t)
*************************** 6. row ***************************
   Log_name: binlog.000017
        Pos: 375
 Event_type: Update_rows
  Server_id: 1
End_log_pos: 456
       Info: table_id: 62 flags: STMT_END_F
*************************** 7. row ***************************
   Log_name: binlog.000017
        Pos: 456
 Event_type: Xid
  Server_id: 1
End_log_pos: 487
       Info: COMMIT /* xid=8 */
7 rows in set (0.00 sec)

• 3. 由于是二进制文件，没方法通过cat、more/less 等检察文本文件的方式举行检察，因此用如下方式：

[root@dn02 data]# mysqlbinlog --start-position=1 binlog.000017
...
#190630 17:02:44 server id 1  end_log_pos 375 CRC32 0xf88cd5eb  Table_map: `test`.`user_t` mapped to number 62
# at 375
#190630 17:02:44 server id 1  end_log_pos 456 CRC32 0x13626f55  Update_rows: table id 62 flags: STMT_END_F
....
[root@dn02 data]# 

• 4. 在Mysql 中检察有关binlog相干的设置属性，会发现特殊多，如下所示：

mysql> SHOW VARIABLES LIKE '%binlog%';
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| binlog_cache_size                          | 32768                |
| binlog_checksum                            | CRC32                |
| binlog_direct_non_transactional_updates    | OFF                  |
| binlog_error_action                        | ABORT_SERVER         |
| binlog_expire_logs_seconds                 | 2592000              |
| binlog_format                              | ROW                  |
| binlog_group_commit_sync_delay             | 0                    |
| binlog_group_commit_sync_no_delay_count    | 0                    |
| binlog_gtid_simple_recovery                | ON                   |
| binlog_max_flush_queue_time                | 0                    |
| binlog_order_commits                       | ON                   |
| binlog_row_image                           | FULL                 |
| binlog_row_metadata                        | MINIMAL              |
| binlog_row_value_options                   |                      |
| binlog_rows_query_log_events               | OFF                  |
| binlog_stmt_cache_size                     | 32768                |
| binlog_transaction_dependency_history_size | 25000                |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER         |
| innodb_api_enable_binlog                   | OFF                  |
| log_statements_unsafe_for_binlog           | ON                   |
| max_binlog_cache_size                      | 18446744073709547520 |
| max_binlog_size                            | 1073741824           |
| max_binlog_stmt_cache_size                 | 18446744073709547520 |
| sync_binlog                                | 1                    |
+--------------------------------------------+----------------------+
24 rows in set (0.01 sec)

 如：binlog_expire_logs_seconds                 | 2592000  这个设置意思是binlog.xxx日记生存一个月的记载，也就是说可以通过其规复一个月内任何时间内对数据所做的处理处罚，包罗删除修改等更新操纵。

五、redo log(InnoDB 引擎日记文件)

存储内容：记载了 Mysql 中实际被修改的数据。其紧张有以下特点：

• 1. InnoDB特有的，可用于管理Mysql 的 crash-safe 标题；注：数据库发生非常重启，之条件交的记载都不会丢失，这个本领称为crash-safe。
• 2. 在对数据举行操纵时，为了淘汰磁盘IO开销，数据不会立马写入数据存储对应的磁盘中，而是先写入日记中。MySQL内里有一种叫做WAL（Write-Ahead Logging），就是先写日记在写磁盘。就是当有一条记载须要更新的时间，InnoDB 会先写redo log 内里，并更新内存，这个时间更新的操纵就算完成了。之后，MySQL会在符合的时间将操纵记载 flush 到磁盘上面。固然 flush 的条件大概是体系比力空闲，大概是 redo log 空间不敷时。
• 3. redo log 文件的巨细是固定的，其通过循环写的方式往redolog中写入数据，当空间不敷时则flush。如下图所示：

binlog 与 redolog的区别

1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，全部引擎都可以使用。
2. redo log是物理日记，记载的是"在某个数据页上做了什么修改" ; binlog是逻辑日记,记载的是这个语句的原始逻辑,好比"给ID-2这一行的c字段加1"。
3. redo log是循环写的,空间固定会用完；binlog是可以追加写入的。"追加写"是指binlog写到肯定巨细后会切换到下一个，并不会覆盖从前的日记。

六、Mysql 实行一条更新操纵的团体流程

• 实行的语句如下：

mysql> update T set c=c+1 where ID=2;

在上一篇文章中，我们知道Mysql中实行一条查询语句，实在行流程图如下图：

• 同理，在实行更新操纵也根本与查询一样，紧张的差别之处是更新的操纵会涉及对binlog、redolog日记举行更新。
• 其更新过程如下图所示：图中淡色框表现是在InnoDB内部实行的，深色框表现是在实行器中实行的。

  通过上图可看出，对于redolog日记是先写入然后进入prepare阶段，然后再写入binlog，两个都写乐成后再举行一起提交，对于redolog从专业术语上来说，这就是redolog的两阶段提交。 

  两阶段提交紧张是为了后期当通过binlog对数据举行规复时，能确保binlog与redolog对应的数据是同等的。

  具体学习redolog与binlog 保举阅读极客时间《Mysql常见的6个日记标题》一文

注：参考极客时间《日记体系：一条SQL更新语句是怎样实行的？》

Mysql技能黑幕InnoDB存储引擎