MySql各种文件及参数
参数文件
MySql实例启动时,数据库会去读一个配置参数文件,用来寻找数据库的各种文件所在位置以及指定某些初始化参数,这些参数通常定义了内存结构有多大等信息。
数据库的参数可以看成键值对。
可以通过show variables like ‘要看的参数%’\G;来查看想看的参数。支持模糊查询用%,例如:show variables like ‘innodb_buffer%’\G;
MySql数据库的参数可以分为两类:
1.动态参数:可以在数据库实例运行中修改
2.静态参数:在实例的生命周期内不得进行修改
参数的范围有:当前会话session和整个实例生命周期global
可以通过set命令修改动态参数,例如:
Set @@global.read_buffer_size=102400;
Set @@session.read_buffer_size=102400;
查看命令如下:
Select @@global.read_buffer_size\G;
Select @@session.read_buffer_size\G;
日志文件
包括错误日志、二进制日志、慢查询日志、查询日志。
1.错误日志:对MySql的启动、运行、关闭过程进行了记录。
通过命令show variables like ‘log_error’,来找到文件。
2.慢查询日志:可以定位存在问题的SQL语句,进行优化。可以通过设定一个查询时间的阀值,超过该阀值的都会记录到慢查询日志文件中。该阀值可以通过参数long_query_time来设置,默认值是10秒。Show variables like ‘long_query_time’\G;如果慢查询日志太大,可以通过命令mysqldumpslow来查看日志文件。例如
mysqldumpslow ***-slow.log;
如果想查看执行时间最长的10条sql,则:
Mysqldumpslow –s al –n 10 ***.log
比较新的mysql版本还支持把慢查询插入库表slow_log中。
参数log_output可以设置为FILE和TABLE,
Set global.log_output=’TABLE’;
Select * from mysql.slow_log\G;可以查询出慢查询。
3.查询日志
记录了所有对MySql数据库请求的信息
4.二进制日志
记录了对MySql数据库执行更改的所有操作。不包含select和show等操作。二进制文件的主要作用:
恢复:数据库全备文件恢复后,利用二进制文件进行point-in-time的恢复
复制:使远端的从库跟主库数据保持一致
审计:可用于查看是否有攻击
max_binlog_size:用于设置二进制文件的大小,默认1G,超过后后缀自动+1
binlog_cache_size:当使用事务时,所有未提交的将会存在缓存中,这个值可以用于设定缓存大小。(可以通过查看binlog_cache_use、binlog_cache_disk_use来确定缓存大小,show global status like 'binlog_cache%';)
sync_binlog:设置参数sync_binlog=[N]来实现每写缓存N次就同步到磁盘(N=1时采取同步方式同步磁盘)。如果N=1,则会出现当采取事务方式执行时,如果事务执行一半发生了宕机,事务回滚,但二进制文件会记录且不会回滚。此问题可以通过设置innodb_support_xa来解决。
binlog-do-db:设置记录哪些库的日志
binlog-ignore-db:设置不记录哪些库的日志
log-slave-update:如果搭建master=>slave=>slave架构,则设置该参数实现从master获取二进制日志文件写入自己的二进制日志文件中。
binlog_format:设置二进制日志文件的格式。可以取值:statement、row、mixed。如果设置为statement,二进制文件记录的格式是sql语句;如果设置为row,记录的是表的行更改情况;如果设置为mixed,则默认采用statement模式,特殊情况下采用row。如果设置为row,会对磁盘空间要求有一定的增加,并且由于复制是采用传输二进制日志的方式实现的,因此复制的网络开销也会相应增加。
pid文件
mysql实例启动后,会将自己的进程ID记录到一个文件中。通过以下命令查看文件地址:
show variables like ‘pid_file’\G;
表结构定义文件
每张表都会对应一个frm后缀的文件,里面存储库表的结构定义信息。
InnoDB存储引擎文件
InnoDB存储引擎文件包括:表空间文件和重做日志文件
InnoDB采用将存储的数据按表空间进行存放的设计,默认有个初始大小10MB的ibdata1的表空间文件。可以通过innodb_data_file_path设置。
也可以设置每张表一个表空间,设置参数innodb_file_per_table来实现。
在默认情况下,数据库目录下会有名为ib_logfile0和ib_logfile1的两个文件,这俩文件就是重做日志文件。当实例或介质失败时,重做日志文件就可以起作用了。例如断电导致数据库实例失败,可以通过重做日志恢复到断电前。
参数innodb_flush_log_at_trx_commit可以取值0、1、2
0代表当提交事务时,并不将事务的的重做日志写入磁盘的日志文件,而是等待主线程每秒的刷新;
1表示在执行commit时将重做日志缓冲同步写到磁盘
2表示将重做日志异步写到磁盘,写到文件系统的缓存中。
因此如果保证数据库的ACID特性,需要设置innodb_flush_log_at_trx_commit=1。