本书主要对MYSQL服务端各种存储文件进行介绍，以便了解其原理。

前言：MYSQL是通过文件系统对数据和索引进行存储的，从物理结构上可以分为日志文件和数据索引文件

数据文件

数据文件基本上是随机IO，可通过如下命令查看其相关参数

mysql> SHOW VARIABLES LIKE '%datadir%';
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| datadir       | /home/mysql/mysql3306/data/ |
+---------------+-----------------------------+
1 row in set (0.00 sec)

INNODB数据文件

• .frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
• .ibd文件：使用独享表空间存储表数据和索引信息
  • 独享表空间：一张表对应一个ibd文件
  • 共享表空间：多张表对应一个文件

mysql> CREATE TABLESPACE shared_tablespace
    -> ADD DATAFILE 'shared_tablespace.ibd'
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql> use toby;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> alter table t2 tablespace shared_tablespace;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SELECT *  FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES ;
+-------+----------------------------------------------------------+
| SPACE | PATH                                                     |
+-------+----------------------------------------------------------+
|   100 | ./shared_tablespace.ibd                                  |
+-------+----------------------------------------------------------+

MYISAM数据文件

• .frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
• .myd文件：主要用来存储表数据信息
• .myi文件：主要用来存储表数据文件中任何索引的数据树

其他引擎不常使用，先不介绍

日志文件

错误日志

MySQL服务器记录各种错误和警告的文件。它可以帮助您诊断和解决MySQL服务器的问题。

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

错误日志中包含如下信息：

• 错误和警告信息
• 启动和关闭时间
• 查询和语法错误
• 连接问题
• 备份和恢复操作
• 性能问题
• 服务器状态变更

慢查询日志

记录执行时间超过某一阀值的所有SQL，慢查日志可以帮助定位可能存在问题的额SQL语句。默认情况下，MySQL并不启动慢查日志。

相关参数配置

• slow_query_log:设置为1或ON以启用慢查询日志
• long_query_time:指定查询执行时间的阈值（以秒为单位）。超过该阈值的查询将被记录到慢查询日志中
• slow_query_log_file:记录慢查询日志的存储路径
• log_queries_not_using_indexes：如果将此参数设置为1或ON，MySQL会将未使用索引的查询语句记录到慢查询日志中。这对于识别需要优化索引的查询非常有用。默认情况下，它的值为0。
• log_throttle_queries_not_using_indexes：此参数与log_queries_not_using_indexes一起使用，用于限制记录未使用索引的查询语句的频率。您可以设置一个时间间隔（以秒为单位），在该时间间隔内，只有一条未使用索引的查询语句会被记录到慢查询日志中。默认情况下，它的值为0，表示不进行限制。
• min_examined_row_limit：如果查询扫描的行数未达到此参数指定的值，则不会被记录到慢查询日志中。这可以用于排除对小型表的查询记录，以避免日志文件被填充。默认情况下，它的值为0，表示不进行限制。
• log_slow_admin_statements：如果将此参数设置为1或ON，MySQL会将管理员执行的查询（如ALTER TABLE、CREATE INDEX等）记录到慢查询日志中。默认情况下，它的值为0。
• log_slow_slave_statements:用于指定是否将从服务器（Slave）执行的慢查询语句记录到慢查询日志中。
• log_output：指定日志输出的方式。常见的选项包括FILE（输出到文件）和TABLE（输出到表）。默认情况下，它的值为FILE。

文件格式

相关参数配置如下：

mysql> SHOW VARIABLES LIKE '%slow_%';
+---------------------------+------------------------------------+
| Variable_name             | Value                              |
+---------------------------+------------------------------------+
| log_slow_admin_statements | OFF                                |
| log_slow_slave_statements | OFF                                |
| slow_launch_time          | 2                                  |
| slow_query_log            | ON                                 |
| slow_query_log_file       | /home/mysql/mysql3306/log/slow.log |
+---------------------------+------------------------------------+
5 rows in set (0.00 sec)

执行存储过程超时

# Time: 2023-07-09T01:52:00.353277Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 4.422799  Lock_time: 0.000057 Rows_sent: 0  Rows_examined: 0
use toby;
SET timestamp=1688867520;
call idata();

查询日志

是一种记录所有执行的查询语句的日志记录工具。它可以帮助您了解数据库的查询活动，包括查询语句、执行时间、返回结果等。

• general_log：设置为1或ON以启用查询日志。
• general_log_file：指定查询日志文件的路径和名称。

默认配置如下

mysql> SHOW VARIABLES LIKE '%general_log%';
+------------------+-----------------------------------------+
| Variable_name    | Value                                   |
+------------------+-----------------------------------------+
| general_log      | OFF                                     |
| general_log_file | /home/mysql/mysql3306/data/TOBY-HYW.log |
+------------------+-----------------------------------------+
2 rows in set (0.00 sec)

二进制日志 BINLOG

记录对MySql数据库执行更改的所有操作，不包括select，show这类操作。若更改操作并未修改，也会记录。 可用于恢复、复制、审计。

关键BINLOG参数

• log_bin：设置为1或ON以启用BINLOG。默认情况下，它是启用的。如果将其设置为0或OFF，则禁用BINLOG。
• binlog_format：指定BINLOG的格式。常见的格式包括STATEMENT、ROW和MIXED。
  • STATEMENT：记录SQL语句的原始文本。
  • ROW：记录每一行数据的变更。
  • MIXED：根据情况选择使用STATEMENT或ROW格式。
• binlog_expire_logs_seconds：指定BINLOG日志文件的过期时间，以秒为单位。过期的日志文件将被自动删除。
• binlog_cache_size：指定BINLOG的缓存大小。
• max_binlog_size：指定单个BINLOG日志文件的最大大小。当一个BINLOG文件达到指定大小时，MySQL会自动创建一个新的BINLOG文件。默认值为1GB。
• sync_binlog：用于控制BINLOG的同步策略。它决定了在事务提交时是否需要将BINLOG数据同步写入磁盘。
  • sync_binlog=0：表示不进行BINLOG的同步写入。在事务提交后，MySQL将异步地将BINLOG数据写入磁盘。这是最高性能的设置，但也是最不可靠的，因为在发生故障时可能会丢失最后一个提交的事务。
  • sync_binlog=1：表示在事务提交后，MySQL将立即将BINLOG数据同步写入磁盘。这提供了更高的可靠性，但也带来了一定的性能开销，因为每个事务都需要等待BINLOG的写入完成。
  • sync_binlog=N（N > 1）：表示在事务提交后，MySQL将每隔N个事务将BINLOG数据同步写入磁盘。这种设置可以在一定程度上平衡性能和可靠性，减少写入磁盘的频率，但仍具备一定的数据丢失风险。

查看当前BINLOG文件

使用命令如下

mysql> show binary logs;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| mysq3306-bin.000001 |       177 |
| mysq3306-bin.000002 |       154 |
| mysq3306-bin.000003 |      2244 |
| mysq3306-bin.000004 |       217 |
| mysq3306-bin.000005 |       194 |
| mysq3306-bin.000006 |      2294 |
| mysq3306-bin.000007 |  29495600 |
| mysq3306-bin.000008 |       217 |
| mysq3306-bin.000009 | 104857779 |
| mysq3306-bin.000010 |  79463590 |
| mysq3306-bin.000011 |   2685950 |
| mysq3306-bin.000012 |       194 |
| mysq3306-bin.000013 |       217 |
| mysq3306-bin.000014 |       701 |
| mysq3306-bin.000015 |       194 |
| mysq3306-bin.000016 |       194 |
| mysq3306-bin.000017 |       792 |
+---------------------+-----------+
17 rows in set (0.00 sec)

mysql> show master status;
+---------------------+----------+--------------+------------------+-----------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                             |
+---------------------+----------+--------------+------------------+-----------------------------------------------+
| mysq3306-bin.000017 |      792 |              |                  | 90acf47b-15bf-11ee-aceb-00155d34f9dc:1-811079 |
+---------------------+----------+--------------+------------------+-----------------------------------------------+
1 row in set (0.00 sec)

导入和查看

使用mysqlbinlog

mysqlbinlog /path/to/mysq3306-bin.000008 > binlog.sql
mysql -u username -p
mysql> source /path/to/binlog.sql;

SOCKET文件

是MySQL服务器用于与客户端进行通信的一种IPC（进程间通信）机制。它是一个特殊的文件，用于在本地主机上建立服务器与客户端之间的连接。

当MySQL服务器启动时，它会在指定的目录中创建一个sock文件。客户端可以通过连接到该sock文件来与MySQL服务器建立本地连接，而不需要通过网络进行通信。这种本地连接方式比基于网络的连接更快速和高效。

mysql> SHOW VARIABLES LIKE '%socket%';
+-----------------------------------------+-----------------------------------+
| Variable_name                           | Value                             |
+-----------------------------------------+-----------------------------------+
| performance_schema_max_socket_classes   | 10                                |
| performance_schema_max_socket_instances | -1                                |
| socket                                  | /home/mysql/mysql3306/mysqld.sock |
+-----------------------------------------+-----------------------------------+
3 rows in set (0.00 sec)

PID文件

PID文件是一个对应于MySQL服务器进程的文件，用于存储MySQL服务器进程的PID。PID文件的主要作用是让其他进程或管理工具能够轻松地找到和识别MySQL服务器进程，并进行相关的操作，如启动、停止、重启等。

mysql> SHOW VARIABLES LIKE '%pid%';
+------------------------+----------------------------------+
| Variable_name          | Value                            |
+------------------------+----------------------------------+
| log_syslog_include_pid | ON                               |
| pid_file               | /home/mysql/mysql3306/mysqld.pid |
+------------------------+----------------------------------+
2 rows in set (0.00 sec)

重做日志 REDOLOG(INNODB)

重做日志（Redo Log）是数据库系统中的一种机制，用于确保事务的持久性和恢复能力。它记录了数据库中发生的所有修改操作，包括插入、更新和删除，以便在系统崩溃或故障恢复后，可以重新应用这些操作，将数据库还原到最近一次提交的状态。 重做日志（redo log）由内存中的重做日志缓冲（redo log buffer），和重做日志文件（redo log files）组成

事务提交时，先写重做日志。redo log都是以512字节存储，重做日志缓冲和重做日志文件都是以块（block）进行保存，重做日志块（redo log block）和磁盘扇区大小一样，都是512字节。因此重做日志的写入可以保证原子性，不需要doublewrite技术。

关键参数

• innodb_log_file_size：指定每个重做日志文件的大小，默认为 48MB。可以根据系统的需求和负载情况进行适当调整。
• innodb_log_files_in_group：指定重做日志文件的数量，默认为 2。增加文件数量可以提高重做日志的并发写入能力。
• innodb_log_group_home_dir:用于指定InnoDB重做日志组文件的存储路径
• innodb_flush_log_at_trx_commit：控制InnoDB引擎在事务提交时将重做日志写入磁盘的行为。
  • 0：表示事务提交时不立即将重做日志写入磁盘。将重做日志缓冲在内存中，然后按一定的策略写入磁盘。这提供了较高的性能，但在系统故障时可能会丢失最后一个已提交的事务。
  • 1：表示事务提交时立即将重做日志写入磁盘。确保每个事务的持久性，但对性能有一定的影响。
  • 2：表示事务提交时将重做日志写入磁盘，但以每秒一次的方式进行刷新。这提供了一种折中的方式，在性能和持久性之间取得平衡。

重做日志与二进制日志区别

重做日志（Redo Log）和二进制日志（Binary Log）是数据库系统中两种不同的日志机制，它们具有不同的功能和目的。

1. 功能和目的：

   • 重做日志：用于确保事务的持久性和恢复能力。它记录了数据库中发生的所有修改操作，包括插入、更新和删除，以便在系统故障或崩溃后，可以重新应用这些操作来恢复数据库的一致性。
   • 二进制日志：用于数据库的备份、复制和恢复。它记录了数据库执行的所有更改操作，包括数据修改语句和数据定义语句，以便在需要时可以重放这些操作，实现数据的备份、复制或恢复。

2. 内容：

   • 重做日志：记录了对数据库数据页的物理修改操作，例如页面的插入、更新和删除。它记录了对数据文件的实际更改，而不是SQL语句本身。
   • 二进制日志：记录了数据库执行的SQL语句或数据更改语句，包括INSERT、UPDATE、DELETE等。它记录了执行的逻辑操作，而不是对数据文件的实际更改。

3. 存储位置：

   • 重做日志：通常存储在数据库引擎的数据目录中的特定文件中，如InnoDB的重做日志文件（ib_logfile0、ib_logfile1）。
   • 二进制日志：通常存储在数据库引擎的数据目录中的特定文件中，如MySQL的二进制日志文件（binlog）。

4. 使用场景：

   • 重做日志：主要用于事务的持久性和数据库的恢复。它在数据库崩溃后，通过重新应用重做日志中的操作，将数据库恢复到最近一次提交的状态。
   • 二进制日志：主要用于数据库的备份、复制和恢复。它可以用于创建数据库的逻辑备份，实现数据库的主从复制，以及在灾难恢复时进行数据的恢复。

总结：重做日志是为了事务的持久性和恢复而记录数据库物理修改操作的日志，而二进制日志是为了数据库的备份、复制和恢复而记录SQL语句和数据更改操作的日志。它们具有不同的功能、内容和使用场景。重做日志记录物理修改操作，而二进制日志记录逻辑操作。