第3章 文件
3.1 参数文件
mysql 数据库的参数文件是以文本的形式进行存储的,可以用vim进行修改 my.ini my.conf等
3.1.1 什么是参数
SHOW VARIABLES 可以查看mysql 配置参数
3.1.2 参数类型
- 动态参数 当前会话有效
- 静态参数 readonly
3.2 日志文件
- 错误日志(error log)
- 二进制日志(bin log)
- 慢查询日志(slow query log)
- 查询日志(log)
3.2.1 错误日志
SHOW VARIABLES LIKE 'log_error'\G;
3.2.2 慢查询日志
SHOW VARIABLES LIKE 'long_query_time'\G;
MySQL 慢查询的相关参数解释:slow_query_log :是否开启慢查询日志,1表示开启,0表示关闭。
slow_query_log :是否开启慢查询日志,1表示开启,0表示关闭。
log-slow-queries :旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
long_query_time :慢查询阈值,当查询时间多于设定的阈值时,记录日志。
log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。
log_output:日志存储方式。log_output='FILE'表示将日志存入文件,默认值是'FILE'。log_output='TABLE'表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。MySQL数据<br>库支持同时两种日志存储方式,配置的时候以逗号隔开即可,如:log_output='FILE,TABLE'。日志记录到系统的专用日志表中,要比记录到文件耗费更多的系统资源,因此对于需要启用慢查询日志,又需<br>要能够获得更高的系统性能,那么建议优先记录到文件。
慢查询日志配置
默认情况下slow_query_log的值为OFF,表示慢查询日志是禁用的,可以通过设置slow_query_log的值来开启,如下所示:
mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+------------------------------------------+
2 rows in set (0.00 sec)
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+------------------------------------------+
2 rows in set (0.00 sec)
#使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效,MySQL重启后则会失效。如果要永久生效,就必须修改配置文件my.cnf(其它系统变量也是如此)
-s, 是表示按照何种方式排序
c: 访问计数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-t, 是top n的意思,即为返回前面多少条的数据;
-g, 后边可以写一个正则匹配模式,大小写不敏感的;
比如:
得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
3.2.3 查询日志
默认文件名: 主机名.log
3.2.4 二进制日志
二进制日志 记录了对Mysql数据库执行更改的所有操作,但是不包括SELECT 和 SHOW ;
格式:
- STATMENT
- ROW
- MIXED
二进制日志的作用
- 恢复 可以用binlog 实现point-in-time 恢复
- 复制 主库从库之间进行实时同步
- 审计 作为审计日志 查看是否被注入攻击
3.3 套接字文件
本地连接mysql 可以使用unix域套接字文件 一般在/tmp/mysql.sock
3.4 pid文件
进程号文件
3.5 表结构定义文件
frm 文件 描述表结构的定义
3.6 InnoDB 存储引擎文件
3.6.1 表空间文件
innodb 采用表空间设计(tablespace) 在默认配置下 又一个初始大小为10MB的 ibdata1的文件 可以指定多个文件组成一个表空间。
多个磁盘的文件组成表空间 可以分担负载。所有基于innodb引擎的表数据 都会记录到共享空间中。如果开启了innodb_file_per_table 会层层一个独立的表空间,表名.ibd
独立的表空间里面只存储该表的数据、索引、插入缓冲、BITMAP等信息,其他信息存放在共享表空间中。
3.6.2 重做日志
在默认情况下,innodb存储引擎目录下 有ib_logfile0 和 ib_logfile1的文件,这就是重做日志。每个innodb引擎 至少有一组重做日志,其中有两个重做日志文件,可以放在不同的磁盘上 保证高可用,每个重做日志文件大小一致,并采用循环写入的方式,先写日志文件1,达到文件末尾的时候,切换到日志文件2,如果日志文件2也被写满,再切回到日志文件1
重做日志的大小非常重要,不能设置的太大,设设置的太大,恢复需要很长的时间,太小也不行,太小了导致一个事务日志来回切换文件,并且频繁发生async checkpoint,导致性能抖动。重做日志里面有一个capacity变量,最后检查点不能超过这个阀值,否则必须把缓冲池里面的脏页列表写回到磁盘里面,这个会阻塞用户线程。
和binlog 的区别 , binlog不区分存储引擎,redolog 仅限于innodb , 其次 记录的内容也不一样 innodb 不管statment row mixed 记录的都是逻辑日志,而redo log 记录的是页的物理变化。还有binlog 只写一次,但是redo log 在事务中不断写入。
redolog 格式: redo_log_type+ space +page_no + redolog_body
为了保证事务的ACID的持久性,必须将innodb_flush_log_at_trx_commit=1 , 也就是在事务提交的时候,必须确保写入重做日志,如果宕机,就可以通过redo log 恢复。