3_mysql（事务、日志管理、备份恢复）

一、事务

1. 事务的ACID特性
A：atomicity，原子性，指事务是一个不可分割的工作单位，事务中的操作要么都发生（commit，提交），要么都不发生（undo，回滚）
C：consistency，一致性，如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态；一致性表示事务完成后，符合逻辑运算
I：isolation，隔离性，多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离
D：durability，持久性，一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
2. 事务生命周期管理
2.1 标准事务控制语句
标准事务语句(DML)：insert、update、delete、select
2.2 自动提交功能
2.3 隐式事务控制语句

-- 隐式提交
 - 设置了autocommit=1
 - 导致提交的非事务语句：
	DDL语句： （ALTER、CREATE 和 DROP）
	DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
	锁定语句： （LOCK TABLES 和 UNLOCK TABLES）
	
-- 隐式回滚
 - 会话关闭
 - 数据库宕机
 - 事务语句执行失败

3.InnoDB事务的ACID如何保证
3.1 名词介绍
3.1.1 重做日志
redo logs：用于记录数据页的变化情况
redo logs buffer：redo在内存中的缓冲区域
3.1.2 数据页存储位置
ibd：存储数据行和主键索引
buffer pool：缓冲区池，数据页和索引页的缓冲
3.1.3 LSN
log segment number，日志序列号，磁盘数据页、redo logs、buffer pool、redu buffer中都会记录
MySQL每次启动时都会比较磁盘数据页和redo logs的LSN，要求必须两者LSN一致时数据库才能正常启动
3.1.4 WAL
wait ahead log，日志优先于数据页写入，实现持久化
3.1.5 脏页
内存中的数据发生了修改，在没回写到磁盘前把内存也称之为脏页
3.1.6 CKPT
checkpoint，检查点，在检查点会触发将脏页数据写到磁盘的动作
3.1.7 TXID
transaction id，事务号，InnoDB会为每一个事务生成一个事务号，伴随着整个事务的生命周期
3.1.8 UNDO
保存在idata1中，存储了事务工作中的回滚信息(DML的反操作)
4.事务的工作流程
4.1 redo logs
4.1.1
redo是什么：顾名思意，“重做日志”，是事务日志的一种，存储的是在事务工作过程中数据页的变化，属于物理日志
作用是什么：在事务的ACID过程中，实现的是"D"持久化的作用，对于AC也有相应的作用
redo日志位置：iblogfile0~N
redo buffer：存储数据页的变化信息+数据页当时的LSN号
redo的刷新策略：每次commit时，会将当前事务的redo buffer刷新到磁盘中，还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘
工作流程：

• 在事务commit时redo会立即写入磁盘，日志落盘成功就代表commit
• 在MySQL出现Crash异常宕机时，提供前滚功能(CSR)
  4.1.2 MySQL在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致
  情况一:
  我们做了一个事务,begin;update;commit.
  在begin ,会立即分配一个TXID=tx_01.
  update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中
  DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102
  LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redo buffer
  执行commit时,LGWR日志写线程会将redo buffer信息写入redo log日志文件中,基于WAL原则,
  在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
  假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失 MySQL再次重启时,必须要redo
  log和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102
  MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正常启动
  以上的工作过程,我们把它称之为基于REDO的"前滚操作"
  4.1.3 相关参数
  innodb_flush_log_at_trx_commit：日志刷写时机
  1：默认值，在每次事务提交时，会立即刷新redo到磁盘，commit才能成功
  0：每秒刷新redo buffer到os cache，再fsync到磁盘，异常宕机时，有可能丢失1s内的事务
  2：每次事务提交，都立即刷新redo buffer到os cache，再每秒fsync到磁盘，磁盘异常宕机时，有可能丢失1s内的事务
  另外
  redo buffer还和操作系统的缓存机制有关，所以刷写策略可能和innodb_flush_method参数有一定关系
  redo也有group commit，可以理解为在每次刷新已提交的redo时，顺便可以将一些未提交的事务redo也一次性刷写到磁盘，此时为了区分不同状态的redo，会加一些特殊的标记(是否提交标记)
  
  4.2 undo logs
  undo是什么？顾名思意，“回滚日志”，是事务日志的一种，存储的是事务的反操作，属于逻辑日志
  作用是什么？
  在事务的ACID过程中，实现的是"A"原子性的作用，对于CI也有相应的作用
  在rollback时，会将数据恢复到修改之前的状态
  在CSR实现的是，将redo当中记录的未提交的时候进行回滚，先做redo前滚，在做undo回滚
  undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备
  5.事务的隔离级别
  5.1 数据库并发会引起的问题
  脏读：A事务读取B事务尚未提交的更改数据，并在这个数据基础上操作。如果B事务回滚，那么A事务读到的数据根本不是合法的，称为脏读
  不可重复读：A事务读取了B事务已经提交的更改（或删除）数据。比如A事务第一次读取数据，然后B事务更改该数据并提交，A事务再次读取数据，两次读取的数据不一样
  幻读：A事务读取了B事务已经提交的新增数据。注意和不可重复读的区别，这里是新增，不可重复读是更改（或删除）
  5.2 事务的隔离级别
  5.2.1 read-uncommitted
  读未提交，一个事务还没提交时，它做的变更就能被别的事务看到，即允许读取未提交的数据，会导致脏读、不可重复读、幻读的问题
  5.2.2 read-committed
  读已提交，一个事务提交之后，它做的变更才会被其他事务看到，这里的读指的是 一致性非锁定读 ，即每次都读最新的快照数据，不加共享锁，解决了脏读问题，但仍会导致不可重复读、幻读问题
  5.2.3 repeatable-read
  可重复读，MySQL的默认隔离级别，总是会在事务开启的时候读取最新提交的行版本，并将该行版本一直持有到事务结束，未提交变更对其他事务也是不可见的，解决了不可重复读问题，但仍有可能发生幻读问题
  5.2.4 serializable
  可串行化，这种隔离级别不会造成任何并发问题，但并发性能极低
  5.2.5 避免幻读
  Gap Lock：间隙锁，加锁后如果更新的范围会影响到间隙锁的范围，则操作会被挂起，直到间隙锁释放或等待超时，针对事务隔离级别为可重复读或以上
  Next-Key Lock：行锁+间隙锁=下键锁
  默认情况下，InnoDB工作在可重复读隔离级别下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录
  6.事务的一致性问题总结
  A：原子性，UNDO、REDO
  D：持久性，REDO(WAL机制)
  I：隔离性，ISOLATION LEVEL、Lock、MVCC(UNDO)
  C：一致性，以上所有特性都是用来保证一致性的
  写一致性：REDO、UNDO、LOCK
  读一致性：ISOLATION LEVEL、MVCC(UNDO)
  数据页一致性：DOUBLE WRITE BUFFER
  7.存储引擎核心参数
  7.1 innodb_flush_log_at_trx_commit
  双一标准之一：redo log 刷写参数
  1：默认值，在每次事务提交时，会立即刷新redo到磁盘，commit才能成功
  0：每秒刷新redo buffer到os cache，再fsync到磁盘，异常宕机时，有可能丢失1s内的事务
  2：每次事务提交，都立即刷新redo buffer到os cache，再每秒fsync到磁盘，磁盘异常宕机时，有可能丢失1s内的事务
  7.2 innodb_flush_method
  null：默认值，在unix-like系统中体现为fsync
  buffer poll的数据写磁盘时需要先经历os buffer然后再写到磁盘
  redo buffer的数据写磁盘时需要先经历os buffer然后再写到磁盘
  O_DSYNC：
  buffer poll的数据写磁盘时需要先经历os buffer然后再写到磁盘
  redo buffer的数据写磁盘时跨过os buffer直接写到磁盘
  O_DIRECT:建议使用
  buffer poll的数据写磁盘时跨过os buffer直接写到磁盘
  redo buffer的数据写磁盘时需要先经历os buffer然后再写到磁盘
  7.3 数据缓冲区大小参数 innodb_buffer_size
  缓冲数据页和索引页，是MySQL最大的内存区域
  默认：128M
  官方建议：80~90%
  生产建议：低于75％，按需调配

二、日志管理

1.错误日志（errorlog）
主要关注[ERROR]块，看上下文；重下往上看
2.二进制日志（binlog）
双一标准之二：sync_binlog =1 （binlog日志刷盘策略，双一中的第二个1，每次事务提交立即刷写binlog到磁盘）
2.1 作用
主要记录数据库变化（DDL、DCL、DML）性质的日志(逻辑层)
备份恢复必须依赖二进制日志；主从环境必须依赖二进制日志
2.2 binlog记录
bilog是SQL层的功能，记录的是变更类型的SQL语句，不记录查询语句
2.2.1 记录SQL语句种类
DDL：原封不动的记录当前DDL(statement语句方式)
DCL：原封不动的记录当前DCL(statement语句方式)
DML：只记录已提交事务的DML
2.2.2 DML的记录格式
参数：binlog_format
(1) statement：5.6默认，SBR(statment based replication)，语句模式原封不动的记录当前DML
(2) row：5.7默认，RBR(row baesd replication)，记录数据行的变化(直接打开看不懂，需要借助工具分析)
(3) mixed：MBR(mixed based replication)，以上两种的混合
2.2.3 SBR与RBR的区别
statement： 可读性较高(直接存储语句)，日志量少，但是不够严谨
row：可读性低(需要借助工具分析)，日志量大，足够严谨
2.4 event
对于DDL、DCL来说，一条语句就是一个事件(也是一个事务)；对于DML来说：只记录已提交的事务
2.5 binlog的GTID模式
GTID是对一个已提交事务的编号，并且是一个全局唯一的编号
3.慢日志（slowlog）
记录MySQL运行过程中较慢的语句，通过一个文本文件记录下来。帮助我们进行语句优化的工具日志

三、备份恢复

https://blog.csdn.net/weixin_42511320/article/details/107163232
1. 常用备份工具
逻辑备份方式
mysqldump(MDP)
replication
mydumper
load data in file
物理备份方式
MySQL Enterprise Backup
Percona Xtrabackup(PBK、XBK)
2. mysqldump
2.1 介绍
逻辑备份工具，备份的是SQL语句
对InnoDB表：
可以采用快照备份的方式：开启一个独立的事务，获取当前最新的一致性快照(不需要锁表)
将快照数据放在临时表中，转换成SQL(create database、create table、insert)保存到SQL文件中
对于非InnoDB表(MyISAM)：
需要锁表备份，会触发FTWRL，全局锁表，转换成SQL，保存到SQL文件中
2.2 小结
优点：

• 不需要下载安装 备份出来的是SQL，文本格式，
• 可读性高,便于备份处理 压缩比较高，
• 节省备份的磁盘空间
  缺点：
• 依赖于数据库引擎，需要从磁盘把数据读出，然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低
• 备份事件相对较长，恢复时间很长
  建议：
• 100G以内的数据量级，可以使用mysqldump
• 超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统
  3.Percona Xtrabackup
  物理备份工具，备份的是数据文件，支持全量备份和增量备份