MySQL技术内幕 - 文件
参数文件
- 作用：mysql实例启动时，数据库会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数，这些参数通常定义了某种内存结构多大等。
- 查看参数：
代码
show variables;
- 参数类型有哪些？
动态参数：可以修改的参数，通过SET命令修改。
静态参数：只读参数，不可修改。
代码
mysql> show variables like 'read_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.00 sec)
## 修改动态参数
mysql> set read_buffer_size=524288;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'read_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| read_buffer_size | 524288 |
+------------------+--------+
1 row in set (0.00 sec)
套接字文件
- 作用：unix系统下本地连接Mysql采用unix域套接字方式，这种方式需要一个套接字文件。
代码
## 通过参数文件查看套接字文件的位置
mysql> show variables like 'socket'\g;
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| socket | /var/run/mysqld/mysqld.sock |
+---------------+-----------------------------+
1 row in set (0.01 sec)
## 查找套接字文件
bash-4.4# find / -name mysqld.sock
/run/mysqld/mysqld.sock
pid文件
- 作用：当mysql实例启动时，会将自己的进程ID写入一个文件，该文件就是pid文件。文件由参数pid_file控制，默认位于数据库目录下，文件名未主机名.pid。
代码
bash-4.4# find / -name *.pid
/var/run/mysqld/mysqld.pid
表结构定义文件
- 作用：mysql数据的存储是根据表进行的，每个表都有对应文件。mysql表结构定义文件以frm为后缀名，与此同时frm文件也存放视图定义。
- mysql8将表定义文件写在.ibd文件中。
innodb存储引擎文件
- 表空间文件：innodb将存储的数据按表空间进行存放设计。默认初始大小为10MB，名为ibdata1文件。使用innodb_data_file_path参数设置共享表空间。若设置了参数innodb_file_per_table,则用户可以将每个基于innodb存储引擎产生的表都放到一个独立表空间。命名规则为表名.ibd。
代码
## 查看配置的共享表空间文件，12M用完自动增长
mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.05 sec)
## 查看是否开启了独立表空间
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
查看共享表空间和独立表空间位置：
代码
bash-4.4# find / -name ibdata1
/var/lib/mysql/ibdata1
bash-4.4# find / -name *.ibd
/var/lib/mysql/education/edu_stu.ibd
/var/lib/mysql/education/edu_teacher.ibd
单独表空间文件仅存放表的数据、索引和插入缓冲Bitmap，其余的还是在共享表空间文件中。
- 重做日志文件：默认情况下，innodb引擎下的数据目录存在两个名为ib_logfile0和ib_logfile1文件，就是重做日志文件。
- 重做日志文件作用：记录了innodb存储引擎的事务日志（实际就是每个数据页Page的更改物理情况）。每个innodb存储引擎至少有1个重做日志文件组，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0和ib_logfile1。
![image]()
注意：重做日志文件太大会带来很长的恢复时间，太小可能会让一个事务多次切换重做日志文件，还会频繁的发生async checkpoint，造成性能抖动。重做日志有一个检查点，如果超过了这个检查点，那么必须将缓冲池中脏页列表中的部分脏数据页写回磁盘，导致用户线程的阻塞。
写入重做日志过程：先写入重做日志缓冲区（redo log buffer），然后按照一定条件顺序写入日志文件。重做日志缓冲往磁盘写入时，是按512个字节，也就是一个扇区大小写入。因为扇区是写入的最小单位，可以保证必定写入成功，所以不需要双写（double write）。
什么是double write：为了提高innodb的可靠性，因为innodb在写数据过程中，只写了部分数据就写失败了，为了保证数据持久性，将剩下的脏数据写到了磁盘上，就是写到了共享空间ibdata1中，之前的一份写到了真正的数据永久保存，写了两次脏数据的过程叫double write。
日志缓冲区写入到重做日志文件的时机：1.主线程master中每秒会将重做日志缓冲写入磁盘的重做日志中，不论事务是否已经提交。2.写磁盘过程由参数innodb_flush_log_at_trx_commit参数控制，表示在提交操作时，处理重做日志的方式。
代码
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)
"0"代表当提交事务时，不将事务的重做日志写入磁盘的日志文件，而是等待主线程每秒的刷新。"1"代表在执行commit时，将重做日志缓冲同步到磁盘上（即伴有fsync的调用）。"2"代表将重做日志异步写到磁盘。
日志文件
- 错误日志：主要记录了mysql的启动、运行、关闭。
代码
## 查看错误日志文件的位置
mysql> show variables like 'log_error';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| log_error | stderr |
+---------------+--------+
1 row in set (0.00 sec)
stderr：该值意思是错误打印到控制台。
- 慢查询日志：可以在mysql启动时设置一个最大值，将运行时间超过该值的sql语句都记录到慢查询日志中。通过参数long_query_time来设置，默认值为10，代表10秒。使用slow_query_log来启动慢查询日志。
代码
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)
## 开启慢查询日志
mysql> set global slow_query_log ='ON';
Query OK, 0 rows affected (0.01 sec)
## 查看慢查询的阙值
mysql> show VARIABLES like 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
##慢查询日志的位置
mysql> show variables like 'slow_query_log_file';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log_file | /var/lib/mysql/23163e75e735-slow.log |
+---------------------+--------------------------------------+
1 row in set (0.00 sec)
参数log_queries_not_using_indexes设置将未使用索引的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.slow_log表。
- 查询日志：查询日志记录了所有对mysql数据库请求的信息，无论请求是否得到了正确的执行，默认文件名为主机.log。
查询日志会记录在mysql.general_log表。
代码
## 查看查询日志是否开启
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
1 row in set (0.01 sec)
## 开启查询日志
mysql> set global general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)
## 查询日志的位置
mysql> show variables like 'general_log_file';
+------------------+---------------------------------+
| Variable_name | Value |
+------------------+---------------------------------+
| general_log_file | /var/lib/mysql/23163e75e735.log |
+------------------+---------------------------------+
1 row in set (0.00 sec)
- 二进制日志：二进制日志记录了mysql执行更改的所有操作，不包括select和show操作。二进制文件的设置在my.cnf中为log-bin=mysql-bin，二进制日志的后缀名为二进制日志的序列号。
作用：1.恢复：用户可以通过二进制日志进行point-in-time恢复。2.复制：通过复制和执行二进制日志使一台远程的mysql数据库（一般称为slave或者standby）与一台mysql数据库（一般成为master）进行实时同步。3.审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。
代码
## 执行update语句
update edu_user set password = '000000' where username = 'zhangsan';
## 查询master线程的状态
mysql> show master status;
+---------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+-------------------------------------------------+-------------------+
| binlog.000002 | 531 | | mysql,performance_schema,information_schema,sys | |
+---------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
## 查看刚才update语句
mysql> show binlog events in 'binlog.000002';
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------+
| binlog.000002 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.29, Binlog ver: 4 |
| binlog.000002 | 126 | Previous_gtids | 1 | 157 | |
| binlog.000002 | 157 | Anonymous_Gtid | 1 | 236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 236 | Query | 1 | 337 | BEGIN |
| binlog.000002 | 337 | Query | 1 | 500 | use `education`; update edu_user set password = '000000' where username = 'zhangsan' |
| binlog.000002 | 500 | Xid | 1 | 531 | COMMIT /* xid=54 */ |
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
二进制日志的参数：
1.max_binlog_size:单个二进制文件的最大值。
2.binlog_cache_size:基于会话的缓存。
3.binlog_cache_disk_use:记录了使用临时文件写二进制日志的次数。
4.binlog_format：参数指定二进制日志文件的格式。使用STATEMENT的好处就是它占用空间少，因为它只记录SQL语句，缺点就是如果语句中使用了UUID这类随机或依赖环境的函数，那么当需要通过二进制日志复制时就会出现数据不一致的问题。使用ROW的好处就是它不会出现数据不一致问题，还有什么启用这个就可以开启已提交读隔离级别，因为它相当于在日志中记录了整行修改后的快照。缺点就是占用空间极大。使用MIXED会在必要的时候使用ROW记录，其他时候使用STATEMENT记录。
浙公网安备 33010602011771号