MySQL高级篇4

READ COMMITTED ：每次读取数据前都生成一个ReadView。

使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个 ReadView ，之后的查询就不会重复生成了。

假设现在表 student 中只有一条数据，数据内容中，主键 id=1，隐藏的 trx_id=10，它的 undo log 如下图所示。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RTHxBOmd-1656250508374)(https://cdn.jsdelivr.net/gh/aoshihuankong/cloudimg@master/img/202204051556631.png)]

假设现在有事务 A 和事务 B 并发执行，事务 A的事务 id 为20，事务 B的事务 id 为30。

步骤1：事务 A 开始第一次查询数据，查询的 SQL 语句如下。

select * from student where id >= 1;

在开始查询之前，MySQL 会为事务 A 产生一个 ReadView，此时 ReadView 的内容如下：trx_ids= [20,30]，up_limit_id=20，low_limit_id=31，creator_trx_id=20。

由于此时表 student 中只有一条数据，且符合 where id>=1 条件，因此会查询出来。然后根据 ReadView机制，发现该行数据的trx_id=10，小于事务 A 的 ReadView 里 up_limit_id，这表示这条数据是事务 A 开启之前，其他事务就已经提交了的数据，因此事务 A 可以读取到。

结论：事务 A 的第一次查询，能读取到一条数据，id=1。

步骤2：接着事务 B(trx_id=30)，往表 student 中新插入两条数据，并提交事务。

insert into student(id,name) values(2,'李四'); 
insert into student(id,name) values(3,'王五');

此时表student 中就有三条数据了，对应的 undo 如下图所示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ic2dvGmO-1656250508376)(https://cdn.jsdelivr.net/gh/aoshihuankong/cloudimg@master/img/202204051559345.png)]

步骤3：接着事务 A 开启第二次查询，根据可重复读隔离级别的规则，此时事务 A 并不会再重新生成ReadView。此时表 student 中的 3 条数据都满足 where id>=1 的条件，因此会先查出来。然后根据ReadView 机制，判断每条数据是不是都可以被事务 A 看到。

1）首先 id=1 的这条数据，前面已经说过了，可以被事务 A 看到。

2）然后是 id=2 的数据，它的 trx_id=30，此时事务 A 发现，这个值处于 up_limit_id 和 low_limit_id 之间，因此还需要再判断 30 是否处于 trx_ids 数组内。由于事务 A 的 trx_ids=[20,30]，因此在数组内，这表示 id=2 的这条数据是与事务 A 在同一时刻启动的其他事务提交的，所以这条数据不能让事务 A 看到。

3）同理，id=3 的这条数据，trx_id 也为 30，因此也不能被事务 A 看见。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hNBgbjik-1656250508377)(https://cdn.jsdelivr.net/gh/aoshihuankong/cloudimg@master/img/202204051559867.png)]

结论：最终事务 A 的第二次查询，只能查询出 id=1 的这条数据。这和事务 A 的第一次查询的结果是一样的，因此没有出现幻读现象，所以说在 MySQL 的可重复读隔离级别下，不存在幻读问题。

这里介绍了MVCC在READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行快照读操作时访问记录的版本链的过程。这样使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

核心点在于 ReadView 的原理，READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是生成ReadView的时机不同：

• READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView
• REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。

MySQL有不同类型的日志文件，用来存储不同类型的日志，分为二进制日志、错误日志、通用查询日志和慢查询日志，这也是常用的4种。MySQL 8又新增两种支持的日志：中继日志和数据定义语句日志。使用这些日志文件，可以查看MySQL内部发生的事情。

• **慢查询日志：**记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。

• **通用查询日志：**记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。

• **错误日志：**记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。

• **二进制日志：**记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。

• **中继日志：**用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。

• **数据定义语句日志：**记录数据定义语句执行的元数据操作。

除二进制日志外，其他日志都是文本文件。默认情况下，所有日志创建于MySQL数据目录中。

• 日志功能会降低MySQL数据库的性能。

• 日志会占用大量的磁盘空间。

通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助我们准确定位问题。

mysql> SHOW VARIABLES LIKE '%general%';

方式1：永久性方式

[mysqld] 
general_log=ON 
general_log_file=[path[filename]] #日志文件所在目录路径，filename为日志文件名

方式2：临时性方式

SET GLOBAL general_log=on; # 开启通用查询日志
SET GLOBAL general_log_file=’path/filename’; # 设置日志文件保存位置
SET GLOBAL general_log=off; # 关闭通用查询日志
SHOW VARIABLES LIKE 'general_log%'; # 查看设置后情况

方式1：永久性方式

[mysqld] 
general_log=OFF

方式2：临时性方式

SET GLOBAL general_log=off;
SHOW VARIABLES LIKE 'general_log%';

在MySQL数据库中，错误日志功能是默认开启的。而且，错误日志无法被禁止。

[mysqld] 
log-error=[path/[filename]] #path为日志文件所在的目录路径，filename为日志文件名

mysql> SHOW VARIABLES LIKE 'log_err%';

install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log
mysqladmin -uroot -p flush-logs

mysql> show variables like '%log_bin%';

方式1：永久性方式

[mysqld] 
#启用二进制日志 
log-bin=atguigu-bin 
binlog_expire_logs_seconds=600 max_binlog_size=100M

设置带文件夹的bin-log日志存放目录

[mysqld] 
log-bin="/var/lib/mysql/binlog/atguigu-bin"

注意：新建的文件夹需要使用mysql用户，使用下面的命令即可。

chown -R -v mysql:mysql binlog

方式2：临时性方式

# global 级别 
mysql> set global sql_log_bin=0; 
ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION variable and can`t be used with SET GLOBAL 

# session级别 
mysql> SET sql_log_bin=0; 
Query OK, 0 rows affected (0.01 秒)

mysqlbinlog -v "/var/lib/mysql/binlog/atguigu-bin.000002"
# 不显示binlog格式的语句
mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/atguigu-bin.000002"

# 可查看参数帮助 
mysqlbinlog --no-defaults --help 

# 查看最后100行 
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |tail -100 

# 根据position查找 
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |grep -A20 '4939002'

上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

• IN 'log_name'：指定要查询的binlog文件名（不指定就是第一个binlog文件）
• FROM pos：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
• LIMIT [offset]：偏移量(不指定就是0)
• row_count:查询总条数（不指定就是所有行）

mysql> show binlog events in 'atguigu-bin.000002';

mysqlbinlog恢复数据的语法如下：

mysqlbinlog [option] filename|mysql –uuser -ppass;

• filename：是日志文件名。
• option：可选项，比较重要的两对option参数是–start-date、–stop-date 和 --start-position、-- stop-position。
  • --start-date 和 --stop-date：可以指定恢复数据库的起始时间点和结束时间点。
  • --start-position和--stop-position：可以指定恢复数据的开始位置和结束位置。

注意：使用mysqlbinlog命令进行恢复操作时，必须是编号小的先恢复，例如atguigu-bin.000001必须在atguigu-bin.000002之前恢复。

1. PURGE MASTER LOGS：删除指定日志文件

PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’ 
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mE07m2wK-1656250508378)(https://cdn.jsdelivr.net/gh/aoshihuankong/cloudimg@master/img/202204051630535.png)]

write和fsync的时机，可以由参数sync_binlog控制，默认是 0。为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的binglog 会丢失。如下图：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zXkpxMYo-1656250508378)(https://cdn.jsdelivr.net/gh/aoshihuankong/cloudimg@master/img/202204051631346.png)]

为了安全起见，可以设置为1，表示每次提交事务都会执行fsync，就如同redo log 刷盘流程一样。最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2uevENl9-1656250508379)(https://cdn.jsdelivr.net/gh/aoshihuankong/cloudimg@master/img/202204051632526.png)]

在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样的，如果机器宕机，会丢失最近N个事务的binlog日志。

• redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生的。
• 而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。
• 虽然它们都属于持久化的保证，但是侧重点不同。
  • redo log 让InnoDB存储引擎拥有了崩溃恢复能力。
  • binlog保证了MySQL集群架构的数据一致性

中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。

如果从服务器宕机，有的时候为了系统恢复，要重装操作系统，这样就可能会导致你的服务器名称与之前不同。而中继日志里是包含从服务器名的。在这种情况下，就可能导致你恢复从服务器的时候，无法从宕机前的中继日志里读取数据，以为是日志文件损坏了，其实是名称不对了。

解决的方法也很简单，把从服务器的名称改回之前的名称。

一般应用对数据库而言都是“读多写少”，也就说对数据库读取数据的压力比较大，有一个思路就是采用数据库集群的方案，做主从架构、进行读写分离，这样同样可以提升数据库的并发处理能力。但并不是所有的应用都需要对数据库进行主从架构的设置，毕竟设置架构本身是有成本的。

如果我们的目的在于提升数据库高并发访问的效率，那么首先考虑的是如何优化SQL和索引，这种方式简单有效；其次才是采用缓存的策略，比如使用 Redis将热点数据保存在内存数据库中，提升读取的效率；最后才是对数据库采用主从架构，进行读写分离。

第1个作用：读写分离。

第2个作用就是数据备份。

第3个作用是具有高可用性。

三个线程

实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于3 个线程来操作，一个主库线程，两个从库线程。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mC4bASzK-1656250508382)(https://cdn.jsdelivr.net/gh/aoshihuankong/cloudimg@master/img/202204051646097.png)]

二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。

从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。

从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qUkamHNA-1656250508382)(https://cdn.jsdelivr.net/gh/aoshihuankong/cloudimg@master/img/202204051647759.png)]

复制三步骤

步骤1：Master将写操作记录到二进制日志（binlog）。

步骤2：Slave将Master的binary log events拷贝到它的中继日志（relay log）；

步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从接入点开始复制。

复制的问题

复制的最大问题：延时

• 每个Slave只有一个Master

• 每个Slave只能有一个唯一的服务器ID

• 每个Master可以有多个Slave

主从同步的要求：

• 读库和写库的数据一致(最终一致)；

• 写数据必须写到写库；

• 读数据必须到读库(不一定)；

进行主从同步的内容是二进制日志，它是一个文件，在进行网络传输的过程中就一定会存在主从延迟（比如 500ms），这样就可能造成用户在从库上读取的数据不是最新的数据，也就是主从同步中的数据不一致性问题。

在网络正常的时候，日志从主库传给从库所需的时间是很短的，即T2-T1的值是非常小的。即，网络正常情况下，主备延迟的主要来源是备库接收完binlog和执行完这个事务之间的时间差。

**主备延迟最直接的表现是，从库消费中继日志（relay log）的速度，比主库生产binlog的速度要慢。**造成原因：

1、从库的机器性能比主库要差

2、从库的压力大

3、大事务的执行

若想要减少主从延迟的时间，可以采取下面的办法：

1. 降低多线程大事务并发的概率，优化业务逻辑

2. 优化SQL，避免慢SQL，减少批量操作，建议写脚本以update-sleep这样的形式完成。

3. 提高从库机器的配置，减少主库写binlog和从库读binlog的效率差。

4. 尽量采用短的链路，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网络延时。

5. 实时性要求的业务读强制走主库，从库只做灾备，备份。

读写分离情况下，解决主从同步中数据不一致的问题， 就是解决主从之间 数据复制方式 的问题，如果按照数据一致性 从弱到强 来进行划分，有以下 3 种复制方式。

方法 1：异步复制

方法 2：半同步复制

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lIkVbUsv-1656250508383)(https://cdn.jsdelivr.net/gh/aoshihuankong/cloudimg@master/img/202204051655175.png)]

方法 3：组复制

首先我们将多个节点共同组成一个复制组，在执行读写（RW）事务的时候，需要通过一致性协议层（Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节点）的同意，大多数指的是同意的节点数量需要大于 （N/2+1），这样才可以进行提交，而不是原发起方一个说了算。而针对只读（RO）事务则不需要经过组内同意，直接 COMMIT 即可。

物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大，MySQL中可以用xtrabackup工具来进行物理备份。

逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。MySQL 中常用的逻辑备份工具为mysqldump。逻辑备份就是备份sql语句，在恢复的时候执行备份的sql语句实现数据库数据的重现。

mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名 称.sql

mysqldump -uroot -p atguigu>atguigu.sql #备份文件存储在当前目录下
mysqldump -uroot -p atguigudb1 > /var/lib/mysql/atguigu.sql

mysqldump -uroot -pxxxxxx --all-databases > all_database.sql 
mysqldump -uroot -pxxxxxx -A > all_database.sql

mysqldump –u user –h host –p --databases [数据库的名称1 [数据库的名称2...]] > 备份文件名 称.sql

mysqldump -uroot -p --databases atguigu atguigu12 >two_database.sql
mysqldump -uroot -p -B atguigu atguigu12 > two_database.sql

mysqldump –u user –h host –p 数据库的名称 [表名1 [表名2...]] > 备份文件名称.sql

mysqldump -uroot -p atguigu book> book.sql
#备份多张表 
mysqldump -uroot -p atguigu book account > 2_tables_bak.sql

mysqldump -uroot -p atguigu student --where="id < 10 " > student_part_id10_low_bak.sql

mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql

• 只备份结构

mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql

• 只备份数据

mysqldump -uroot -p atguigu --no-create-info > atguigu_no_create_info_bak.sql

mysqldump -uroot -p -R -E --databases atguigu > fun_atguigu_bak.sql

mysql –u root –p [dbname] < backup.sql

#备份文件中包含了创建数据库的语句
mysql -uroot -p < atguigu.sql
#备份文件中不包含了创建数据库的语句
mysql -uroot -p atguigu4< atguigu.sql

mysql –u root –p < all.sql

sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql > atguigu.sql 
#分离完成后我们再导入atguigu.sql即可恢复单个库

cat atguigu.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > class_structure.sql 
cat atguigu.sql | grep --ignore-case 'insert into `class`' > class_data.sql 
#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复 

use atguigu; 
mysql> source class_structure.sql; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 

mysql> source class_data.sql; 
Query OK, 1 row affected (0.01 sec)

1. 使用SELECT…INTO OUTFILE导出文本文件

SHOW GLOBAL VARIABLES LIKE '%secure%';
SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";

2. 使用mysqldump命令导出文本文件

mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account
# 或
mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account --fields-terminated- by=',' --fields-optionally-enclosed-by='\"'

3. 使用mysql命令导出文本文件

mysql -uroot -p --execute="SELECT * FROM account;" atguigu> "/var/lib/mysql-files/account.txt"

1. 使用LOAD DATA INFILE方式导入文本文件

LOAD DATA INFILE '/var/lib/mysql-files/account_0.txt' INTO TABLE atguigu.account;
# 或
LOAD DATA INFILE '/var/lib/mysql-files/account_1.txt' INTO TABLE atguigu.account FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

2. 使用mysqlimport方式导入文本文件

mysqlimport -uroot -p atguigu '/var/lib/mysql-files/account.txt' --fields-terminated- by=',' --fields-optionally-enclosed-by='\"'