（3.12）mysql基础深入——mysql日志文件/其他文件（socket/pid/表结构/Innodb）
关键词：mysql日志文件，mysqldumpslow分析工具
目录：日志文件的分类
1、错误日志（error log）
2、二进制日志（binlog）：mysqlbinlog分析查看工具
3、慢查询日志（show query log）：mysqldumpslow分析工具
4、通用日志（general log）
5、重做日志（redo log）
6、中继日志（relay log）
7、DDL日志（ddl log）
8、其他文件（socket/pid/表结构/Innodb）
详情：
1、错误日志（error log）
【1.1】查看错误日志是否启用
mysql> show variables like 'log_err%';
【1.2】配置文件设置
[mysqld] log-error=[path/[file_name]]
【1.3】重建错误日志文件（如果日志文件不存在，mysql启动或者执行flush logs时会创建新的日志文件）
（1）mysqladmin -u root -p flush-logs
（2）mysql> flush logs;
【1】概念：mysql启动、运行、关闭过程的记录，记录错误、警告、正常的信息。-- 相当于oracle的alert日志
【2】参数查看：show variables like '%log_error%';
解析：log_error_verbosity ：（1）只记录错误日志 （2）记录错误+警告信息 （3）记录错误+警告+正常信息
【3】linux系统异常日志：/var/log/messages
2、二进制日志（binlog）
【2.1】# 查看二进制文件
mysql> show variables like 'log_bin%'; #查看是否启用
mysql> show binary logs; #查看二进制文件个数及名称
【2.2】配置文件设置
[mysqld] log-bin = PATH/[FILENAME] #这里定义的只是一个索引文件，例如这里给/log/binlog，那么会生成binlog.index，而实际存放数据是类似于binlog.00001 之类的，超出范围会继续新建binlog.00002，以此类推 expire_logs_days=10 #清除binlog文件中超过10天的内容，默认值为0，表示“没有自动删除”。当mysql启动或刷新二进制日志时可能删除该文件 max_binlog_size=100M #单个binlog文件的最大容量，如果有大事务运行，运行到一半binlog到达最大值了这个时候不会立马新建，而是会持续写到这个文件里，所以有binlog超出该值也正常。
#常用实践：
[mysqld] server_id=3306 #服务ID，主从必须不一致。(建议数字：ip+端口) 5.7.3以后版本，必须加 #目录必须存在且授权好，binlog为索引文件，实际文件会自动根据索引文件建立如binlog.0000001 log_bin=/mysql/log/3306/binlog
sql_log_bin=on #开启binlog，如果用了log_bin配置，会自动开启 log_bin_index=/mysql/log/3306/binlog.index #不设置的话，会根据log_bin值名称自动生成binlog.index binlog_format='row' #(row,statement,mixed) #3种模式 binlog_rows_query_log_events=on #打开才能查看详细记录
expire_logs_days=10 #超过10天的数据会被认定为过期，且会被清理
max_binlog_size=100M #表示单个二进制文件的最大值为100M，如果有大事务可能会超出最大值
binlog_row_image=full #（full,minimal,noblob），分别表示binlog中内容全记录，只记录被操作的，和不记录二进制
【2.2.1】flush disk相关的
写binlog流程如下：
# 数据操作buffer pool > binlog buffer > file system buffer > commit > binlog file
在写binlog file之前，commit有3种模式，分别是：0,1，N
sync_binlog=0:mysql不会主动同步Binlog内容到磁盘文件中，而是依赖操作系统刷新文件的机会刷binlog file.一般是1S/次
sync_binlog=1:默认值，mysql主动刷新file system buffer到磁盘上的binlog file中，每1次commit，就主动fsync一次。
sync_binlog=N:非0非1，mysql主动刷新file system buffer到磁盘上的binlog file中，每N次commit，就主动fsync一次。
【2.2.2】数据库先写redo log还是先写binlog?
答案：先写redo LOG，再写binlog。如果2个有任一失败，就会回滚。
sync_binlog配合另一个参数innodb_flush_log_at_trx_commit;
如果都是1，数据库挂了以后，最多只丢一条一句或一个事务的数据；
show variables like 'innodb_flush_log_at_trx_commit';
但会影响性能，只能说在数据要求非常高的场景下使用。
【2.3】删除二进制文件
【2.3.1】一般形式
PURGE MASTER LOGS;
PURGE {MASTER | BINARY} LOGS TO 'log_name' #删除log_name 之前建立的文件，不包含该文件
PURGE {MASTER | BINARY} LOGS BEFORE 'date' #删除某个时间之前的所有文件内容，不包含该天
【2.3.2】删除所有二进制文件
RESET MASTER; #执行该语句，所有二进制日志将被删除，mysql 会重新创建二进制日志，新的日志文件扩展名将重新从000001开始编号
【2.4】阅读查看二进制文件
mysqlbinlog /log/binlog.00001 > /tmp/binlog1.log
【2.5】binlog恢复数据
mysqlbinlog恢复数据的语法如下： mysqlbinlog [option] filename |mysql -uuser -ppass option是一些可选项，filename是日志文件名 比较重要的两对option参数是 --start-datetime、--stop-datetime --start-position、--stop--position --start-date、--stop-date可以指定恢复数据库的起始时间点和结束时间点 --start-position、--stop--position可以指定恢复数据的开始位置和结束位置 使用mysqlbinlog恢复mysql数据库到2014年7月2日15：27：48时的状态，执行下面命令 mysqlbinlog --stop-datetime="2014-7-2 15:27:48 " D:mysqlloginloginlog.000008 |mysql -u user -p password 该命令执行成功后，会根据binlog.000008日志文件恢复2014年7月2日15：27：48前的所有操作。 这种方法对误操作的删除数据比较有效
【1】概念：记录数据库发生更改的SQL语句，以二进制方式保存在磁盘中。--相当于Oracle的归档日志
【2】作用：备份恢复、复制、审计
【3】特点：
（1）记录是SQL语句的形式
（2）commit提交的时候才写binlog，提交之前写binlog_buffer，提交时才回写到binlog日志文件。
binlog不会被覆盖，会一直存在（但可以设置保留多场时间的数据，可清多少天之前的数据清理）
（3）对所有表起作用
【4】查看：mysqlbinlog -vv [binlog_filename]
3、慢查询日志（slow query log）
【3.1】查看慢查询日志路径与开启 show variables like 'slow_query%'; 【3.2】配置文件参数（下面选其一） [mysqld] log-slow-queries=[path/[filename]] #开启慢查询并指定日志文件 long_query_time=n #超过n秒的查询记录到慢查询日志中，为0则记录所有查询 [mysqld] ###***logs long_query_time = 10 #慢查询判断时间/s,为0记录所有查询 slow_query_log = 1 #是否开启1开启0关闭 slow_query_log_file=/mysql/slow.log #开启后指定日志文件路径
【3.3】文件内容释义
#time:2019-03-20T00:14:20+08:00
#User@Host:root[root]@[10.10.10.11] ID:4
#Query_time:0.01 这条SQL执行总时间，locak_time:0.001，锁等待时间
#Row_sent:10 这条SQL返回给用户有几条数据 Rows_examined:109，这条SQL一共检查扫描处理了多少行数据。
【3.4】慢查询的原因
（1）lock_time锁等待时间太长 （2）examined处理的数据太多
【3.5】相关参数：
（1）log_queries_not_using_indexes：默认值off，当off时，表示如果使用了索引，就算慢，也不会记录日志。建议on
（2）log_throttle_queries_not_using_indexes:默认值0，即默认1分钟刷一次。表示每1分钟记录下所有未使用索引的SQL（5.6以后才有此参数）。建议10分钟以上
（3）log_output:默认是文件（FILE值），还有一个值是table
【3.6】如何分析查看是否有使用索引？
使用执行计划（1）desc select…… (2)explain select……
【3.7】慢查询日志分析工具（mysql自带）
mysqldumpslow /mysql/slow_query.log
使用 mysqldumpslow --help 查看使用方式
常用核心参数
【3.7.1】-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time 【3.7.2】-t NUM just show the top n queries 【3.7.3】-g PATTERN grep: only consider stmts that include this string
【3.7.4】
（1）举例取慢查询日志文件中锁定时间最长的10条SQL
mysqldumpslow -s al -t 10 /mysql/slow_query.log
（2）获取慢查询日志文件中平均用时最长的包括right join的10条SQL
mysqldumpslow -s at -t 10 -g "right join" /mysql/slow_query.log
【3.8】删除慢查询日志
（1）mysqladmin -u root -p flush logs （2）mysql> flush logs;
【3.9】注意事项
记住，慢查询日志只会记录已经查询完的SQL语句，正在执行的不会被记录；
【3.10】MySQL的slow log中Query_time包含了Lock_time吗？
首先先给出结论，Query_time包含了Lock_time
下面给出slow log的头部示例：
# Time: 2019-10-08T08:46:34.635823Z
# User@Host: root[root] @ localhost [] Id: 16
# Query_time: 0.064742 Lock_time: 0.000460 Rows_sent: 1 Rows_examined: 9997
其中：
1、Query_time为SQL的消耗时间
2、Lock_time为锁等待的时间，包括行锁、MDL锁等
3、是否记录slow log的判定条件为SQL的实际执行时间（Query_time - Lock_time）是否超过long_query_time，或者是否开启log_queries_not_using_indexes
4、通用日志（general log）
记录数据库的所有操作，任何情况下都不建议开启
【4.1】查看通用日志
mysql> show variables like '%general%'
【4.2】配置文件设置
[mysqld] log[=path/[filename]] #可以指定通用日志文件，也可以只写一个log（默认路径）
【4.3】查看内容
5、重做日志（redo log）（深入了解参考：https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html）
【5.1】作用
在mysql数据库挂了之后，通过redo log + 旧的数据块 进行数据前滚，再undo进行回滚保障数据一致性
【5.2】特点
（1）mysql数据库记录dml操作，redo log是循环的，能保障脏页没有写磁盘上时，对应的redo log不会被覆盖。
（2）mysql里的redo log只能用于崩溃恢复
（3）只针对innodb的表起作用
【5.3】log buffer => log file 触发机制
（1）强制每1s写一次。
（2）大于log buffer空间1/2的时候
（3）commit的时候
（4）log buffer写到1M的时候
（5）日志先写机制，后台的脏页写磁盘之前，就先把日志写过来。
innodb_flush_log_at_trx_commit =1 #innodb每次提交事务redo buffer 刷新到redo log
innodb_doublewrite =on #开启innodb特性“二次写”
【5.4】查看物理文件位置
即为datadir目录牟其中ib_logfile0/ib_logfile1为redo log file，ibdata1为 undo log file
6、中继日志（relay log）
【6.1】概念
与二进制日志有些相似，用于存取从服务器的IO线程接受来自主服务器发来的变更日志。一般用于主从复制。
【6.2】查看
show variables like '%relay%';
7、DDL日志（ddl log）
【7.1】概念
记录元数据变更的操作（DDL操作）
【7.2】文件
ddl_log.log
#为了在DDL崩溃后恢复，以二进制方式存取，不可读，文件大小最大约4G，大约100W行数据。
#如果慢了后要清理，否则就不能再运行别的DDL语句。
8、其他文件（socket/pid/表结构/Innodb）
【8.1】socket 套接字文件
【8.1.1】组成：ip+端口（本地IP：port，远程IP：port）
【8.1.2】进行网络通信必须5种信息：协议、本地IP、本地协议端、远程IP、远程协议端口
【8.1.3】查看定义：show variables like '%socket%';
【8.1.4】如果主机上有多个实例，通过连接socket可以连接相应实例
如：mysql -uroot -p -S /mysql/data/3306/mysql.sock
【8.1.5】建立连接的大致过程
（1）建立socket套接字
（2）给socket套接字赋予地址
（3）建立socket连接
【8.1.6】配置文件
[mysqld]
socket = [path/file_name]
【8.2】pid文件
【8.2.1】查看
show variables like '%pid%';（每次启动都会去写入pid文件）
【8.3】表结构文件
innodb：二进制形式文件（1）.frm为表定义 信息 （2）.ibd 表的数据和索引信息
MyISAM：（1）.frm 为表定义信息 （2）.MYD 为表数据文件 （3）.MYI 为表索引文件
【8.4】innodb存储引擎相关的文件
【8.4.1】表空间文件：数据文件、临时文件；
独立表空间设置参数：show variables like 'innodb_file_per_table'；
（1）参数为 off/0，就是共享表空间。所有的库/表数据都放在一个或几个文件
（2）参数为on/1，就是独立表空间。每一个表都有自己的表空间（即每个表都有独立的文件）
【8.4.2】数据文件与临时文件
show variables like '%innodb%data%';
如图：
（1）临时文件：innodb_data_file_path 。如下图，这里没有路径，那么默认就在datadir下。
（2）临时文件
【8.4.3】配置文件
[mysqld]
innodb_data_file_path=ibdata1:1G:autoextend:max:5G
innodb_temp_data_file_path = ibtemp1:200M:autoextend:max:10G
【8.5】redo文件与undo文件
【8.5.1】查看
（1）redo：show variables like '%innodb_log%';
选中的行是存放位置，./ 就是datadir；
（2）undo：show variables like '%innodb_undo%';
同上。
【8.6】其他文件总结，所有文件几乎都在配置文件里配置了。
但是undo与redo一般情况下是默认的。
如图：
【1】ib_logfile0/ib_logfile0 ：为redo文件。
【2】ibtemp1：为Innodb临时文件
【3】ibdata1：为Innodb共享表空间、原数据文件、undo日志、double write 存放
【4】ib_buffer_pool：innodb缓存池预热保存文件