菜单 学习猿地 - LMONKEY

VIP

开通学习猿地VIP

尊享10项VIP特权 持续新增

知识通关挑战

打卡带练！告别无效练习

接私单赚外块

VIP优先接，累计金额超百万

学习猿地私房课免费学

大厂实战课仅对VIP开放

你的一对一导师

每月可免费咨询大牛30次

开通VIP

领取更多软件工程师实用特权

 入驻

搜索

类型： 全部 | 视频教程 | 实战项目 | 项目源码 | 技术手册 | 课件 | 测试题 | 技术文章 | 软件下载 | 问答求助
水平： 全部 | 入门 | 普通 | 进阶

历史记录 清除记录

    你还没搜索过喔。

近期热搜

项目开发全程实录（电商EW_Shop）

8678 人 1年前

轻松学会Laravel-项目篇（商城API）

2022 人 1年前

Python数据分析2.0-金融

325 人 1年前

Python办公自动化2.0

241 人 1年前
猿创博文
猿创博文 / 其他 /
正文
365
0

mysql 备份恢复

原创
guhao022
其他
05/13 14:22
阅读数 19546
尊享10+项VIP特权
会员卡
开通学习猿地会员卡

备份概述

mysqldump ： 逻辑备份，占用空间少，备份会影响数据库内线程运行，

xtrabackup： 物理备份，占用空间大，几乎与原数据库文件大小一样，不影响数据库内线程运行（对数据库运行也是有影响的，只是影响不算大，不代表你可以在任何时候在线上运行该命令），备份时间短

增量备份：mysql binary log 的备份，可以还原到任何一个时间点

mydumper： 在mysql5.7环境上，对JSON的支持不友好，对虚拟列的支持也不友好，很可能备份时就出异常。因此，不推荐在mysql5.7上使用mydumper。曾经出过故障，但未专门做过此类验证。

 

 

开启开局general日志

mysql> show variables like '%general_log%';
+------------------+----------------------------------+
| Variable_name    | Value                            |
+------------------+----------------------------------+
| general_log      | OFF                              |
| general_log_file | /data/mysql_3306/data/ubuntu.log |
+------------------+----------------------------------+
2 rows in set (0.00 sec)

mysql> set global general_log=ON;
Query OK, 0 rows affected (0.03 sec)

mysql> set session general_log=ON;
ERROR 1229 (HY000): Variable 'general_log' is a GLOBAL variable and should be set with SET GLOBAL
mysql> 
mysql> 
mysql> 
mysql> show variables like '%general_log%';
+------------------+----------------------------------+
| Variable_name    | Value                            |
+------------------+----------------------------------+
| general_log      | ON                               |
| general_log_file | /data/mysql_3306/data/ubuntu.log |
+------------------+----------------------------------+
2 rows in set (0.01 sec)

 

备份用户权限

mysql> show grants for backup@'localhost';
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for backup@localhost                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, SHOW DATABASES, SUPER, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW, EVENT ON *.* TO 'backup'@'localhost' |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

reload 加载与刷新缓存

lock tables 锁表

select 读取数据

show databases 查看数据库列表

 

以下权限非备份所需要，加上会方便一些

show view 查看视图权限

event 对事件的增删改查的权限

replication slave  作为从库的复制权限，具有该权限才能以从库的角色从主库拉取数据
replication client  不可用于建立复制，"SHOW SLAVE STATUS" 、"SHOW MASTER STATUS"等命令。在5.6.6版本以后，也可以使用" SHOW BINARY LOGS " 。

super 超级权限

The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or 

mysqladmin kill to kill threads belonging to other accounts (you

can always kill your own threads), PURGE BINARY LOGS, configuration  changes using SET GLOBAL to modify global system variables, the

mysqladmin debug command, enabling or disabling logging, performing  updates even if the read_only system variable is enabled, starting and

stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables

you to connect (once) even if the connection limit controlled by the  max_connections system variable is reached.

 

权限放大了一些，方便了一些，但这样的授权配置只能走socket连接，socket本地连接，高效又安全

mysql -ubackup -pBack_123 -S /data/mysql_3306/tmp/mysql.sock

直接连接会报错，该报警并不是因为client中没有配置socket

root@ubuntu:~# mysql -ubackup -pBack_123 -P3358 -hlocalhost
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

 

备份的注意事项

mysqldump命令是与mysql的版本相匹配的，如果服务器上安装有多个版本的数据库，最好用相同版本的命令去导该版本下的实例

通常备份只关心表结构、表数据，实际上数据库的备份在可用于数据库恢复、迁移的，是指备份数据库中所有的内容，存储过程、视图、触发器、事件也是需要注意的；

select db,name from mysql.proc;
select db,name,body,created,starts,status from mysql.event;
select TABLE_SCHEMA,TABLE_NAME from information_schema.views;
select TRIGGER_SCHEMA,TRIGGER_NAME from information_schema.TRIGGERS;

 

mysqldump常用命令

 ./mysqldump -ubackup -pBack_123 -S /data/mysql_3306/tmp/mysql.sock  --set-gtid-purged=OFF --add-drop-database --add-drop-table -E --flush-logs --single-transaction --triggers --routines --events --master-data=2 --default-character-set=UTF8 --all-databases > /export/bak/all.sql

 

 为了在介绍原理的时候突出重点，文本的描述使用下文的命令进行备份

cd /opt/app/mysql-5.7.32-linux-glibc2.12-x86_64/bin

./mysqldump -ubackup -pBack_123 -S /data/mysql_3306/tmp/mysql.sock --single-transaction --master-data=2 vodb > /export/bak/vodb.sql

savepoint

上面备份命令对应的genaral日志有200多行，在这之前，先看一个功能savepoint。

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> savepoint aa;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select * from test1 where uid = (select max(uid) from test1);
+--------+--------+-------+--------+--------+---------------------+
| id     | uid    | tid   | tname  | tvalue | createtime          |
+--------+--------+-------+--------+--------+---------------------+
| 145501 | 145501 | 49999 | aaabbb | aa     | 2021-04-21 10:21:17 |
+--------+--------+-------+--------+--------+---------------------+
1 row in set (0.00 sec)

mysql> update test1 set tvalue ='bb' where uid = 145501;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test1 where uid = (select max(uid) from test1);
+--------+--------+-------+--------+--------+---------------------+
| id     | uid    | tid   | tname  | tvalue | createtime          |
+--------+--------+-------+--------+--------+---------------------+
| 145501 | 145501 | 49999 | aaabbb | bb     | 2021-04-21 10:21:17 |
+--------+--------+-------+--------+--------+---------------------+
1 row in set (0.00 sec)

mysql> rollback to savepoint aa;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1 where uid = (select max(uid) from test1);
+--------+--------+-------+--------+--------+---------------------+
| id     | uid    | tid   | tname  | tvalue | createtime          |
+--------+--------+-------+--------+--------+---------------------+
| 145501 | 145501 | 49999 | aaabbb | aa     | 2021-04-21 10:21:17 |
+--------+--------+-------+--------+--------+---------------------+
1 row in set (0.00 sec)

mysql> release savepoint aa;
Query OK, 0 rows affected (0.00 sec)

 

开启事务(trx1)，首先savepoint是一个事务内的操作；

savepoint aa;                    记录一个保存点 aa

rollback to savepoint aa;  在事务trx1内部，将数据回滚到aa的位置，在本事务内，数据回滚到了aa时的位置；

release savepoint aa;       释放保存点aa；

更多savepoint阅读: mysql 事务之使用savepoint部分回滚

 

 flush tables

flush tables : 强制关闭所有打开的表，并清一次缓存，如果有长事务占用表，被会阻塞该操作；实例级操作；

flush tables with read lock: 强制关闭所有打开的表，并阻塞所有修改操作；不阻塞读

更多flush tables阅读: mysql关于FLUSH TABLES和FLUSH TABLES WITH READ LOCK的理解

dml会阻塞flush tables实验举例如下

会话1，找一个数据量大一些的表，让锁表的时候长一些，全球观察

 

mysql> update test1 set tvalue = concat('value ',uid);
Query OK, 695393 rows affected (5.08 sec)
Rows matched: 695393  Changed: 695393  Warnings: 0

 

会话2，flush tables正常几乎不耗时，执行上面的update后，立即在另外一个会话中执行，耗时3.05秒

 

 至此，足以说明DML操作必定是阻塞flush tables的，同样的，如果flush tables能执行，必定是全库状态一致的时刻，没有正在执行DML/DDL；

如果flush tables;与flush tables with read lock合起来，连续执行，那么意思就是，等到某一时刻数据库中没有DML/DDL执行的时候，加全局读锁，整个数据库不可再修改；

 

 

RR级别的事务

RR级别,可重复读，针对修改与与删除操作保证是100%，但针对insert操作，也就是幻读问题，有争议，看下面的例子

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> savepoint aa;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1 where uid < (select max(uid) from test1) order by uid desc limit 5;
+--------+--------+-------+--------+----------------------------+---------------------+
| id     | uid    | tid   | tname  | tvalue                     | createtime          |
+--------+--------+-------+--------+----------------------------+---------------------+
| 145399 | 145399 | 49897 | aaabbb | 有张有驰有分寸49897        | 2021-04-21 10:21:17 |
| 145393 | 145393 | 49891 | aaaa   | aaaa                       | 2021-04-21 15:45:39 |
| 145392 | 145392 | 49890 | aaabbb | 有张有驰有分寸49890        | 2021-04-21 10:21:17 |
| 145391 | 145391 | 49889 | aaabbb | 有张有驰有分寸49889        | 2021-04-21 10:21:17 |
| 145390 | 145390 | 49888 | aaabbb | 有张有驰有分寸49888        | 2021-04-21 10:21:17 |
+--------+--------+-------+--------+----------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> select * from test1 where uid < (select max(uid) from test1) order by uid desc limit 5;
+--------+--------+-------+--------+----------------------------+---------------------+
| id     | uid    | tid   | tname  | tvalue                     | createtime          |
+--------+--------+-------+--------+----------------------------+---------------------+
| 145399 | 145399 | 49897 | aaabbb | 有张有驰有分寸49897        | 2021-04-21 10:21:17 |
| 145393 | 145393 | 49891 | aaaa   | aaaa                       | 2021-04-21 15:45:39 |
| 145392 | 145392 | 49890 | aaabbb | 有张有驰有分寸49890        | 2021-04-21 10:21:17 |
| 145391 | 145391 | 49889 | aaabbb | 有张有驰有分寸49889        | 2021-04-21 10:21:17 |
| 145390 | 145390 | 49888 | aaabbb | 有张有驰有分寸49888        | 2021-04-21 10:21:17 |
+--------+--------+-------+--------+----------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> update test1 set tvalue = 'bbbb' where uid between 145393 and 145399;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from test1 where uid < (select max(uid) from test1) order by uid desc limit 5;
+--------+--------+-------+--------+----------------------------+---------------------+
| id     | uid    | tid   | tname  | tvalue                     | createtime          |
+--------+--------+-------+--------+----------------------------+---------------------+
| 145399 | 145399 | 49897 | aaabbb | bbbb                       | 2021-04-21 10:21:17 |
| 145394 | 145394 | 49891 | aaaa   | bbbb                       | 2021-04-21 15:50:28 |
| 145393 | 145393 | 49891 | aaaa   | bbbb                       | 2021-04-21 15:45:39 |
| 145392 | 145392 | 49890 | aaabbb | 有张有驰有分寸49890        | 2021-04-21 10:21:17 |
| 145391 | 145391 | 49889 | aaabbb | 有张有驰有分寸49889        | 2021-04-21 10:21:17 |
+--------+--------+-------+--------+----------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> rollback to savepoint aa;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1 where uid < (select max(uid) from test1) order by uid desc limit 5;
+--------+--------+-------+--------+----------------------------+---------------------+
| id     | uid    | tid   | tname  | tvalue                     | createtime          |
+--------+--------+-------+--------+----------------------------+---------------------+
| 145399 | 145399 | 49897 | aaabbb | 有张有驰有分寸49897        | 2021-04-21 10:21:17 |
| 145394 | 145394 | 49891 | aaaa   | aaaa                       | 2021-04-21 15:50:28 |
| 145393 | 145393 | 49891 | aaaa   | aaaa                       | 2021-04-21 15:45:39 |
| 145392 | 145392 | 49890 | aaabbb | 有张有驰有分寸49890        | 2021-04-21 10:21:17 |
| 145391 | 145391 | 49889 | aaabbb | 有张有驰有分寸49889        | 2021-04-21 10:21:17 |
+--------+--------+-------+--------+----------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> release savepoint aa;
Query OK, 0 rows affected (0.00 sec)

 

 这是因为对于select查询是的快照是第一次select时刻的快照，是读快照，这个快照并不阻塞其他事务写，所以在其他会话中能够insert数据；

而update操作，改变了这一点，update操作本身是写操作，会阻塞其他事务会话的写，从update开始那一时刻，其他事务全部被阻塞。

下面是另外一个事务会话，多出的145394记录就是这个会话插入的，在原会话update之后，该会话的插入被阻塞。

 

 也就是说，虽然RR级别能否解决幻读的问题有争议，但只要不在同一事务中进行修改类操作，读一致性是可以保证的；

在这个过程中加入savepoint是为了说明，savepoint无法回滚其他会话的事务，所以savepoint对幻读问题的解决没有帮忙，只可回滚本会话内的事务

 

show master status

mysql> show master status\G;
*************************** 1. row ***************************
             File: ON.000110
         Position: 295183070
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 488a7baa-54c8-11eb-823e-fa163e3274d2:7-128,
4aeacbf9-4786-11eb-b9f0-000c29148323:1-322362
1 row in set (0.00 sec)

一个事务一个位点，有事务变化，位点就会增加；虽然RR级别保证了可重复读，但绝对不包含这个的，只要有事务产生，绝对位点增加，RR的可重复读是表中数据的可重复读。

这意味着，在一个RR级别中的事务中，也是可以看到这个值是不断增加的

 


character_set_results

The character set used for returning query results to the client. This includes result data such as column values, result metadata such as column names, and error messages.

 

 

 

mysqldump备份原理

 回顾完mysql相关知识点后，下面开始看dump的日志，不记录所有日志，只摘录重点部分

开启备份RR事务

Query    FLUSH /*!40101 LOCAL */ TABLES                   关闭表，清缓存，
Query    FLUSH TABLES WITH READ LOCK                      关闭表，清缓存，实例级读锁，阻塞任何对库的修改
Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ         会话级RR隔离级别，因为原来的隔离级别很可能是RC，特定情况下可100%达到可重复、一致性读的效果
Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */          开启事务

 

读取事务开始时刻的位点

Query    SHOW VARIABLES LIKE 'gtid\_mode'
Query    SELECT @@GLOBAL.GTID_EXECUTED
Query    SHOW MASTER STATUS
Query    UNLOCK TABLES

 锁表的过程中读取了master status，这是事务的位点，备份的是一个逻辑快照，就是这一时刻的数据

 

记录一个savepoint sp

Query    SAVEPOINT sp

 

读取表数据

Query    show tables
Query    show table status like 'test\_utf8'
Query    SET SQL_QUOTE_SHOW_CREATE=1
Query    SET SESSION character_set_results = 'binary'
Query    show create table `test_utf8`
Query    SET SESSION character_set_results = 'utf8'
Query    show fields from `test_utf8`
Query    show fields from `test_utf8`
Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_utf8`
Query    SET SESSION character_set_results = 'binary'
Query    use `vodb`
Query    select @@collation_database
Query    SHOW TRIGGERS LIKE 'test\_utf8'
Query    SET SESSION character_set_results = 'utf8'
Query    ROLLBACK TO SAVEPOINT sp

Query    show table status like 'test\_utf8mb4'
Query    SET SQL_QUOTE_SHOW_CREATE=1
Query    SET SESSION character_set_results = 'binary'
Query    show create table `test_utf8mb4`
Query    SET SESSION character_set_results = 'utf8'
Query    show fields from `test_utf8mb4`
Query    show fields from `test_utf8mb4`
Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_utf8mb4`
Query    SET SESSION character_set_results = 'binary'
Query    use `vodb`
Query    select @@collation_database
Query    SHOW TRIGGERS LIKE 'test\_utf8mb4'
Query    SET SESSION character_set_results = 'utf8'
Query    ROLLBACK TO SAVEPOINT sp

 

show tables 得到所有的表，以二进制方式读取表结构，在utf8读取字字段，以NO_SQL_CACHE方式读取表数据

字符集特别说明，这里是utf8，是库的，不是表的；这里面两个表，一个是utf8，一个是utf8mb4，但备份导出的时候指定的字符集皆为utf8，未测试这种情况是否会有问题。

mysql> show create database vodb;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| vodb     | CREATE DATABASE `vodb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table test_utf8mb4;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                            |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_utf8mb4 | CREATE TABLE `test_utf8mb4` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table test_utf8;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_utf8 | CREATE TABLE `test_utf8` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(300) DEFAULT NULL,
  `c20` varchar(33) DEFAULT NULL,
  `c21` varchar(33) DEFAULT NULL,
  `c22` varchar(33) DEFAULT NULL,
  `c23` varchar(33) DEFAULT NULL,
  `c24` varchar(33) DEFAULT NULL,
  `c25` varchar(33) DEFAULT NULL,
  `c26` varchar(33) DEFAULT NULL,
  `c27` varchar(33) DEFAULT NULL,
  `c28` varchar(33) DEFAULT NULL,
  `c31` varchar(33) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

所有表都以相同的事务开始

每次读完表，都会执行一次rollback to savepoint aa; 让事务回到最初的那个保存点，这意味每一张表select的事务是一致的。

 

释放savepoint，退出备份

Query    RELEASE SAVEPOINT sp
Quit

 

如何保证逻辑备份数据一致性重点强制

2021-04-21T11:38:17.242658+08:00       15 Query    FLUSH TABLES WITH READ LOCK
2021-04-21T11:38:17.242709+08:00       15 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2021-04-21T11:38:17.242736+08:00       15 Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2021-04-21T11:38:17.242824+08:00       15 Query    SHOW VARIABLES LIKE 'gtid\_mode'
2021-04-21T11:38:17.243721+08:00       15 Query    SELECT @@GLOBAL.GTID_EXECUTED
2021-04-21T11:38:17.243785+08:00       15 Query    SHOW MASTER STATUS
2021-04-21T11:38:17.243820+08:00       15 Query    UNLOCK TABLES
2021-04-21T11:38:17.243909+08:00       15 Query    SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('vodb'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2021-04-21T11:38:17.256874+08:00       15 Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('vodb')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2021-04-21T11:38:17.260724+08:00       15 Query    SHOW VARIABLES LIKE 'ndbinfo\_version'
2021-04-21T11:38:17.261649+08:00       15 Init DB    vodb
2021-04-21T11:38:17.261818+08:00       15 Query    SAVEPOINT sp
2021-04-21T11:38:17.261892+08:00       15 Query    show tables

在unlock talbes 与 savepoint aa之间，还有 261818 - 243820 = 17998，即0.17998秒的时差，这点时间对计算机来说，是不短的时间

read lock是加在strat transaction之前的，位点静止不变，之后的show master status的位点，必定就是start transaction时的位点，因为没有变化，所以是同一位点；

尽管在savepoint的时候，已经过去了0.17998秒，但由于RR级别下的可重复读特性，即使有其他事务导致位点变化，但读取的数据依然是start transaction时刻的数据。

至此，个人认为，文本已经非常清晰地阐述了mysqldump如何备份、如何保证数据一致性的这个问题了。

 

未完 ... 下面开始xtrabackup如何备份数据 ... 

 

365
0
0

© 著作权归作者所有

举报

发表评论

0/200
365 点赞
0 评论
收藏
友情链接：

实猿科技 学习猿地-LMONKEY © 辽ICP备19013136号

学习猿地
程序员学习的地方

扫码关注