第四章 MySQL的日志类型

一 MySQL常用日志类型：

　　错误日志（error-log）：记录mysql在启动、运行和停止时出现的问题诊断分析

　　　　使用场景: a.分析排查mysql运行错误; b.记录未经授权的访问

　　　　常用配置参数:

log_error=$mysql/sql_log/mysql-error.log
log_error_verbosity=[1,2,3]   //5.7新增  错误日志中记录错误信息的级别;
//解释：
1：Error messages
2:Error and warning messages
3:Error,waring and note message

 　　　　log_error_services=[日志服务组件；日志服务组件] 8.0新增

　　　　常用错误日志的服务组件：

　　　　常用配置参数：

log_filter_internal: //默认的日志过滤组件，依赖log_error_verbosity
log_sink_internal:   //默认的日志输出组件，依赖log_error
log_sink_json:       //将错误日志输出到json文件
log_sink_syseventlog://将错误日志输出到系统日志文件

 

　　常规日志（general_log）：记录所有mysql客户端发向mysql服务器的请求。包括连接请求、数据库操作请求以及一些管理命令，无论请求是否成功都会记录在这个日志中，可想如果是一个繁忙的业务系统场景，这个日志会很大，所以开启这个日志会对数据库服务器性能会有一定的影响，在业务系统中用完一定要记得关掉和清理这个日志中的内容

　　　　常用配置参数:　　　　

general_log = [ON|OFF]/[1/0]
general_log_file = $mysql/sql_log/general.log
log_output = [file|table|none]
show variables like 'general_log';

 

　　慢查询日志(slow_query_log)：记录符合条件的查询

　　　　使用场景: a.将执行成功并符合条件的查询记录到日志中; b.找到需要优化的sql

　　　　常用配置参数:

slow_query_log=[on|off]
slow_query_log_file=$mysql/sql_log/slowlog.log
long_query_time=XX秒 //（可以精确到微秒）查询超过这个值才会被记录到slowlog中，互联网行业中一般设置0.001s比较合适。
set global long_query_time=0.001 //只对新建立的连接起作用。设置为0 是记录所有sql.
log_queries_not_using_indexes=[on|off]
log_slow_admin_statements=[on|off]//设置为on时将会记录管理类命令比如create drop等。
log_slow_slave_statements=[on|off] //前提要开启binlog

　　二进制日志（binary_log）：记录全部有效的数据修改日志，是server层的日志，又称为归档日志，属于逻辑日志；作为恢复数据和主从复制

　　　　使用场景: a.记录所有对数据库中数据的修改; b.基于时间点的备份和恢复; c.高可用架构的基础-主从复制

　　　　常用配置参数:

log_bin =  [base_name]  //静态参数 只能在my.cnf中配置 重启服务后生效。 5.7之前一般不会启用这个参数。
binlog_format = [row/statement/mixed] //动态参数。 5.7之后默认row 主从复制中安全性比较高
binlog_row_image = [full/minimal/noblob]  minimal //只会记录被修改的值，noblob会记录除blob外的所有修改前修改后的值。full没修改的列对应值也会记录。
set session binlog_row_image = 'minimal';
binlog_rows_query_log_events = [on|off]  //会同时记录执行的sql语句
log_slave_updates = [on|off]   //默认情况下slave不会记录同步主库过来的binlog日志，有些情况比如级联复制  slave是另一个mysql实例的主，还有需要gtid复制。都需要记录从主从同步过来的binlog日志。
sync_binlog = [1|0]  //控制如何刷新二进制日志到磁盘，mysql每写一次二进制日志都会向磁盘刷新一次。
//二进制日志的清除:
expire_logs_days = days  //动态参数
purge binary logs to 'mysql-bin.010' //将指定日志前的二进制日志都删除。
purge binary logs before 'yyyy-mm-dd hh24:mi:ss';//删除指定日期前的日志。

 
　　中继日志（relay_log）：用于主从复制，临时存储从主库同步的二进制日志

　　　　常用配置参数:

relay_log = filename  //指定relay log存放位置和名称。
relay_log_purge = [on|off]   //自动清理

　　重做日志（redo log）：属于innodb事务日志，通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)

　　　　使用场景: 确保事务的持久性,当发生数据修改的时候，innodb引擎会先将记录写到redo log中， 并更新内存，此时更新就算是完成了，同时innodb引擎会在合适 的时机将记录操作到磁盘中.redolog是固定大小的，是循环写的过程,有了redolog之后，innodb就可以保证即使数据库发生异常重启， 之前的记录也不会丢失，叫做crash-safe.默认情况下，对应的物理文件位于数据库的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

 
　　回滚日志（undo log）：属于innodb事务日志，用来回滚行记录到某个版本；一般是逻辑日志，根据每行记录进行记录

　　　　使用场景: undo log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中， 还用UndoLog来实现多版本并发控制(简称：MVCC)。

　　　　　　　　 在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方 称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了 ROLLBACK语句，系统可以利用UndoLog中的备份将数据恢复到事务开始之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

　　　　　　　　注意：undo log是逻辑日志，可以理解为：当delete一条记录时，undo log中会记录一条对应的insert记录;当insert一条记录时，undo log中会记录一条对应的delete记录;当update一条记录时，它记录一条对应相反的update记录

　　　　　　　　MySQL5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间的默认的名称是ibdata，位于数据文件目录中。MySQL5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数.如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。

　　　　常用配置参数:

innodb_undo_directory = /data/undospace/ //undo独立表空间的存放目录
innodb_undo_logs = 128 //回滚段为128KB
innodb_undo_tablespaces = 4 //指定有4个undo log文件
//如果undo使用的共享表空间，这个共享表空间中又不仅仅是存储了undo的信息，共享表空间的默认为与MySQL的数据目录下

 

二 要避免io，为什么写重做日志(redo log)的时候不会造成io的问题

　　redo log包括两部分： 一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；

　　　　　　　　　　　　二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的

　　在概念上，innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。为了确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作(即fsync()系统调用)。因为MariaDB/MySQL是工作在用户空间的，MariaDB/MySQL的log buffer处于用户空间的内存中。要写入到磁盘上的log file中，中间还要经过操作系统内核空间的os buffer，调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中。从redo log buffer写日志到磁盘的redo log file中，过程如下:

　　MySQL支持用户自定义在commit时如何将log buffer中的日志刷log file中。这种控制通过变量 innodb_flush_log_at_trx_commit 的值来决定。该变量有3种值：0、1、2，默认为1。但注意，这个变量只是控制commit动作是否刷新log buffer到磁盘：

　　当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差；

　　当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据；

　　当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk

 

redo log的两阶段提交

　　先写redo log后写binlog:假设在redo log写完，binlog还没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。

　　先写binlog后写redo log:如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值 就是1，与原库的值不同

三 MySQL的数据更新流程:

　　1、执行器先从引擎中找到数据，如果在内存中直接返回，如果不在内存中，查询后返回

　　2、执行器拿到数据之后会先修改数据， 然后调用引擎接口重新写入数据

　　3、引擎将数据更新到内存，同时写数据到redo中，此时处于prepare（准备）阶段，并通知执行器执行完成，随时可以操作

　　4、执行器生成这个操作的binlog

　　5、执行器调用引擎的事务提交接口，引 擎把刚刚写完的redo改成commit状态， 更新完成