mysql 备份恢复
备份概述
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如何备份数据 ...
