浅谈MySQL日志文件系统
MySQL日志文件系统
日志是mysql数据库的重要组成部分。日志文件中记录着mysql数据库运行期间发生的变化；也就是说用来记录mysql数据库的客户端连接状况、SQL语句的执行情况和错误信息等。当数据库遭到意外的损坏时，可以通过日志查看文件出错的原因，并且可以通过日志文件进行数据恢复。
MySQL日志分类
MySQL日志大致可以分为一下几种类型：
| 日志类型 | 日志作用 |
|---|---|
| 错误日志 | 记录启动、运行或停止mysqld时出现的问题。 |
| 通用查询日志 | 记录建立的客户端连接和执行的语句。 |
| 二进制日志 | 记录所有更改数据的语句。还用于复制。 |
| 中继日志 | 只有主从复制的slave服务器才会有，记录的是来自主服务器的数据变化； |
| 慢查询日志 | 记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。 |
| DDL日志 | 也叫元数据日志，记录的是通过DDL进行的元数据操作； |
错误日志
MySQL的错误日志，默认是开启并无法关闭，一般存放在在数据目录中，以主机名.err格式命名，也可以用 可以用–log-error[=file_name]选项来开启mysql错误日志，该选项指定mysqld保存错误日志文件的位置。错误日志一般是文本文件，可以直接tail打开进行分析查看。
错误日志不仅仅记录着错误信息，它记录的事件有：
- 服务器启动和关闭过程中的信息
- 服务器运行过程中的错误信息
- 事件调度器运行一个事件时产生的信息
- (如果被配置为从服务器)启动从服务器进程时产生的信息
查看错误日志存放路径：
mysql> SHOW VARIABLES LIKE 'log_error%'; +---------------+---------------------------------+ | Variable_name | Value | +---------------+---------------------------------+ | log_error | /var/lib/mysql/iZ2319y43bmZ.err | +---------------+---------------------------------+ 1 row in set (0.00 sec) mysql>
通用查询日志
通用查询日志记录了数据库执行的命令。而不仅仅只是select语句，当开启查询日志之后，数据库可能需要不停地写入查询日志，会增加很多磁盘I/O，所以除非是出于调试目的，一般不建议开启查询日志。
如果需要可以手动开启。如果开启了查询日志，可以通过如下3种方式输出在查询日志
- 将查询日志存放于指定的日志文件中。
- 将查询日志存放于mysql.general_log表中。
- 将查询日志同时存放于指定的日志文件与mysql库的general_log表中。
查看通用查询日志相关信息：
mysql> show variables where variable_name like '%general_log%' or variable_name='log_output'; +------------------+---------------------------------+ | Variable_name | Value | +------------------+---------------------------------+ | general_log | OFF | | general_log_file | /var/lib/mysql/iZ2319y43bmZ.log | | log_output | FILE | +------------------+---------------------------------+ 3 rows in set (0.00 sec)
log_oupt可以log_output:表示当查询日志开启以后，以哪种方式存放，log_output可以设置为4种值，FILE、TABLE 、FILE,TABLE、NONE。
此值为NONE时表示不记录查询日志，即使general_log设置为ON，如果log_output设置为NONE，也不会记录查询日志，其实，log_output不止控制用户查询日志的输出，慢查询日志的输出也是由此参数进行控制，也就是说，log_output设置为file，就表示查询日志和慢查询日志都存放到对应的文件中，设置为table，查询日志和慢查询日志就都存放在对应的数据库表中。
慢查询日志
MySQL慢查询日志是MySQL提供的一种日志记录，用来记录执行时长超过指定时长的查询语句，具体指运行时间超过 long_query_time 值的 SQL 语句，则会被记录到慢查询日志中。
long_query_time 默认值是 10 ，单位是 s，即默认是 10秒 。默认情况下，MySQL数据库并不会开启慢查询日志，需要手动设置这个参数。
慢查询日志开销比较小，通过对慢查询的分析可以到系统的性能问题，找到相关的sql语句并进一步优化，因为或多或少都有一些性能开销，所以如果不是为了调优，一般建议不用开启。
mysql> SHOW VARIABLES LIKE 'slow_query_log%'; +---------------------+--------------------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /usr/local/var/mysql/upstreamdeMac-mini-slow.log | +---------------------+--------------------------------------------------+
慢查询相关配置
# slow logging slow_query_log=1 //是否启用慢查询日志，[1 | 0] 或者 [ON | OFF]， MySQL 5.6以前此参数应该为log_slow_queries log-output=FILE // 指定用文件存储或者用数据表存储，与通用查询日志共用这个参数，日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。 slow_query_log_file //指定文件存储路径和文件名，一般在数据目录下，默认是/var/lib/mysql/hostname-slow.log。 long_query_time=10 //慢查的时长单位为秒，可以精确到小数点后6位(微秒)。 log_queries_not_using_indexes //没有使用到索引的语句，是否被记录到慢查询日志中。 log_timestamps //主要是控制 error log、genera log，等等记录日志的显示时间参数,默认时间戳是utc时间，与北京时间相差8h，一般我们会设置成SYSTEMd为操作系统时间。
慢查询日志分析
慢查询一般用下面两种工具来进行分析：
mysqldumpslow
mysqldumpslow是官方自带的慢查询日志分析工具，用perl编写，可以用mysqldumpslow -h来看看具体用法。
[root@iZ2319y43bmZ ~]# mysqldumpslow -h Option h requires an argument ERROR: bad option Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] Parse and summarize the MySQL slow query log. Options are --verbose verbose --debug debug --help write this text to standard output -v verbose -d debug -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 -r reverse the sort order (largest last instead of first) -t NUM just show the top n queries -a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time [root@iZ2319y43bmZ ~]#
主要参数：
- -v、–verbose : 在详细模式下运行，打印有关该程序的更多信息。
- -d、–debug : 在调试模式下运行。
- -s [sort_type] : sort_type 是信息排序的依据
- al：average lock time，按平均等待锁的时间排序
- ar：average rows sent，按平均发给客户端的行总数排序
- at：average query time，按平均查询时间排序
- c：count，按出现总次数排序
- l：lock time，按等待锁的时间排序
- r：rows sent，按扫描的行总数排序
- t：query time，按累计总耗费时间排序
- -r ：倒序信息排序
- -t NUM ：只显示 top n 条查询
- -g PATTERN : 根据字符串筛选慢查询日志，可写正则匹配，大小写不敏感。
- -h HOSTNAME : 根据服务器名称选择慢查询日志
- -i NAME : 根据服务器 MySQL 实例名称选择慢查询日志
- -l : 不要将总时间减去锁定时间
[root@iZ2319y43bmZ mysql]# mysqldumpslow slowquery_2018052511.log Reading mysql slow query log from slowquery_2018052511.log Count: 1 Time=1.23s (3s) Lock=0.00s (0s) Rows=143.0 (143), doyd[doyd]@[119.145.8.228] show full tables from `kpzs_v2` where table_type = 'S' [root@iZ2319y43bmZ mysql]#
它主要统计慢查询语句的如下信息：
- 出现次数(count)
- 执行最长时间，耗费总时间
- 等待锁的时间
- 发送给客户端的行数
- 执行该语句的客户端地址
- 执行的原语句
percona-toolkit
percona-toolkit 是一组高级命令行工具的集合，用prel语言编写，需要自行手工安装，安装可以参考官网文档，用来执行各种通过手工执行非常复杂和麻烦的 mysql 和系统任务。percona-toolkit中关于慢查询，主要是pt-query-digest 这个工具，这个工具可以把分析结果输出到表中，做慢查询监控时，可以考虑写脚本去分析慢查询并将结果输出到监控库，设置脚本定期执行即可。如果想直接定位分析，一般pt-query-digest slowquery_yyyymmddhh.log即可
用法：
pt-query-digest [OPTIONS] [FILES] [DSN]
参数说明：
- –create-review-table : 当使用–review参数把分析结果输出到表中时，如果没有表就自动创建。
- –create-history-table : 当使用–history参数把分析结果输出到表中时，如果没有表就自动创建。
- –filter : 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
- –limit : 限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
- –host : mysql服务器地址
- –user : mysql用户名
- -password : mysql用户密码
- –history : 将分析结果保存到表中，分析结果比较详细，下次再使用–history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
- –review : 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用–review时，如果存在相同的语句分析，就不会记录到数据表中。
- –output : 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
- –since : 从什么时间开始分析，值为字符串，可以是指定的某个 “yyyy-mm-dd [hh:mm:ss]” 格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
–until : 截止时间，配合—since可以分析一段时间内的慢查询。
[root@iZ2319y43bmZ mysql]# pt-query-digest slowquery_2018060110.log # 240ms user time, 20ms system time, 24.45M rss, 204.81M vsz # Current date: Mon Jun 4 10:08:33 2018 # Hostname: iZ2319y43bmZ # Files: slowquery_2018060110.log # Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________ # Time range: all events occurred at 2018-06-01 10:49:59 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 1s 1s 1s 1s 1s 0 1s # Lock time 75us 75us 75us 75us 75us 0 75us # Rows sent 143 143 143 143 143 0 143 # Rows examine 143 143 143 143 143 0 143 # Query size 42 42 42 42 42 0 42 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============= ===== ====== ===== =========== # 1 0xFBE0E38DA0F69470 1.0037 100.0% 1 1.0037 0.00 SHOW TABLES # Query 1: 0 QPS, 0x concurrency, ID 0xFBE0E38DA0F69470 at byte 0 ________ # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: all events occurred at 2018-06-01 10:49:59 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 100 1 # Exec time 100 1s 1s 1s 1s 1s 0 1s # Lock time 100 75us 75us 75us 75us 75us 0 75us # Rows sent 100 143 143 143 143 143 0 143 # Rows examine 100 143 143 143 143 143 0 143 # Query size 100 42 42 42 42 42 0 42 # String: # Databases kpzs # Hosts localhost # Users doyd # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms # 1s ################################################################ # 10s+ # Tables # SHOW TABLE STATUS FROM `kpzs` LIKE 'kpzs_v2'\G # SHOW CREATE TABLE `kpzs`.`kpzs_v2`\G SHOW /*!50002 FULL*/ TABLES FROM `kpzs_v2`\G [root@iZ2319y43bmZ mysql]#
二进制日志文件
二进制日志（binlog）记录 MySQL 数据库中所有与更新相关的操作，即二进制日志记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。常用于恢复数据库和主从复制。
二进制日志作用
假如备份策略是每天0：00备份一次，那么在本次备份后，下面备份前的这个时间段内某一时刻，数据库崩溃，如果只是依靠备份文件来恢复，中间那段时间的数据就丢失了，所以还需要依靠二进制日志文件来对0：00之后的所有更新相关操作进行replay一次，这样就可以将数据恢复到崩溃时的状态，这与oracle中的归档日志是类似的。
二进制日志类型
statement模式：记录对数据库实例的更新语句，例如对语句update A set user=’test’，这种模式只记录该语句，这种模式的优点是日志量小，IO压力小，性能较高，缺点是记录的不够精确，例如，某些sql语句带了一些函数如user()，不同的数据库用户执行sql可能会操作不同的数据，这种情况下，只是记录语句是不够的，在恢复的时候，并不知道该语句当初是哪个用户执行的，所以并不能保证恢复与操作完全一致。
row模式：记录对语句所修改到的行，以及对行的具体修改，例如，update A set user=’test’，这条语句所影响到的行，以及修改，都会记录到二进制文件中，如果A表中有1000行数据，那么这一千行数据都会
mixed模式：在如下情况，系统会自动从statement模式切换刀片
查看二进制相关配置
mysql> SHOW VARIABLES LIKE 'log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | +---------------------------------+-------+ 5 rows in set (0.04 sec) mysql>