《MySQL系列-InnoDB引擎03》MySQL文件相关介绍

第三章 文件

MySQL数据和InnoDB存储引擎表中的各种类型的文件，这些文件如下：

• 参数文件：MySQL启动时的数据库文件，指定初始化参数，介绍各种参数类型，以及定义某种内存结构的大小等
• 日志文件：用来记录MySQL实例对某种条件做出响应时写入的文件，如错误日志、二进制日志、慢查询日志、查询日志等
• socket文件：当用UNIX域套接字方式进行连接时需要的文件
• pid文件：MySQL实例的进程ID文件
• MySQL表结构文件：用来存放MySQL表结构定义文件
• 存储引擎文件：因为MySQL表存储引擎的关系，每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。

1 参数文件

  当MySQL实例启动时，数据库会先去读一个配置参数，用来寻找数据库的各种我恩据所在文职以及指定某些初始化参数，这些参数通常定义了某种内存结构有多大等。在默认情况下，MySQL实例会按照一定的顺序在指定的位置进行读取，用户只需要通过命令mysql --help | grep my.cnf来寻找即可。

  MySQL数据库参数的作用和Oracle数据库的参数极其类似，不同的是，Oracle实例在启动时若找不到参数文件，是不能进行状态(mount)操作的。MySQL稍微有所不同，MySQL实例可以不需要参数文件，这时所有的参数取决于编译MySQL时指定的默认值和源代码的指定参数的默认值。但是，如果MySQL实例在默认的数据库目录下找不到MySQL架构，则启动同样会失败，可通过错误日志查看。

  MySQL的参数文件是以文本方式进行存储的，用户可以直接通过一些常用的文本编辑器来进行参数的修改。

1.1 什么是参数？

简单的说，可以把数据库参数看成一个键值对。

比如参数datadir是键，那么他的值就是/var/lib/mysql/

• 可以通过SHOW VARIABLES查看数据库中所有参数，也可以通过LIKE来过滤参数名。

  mysql> show variables like 'datadir%';
  +---------------+-----------------+
  | Variable_name | Value           |
  +---------------+-----------------+
  | datadir       | /var/lib/mysql/ |
  +---------------+-----------------+
  1 row in set (0.00 sec)
  

• 从MySQL 5.1版本开始，可以通过information_schema架构下的global_variables视图进行查找：

  select * 
  from global_variables 
  where variable_name like 'datadir%'
  

• 从MySQL 5.7.6开始information_shcema.global_variables已经开始被舍弃，为了兼容性，此时需要打开show_compatibility_56

  # 1.查询global_variables异常
  mysql> select * from global_variables where variable_name like 'innodb_buffer%'                                                                          \G;
  ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabl                                                                          ed; see the documentation for 'show_compatibility_56'
  ERROR:
  No query specified
  
  # 2.查看show_compatibility_56状态
  mysql> show variables like '%show_compatibility_56%';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | show_compatibility_56 | OFF   |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  
  # 3.临时开启show_compatibility_56，重启后失效
  # 需要修改my.cnf，添加"show_compatibility_56=1"后永久生效
  mysql> set global show_compatibility_56=on;
  Query OK, 0 rows affected (0.00 sec)
  
  # 4.再次查询
  mysql> select * from global_variables where variable_name like 'datadir%';
  +---------------+-----------------+
  | VARIABLE_NAME | VARIABLE_VALUE  |
  +---------------+-----------------+
  | DATADIR       | /var/lib/mysql/ |
  +---------------+-----------------+
  1 row in set, 1 warning (0.00 sec)
  
  

1.2 参数类型

MySQL数据库参数可以分为两类：

• 动态参数
• 静态参数

  动态参数意味着可以在MySQL实例运行中进行更改，静态参数说明在整个实例生命周期内都不得进行更改，就好像是只读的。可以通过SET命令对动态参数值进行修改，SET的语法如下：

SET
| [global | session] system_var_name=expr
| [@@global. | @@session. | @@]system_var_name=expr

  可以看到这里有global和session关键字，它们表明该参数的修改是基于当前会话还是整个实例的生命周期。有些动态参数只能在会话中进行修改，如autocommit;而有些参数修改完后，在整个实例生命周期中都会生效，如binlog_cache_size; 而有些参数既可以在会话中又可以在整个生命周期内生效，如read_buffer_size。

  但是需要注意的是，通过set @@global.system_var_name=expr;方法对变量的全局值进行修改，在这次的实例生命周期内都有效。但MySQL实例本身并不会对参数文件中的该值进行修改。也就是说，在下次启动时MySQL实例还是会读取参数文件。若想在数据库实例下一次启动时参数保留当前修改的值，那么必须通过修改参数文件实现。

2 日志文件

日志文件记录了影响MySQL数据库的各种类型活动。MySQL数据库中常见的日志文件有：

• 错误日志(error log)
• 二进制日志(bilog)
• 慢查询日志(slow query log)
• 查询日志(log)

这些日志文件可以帮助DBA对MySQL数据库的运行状态进行诊断，从而更好的进行数据库层面的优化。

2.1 错误日志

  错误日志文件对MySQL的启动、运行、关闭过程进行记录。MySQL DBA在遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录一些警告信息或正确信息。用户可以通过命令show variables like 'log_error'来定位该文件。

mysql> show variables like 'log_error';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.00 sec)

  当数据库不能正常启动时，首先就是需要查看错误日志文件，该文件记录了错误信息，能够很好的指导用户发现问题。比如查找登录失败的记录：

[root@zxy_master log]# cat mysqld.log | grep 'Access denied'
2022-06-11T14:56:27.838371Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2022-06-11T14:56:55.068648Z 3 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2022-06-11T14:57:03.115152Z 4 [Note] Access denied for user 'root'@'localhost' (using password: YES)

2.2 慢查询日志

  通过错误日志(error log)可以获得关于数据库优化的信息，而慢查询日志(slow log)可以帮助DBA定位可能存在问题的SQL，从而进行SQL语句层面的优化。例如，可以在MySQL启动时设置一个阈值，将运行时间超过该值的所有SQL都记录到慢查询日志文件中。DBA每天或每过一段时间对其进行检查，确认是否有SQL语句需要进行优化。该阈值可以通过参数long_query_time来设置，默认值为10，代表10秒。

  在默认情况下，MySQL数据库并不启动慢查询日志，用户需要手动将这个参数设置为ON：

show variables like 'long_query_time'\G;

show variables like 'long_slow_queries'\G;

  还有两点需要注意的是。1：设置long_query_time这个阈值后，MySQL数据库会记录运行时间超过该值的所有SQL语句，但是运行时间刚好等于long_query_time的情况并不会被记录。2：从MySQL 5.1开始，long_query_time开始以微秒记录SQL语句运行的时间，之前仅用秒为单位记录。使用微妙记录SQL运行时间，可以供DBA更好的分析，对DBA来说，一条SQL语句运行0.5秒和0.05秒是非常不一样的。前者可能进行了表扫描，后面可能进行了索引。

  另一个跟慢查询日志有关的参数是log_queries_not_using_indexes，如果运行的SQL语句没有使用索引，则MySQL数据库同样会将这条SQL语句记录到慢查询日志文件。

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

  MySQL 5.6.5版本开始新增了一个参数log_throttle_queries_not_using_indexes，用来表示每分钟允许记录到slow log的且未使用索引的SQL语句次数。该值默认为0，表示没有限制。在生产环境下，若没有使用索引，此类SQL语句被频繁记录到slow log，从而导致slow log文件的大小不断增加，故DBA可通过此参数进行配置。

  DBA可以通过慢查询日志来找出有问题的SQL语句，对其进行优化。然后随着MySQL数据库服务器运行时间的增加，可能会越来越多的SQL被记录到慢查询日志中，此时要分析该文件就显得不是那么简单和直观了。这时MySQL数据库提供了mysqldumpslow命令，可以很好的帮助DBA解决该问题。也可以通过查询mysql.slow_log表查看慢查询日志记录。

2.2x 慢查询日志拓展-如何开启MySQL慢查询日志？

1.查看MySQL慢查询日志是否开启？

show variables like '%query%';

查询出的结果中，主要观察如下三条：

• long_query_time

  通过long_query_time设置阈值，设置阈值后，MySQL数据库会记录运行时间超过该值的所有SQL语句，但是运行时间刚刚好等于long_query_time的情况并不会被记录。

• slow_query_log

  ON表示开启慢查询日志，OFF表示关闭慢查询日志

  可以通过set global slow_query_log=ON开启，通过set global slow_query_log=OFF关闭。

• slow_query_log_file

  记录慢查询日志文件的位置

mysql> show variables like '%query%';
+------------------------------+------------------------------------+
| Variable_name                | Value                              |
+------------------------------+------------------------------------+
| long_query_time              | 10.000000                          |
| slow_query_log               | OFF                                |
| slow_query_log_file          | /var/lib/mysql/zxy_master-slow.log |
+------------------------------+------------------------------------+
13 rows in set (0.00 sec)

2.开启慢查询日志

set global slow_query_log=ON;

mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)

3.查看慢查询日志存储类型

show variables like '%log_output%';

• set global log_output = ‘FILE’;

  存储在磁盘目录中，可以通过参数slow_query_log_file查看。

• set global log_output = ‘TABLE’;

  存储在数据库中，可以通过查询mysql.slow_log查看；

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

4.测试慢查询日志

4.1 log_output = ‘FILE’

# 1.设置慢查询格式为FILE
mysql> set global log_output = 'file';
Query OK, 0 rows affected (0.00 sec)

# 2.模拟慢查询11秒
mysql> select sleep(11);
+-----------+
| sleep(11) |
+-----------+
|         0 |
+-----------+
1 row in set (11.00 sec)

查询日志

[root@zxy_master mysql]# cat /var/lib/mysql/zxy_master-slow.log
# Time: 2023-01-09T01:48:59.330285Z
# User@Host: root[root] @ localhost []  Id:  1634
# Query_time: 11.000191  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1673228939;
select sleep(11);

4.2 log_output = ‘TABLE’

# 1.设置慢查询日志为TABLE
mysql> set global log_output = 'TABLE';
Query OK, 0 rows affected (0.00 sec)

# 2.模拟慢查询日志11秒
mysql> select sleep(11);
+-----------+
| sleep(11) |
+-----------+
|         0 |
+-----------+
1 row in set (11.00 sec)

查询日志

# 从mysql.slow_log表中查询日志
mysql> select * from mysql.slow_log\G;
*************************** 1. row ***************************
    start_time: 2023-01-09 09:53:01.251772
     user_host: root[root] @ localhost []
    query_time: 00:00:11.000189
     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: 1634
1 row in set (0.00 sec)

2.2XX 慢查询日志拓展-mysqldumpslow日志分析工具

1.mysqldumpslow

[root@zxy_master mysql]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are
#分析并总结MySQL慢速查询日志。选项包括

  --verbose    verbose #详细
  --debug      debug #调试
  --help       write this text to standard output #帮助将此文本写入标准输出

  -v           verbose #详细
  -d           debug #调试
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
  				# 排序依据 (al, at, ar, c, l, r, t),默认为'at'
                al: average lock time
                # 平均锁定时间
                ar: average rows sent
                # 发送的平均行数
                at: average query time
                # 平均查询时间
                 c: count
                 # 计数
                 l: lock time
                 # 锁定时间
                 r: rows sent
                 # 发送的行数
                 t: query time
                 #查询时间
  -r           reverse the sort order (largest last instead of first)
  				# 反转排序顺序（最大的最后一个，而不是第一个）
  -t NUM       just show the top n queries
  				# NUM仅显示前n个查询
  -a           don't abstract all numbers to N and strings to 'S'
  				# 不要将所有数字抽象为N，将字符串抽象为S
  -n NUM       abstract numbers with at least n digits within names
  				# NUM个抽象数字，名称中至少有n个数字
  -g PATTERN   grep: only consider stmts that include this string
  				# grep:只考虑此字符串的stmts
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
               # hostname数据库服务器的主机名，用*-slow.log filename(可以是通配符)，默认为*，即匹				配所有
  -i NAME      name of server instance (if using mysql.server startup script)
  				# 服务器实例的名称（如果使用MySQL.server启动脚本）
  -l           don't subtract lock time from total time
  				# 不要从总时间中减去锁定时间

2.mysqldumpslow结果介绍

[root@zxy_master mysql]# mysqldumpslow -s r -t 10 /var/lib/mysql/zxy_master-slow.log

Reading mysql slow query log from /var/lib/mysql/zxy_master-slow.log
Count: 2  Time=11.00s (22s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost
  select sleep(N)

Died at /usr/bin/mysqldumpslow line 167, <> chunk 1.

# Count:2 --出现次数
# Time=11.00s (22s) --执行最长时间，累计总耗费时间
# Lock=0.00s (0s) --等待锁的时间
# Rows=1.0 (2) --发送给客户端的行总数，扫描行总数
# root[root]@localhost --执行用户及地址

2.mysqldumpslow使用案例

# 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/zxy_master-slow.log

# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/zxy_master-slow.log

# 得到根据查询时间排序后的前10个SQL
mysqldumpslow -s t -t 10 /var/lib/mysql/zxy_master-slow.log

# 得到根据查询时间排序后,并包含'zxy'的前10个SQL
mysqldumpslow -s t -t 10  -g "zxy" /var/lib/mysql/zxy_master-slow.log

2.3 查询日志

  查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。默认文件名为：hostname.log。

# 1.查询日志未开启
mysql> show variables like 'general_log%';
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| general_log      | OFF                           |
| general_log_file | /var/lib/mysql/zxy_master.log |
+------------------+-------------------------------+
2 rows in set (0.00 sec)

# 2.日志保存类型为table，查询日志存储在mysql.general_log表
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.00 sec)

# 3.开启查询日志
mysql> set global general_log = ON;
Query OK, 0 rows affected (0.00 sec)

# 4.查看查询日志
mysql> select * from mysql.general_log\G;
*************************** 1. row ***************************
  event_time: 2023-01-12 16:56:32.154346
   user_host: root[root] @ localhost []
   thread_id: 1664
   server_id: 1
command_type: Query
    argument: select * from mysql.general_log

  通过上述步骤可以看到，开启查询日志后，当日志存储类型为TABLE的时候，可以直接在mysql.general_log表中查询到。也可以尝试将日志存储类型改为FILE，那么就需要查看/var/lib/mysql/zxy_master.log文件。

2.4 二进制日志

  二进制日志(binary log)记录了对MySQL数据库执行更改的所有操作，但是不包括select和show这类操作，因为该类操作本身对数据没有修改。然而，若操作本身并没有导致数据库发生变化，那么该操作可能也会写入二进制日志，比如update一个数据，即使不影响数据结果，依然会记录到二进制日志中。

  如果用户想记录select和show操作，那么只能使用查询日志，而不是二进制日志。此外，二进制日志还包括了执行数据库更改操作的时间等其他额外信息。总的来说，二进制日志主要有以下几种作用。

• 恢复(recovery): 某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。
• 复制(replication): 其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库(一般称为slave或standby)与一台MySQL数据库(一般称为master或primary)进行实时同步
• 审计(audit): 用户可以进行通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

  通过配置参数log-bin [=name]可以启动二进制日志。如果不指定name，则默认二进制日志文件为主机名，后缀名为二进制日志的序列号，所在路径为数据库所在目录(datadir)，如：

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

# 比如mysql-bin.000001为二进制日志文件
# mysql-bin.index为二进制的索引文件，用来存储过往产生的二进制日志序号，在通过情况下，不建议手工修改这个文件。
mysql> system ls -lh /var/lib/mysql
-rw-r----- 1 mysql mysql  50M Jul  8  2022 mysql-bin.000001
-rw-r----- 1 mysql mysql  209 Nov 16 14:53 mysql-bin.index
-rw-r--r-- 1 root  root  138K Oct  8 16:50 zxy.sql
.......

  二进制日志文件在默认情况下并没有启动，需要手动指定参数来启动。当然，开启二进制日志会影响数据库的性能，但是性能的损失十分有限。根据MySQL官网手册中测试表明，开启二进制日志会使性能下降1%，但考虑到可以使用复制(replaction)和point-in-time的恢复，这些性能的损失是可以接收的。

以下配置文件的参数影响着二进制日志记录的信息和行为：

• max_binlog_size

    参数max_binlog_size指定单个二进制文件的最大值，如果超过该值，则产生新的二进制文件，后缀名+1，并记录到.index文件。从MySQL 5.0开始的默认值为1 073 741 824，代表1G(在之前版本中max_binlog_size默认为1.1G)。

• binlog_cache_size

    当使用事物的表存储引擎(如InnoDB引擎)时，所有未提交(uncommitted)的二进制日志会被记录到一个缓存中去，等该事物提交(committed)时直接将缓冲中的二进制日志写入二进制文件，而该缓冲的大小由binlog_cache_size决定，默认大小为32K。

    此外，binlog_cache_size是基于会话(session)的，也就是说，当一个线程开始一个事务的时候，MySQL会自动分配一个大小为binlog_cache_size的缓存，因此该值的设置需要相当小心，不能设置过大。当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中，因此该值又不能设置太小。

    通过show global status命令查看binlog_cache_use,binlog_cache_disk_use的状态，可以判断当前binlog_cache_size的设置是否合适。

  mysql> show variables like 'binlog_cache_size';
  +-------------------+-------+
  | Variable_name     | Value |
  +-------------------+-------+
  | binlog_cache_size | 32768 |
  +-------------------+-------+
  1 row in set (0.00 sec)
  

    binlog_cache_use记录使用缓冲写二进制日志的次数，binlog_cache_disk_use记录使用临时文件写二进制日志的次数。

  mysql> show global status like 'binlog_cache%';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | Binlog_cache_disk_use | 71    |
  | Binlog_cache_use      | 60773 |
  +-----------------------+-------+
  2 rows in set (0.00 sec)
  

    使用缓冲次数60773次，临时文件使用次数为71次。可以看出binlog_cache_size为32KB对于当前数据库完全够用，暂时不必增加binlog_cache_size的值。

• sync_binlog

    在默认情况下，二进制日志并不是在每次写的时候同步到磁盘(可以理解为缓冲写)。因此，当数据库所在操作系统发送宕机时，可能会有最后一部分数据没有写入二进制日志文件，这会给恢复和复制带来问题。参数sync_binlog=[N]表示每写缓冲多少次就同步到磁盘。如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。sync_binlog的默认值为0，如果使用InnoDB存储引擎进行复制，并且想得到最大的高可用性，建议将值设置为ON。不过该值为ON时，确实会对数据库的IO系统带来一定的影响。

    但是将sync_binlog设为1，还是会有一种情况导致问题的发送。当使用innodb存储引擎时，在一个事务发出commit动作之前，由于sync_binlog为1，因此会将二进制日志立即写入磁盘。如果此时已经写入二进制日志，但是还没提交就发生了宕机，那么MySQL数据库下次启动时，由于commit操作没有发生，这个事务就会被回滚。但二进制日志已经记录了该事务信息，又不能被回滚。这个问题可以通过设置参数innodb_suport_xa=1来解决，确保二进制日志和InnoDB存储引擎数据文件的同步，但是默认这个参数是不开启的，因此性能影响会达到5%。

• binlog-do-db

    表示需要写入哪些库的的日志，默认为空，表示需要同步所有库的日志到二进制日志。

• binlog-ignore-db

    表示需要忽略哪些库的日志，默认为空，表示同步所有库的日志到二进制日志。

• log-slave-update

    如果当前数据库是slave角色，默认情况下slave不会将从master取得并执行的二进制日志写入自己的二进制日志文件中。如果需要写入，要设置log-slave-update。如果需要搭建master->slave->slave架构的复制，则必须设置该参数。

• binlog_format

    binlog_format参数十分重要，它影响记录二进制日志格式。在MySQL 5.1版本之前，没有这个参数。所有二进制文件的格式都是基于SQL语句(statement)级别的，因此基于这个格式的二进制日志文件的复制(replacation)和Oracle的逻辑Standy有点相似。同时，对于复制都是有一定要求的。如在主服务器运行的rand、uuid等函数，又或者使用触发器等操作，这些都可能会导致主从服务器上表中数据的不一致(not sync)。另一个影响是，会发现InnoDB存储引擎的默认事务隔离级别是repeatable read。这其实也是因为二进制日志文件格式的关系，如果使用read committed的事务隔离级别(大多数数据库，如Oracle、Microsoft SQL Server数据库的默认隔离级别)，会出现类型丢失更新的现象，从而出现主从数据库上的数据不一致。

  MySQL 5.1开始引入binlog_format参数，该参数可设的值有statement、row和mixed。

  • (1) statement格式和之前MySQL版本一样，二进制日志文件记录的是日志的逻辑SQL语句。

  • (2) 在row格式下，二进制日志记录的不再是简单的SQL语句了，而是记录表的行更改情况。基于row格式的复制类似于Oracle的物理standy(当然，还是有些区别)。同时，对上诉提及的Statement格式下复制的问题予以解决。从MySQL 5.1版本开始，如果设置了binlog_format为row，可以将InnoDB的事务隔离基本设为read committed，以获得更好的并发性。

  • (3) 在mixed格式下，MySQL默认采用statement格式进行二进制日志文件的记录，但是在一些情况下会使用row格式，可能的情况有：

    • 1.对表的DML操作都会以row格式记录
    • 2.使用uuid()、user()、current_user()、found_rows()、row_count()等不确定函数。
    • 使用insert delay语句
    • 使用用户定义函数(UDF)
    • 使用临时表(temporary table)

    此外，binlog_format参数还有对于存储引擎的限制，如下：

    存储引擎	Row格式	Statement格式
    InnoDB	Yes	Yes
    MyISAM	Yes	Yes
    HEAP	Yes	Yes
    MERGE	Yes	Yes
    NDB	Yes	No
    Archive	Yes	Yes
    CSV	Yes	Yes
    Federate	Yes	Yes
    Blockhole	No	Yes

    binlog_format是动态参数，因此可以在数据库运行环境下进行唔该，例如，我们可以将当前会话的binlog_format设为ROW，如：

  ##通过set @@session.binlog_format='row';可以修改。
  mysql> select @@session.binlog_format;
  +-------------------------+
  | @@session.binlog_format |
  +-------------------------+
  | ROW                     |
  +-------------------------+
  1 row in set (0.00 sec)
  
  

    当然，也可以将全局的binlog_format设置为想要的格式，不过通常这个操作会带来问题，运行时要确保更改后不会对复制带来影响。

  ## set global binlog_format = 'row';
  mysql> select @@global.binlog_format;
  +------------------------+
  | @@global.binlog_format |
  +------------------------+
  | ROW                    |
  +------------------------+
  1 row in set (0.00 sec)
  
  

    通常情况下，我们将参数binlog_format设置为ROW，这可以为数据库的恢复和复制带俩更好的可靠性。但是不能忽略的一点是，这会带来二进制文件的增加，相较于statement格式，row格式的二进制日志会需要更大的容量。

    所以当参数binlog_format设置为row时，会对磁盘空间要求有一点的增加。而由于腐殖酸采用传输二进制日志的方式实现的，因此复制的网络开销也有所增加。

  人如其名，二进制的文件格式为二进制，它不能像错误日志、慢查询日志那样使用cat、head、tail等命令来查看。要查看二进制日志文件的内容，必须通过MySQL提供的工具mysqlbinlog。对于statement格式的二进制日志文件，在使用mysqlbinlog后，看到的就是执行的逻辑SQL语句，如：

• statement格式

  ## 1.设置当前会话binlog格式为statement
  mysql> set @@session.binlog_format='statement';
  Query OK, 0 rows affected (0.00 sec)
  
  ## 2.查询当前会话的binlog格式
  mysql> select @@session.binlog_format;
  +-------------------------+
  | @@session.binlog_format |
  +-------------------------+
  | STATEMENT               |
  +-------------------------+
  1 row in set (0.00 sec)
  
  ## 3.查看binlog状态、position
  mysql> show master status\G;
  *************************** 1. row ***************************
               File: mysql-bin.000011
           Position: 115152005
       Binlog_Do_DB:
   Binlog_Ignore_DB:
  Executed_Gtid_Set:
  1 row in set (0.00 sec)
  
  ERROR:
  No query specified
  
  ## 4.修改SQL
  mysql> update zxy set name = 'zxy' where id = 3;
  Query OK, 1 row affected (0.01 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  ## 5.使用mysqlbinlog工具，查看binlog，可以看到在statement格式下，记录的是SQL语句
  [root@zxy_master mysql]# mysqlbinlog --start-position=115152005 mysql-bin.000011
  /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  DELIMITER /*!*/;
  # at 4
  #221116 14:53:40 server id 1  end_log_pos 123 CRC32 0x9c0223e1  Start: binlog v 4, server v 5.7.38-log created 221116 14:53:40 at startup
  # Warning: this binlog is either in use or was not closed properly.
  ROLLBACK/*!*/;
  BINLOG '
  9Ih0Yw8BAAAAdwAAAHsAAAABAAQANS43LjM4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  AAAAAAAAAAAAAAAAAAD0iHRjEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
  AeEjApw=
  '/*!*/;
  # at 115152005
  #230208  9:23:08 server id 1  end_log_pos 115152070 CRC32 0xa3c7cc82    Anonymous_GTID  last_committed=60946    sequence_number=60947   rbr_only=no
  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  # at 115152070
  #230208  9:23:08 server id 1  end_log_pos 115152147 CRC32 0x275b9221    Query   thread_id=2061  exec_time=0     error_code=0
  SET TIMESTAMP=1675819388/*!*/;
  SET @@session.pseudo_thread_id=2061/*!*/;
  SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  SET @@session.sql_mode=1436549152/*!*/;
  SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  /*!\C utf8 *//*!*/;
  SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
  SET @@session.lc_time_names=0/*!*/;
  SET @@session.collation_database=DEFAULT/*!*/;
  BEGIN
  /*!*/;
  # at 115152147
  #230208  9:23:08 server id 1  end_log_pos 115152259 CRC32 0xc13c4af2    Query   thread_id=2061  exec_time=0     error_code=0
  use `zxy`/*!*/;
  SET TIMESTAMP=1675819388/*!*/;
  update zxy set name = 'zxy' where id = 3
  /*!*/;
  # at 115152259
  #230208  9:23:08 server id 1  end_log_pos 115152290 CRC32 0xf3d9a61f    Xid = 655358
  COMMIT/*!*/;
  SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  DELIMITER ;
  # End of log file
  /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
  

• row格式

  ## 1.设置当前会话的binlog格式为row
  mysql> set @@session.binlog_format='row';
  Query OK, 0 rows affected (0.00 sec)
  
  ## 2.查看当前会话的binlog格式
  mysql> select @@session.binlog_format;
  +-------------------------+
  | @@session.binlog_format |
  +-------------------------+
  | ROW                     |
  +-------------------------+
  1 row in set (0.00 sec)
  
  ## 3.查看binlog状态、position
  mysql> show master status\G;
  *************************** 1. row ***************************
               File: mysql-bin.000011
           Position: 115152290
       Binlog_Do_DB:
   Binlog_Ignore_DB:
  Executed_Gtid_Set:
  1 row in set (0.00 sec)
  
  ERROR:
  No query specified
  
  ## 4.测试SQL
  mysql> update zxy set name = 'z' where id = 3;
  Query OK, 1 row affected (0.01 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  ## 5.当binlog_format格式为row的时候，简单的SQL"update zxy set name = 'z' where id = 3;"语句就记录了对于整个行更改的而信息。这也解释了为什么在row格式下，二进制文件较大。
  [root@zxy_master mysql]# mysqlbinlog -vv --start-position=115152290 mysql-bin.000011
  /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  DELIMITER /*!*/;
  # at 4
  #221116 14:53:40 server id 1  end_log_pos 123 CRC32 0x9c0223e1  Start: binlog v 4, server v 5.7.38-log created 221116 14:53:40 at startup
  # Warning: this binlog is either in use or was not closed properly.
  ROLLBACK/*!*/;
  BINLOG '
  9Ih0Yw8BAAAAdwAAAHsAAAABAAQANS43LjM4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  AAAAAAAAAAAAAAAAAAD0iHRjEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
  AeEjApw=
  '/*!*/;
  # at 115152290
  #230208  9:55:59 server id 1  end_log_pos 115152355 CRC32 0x50a2e5ba    Anonymous_GTID  last_committed=60947    sequence_number=60948   rbr_only=yes
  /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  # at 115152355
  #230208  9:55:59 server id 1  end_log_pos 115152426 CRC32 0x74f02ce6    Query   thread_id=2061  exec_time=0     error_code=0
  SET TIMESTAMP=1675821359/*!*/;
  SET @@session.pseudo_thread_id=2061/*!*/;
  SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  SET @@session.sql_mode=1436549152/*!*/;
  SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  /*!\C utf8 *//*!*/;
  SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
  SET @@session.lc_time_names=0/*!*/;
  SET @@session.collation_database=DEFAULT/*!*/;
  BEGIN
  /*!*/;
  # at 115152426
  #230208  9:55:59 server id 1  end_log_pos 115152477 CRC32 0x78036d13    Table_map: `zxy`.`zxy` mapped to number 165
  # at 115152477
  #230208  9:55:59 server id 1  end_log_pos 115152537 CRC32 0xfdd1d78e    Update_rows: table id 165 flags: STMT_END_F
  
  BINLOG '
  LwHjYxMBAAAAMwAAAF0W3QYAAKUAAAAAAAEAA3p4eQADenh5AAMDDw8EGQAyAAcTbQN4
  LwHjYx8BAAAAPAAAAJkW3QYAAKUAAAAAAAEAAgAD///4AwAAAAN6eHkDMTI0+AMAAAABegMxMjSO
  19H9
  '/*!*/;
  ### UPDATE `zxy`.`zxy`
  ### WHERE
  ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
  ###   @2='zxy' /* VARSTRING(25) meta=25 nullable=1 is_null=0 */
  ###   @3='124' /* VARSTRING(50) meta=50 nullable=1 is_null=0 */
  ### SET
  ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
  ###   @2='z' /* VARSTRING(25) meta=25 nullable=1 is_null=0 */
  ###   @3='124' /* VARSTRING(50) meta=50 nullable=1 is_null=0 */
  # at 115152537
  #230208  9:55:59 server id 1  end_log_pos 115152568 CRC32 0x4a89c161    Xid = 655362
  COMMIT/*!*/;
  SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  DELIMITER ;
  # End of log file
  /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
  

3 套接字文件

  在UNIX系统下本地连接MySQL可以采用UNIX域套接字方式，这种方式需要一个套接字(socket)文件。套接字文件可由参数socket控制。一般在/tmp目录下，名为mysql.sock。

mysql> show variables like 'socket'\G;
*************************** 1. row ***************************
Variable_name: socket
        Value: /var/lib/mysql/mysql.sock
1 row in set (0.00 sec)

4 PID文件

  该MySQL实例启动时，会将自己的进程ID写入一个文件中-该文件即为pid文件。该文件可由参数pid_file控制，默认位于数据库目录下.

mysql> show variables like 'pid_file';
+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| pid_file      | /var/run/mysqld/mysqld.pid |
+---------------+----------------------------+
1 row in set (0.01 sec)

5 表结构定义文件

  因为MySQL插件式存储引擎的体系结构的关系，MySQL数据的存储是根据表进行的，每个表都会有与之相应的文件。但不论表采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义。

  frm还用来存放视图的定义，如果用户创建了一个v_a视图，那么对应的会产生一个v_a.frm文件，用来记录视图的定义，该定义是文本文件，可以直接使用cat命令进行查看。

## 1.创建视图
mysql> create view view_zxy as select * from zxy;
Query OK, 0 rows affected (0.02 sec)

## 2.查看视图
mysql> select * from view_zxy;
+------+------+---------------------+
| id   | name | testtime            |
+------+------+---------------------+
|   19 | xia  | 2022-03-04 16:22:28 |
|   20 | zhou | 2027                |
|   21 | zxy  | 2027                |
|    1 | zxy  | 2027                |
|   22 | zxy  | 2027                |
|   23 | zxy  | 2028                |
|    1 | zxy  | 13232               |
|    1 | zxy  | 13232               |
|    1 | zxy  | 1                   |
|    2 | 1    | 1                   |
|   12 | 1    | 13                  |
|    3 | z    | 124                 |
+------+------+---------------------+
12 rows in set (0.00 sec)

## 3.打开对应数据库文件夹
[root@zxy_master /]# cd /var/lib/mysql/zxy
## 4.查看view_zxy视图记录
[root@zxy_master zxy]# cat view_zxy.frm
TYPE=VIEW
query=select `zxy`.`zxy`.`id` AS `id`,`zxy`.`zxy`.`name` AS `name`,`zxy`.`zxy`.`testtime` AS `testtime` from `zxy`.`zxy`
md5=96e65d0b46d17d7ccd8f94ecd0cbdc48
updatable=1
algorithm=0
definer_user=root
definer_host=localhost
suid=2
with_check_option=0
timestamp=2023-02-08 02:57:11
create-version=1
source=select * from zxy
client_cs_name=utf8
connection_cl_name=utf8_general_ci
view_body_utf8=select `zxy`.`zxy`.`id` AS `id`,`zxy`.`zxy`.`name` AS `name`,`zxy`.`zxy`.`testtime` AS `testtime` from `zxy`.`zxy`

6 InnoDB存储引擎文件

  之前介绍的文件都是MySQL数据库本身的文件，和存储引擎无关。除了这些文件外，每个表存储引擎都有其自己独有的文件。本节将具体介绍与InnoDB存储引擎密切相关的文件，这些文件包括重做日志文件、表空间文件。

6.1 表空间文件

  InnoDB采用将存储的数据按表空间(tablespace)进行存放的设计。在默认配置下会有一个初始大小为10MB，名为idbta1的文件。该文件就是默认的表空间文件(tablespace file)，用户可以通过参数innodb_data_file_path对其进行设置，格式如下：

## innodb_data_file_path=datafile_spec1[;datafile_spec2]...
mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

  用户可以通过多个文件组成一个表空间，同时制定文件的属性，如：

[mysqld]

innodb_data_file_path = /db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

  这里将/db/ibdata1和/dr2/db/ibdata2两个文件来组成表空间。若这两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能。同时，两个文件的文件名后面都跟了属性，表示文件ibdata1的大小为2000MB；文件ibdata2的大小为2000MB，如果用完也可以自动增长(autoextend)；

  设置innodb_data_file_path参数后，所有基于InnoDB存储引擎的表数据都会记录到该共享表空间中。

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

  若设置了参数innodb_file_per_table，则用户可以将每个基于InnoDB存储引擎的表产生一个独立表空间。独立表空间命名规则为：表名.idb。

# 如下所示，article和c_zxy有独立的表空间。
# 需要注意的是，这些单独表空间文件仅存储该表的数据、索引和插入缓冲bitmap等信息
# 其余信息还是存放在默认表空间中。
mysql> system ls -lh /var/lib/mysql/zxy
total 69M
-rw-r----- 1 mysql mysql 8.6K Oct  8 16:51 article.frm
-rw-r----- 1 mysql mysql  96K Oct  8 16:51 article.ibd
-rw-r----- 1 mysql mysql 8.5K Oct  8 16:51 c_zxy.frm
-rw-r----- 1 mysql mysql  96K Oct  8 16:51 c_zxy.ibd

6.2 重做日志文件

  在默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。在MySQL中称其为InnoDB存储引擎的日志文件，也可以称其为重做日志文件(redo log file)。为什么强调是重做日志文件呢？因为重做日志文件对于InnoDB存储引擎至关重要，记录着InnoDB存储引擎的事务日志。

  当实例或介质失败(media failure)时，重做日志文件就能派上用场。例如，数据库由于所在主机掉电导致实例失败，InnoDB存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。

  每个InnoDB存储引擎至少有1个重做日志文件组，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0和ib_logfile1。为了得到更高的可靠性，用户可以设置多个镜像日志组，将不同文件组放在不同的磁盘上，以此提高重做日志的高可用性。

下列参数影响着重做日志的属性：

• innodb_log_file_size

  该参数指定每个重做日志文件的大小

  mysql> show variables like 'innodb_log_file_size';
  +----------------------+----------+
  | Variable_name        | Value    |
  +----------------------+----------+
  | innodb_log_file_size | 50331648 |
  +----------------------+----------+
  1 row in set (0.00 sec)
  

• innodb_log_files_in_group

  该参数指定了日志文件组中重做日志文件的数量，默认为2

  mysql> show variables like 'innodb_log_files_in_group';
  +---------------------------+-------+
  | Variable_name             | Value |
  +---------------------------+-------+
  | innodb_log_files_in_group | 2     |
  +---------------------------+-------+
  1 row in set (0.00 sec)
  

• innodb_mirrored_log_groups

  该参数指定了日志镜像文件组的数量，默认为1。表示只有一个日志文件组，没有镜像。若磁盘本身已经做了高可用的方案，如磁盘阵列，那么可以不开启重做日志镜像的功能。

• innodb_log_group_home_dir

  该参数指定了日志文件组的路径，默认为./，表示MySQL数据库的数据目录下。

  重做日志的大小设置对于InnoDB存储引擎的性能有非常大的影响。一方面重做日志文件不能设置太大，如果设置很大，在恢复时可能需要很长的时间；另一方面又不能设置的太小，否则可能导致一个事务的日志需要多层切换重做日志文件。此外，重做日志文件太小会导致频繁发生async checkpoint,导致性能的抖动。例如会看到如下的警告信息：

160719 13:43:04  InnoDB: ERROR: the age of the last checkpoint is 9448350,
InnoDB: which exceeds the log group capacity 9433498.

  当出现以上问题的时候，可以关闭数据库服务，然后将两个重做日志文件备份并删除，然后修改my.cnf文件，给参数innodb_log_file_size分配更大的空间，最后启动数据库服务。

  而为什么出现这种问题呢？主要是因为重做日志有一个capacity变量，该值代表了最后的检查点不能超过这个阈值，如果超过则必须将缓冲池(innodb buffer pool)中脏页列表(flush list)中部分脏数据写回磁盘，这时会导致用户线程的阻塞。

二进制日志和重做日志有什么区别呢？

  首先，二进制日志会记录所有与MySQL数据库有关的日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志。而InnoDB的重做日志只记录有关本身的事务日志。

  其次，记录的内容不同，二进制日志的各种类型日志，记录的都是关于事务的具体操作内容，即该日志是逻辑日志。而InnoDB存储引擎的重做日志文件记录的是关于每个页(Page)的更改的物理情况。

  最后，两者写入的时间也不同，二进制日志仅在事务提交前进行提交，即只写磁盘一次，不论该事务多大。而在事务进行的过程中，却不断有重做日志条目(redo entry)被写入到重做日志文件中。

重做日志写流程？

  写入重做日志的操作不是直接写，而是先写入一个重做日志缓冲(redo log buffer)中，然后按照一定的条件顺序的写入日志。

  从重做日志缓冲往磁盘写入时，是按512个字节，也就是一个扇区的大小进行写入。因为扇区是写入的最小单位，因此也可以保证写入必定是成功的。因此在重做日志的写入过程中不需要有doublewrite。

重做日志写入磁盘的条件有哪些呢？

  一、主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件中，不论事务是否已经提交。二、写操作的过程是由参数innodb_flush_log_at_trx_commit控制，表示提交(commit)操作时，处理重做日志的方式。

  参数innodb_flush_log_at_trx_commit的有效值有0、1、2。0代表当提交事务时，并不将事务的重做日志写入磁盘上的日志文件，而是等待主线程每秒的刷新。1和2不同的地方在于：1表示在执行commit时将重做日志缓冲同步到磁盘，即伴有fsync的调用。2表示将重做日志异步写入到磁盘，即写到文件系统的缓存中。因此不能完全保证在执行commit时肯定会写入重做日志文件，只是有这个动作发生。

  因此为保证事务的ACID中的持久性，必须将innodb_flush_log_at_trx_commit设置为1，也就是每当事务提交时，就必须确保事务都已经写入重做日志文件。那么当数据库因为意外宕机时，可以通过重做日志文件恢复，并保证可以恢复已经提交的事务。而重做日志设置为0或2，都有可能发生恢复时部分事务的丢失。不同之处在于，设置为2时，当MySQL数据库发生宕机而操作系统及服务器并没有发生宕机时，由于此时未写入磁盘的事务日志保存在文件系统缓存中，当恢复时同样能保证数据不丢失。

mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

7 小结

  文件章节可以分为MySQL数据库文件以及各存储引擎相关的文件。与MySQL数据库有关的文件中，错误日志和二进制日志非常重要。当MySQL数据库发生任何问题时，DBA首先应该去查看错误日志，从文件提示中找到问题所在。而错误日志中记录的警告今昔，可以帮助DBA对数据库和存储引擎进行优化。

  二进制日志的作用同样关键，可以用来进行point in time的恢复以及复制环境(replication)的搭建。因此任何时间都需要启用二进制日志的记录。并且根据场景需要，选择合适的二进制日志类型(statement、mix、row)。

  最后InnoDB存储引擎相关的文件，包括表空间文件和重做日志文件。表空间文件用来管理InnoDB存储引擎的存储，分为共享表空间和独立表空间。重做日志非常的重要，用来记录InnoDB存储引擎的事务日志，也因为重做日志的存储，才使得InnoDB存储引擎可以提供可靠的事务。