mysql数据库查看日志 mysql数据库日志

MySQL数据库中的日志

• MySQL日志：
• 事务日志(mysql的innodb存储引擎层日志)

• 重做日志（事务日志之redo log）

• 1、redo log的作用
• 2、redo log的内容
• 3、redo log生命周期：redo log什么时候产生
• 4、redo log生命周期：redo log什么时候释放
• 5、redo log对应的物理文件
• 6、redo log是什么时候写盘的？

• 回滚日志（事务日志之undo log）

• 1、undo log作用
• 2、undo log内容
• 3、undo log什么时候产生
• 4、undo log什么时候释放
• 5、undo log对应的物理文件
• 6、undo 表空间

• 总结

• 二进制日志（binlog）（非常重要）

• 二进制日志中常用的定义格式：
• 1、binlog的作用
• 2、binlog的内容
• 3、binlog什么时候产生
• 4、binlog什么时候释放
• 5、对应的物理文件
• 6、binlog和redo log的区别

• 慢查询日志
• 错误日志
• 中继日志：
• 查询日志

MySQL日志：

错误日志(log_error，log_warnings):记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。
查询日志(general_log):记录建立的客户端连接和执行的语句。
二进制日志(binlog):记录所有更改数据的语句，可用于数据复制。
慢速查询日志(log_slow_queries):记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。
中继日志(relay_log):主从复制时使用的日志。
事务日志：innodb_log【重做日志（redo log）、回滚日志（undo log）】

事务日志包括重做日志和回滚日志，从严格意义来讲事务日志属于存储引擎层日志，不属于mysql服务器层日志。
因为mysql是基于可插拔式存储引擎，不同的存储可能就没有事务日志。如MyISAM等非事务存储引擎是没有事务日志的。mysql的事务日志，主要指innoDB存储引擎的重做日志和回滚日志。
其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系，这三种日志，对理解MySQL中的事务操作有着重要的意义。

事务日志(mysql的innodb存储引擎层日志)

事务日志（InnoDB特有的日志）可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把改修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的，我们通常称之为预写式日志，修改数据需要写两次磁盘。

如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具有的恢复方式则视存储引擎而定。

重做日志（事务日志之redo log）

1、redo log的作用

确保事务的持久性。

防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

2、redo log的内容

物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序I/O写入redo log file的物理文件中去的。

3、redo log生命周期：redo log什么时候产生

事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。

4、redo log生命周期：redo log什么时候释放

当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

5、redo log对应的物理文件

默认情况下，对应的物理文件位于数据库的data目录下的ib_logfile1&ib_logfile2

innodb_log_group_home_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。

innodb_log_files_in_group 指定重做日志文件组中文件的数量，默认2

关于文件的大小和数量，由一下两个参数配置

innodb_log_file_size 重做日志文件的大小。

innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认1

6、redo log是什么时候写盘的？

很重要一点，redo log是什么时候写盘的？前面说了是在事物开始之后逐步写盘的。

之所以说重做日志是在事务开始之后逐步写入重做日志文件，而不一定是事务提交才写入重做日志缓存，

原因就是，重做日志有一个缓存区Innodb_log_buffer，Innodb_log_buffer的默认大小为8M(这里设置的16M),Innodb存储引擎先将重做日志写入innodb_log_buffer中。

然后会通过以下三种方式将innodb日志缓冲区的日志刷新到磁盘

1，Master Thread 每秒一次执行刷新Innodb_log_buffer到重做日志文件。

2，每个事务提交时会将重做日志刷新到重做日志文件。

3，当重做日志缓存可用空间 少于一半时，重做日志缓存被刷新到重做日志文件

由此可以看出，重做日志通过不止一种方式写入到磁盘，尤其是对于第一种方式，Innodb_log_buffer到重做日志文件是Master Thread线程的定时任务。

因此重做日志的写盘，并不一定是随着事务的提交才写入重做日志文件的，而是随着事务的开始，逐步开始的。

另外引用《MySQL技术内幕 Innodb 存储引擎》（page37）上的原话：

即使某个事务还没有提交，Innodb存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。

这一点是必须要知道的，因为这可以很好地解释再大的事务的提交（commit）的时间也是很短暂的。

回滚日志（事务日志之undo log）

1、undo log作用

保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

2、undo log内容

逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

3、undo log什么时候产生

事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性

4、undo log什么时候释放

当事务提交之后，undo log并不能立马被删除，

而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

5、undo log对应的物理文件

MySQL5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间的默认的名称是ibdata，位于数据文件目录中。

MySQL5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数

如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。

关于MySQL5.7之后的独立undo 表空间配置参数如下

innodb_undo_directory = /data/undospace/ –undo独立表空间的存放目录

innodb_undo_logs = 128 –回滚段为128KB

innodb_undo_tablespaces = 4 –指定有4个undo log文件

如果undo使用的共享表空间，这个共享表空间中又不仅仅是存储了undo的信息，共享表空间的默认为与MySQL的数据目录下面，其属性由参数innodb_data_file_path配置。

6、undo 表空间

undo是在事务开始之前保存的被修改数据的一个版本，产生undo日志的时候，同样会伴随类似于保护事务持久化机制的redolog的产生。

默认情况下undo文件是保持在共享表空间的，也即ibdatafile文件中，当数据库中发生一些大的事务性操作的时候，要生成大量的undo信息，全部保存在共享表空间中的。

因此共享表空间可能会变的很大，默认情况下，也就是undo 日志使用共享表空间的时候，被“撑大”的共享表空间是不会也不能自动收缩的。

因此，mysql5.7之后的“独立undo 表空间”的配置就显得很有必要了。

总结

1》事务型存储引擎innodb用于保证事务特性的日志文件，redo log 和undo log 
    2》出于性能和故障恢复的考虑，MySQL 服务器不会立即执行事务，而是先将事务记录在日志里面，这样可以将随机I/O转换成顺序I/O，从而提高I/O性能。
    3》事物日志默认情况下会有两个文件，名称分别为ib_logfile0和ib_logfile1。当其中一个写满时，MySQL会将事务日志写入另一个日志文件(先清空原有内容)。
    4》当 MySQL 从崩溃中恢复时，会读取事务日志，将其中已经 commit 的事务写入数据库，没有 commit 的事务 rollback 。
    5》在事物提交时，innodb是否将缓冲到文件中同步，只要提交则立刻同步，同时又不会保证每个语句都同步，因此性能不会有特别大的影响

二进制日志（binlog）（非常重要）

二进制日志也叫作变更日志，主要用于记录修改数据或有可能引起数据改变的mysql语句，并且记录了语句发生时间、执行时长、操作的数据等等。所以说通过二进制日志可以查询mysql数据库中进行了哪些变化。一般大小体积上限为1G。

二进制日志包含了引起或可能引起数据库改变(如delete语句但没有匹配行)的事件信息，但绝不会包括select和show这样的查询语句。语句以"事件"的形式保存，所以包含了时间、事件开始和结束位置等信息。

二进制日志是以事件形式记录的，不是事务日志(但可能是基于事务来记录二进制日志)，不代表它只记录innodb日志，myisam表也一样有二进制日志。

二进制日志只在事务提交的时候一次性写入(基于事务的innodb二进制日志)。

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。

1	[mysqld]
2	# server_id=1234
3	log-bin=[on filename]

二进制日志文件的最大值通过变量 max_binlog_size 设置(默认值为1G)。但由于二进制日志可能是基于事务来记录的(如innodb表类型)，而事务是绝对不可能也不应该跨文件记录的，如果正好二进制日志文件达到了最大值但事务还没有提交则不会滚动日志，而是继续增大日志，所以 max_binlog_size 指定的值和实际的二进制日志大小不一定相等。

因为二进制日志文件增长迅速，但官方说明因此而损耗的性能小于1%，且二进制目的是为了恢复定点数据库和主从复制，所以出于安全和功能考虑，极不建议将二进制日志和datadir放在同一磁盘上。

二进制日志中常用的定义格式：

1、语句(statement)：默认的记录格式；

2、行(row)：定义的并非数据本身而是这一行的数据是什么；

3、混合模式(mixed)：交替使用行和语句、由mysql服务器自行判断。

其中基于行的定义格式数据量会大一些但是可以保证数据的精确性。

1、binlog的作用

用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步；

用于数据库的基于时间点的还原；

2、binlog的内容

逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。

但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，

也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。

在使用mysqlbinlog解析binlog之后一些都会真相大白。

因此可以基于binlog做到类似于oracle的闪回功能，其实都是依赖于binlog中的日志记录。

3、binlog什么时候产生

事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。

这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。

因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。

这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

4、binlog什么时候释放

binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

5、对应的物理文件

配置文件的路径为log_bin_basename，binlog日志文件按照指定大小，当日志文件达到指定的最大的大小之后，进行滚动更新，生成新的日志文件。

对于每个binlog日志文件，通过一个统一的index文件来组织。

6、binlog和redo log的区别

二进制日志的作用之一是还原数据库的，这与redo log很类似，很多人混淆过，但是两者有本质的不同：

作用不同：
redo log是保证事务的持久性的，是事务层面的，binlog作为还原的功能，是数据库层面的（当然也可以精确到事务层面的），虽然都有还原的意思，但是其保护数据的层次是不一样的。

内容不同：redo log是物理日志，是数据页面的修改之后的物理记录，binlog是逻辑日志，可以简单认为记录的就是sql语句

另外，两者日志产生的时间，可以释放的时间，在可释放的情况下清理机制，都是完全不同的。

恢复数据时候的效率，基于物理日志的redo log恢复数据的效率要高于语句逻辑日志的binlog

关于事务提交时，redo log和binlog的写入顺序，为了保证主从复制时候的主从一致（当然也包括使用binlog进行基于时间点还原的情况），是要严格一致的，

MySQL通过两阶段提交过程来完成事务的一致性的，也即redo log和binlog的一致性的，理论上是先写redo log，再写binlog，两个日志都提交成功（刷入磁盘），事务才算真正的完成。

慢查询日志

查询超出变量 long_query_time 指定时间值的为慢查询。但是查询获取锁(包括锁等待)的时间不计入查询时间内。

mysql记录慢查询日志是在查询执行完毕且已经完全释放锁之后才记录的，因此慢查询日志记录的顺序和执行的SQL查询语句顺序可能会不一致(例如语句1先执行，查询速度慢，语句2后执行，但查询速度快，则语句2先记录)。

注意，MySQL 5.1之后就支持微秒级的慢查询超时时长，对于DBA来说，一个查询运行0.5秒和运行0.05秒是非常不同的，前者可能索引使用错误或者走了表扫描，后者可能索引使用正确。

另外，指定的慢查询超时时长表示的是超出这个时间的才算是慢查询，等于这个时间的不会记录。

和慢查询有关的变量：

1 long_query_time=10 # 指定慢查询超时时长(默认10秒)，超出此时长的属于慢查询
2 log_output={TABLE|FILE|NONE} # 定义一般查询日志和慢查询日志的输出格式，默认为file
3 log_slow_queries={yes|no}    # 是否启用慢查询日志，默认不启用
4 slow_query_log={1|ON|0|OFF}  # 也是是否启用慢查询日志，此变量和log_slow_queries修改一个另一个同时变化
5 slow_query_log_file=/mydata/data/hostname-slow.log  #默认路径为库文件目录下主机名加上-slow.log
6 log_queries_not_using_indexes=OFF # 查询没有使用索引的时候是否也记入慢查询日志

现在启用慢查询日志。

mysql> set @@global.slow_query_log=on;
随着时间的推移，慢查询日志文件中的记录可能会变得非常多，这对于分析查询来说是非常困难的。好在提供了一个专门归类慢查询日志的工具mysqldumpslow。
慢查询在SQL语句调优的时候非常有用，应该将它启用起来。

错误日志

错误日志本身所定义的内容本身是可以定义的 。错误日志是最重要的日志之一，它记录了MariaDB/MySQL服务启动和停止正确和错误的信息，还记录了mysqld实例运行过程中发生的错误事件信息。

错误日志记录的信息：
        1》mysqld启动和关闭过程中输出的信息。
            未必是错误信息，比如mysql是如何去初始化存储引擎的过程记录在错误日志里等等
        2》mysqld运行中产生的错误信息。
            比如sock文件找不到，无法加载mysql数据库的数据文件，如果忘记初始化mysql或data dir路径找不到，或权限不正确等，都会记录在此。
        3》事件调度器（event scheduler）运行时产生的信息。
            一旦mysql调度启动一个计划任务的时候，它也会将相关信息记录在错误日志中。
        4》 主从复制架构中，从服务器复制线程启动时产生的日志；

中继日志：

中继日志用于主从复制架构中的从服务器上，从服务器的 slave 进程从主服务器处获取二进制日志的内容并写入中继日志，然后由 I/O 进程读取并执行中继日志中的语句。

查询日志

查询日志分为一般查询日志和慢查询日志，它们是通过查询是否超出变量 long_query_time 指定时间的值来判定的。在超时时间内完成的查询是一般查询，可以将其记录到一般查询日志中，但是建议关闭这种日志（默认是关闭的），超出时间的查询是慢查询，可以将其记录到慢查询日志中。