MySQL数据库文件

分类 Mysql 标签 Mysql 评论:
  1. MySQL数据库文件
    1. 1、参数文件
    2. 2、日志文件
      1. 2.1、错误日志文件 （error log）
      2. 2.2、二进制日志文件 （bin log）
      3. 2.3、慢查询日志文件 （slow query log）
      4. 2.4、查询日志文件 （query log）
      5. 2.5、中继日志（relay log）
    3. 3、socket文件
    4. 4、pid文件
    5. 5、MySQL表结构文件
    6. 6、innodb存储引擎文件
      1. 6.1、表空间文件
      2. 6.2、重做日志文件（redo log）
      3. 6.3、回滚日志文件（undo log）

MySQL数据库文件

1、参数文件

告诉数据库启动实例的时候在哪里可以找到数据库文件，并指定初始化参数；
通过mysql –help | grep my.cnf来寻找即可

2、日志文件

记录mysql实例对对某种条件作出响应时写入的文件，如错误日志文件，二进制日志文件，慢查询日志文件，查询日志文件等；
注意：redo log 和undo log不在这个范围，因为仅支持事务的存储引擎才有redo log和undo log

2.1、错误日志文件 （error log）

对mysql的启动、运行、关闭进行了记录；show variables like ‘%log_error%’ 来查看错误日志文件的路径；

mysql> show variables like '%log_error%';
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| binlog_error_action | ABORT_SERVER                |
| log_error           | /usr/local/mysql/mysqld.err |
| log_error_verbosity | 3                           |
+---------------------+-----------------------------+
3 rows in set (0.00 sec)

2.2、二进制日志文件 （bin log）

bin log记录了对mysql数据库执行更改的所有操作，但不包括select和show操作，因为这两个操作对数据本身并没有修改。bin log有下面几个作用：

  • 恢复：对数据库全备恢复后，还需要恢复二进制日志来保证数据的完整；
  • 复制：主从复制，通过bin log来进行数据同步；
  • 审计：通过对二进制日志中的信息来进行审计，判断是否有sql注入攻击；

bin log默认没有开启。需要手动开启，官方解释开启bin log会使性能下降1%，但是作用巨大，性能损失可以忽略；

相关参数：

  • max_binlog_size：指定单个二进制文件最大值，默认1G；
  • binlog_cache_size：指定二进制缓冲区大小，默认32K，其是针对会话（session）的，就是说，当一个线程开始一个事务的时候，都会自动分配一个大小为binlog_cache_size的内存，因此注意该值不能设置过大；通过binlog_cache_use和binlog_cache_disk_use的状态，可以判断是否设置合适，binlog_cache_use记录了使用缓冲写二进制日志的次数，binlog_cache_disk_use记录了临时文件写二进制的次数，该值如果为0，则表示binlog_cache_size大小够用，否则需要适当的增加大小；
  • sync_binlog = [N] 表示每写缓冲多少次就刷新到磁盘，1是最安全的值，但也是最慢的；
    sync_binlog 参数决定bin log刷新机制
    1：实时刷新（默认）
    0：交由操作系统管理
    （2，3，4……n）：n个事务刷新一次
  • binlog-do-db 哪些库写入二进制文件
  • binlog-ignore-db 忽略哪些库写入二进制文件
  • log-slave-update 如果是从库，需要设置该参数来讲主库的二进制文件写入自己的二进制文件中去
    binlog_format
    • STATEMENT：记录的是逻辑SQL语句
    • ROW：记录表行的更改情况，日志文件会增大，对磁盘空间要求有一定增加；
    • MIXED：混合使用，默认还是使用STATEMENT，但是某些情况下会使用ROW，比如
      1）表的存储引擎为NDB，这时对表的DML语句都是使用ROW；
      2）使用了UUID()、 USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数；
      3）使用了INSERT DELAY语句；
      4）使用了用户定义函数（UDF）；
      5）使用了临时表（temporary table）；

二进制文件是二进制的，需要使用mysqlbinlog工具辅助阅读；

mysqlbinlog --no-defaults -v -v --base64-output=decode-row /data/binlog > 1.log

2.3、慢查询日志文件 （slow query log）

默认情况下，mysql不启动慢查询日志，需要手动开启；

mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_query_log      | OFF                         |
| slow_query_log_file | /data/mysql/lyucan-slow.log |
+---------------------+-----------------------------+
2 rows in set (0.01 sec)

相关参数：

  • long_query_time：设置慢查询阈值，当超过这个阈值的时候，记录到满查询日志，供DBA进行查询优化，默认为10s；
  • long_query_not_using_indexes：参数控制是否将没有使用索引的sql语句记录到慢查询日志里面；
  • log_throttle_queries_not_using_indexes：5.6以后引入了一个参数来表示每分钟记录到慢查询日志的且未使用索引的sql语句数量，默认值为0，表示没有限制；
  • slow_query_type来表示启用slow log的方式，可以为：
    0：表示不将sql语句记录到slow log
    1：根据运行时间将sql记录到slow log
    2：根据逻辑IO次数将sql记录到slow log
    3：同事根据运行时间和逻辑IO次数将sql记录到slow log

可以使用mysqldumpslow命令来辅助分析慢查询日志；

2.4、查询日志文件 （query log）

对未能正确查询的sql语句，进行了记录

2.5、中继日志（relay log）

3、socket文件

使用unix套接字连接时需要的文件；

4、pid文件

mysql实例启动的进程文件；

5、MySQL表结构文件

存放mysql表结构定义的文件，.frm后缀；
每张表都会有一个.frm的文件，存放该表的表结构定义，同时还用来存放视图，如果创建了一个v_a视图，那就会对应产生一个 v_a.frm文件，用来存放视图的定义，该文件可以使用cat查看；

6、innodb存储引擎文件

因为mysql存储引擎是基于表的，每个存储引擎都会有自己的文件来保存各种数据；

6.1、表空间文件

innodb默认情况下有一个表空间文件，名为ibdata1，用户可以通过参数innodb_data_file_path对其进行设置，格式为：innodb_data_file_path = datafile_spec1[;datafile_spec2]…

mysql> show variables like '%innodb_data_file%';
+-----------------------+------------------------+
| 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;/db/ivdata2:2000M:autoextend

若两个文件存放在不同的磁盘上，磁盘的负载可以被均衡，提高数据库的在整体性能；
设置innodb_data_file_path参数后，说有基于innodb的存储引擎的表的数据都会存放在该表空间中
如果设置了innodb_file_per_table参数，则用户基于每个innodb存储引擎的表都会产生一个独立的表空间，独立表空间的命名规则为：表名.ibd；

6.2、重做日志文件（redo log）

默认情况下，数据目录下会有两个redo log 文件，ib_logfile0 和 ib_logfile1，每个InnoDB存储引擎至少有1个redo log文件组，每个文件组下至少有2个redo log文件。为了得到更高的可靠性，可以设置多个mirrored log group，将不同的文件组放在不同的磁盘上。日志组中每个redo log文件的大小一致，并以循环方式使用。InnoDB存储引擎先写redo log文件1，当达到文件的最后是，会切换至文件2，当文件2也被写满时，会再切换到文件1中。

相关参数：

  • innodb_log_file_size：参数指定了redo log文件的大小；
  • innodb_log_files_in_group：参数指定了redo log文件组中redo log文件的数量，默认为2；
  • innodb_mirrored_log_groups：参数指定了日志镜像文件组的数量，默认为1，代表只有一个文件组，没有镜像；
  • innodb_log_group_home_dir：参数指定了日志文件组所在路径，默认在数据库路径下；
  • innodb_flush_log_at_trx_commit 参数决定redo log的刷新机制
    0：每隔1秒刷新一次
    1：实时刷新（默认）建议设置为1
    2：交由操作系统管理
    重做日志不需要二次写，因为重做日志的写入是以扇区为单位的，512字节，扇区是写入的最小单位，因此可以保证每次写都是成功的，所以不需要二次写；

6.3、回滚日志文件（undo log）

当我们对数据进行操作的时候，就会产生undo记录，Undo记录默认记录在系统表空间（ibdata）中，从MySQL 5.6开始，Undo使用的表空间可以分离为独立的Undo log文件。

mysql> show variables like '%undo_table%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_undo_tablespaces | 0     |
+-------------------------+-------+
1 row in set (0.01 sec)

0表示使用系统默认的表空间，大于1表示使用独立的undo log文件，等于几就有几个文件；
相关参数：

mysql> show variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_truncate | OFF        |
| innodb_undo_logs         | 128        |
| innodb_undo_tablespaces  | 0          |
+--------------------------+------------+
5 rows in set (0.00 sec)

innodb_max_undo_log_size：控制最大undo tablespace 文件的大小
其他参数保持默认即可，没必要把表空间文件独立出来；

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 289211569@qq.com

©2016-2023 Daryl