mysql 日志

MySQL 里经常说到的 WAL技术，也就是先写日志，再写磁盘。

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

MySQL 从 内存更新到磁盘的过程，称为刷脏页的过程（flush）。

InnoDB 刷脏页的时机：

1. 内存中的redo log 写满了，这时系统就会停止所有更新操作，把checkoutpoint 往前推，redo log留出空间可以继续写。 

往前推进之后，就要把两个点之间的日志对应的所有脏页都 flush 到磁盘上。

这种情况是 InnoDB 要尽量避免的。因为出现这种情况，整个系统都不能接受更新。更新数会跌为0。

2. 系统中内存不足时，当这个时候需要新的数据页到内存中，就要淘汰掉一些数据页，如果淘汰的是“脏页”，就要先将“脏页”写到磁盘。

那么为什么不能直接淘汰所有的内存，下次请求的时候，再从磁盘读入数据页，然后 拿 redo log 出来应用？这其实也是从性能的角度来考虑的，刷脏页一定写盘，就保证了每个数据页只有两种情况：

• 数据页直接在内存里，内存里的肯定是正确的，直接返回
• 内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。 这样的效率最高。

这种情况在日常应用中其实是常态。在InnoDB 中，使用缓冲池 （buffer pool）管理内存，缓冲池中的内存页有三种状态：

• 还没有使用的；
• 使用了并且是干净页
• 使用了并且是脏页

3. 数据库空闲的时候刷脏页。
4. 数据库正常关闭的时候，也要把内存中所有的脏页全都flush 到磁盘上。

对性能的影响

刷脏页是常态，所以如果出现以下的情况，都会明明显影响性能：

• 一个查询要淘汰的脏页太多，会导致查询的响应时间明显变长；
• 日志写满，更新全部堵住，写性能跌为0，这种情况对于敏感业务来说是不能接受的。

InnoDB 刷脏页的控制策略

首先，需要让 InnoDB 正确指导系统的 IO 能力，来控制刷脏页的快慢。

innodb_io_capacity 这个参数，它会告诉 InnoDB 你的磁盘能力，所以尽量设置成磁盘的 IOPS。可以使用 fio 工具来获取。

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

然后，如果你来设计策略控制刷脏页的速度，会参考哪些因素呢？

这个问题可以这么想，如果刷太慢，会出现什么情况？首先是内存脏页太多，其次是 redo log 写满。

所以，InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。

参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认是 75%。InnoDB 会根据当前的脏页比例，计算出一个数字 F1。

F1(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

InnoDB 写入日志都会有一个序号，当前写入序号跟 checkpoint 对应的序号之间的差值，假设为N。InnoDB 会根据N 计算出 F2.

根据 F1和F2 取其中较大的值为 R，之后引擎就可以按照 Innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。

 

 

MySQL 中有一个机制，刷脏页的时候如果数据页旁边的数据页也是脏页，那么就会一起刷掉，而且这个逻辑是可以蔓延的，所以对于每个相邻的数据页，都会被一起刷。

在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。

在使用机械硬盘时，这个优化很有意义，可以减少很多随机 IO。如果使用的是 SSD 这种IOPS 比较高的设备，可以设置innodb_flush_neighbors 为0，只刷自己，这个时候 IOPS 往往就不是性能瓶颈了。只刷自己就可以提高刷脏页的速度，减少 SQL 语句的响应时间。

binlog 的写入机制

binlog 的写入机制比较简单：事务执行的过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到binlog 文件中。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binglog_cache_size 用于控制单个线程内 binlog cache 的内存大小，超过就要暂存在磁盘。

事务提交的时候，执行器把 binlog cache 里完整事务写入到 binlog 中，并清空 binlog cache。

 

binlog 写盘状态

• write 指的是把日志写入到文件系统的 page cache，并没有吧数据持久化到磁盘，所以速度比较快。
• fsync 是持久化到磁盘的操作，一般情况下， fsync 才会占磁盘的 IOPS。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

• sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
• sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
• sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

redo log 的写入机制

事务的执行过程中，生成的 redo log 是要先写到 redo log buffer 的。

redo log 三种状态：

• 存在 redo log buffer 中，物理上是在 MySQL 进程内存中
• 写到磁盘（write），但是没有持久化（fsync），物理上是在文件系统的 page cache 里
• 持久化磁盘，对应的是 hard disk

日志写到 redo log buffer 是很快的，write 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。

InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，取值如下：

1. 设置为 0 时，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中；
2. 设置为 1 时，表示每次事务提交时都将 redo log 直接持久化到磁盘；
3. 设置为 2 时，表示每次事务提交时都只是把 redo log 写到 page cache。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

组提交机制

日志逻辑序列号（log sequence number，LSN）是一个单调递增的值，对应 redo log 的一个个写入点。每次写入的长度为 lenght 的 redo log，LSN的值就会加上 length。

LSN 也会写到 InnoDB 的数据页中，来确保数据也不会被多次执行重复的 redo log。
在一组提交里面，组员越多，节约磁盘 IOPS 的效果越好。在并发更新的场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好。

1. binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
2. binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

WAL机制主要得益于两个方面：

1. redo log 和binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
2. 组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？

针对这个问题，可以考虑以下三种方法：

1. 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
2. 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
3. 将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。

 ---------------------------------------------------------------------------redo日志-----------------------------------------------------

1.2 影响redo log参数
innodb_log_file_size：指定每个redo日志大小，默认值48MB

innodb_log_files_in_group：指定日志文件组中redo日志文件数量，默认为2

innodb_log_group_home_dir：指定日志文件组所在路劲，默认值./，指mysql的数据目录datadir

innodb_mirrored_log_groups：指定日志镜像文件组的数量，默认为1，此功能属于未实现的功能，在5.6版本中废弃，在5.7版本中删除了。

1.3 redo log大小怎么设置？
redo log文件的大小设置对于InnoDB存储引擎的性能有着非常大的影响。

设置的太大
设置很大以后减少了checkpoint，并且由于redo log是顺序I/O，大大提高了I/O性能。但是如果数据库意外出现了问题，比如意外宕机，那么需要重放日志并且恢复已经提交的事务，如果日志很大，那么将会导致恢复时间很长。甚至到我们不能接受的程度。

设置的太小
当一个日志文件写满后，innodb会自动切换到另外一个日志文件，而且会触发数据库的检查点（checkpoint），这会导致innodb缓存脏页的小批量刷新，会明显降低innodb的性能

把重做日志文件设置很大，甚至与缓冲池一样大。当InnoDB将重做日志文件写满时，它会触发数据库的检查点，把缓冲池的脏数据写入磁盘。小的重做日志文件会导致许多不必要的磁盘写入。虽然在以前版本中，大的重做日志文件导致冗长的恢复时间，但现在恢复速度更快，可以放心地使用大型重做日志文件。

考虑增加日志缓冲区的大小。 大型日志缓冲区可以在事务提交之前运行大型事务，而无需将日志写入磁盘。 因此，如果您有更新，插入或删除许多行的事务，则使日志缓冲区更大可以节省磁盘I/O. 使用innodb_log_buffer_size配置选项配置日志缓冲区大小。

设置innodb_log_write_ahead_size参数，表示redo log写前的块大小。InnoDB以512字节一个block的方式对齐写入ib_logfile文件，但文件系统一般以4096字节为一个block单位。如果即将写入的日志文件块不在OS Cache时，就需要将对应的4096字节的block读入内存，修改其中的512字节，然后再把该block写回磁盘
 ---------------------------------------------------------------------------binlog日志-----------------------------------------------------

2.2 影响binlog的参数
max_binlog_size:指定单个binlog文件最大值。默认值为1g，最大值1g，如果超过该值，则产生新的binlog文件，后缀名+1，并记录到.index文件。

binlog_cache_size：使用事务表存储引擎（如innodb存储引擎）时，所有未提交的binlog日志会被记录到一个缓存中去，等事务提交时再将缓存中的binlog写入到binlog文件中。缓存的大小由binlog_cache_size决定，默认大小为32K。

binlog_cache_size是基于session的，也就是说，当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog_cache_size的缓存，因此该值的设置需要非常小心，不能设置过大。
当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓存中的日志写入一个临时文件中，因此该值又不能设的太小
binlog记录次数使用次数

通过show global status命令查看binlog_cache_use,binlog_cache_disk_use的状态，以判断当前binlog_cache_size设置是否合适。

binlog_cache_use：记录了使用缓存写binlog次数
binlog_cache_disk_use：记录了使用临时文件写binlog次数。

示例：

show variables like 'binlog_cache_size';
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| binlog_cache_size | 32768 |
+-------------------+---------+
1 row in set (0.00 sec)

show global status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 33553 |
+-----------------------+-------+
2 rows in set (0.00 sec)

日志缓存大小

使用缓存次数为33553，临时文件使用次数为0。说明32KB的缓存大小对于当前MySQL数据库是够用的。

max_binlog_cache_size:如果事务需要的内存超过很多字节，则服务器会生成多于“max_binlog_cache_size”字节的存储错误所需的并发事务。 最小值为4096字节，最大可能值为16EB（exabytes）。 建议的最大值为4GB; 这是因为MySQL目前无法使用大于4GB的二进制日志位置。

expire_logs_days：表示binlog文件自动删除N天前的文件。默认值为0，表示不自动删除，最大值99.要手动删除binlog文件，可以使用purge binary logs语句
删除binlog日志

PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }

PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
PURGE BINARY LOGS BEFORE now() - interval 3 days;

记录原始sql记录

binlog_rows_query_log_events：默认为不启用，启用binlog_rows_query_log_events时，可以在binary log中记录原始的SQL语句

同步binlog节奏
sync_binlog：sync_binlog=[N]表示没写缓冲N次就同步到磁盘，如果将N设为1，即sync_binlog表示采用同步写磁盘的方式来写二进制日志，在MySQL5.7.7后，默认为1。会对数据库的IO系统带来一定影响，但可以得到最大的高可用行。
binlog_checksum：该参数目的就是写入binlog进行校验，有两个值[crc32|none],默认为crc32

binlog-do-db：表示需要写入日志的数据库，默认为空，表示同步所有库

binlog-ignore-db：表示忽略写入日志的数据库，默认为空，表示同步所有库

log-slave-update:表示从master端取得并执行的binlog，写入自己的binlog文件中，一般应用在master=>slave=>slave架构

备份恢复数据操作

show variables like '%log_bin%'//查看日子状态
show master logs;//查看所有binlog日志文件
show master status;//查看最后一个binlog文件
Flush logs;//刷新日志，此刻开始产生一个新编号的binlog文件
reset master;//清空所有binlog日志命令
show binlog events in 'mysql-bin.000283' from 1 limit 2,4;//查看binlog二进制日志中的确进行了记录

/usr/bin/mysqlbinlog --start-position=573 --stop-position=435 --database=hello /var/lib/mysql/mysql-bin.000006 | /usr/bin/mysql -uroot -p密码 -v hello;//指定start,pos事务区间恢复(部分恢复)
/usr/bin/mysqlbinlog --start-datetime="2018-04-27 20:57:55" --stop-datetime="2018-04-27 20:58:18" --database=hello /var/lib/mysql/mysql-bin.000009 | /usr/bin/mysql -uroot -p8856769abcd -v hello;通过时间点来恢复从备份数据后到删除数据库这期间所有操作的数据