文件

Posted on

介绍MySQL数据库和InnoDB存储引擎表的各种类型文件

参数文件

告诉MySQL实例启动时，在哪里找到数据库文件，并且指定某些初始化参数。可通过命令mysql --help | grep my.cnf来寻找参数文件

MySQL可以不需要参数，这时的参数取决于编译MySQL时指定的默认值和源代码中指定参数的默认值

参数其实就是键值对，可以通过SHOW VARIABLES查看数据库中的所有参数，也可以通过LIKE来过滤参数名

MySQL参数可以分为动态和静态两种类型。动态参数意味着可以在MySQL实例运行中进行更改，静态参数则相反。可以通过SET对动态参数值进行修改，语法如下：

1
2
3
SET
| [global | session] system_var_name= expr
| [@@global. | @@session. | @@] |system_var_name= expr

global和session两个关键词表明该参数的修改是基于当前回话还是整个实例的生命周期，例子如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> SET read_buffer_size=524288;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.read_buffer_size;
+----------------------------+
| @@session.read_buffer_size |
+----------------------------+
|                     524288 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@global.read_buffer_size;
+---------------------------+
| @@global.read_buffer_size |
+---------------------------+
|                    131072 |
+---------------------------+
1 row in set (0.00 sec)

对变量的全局值进行修改，只在这次实例的生命周期内有效，不会去修改参数文件中对应的值，因此用户如果想在MySQL下次启动时使用该参数，就要去修改参数文件

日志文件

记录MySQL实例对某种条件作出相应时写入的文件，如错误日志、二进制文件、慢查询文件、查询日志文件

MySQL日志文件有四类，这些日志文件将帮助开发人员对数据库的运行状态进行诊断，并进行数据库层面的优化

错误日志

对MySQL的启动、运行、关闭过程进行了记录，可通过SHOW VAIABLES LIKE ‘log_error来定位该文件

慢查询日志

slow log可以定位可能存在问题的SQL语句，从而进行SQL语句层面的优化

MySQL在启动时，要设置一个阈值，将运行时间超过（大于，而非大于等于）该阈值的SQL语句都记录到慢查询日志文件中，该值可以通过long_query_time设置，单位是微秒

1
2
3
4
5
6
7
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

默认情况下，MySQL是不启动慢查询日志，要手动设置为ON

1
2
mysql> show variables like 'log_slow_queries';
Empty set (0.00 sec)

MySQL另外一个参数是log_throttle_queries_not_using_indexes，用来表示每分钟允许记录到slow log的且未使用索引的SQL语句次数

当慢查询日志越来越多时，就需要MySQL提供的mysqldumpslow命令

1
2
3
4
5
6
7
8
9
mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output
...

MySQL也将慢查询日志记录放在一张表中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
show create table mysql.slow_log;
| Table    | Create Table                                                                             
| slow_log | 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'

参数log_output指定了慢查询输出的格式，默认为FILE，可将其设置为TABLE

MySQL还增加了来逻辑读取和物理读取的统计，物理读取就是指从磁盘进行I/O读取的次数，逻辑读取包含所有的读取，不管是磁盘还是缓冲池。可以设置long_query_io参数来将超过指定逻辑IO次数的查询记录到slow log中

查询日志

记录了对MySQL数据库请求的所有信息，无论这些请求是否得到了正确执行

二进制日志

记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身没有修改

总的来说，二进制文件主要有以下作用

  • 恢复：某些数据的恢复需要二进制日志，用户可以通过二进制文件进行point-in-time的恢复
  • 复制：与恢复类似，通过复制和执行二进制日志使一台远程数据库（slave或standby）与另一台（master或primary）进行实时同步
  • 审计：通过二进制日志中的信息进行审计，判断是否对数据库进行注入的攻击

以下参数配置会影响二进制文件的信息和行为

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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_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_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                  |
| innodb_locks_unsafe_for_binlog             | OFF                  |
| log_statements_unsafe_for_binlog           | ON                   |
| max_binlog_cache_size                      | 18446744073709547520 |
| max_binlog_size                            | 104857600            |
| max_binlog_stmt_cache_size                 | 18446744073709547520 |
| sync_binlog                                | 1                    |
+--------------------------------------------+----------------------+
22 rows in set (0.01 sec)

max_binlog_size

指定单个二进制文件的最大值，如果超过，建立新的二进制文件

binlog_cache_size

当使用事务的表存储引擎时，所有未提交的二进制日志都会被记录到一个缓存中，等该事务提交时直接将缓冲中的二进制日志记录到二进制日志文件中，该参数就是指定缓冲区的大小

该参数是基于会话（session），也就是说，当一个线程开始一个事务时，MySQL就会自动分配一个大小为binlog_cache_size的缓存，因此不能设置过大，但是也不能设置过小，因为当事务记录大于设定的参数时，会将缓存中的日志写入一个临时文件。可以通过下面去查看这个参数设置是否合适

1
2
3
4
5
6
7
8
mysql> show global status like "binlog_cache%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0     |
| Binlog_cache_use      | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

Binlog_cache_disk_use记录使用临时文件写二进制日志的次数，Binlog_cache_use记录使用缓存写二进制日志的次数。使用之前要先配置log-bin，并且要重启，我说怎么次数是0

1
root@ubuntu:/home/xzy# service mysql restart

sync_binlog

二进制日志并不是每次写的时候同步到磁盘的。该参数就是表示缓存多少次同步到磁盘

binlog_format

影响记录二进制日志的格式，在MySQL 5.1之前，二进制文件格式都是基于SQL语句级别。这种形式的二进制文件会导致主从数据库的数据不一致

因此引入以下参数

  • STATEMENT：这种模式记录的依旧是逻辑SQL语句
  • ROW：记录表的更改情况
  • MIXED：MySQL默认使用STATEMENT格式进行记录，但是在某些情况下使用ROW格式记录

使用ROW格式，可能会导致二进制文件大小变得很大，这对磁盘空间要求有一定的增加，而且复制是采用传输二进制文件实现的，会对网络开销产生影响

查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
root@ubuntu:/var/log/mysql# mysqlbinlog mysql-bin.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190312  7:20:40 server id 1  end_log_pos 123 CRC32 0x7bdc45e3  Start: binlog v 4, server v 5.7.25-0ubuntu0.18.04.2-log created 190312  7:20:40 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
OMCHXA8BAAAAdwAAAHsAAAABAAQANS43LjI1LTB1YnVudHUwLjE4LjA0LjItbG9nAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA4wIdcEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AeNF3Hs=
'/*!*/;
# at 123
#190312  7:20:40 server id 1  end_log_pos 154 CRC32 0x47bb9591  Previous-GTIDs
# [empty]
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*/;

此时产生的信息时不可读的，要加上参数-v或-vv，可以看到执行的具体信息

套接字文件

当用UNIX域套接字文件进行连接时需要的文件，套接字文件可由参数socket控制

pid文件

MySQL实例启动时，会将自己的进程ID写入pid文件

上面两个文件的位置都可以在配置文件中查看到

MySQL表结构文件

因为MySQL插件式存储引擎的体系结构的关系，MySQL数据的存储都是基于表的，每个表都有与之对应的文件。但不论表采取何种存储引擎，MySQL都有一个frm为后缀名的文件，记录该表的表结构定义。frm同样用来存放视图的定义

上面介绍的都是MySQL数据库本身的文件，和存储引擎无关

存储引擎文件

MySQL的存储引擎都有自己的文件来存储各种数据，这些存储引擎才真正存储记录和索引等数据

表空间文件

InnoDB采用将存储的数据按照表空间进行存放的设计。在默认配置下，会有一个初始大小为10M,名为iddata1的文件，该文件就是默认的表空间文件

可以通过innodb_data_file_path参数将多个文件组成一个表空间，同时设置文件的属性。如果文件位于不同的磁盘上，就能达到负载均衡，提供数据库整体性能。设置了该参数，则所有InnoDB存储引擎的表的数据都会记录到该共享表空间中

另一个参数innodb_file_per_table，用户可以将每个基于InnoDB的表产生一个独立表空间，文件名为table_name.ibd，这些单独的文件仅存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是放在默认的共享表空间

重做日志文件

该文件对InnoDB至关重要，记录了对于InnoDB的事务日志。InnoDB会根据重做日志文件将数据库恢复到出现故障之前的状态，保证数据的完整性

每个InnoDB存储引擎至少有1个重做日志文件组，每个组下面至少2个重做日志文件。在日志组中每个重做日志文件大小一致。并以循环写入的方式运行

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

  • innodb_log_file_size：重做日志文件大小
  • innodb_log_file_in_group：日志文件组中重做日志文件的数量
  • innodb_mirrored_log_groups：日志镜像文件组的数量
  • innodb_log_group_name：日志文件组所在路径，默认为./，表示MySQL数据库的数据目录下

重做日志文件的大小设置对性能影响非常大。设置过大，恢复时需要很长的时间，设置过小，会但质一个事务的日志需要多次切换重做日志文件

和二进制日志的区别

  • 二进制文件记录的是和MySQL相关的日志记录，包括InnoDB、MyISAM等日志；InnoDB重做日志文件只记录自身引擎本身的事务日志
  • 二进制文件记录关于一个事务的具体操作，该日志是逻辑日志，重做日志记录关于每个页的更改的物理情况
  • 二进制日志文件仅在事务提交前进行提交，不论事务多大，只提交一次；重做日志在事务执行期间不断有条目写入日志

重做日志条目由4部分组成

  • redo_log_type：表示重做日志的类型
  • space：表空间ID
  • page_no：页的偏移量
  • redo_log_body：数据部分，恢复时需要调用相应函数进行解析

写入重做日志文件先写入一个重做日志缓冲，再按照一定条件写入磁盘，也就是重做日志文件，此时按照512字节大小，也就是一个扇区的大小写入。因为扇区是写入的最小单位，因此可以保证写入必定成功

主线程每秒会将重做日志缓冲写入文件，不论事务是否已经提交

另一个触发写入操作由参数innodb_flush_log_at_trx_commit控制。0表示当提交事务时，并不将事务的重做日志写入磁盘文件的日志文件，而是等待主线程的刷新。1表示在执行commit时将重做日志缓冲同步到磁盘，即伴有fsync的调用。2表示重做日志异步写到磁盘，即写到文件系统的缓存中