MySQL · 文件 | Rao's Blog

前言

解析构成 MySQL 数据库和 InnoDB 存储引擎表的各种类型文件。

参数文件

1 2	// 参数文件 my.cnf -rw-r--r-- 1 root root 1149 3月 30 13:51 /etc/my.cnf

用途：用来指定数据库各种文件存储位置以及某些初始化参数

读取顺序：/etc/my.cnf –> /etc/mysql/my.cnf –> /usr/local/mysql/etc/my.cnf –> ~/.my.cnf

查询方法：show variables like “”;

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

参数类型：

动态参数：MySQL 实例运行时可修改，参数列表，例如：read_only、binlog_format
静态参数：MySQL 实例运行时不可修改，例如：server_id

// 动态参数
mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

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

注意：动态修改某参数，要同时修改配置文件，以防下一次启动该参数修改失效。

日志文件

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

// 日志文件
.
├── error.log                // 错误日志
├── mysql-bin.000001         // 二进制日志
├── mysql-bin.index
├── slow.log                 // 慢查询日志
└── general.log              // 查询日志

错误日志

用途：error log 对 MySQL 的启动、运行、关闭过程进行了记录，遇到问题首先查看该文件以便定位问题。

查询方法：

mysql> show variables like 'log_error';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| log_error     | /data/mysql/error.log |
+---------------+-----------------------+

慢查询日志

用途：slow log 可帮助定位可能存在问题的 SQL 语句，从而进行 SQL 语句层面的优化。

相关参数：

// 默认未开启，需要手动开启
mysql> show variables like 'slow_query_log%';
+---------------------+----------------------+
| Variable_name       | Value                |
+---------------------+----------------------+
| slow_query_log      | ON                   |
| slow_query_log_file | /data/mysql/slow.log |
+---------------------+----------------------+

// 慢查询阈值
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+

// 未使用索引的SQL会被记录到慢查询日志
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+

// 慢查询输出格式，默认为FILE，设置为TABLE可以在mysql.slow_log表查询
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

注意：设置阈值 long_query_time，运行时间正好等于 long_query_time 的 SQL 不会被记录。

优化：可以借助 pt-query-digest 工具进行分析优化。

查询日志

用途：记录了所有对 MySQL 数据库请求的信息，无论这些请求是否得到正确执行，查询日志都会记录。

相关参数：

mysql> show variables like 'general_log%';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| general_log      | OFF                |
| general_log_file | /data/mysql/db.log |
+------------------+--------------------+

// 日志输出格式设置为TABLE，可以在mysql.general_log表查询
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

二进制日志

用途：binary log 记录了对 MySQL 数据库执行更改的所有操作，不包括 select 和 show 操作。主要有以下三种作用：

恢复：可以通过二进制日志进行 point-in-time 恢复。
复制：主从数据实时同步。
审计：通过记录的日志信息进行审计，判断是否对数据库进行注入攻击。

相关参数：

// 默认是关闭的，通过log_bin打开
mysql> show variables like 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /data/mysql/mysql-bin       |
| log_bin_index                   | /data/mysql/mysql-bin.index |
+---------------------------------+-----------------------------+

// max_binlog_size 设置单个binlog文件最大值，如果超出生成新的日志文件，默认大小1.1G
mysql> show variables like 'max_binlog_size';
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| max_binlog_size                            | 1073741824           |
+--------------------------------------------+----------------------+

// binlog_cache_size 基于session分配的内存，在事务过程中用来存储二进制日志的缓存，默认大小32K
// 当事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲池中的日志写入一个临时文件，此值不能设置过小
mysql> show variables like 'binlog_cache_size';
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| binlog_cache_size                          | 32768                |
+--------------------------------------------+----------------------+

// Binlog_cache_use 记录使用缓冲写二进制日志的次数
// Binlog_cache_disk_use 记录使用临时文件写二进制日志的次数
mysql> show global status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 166   |
| Binlog_cache_use      | 168   |
+-----------------------+-------+

// sync_binlog 设置二进制日志（binlog）同步到磁盘的频率
mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+

// log_slave_updates 该参数主要是为了让从库从主库复制数据时可以写入到binlog日志
// 搭建master->slave->slave级联复制时必须设置
mysql> show variables like 'log_slave_updates';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | OFF   |
+-------------------+-------+

// 有三种日志格式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

// expire_logs_days 二进制日志过期时间，默认关闭
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 15    |
+------------------+-------+

日志格式	记录说明	优点	缺点
STATEMENT	记录逻辑 SQL 语句	日志文件小，节约 IO	准确性差，一些系统函数不能准确复制
ROW	记录表行数据变更信息	准确性强，恢复和复制可靠性更好	日志文件大，磁盘空间和网络开销会增加
MIXED	以上两种模式的混合	准确性强，文件大小适中	有可能发生主从不一致问题

查看工具：mysqlbinlog

套接字文件

1 2	// 套接字文件 srwxrwxrwx 1 mysql mysql 0 3月 30 13:53 /data/mysql/mysql.sock

用途：在 UNIX 系统下本地连接 MySQL 可以使用套接字方式。

相关参数：

mysql> show variables like 'socket';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| socket        | /data/mysql/mysql.sock |
+---------------+------------------------+

pid 文件

1 2	// pid文件 -rw-r----- 1 mysql mysql 6 3月 30 13:53 /data/mysql/mysql.pid

用途：当 MySQL 实例启动时，会讲自己的进程 ID 写入文件中。

相关参数：

mysql> show variables like 'pid_file';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| pid_file      | /data/mysql/mysql.pid |
+---------------+-----------------------+

表结构定义文件

// 表结构定义文件
.
├── current_dept_emp.frm
├── departments.frm
├── dept_emp.frm
└── titles.frm

用途： .frm 为后缀名的文件，是 MySQL 表结构定义文件，用来保存每个数据表的元数据 (meta) 信息，可以用来在数据库崩溃时恢复表结构。

InnoDB 存储引擎文件

// InnoDB 存储引擎文件
.
├── ibdata1                  // 共享表空间文件
├── ib_logfile0              // 重做日志文件
├── ib_logfile1
├── employees
│   ├── departments.ibd      // 独立表空间文件
│   ├── dept_emp.ibd
│   ├── dept_manager.ibd
│   ├── employees.ibd
│   └── titles.ibd
└── ...

表空间文件

InnoDB 采用将存储的数据按表空间（tablespace）进行存放的设计。

系统表空间：文件名为 ibdata1，初始化为 10M，存放 undo logs、缓存等。
共享表空间：.frm 共享表空间文件。
独立表空间：每个表都将会生成以独立的文件方式 .ibd 来存储，在 MySQL 5.6.6 后的版本，默认选择，主要存放数据、索引等信息，其余信息还是存放在共享表空间中。

相关参数：

mysql> show variables like 'innodb_data_file_path';
+-----------------------+-----------------------+
| Variable_name         | Value                 |
+-----------------------+-----------------------+
| innodb_data_file_path | ibdata1:1G:autoextend |
+-----------------------+-----------------------+

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

// test表从独立表空间移动到系统表空间
ALTER TABLE test TABLESPACE innodb_system;

// test表从系统表空间移动到独立表空间
ALTER TABLE test TABLESPACE innodb_file_per_table;

重做日志文件

用途：Redo Log 记录了对于 InnoDB 存储引擎的事务日志，默认会有两个日志文件 ib_logfile0 和 ib_logfile1。对于 InnoDB 存储引擎至关重要，当数据库由于主机掉电导致实例运行失败，InnoDB 存储引擎会使用重做日志恢复到掉电前的时刻，以此来保障数据的完整性。

相关参数：

mysql> show variables like 'innodb_log%';
+-----------------------------+------------+
| Variable_name               | Value      |
+-----------------------------+------------+
| innodb_log_file_size        | 1073741824 |
| innodb_log_files_in_group   | 2          |
| innodb_log_group_home_dir   | ./         |
+-----------------------------+------------+

mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+

重做日志文件的大小设置对于 InnoDB 存储引擎的性能有非常大的影响。如果设置太大，在恢复时可能需要很长的时间；如果设置太小，可能导致一个事务的日志需要多次切换重做日志文件，引起性能抖动。

redo log 与 binlog 的区别：

面向对象不同：binlog 面向所有存储引擎，redo log 仅面向 InnoDB 存储引擎。
记录内容不同：binlog 记录的是事务的逻辑 SQL 日志，redo log 记录关于每个页（page）的物理日志。
写入时间不同：binlog 在事务提交前提交，redo log 在事务进行过程中写入。
作用不同：binlog 用于恢复和复制，redo log 用于崩溃恢复。

innodb_flush_log_at_trx_commit：控制从日志缓冲写入磁盘上的重做日志文件的方式。

0：表示当提交事务时，不将重做日志缓冲立即写入磁盘的日志文件，而是等待主线程每秒的刷新。
1：表示当提交事务时，将重做日志缓冲同步写入磁盘，即伴有 fsync 调用。
2：表示当提交事务时，将重做日志缓冲异步写入磁盘，即写到文件系统的缓存中。

设置为 0 或者 2 时，都有可能在恢复时丢失部分事务，区别在于设置为 2 时，当 MySQL 数据库发生宕机而操作系统及服务器并没有发生宕机时，由于此时未写入磁盘的事务日志保存在文件系统缓存中，当恢复时同样能保证数据不丢失。