MySQL 日志 socket_（3.12）mysql基础深入——mysql日志文件/其他文件（socket/pid/表结构/Innodb）...

(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:\mysql\log\binlog\binlog.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 locktime

ar: average rows sent

at: average querytimec: count

l: locktimer: rows sent

t: querytime【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缓存池预热保存文件