MySQL高级篇4

最新推荐文章于 2023-06-06 20:06:05 发布
最新推荐文章于 2023-06-06 20:06:05 发布
阅读量331 收藏
点赞数
文章标签： sql mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_64892750/article/details/125474713
版权

第16章 多版本并发控制

1. 什么是MVCC

MVCC （Multiversion Concurrency Control），多版本并发控制。顾名思义，MVCC 是通过数据行的多个版本管理来实现数据库的并发控制。这项技术使得在InnoDB的事务隔离级别下执行一致性读操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。

2. 快照读与当前读

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读，而这个读指的就是快照读, 而非当前读。当前读实际上是一种加锁的操作，是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式。

2.1 快照读

快照读又叫一致性读，读取的是快照数据。不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞读。

之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于MVCC，它在很多情况下，避免了加锁操作，降低了开销。

既然是基于多版本，那么快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

2.2 当前读

当前读读取的是记录的最新版本（最新数据，而不是历史版本的数据），读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加锁的 SELECT，或者对数据进行增删改都会进行当前读。

3. 复习
3.1 再谈隔离级别

我们知道事务有 4 个隔离级别，可能存在三种并发问题：

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

另图：

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

3.2 隐藏字段、Undo Log版本链

回顾一下undo日志的版本链，对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列。

  • trx_id：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id 隐藏列。
  • roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo日志 中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。
4. MVCC实现原理之ReadView

MVCC 的实现依赖于：隐藏字段、Undo Log、Read View。

4.1 什么是ReadView

ReadView就是事务在使用MVCC机制进行快照读操作时产生的读视图。当事务启动时，会生成数据库系统当前的一个快照，InnoDB为每个事务构造了一个数组，用来记录并维护系统当前活跃事务的ID（“活跃”指的就是，启动了但还没提交）。

4.2 设计思路

使用READ UNCOMMITTED隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。

使用SERIALIZABLE隔离级别的事务，InnoDB规定使用加锁的方式来访问记录。

使用READ COMMITTED和REPEATABLE READ隔离级别的事务，都必须保证读到已经提交了的事务修改过的记录。假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是需要判断一下版本链中的哪个版本是当前事务可见的，这是ReadView要解决的主要问题。

这个ReadView中主要包含4个比较重要的内容，分别如下：

  1. creator_trx_id，创建这个 Read View 的事务 ID。

说明：只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为0。

  1. trx_ids，表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。

  2. up_limit_id，活跃的事务中最小的事务 ID。

  3. low_limit_id，表示生成ReadView时系统中应该分配给下一个事务的id值。low_limit_id 是系统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID。

注意：low_limit_id并不是trx_ids中的最大值，事务id是递增分配的。比如，现在有id为1， 2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，trx_ids就包括1和2，up_limit_id的值就是1，low_limit_id的值就是4。

4.3 ReadView的规则

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见。

  • 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
  • 如果被访问版本的trx_id属性值小于ReadView中的up_limit_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
  • 如果被访问版本的trx_id属性值大于或等于ReadView中的low_limit_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
  • 如果被访问版本的trx_id属性值在ReadView的up_limit_id和low_limit_id之间，那就需要判断一下trx_id属性值是不是在 trx_ids 列表中。
    • 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。
    • 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。
4.4 MVCC整体操作流程

了解了这些概念之后，我们来看下当查询一条记录的时候，系统如何通过MVCC找到它：

  1. 首先获取事务自己的版本号，也就是事务 ID；

  2. 获取 ReadView；

  3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；

  4. 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；

  5. 最后返回符合规则的数据。

在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次Read View。

如表所示：

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

注意，此时同样的查询语句都会重新获取一次 Read View，这时如果 Read View 不同，就可能产生不可重复读或者幻读的情况。

当隔离级别为可重复读的时候，就避免了不可重复读，这是因为一个事务只在第一次 SELECT 的时候会获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View，如下表所示：

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

5. 举例说明
5.1 READ COMMITTED隔离级别下

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

5.2 REPEATABLE READ隔离级别下

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

5.3 如何解决幻读

假设现在表 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 的可重复读隔离级别下，不存在幻读问题。

6. 总结

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

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

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

第17章 其它数据库日志

1. MySQL支持的日志
1.1 日志类型

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

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

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

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

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

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

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

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

1.2 日志的弊端

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

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

2. 通用查询日志(general query log)

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

2.1 查看当前状态
mysql> SHOW VARIABLES LIKE '%general%';
2.2 启动日志

方式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%'; # 查看设置后情况
2.3 停止日志

方式1：永久性方式

[mysqld] 
general_log=OFF

方式2：临时性方式

SET GLOBAL general_log=off;
SHOW VARIABLES LIKE 'general_log%';
3.错误日志(error log)
3.1 启动日志

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

[mysqld] 
log-error=[path/[filename]] #path为日志文件所在的目录路径，filename为日志文件名
3.2 查看日志
mysql> SHOW VARIABLES LIKE 'log_err%';
3.3 删除\刷新日志
install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log
mysqladmin -uroot -p flush-logs
4. 二进制日志(bin log)
4.1 查看默认情况
mysql> show variables like '%log_bin%';
4.2 日志参数设置

方式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 秒)
4.3 查看日志
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';
4.4 使用日志恢复数据

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之前恢复。

4.5 删除二进制日志

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

PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’ 
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’
5. 再谈二进制日志(binlog)
5.1 写入机制

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日志。

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

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。

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

使用两阶段提交后，写入binlog时发生异常也不会有影响

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

另一个场景，redo log设置commit阶段发生异常，那会不会回滚事务呢？

image-20220405163927129

并不会回滚事务，它会执行上图框住的逻辑，虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。

6. 中继日志(relay log)
6.1 介绍

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

6.2 恢复的典型错误

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

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

第18章 主从复制

1. 主从复制概述
1.1 如何提升数据库并发能力

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

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

1.2 主从复制的作用

第1个作用：读写分离。

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

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

2. 主从复制的原理
2.1 原理剖析

三个线程

实际上主从同步的原理就是基于 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复制是异步的且串行化的，而且重启后从接入点开始复制。

复制的问题

复制的最大问题：延时

2.2 复制的基本原则

  • 每个Slave只有一个Master

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

  • 每个Master可以有多个Slave

3. 同步数据一致性问题

主从同步的要求：

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

  • 写数据必须写到写库；

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

3.1 理解主从延迟问题

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

3.2 主从延迟问题原因

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

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

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

2、从库的压力大

3、大事务的执行

3.3 如何减少主从延迟

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

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

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

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

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

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

3.4 如何解决一致性问题

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

方法 1：异步复制

image-20220405165455998

方法 2：半同步复制

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

方法 3：组复制

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

image-20220405165650425

第19章 数据库备份与恢复

1. 物理备份与逻辑备份

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

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

2. mysqldump实现逻辑备份
2.1 备份一个数据库
mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名 称.sql

mysqldump -uroot -p atguigu>atguigu.sql #备份文件存储在当前目录下
mysqldump -uroot -p atguigudb1 > /var/lib/mysql/atguigu.sql
2.2 备份全部数据库
mysqldump -uroot -pxxxxxx --all-databases > all_database.sql 
mysqldump -uroot -pxxxxxx -A > all_database.sql
2.3 备份部分数据库
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
2.4 备份部分表
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
2.5 备份单表的部分数据
mysqldump -uroot -p atguigu student --where="id < 10 " > student_part_id10_low_bak.sql
2.6 排除某些表的备份
mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql
2.7 只备份结构或只备份数据
  • 只备份结构
mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql
  • 只备份数据
mysqldump -uroot -p atguigu --no-create-info > atguigu_no_create_info_bak.sql
2.8 备份中包含存储过程、函数、事件
mysqldump -uroot -p -R -E --databases atguigu > fun_atguigu_bak.sql
3. mysql命令恢复数据
mysql –u root –p [dbname] < backup.sql
3.1 单库备份中恢复单库
#备份文件中包含了创建数据库的语句
mysql -uroot -p < atguigu.sql
#备份文件中不包含了创建数据库的语句
mysql -uroot -p atguigu4< atguigu.sql
3.2 全量备份恢复
mysql –u root –p < all.sql
3.3 从全量备份中恢复单库
sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql > atguigu.sql 
#分离完成后我们再导入atguigu.sql即可恢复单个库
3.4 从单库备份中恢复单表
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)
4. 表的导出与导入
4.1 表的导出

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"
4.2 表的导入

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='\"'
栗子举一个
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MYsql高级进阶篇哦
11-10
MYsql高级进阶篇哦
数据库 MySQL 学习笔记高级篇.md
09-01
数据库 MySQL 学习笔记高级篇.md
MySQL高级篇1
m0_64892750的博客
06-28 714
检查mysql service： 1.2 MySQL的卸载 1. 关闭 mysql 服务 2. 查看当前 mysql 安装状况 3. 卸载上述命令查询出的已安装程序 务必卸载干净，反复执行确认是否有卸载残留4. 删除 mysql 相关文件 删除上述命令查找出的相关文件 5.删除 my.cnf 2. MySQL的Linux版安装 2.1 CentOS7下检查MySQL依赖 1. 检查/tmp临时目录权限（必不可少）由于mysql安装过程中，会通过mysql用户在/tmp目录下新建tmp_db文件，所
07_InnoDB数据存储结构
qq_48083892的博客
10-21 134
实战
数据库事务
孟庆云的博客
03-20 1094
1 前言 关于事务，说简单点就是一串原子操作，说复杂又牵扯出好多疑问：为啥需要事务；ACID是干啥的，是怎么保证的；隔离级别和并发问题是什么关系，底层是怎么实现…带着这些问题，让我们开始学习事务吧 2 为什么要有事务 现实中存在很多一组操作需要顺序完成的场景，比如转账至少包括A账户减少和B账户增加两步操作，我们希望他们能要么同事执行，要么同时失败，并且操作前后数据库都要处于正确状态。 存在的问题（...
记一次binlog恢复Mysql某张表数据的过程
熊浪的博客
06-06 1762
7、把binlog文件转为sql文件（需要转换几个，修改binlog文件名和生成的sql名称就可以了）2、要用户不能操作（如果不能停止，可以新建一个库，所有的binlog执行操作在新库执行）。8、继续新开一个窗口，进入刚刚生成SQL的文件目录下。6、新开一个窗口，找到 mysqlbinlog所在。登录成功后，通过source命令，加载sql文件。可以看出，文件主要更新所在文件是175.sql。10、使用mysql的root用户登录。14、同步刚刚导出的表数据；12、导出这张表的数据。binlog文件所在。
使用免费的CDN平台jsDelivr搭建个人cdn资源网盘
仿站、源码搭建/迁移技术博客
05-15 3847
放在Github的资源在国内加载速度比较慢，因此需要使用CDN加速来优化网站打开速度，jsDelivr + Github便是免费且好用的CDN，非常适合博客网站使用。 1、什么是CDN CDN的全称是Content Delivery Network，即内容分发网络。CDN是构建在现有网络基础之上的智能虚拟网络，依靠部署在各地的边缘服务器，通过中心平台的负载均衡、内容分发、调度等功能模块，使用户就近获取所需内容，降低网络拥塞，提高用户访问响应速度和命中率。CDN的关键技术主要有内容存储和分发技术。简而言之提
尚硅谷MySQL高级_笔记
10-05
尚硅谷MySQL高级的视频学习笔记，由本人整理
MySQL数据库管理高级篇(二)
06-14
MySQL是一个关系型数据库管理系统，目前属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一。...本课程，是基于《 mysql 数据管理基础篇 》进行的讲解，请仔细查看目录，是否符合自己的需求。
MySQL高级.zip
08-10
主键自动建立唯一索引 频繁作为查询条件的字段应该建立索引 查询中与其他表关联的字段，外键关系建立索引 频繁更新的字段不适合创建索引 -- 因为每次更新不单单是更新了记录还会更新索引 Where条件里用不到的字段不...
MySQL数据库高级篇教程 B站康师傅笔记（持续更新）
FenQing1213的博客
05-16 4104
第二部分 MySQL高级特性篇 第01章 Linux下MySQL的安装与使用 1. 安装前说明 1.1 查看是否安装过MySQL 如果你是用rpm安装, 检查一下RPM PACKAGE： rpm -qa | grep -i mysql # -i 忽略大小写 检查mysql service： systemctl status mysqld.service 1.2 MySQL的卸载 1. 关闭 mysql 服务 systemctl stop mysqld.service 2. 查看当前 my
MySQL-高级-12 mysql锁
weixin_45483328的博客
05-10 604
表锁、行锁、页锁
十分钟搞懂MySQL 如何实现四大隔离级别
程序猿踩坑集锦
04-21 1452
什么是MVCC？ MVCC的全称是“多版本并发控制”。这项技术使得InnoDB的事务隔离级别下执行一致性读操作有了保证，换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值。这是一个可以用来增强并发性的强大的技术，因为这样的一来的话查询就不用等待另一个事务释放锁。这项技术在数据库领域并不是普遍使用的。一些其它的数据库产品，以及mysql其它的存储引擎并不支持它。 在MySQL的众多存储引擎中，只有InnoDB支持事务，所有这里说的事务隔离级别指的是InnoDB下的事务隔离..
MySQL MVCC 多版本并发控制机制 工作原理
11-06 408
MySQL MVCC 多版本并发控制机制 工作原理
mysql中MVCC原理
hua8367741711989的专栏
04-16 111
MVCC 是啥？ MVCC 的英文全称是 Multiversion Concurrency Control ，中文意思是多版本并发控制技术。原理是，通过数据行的多个版本管理来实现数据库的并发控制，简单来说就是保存数据的历史版本。可以通过比较版本号决定数据是否显示出来。读取数据的时候不需要加锁可以保证事务的隔离效果。 MVCC 可以解决什么问题？ 读写之间阻塞的问题，通过 MVCC 可以让读写互相不阻塞，读不相互阻塞，写不阻塞读，这样可以提升数据并发处理能力。 降低了死锁的概率，这个是因为 MV...
数据库MVCC多版本并发控制原理
m0_37615662的博客
05-08 442
MVCC是在并发访问数据库时，通过对数据做多版本管理，避免因为写锁的阻塞而造成读数据的并发阻塞问题。通俗的讲就是MVCC通过保存数据的历史版本，根据比较版本号来处理数据的是否显示，从而达到读取数据的时候不需要加锁就可以保证事务隔离性的效果。在MySQL中，MVCC只在。
MySQL——通过binlog恢复数据
热门推荐
醉酒的戈多的专栏
04-07 4万+
二进制日志(binnary log)以事件形式记录了对MySQL数据库执行更改的所有操作。binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE、DROP等）以及表数据修改（INSERT、UPDATE、DELETE、TRUNCATE等）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。
mysql集群搭建(一主一从、主从复制、读写分离)
u014014014的博客
11-24 434
mysql集群搭建(一主一从、主从复制、读写分离)
【数据库】MySQL的ReadView
Cool_Wayen的博客
08-16 3715
MYSQL的READ VIEW机制
宋红康mysql高级篇
最新发布
08-23
对于MySQL高级篇，以下是一些主题和问题的可能讨论： 1. 存储引擎： - 请问MySQL支持哪些存储引擎？ - InnoDB和MyISAM之间的区别是什么？ - 如何选择正确的存储引擎？ 2. 数据库优化： - 介绍一下索引，如何创建和...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值