先说个基础知识作为铺垫:
MySQL数据库中的参数可以分为两类,动态参数和静态参数。静态参数是只读的,不能通过SET命令修改,只能改MySQL 的配置文件(my.cnf)。动态参数可以通过SET命令修改。SET命令语法如下:
可以看到global和session关键字,表明该参数的修改是基于当前会话(指的是命令行建立的连接,可以建多个连接)还是整个实例的生命周期(启动后就算生命周期的开始,除非遇到下一次重启)。即时是global,MySQL本身并不会对参数文件中的该值进行修改。也就是说,在下一次启动实例时还是会读取参数文件。如果想从根本上修改,就必须去修改参数文件(my.cnf),修改后要重启MySQL服务器才能生效。
MySQL日志类型
关于分类,从不同的角度看,可以有不同的分类。例如,从存储类型的角度来看,可以分为两类:逻辑日志(存储 SQL 修改语句)和物理日志(存储数据被修改的值)。按照功能分成七类:重做日志(redo log),回滚日志(undo log),二进制日志(binlog),错误日志(errorlog),慢查询日志(slow query log),一般查询日志(general log),中继日志(relay log)。
接下来重点介绍错误日志、慢查询日志、二进制日志、重做日志。
错误日志(errorlog)
记录 MySQL 服务实例启动、运行、停止等等的过程信息,需要注意的是这里记录的不仅仅是错误信息,还有警告和正确的信息。
mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+----------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------+
| log_error | /usr/local/mysql/data/mysqld.local.err |
+---------------+----------------------------------------+
默认情况下,错误日志文件名会以 “主机名.err” 结尾。但是,需要知道,错误日志不会记录所有的错误信息,只有被 MySQL 声明为 “关键错误” 的事件发生时才会记录。
一般查询日志(general log)
记录了所有对MySQL数据库的执行sql。对于查询日志来说,MySQL 发生了什么,就会记录什么。通常来说,查询日志都是关闭的,默认也是关闭的。只有在调试或者特殊时期,需要追踪某些特殊的查询,可以临时打开查询日志。
-- 第一个参数 general_log,标识查询日志是否处于开启状态,默认是 OFF,即关闭
mysql> SHOW VARIABLES LIKE 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
开启查询日志有两种方式,一种是修改配置文件,但是需要重启服务;另一种是在运行时通过 SET 命令,但是只能对当前的会话生效。general_log_file 参数则用于指定文件的位置(同样可以自行修改),我们可以看一看:
-- 第二个参数 general_log_file
mysql> SHOW VARIABLES LIKE 'general_log_file';
+------------------+-----------------------------------------+
| Variable_name | Value |
+------------------+-----------------------------------------+
| general_log_file | /usr/local/mysql/data/B000000104678.log |
+------------------+-----------------------------------------+
最后一个参数是 log_output,它可以用于设置查询日志和慢查询日志(是的,这个参数控制了两类日志的行为)的输出类型。默认情况下,这个参数的值是 “FILE”,代表将日志写入文件。我们可以把它修改成 “TABLE”,这样,查询日志就会被写入 mysql 系统库的 general 表中(对于慢查询日志来说,会写入 slow_log 表中)。但是这会严重降低服务器的性能,只能将它应用于调试,线上服务要绝对禁止。
慢查询日志(slow query log)
MySQL 的慢查询日志记录响应时间超过参数 long_query_time的查询语句。
需要注意的是:
1.默认环境下,慢查询日志是关闭的。
2.默认情况下,慢查询日志中不会记录管理语句,可通过设置 log_slow_admin_statements = on 让管理语句中的慢查询也会记录到慢查询日志中。
3.默认情况下,也不会记录查询时间不超过 long_query_time 但是不使用索引的语句,可通过配置 log_queries_not_using_indexes = on 让不使用索引的 SQL 都被记录到慢查询日志中。
4.慢查询日志只会记录执行成功的语句,这与查询日志不同。
获取已经执行完的慢查询:
-- slow_query_log_file:标记存放慢查询日志文件的完整路径
mysql> SHOW VARIABLES LIKE 'slow_query_log_file';
+---------------------+----------------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------------+
| slow_query_log_file | /usr/local/mysql/data/B000000104678-slow.log |
+---------------------+----------------------------------------------+
查看已经执行完的慢查询:
# tail -n5 /data/mysql/data/3306/mysql-slow.log
Time: 2019-05-21T09:15:06.255554+08:00
User@Host: root[root] @ localhost [] Id: 8591152
Query_time: 10.000260
Lock_time: 0.000000
Rows_sent: 1
Rows_examined: 0
SET timestamp=1558401306;
select sleep(10);
这里对上方的还行结果详细描述一下:
- tail -n5:只查看慢查询文件的最后 5 行
- Time:慢查询发生的时间
- User@Host:客户端用户和 IP
- Query_time:查询时间
- Lock_time:等待表锁的时间
- Rows_sent:语句返回的行数
- Rows_examined:语句执行期间从存储引擎读取的行数
上面这种方式是用系统自带的慢查询日志查看的,如果觉得系统自带的慢查询日志不方便查看,小伙伴们可以使用 pt-query-digest 或者 mysqldumpslow 等工具对慢查询日志进行分析。
以下是使用mysqldumpslow的示例:
# 查询访问次数最多的 10条 SQL 语句
mysqldumpslow -s c -t 10 /usr/local/mysql/data/B000000104678-slow.log
# 查询返回记录数最多的 10条 SQL 语句
mysqldumpslow -s r -t 10 /usr/local/mysql/data/B000000104678-slow.log
# 查询含有 like 的 SQL 语句
mysqldumpslow -g 'like' /usr/local/mysql/data/B000000104678-slow.log
另外,线上业务一般建议把 long_query_time 设置为 1 秒,如果某个业务的 MySQL 要求比较高的 QPS,可设置慢查询为 0.1 秒。一般测试环境建议 long_query_time 设置的阀值比生产环境的小,比如生产环境是 1 秒,则测试环境建议配置成 0.5 秒。便于在测试环境及时发现一些效率低的 SQL。
二进制日志(binlog)
Binlog 用于记录数据库的所有变化,简单的说,所有涉及数据变动的操作,都会记录下来。(例如select和show就不能被记录)。作用于恢复和复制(主从同步)。
对于 8.0 之前的版本来说,Binlog 是默认关闭的。想要开启 Binlog,我们就必须要去修改 MySQL 的配置文件(my.cnf),然后重启 MySQL。开启二进制文件会对性能有一定影响。开启后,想要查看当前系统的所有 Binlog 日志,可以查看 log_bin_index 参数,如下所示:
-- log_bin_index 参数标识的是 Binlog 索引文件的位置
mysql> SHOW VARIABLES LIKE 'log_bin_index';
+---------------+---------------------------------------+
| Variable_name | Value |
+---------------+---------------------------------------+
| log_bin_index | /usr/local/mysql/data/mysql-bin.index |
+---------------+---------------------------------------+
之后,可以使用cat命令查看下这个文件(可能需要 root/管理员 权限):
➜ ~ sudo cat /usr/local/mysql/data/mysql-bin.index Password:
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
后缀为二进制文件的序列号。
Binlog 日志的记录格式一共有三种:ROW、STATMENT 以及 MIXED,下面,我来对它们进行说明:
- ROW(记录行的内容,记两条,更新前和更新后都有)
- 说明:基于行的复制,不会记录每一条 SQL 语句的上下文信息,仅仅会保存哪条记录被修改
- 优点:记录了每一行数据的修改细节
- 缺点:日志量巨大,特别是批量修改的情况
- STATMENT(记sql语句)
- 说明:基于 SQL 语句的复制,记录每一条修改数据的 SQL,是 Binlog 的默认格式
- 优点:日志量小,节约磁盘 IO
- 缺点:在某些情况下会导致 Master-Slave 不一致,例如:执行 Sleep 函数
其中,MIXED 格式是 ROW 和 STATMENT 两种格式的混合,普通的复制使用的是 STATMENT 格式,对于 STATMENT 无法复制的操作则会使用 ROW 格式去记录。使用 MIXED 格式时,会由 MySQL 来决定使用哪种格式记录日志。
随着系统在不断运行,日志量也会逐步增加,所以,自然也就会有清理日志的工作。MySQL 提供了两种方式来清理 Binlog,一种是手动的,一种是自动的。手动清理 Binlog 是通过 PURGE 命令,它会同时删除 Binlog 文件和 Binlog 索引文件记录。自动清理 Binlog 是通过配置 MySQL 的 expire_logs_days 参数(在 my.cnf 中配置)。例如,设置 expire_logs_days = 10,表示系统保留10天的 Binlog,第11天将会删除第1天的 Binlog。
binlog时二进制文件,不能像错误日志那样用cat、tail来看,必须通过MySQL提供的工具mysqlbinlog。
重做日志(redo log)
redo log 是物理日志,记录的是“在某个数据页上做了什么修改”。当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log里面,并更新内存,这个时候更新就算完成了(还未写入磁盘)。InnoDB 引擎会在适当的时候,将这个操作记录更新到磁盘里面。注意,事务开始之后就开始写入redo log文件,事务提交并不等于更新到磁盘中。
InnoDB 的 redo log 是固定大小的,比如可以配置为一组 4 个文件,每个文件的大小是 1GB,那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写,写到末尾就又回到开头循环写(循环利用空间,把数据写到磁盘了才可以释放空间)。 redo log 是 InnoDB 引擎特有的日志,而 Server 层也有自己的日志,称为 binlog(归档日志)。
为什么会binlog和redo log日志都有恢复功能呢?
因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM,但是 MyISAM 没有 crash-safe 的能力,binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的,既然只依靠 binlog 是没有 crash-safe 能力的,所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现。
这两种日志有以下三点不同。
1.redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用。
2.redo log 是物理日志,记录的是“在某个数据页上做了什么修改”;binlog 是逻辑日志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ”。
3.redo log 是循环写的,空间固定会用完;binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。
回滚日志(undo log)
保存了事务发生之前的数据的一个版本,可以用于回滚。存的是操作,逻辑日志。实现MVCC的基础。
中继日志(relay log)
可以理解为来自Master的二进制日志,I/O线程从Master读取,然后写入本地的一类日志,类似于binary log。中继日志是连接mastert和slave的信息,它是复制的核心,I/O线程将来自master的事件存储到中继日志中,中继日志充当缓冲,这样master不必等待slave执行完成就可以发送下一个事件。
最后,不懂就问~