MySQL高级进阶五

MySQL高级进阶

日志系统

bin log日志

二进制日志以事件形式记录了对MySQL数据库执行更改的所有操作

binlog记录了所有数据库表结构变更以及表数据修改的二进制日志

binlog是mysql server层维护的，跟采用何种引擎没有关系，记录的是所有的更新操作的日志记录，binlog是在事务最终commit前写入的。对支持事务的引擎如innodb，必须要提交了事务才会记录binlog

binlog文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志，像redo log，undo log 是循环写入的，后面写入的可能会覆盖前面写入的

binlog使用场景

主从复制

数据恢复

数据恢复

• 查看是否开启bin log日志

show variables like '%log_bin%';

• 刷新生成新的日志文件

flush logs;

• 查看日志信息

mysqlbinlog -v 日志文件名+后缀

• 指定条件查看日志信息

# 指定位置范围
mysqlbinlog -v 日志文件名+后缀 --start-position=起始编号 --stop-position=结束编号
# 指定时间范围
mysqlbinlog -v 日志文件名+后缀 --start-datetime="起始时间" --stop-datetime="结束时间" 

• 恢复数据

mysqlbinlog -v 日志文件名+后缀 --stop-position=开始编号 -v | mysql -uroot -p

格式分类

Statement：每一条会修改数据的 SQL 都会记录在 binlog 中

Row：不记录 SQL 语句上下文信息，仅保存哪条记录被修改

Mixed：Statement 和 Row 的混合体，当前默认的选项，默认row

日志格式

• binlog文件以一个值为0Xfe62696e的魔数开头，这个魔数对应0xfebin。

• binlog由一系列的binlog event构成。每个binlog event包含header和data两部分

header部分提供的是event的公共的类型信息，包括event的创建时间，服务器等等

data部分提供的是针对该event的具体信息，如具体数据的修改

binlog刷盘

二进制日志文件并不是每次写的时候同步到磁盘。因此当数据库所在操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中，所以给恢复和复制带来了问题

参数sync_binlog=[N]表示每写多少次就同步到磁盘。如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志

binlog实现主从同步

单点部署的问题

服务器宕机，会导致业务停顿，影响客户体验

服务器损坏，数据丢失，不能及时备份，造成巨大损失

读写操作都在同一台服务器，在并发量大的情况下性能存在瓶颈

主从复制工作原理

Master 数据库只要发生变化，立马记录到Binary log 日志文件中

Slave数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志

Slave I/O获取到的二进制日志，保存到自己的Relay log 日志文件中

Slave 有一个SQL thread定时检查Realy log是否变化，变化那么就更新数据

搭建

• 准备两台服务器并安装相同的mysql版本

mysql1(master): 43.143.229.12:3306
mysql2(slave):  172.168.117.200:3306

• mysql1（master）: 开启bin_log且需要配置一个server-id

#mysql master1 config 
[mysqld]
server-id = 1            # 节点ID，确保唯一

# log config
log-bin = master-bin     #开启mysql的binlog日志功能

• mysql2（slave）: 需要开启中继日志

[mysqld]
server-id=2
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=sys.%
replicate-wild-ignore-table=information_schema.%
replicate-wild-ignore-table=performance_schema.%

• 重新启动两个mysql服务

• 为master创建复制用户

CREATE USER 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'ZAQzaq123.';

这里一定要设置加密方式，WITH 'mysql_native_password'改一下加密方式，真的，我被这个坑坑了一个小时

• 赋予该用户复制的权利

grant replication slave on *.* to 'repl'@'%'; 
FLUSH PRIVILEGES;

• 查看master的状态

show master status;

• 配置从库

CHANGE MASTER TO 
MASTER_HOST = '43.143.229.12',  
MASTER_USER = 'repl', 
MASTER_PASSWORD = 'ZAQzaq123.',
MASTER_PORT = 3306,
MASTER_LOG_FILE='master-bin.000004',
MASTER_LOG_POS=3648,
MASTER_HEARTBEAT_PERIOD = 10000; 

MASTER_HOST：master的IP地址

MASTER_USER：用户

MASTER_PASSWORD：密码

MASTER_PORT：端口号

MASTER_LOG_FILE：在master的状态中的日志文件名

MASTER_LOG_POS：在master的状态中的开始编号

• 启动从库

# 这个是停止的
stop slave;
# 这个是启动的
start slave;

• 查看配置状态

show slave status \G;

Slave_IO_Running：从库的IO线程，用来接收master发送的binlog，并将其写入到中继日志relag log，这个必须是YES状态

Slave_SQL_Running：从库的SQL线程，用来从relay log中读取并执行binlog，这个必须是YES状态

其他日志

通用日志

记录建立的客户端连接和执行的所有DDL和DML语句，默认情况下是关闭的，它是一个文本文件

• 查看

SHOW VARIABLES LIKE '%general_log%';

• 开启

# 在全局模式下，开启通用查询日志，1表示开启，0表示关闭
SET global general_log=1;

• 查看日志文件

more 日志文件名 

centos.log:日志文件

慢查询日志

执行时间大于该值就会被记录在慢查询日志中，默认是3s

• 查看

SHOW VARIABLES LIKE '%slow_query_log%';

错误日志

错误日志（Error Log）主要记录 MySQL 服务器启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况

redo log日志

redo log（重做日志）的设计主要是为了防止因系统崩溃而导致的数据丢失

解决系统崩溃导致数据丢失

每次提交事务之前，必须将所有和当前事务相关的buffer pool中的脏页刷入磁盘，但是，这个效率比较低，可能会影响主线程的效率，产生用户等待，降低响应速度，因为刷盘是I/O操作，同时一个事务的读写操作也不是顺序读写

当前事务中修改的数据内容在日志中记录下来，日志记录是顺序写，性能很高。mysql就是这么做的，这个日志被称为redo log，执行事务中，每执行一条语句，就可能有若干redo log日志，并按产生的顺序写入磁盘，redo log日志占用的空间非常小，当redo log空间满了之后又会从头开始以循环的方式进行覆盖式的写入

redo log的格式

type：日志的类型，在5.7中，大概有53种不同类型的redo log，占用一个字节

space id：表空间id

page number：页号

data：日志数据

MTR

在innodb执行任务时，有很多操作，必须具有原子性，这一类操作称之为MIni Transaction

事务 sql MTR redolog的关系

一个事务包含一条或多条sql

一条sql包含一个或多个MTR

一个MTR包含一个或多个redo log

undo log日志

undo log（撤销日志或回滚日志），主要作用是为了实现回滚操作。他也是MVCC多版本控制的核心模块。undo log保存在共享表空间ibdata1文件中

事务id（trx_id）

在innodb的行数据中，会自动添加两个隐藏列，一个是trx_id，一个是roll_pointer，如果该表中没有定义主键，也没有定义非空唯一列，则还会生成一个隐藏列row_id

事务id是一个自增的全局变量，如果一个事务对任意表做了增删改的操作，那么innodb就会给他分配一个独一无二的事务id

事务id保存在一个全局变量MAX_TRX_ID上，每次事务需要分配事务id，就会从这个全局变量中获取，然后自增1

该变量每次自增到256的倍数会进行一个落盘（保存在表空间页号为5的页面中），发生服务停止或者系统崩溃后，再起启动服务，会读取这个数字，然后再加256。这样做既保证不会有太多I/O操作，还能保证id的有序增长，保证新的事务id一定大

roll_pointer

undo log在记录日志时是这样记录的，每次修改数据，都会将修改的数据标记一个新的版本，同时，这个版本的数据的地址会保存在修改之前的数据的roll_pointer列中

分类

只记录插入类型的操作（insert）

插入型的记录不需要记录版本，事务提交以后这一片空间就可以重复利用了

插入一条数据时，至少要把这条数据的主键记录下来，以后不想要了直接根据主键删除

只记录修改类型的操作（delete，update）

修改型的必须将每次修改作为一个版本记录下来，即使当前事务已经提交，也不一定能回收空间，因为其他事务可能在用

删除一条数据时，至少要把这个数据所有的内容全部记录下来，以后才能全量恢复。但事实上不需要，每行数据都有一个delete_flag，事务中将其置1，记录id，如需要回滚根据id复原即可，提交事务后又清除线程处理垃圾

修改一条数据时，至少要将修改前后的数据都保存下来

隔离级别与MVCC

全称Multi-Version Concurrency Control，多版本并发控制，MVCC在MySQL InnoDB中的实现主要是为了提高数据库的并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

Read View（读视图）

当前读

像select lock in share mode（锁）、 select for update、 update、insert、delete（排他锁）这些操作都是当前读，他读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

快照读

像不加锁的select操作就是快照读，即不加锁的非阻塞读，快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读，快照读读取的是快照，他是通过readView实现的

实现原理

Read View就是事务进行快照读的时候生产的读视图(Read View)，在该事务执行快照读的那一刻，会生成数据库系统当前的一个快照

Read View结构

m_ids：生成该readview时，当前系统中活跃的事务id列表

min_trx_id：当前系统活跃事务中最小的事务id，他也是m_ids的最小值

max_trx_id：当前系统中计划分配给下一个事务的id，他可能是m_ids的最大值+1，也可能比他大

creator_trx_id：生成这个readView的事务id

想象成是一个Java实例

快照读原理解析

在一个事务读取数据时，会根据当前数据形成一个readview，按照逻辑规则进行读取

逻辑规则

如果被访问数据的事务trx_id和readView中的creator_trx_id值相同，意味着自己在访问自己修改过的记录，当然可以被访问

如果被访问数据的事务trx_id小于readView中的min_trx_id值，说明生成这个版本数据的事务，在生成readview前已经提交，这样的数据也可以访问

大白话：这个数据之前被其他的事务修改过，但是事务已经提交，所以这个版本的数据是可以使用的，这样不会产生脏读

如果被访问数据的事务trx_id大于等于readView中的max_trx_id值，说明生成这个版本数据的事务，是在生成readview后开启，这样的数据不应该被访问

大白话：你读取数据之后，有人修改了当前数据，那人家后边修改的数据，你也不能读

如果被访问数据的事务trx_id如果在min_trx_id和max_trx_id范围内，则需要判断是不是在m_ids中（目的是判断这个数据是不是已经提交），如果在，说明生成这个版本的事务还是活跃的，没有提交的事务产生的数据当然不能读，如果不在，说明事务已经提交，该数据可以被访问

大白话：这个数据被现在活跃的其他事务正在修改中，读取时要看此时这个事务是不是已经提交，目的也是为了不要读取别人未提交的事务

解决脏读和不可重复读

对于RU隔离级别的事务来说，由于可以读取到未提交的事务，所有直接读取最新的记录（当前读）就可以，对于serializable的事务来说，必须使用加锁的方式来访问

脏读问题

加锁方式

一个事务读取了数据之后，立马给这个数据加写锁，不允许其他事务进行修改，这是加锁解决脏读

使用undo+mvcc

所有事务对数据的修改，记录成版本链，使用readview进行版本选择，每个事务只能读取满足条件的数据，这个过程不需要加锁

使用mvcc很好的解决了读写操作的并发执行，而且采用了无锁机制

不可重复读

RC和RR两个隔离级别解决不可重复读是通过生成readview时间不同

RC隔离级别

同一个事务中每次读取数据时都生成一个新的ReadView，两次读取时，如果中间有其他事务进行提交，可能会生成两个不同的readview，导致当前事务中，两次读取的数据不一致，这就是不可重复读

RR隔离级别

同一个事务中只在第一次读取数据时生成一个ReadView，以后这个事务中一直使用这个readview，那么同一个事务中就能保证多次读取的数据是一致的

正确的开始，微小的长进，然后持续，嘿，我是小博，带你一起看我目之所及的世界……