第3章 文件
- 参数文件：告诉MySQL实例启动时在哪里找到数据库文件，并且指定某些初始化参数
- 日志文件：记录MySQL实例对某种条件做出响应时写入的文件
- 套接字文件：用UNIX域套接字方式进行连接时需要的文件
- pid文件：MySQL实例的进程ID文件
- MySQL表结构文件：存放MySQL表结构定义文件
- 存储引擎文件：存储引擎存储的记录和索引等数据
3.1 参数文件
- 当MySQL实例启动时，数据库会先读取一个配置参数文件
- 用来寻找数据库的各种文件所在位置以及指定某些初始化参数，参数通常定义了内存结构的大小
- 默认情况下，MySQL实例会按照一定的顺序在指定位置进行读取
- MySQL数据库参数文件的作用和Oracle数据库的参数文件类似
- Oracle实例在启动时若找不到参数文件，则不能进行装载操作
- MySQL实例可以不需要参数文件，这时所有的参数值取决于编译MySQL时指定的默认值和源代码中指定参数的默认值
- MySQL实例在默认的数据库目录下若找不到mysql架构，则会启动失败
- MySQL的mysql架构中记录了访问该实例的权限
- MySQL数据库的参数文件是以文本方式进行存储的，用户可以通过文本编辑进行参数的修改
3.1.1 什么是参数
- 数据库参数可以看作是一个键值对，键是参数，值是给参数设置的值
- Oracle数据库和SQL Server数据库存在隐藏参数以供“内部人士”使用，MySQL数据库则没有提供
- 在绝大多数情况下，数据库厂商不建议用户在生产环境中对其进行很大的调整
3.1.2 参数类型
- MySQL数据库中的参数分为2类
- 动态参数（dynamic）
- 在MySQL实例运行中可以进行修改
- 静态参数（static）
- 在整个实例生命周期内都不能修改
- 动态参数（dynamic）
- global和session关键字
- 表示该参数的修改是基于当前会话还是整个实例的生命周期
- 对全局变量的修改，仅在这次的实例生命周期内有效
- MySQL实例本身并不会对参数文件中的该值进行修改
- 即在下次启动时MySQL实例还是会读取参数文件
3.2 日志文件
3.2.1 错误日志
- 错误文件对MySQL的启动、运行、关闭过程进行了记录，该文件不仅记录了所有的错误信息，也记录一些警告信息或正确的信息，默认文件名为主机名.err
- MySQL DBA在遇到问题时应该首先查看该文件以便定位问题
- 当MySQL数据库不能正常启动时，第一个必须查找的文件就是错误日志文件
- 当MySQL数据库不能重启时，可以通过查看错误日志文件得到帮助
3.2.2 慢查询日志
- MySQL 5.1开始，慢查询日志记录可以存放在表中，慢查询表在mysql架构下，名为slow_log
- 错误日志可以得到关于数据库优化的信息，慢查询日志可以帮助DBA定位到可能存在问题的SQL语句，从而进行SQL语句层面的优化
- 默认情况下，MySQL数据库不启动慢查询日志
- 开启慢查询日志后，通过参数long_query_time在MySQL启动时设置一个阈值
- 将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中，默认值为10，代表10秒
- 即MySQL数据库会记录运行时间超过该值的所有SQL语句，运行时间正好等于阈值的情况不会被记录
- 从MySQL 5.1开始，该阈值以微秒记录SQL语句运行的时间，可以更精确的记录SQL运行时间
- MySQL提供参数log_queries_not_using_indexes
- 表示如果运行的SQL语句没有使用索引，则MySQL数据库会将这条SQL语句记录到慢查询日志文件
- MySQL 5.6.5版本新增参数log_throttle_queries_not_using_indexes
- 表示每分钟允许记录到slow log且未使用索引的SQL语句次数
- 若没有使用索引，此类SQL语句会频繁的被记录，从而导致slow log文件大小不断增加
- 随着MySQL数据库服务器运行时间的增加，可能会有越来越多的SQL查询被记录到慢查询日志中
- 可以通过参数log_output指定慢查询输出格式，默认为FILE，可设置为TABLE
- 该参数是动态的，全局的，用户可以在线进行修改
- MySQL的slow log通过运行时间来对SQL语句进行捕获
- InnoSQL版本加强了对于SQL语句的捕获方式，增加了对于逻辑读取和物理读取的统计
- 逻辑读取：包含所有的读取，不管是磁盘还是缓冲池
- 物理读取：指从磁盘进行IO读取的次数
- 通过参数log_query_io可以将超过指定逻辑IO次数的SQL语句进行记录，默认值为100
- 通过参数slow_query_type设置slow log启用方式
- 0表示不将SQL语句记录到slow log
- 1表示根据运行时间将SQL语句记录到slow log
- 2表示根据逻辑IO次数将SQL语句记录到slow log
- 3表示根据运行时间及逻辑IO次数将SQL语句记录到slow log
- InnoSQL版本加强了对于SQL语句的捕获方式，增加了对于逻辑读取和物理读取的统计
3.2.3 查询日志
- MySQL 5.1开始，查询日志的记录可以放入mysql架构的general_log表中，默认文件名为主机名.log
- 查询日志记录所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行
3.2.4 二进制日志
- 通过配置参数log-bin [=name]启动二进制日志
- 若不指定name，默认二进制日志文件名为主机名，后缀名为二进制日志的序列号
- .index是二进制的索引文件，用来存储过往产生的二进制日志序号
- 二进制日志记录了对MySQL数据库执行更改的所有操作
- 不包括SELECT和SHOW这类操作
- 若操作本身并没有导致数据库发生变化，该操作可能也会写入二进制日志
- 还记录了执行数据库更改操作的时间等其他额外信息
- 二进制日志主要作用
- 恢复
- 某些数据的恢复需要二进制日志
- 如数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复
- 复制
- 通过复制和执行二进制日志使一台远程MySQL数据库（slave）与一台MySQL数据库（master）进行实时同步
- 审计
- 用户可以通过二进制日志中的信息进行审计，判断是否有对数据库进行注入的攻击
- 恢复
- MySQL官方手册中测试数据表明
- 开启二进制日志会使性能下降1%，考虑到可以使用复制和point-in-time的恢复，性能损失可以接受
1.max_binlog_size
- 该参数指定了单个二进制日志文件的最大值
- 如果超过该值，则产生新的二进制日志文件，后缀名+1，并记录到.index文件
2.binlog_cache_size
- 当使用事务的表存储引擎时，所有未提交的二进制日志会被记录到一个缓存中
- 待该事务提交时直接将缓冲中的二进制日志写入二进制日志文件
- 该缓冲大小由该参数设定，默认大小为32K
- 该参数的设定是基于会话的，开始一个事务时，MySQL会自动分配缓存
- 当一个事务的记录大于设定的阈值时，MySQL会把缓冲中的日志写入一个临时文件
3.sync_binlog
- 默认情况下，二进制日志不是在每次写的时候就同步到磁盘
- 当数据库宕机时，可能会有一部分数据没有写入二进制日志文件中，会给恢复和复制带来问题
- 该参数表示每写缓冲多少次就同步到磁盘
- 若将N设为1，则表示采用不同写磁盘的方式来写二进制日志
- 即写操作不使用操作系统的缓冲来写二进制日志
- 该参数默认值为0
- 若使用InnoDB存储引擎进行复制，并且想得到最大的高可用性，则设置为ON
- 该值为ON时，会对数据库的IO系统带来一定的影响
- 即使该参数设置为1，还是会发生问题
- 已经写入了二进制文件，但是未提交，此时发生了宕机
- 在数据库下次启动时，由于COMMIT操作没有发生，这个事务会被回滚掉
- 二进制日志已经记录了该事务信息，不能被回滚
- 通过将参数innodb_support_xa设为1，可以确保二进制日志和InnoDB存储引擎数据文件的同步
4.binlog-do-do&binlog-ignore-db
- 参数表示需要写入或忽略写入哪些库的日志
- 默认为空，表示需要同步所有库的日志到二进制日志
5.log-slave-update
- 若当前数据库是复制中的slave角色
- 它不会将从master取得并执行的二进制日志写入自己的二进制日志文件中
- 若需要写入，则设置该参数
- 若需要搭建master=>slave=>slave构架的复制，则必须设置该参数
6.binlog_format
- 该参数会影响记录二进制日志的格式
- MySQL 5.1版本之前，没有这个参数，二进制文件是基于SQL语句级别的
- 基于这个格式的二进制日志文件的复制可能会导致主从服务器上表中数据不一致
- InnoDB存储引擎的默认事务隔离级别的是REPEATABLE READ，因为二进制日志文件格式的原因
- 若使用READ COMMITTED事务隔离级别，会出现类似丢失更新的现象，从而出现主从数据库上的数据不一致
- MySQL 5.1版本引入了该参数，有3个可设值
- STATEMENT
- 和原版MySQL一样，二进制日志文件记录的是日志的逻辑SQL语句
- ROW
- 记录表的行更改情况
- 该格式解决了STATEMENT格式下的复制问题
- 若该参数设置为ROW，则事务隔离可设置为READ COMMITTED，以获得更好的并发性
- MIXED
- 默认使用STATEMENT格式进行二进制日志文件的记录
- 在一些情况下使用ROW格式
- 表的存储引擎为NDB，对表的DML操作都以ROW格式记录
- 使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数
- 使用了INSERT DELAY语句
- 使用了用户定义函数（UDF）
- 使用了临时表（temporary table）
- STATEMENT
- 通常情况下，该参数设置为ROW
- 可以为数据库的恢复和复制带来更好的可靠性
- 二进制文件的大小会增加，对磁盘空间要求有增加
- 由于复制是采用传输二进制日志方式实现的，复制的网络开销也会增加
- 二进制文件的文件格式为二进制
- 不能像错误日志文件、慢查询日志文件用命令查看内容
- 必须通过MySQL提供的工具mysqlbinlog查看
3.3 套接字文件
- 一般在/tmp目录下，名为mysql.sock
- UNIX系统下本地连接MySQL可以采用UNIX域套接字方式，需要套接字（socket）文件
- 套接字文件可由参数socket控制
3.4 pid文件
- 位于数据库目录下，名为主机名.pid
- 当MySQL实例启动时，会将进程ID写入pid文件
- 该文件可由参数pid_file控制
3.5 表结构定义文件
- 因为MySQL插件式存储引擎体系结构的关系，MySQL数据的存储时根据表进行的，每个表都会有与之对应的文件
- 不论采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义
- 该文件还用来存放视图的定义
3.6 InnoDB存储引擎文件
3.6.1 表空间文件
- InnoDB采用将存储的数据按表空间进行存放的设计
- 在默认配置下会有一个初始大小为10MB，名为ibdata1的文件，该文件可以自动增长
- 该文件就是默认的表空间文件，可以通过多个文件组成一个表空间
- 参数innodb_data_file_path可以进行设置，并制定文件的属性
- 所有基于InnoDB存储引擎的表的数据都会记录到该共享表空间中
- 将两个文件用来组成表空间，若这两个文件位于不同的磁盘上，磁盘的负载可能被平均，可以提高数据库的整体性能
- 参数innodb_file_per_table设置后
- 用户可以将每个基于InnoDB存储引擎的表产生一个独立表空间，独立表空间的命名规则为表明.idb
- 独立表空间仅存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的表空间中
3.6.2 重做日志文件
1.概述
- 默认情况下，InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件，即重做日志文件，记录了对于InnoDB存储引擎的事务日志
- 当实例或介质失败时，InnoDB存储引擎会使用重做日志恢复到之前的数据，以此保证数据的完整性
- 每个InnoDB存储引擎至少有1个重做日志文件组，每个文件组下至少有2个重做日志文件
- 为了得到更高的可靠性，可以设置多个镜像日志组，将不同的文件组放在不同的磁盘
- 在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行
- 参数
- innodb_log_file_size：指定每个重做日志文件的大小
- innodb_log_files_in_group：指定日志文件组中重做日志文件的数量，默认为2
- innodb_mirrored_log_groups：指定日志镜像文件组的数量，默认为1，表示只有一个日志文件组，没有镜像
- innodb_log_group_home_dir：指定日志文件组所在路径，默认为./，表示在MySQL数据库的数据目录下
- 重做日志文件的大小设置对于InnoDB存储引擎的性能有很大的影响
- 若设置很大，在恢复时可能需要很长时间
- 若设置太小，可能导致一个事务的日志需要多次切换重做日志文件，也会导致频繁的发生async checkpoint
2.二进制日志文件和重做日志文件的区别
- 日志记录不同
- 二进制日志文件会记录所有与MySQL数据库有关的日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志
- 重做日志文件只记录有关该存储引擎本身的事务日志
- 记录内容不同
- 二进制日志文件记录的是关于一个事务的具体操作内容，即该日志的逻辑日志
- 重做日志文件记录的是关于每个页的更改的物理情况
- 写入时间不同
- 二进制日志文件仅在事务提交前进行提交，即只写磁盘一次，不论该事务大小
- 重做日志文件是在事务进行中，不断有重写日志条目被写入到重做日志文件中
3.重做日志格式
- InnoDB存储引擎中，对于各种不同的操作有着不同的重做日志格式
- 到InnoDB 1.2.x版本为止，总共定义了51种重做日志类型，但它们有着基本相同格式
- redo_log_type：表示重做日志类型（1个字节）
- space：表示表空间的ID（小于4个字节）
- page_no：表示页的偏移量
- redo_log_body：表示每个重做日志的数据部分，恢复时需要调用相应的函数进行解析
4.重做日志文件的写入
- 重做日志文件先写入一个重做日志缓冲中，然后按照一定的条件顺序的写入日志文件
- 从重做日志缓冲往磁盘写入时，是按512个字节，即一个扇区的大小进行写入
- 扇区是写入的最小单位，可以保证写入必定成功的，因此重做日志的写入过程中不需要doublewrite
- 重做日志写入条件
- 主线程每秒或每10秒会将重做日志缓冲写入磁盘的重做日志文件中，不论事务是否已提交
- 设置参数innodb_flush_log_at_trx_commit，表示在提交操作时，处理重做日志的方式
- 0代表不将事务的重做日志写入磁盘上的日志文件，而是等待主线程每秒的刷新
- 1代表将重做日志缓冲同步写到磁盘，即伴有fsync的调用
- 2代表将重做日志异步写到磁盘，即写到文件系统的缓存中
- 因此不能保证执行commit时肯定会写入重做日志文件，只是有这个动作发生
- 为了保证事务的ACID中的持久性
- 必须将该参数设置为1，当发生宕机时，可以通过重做日志文件恢复，并保证可以恢复已经提交的事务
- 若参数设置0或2，可能发生恢复时部分事务的丢失
- 设置为2时，若只是数据库发生宕机而操作系统及服务器没有发生宕机时
- 此时为写入磁盘的事务日志保存在文件系统缓存中，恢复时同样能保证数据不丢失
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
