重新认识Database-MySQL⑦--日志机制

星星152
100 阅读11分钟

重要日志介绍

  • binlog：归档日志，MySQLSerer层实现，所有引擎（MyIASM,InnoDB）都可以使用,是逻辑日志，记录数组操作的逻辑。
  • redo log：重做日志，InnoDB引擎实现,是物理日志，记录某个数据页上操作。
  • undo log：回滚日志，MySQL Server层实现,是逻辑日志，记录数据操作的原始数据相反逻辑，如：删除数据，binlog记录删除记录，而undo log记录是添加insert记录。

通过三种日志的配合,实现事务机制、数据恢复、主备同步等。

binlog

binlog二进制归档日志记录了所有执行过的增加、修改操作语句，不记录查询。假设MySQL意外宕机，可通过该文件排查，进行表数据的操作，从而恢复数据库的数据。启动binlog，会影响性能，但是如果需要保证数据完整可恢复或主从复制，则好处显而易见。

-- 查看binlog参数
show variables like '%log_bin%';

MySQL5.7版本默认是关闭binlog,而8版本是默认打开的。log_bin的值Value是OFF表示关闭状态，打开需要修改配置文件my.ini(windows)或my.cnf(linux)，接着重启MySQL生效。 在配置文件的[mysqld]部分增加配置,一般配置如下:

# binlog存放位置,相对路劲在data目录下.
log-bin=mysql-binlog
# server id,集群环境唯一标识
server-id=1
# 配置日志格式
binlog_format=rows
# 保存binlog日志文件天数,默认0,表示都保存不删除数据
expire_logs_days=15
# 单个binlog文件大小,默认1G

在binlog的数据结构中，XID、GTID、server_id以及记录的数据操作主体（由多个字段组成）是我们需要特别注意的几个字段。

  • XID：借助这个字段，binlog可以和redo log关联起来，在数据恢复时，配合使用。

崩溃恢复时，会按顺序扫描redo log。碰到既有prepare和commit状态的两个redo log，就直接提交；只碰到prepare状态、没有commit状态的单个redo log，拿着redo log的XID去寻找是否对应的binlog。

  • GTID：全局事务ID，是事务在提交时生成的唯一标识，可用于在从库中找新主库的同步位置、判断主备库是否有延迟。

  • server_id：记录了该操作第一次执行时所在的MySQL实例ID，可用于解决主备一致中循环复制的问题。

  • binlog有row、statement、mixed三种记录数据操作的格式。

binlog的完整性保证：

  • binlog具有完整的格式，statement格式的binlog最后会有commit，row格式的binlog最后有XID event。

并且MySQL 5.6.2版本后引入了binlog-checksum参数，用于验证binlog的正确性。

三种格式

  • statement：记录的是SQL语句的原文。这种方式占据空间小，但有可能会导致主备不一致。

  • row：记录了SQL语句操作行的所有字段，如delete语句就会记录所有字段的值，保证了主备库的操作都是一致的。但这种方式binlog占的空间会很大，比如我delete了10G的数据，那么binlog中就会记录10G的数据，也要耗费对应的IO资源。

  • mixed：statement和row格式混用，MySQL判断当前SQL语句是否会导致主备不一致，若是，则使用row，否则使用statement。

  • 看似mixed方式很好，但是在数据恢复的时候，若binlog是row格式，直接解析binlog数据并应用其反逻辑即可，但有些SQL语句依赖上下文命令，若直接使用statement的binlog恢复，执行结果很有可能是错误的。并且，在主备同步的时候，使用row格式的binlog会使得主备数据不一致的问题更容易被发现。

综上: binlog的格式最好还是设置为row。

写入机制

binlog是追加写，binlog文件写到一定大小之后会切换到下一个文件，不会覆盖之前的binlog。

写入过程：在事务的执行期间，先把binlog写入到binlog cache，等事务提交的时候，（write）再把binlog cache中的binlog写入到binlog文件（位于文件系统的page cache）中，清空binlog cache，并根据sync_binlog的设置，（fsync，此时才认为会消耗IO资源）将binlog持久化到磁盘中。

  • 一个事务的binlog不能拆开，无论事务多大，都要确保一次性写入。
  • 一个线程一个binlog cache，若事务大小超过了binlog cache大小，就要把binlog暂存到磁盘中。
  • 多个线程共用一个binlog文件。
  • sync_binlog = N。N=0，每次提交事务只write，不fsync；N≠0，每次提交事务都会write，但累积N个事务才会fsync。在IO瓶颈时，可以将N值设置得大一些，但缺点就是若主机发生异常重启，会丢失N个事务的binlog。

数据恢复

在进行数据恢复时，需要使用全量备份+binlog来恢复数据。数据恢复的流程：

  • 取最近一次的全量备份，用备份恢复出一个临时库；
  • 从日志备份中，取出全量备份之后的binlog和处于prepare的redo log，将这些binlog应用到临时库中，若有误删的语句，就不用应用到临时库了（跳过日志，可以使用GTID，或使用stop/start-position的方式）。

以下任务事件发生,binlog日志都会重新生成:

  • 服务器启动或重启
  • 服务器刷新日志,执行flush logs命令
  • 日志文件超过max_binlog_size设定的值,默认1G

注意:binlog文件一般需要进行数据的备份,防止数据被删,可以通过binlog日志来回复数据.

redo log

innodb_log_buffer_size: redo log buffer参数,默认16M,最大值4096M,最小1M.

show variables like '%innodb_log_buffer_size%';

innodb_log_group_home_dir: redo log 文件存储地址参数,默认./,即data目录下存储位.

show variables like '%innodb_log_group_home_dir%';

innodb_log_files_in_group: redo log 文件个数参数,默认2个,最大100.文件如下:ib_logfile0,ib_logfile1...

show variables like '%innodb_log_files_in_group%';

innodb_log_file_size: redo log 文件大小参数,默认48M,最大512G,最大值值整个redo log文件之和,即(innodb_log_files_in_group*innodb_log_file_size)不能大于最大值512G.

show variables like '%innodb_log_file_size%';

redo log 写入过程

redo log 是 InnoDB 特有日志，为什么要引入 redo log 呢，想象这样一个场景，MySQL 为了保证持久性是需要把数据写入磁盘文件的。我们知道，在写入磁盘时，会进行文件的 IO，查找操作，如果每次更新操作都这样的话，整体的效率就会特别低，根本没法使用。

既然直接写入磁盘不行，解决方法就是先写进内存，在系统空闲时再更新到磁盘就可以了。但光更新内存不行，假如系统出现异常宕机和重启，内存中没有被写入磁盘的数据就会被丢掉，数据的一致性就出现问题了。这时 redo log 就发挥了作用，在更新操作发生时，InnoDb 会先写入 redo log 日志（记录了数据发生了怎么样的改变），同时更新内存，最后在适当的时间再写入磁盘，一般是找系统空闲的时间做。先写日志，在写磁盘的操作，就是常说到的 WAL （Write-Ahead- Logging）技术。

redo log 的出现，除了在效率上有了很大的改善，还保证了 MySQL 具有了 crash-safe 的能力，在发生异常情况下，不会丢失数据。

在具体实现上 redo log 的大小是固定的，可配置一组为 4 个文件，每个文件 1GB，更新时对四个文件进行循环写入。

img write pos 记录当前写入的位置，写完就后移，当第写入第 4 个文件的末尾时，从第 0 号位置重新写入。

check point 表示当前可以擦除的位置，当数据更新到磁盘时，check point 就向后移动。

write pos 和 check point 之间的位置，就是可以记录更新操作的空间。当 write pos 追上 check point ，不在能执行新的操作，先让 check point 去写入一些数据。

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

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

为什么会有redolog和binlog两份日志呢？

因为最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，binlog日志只能用于归档。而InnoDB是另一个公司以插件形式引入MySQL的，既然只依靠binlog是没有crash-safe能力的，所以InnoDB使用另外一套日志系统——也就是redolog来实现crash-safe能力。有了redolog，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

undo log

undo log由InnoDB存储引擎实现,文件管理采用段的方式,即回滚段(rollback segment).每个回滚段记录1024个undo log segment,每个事务只会有且只有一个undo log segment. 在MySQL5.5之前，只支持1个rollback segment，也就是只能记录1024个undo操作。在MySQL5.5之后，可以支持128个rollback segment，分别从resg slot0 - resg slot127，每一个resg slot，也就是每一个回滚段，内部由1024个undo segment 组成，即总共可以记录128 * 1024个undo. undo log日志了解 如上图，可以看到，undo log日志里面不仅存放着数据更新前的记录，还记录着RowID、事务ID、回滚指针。其中事务ID每次递增，回滚指针第一次如果是insert语句的话，回滚指针为NULL，第二次update之后的undo log的回滚指针就会指向刚刚那一条undo log日志，依次类推，就会形成一条undo log的回滚链，方便找到该条记录的历史版本.

同时保证原子性和持久化，是因为以下特点：

  • 更新数据前记录undo log。
  • 为了保证持久性，必须将数据在事务提交前写到磁盘，只要事务成功提交，数据必然已经持久化到磁盘。
  • undo log必须先于数据持久化到磁盘。如果在G,H之间发生系统崩溃，undo log是完整的，可以用来回滚。
  • 如果在末端操作之间发生系统崩溃，因为数据没有持久化到磁盘，所以磁盘上的数据还是保持在事务开始前的状态。

缺陷：每个事务提交前将数据和undo log写入磁盘，这样会导致大量的磁盘IO，因此性能较差。 如果能够将数据缓存一段时间，就能减少IO提高性能，但是这样就会失去事务的持久性。

  • undo日志属于逻辑日志，redo是物理日志，所谓逻辑日志是undo log是记录一个操作过程，不会物理删除undo log，sql执行delete或者update操作都会记录一条undo日志。

错误日志

Mysql还有一个比较重要的日志是错误日志，它记录了数据库启动和停止，以及运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。在MySQL数据库中，错误日志功能是默认开启的，而且无法被关闭。

#查看错误日志存放位置
show variables like '%log_error%';

文章参阅

zhuanlan.zhihu.com/p/58011817

blog.csdn.net/sinat_33087…

juejin.cn/post/720180…

avatar
文章
阅读
粉丝