MySQL日志系统详细资料分享

做过大型系统的都知道，日志的作用不用小觑，往往到了项目中后期，对项目进行优化升级都是依据日志做出升级优化的决策的。那么学习MySQL，日志部分当然不能错过。我们面试中实际应用的所谈到的优化都是要从日志中得出来的。系统的学习mysql的日志，有助于我们准确的定位问题，提高自己的工作水平。此外，后面的一系列日志会重点从DBA的运维方面进行着手，系统的去理解MySQL各方面的配置，做到知己知彼，让MySQL成为自己得心应手的数据仓库。

一、MySQL的日志类型

默认情况下，所有的MySQL日志以文件的方式存放在数据库根目录下：

[root@roverliang data]# pwd
/usr/local/webserver/extend_lib/mysql/data
[root@roverliang data]# ls
auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mytest performance_schema roverliang roverliang.err roverliang.pid test

MySQL的日志类型有以下几种：

1.  错误日志（error）,MySQL服务实例启动、运行或者停止等相关信息。
2.  普通查询日志（general）,MySQL服务实例运行的所有SQL语句或者MySQL命令。
3.  二进制日志（binary）,对数据库执行的所有更新语句，不包括select 和show语句。
4.  慢查询日志（slow），执行时间超过long_query_time 设置值的SQL语句，或者没有使用索引的SQL语句。

二、MySQL日志缓存

一个高速、稳定、可靠的系统，缓存在其中必定起着至关重要的作用。MySQL日志处理也使用了缓存机制。MySQL日志最初存放在MySQL服务器的内存中，若超过指定的存储容量，内存中的日志则写（或者刷新flush）到外存中，以数据库表或者以文件的方式永远的保存在硬盘中。

三、MySQL错误日志（error log）

MySQL的错误日志主要记录MySQL服务实例每次启动、停止的详细信息，以及MySQL实例运行过程中产生的警告或者错误信息。和其他的日志不同，MySQL的error日志必须开启，无法关闭。

默认情况下，错误日志的文件名为： 主机名.err。 但error 日志并不会记录所有的错误信息，只有MySQL服务实例运行过程中发声的关键错误（critical）才会被记录下来。

mysql> show variables like 'log_error'\G
*************************** 1. row ***************************
Variable_name: log_error
Value: /usr/local/webserver/extend_lib/mysql/data/roverliang.err
1 row in set (0.02 sec)

四、MySQL普通查询日志(general log)

MySQL普通查询日志记录MySQL服务实例所有的操作，如select、update、insert、delete等操作，无论该操作是否成功执行。还有MySQL客户机与MySQL服务端连接及断开的相关信息，无论连接成功还是失败。与MySQL普通查询日志有关的参数有三个。

[]()general_log

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log  | OFF  |
+---------------+-------+
1 row in set (0.01 sec)

可以通过 set @@global.general_log = 1 的方式来开启普通查询日志。

mysql> set @@global.general_log =1;
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log  | ON  |
+---------------+-------+

但是通过这种方式修改MySQL的变量只会在当前的MySQL实例运行期间生效，一旦MySQL重启，则会重新恢复到默认的状态。永久生效的方式是修改mysql 的my.cnf 文件。在配置文件后添加：

general_log = 1
general_log_file

普通查询日志一旦开启，MySQL服务实例将自动创建普通查询日志文件，general_log_file参数设置了普通查询日志文件的物理位置。如下：

mysql> show variables like 'general_log_file';
+------------------+-----------------------------------------------------------+
| Variable_name  | Value                           |
+------------------+-----------------------------------------------------------+
| general_log_file | /usr/local/webserver/extend_lib/mysql/data/roverliang.log |
+------------------+-----------------------------------------------------------+

注意：由于普通查询日志几乎记录了MySQL的所有操作，对于数据访问频繁的数据库服务器而言，如果开启MySQL的普通查询日志将会大幅度的降低数据库的性能，因此建议关闭普通查询日志。只有在特殊时期，如需要追踪某些特殊的查询日志，可以临时打开普通的查询日志。

log_output

log_output参数设置了普通查询日志以及慢查询日志的内容存储到数据库表中。 可以使用 set @@global.log_output='table' 将普通查询日志及慢查询日志存入mysql系统数据库中的general 表 以及slow_log 表中。值得注意的是这两个表的存储引擎为CSV,此后查看新的普通查询日志内容时便可以使用SQL语句;

set @@global.log_output = 'table';
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output  | TABLE |
+---------------+-------+

五、MySQL慢查询日志（slow log）

慢查询日志的相关问题，面试的时候面试官非常青睐聊这方面的问题。以前只能够高谈阔论MySQL主从架构，以及从各个方面优化MySQL，但是平时并没有真正的去了解过慢查询如何去开启以及相关的配置。

使用MySQL慢查询日志可以有效的跟踪 执行时间过长 或者 没有使用索引的查询语句。这种包括select 语句，update语句，delete语句，以及insert语句，为优化查询提供帮助。与普通查询日志不同的另一个区别在于，慢查询日志只包含成功执行过的查询语句。与MySQL慢查询日志有关的参数有5个。

1、slow_query_log

slow_query_log 设置慢查询日志是否开启。

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF  |
+----------------+-------+

2、slow_query_log_file

慢查询日志一旦开启，MySQL实例将自动创建慢查询日志文件。slowquerylog_file 所指定的文件，存放慢查询日志内容。 修改方法和上文所示的一致。直接到my.cnf 文件中进行编辑。

3、long_query_time

long_query_time 设置了慢查询的时间阈值。默认阈值是10s。

4、log_quries_not_using_indexes

log_quries_not_using_indexes 是否将不使用索引的查询语句记录到慢查询日志中，无论查询速度有多快。

mysql> set @@global.log_queries_not_using_indexes=1;

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name         | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON  |
+-------------------------------+-------+

5、log_output

设置了普通查询日志以及慢查询日志的输出形式，值有两个file、table;

六、MySQL慢查询日志查看

log_output参数可以设置慢查询日志的输出形式。默认为FILE,可以设置为TABLE;

mysql> desc mysql.slow_log;
+----------------+---------------------+
| Field     | Type        |
+----------------+---------------------+
| start_time   | timestamp      |
| user_host   | mediumtext     |
| query_time   | time        |
| lock_time   | time        |
| rows_sent   | int(11)       |
| rows_examined | int(11)       |
| db       | varchar(512)    |
| last_insert_id | int(11)       |
| insert_id   | int(11)       |
| server_id   | int(10) unsigned  |
| sql_text    | mediumtext     |
| thread_id   | bigint(21) unsigned |
+----------------+---------------------+

其中: lock_time表示该SQL执行时被锁阻塞的时间。 rows_send表示执行SQL后返回的内容行数。 rows_examined表示该SQL执行时实际扫描的记录条数。

但是使用TABLE来存储慢查询日志并不常见，业务量较大的情况下，对于系统的主服务会有影响。我们可以使用FILE 的方式来进行日志存储。安装MySQL的时候在MySQL的bin目录下已经默认安装了mysqldumpslow.pl工具来进行慢查询的日志分析。 window下使用这个工具，可能需要折腾些配置，这不在本文的介绍范围内，学系统服务，还是移步linux吧。 linux下的命令以及工具都可以使用 命令本身 + --help 的选项来查看帮助文档。

-s 表示按照何种方式排序

子选项: c、t、l、r

c : SQL执行的次数
t : 执行时间
l : 锁等待时间
r : 返回数据条数
at、al、ar 是对应 t l r 的平均值。 -t :表示返回前 N 条记录。

-g： grep 缩写。包含模糊匹配

常用法如下：

//返回访问次数最多的20条SQL语句
./mysqldumpslow -s c -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

//返回return记录数最多的20条SQL语句
./mysqldumpslow -s r -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

//返回含有like的SQL语句
./mysqldumpslow -g 'like' 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

七、二进制日志（binary）

二进制日志和前面提到的几种日志不同，二进制不能直接通过cat或者less 文本查看器查看。需要借助专业的工具。二进制日志主要记录数据库的变化情况，因此可以用作主从库的同步。内容主要包括数据库所有的更新操作，use语句、insert语句、delete语句、update语句、create语句、alter语句、drop语句。用一句更简洁易懂的话概括就是：所有涉及数据变动的操作，都要记录进二进制日志中。

启动二进制日志 使用 show variables like 'log_bin'\G 来查看二进制日志是否开启。

mysql> show variables like 'log_bin'\G
*************************** 1. row ***************************
Variable_name: log_bin
    Value: OFF
1 row in set (0.00 sec)

mysql> set @@global.log_bin=1;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
mysql>

看到log_bin 默认是不开启的，并且是个只读的变量，需要在my.cnf中配置，然后重启MySQL。 service mysql restart 重启MySQL后，在data目录会发现生成了一个1.000001的文件。实际上每次MySQL重启，在目录下都会生成一个这样的文件，文件名依次递增。此外，MySQL还会在该目录下创建一个二进制日志的索引文件，可以通过命令show variables like 'log_bin_index'\G来查看索引文件的位置，然后使用cat命令看下。会发现，里面记录着二进制文件的相对路径。

查看二进制日志 可以使用MySQL 中自带的工具。具体位置在mysql的bin目录下。 mysqlbinlog命令的常用选项：

-s                          以精简的方式显示日志内容
-v                          以详细的方式显示日志内容
-d=数据库名                  只显示指定数据库的日志内容
-o=n                        忽略日志中前n行MySQL命令
-r=file                    将指定内容写入指定文件

--start-datetime 
                            显示指定时间范围内的日志内容
--stop-datetime

--start-position       
                            显示指定位置间隔内的日志内容
--stop-position

获取当前使用的二进制日志文件

mysql> show master status;
+----------+----------+--------------+------------------+-------------------+
| File   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------+----------+--------------+------------------+-------------------+
| 1.000002 |   120 |       |         |          |
+----------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

使用二进制日志恢复数据

语法很简单：

mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p

mysqlbinlog 之后可以跟 --start-datetime 、--stop-datetime 、start-position 、stop-position 等参数。

--start-datetime 、--stop-datetime 这两个参数可以基于时间点进行数据恢复；

start-position 、stop-position 可以进行操作点更加细化的进行数据恢复；

MySQL二进制日志相关参数

mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name              | Value        |
+-----------------------------------------+----------------------+
| binlog_cache_size            | 32768        |
| binlog_checksum             | CRC32        |
| binlog_direct_non_transactional_updates | OFF         |
| binlog_error_action           | IGNORE_ERROR     |
| binlog_format              | STATEMENT      |
| binlog_gtid_simple_recovery       | OFF         |
| binlog_max_flush_queue_time       | 0          |
| binlog_order_commits          | ON          |
| binlog_row_image            | FULL         |
| binlog_rows_query_log_events      | OFF         |
| binlog_stmt_cache_size         | 32768        |
| binlogging_impossible_mode       | IGNORE_ERROR     |
| innodb_api_enable_binlog        | OFF         |
| innodb_locks_unsafe_for_binlog     | OFF         |
| max_binlog_cache_size          | 18446744073709547520 |
| max_binlog_size             | 1073741824      |
| max_binlog_stmt_cache_size       | 18446744073709547520 |
| simplified_binlog_gtid_recovery     | OFF         |
| sync_binlog               | 0          |
+-----------------------------------------+----------------------+

max_binlog_size

maxbinlogsize 单个二进制日志文件的大小。如果超过该值，则生成新的文件，后缀名+1；

binlog_cache_size

binlogcachesize 内存中存放二进制日志的缓存大小

sync_binlog

sync_binlog 缓存中写入几次二进制日志，开始同步刷新到外存（硬盘）中。

log_slave_updates

logslvaeupdates 用于主从复制

二进制日志的清理

原则上要先将准备清理的日志通过物理备份的方式备份到其他存储设备上，永久留存。 然后推荐使用下面两种风险较小的清理方式：

第一种：

purge master logs before '2017-02-16 00:00:00';

第二种：

直接在MySQL的配置文件my.cnf 中设置expire_logs_days参数来设置二进制文件的过期天数，过期的二进制文件将会被自动删除。建议在删除前另启一个周期计划任务，定期去备份二进制任务。免得一些数据若干天才发现出现了差错，而二进制日志被自动删除了。

expire_logs_days=90

八 、InnoDB 事务日志

MySQL会最大程度的利用缓存，从而提高数据的访问效率。那么换一句话来说，任何高性能的系统都必须利用到缓存，从各个层面来讲，缓存都发挥了巨大的作用。再上升到一个高度提炼一下：缓存和队列是实现高性能的必走之路。那么对于数据库来说这个却是个很棘手的问题，要保证数据更高效率的读取和存储，所以要利用到缓存。但是要保证数据的一致性，则必须保证所有的数据都必须准确无误的存储到数据库中，及时发生意外，也要保证数据可恢复。我们知道InnoDB是一个事务安全的存储引擎，而一致性是事务ACID中的一个重要特性。InnoDB存储引擎主要是通过InnoDB事务日志实现数据一致性的，InnoDB事务日志包括重做（redo）日志，以及回滚（undo）日志。

InnoDB事务日志与前面提高的日志不同，InnoDB事务日志由InnoDB存储引擎自行维护，而且内容不能被数据库管理员读取。

重做日志（redo）

重做日志主要是记录已经全部完成的事务,即执行了commit的日志，在默认情况下重做日志的值记录在iblogfile0 以及iblogfile1重做日志中。

[root@roverliang data]# pwd
/usr/local/webserver/mysql/data
[root@roverliang data]# ls ib*
ibdata1 ib_logfile0 ib_logfile1

回滚日志（undo）

回滚日志主要记录已经部分完成并且写入硬盘的未完成事务，默认情况情况下，回滚日志的信息记录在表空间文件，共享表空间文件ibdata1或者独享表空间未见ibd中。

由上图我们可以知道，回滚日志默认是记录在ibdta1中的。我的mysql系统版本为：5.6.24.

Checkpoint机制

MySQL服务器崩溃后，重新启动MySQL服务时，由于重做日志（redo）与回滚日志（undo）日志的存在，InnoDB通过回滚日志（undo）日志将所有已部分完成并写入硬盘的未完成事务进行回滚操作（rollback）。然后将重做日志（undo）日志中的事务全部重新执行一遍即可恢复所有的数据。但是数据量过大，为了缩短恢复的时间InnoDB引入了Checkpoint 机制。

脏页（dirty page）

当事务需要修改某条记录是，InnoDB首先将该数据所在的数据块从外存中读取到硬盘中，事务提交后，InnoDB修改数据页中的记录，这时缓存的数据页已经和外存中的数据块已经不一样了，此时缓存中的数据页称为脏页（dirty page）,脏页刷新到外存中，变为干净页（clean page）。

备注：一个内存页默认为4K，或者4K的倍数。你可以把内存想象成一本可以擦洗的书，每次MySQL读数据的时候，向内存申请几张干净的书页，然后书写上去。当数据刷新到硬盘之后，这些数据页马上被擦除，供其他程序使用。

日志序列号（log sequence number）

日志序列号（LSN）是日志空间中每条日志的结束点，用字节偏移量表示，在Checkpoin和恢复时使用。

Checkpoint 机制原理 假设在某个时间点，所有的脏页（dirty page）都被刷新到了硬盘上，这个时间点之前的所有重做日志（redo）就不需要重做了.系统就就下这个时间点重做日志的结尾位置作为Checkpoint,Checkpoint之前的重做日志也就不需要再重做了，可以放心的删除掉。为了更好的利用重做日志（redo）的空间,InnoDb采用轮循的策略使用重做日志空间，因此InnoDB的重做日志文件至少为2个。通过Checkpoint 机制，通过重做日志（redo）将数据库崩溃时已经完成但还没有来得及将缓存中已经修改但还未完全写入外存的事务进行重做（undo）操作，即可保证数据的一致性，也可以缩短恢复时间。

InnoDB重做日志(redo)的参数

innodb_log_buffer_size: 设置了重做日志缓存的大小。
innodb_log_files_in_group : 设置了日志文件组中重做日志（redo）日志的数量。
innodb_log_file_size: 设置了重做日志文件的大小，文件越大，恢复起来耗时越长。
innodb_mirrored_log_groups: 重做日志镜像文件组数量，只能设置为1.
innodb_log_group_home_dir： 设置日志文件组存放的目录，默认在数据库根目录下。

InnoDB回滚日志（undo）的参数

innodb_undo_directory: 设置回滚日志存放的目录。
innodb_undo_logs :设置回滚日志的回滚段大小，默认为128k
innodb_undo_tablespace:设置了回滚日志由多少个回滚日志文件组成，默认为0.
Warning 特别注意：安装MySQL后需要在my.cnf中设置回滚日志的参数，如果创建数据库以后再设置回滚日志的参数，MySQL就会报错，并且回滚日志建好后，就不能再次修改或者增加。

九 、日志文件备份

备份的时候可以使用flush logs，关闭当前的所有日志文件，然后产生新的日志文件。关闭日志文件后，可以采用物理方式备份。 另外flush logs可以添加具体的日志类型：

flush error logs
flush general logs
flush binary logs
flush slow logs