Mysql高级部分系列（七）

1、多版本并发控制

1.1、什么是MVCC

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

1.2、快照读与当前读

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

1.2.1、快照读

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

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

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

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

1.2.2、当前读

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

1.3、复习

1.3.1、再谈隔离级别

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

另图：

1.3.2、隐藏字段、Undo Log版本链

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

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

1.4、MVCC实现原理之ReadView

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

1.4.1、什么是ReadView

在MVCC机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在Undo Log里。如果一个事务想要查询这个行记录，需要读取哪个版本的行记录呢?这时就需要用到ReadView了，它帮我们解决了行的可见性问题。

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

1.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。

2. trx_ids，表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
3. up_limit_id，活跃的事务中最小的事务 ID。
4. 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。

1.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时生成该版本的事务已经被提交，该版本可以被访问。

1.4.4、MVCC整体操作流程

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

1. 首先获取事务自己的版本号，也就是事务 ID；
2. 获取 ReadView；
3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；
4. 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；
5. 最后返回符合规则的数据。

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

如表所示：

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

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

1.5、举例说明

1.5.1、READ COMMITTED隔离级别下

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

1.5.2、REPEATABLE READ隔离级别下

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

1.5.3、如何解决幻读

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

假设现在有事务 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 如下图所示：

步骤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 看见。

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

1.6、总结

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

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

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

说明:我们之前说执行DELETE语句或者更新主键的UPDATE语句并不会立即把对应的记录完全从页面中删除，而是执行一个所谓的
delete mark操作，相当于只是对记录打上了一个删除标志位，这主要就是为MVCC服务的。

通过MVCC 我们可以解决:

1. 读写之间阻塞的问题。通过 Mycc可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事
   务并发处理能力。
2. 降低了死锁的概率。这是因为MVCC采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁
   定必要的行。
3. 解决快照读的问题。当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。

1.7、总结概述

MySQL 的多版本并发控制 (Multi-Version Concurrency Control，简称 MVCC) 是一种数据库系统在处理多个事务时的保证数据一致性控制机制。MVCC 允许多个事务在同时读取和修改数据库，同时保证每个事务看到的数据是一致的，而且不会出现读取到脏数据的情况。

MVCC 的核心思想是：每个事务都可以看到数据库的一个“快照”，也就是一个特定版本的数据。并且每个事务读取的数据都来自于这个快照，而不是直接读取当前的数据。同时，每个事务对数据的修改也会生成一个新的版本，这个版本只对该事务可见，其他事务仍然可以看到旧版本的数据。

在 MySQL 中，MVCC 是通过使用 undo log 和 read view 来实现的。Undo log 记录了每个事务所做的修改操作，而 read view 则是事务所能看到的数据快照。当一个事务读取数据时，它会创建一个 read view，并根据该 read view 来决定应该读取哪个版本的数据。当一个事务更新数据时，它会将新版本的数据写入数据库，并将旧版本的数据记录到 undo log 中，以便在需要时可以回滚到旧版本。

需要注意的是，MVCC 只适用于读已提交和可重复读这两种隔离级别，而不适用于读未提交和串行化这两种隔离级别。此外，在使用 MVCC 时，需要注意一些性能问题，如 undo log 的增长和读取历史版本的代价。

2、其它数据库日志

2.1、MySQL支持的日志

2.1.1、日志类型

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

• 慢查询日志： 记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。
• 通用查询日志： 记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
• 错误日志： 记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。
• 二进制日志： 记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。
• 中继日志： 用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。
• 数据定义语句日志： 记录数据定义语句执行的元数据操作。

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

2.1.2、日志的弊端

• 日志功能会降低MySQL数据库的性能。
• 日志会占用大量的磁盘空间。

2.2、通用查询日志(general query log)

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

2.2.1、查看当前状态

mysql> SHOW VARIABLES LIKE '%general%';

2.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.2.3、停止日志

方式1：永久性方式

[mysqld] 
general_log=OFF

方式2：临时性方式

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

2.3、错误日志(error log)

错误日志记录了MySQL服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。通过错误日志可以查看系统的运行状态，便于即时发现故障、修复故障。如果MysQL服务出现异常，错误日志是发现问题、解决故障的首选。

2.3.1、启动日志

在MySQL数据库中，错误日志功能是默认开启的。而且，错误日志无法被禁止。
默认情况下，错误日志存储在MySQL数据库的数据文件夹下，名称默认为mysqld.log (Linux系统)或hostname.err (mac系统)。如果需要制定文件名，则需要在my.cnf或者my.ini中做如下配置:

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

2.3.2、查看日志

mysql> SHOW VARIABLES LIKE 'log_err%';

2.3.3、删除\刷新日志

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

flush-logs指令操作:

• MySQL 5.5.7以前的版本，flush-logs将错误日志文件重命名为filename.errlold，并创建新的日志文件。
• 从MysQL 5.5.7开始,flush-logs只是重新打开日志文件，并不做日志备份和创建的操作。
• 如果日志文件不存在，MySQL启动或者执行flush-logs时会自动创建新的日志文件。重新创建错误日志，大小为0字节。

2.4、二进制日志(bin log)

binlog可以说是MysQL中比较重要的日志了，在日常开发及运维过程中，经常会遇到。binlog即binary log，二进制日志文件，也叫作变更日志(update log)。它记录了数据库所有执行的DDL和DML等数据库更新事件的语句，但是不包含没有修改任何数据的语句(如数据查询语句select、show等，它以事件形式记录并保存在二进制文件中。通过这些信息，我们可以再现数据更新操作的全过程。

如果想要记录所有语句（例如，为了识别有问题的查询)，需要使用通用查询日志。

binlog主要应用场景:

• 一是用于数据恢复，如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
• 二是用于数据复制，由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

2.4.1、查看默认情况

mysql> show variables like '%log_bin%';

• log_bin_basename:是binlog日志的基本文件名，后面会追加标识来表示每一个文件
• log_bin_index:是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录
• log_bin_trust_function_creators :限制存储过程，前面我们已经讲过了，这是因为二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后，需要限制存储函数的创建、修改、调用
• log_bin_use_v1_row_events此只读系统变量已弃用。ON表示使用版本1二进制日志行，OFF表示使用版本2二进制日志行(MySQL 5.6的默认值为2)。

2.4.2、日志参数设置

方式1：永久性方式

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

提示:

• log-bin=mysql-bin #打开日志(主机需要打开)，这个mysql-bin也可以自定义，这里也可以加上路径,如:/home/www/mysql_bin_log/mysql-bin
• binlog_expire_logs_seconds:此参数控制二进制日志文件保留的时长，单位是秒，默认2592000 30天–14400 4小时; 86400 1天;259200 3天;
• max_binlog_size:控制单个二进制日志大小，当前日志文件大小超过此变量时，执行切换动作。此参数的最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，可能不做切换日志的动作，只能将该事务的所有sQL都记录进当前日志，直到事务结束。一般情况下可采取默认值。

设置带文件夹的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 秒)

2.4.3、查看日志

所有对数据库的修改都会记录在binglog中。但binlog是二进制文件，无法直接查看，想要更直观的观测它就要借助mysqlbinlog命令工具了。指令如下:在查看执行，先执行一条sQL语句，如下

update student set name='张三_back ' where id=1 ;

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';

2.4.4、使用日志恢复数据

如果MySQL服务器启用了二进制日志，在数据库出现意外丢失数据时，可以使用MySQLbinlog工具从指定的时间点开始（例如，最后一次备份）直到现在或另一个指定的时间点的日志中恢复数据。

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

2.4.5、删除二进制日志

MysQL的二进制文件可以配置自动删除↓同时MysQL也提供了安全的手动删除二进制文件的方法。PURGEMASTER LOGS只删除指定部分的二进制日志文件，RESET MASTER删除所有的二进制日志文件。具体如下:

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

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

举例:使用PURGE MASTER LOGS语句删除创建时间比binlog.000005早的所有日志

• 多次重新启动MySQL服务，便于生成多个日志文件。然后用SHOW语句显示二进制日志文件列表

SHOW BINARY LOGS;

• 执行PURGE MASTER LOGS语句删除创建时间比binlog.000005早的所有日志

PURGE MASTER LOGS TO "binlog. 000885";

• 显示二进制日志文件列表

SHOW BINARY LOGS;

比binlog.000005早的所有日志文件都已经被删除了。

举例:使用PURGEMASTER LOGS语句删除202o年10月25号前创建的所有日志文件。具体步骤如下:

• 显示二进制日志文件列表

SHOW BINARY LOGS;

• 执行mysqlbinlog命令查看二进制日志文件binlog.oo0005的内容

mysqlbinlog --no-defaults "/var/lib/mysql/binlog/atguigu-bin.000005"

结果可以看出20220105为日志创建的时间，即2022年1月05日。

• 使用PURGE MASTER LOGS语句删除2022年1月05日前创建的所有日志文件

PURGE MASTER LOGS before "20220105";

• 显示二进制日志文件列表

SHOW BINARY LOGS ;

2022年01月05号之前的二进制日志文件都已经被删除，最后一个没有删除，是因为当前在用，还未记录最后的时间，所以未被删除。

RESET MASTER:删除所有二进制日志文件

使用RESET MASTER语句，清空所有的binlog日志。MySQL会重新创建二进制文件，新的日志文从000001开始编号。慎用!
举例:使用RESET MASTER语句删除所有日志文件。

• 重启MySQL服务若干次，执行SHOW语句显示二进制日志文件列表。

SHOW BINARY LOGS;

• 执行RESET MASTER语句，删除所有日志文件

RESET MASTER;

执行完该语句后，原来的所有二进制日志已经全部被删除。

二进制日志可以通过数据库的全量条份和二进制日志中保存的增量信忌，完成数据厍的无损失恢复。但是，如果遇到数据量大、数据库和数据表很多(比如分库分表的应用)的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止位置不容易管理。

在这种情况下，一个有效的解决办法是配置主从数据库服务器，甚至是一主多从的架构，把二进制日志文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常等问题。

2.5、再谈二进制日志(binlog)

2.5.1、写入机制

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

我们可以通过binlog_cache_size参数控制单个线程 binlog cache大小，如果存储内容超过了这个参数，就要暂存到磁盘(Swap)。binlog日志刷盘流程如下:

• 上图的write，是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快。
• 上图的fsync，才是将数据持久化到磁盘的操作

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

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

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

2.5.2、binlog与redolog对比

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

2.5.3、两阶段提交

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

redo log与binlog两份日志之间的逻辑不一致，会出现什么问题？

由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。

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

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

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

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

2.6、中继日志(relay log)

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

文件名的格式是:从服务器名–relay-bin.序号。中继日志还有一个索引文件:从服务器名-relay-bin.index，用来定位当前正在使用的中继日志。

2.6.1、介绍

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

2.6.2、恢复的典型错误

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

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

3、主从复制

3.1、主从复制概述

3.1.1、如何提升数据库并发能力

在实际工作中，我们常常将Redis作为缓存与MySQL 配合来使用，当有请求的时候，首先会从缓存中进行查找，如果存在就直接取出。如果不存在再访问数据库，这样就提升了读取的效率，也减少了对后端数据库的访问压力。Redis的缓存架构是高并发架构中非常重要的一环。

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

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

3.1.2、主从复制的作用

3.1.2.1、第1个作用：读写分离。

我们可以通过主从复制的方式来同步数据，然后通过读写分离提高数据库并发处理能力。

其中一个是Master主库，负责写入数据，我们称之为:写库。其它都是slave从库，负责读取数据，我们称之为:读库。当主库进行更新的时候，会自动将数据复制到从库中，而我们在客户端读取数据的时候，会从从库中进行读取。

面对“读多写少”的需求，采用读写分离的方式，可以实现更高的并发访问。同时，我们还能对从服务器进行负载均衡，让不同的读请求按照策略均匀地分发到不同的从服务器上，让读取更加顺畅。读取顺畅的另一个原因，就是减少了锁表的影响，比如我们让主库负责写，当主库出现写锁的时候，不会影响到从库进行SELECT的读取。

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

我们通过主从复制将主库上的数据复制到了从库上，相当于是一种热备份机制，也就是在主库正常运行的情况下进行的备份，不会影响到服务。

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

数据备份实际上是一种冗余的机制，通过这种冗余的方式可以换取数据库的高可用性，也就是当服务器出现故障或宕机的情况下，可以切换到从服务器上，保证服务的正常运行。

关于高可用性的程度，我们可以用一个指标衡量，即正常可用时间/全年时间。比如要达到全年99.999%的时间都可用，就意味着系统在一年中的不可用时间不得超过3652460× (1-99.999%) =5.256分钟〈含系统崩溃的时间、日常维护操作导致的停机时间等)，其他时间都需要保持可用的状态。

实际上，更高的高可用性，意味着需要付出更高的成本代价。在现实中我们需要结合业务需求和成本来进行选择。

3.2、主从复制的原理

Slave会从Master读取binlpg来进行数据同步。

3.2.1、原理剖析

3.2.1.1、三个线程

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

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

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

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

注意:
不是所有版本的MySQL都默认开启服务器的二进制日志。在进行主从同步的时候，我们需要先检查服务器是否已经开启了二进制日志。
除非特殊指定，默认情况下从服务器会执行所有主服务器中保存的事件。也可以通过配置，使从服务器执行特定的事件。

3.2.1.2、复制三步骤

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

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

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

3.2.1.3、复制的问题

复制的最大问题：延时

3.2.2、复制的基本原则

• 每个Slave只有一个Master
• 每个Slave只能有一个唯一的服务器ID
• 每个Master可以有多个Slave

3.2.3、一主一从架构搭建

一台 主机 用于处理所有 写请求 ，一台 从机 负责所有 读请求 ，架构图如下：

3.2.3.1、准备工作

1. 准备 2台 CentOS 虚拟机
2. 每台虚拟机上需要安装好MySQL (可以是MySQL8.0 )
   说明：前面我们讲过如何克隆一台CentOS。大家可以在一台CentOS上安装好MySQL，进而通过克隆的方式复制出1台包含MySQL的虚拟机。

注意：克隆的方式需要修改新克隆出来主机的：

• ① MAC地址
• ② hostname
• ③ IP 地址
• ④ UUID 。

此外，克隆的方式生成的虚拟机（包含MySQL Server），则克隆的虚拟机MySQL Server的UUID相同，必须修改，否则在有些场景会报错。比如： show slave status\G ，报如下的错误：

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

修改MySQL Server 的UUID方式：

vim /var/lib/mysql/auto.cnf
systemctl restart mysqld

3.2.3.2、主机配置文件

建议mysql版本一致且后台以服务运行，主从所有配置项都配置在 [mysqld] 节点下，且都是小写字母。
具体参数配置如下：

• 必选

#[必须]主服务器唯一ID 
server-id=1 
#[必须]启用二进制日志,指名路径。比如：自己本地的路径
/log/mysqlbin log-bin=atguigu-bin

• 可选

#[可选] 0（默认）表示读写（主机），1表示只读（从机） 
read-only=0 
#设置日志文件保留的时长，单位是秒 
binlog_expire_logs_seconds=6000 
#控制单个二进制日志大小。此参数的最大和默认值是1GB
 max_binlog_size=200M 
 #[可选]设置不要复制的数据库 
 binlog-ignore-db=test 
 #[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave 
 binlog-do-db=需要复制的主数据库名字 
 #[可选]设置binlog格式 
 binlog_format=STATEMENT

binlog格式设置：

• 格式1：STATEMENT模式 （基于SQL语句的复制(statement-based replication, SBR)）

binlog_format=STATEMENT

每一条会修改数据的sql语句会记录到binlog中。这是默认的binlog格式。

SBR 的优点：

1. 历史悠久，技术成熟
2. 不需要记录每一行的变化，减少了binlog日志量，文件较小
3. binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况
4. binlog可以用于实时的还原，而不仅仅用于复制
5. 主从版本可以不一样，从服务器版本可以比主服务器版本高

SBR 的缺点：

1. 不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。

• 使用以下函数的语句也无法被复制：LOAD_FILE()、UUID()、USER()、FOUND_ROWS()、SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

  • INSERT … SELECT 会产生比 RBR 更多的行级锁
  • 复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁
  • 对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句
  • 对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响
  • 执行复杂语句如果出错的话，会消耗更多资源
  • 数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错。

• 格式2：ROW模式（基于行的复制(row-based replication, RBR)）

 binlog_format=ROW 

5.1.5版本的MySQL才开始支持，不记录每条sql语句的上下文信息，仅记录哪条数据被修改了，修改成什么样了。

RBR 的优点：

1. 任何情况都可以被复制，这对复制来说是最 安全可靠 的。（比如：不会出现某些特定情况下的存储过程、function、trigger的调用和触发无法被正确复制的问题）
2. 多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
3. 复制以下几种语句时的行锁更少：INSERT … SELECT、包含 AUTO_INCREMENT 字段的 INSERT、没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句
4. 执行 INSERT，UPDATE，DELETE 语句时锁更少
5. 从服务器上采用 多线程 来执行复制成为可能

RBR 的缺点：

1. binlog 大了很多
2. 复杂的回滚时 binlog 中会包含大量的数据
3. 主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写问题
4. 无法从 binlog 中看到都复制了些什么语句

• 格式3： MIXED模式（混合模式复制(mixed-based replication, MBR)）

binlog_format=MIXED 

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。
在Mixed模式下，一般的语句修改使用statment格式保存binlog。如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog。 MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

3.2.3.3、从机配置文件

要求主从所有配置项都配置在 my.cnf 的 [mysqld] 栏位下，且都是小写字母。

• 必选

#[必须]从服务器唯一ID 
server-id=2

• 可选

#[可选]启用中继日志 
relay-log=mysql-relay

重启后台mysql服务，使配置生效。

注意：主从机都关闭防火墙
service iptables stop #CentOS 6
systemctl stop firewalld.service #CentOS 7

3.2.3.4、主机：建立账户并授权

#在主机MySQL里执行授权主从复制的命令 
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'从机器数据库IP' IDENTIFIED BY 'abc123'; 
#5.5,5.7

注意：如果使用的是MySQL8，需要如下的方式建立账户，并授权slave：

CREATE USER 'slave1'@'%' IDENTIFIED BY '123456'; 
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%'; 
#此语句必须执行。否则见下面。
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

注意：在从机执行show slave status\G时报错：Last_IO_Error: error connecting to master ‘slave1@192.168.1.150:3306’ - retry-time: 60 >retries: 1 message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.

查询Master的状态，并记录下File和Position的值。

记录下File和Position的值

注意：执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化。

3.2.3.5、从机：配置需要复制的主机

• 步骤1：从机上复制主机的命令

CHANGE MASTER TO 
MASTER_HOST='主机的IP地址', 
MASTER_USER='主机用户名', 
MASTER_PASSWORD='主机用户名的密码', 
MASTER_LOG_FILE='mysql-bin.具体数字', 
MASTER_LOG_POS=具体值;

举例：

CHANGE MASTER TO 
MASTER_HOST='192.168.1.150',
MASTER_USER='slave1',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='atguigu-bin.000007',
MASTER_LOG_POS=154;

• 步骤2：

#启动slave同步 
START SLAVE;

如果报错：

可以执行如下操作，删除之前的relay_log信息。然后重新执行 CHANGE MASTER TO …语句即可。

mysql> reset slave; #删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件

接着，查看同步状态：

SHOW SLAVE STATUS\G;

上面两个参数都是Yes，则说明主从配置成功！

显式如下的情况，就是不正确的。可能错误的原因有：

1. 网络不通
2. 账户密码错误
3. 防火墙
4. mysql配置文件问题
5. 连接服务器时语法
6. 主服务器mysql权限

3.2.3.6、测试

主机新建库、新建表、insert记录，从机复制：

CREATE DATABASE atguigu_master_slave; 
CREATE TABLE mytbl(id INT,NAME VARCHAR(16)); 
INSERT INTO mytbl VALUES(1, 'zhang3'); 
INSERT INTO mytbl VALUES(2,@@hostname);

3.2.3.7、停止主从同步

• 停止主从同步命令：

stop slave;

• 如何重新配置主从
  如果停止从服务器复制功能，再使用需要重新配置主从。否则会报错如下：
  

重新配置主从，需要在从机上执行：

stop slave; 
reset master; #删除Master中所有的binglog文件，并将日志索引文件清空，重新开始所有新的日志文件(慎用)

3.2.3.8、后续

搭建主从复制：双主双从

3.3、同步数据一致性问题

主从同步的要求：

• 读库和写库的数据一致(最终一致)；
• 写数据必须写到写库；
• 读数据必须到读库(不一定)；

3.3.1、理解主从延迟问题

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

3.3.2、主从延迟问题原因

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

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

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

2、从库的压力大

3、大事务的执行

3.3.3、如何减少主从延迟

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

1. 降低多线程大事务并发的概率，优化业务逻辑
2. 优化SQL，避免慢SQL，减少批量操作，建议写脚本以update-sleep这样的形式完成。
3. 提高从库机器的配置，减少主库写binlog和从库读binlog的效率差。
4. 尽量采用短的链路，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网络延时。
5. 实时性要求的业务读强制走主库，从库只做灾备，备份。

3.3.4、如何解决一致性问题

如果操作的数据存储在同一个数据库中，那么对数据进行更新的时候，可以对记录加写锁，这样在读取的时候就不会发生数据不一致的情况。但这时从库的作用就是 备份 ，并没有起到 读写分离 ，分担主库读压力 的作用。

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

方法 1：异步复制
异步模式就是客户端提交COMMIT之后不需要等从库返回任何结果，而是直接将结果返回给客户端,这样做的好处是不会影响主库写的效率，但可能会存在主库宕机，而Binlog还没有同步到从库的情况，也就是此时的主库和从库数据不一致。这时候从从库中选择一个作为新主，那么新主则可能缺少原来主服务器中已提交的事务。所以，这种复制模式下的数据一致性是最弱的。|

方法 2：半同步复制
MySQL5.5版本之后开始支持半同步复制的方式。原理是在客户端提交COMMIT之后不直接将结果返回给客户端，而是等待至少有一个从库接收到了Binlog，并且写入到中继日志中，再返回给客户端。

这样做的好处就是提高了数据的一致性，当然相比于异步复制来说，至少多增加了一个网络连接的延迟，降低了主库写的效率。

在MySQL5.7版本中还增加了一个rpl_semi_sync_master_wait_for_slave_count参数，可以对应答的从库数量进行设置，默认为1，也就是说只要有1个从库进行了响应，就可以返回给客户端。如果将这个参数调大，可以提升数据一致性的强度，但也会增加主库等待从库响应的时间。

方法 3：组复制
异步复制和半同步复制都无法最终保证数据的一致性问题，半同步复制是通过判断从库响应的个数来决定是否返回给客户端，虽然数据一致性相比于异步复制有提升，但仍然无法满足对数据一致性要求高的场景。比如金融领域。MGR很好地弥补了这两种复制模式的不足。

组复制技术，简称MGR (MysQL Group Replication)。是MySQL在5.7.17版本中推出的一种新的数据复制技术，这种复制技术是基于Paxos协议的状态机复制。

MGR是如何工作的？

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

在一个复制组内有多个节点组成，它们各自维护了自己的数据副本，并且在一致性协议层实现了原子消息和全局有序消息，从而保证组内数据的一致性。

4、数据库备份与恢复

在任何数据库环境中，总会有不确定的意外情况发生，比如例外的停电、计算机系统中的各种软硬件故障、人为破坏、管理员误操作等是不可避免的，这些情况可能会导致数据的丢失、服务器瘫痪等严重的后果。存在多个服务器时，会出现主从服务器之间的数据同步问题。

为了有效防止数据丢失，并将损失降到最低，应定期对MySQL数据库服务器做备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复。主从服务器之间的数据同步问题可以通过复制功能实现。

4.1、物理备份与逻辑备份

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

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

4.2、mysqldump实现逻辑备份

mysqldump是MySQL提供的一个非常有用的数据库备份工具。

4.2.1、备份一个数据库

mysqldump命令执行时，可以将数据库备份成一个文本文件，该文件中实际上包含多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据。
查出需要备份的表的结构，在文本文件中生成一个CREATE语句。将表中的所有记录转换成一条INSERT语句。

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

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

4.2.2、备份全部数据库

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

4.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

4.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

4.2.5、备份单表的部分数据

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

4.2.6、排除某些表的备份

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

4.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

4.2.8、备份中包含存储过程、函数、事件

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

3、mysql命令恢复数据

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

其中，dbname参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。指定数据库名时，表示还原该数据库下的表。此时需要确保MySQL服务器中已经创建了该名的数据库。不指定数据库名时，表示还原文件中所有的数据库。此时sql文件中包含有CREATE DATABASE语句，不需要MysQL服务器中已存在这些数据库。

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、从单库备份中恢复单表

这个需求还是比较常见的。比如说我们知道哪个表误操作了，那么就可以用单表恢复的方式来恢复
举例:我们有atguigu整库的备份，但是由于class表误操作，需要单独恢复出这张表。

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)

3.5、物理备份:直接复制整个数据库

直接将MySQL中的数据库文件复制出来。这种方法最简单，速度也最快。MySQL的数据库目录位置不一定相同:。在Windows平台下，MySQL 8.0存放数据库的目录通常默认为“C:\ProgramData \MySQL \AySQL Server8.0\Data ”或者其他用户自定义目录;

• 在Linux平台下，数据库目录位置通常为/var/lib/mysql/;
• 在MAC OSX平台下，数据库目录位置通常为“/usr/local/mysql/data"

但为了保证备份的一致性。需要保证:

• 方式1:备份前，将服务器停止。
• 方式2:备份前，对相关表执行FLUSH TABLES WITH READ LOCK操作。解锁 UNLOCK TABLLES ,这样当复制数据库目录中的文件时，允许其他客户继续查询表。同时，FLUSH TABLES语句来确保开始备份前将所有激活的索引页写入硬盘。这种方式方便、快速，但不是最好的备份方法，因为实际情况可能不允许停止MySQL服务器或者锁住表，而且这种方法对InnoDB存储引擎的表不适用。对于MyISAM存储引擎的表，这样备份和还原很方便，但是还原时最好是相同版本的MySQL数据库，否则可能会存在文件类型不同的情况。

注意，物理备份完毕后，执行UNLOCK TABLES来结算其他客户对表的修改行为。

此外，还可以考虑使用相关工具实现备份。比如，MysQLhotcopy 工具。MySQLhotcopy是一个Perl脚本，它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表最快的途径，但它只能运行在数据库目录所在的机器上，并且只能备份MylSAM类型的表。多用于mysql5.5之前。

3.6、物理恢复:直接复制到数据库目录

步骤:

1. 演示删除备份的数据库中指定表的数据
2. 将备份的数据库数据拷贝到数据目录下，并重启MySQL服务器
3. 查询相关表的数据是否恢复。需要使用下面的chown 操作。

要求:

• 必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同。因为只有MysQL数据库主版本号相同时，才能保证这两个MySQL数据库文件类型是相同的。
• 这种方式对MyISAM类型的表比较有效，对于InnoDB类型的表则不可用。因为InnoDB表的表空间不能直接复制。
• 在Linux操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成mysql，命令如下:

chown -R mysql.mysql /var/ lib/mysql/dbname

其中，两个mysql分别表示组和用户;“-R"参数可以改变文件夹下的所有子文件的用户和组;“dbname"参数表示数据库目录。

提示
Linux操作系统下的权限设置非常严格。通常情况下，MySQL数据库只有root用户和mysql用户组下的mysql用户才可以访问，因此将数据库>目录复制到指定文件夹后，一定要使用chown命令将文件夹的用户组变为mysql，将用户变为mysql。

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='\"'

5、数据库迁移

5.1、概述

数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外， 验证迁移数据的完整性 和 退役原来旧的数据存储 ，也被认为是整个数据迁移过程的一部分。数据库迁移的原因是多样的，包括服务器或存储设备更换、维护或升级，应用程序迁移，网站集成，灾难恢复和数据中心迁移。

根据不同的需求可能要采取不同的迁移方案，但总体来讲，MySQL 数据迁移方案大致可以分为 物理迁移 和 逻辑迁移 两类。通常以尽可能 自动化 的方式执行，从而将人力资源从繁琐的任务中解放出来。

5.2、迁移方案

5.2.1、物理迁移

物理迁移适用于大数据量下的整体迁移。使用物理迁移方案的优点是比较快速，但需要停机迁移并且要求 MySQL 版本及配置必须和原服务器相同，也可能引起未知问题。物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种。不同服务器之间可以采用物理迁移，我们可以在新的服务器上安装好同版本的数据库软件，创建好相同目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝来数据文件及日志文件，配置好文件组权限，之后在新服务器这边使用 mysqld 命令启动数据库。

5.2.2、逻辑迁移

逻辑迁移适用范围更广，无论是 部分迁移 还是 全量迁移 ，都可以使用逻辑迁移。逻辑迁移中使用最多的就是通过 mysqldump 等备份工具。

5.2.3、迁移注意点

5.2.3.1、相同版本的数据库之间迁移注意点

指的是在主版本号相同的MySQL数据库之间进行数据库移动。

• 方式1： 因为迁移前后MySQL数据库的 主版本号相同 ，所以可以通过复制数据库目录来实现数据库迁移，但是物理迁移方式只适用于MyISAM引擎的表。对于InnoDB表，不能用直接复制文件的方式备份数据库。
• 方式2： 最常见和最安全的方式是使用 mysqldump命令 导出数据，然后在目标数据库服务器中使用MySQL命令导入。

举例：

#host1的机器中备份所有数据库,并将数据库迁移到名为host2的机器上 
mysqldump –h host1 –uroot –p –-all-databases| mysql –h host2 –uroot –p

在上述语句中，“|”符号表示管道，其作用是将mysqldump备份的文件给mysql命令；“–all-databases”表示要迁移所有的数据库。通过这种方式可以直接实现迁移。

5.2.3.2、不同版本的数据库之间迁移注意点

例如，原来很多服务器使用5.7版本的MySQL数据库，在8.0版本推出来以后，改进了5.7版本的很多缺陷，因此需要把数据库升级到8.0版本
旧版本与新版本的MySQL可能使用不同的默认字符集，例如有的旧版本中使用latin1作为默认字符集，而最新版本的MySQL默认字符集为utf8mb4。如果数据库中有中文数据，那么迁移过程中需要对 默认字符集 进行修改 ，不然可能无法正常显示数据。高版本的MySQL数据库通常都会 兼容低版本 ，因此可以从低版本的MySQL数据库迁移到高版本的MySQL数据库.

5.2.3.3、不同数据库之间迁移注意点

不同数据库之间迁移是指从其他类型的数据库迁移到MySQL数据库，或者从MySQL数据库迁移到其他类型的数据库。这种迁移没有普适的解决方法。

迁移之前，需要了解不同数据库的架构， 比较它们之间的差异 。不同数据库中定义相同类型的数据的 关 键字可能会不同 。例如，MySQL中日期字段分为DATE和TIME两种，而ORACLE日期字段只有DATE；SQL Server数据库中有ntext、Image等数据类型，MySQL数据库没有这些数据类型；MySQL支持的ENUM和SET类型，这些SQL Server数据库不支持。

另外，数据库厂商并没有完全按照SQL标准来设计数据库系统，导致不同的数据库系统的 SQL语句 有差别。例如，微软的SQL Server软件使用的是T-SQL语句，T-SQL中包含了非标准的SQL语句，不能和MySQL 的SQL语句兼容。

不同类型数据库之间的差异造成了互相 迁移的困难 ，这些差异其实是商业公司故意造成的技术壁垒。但是不同类型的数据库之间的迁移并 不是完全不可能 。例如，可以使用 MyODBC 实现MySQL和SQL Server之间的迁移。MySQL官方提供的工具 MySQL Migration Toolkit 也可以在不同数据之间进行数据迁移。MySQL迁移到Oracle时，需要使用mysqldump命令导出sql文件，然后， 手动更改 sql文件中的CREATE语句。

5.2.3.4、迁移小结

5.2、误删除数据了怎么办？

传统的高可用架构是不能预防误删数据的，因为主库的一个drop table命令，会通过binlog传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。为了找到解决误删数据的更高效的方法.
我们需要先对和MySQL相关的误删数据，做下分类:

1. 使用delete语句误删数据行;
2. 使用drop table或者truncate table语句误删数据表;
3. 使用drop database语句误删数据库;
4. 使用rm命令误删整个MySQL实例。

5.2.1、delete：误删行

1. 恢复数据比较安全的做法，是 恢复出一个备份 ，或者找一个从库作为 临时库 ，在这个临时库上执行这些操作，然后再将确认过的临时库的数据，恢复回主库。如果直接修改主库，可能导致对数据的 二次破坏 。
2. 当然，针对预防误删数据的问题，建议如下：

• 把 sql_safe_updates 参数设置为 on 。这样一来，如果我们忘记在delete或者update语句中写where条件，或者where条件里面没有包含索引字段的话，这条语句的执行就会报错。如果确定要把一个小表的数据全部删掉，在设置了sql_safe_updates=on情况下，可以
  在delete语句中加上where条件，比如where id>=0。
• 代码上线前，必须经过 SQL审计 。

5.2.2、truncate/drop ：误删库/表

方案：
这种情况下，要想恢复数据，就需要使用 全量备份 ，加 增量日志 的方式了。这个方案要求线上有定期的全量备份，并且实时备份binlog。
在这两个条件都具备的情况下，假如有人中午12点误删了一个库，恢复数据的流程如下：

1. 取最近一次 全量备份 ，假设这个库是一天一备，上次备份是当天 凌晨2点 ；
2. 用备份恢复出一个 临时库 ；
3. 从日志备份里面，取出凌晨2点之后的日志；
4. 把这些日志，除了误删除数据的语句外，全部应用到临时库。

5.2.3、延迟复制备库

如果有 非常核心 的业务，不允许太长的恢复时间，可以考虑搭建延迟复制的备库。一般的主备复制结构存在的问题是，如果主库上有个表被误删了，这个命令很快也会被发给所有从库，进而导致所有从库的数据表也都一起被误删了。

延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N秒的延迟 。比如你把N设置为3600，这就代表了如果主库上有数据被误删了，并且在1小时内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

5.2.4、预防误删库/表的方法

1. 账号分离 。这样做的目的是，避免写错命令。比如：只给业务开发同学DML权限，而不给truncate/drop权限。而如果业务开发人员有DDL需求的话，可以通过开发管理系统得到支持。即使是DBA团队成员，日常也都规定只使用 只读账号 ，必要的时候才使用有更新权限的账号。
2. 制定操作规范 。比如：在删除数据表之前，必须先 对表做改名 操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。
   改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted )，然后删除表的动作必须通过管理系统执行。并且，管理系统删除表的时候，只能删除固定后缀的表。

5.2.5、rm：误删MySQL实例

对于一个有高可用机制的MySQL集群来说，不用担心 rm删除数据 了。只是删掉了其中某一个节点的数据的话，HA系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。我们要做的就是在这个节点上把数据恢复回来，再接入整个集群。