本文详细介绍了MySQL中的多种日志，包括错误日志、一般查询日志、慢查询日志、二进制日志（binlog）及其相关操作，如查看、删除。重点讲述了binlog的用途、记录格式和相关变量，以及事务日志redo log和undo log的概念与区别。此外，还提到了DDL日志在数据库恢复中的作用。
记录日志
不管是哪个数据库产品，一定会有日志文件。在MariaDB/MySQL中，主要有5种日志文件：
- 错误日志(error log)：记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。
- 查询日志(general log)：记录建立的客户端连接和执行的语句。
- 二进制日志(bin log)：记录所有更改数据的语句，可用于数据复制。
- 慢查询日志(slow log)：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。
- 中继日志(relay log)：主从复制时使用的日志。
1.日志刷新操作
以下操作会刷新日志文件，刷新日志文件时会关闭旧的日志文件并重新打开日志文件。对于有些日志类型，如二进制日志，刷新日志会滚动日志文件，而不仅仅是关闭并重新打开。
mysql> FLUSH LOGS;
shell> mysqladmin flush-logs
shell> mysqladmin refresh
2.错误日志
错误日志是最重要的日志之一，它记录了MariaDB/MySQL服务启动和停止正确和错误的信息，还记录了mysqld实例运行过程中发生的错误事件信息。
可以使用" --log-error=[file_name] "来指定mysqld记录的错误日志文件，如果没有指定file_name，则默认的错误日志文件为datadir目录下的 hostname.err ，hostname表示当前的主机名。
也可以在MariaDB/MySQL配置文件中的mysqld配置部分，使用log-error指定错误日志的路径。
如果不知道错误日志的位置，可以查看变量log_error来查看。
mysql> show variables like 'log_error';
+---------------+----------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------+
| log_error | /var/lib/mysql/node1.longshuai.com.err |
+---------------+----------------------------------------+
在MySQL 5.5.7之前，刷新日志操作(如flush logs)会备份旧的错误日志(以_old结尾)，并创建一个新的错误日志文件并打开，在MySQL 5.5.7之后，执行刷新日志的操作时，错误日志会关闭并重新打开，如果错误日志不存在，则会先创建。
在MariaDB/MySQL正在运行状态下删除错误日志后，不会自动创建错误日志，只有在刷新日志的时候才会创建一个新的错误日志文件。
以下是MySQL 5.6.35启动的日志信息。
3.一般查询日志
查询日志分为一般查询日志和慢查询日志，它们是通过查询是否超出变量 long_query_time 指定时间的值来判定的。在超时时间内完成的查询是一般查询，可以将其记录到一般查询日志中，但是建议关闭这种日志（默认是关闭的），超出时间的查询是慢查询，可以将其记录到慢查询日志中。
使用" --general_log={0|1} “来决定是否启用一般查询日志，使用” --general_log_file=file_name "来指定查询日志的路径。不给定路径时默认的文件名以 hostname.log 命名。
和查询日志有关的变量有：
long_query_time = 10 # 指定慢查询超时时长，超出此时长的属于慢查询，会记录到慢查询日志中
log_output={TABLE|FILE|NONE} # 定义一般查询日志和慢查询日志的输出格式，不指定时默认为file
TABLE表示记录日志到表中，FILE表示记录日志到文件中，NONE表示不记录日志。只要这里指定为NONE，即使开启了一般查询日志和慢查询日志，也都不会有任何记录。
和一般查询日志相关的变量有：
general_log=off # 是否启用一般查询日志，为全局变量，必须在global上修改。
sql_log_off=off # 在session级别控制是否启用一般查询日志，默认为off，即启用
general_log_file=/mydata/data/hostname.log # 默认是库文件路径下主机名加上.log
在MySQL 5.6以前的版本还有一个"log"变量也是决定是否开启一般查询日志的。在5.6版本开始已经废弃了该选项。
默认没有开启一般查询日志，也不建议开启一般查询日志。此处打开该类型的日志，看看是如何记录一般查询日志的。
首先开启一般查询日志。
mysql> set @@global.general_log=1;
[root@xuexi data]# ll *.log
-rw-rw---- 1 mysql mysql 5423 Mar 20 16:29 mysqld.log
-rw-rw---- 1 mysql mysql 262 Mar 29 09:31 xuexi.log
执行几个语句。
mysql> select host,user from mysql.user;
mysql> show variables like "%error%";
mysql> insert into ttt values(233);
mysql> create table tt(id int);
mysql> set @a:=3;
查看一般查询日志的内容。
[root@xuexi data]# cat xuexi.log
/usr/local/mysql/bin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /mydata/data/mysql.sock
Time Id Command Argument
180421 20:04:41 13 Query select user,host from mysql.user
180421 20:06:06 13 Query show variables like "%error%"
180421 20:07:28 13 Query insert into ttt values(233)
180421 20:11:47 13 Query create table tt(id int)
180421 20:12:29 13 Query set @a:=3
由此可知，一般查询日志查询的不止是select语句，几乎所有的语句都会记录。
4.慢查询日志
查询超出变量 long_query_time 指定时间值的为慢查询。但是查询获取锁(包括锁等待)的时间不计入查询时间内。
mysql记录慢查询日志是在查询执行完毕且已经完全释放锁之后才记录的，因此慢查询日志记录的顺序和执行的SQL查询语句顺序可能会不一致(例如语句1先执行，查询速度慢，语句2后执行，但查询速度快，则语句2先记录)。
注意，MySQL 5.1之后就支持微秒级的慢查询超时时长，对于DBA来说，一个查询运行0.5秒和运行0.05秒是非常不同的，前者可能索引使用错误或者走了表扫描，后者可能索引使用正确。
另外，指定的慢查询超时时长表示的是超出这个时间的才算是慢查询，等于这个时间的不会记录。
和慢查询有关的变量：
long_query_time=10 # 指定慢查询超时时长(默认10秒)，超出此时长的属于慢查询
log_output={TABLE|FILE|NONE} # 定义一般查询日志和慢查询日志的输出格式，默认为file
log_slow_queries={yes|no} # 是否启用慢查询日志，默认不启用
slow_query_log={1|ON|0|OFF} # 也是是否启用慢查询日志，此变量和log_slow_queries修改一个另一个同时变化
slow_query_log_file=/mydata/data/hostname-slow.log #默认路径为库文件目录下主机名加上-slow.log
log_queries_not_using_indexes=OFF # 查询没有使用索引的时候是否也记入慢查询日志
现在启用慢查询日志。
mysql> set @@global.slow_query_log=on;
因为默认超时时长为10秒，所以进行一个10秒的查询。
mysql> select sleep(10);
查看慢查询日志文件。这里看到虽然sleep了10秒，但是最后查询时间超出了847微秒，因此这里也记录了该查询。
[root@xuexi data]# cat xuexi-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /mydata/data/mysql.sock
Time Id Command Argument
# Time: 170329 9:55:58
# User@Host: root[root] @ localhost [] Id: 1
# Query_time: 10.000847 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use test;
SET timestamp=1490752558;
select sleep(10);
随着时间的推移，慢查询日志文件中的记录可能会变得非常多，这对于分析查询来说是非常困难的。好在提供了一个专门归类慢查询日志的工具mysqldumpslow。
[root@xuexi data]# mysqldumpslow --help
-d debug
-v verbose：显示详细信息
-t NUM just show the top n queries：仅显示前n条查询
-a don't abstract all numbers to N and strings to 'S'：归类时不要使用N替换数字，S替换字符串
-g PATTERN grep: only consider stmts that include this string：通过grep来筛选select语句。
该工具归类的时候，默认会将同文本但变量值不同的查询语句视为同一类，并使用N代替其中的数值变量，使用S代替其中的字符串变量。可以使用-a来禁用这种替换。如：
[root@xuexi data]# mysqldumpslow xuexi-slow.log
Reading mysql slow query log from xuexi-slow.log
Count: 1 Time=10.00s (10s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
select sleep(N)
[root@xuexi data]# mysqldumpslow -a xuexi-slow.log
Reading mysql slow query log from xuexi-slow.log
Count: 1 Time=10.00s (10s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
select sleep(10)
显然，这里归类后的结果只是精确到0.01秒的，如果想要显示及其精确的秒数，则使用-d选项启用调试功能。
[root@xuexi data]# mysqldumpslow -d xuexi-slow.log
Reading mysql slow query log from xuexi-slow.log
[[/usr/local/mysql/bin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /mydata/data/mysql.sock
Time Id Command Argument
# Time: 170329 9:55:58
# User@Host: root[root] @ localhost [] Id: 1
# Query_time: 10.000847 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use test;
SET timestamp=1490752558;
select sleep(10);
]]
<<>>
<<# Time: 170329 9:55:58
# User@Host: root[root] @ localhost [] Id: 1
# Query_time: 10.000847 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use test;
SET timestamp=1490752558;
select sleep(10);
>> at /usr/local/mysql/bin/mysqldumpslow line 97, <> chunk 1.
[[# Time: 170329 9:55:58
# User@Host: root[root] @ localhost [] Id: 1
# Query_time: 10.000847 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use test;
SET timestamp=1490752558;
select sleep(10);
]]
{
{ select sleep(N)}}
Count: 1 Time=10.00s (10s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
select sleep(N)
慢查询在SQL语句调优的时候非常有用，应该将它启用起来，且应该让慢查询阈值尽量小，例如1秒甚至低于1秒。就像一天执行上千次的1秒语句，和一天执行几次的20秒语句，显然更值得去优化这个1秒的语句。
5.二进制日志
5.1 二进制日志文件
二进制日志包含了引起或可能引起数据库改变(如delete语句但没有匹配行)的事件信息，但绝不会包括select和show这样的查询语句。语句以"事件"的形式保存，所以包含了时间、事件开始和结束位置等信息。
二进制日志是以事件形式记录的，不是事务日志(但可能是基于事务来记录二进制日志)，不代表它只记录innodb日志，myisam表也一样有二进制日志。
对于事务表的操作，二进制日志只在事务提交的时候一次性写入(基于事务的innodb二进制日志)，提交前的每个二进制日志记录都先cache，提交时写入。对于非事务表的操作，每次执行完语句就直接写入。
MariaDB/MySQL默认没有启动二进制日志，要启用二进制日志使用--log-bin=[on|off|file_name]选项指定，如果没有给定file_name，则默认为datadir下的主机名加"-bin"，并在后面跟上一串数字表示日志序列号，如果给定的日志文件中包含了后缀(logname.suffix)将忽略后缀部分。
或者在配置文件中的[mysqld]部分设置log-bin也可以。注意：对于mysql 5.7，直接启动binlog可能会导致mysql服务启动失败，这时需要在配置文件中的mysqld为mysql实例分配server_id。
最低0.47元/天 解锁文章
扫一扫
1367
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
