mysql日志

日志简介

mysql常见日志分为以下几种

mysql默认情况下所有日志均为关闭,默认位置为mysql的data目录下

日志类型

错误日志

错误日志是mysql中最重要的日志之一,它记录了当mysqld启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息.当数据库出现任何故障导致无法正常使用时,可以首先查看此日志.

该日志是默认开启的,存放目录为mysql的数据目录(var/lib/mysql),默认的日志文件名为

show variables like 'log_error%';

二进制日志(binlog)

概述

mysql的二进制日志是非常重要的日志,它记录着所有的ddl和dml语句,以事件形式记录,还包含所执行的消耗的时间.

DDL:数据库定义语言

DML:数据库操作语言(不包记录查询)

可以用于灾难时,对数据库进行恢复和数据库的主从复制

二进制日志是没有默认开启的

Linux下MySQL的配置文件是my.cnf，一般会放在/etc/my.cnf，/etc/mysql/my.cnf

log_bin=mysqlbin
binlog_format=STATEMET

日志格式

STATEMENT

该日志格式再日志文件中记录的都是sql语句，每一条对数据库的sql都会记录在日志文件中，通过mysql提供的mysqlbinlog根据，可以清晰的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并且从库中重新执行一次

ROW

该日志格式在日志文件中记录的是每一行数据的变更,而不是记录sql语句.

MIXED

这是目前MYSQL默认的日志格式,混合了statement和row俩种格式.默认情况下采用statement,但在一些特殊的情况下采用row来进行记录

查看

日志默认存放在/var/lib/mysql目录下

可以通过mysqlbinlog 日志文件名来查看日志文件

如果查看的日志格式是row,可以在mysqlbinlog后面加上参数-vv

日志删除

业务比较繁忙的系统,由于每天生成的日志量大,如果这些日志长时间不清除,将会占用大量的磁盘空间.下面介绍几种常见的删除方式

方式一

通过reset master指令删除全部日志,删除之后,日志编号从xxx.000001重新开始

方式二

执行指令purge master logs to 'mysqlbin.******'将命令删除******编号之前的所有日志

方式三

执行指令pirge master logs before 'yyy-mm-dd hh24:mi:ss'该命令将删除日志为yyy-mm-dd hh24:mi:ss之前的所有日志

方式四

执行指令--expire_logs_days=#,此参数的含义是设置日志的过期天数,过了指定的天数后日志将会被自动删除,这样有利于减少DBA管理日志的工作量

配置如下

log_bin=mysqlbin
binlog_format_ROW
--expire_logs_days=3

查询日志

查询日志中激励了客户端所有操作语句,而二进制日志不包含查询的sql语句.

无论这些查询或是命令是否正确甚至是包含于法错误general log都会记录下来

默认情况下查询日志是不开启的.如果需要开启查询日志,可以设置以下配置

#该选项用来开启慢查询日志 0代表关闭,1带表开启
general_log=1
#设置日志的文件名,如果没有指定默认的文件名为host_name.log
general_log_file=mysql_query.log

默认存放在mysql数据文件下/var/lib/mysql

慢查询日志

概述

mysql慢查询日志是mysql提供的一种日志记录,它用来记录再mysql中响应时间超过阈值的语句,具体指运行时间超过long_query_time值的sql,则会被记录到慢查询日志当中

ong_query_time的默认值为10s

默认情况下mysql不会启动慢查询日志,需要我们手动来设置这个参数

如果不需要调优的话一般不建议启动这个参数,因为开启慢查询日志多少会对性能产生一定的影响.

慢查询支持将日志写入文件,也支持将日志记录数据库表

操作

查看是否开启慢查询日志show variables like '%slow_Query_log%';

开启:setglobal slow_query_log=1;

只对当前数据库生效,如果重启后会失效,永久修改需要修改配置文件

show_query_log=1
show_query_log_file=地址

操作

show variables like 'long_query_time' #查看超时时间
setgloballong_query_time=4; #设置超时时间,要断开连接才能生效
select sleep(5) #查询的时候睡眠5秒
show global status like '%slow_queries%';

得到返回记录集最多的10个sql

mysqldumpslow命令

参数

• -s:按照哪种方式排序
• c:访问计数
• I:锁定时间
• r:返回记录
• al:平均时间
• ar:平均访问记录数
• at:平均查询时间
• -t:是top n的元素,返回多少条数据
• -g:可以跟上正则表达式,大小写不敏感

实例

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log #得到返回记录最多的10个sql

重做日志(redolog)

简介

redolog

确保事务的持久性.

防止在发生故障的时间点,尚有脏页未写入磁盘,再重启mysql服务的时候根据redolog进行重做,从而达到事务的持久性.

物理格式的日志,记录的是物理数据页面的修改信息,redolog是顺序写入redo log file的物理文件中的.

当对应事务的脏页写入到磁盘后,我redolog的使命也就完成了,重做日志占用的空间就可以重用.

当内存数据页和磁盘数据页上的内容不一致时，我们称这个内存页为脏页；
内存数据写入磁盘后，内存页上的数据和磁盘页上的数据就一致了，我们称这个内存页为干净页。

与binlog区别

二进制日志是在存储引擎上产生的,不管是什么存储引擎,对数据库进行了修改都会产生二进制日志.而redolog是innodb层产生的,只记录存储引擎中表的修改.

二进制日志记录操作的方法时逻辑性的语句.几遍它是基于格式的记录方式其本质还是逻辑的sql设置,如该行记录的值是多少.而redolog是在物理格式上的日志,它记录的是数据库中每个页的修改

二进制日志只在每次事务提交的时候一次性写入缓存中的日志文件(非事务操作,则是每次执行语句成功后就直接写入).而redo log在数据库准备修改前羞辱缓存中的redolog中,如何才对缓存中的数据执行修改操作;而且保证在发出事务提交指令时,先想缓存中的redolog写入日志,写入完成后才执行提交动作.

事务日志记录的是物理页的情况,它具有幂等性,因此记录日志的方式及其简练,迷瞪的意思是多次操作前后状态是一样的,列入新插入一行后删除该行,前后状态没有变化.而二进制日志记录的是所有影响数据的操作,记录的内容较多.列入插入一行记录一次,删除该行又记录一次

总结

• redolog日志的大小是固定的,记录满了就从头开始循环写
• redo log是属于innoDB层面，binlog属于MySQL Server层面的
• redo log是物理日志,记录该数据页更新的内容;binlog是逻辑日志,记录的是这个更新语句的原始逻辑.
• redolog是循环写,日志空间大小固定;binlog是追加写,是一份写到一定大小的时候会更换下一个文件,不会覆盖
• binlog可以作为恢复数据使用,主从复制搭建.redolog作为一场宕机或者截止故障后的数据恢复使用

基本概念

redolog包括俩部分:以是内存中的日志缓冲(redologbuffer),该部分日志是易失性的;二是磁盘上的重做日志文件(redologfile),该部分日志是持久性的.

在概念上innodb通过force log at commit机制实现事务的持久性,即在事务提交的时候,必须先将该事物的所有日志写入到磁盘上的redo logfile和undo logfile中进行持久化.

与缓冲池的联系

mysql，如果每次更新操作都要写进磁盘，然后磁盘要找到对应记录，然后再更细，整个过程io成本、查找成本都很高。

具体来说,当有一条记录需要更新的时候(在缓存中中),innodb引擎就会先把记录写到redo log里面,并更新1内存,这个时候更新就算完成了.同时innodb引擎会在适当的时候,(checkpoint机制)

例子

假如要修改页号为4的索引页,而这个页面正好在缓冲池中

会直接修改内存(一次内存操作)

写入redo log一次磁盘顺序操作(开销小)

那么是否会出现一致性问题呢,不会

• 读取,会命中缓冲池中的页
• 数据在合适的时间,会将脏页刷会磁盘,
• 数据库崩溃,能够从redolog中恢复数据

定期刷磁盘而不是每次刷磁盘,能够降低磁盘io,提升mysql的性能

写入

redolog是在事务开始之后逐步写盘的.

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

是因为1有一个缓冲区log_buffer,默认大小为8M,innodb存储引擎先将重做日志写入缓冲区中

mysql支持用户在commit时如何将logbuffer中的日志刷到logfile中.通过innodb_flush_log_at_arx_commit来决定.该变量有三种值:0,1,2默认为1,注意这个变量只是控制commit动作是否刷新log_buffer到磁盘

fsync:fsync函数同步内存中所有已修改的文件数据到储存设备。

为了确保每次日志都能写入到事务日志文件中,在每次讲log_buffer中的日志写入日志文件的过程中都会调用操作系统的fsync操作,因为mysql是工作在用户控件的log_buffer处于用户空间的内存中,要写入到磁盘的logfile中,还要经过操作系统内核空间的os _buffer,调用fsync()的作用就是讲OS buffer中的日志刷到磁盘上的logfile中:过程如下

之所以要经过一层os buffer，是因为open日志文件的时候，open没有使用O_DIRECT标志位，该标志位意味着绕过操作系统层的os buffer，IO直写到底层存储设备。不使用该标志位意味着将日志进行缓冲，缓冲到了一定容量，或者显式fsync()才会将缓冲中的刷到存储设备。使用该标志位意味着每次都要发起系统调用。比如写abcde，不使用o_direct将只发起一次系统调用，使用o_object将发起5次系统调用。

• 当设置为1的时候,我每次事务提交都会讲log_buffer中的日志写入os buffer并调用fsync()刷到磁盘中,这种方式即使崩溃也不会丢失任何数据,但是因为每次提交都写入磁盘,IO的性能较差
• 当设置为0时候,事务的提交不会讲log_buffer中的日志写入osbuffer并调用fsync()刷到os buffer,而是每秒写入osbuffer并调用fsync()写入到磁盘中.当系统崩溃会丢失1秒的数据
• 当设置为2的时候,每次提交都仅写入到os buffer,任何每秒调用fsync()将os buffer中的日志写入到磁盘

在主从复制结构中，要保证事务的持久性和一致性，需要对日志相关变量设置为如下：

• 如果启用了二进制日志，则设置sync_binlog=1，即每提交一次事务同步写到磁盘中。
• 总是设置innodb_flush_log_at_trx_commit=1,即每一次事务提交都写入磁盘中

lsn

lsn称为日志的逻辑序列化(log sequence number)在innodb存储引擎中,lsn占用8个字节.lsn的值会随着日志的写入而逐渐增大.

根据lsn可以获取到几个有用的信息

• 数据也的版本信息
• 写入的日志总量,通过lsn开始号码和结束的号码可以计算出写入的日志量
• 可指导检查点的位置

innodb的恢复

在启动innodb的时候,不管上次是正常关闭还是异常关闭都会进行恢复操作

因为redo log记录是数据页的物理变化,因此恢复的时候速度比逻辑日志(二进制日志)要快很多,而且innodb也做了一定程度的优化,让恢复速度变得更加快.

重启innodb时,checkpoint表示已经完整刷到磁盘上data page上的LSN，因此恢复时仅需要恢复从checkpoint开始的日志部分。例如，当数据库在上一次checkpoint的LSN为10000时宕机，且事务是已经提交过的状态。启动数据库时会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从检查点开始恢复。

还有一种情况，在宕机前正处于checkpoint的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度。这时候一宕机，数据页中记录的LSN就会大于日志页中的LSN，在重启的恢复过程中会检查到这一情况，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

另外，事务日志具有幂等性，所以多次操作得到同一结果的行为在日志中只记录一次。而二进制日志不具有幂等性，多次操作会全部记录下来，在恢复的时候会多次执行二进制日志中的记录，速度就慢得多。例如，某记录中id初始值为2，通过update将值设置为了3，后来又设置成了2，在事务日志中记录的将是无变化的页，根本无需恢复；而二进制会记录下两次update操作，恢复时也将执行这两次update操作，速度比事务日志恢复更慢。

参考文章:https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html

回滚日志(undolog)

简介

undologyou俩个作用:提供回滚和多个版本控制(mvcc)

在数据修改的时候,不仅记录了redo,还记录了相对于的undo,如果业务默写原因导致事务失败或者回滚了,可以借助该undo进行回滚.

undo log和redo log记录物理日志不一样,它是逻辑日志.可以认为当delete一条记录时,undolog中会记录一条对应的insert记录,反之亦然,当update一条记录时,它记录一条对应相反的update记录

当执行rollback(回滚),就可以从undolog中的逻辑读取到响应的内容并进行回滚.有时候应用到版本控制的时候,也是通过undolog来实现的:当读取的某一行被其他事务锁定时,他可以从undolog中分析出该行以前的记录数据是什么,从而提供版本信息,让用户实现非锁定一致性读取.

undolog是采用短(segment)的方式来记录的,每个undol操作记录的时候占用一个undo log segment

另外undolog也会产生redolog,因为undolog也要实现持久性维护.

undolog的存储方式

innodb存储引擎对undo的管理采用段的方式.rollback segment称为回滚段,每个回滚段中又1024个undo log segment.

在以前老版本支持rollback segment,这样就只能记录1024个undo log segment.后来5.5可以支持128个rollback segment,及支持128*1024个undo操作,还可以通过变量 innodb_undo_logs (5.6版本以前该变量是 innodb_rollback_segments )自定义多少个rollback segment，默认值为128。

内部机制

事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性,当事物提交的时候,innodb不会立即删除undolog,因为后续可能还会用到undolog,如隔离级别为可重复读的时候,事务读取的时候都是开启事务时的最新提交版本,只要该事物不结束,该行版本就不能删除,即undolog不能删除

但是在事务提交的时候,会将事务对应的undolog放入到删除列表中,未来通过purge.并且提交事务时,还会判断undo log分配的页是否还可以重用,如果可以重用,则会分配给后面来的事务,避免为每个独立的事务分配独立的undolog页而浪费存储空间的性能.

• insert操作无需分析,就是插入行而已
• delete操作实际上不会直接删除,而是将delte对象打上delete flag,标记为删除,最终的删除操作是purge线程完成的.

• update分为俩种情况:update是否是主键

  • 如果不是主键列,在undo log中直接反向记录是如何update的.即update是直接进行的
  • 如果是主键列,update分成俩部执行,先删除该行,在插入一行目标行

事务日志的先后顺序

如果事物不是只读事务,涉及到了数据的修改,默认情况下会泽commit调用的时候fsync()将日志刷到磁盘,保证事务的持久性.

但是一次刷一个事务的日志性能较低,特别是事务集中在摸一个时刻时事务量非常大的时候,innodb提供了group commit给你,可以将多个事务的日志通过一次fsync()刷到磁盘中。

5.6中的三个步骤flush阶段，sync阶段，commit阶段

• flush阶段：像内存中写入每个事物的二进制文件
• sync阶段:将内存中的二进制日志刷盘,若队列中有多个事务,那么仅一次sync操作就完成了二进制日志的刷盘操作
• commit阶段:leader根据顺序调用存储引擎层提交

对应的物理文件

对应的物理文件：
　　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配置。

总结

redo log

重做日志

作用:确保事务的持久性.防止在发生故障的时间点尚有脏页未写入磁盘,在重启mysql服务的时候,根据redolog进行重做,从而达到事务持久这一特性

内容:物理个事的日志,记录的是物理数据页面的修改信息,其redo log是顺序写入redo log file的物理文件去的

binlog

二进制日志

作用:用于复制,在主从复制中,从库利用主库上的binlog进行重播,实现主从同步.用于数据库的基于时间点还原.

内容:逻辑格式的文件,增删改的sql语句

binlog 有三种模式：Statement（基于 SQL 语句的复制）、Row（基于行的复制） 以及 Mixed（混合模式）

undolog

回滚日志

作用:保存了事务发生之前的数据版本,可用于回滚,实现了MVCC

内容:逻辑格式的日志,在执行undo的时候,仅仅是将数据从逻辑上恢复至事务之前的状态,而不是从屋里页面上操作实现的.

二段式提交

俩段式提交,就是我们先把这次更新写入到redolog中并设置为prepare状态,然后再写入binlog,写完binlog之后再提交事务,并设置redolog为commit状态,也就是把redolog拆分成了prepare俩段.

你假设一下如果先保存好redolog,然后再记录binlog。如果redolog写好了之后挂了。ok你看起来好像是没问题了，但是你的binlog还没记录，所以这条记录就少了！如果你备份这份binlog之后，你这条记录就永远的少了！

那如果先写binlog再写redolog呢？那binlog写完了，你数据库挂了，那redolog是不是没有，没有的意思就是你以前你没更新成功。但是binlog已经记录好了，在它那边反正是成功了，所以那备份的binlog也不对！

checkpoint(检查点)机制

简介

checkpoint将缓冲池中的脏页刷回磁盘

思考一下这个场景:如果重做(redolog)日志可以无限增大,同时缓冲池也足够大,那么是不需要将缓冲池中也的新版本刷回磁盘.因为发生宕机的时候,完全可以通过重做日志文件来恢复整个数据库系统的中的数据到宕机发生的时刻.

但这需要俩个前提条件:缓冲池可以缓存数据库中的所有数据,2,日志可以无限增大(nosql)

因此checkpoint(检查点)技术诞生了,目的是解决以下几个问题

• 缩短数据库的恢复时间:数据库发生宕机的时,数据库不需要重做所有的日志,因为checkpoint 之前的页都已经刷新到磁盘.数据库只需要对checkpoint后的重做日志进行恢复,这样大大缩短了恢复时间
• 缓存不够用的时候,将脏页刷新到磁盘:根据LRU算法会移除最近最少使用的页,若此页为脏页,那么强制执行checkpoint,也就是将页的新版本刷回磁盘
• 重做日志不可用时,刷新脏页:因为当前事务数据库系统对重做日志的设计都是循环使用的,并不是让其无线增大的,重做日志可以被重用的部分市直这些重做日志语句不再需要,当数据库发生宕机时,数据库恢复操作不需要这部分的重做日志,因此这部分可以被覆盖重用.如果重做日志还需要使用,那么必须强制checkpoint,将缓冲池的页至少刷新到当前重做日志的位置

LRU（Least recently used，最近最少使用）算法根据数据的历史访问记录来进行淘汰数据，其核心思想是“如果数据最近被访问过，那么将来被访问的几率也更高”。

对于innodb而言,是通过lsn来标记版本的

lsn是把自己的数字,每个页有LSN,重做日志有LSN,Checkpoint也有LSN.可以通过SHOW ENGINE INNODB STATUS来观察

Checkpoint发生的时间、条件及脏页的选择等都非常复杂。而Checkpoint所做的事情无外乎是将缓冲池中的脏页刷回到磁盘，不同之处在于每次刷新多少页到磁盘，每次从哪里取脏页，以及什么时间触发Checkpoint。

分类

innodb存储引擎内部有俩种checkpoint,分别为sharp checkpoint,fuzzy checkpoint

sharp checkpoint:完全检查点,数据库正常关闭时,会出发吧所有脏页写入到磁盘上(这时候redolog的日志已经没用了,脏页语句写到磁盘上了)

• 完全检查点发生在数据库正穿关闭的时候
• 在数据库运行时,不会使用sharp ceckpoint,在引擎内部使用fuzzy checkpoint,即只刷新一部分脏页,而不是刷新所有脏页回磁盘

fuzzy checkpoint:模糊检查点,部分写入磁盘

• 发生在数据库正常运行期间.
• 不是sharp的就是模糊检查点,模糊检查点有四个发生的条件

master thread checkpoint

差不多以每秒或每10秒的速度从缓冲池的脏页中刷新一定比例的页回磁盘,这个过程是异步的,不会阻塞用户的1查询.

• 周期性写入flush list,找到脏页,写入磁盘
• 写入的量比较小
• 异步,不影响业务

• 通过capacity能力告知进行刷盘控制

  • 通过innodb的io能力告知控制对flush list刷脏页数量，io_capacity越高，每次刷盘写入脏页数越多；
  • 如果脏页数量过多，刷盘速度很慢，在io能力允许的情况下，调高innodb_io_capacity值，让多刷脏页。

flush_lru_list_checkpoint

mysq会保证里面有多少个可用的空闲页,在innodb1.1.x版本之前,需要检查在用户查询线程中是否有足够的可用空闲页(差不多100个空闲页),显然这回阻塞用户的线程,如果没有100个空闲页,那么innodbhi将lru列表尾端的页移除,如果这些页中有脏页,那么需要进行checkpoint.innodb1.2(5.6)之后把他单独放到一个线程page cleaner中进行,用户可以通过参数innodb_lru_scan_depth控制lru列表中可用页的数量，默认是1024。

Async/Sync Flush Checkpoint

指的是重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘

这个事件触发的时候被分为同步和异步,不可被覆盖的redolog站logfile的壁纸75%-->异步,90%--->同步.当这俩个事件中发生任何一个发生的时候,都会记录到errlog中,一旦出现这种日志提示一旦要加大logfile

　Async/Sync Flush Checkpoint是为了保证重做日志的循环使用的可用性。在InnoDB 1.2.x版本之前，Async Flush Checkpoint会阻塞发现问题的用户查询线程，而Sync Flush Checkpoint会阻塞所有的用户查询线程，并且等待脏页刷新完成。从InnoDB 1.2.x版本开始——也就是MySQL 5.6版本，这部分的刷新操作同样放入到了单独的Page Cleaner Thread中，故不会阻塞用户查询线程。

dirty page too much checkpoint

脏页的数量太多,为了保证buffer pool的空间的一个检查点

默认是脏页占比75%的时候,就会触发刷盘,将脏页写入磁盘腾出内存空间

备份

备份的目的

• 做灾难的恢复:对损坏的数据进行恢复和还原
• 需求改变,因需求改变需要把数据库恢复到以前
• 测试性能是否可用

备份需要考虑的问题

• 可用容忍丢失多长时间的数据
• 恢复数据要在多长时间内完,
• 恢复的时候是否需要持续的提供服务
• 恢复的对象,是整个库,多个表,还是单个库,单个表

根据是否需要数据库离线

• 冷备份:需要关闭mysql服务,读写请求均在不允许状态下进行
• 温备份:服务在线,但仅支持读请求,不允许写请求
• 热备份:备份的同时,业务不受影响

这种类型的备份,取决于业务的需求,而不是备份工具

myisam不支持热备,innodb支持热备份,但需要专门的工具

根据要备份的数据集合的范围

• 完全备份:备份全部字符集
• 增量备份:上次完全备份或增量备份来改变了数据,不能单独使用,要借助完全备份,备份的频率取决于数据库的更新频率
• 差异备份上次完全备份以来改变的数据

建议的后恢复策略

完全+增量+二进制日志

完全+差异+二进制文件

根据备份数据或文件

物理备份:直接备份数据文件

优点:

备份和恢复操作都比较简单,能够跨mysql版本,恢复速度快,属于文件系统级别的

建议不要假设备份一定可用,要测试

mysql>check tables；检测表是否可用

逻辑备份:备份表中的数据和代码

优点:

恢复简单,备份的结果为ascii文件,可编辑

与存储引擎无关可用通过网络备份和恢复

缺点

备份或恢复都需要mysql服务器进程产于,备份结果占据更多的空间

浮点数还可能会丢失精度

还原之后缩影需要重建

备份的的对象

• 数据
• 配置文件
• 代码:存储过程,存储函数,触发器
• os相关的配置文件
• 复制相关的配置
• 二进制日志