MySQL数据库文件结构

初始化选项文件：

对于UNIX/Linux平台，MySQL程序默认会按照如下顺序扫描下列路径，并使用找到的第一个匹配的选项文件

/etc/my.cnf

/etc/mysql/my.cnf

SYSCONFDIR/my.cnf 通过CMake源码编译时指定的SYSCONFDIR参数指定的路径

$MYSQL_HOME/my.cnf

~/.my.cnf

本例中为

/data/mysqldata/3306/my.cnf

 

错误日志文件：

不仅仅是记录错误信息，MySQL服务进程即mysqld启动或关闭的信息也会被记录进来。错误日志文件是在启用mysqld时，通过log-error选项指定错误日志的路径及文件名

错误日志中记录的信息分为3类：Note，Warning，Error

Note对应的是正常的MySQL服务启动或关闭信息

Error对应的是错误的信息

Warning对应的是警告信息

对于Linux/UNIX系统，错误日志可以被写入到系统日志syslog中，在执行mysqld_safe命令启动MySQL服务时，可以附加--syslog参数，使MySQL的日志信息输出到系统日志中

/data/mysqldata/3306/mysql-error.log

 

查询日志文件：

MySQL会自动将所有执行时间超过指定阈值的SQL语句都记录下来，保存在文件里，这个文件就是慢查询日志文件。

MySQL的查询日志有两种，一种是慢查询日志(Slow Query Log)。另一种是通用查询日志(General Query Log)，不仅仅记录执行慢的查询，而是所有执行的查询语句都会被记录下来

MySQL的查询日志不仅能记录到文件，还能自动保存到MySQL数据库中的表对象里

慢查询日志：就是所有查询语句执行时间超过系统变量long_query_time(默认值为10秒)指定的参数值，并且访问的记录数超过系统变量min_examined_row_limit(默认值为0条)的数量的语句。默认情况下慢查询日志功能是被禁止的，通过MySQL系统参数进行控制。通过慢查询日志调优SQL语句是项非常有效的手段

(system@localhost) [mysql]> set global slow_query_log=on;

(system@localhost) [mysql]> show global 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       | /data/mysqldata/3306/slow_query.log |

+---------------------------+-------------------+

slow_query_log指定是否输出慢查询日志，1为输出，0不输出，默认值为0

slow_query_log_file指定日志文件存储路径和文件名，默认保存在MySQL的data目录下

long_query_time指定慢查询执行时间的阀值

log_short_format用来控制输出到慢查询日志文件的信息，指定后，会减少输出信息

log_slow_admin_statements用来控制是否将一些执行时间较长的管理类语句，如OPTIMIZE TABLE、ANALYZE TABLE、ALTER TABLE语句输出到慢查询日志中

log_queries_not_using_indexes用来控制是否将未使用索引的语句输出到慢查询日志文件

log_throttle_queries_not_using_indexes一般会与log_queries_not_using_indexes参数组合使用，是控制每分钟输出到慢查询日志的未使用索引的记录条数，默认是0，表示不限制

log_slow_slave_statements复制环境专用的参数，用来控制是否将复制的查询语句输出到慢查询日志

/data/mysqldata/3306/slow_query.log

通过慢查询日志调优SQL语句是非常有效的手段

慢查询日志中语句记录和顺序可能跟执行顺序不同

普通查询日志：能够记录mysqld进程所做的几乎所有操作，不仅仅是客户端发出的sql语句会被记录到普通查询日志中，对于数据库或对象的管理操作也会记录下来，甚至连客户端连接或断开连接，服务器都会向文件中写入相应信息。最大的功能是审计，通过浏览日志文件中的信息，可以了解客户端都做了什么。通过MySQL系统参数进行控制。DBA怀疑客户端执行的操作有问题，就可以通过普通查询日志确定客户端究竟执行的是什么

(system@localhost) [mysql]> show global variables like '%general%';

+------------------+----------------------+

| Variable_name    | Value                |

+------------------+----------------------+

| general_log      | OFF                  |

| general_log_file |  /data/mysqldata/3306/general_query.log |

+------------------+----------------------+

general_log是否开启，0表示禁用，1表示启用

general_log_file指定日志文件存储路径和文件名，默认情况下，保存在data目录下，默认为[host_name].log

可以在MySQL服务运行时动态进行修改，而不需要重启MySQL服务。

禁用或启用普通全局查询日志：

SET GLOBAL general_log=’OFF’;

SET GLOBAL general_log=’ON’;

禁用或启用某个会话产生的普通查询日志：

SET sql_log_off=’OFF’;

SET sql_log_off=’ON’;

不要长期启用普通查询日志

普通查询日志文件中出现的顺序，是按照mysqld接收的顺序记录的

配置查询日志：

MySQL服务启动时配置指定--log-output选项：TABLE，FILE，NONE

--log-output=TABLE,FILE --general_log

--log-output=TABLE --general_log --slow_query_log

仅启用普通查询日志，并记录到日志文件和日志表

--log-output=TABLE,FILE --general_log

启用普通查询日志和慢查询日志，日志记录到数据库中的日志表

--log-output=TABLE --general_log --slow_query_log

--log-output=FILE --slow_query_log

--log_output=FILE --slow_query_log --slow_query_log_file=/data/mysql/logs/slow.log

MySQL服务运行中实时修改

 

二进制日志文件：

二进制日志binary log：记录数据库中的修改事件

二进制日志文件binary log file：保存数据库中修改事件的文件

普通查询日志是文本格式文件，为用户实际执行的操作，而二进制日志文件，记录的是数据库实际执行的操作。

通过二进制日志，能够实现两个重要功能：

用户复制，将MySQL Master端的二进制日志发送至Slave端，Slave端即可根据二进制日志中的内容，在本地重做，以达到主从同步的目的

用于恢复，当使用了备份恢复了数据库后，通过应用二进制日志文件，能够实现将数据恢复到故障发生前的状态

默认是不启用二进制日志文件功能，需要在启动MySQL服务时附加--log-bin选项，用来控制MySQL服务端要将数据库的修改操作写入二进制日志文件

在my.cnf中为bin-log选项

[mysql@mysql1 binlog]$ pwd

/data/mysqldata/3306/binlog

[mysql@mysql1 binlog]$ ll

total 1300

-rw-rw----. 1 mysql mysql   67095 Jul  2 23:47 mysql-bin.000001

-rw-rw----. 1 mysql mysql 1216094 Jul  2 23:47 mysql-bin.000002

-rw-rw----. 1 mysql mysql     143 Jul  2 23:53 mysql-bin.000003

-rw-rw----. 1 mysql mysql     894 Jul  3 09:06 mysql-bin.000004

-rw-rw----. 1 mysql mysql    1066 Jul  3 14:49 mysql-bin.000005

-rw-rw----. 1 mysql mysql     436 Jul  3 14:53 mysql-bin.000006

-rw-rw----. 1 mysql mysql     143 Jul  3 17:03 mysql-bin.000007

-rw-rw----. 1 mysql mysql    2892 Jul  4 14:44 mysql-bin.000008

-rw-rw----. 1 mysql mysql     143 Jul  5 10:36 mysql-bin.000009

-rw-rw----. 1 mysql mysql     143 Jul  5 11:18 mysql-bin.000010

-rw-rw----. 1 mysql mysql     143 Jul  7 09:57 mysql-bin.000011

-rw-rw----. 1 mysql mysql     120 Jul  7 09:57 mysql-bin.000012

-rw-rw----. 1 mysql mysql     540 Jul  7 09:57 mysql-bin.index

mysqld进程会自动附加日志序列号到生成的二进制日志文件，最大空间是由系统变量max_binlog_size进行控制，日志文件有可能超过max_binlog_size参数指定的值。为了能够跟踪二进制文件的状态，MySQL服务会创建一个与二进制日志文件同名(扩展名为.index)的二进制日志索引文件，可以通过log-bin-index选项指定文件名和路径

RESET MASTER用来清空所有二进制日志文件，而PURGE BINARY LOGS语句可以用来删除指定的某个或某些日志文件。为安全起见，建议删除前先备份

对于非事务表来说，语句执行后就会立刻写入二进制日志中。对于事务表，则要等到当前没有任何锁定或未提交的信息才会写入二进制日志，以此来确保日志被记录的始终是其执行的顺序。对于暂未提交的事务，事务中的更新操作(update,delte,insert支持事务的表对象)会被缓存起来，直到收到COMMIT语句，而后，mysqld进程就会将整个事务在COMMIT执行前全部写到二进制日志。当线程开始处理事务时，它会按照binlog_cache_size系统变量指定的值分配内存空间，缓存SQL语句，如果语句所需要的空间比分配的缓存区要大，那么线程将打开一个临时文件保存这个事务，直到事务结束时再自动删除临时文件

二进制日志中记录事件的格式有3种：基于行格式记录(row-based logging)，基于语句记录(statement-based logging)，混合模式记录(mixed-based logging)

binlog_cache_use状态变量显示了使用binlog_cache_size系统变量的事务数

binlog_cache_disk_use状态变量显示了使用临时文件的事务数

这两个参数组合起来可用于binlog_cache_size系统变量设置的调整和优化，以尽可能避免使用磁盘临时文件

max_binlog_cache_size系统变量默认为 4GB，用来限制事务能够使用的最大缓存区

系统变量一般都是MySQL数据库中的系统配置，状态变量则是MySQL服务运行过程中的一些状态信息

查看系统变量执行：show [global] variables;

查看状态变量执行：show [global] status;

两个语句都支持附加like子句做细粒度的过滤

默认情况下二进制日志不是实时同步到磁盘，可以设置二进制日志同步到磁盘的频率，MySQL系统变量sync_binlog，设置为1(秒)安全级别最高，同时也是最慢的设置。MySQL初始化参数--innodb_support_xa设置为1，启用分布式事务的支持，确保二进制日志与InnoDB数据文件的同步。MySQL应被配置为以事务为单位同步二进制日志和InnoDB日志到磁盘。InnoDB日志默认即是同步状态，sync_binlog=1可以同步二进制日志。

 

中继日志及复制状态文件：

在复制过程中，Slave节点会创建若干文件，有些用于保存从Master节点接收到的二进制日志，有些用于记录当前复制环境的状态，还有些用于记录日志事件处理进度等相关信息。

中继日志relay log文件，用于保存读取到的Master二进制日志，由Slave节点的I/O线程负责数据的维护。类似Oracle的Standby Redologs

Master信息日志master.info文件，用于保存复制环境中连接Master节点的配置信息，比如说Slaves节点连接Master使用的用户名、密码、IP、端口等均在其中，在5.6之前保存在data目录下，5.6之后可以选择保存在mysql.slave_master_info表对象里

中继日志信息日志relay-log.info文件，保存处理进度及中继日志文件的位置，在5.6之前保存在data目录下的replay-log.info，5.6之后可以选择保存在mysql.slave_master_info表对象里

 

表对象数据文件：

frm文件：表对象的结构定义文件，只要是表对象一定会拥有这个文件，不管什么引擎

ibd文件：InnoDB引擎专用数据文件(含索引)

MYD文件：MyISAM引擎专用数据文件

MYI文件：MyISAM引擎专用索引文件

CSV文件：CSV数据文件

ARZ文件：ARCHIVE专用数据文件

 

进程id文件：

当MySQL服务启动后，在数据库目录下发现一个mysql.pid文件，保存的是当前MySQL服务的进程号

 

套接字文件：

在Linux/UNIX环境下，可以使用域套接字。只有当MySQL客户度和MySQL服务在同一台机器上时才能使用

show variables like ‘socket’;

 

自动配置文件：

 

从MySQL5.6开始，每个MySQL实例会拥有一个唯一的UUID，保存在数据根目录下的auto.cnf文件中，是由MySQL自动生成，不要尝试修改