数据库的备份与恢复

前言：mysql服务器上一般都有默认的四个数据库（mysql/sys/performance_schema/information_schema）,其中mysql库里面的所有表的存储引擎ENGINE都是MyISAM。performance_schema库的所有表的存储引擎ENGINE都是performance_schema，information_schema库的表有的是Innodb存储引擎，有的是memory存储引擎。而我们新建的库一般都是默认的Innodb存储引擎的表。备份与存储引擎息息相关。

一、热备。

1、XtraBackup热备

二、温备。

0、mysqldump执行后的查询日志如下：

1、mysqldump的逻辑备份温备。备份的参数--single-transaction表示在备份开始之前执行start transaction语句，通过此方法获得备份数据的一致性，这对innodb存储引擎有效，但是不能在此期间进行DDL语句执行，因为一致性读并不能隔离DDL语句。备份的参数--master-data=1/2，该参数会自动忽略--lock-tables，如果没有使用--single-transaction，则会自动使用备份参数--lock-all-tables，因此只要使用了备份参数--master-data，那么备份参数--single-transaction和--lock-all-tables有且仅有其中一个。另外mysqldump不能导出视图，因此，如果数据里面使用了视图，那么还需要其他方法导出视图的frm文件。

2、通过将导出操作封装在一个事务(Repeatable Read)内来使得导出的数据是一个一致性快照。常用的工具是MySQL自带的mysqldump。

3、加了--single-transaction就能保证innodb的数据是完全一致的，而myisam引擎无法保证，必须加--lock-all-tables。

4、--master-data=2表示在dump过程中记录主库的binlog和pos点，并在dump文件中注释掉这一行；--master-data=1表示在dump过程中记录主库的binlog和pos点，并在dump文件中不注释掉这一行，即恢复时会执行；--dump-slave=2表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中注释掉这一行；--dump-slave=1表示在dump过程中，在从库dump，mysqldump进程也要在从库执行，记录当时主库的binlog和pos点，并在dump文件中不注释掉这一行；注意:在从库上执行备份时，即--dump-slave=2，这时整个dump过程都是stop io_thread的状态。

5、如何测试mysqldump命令的执行过程？开启mysql的查询日志，即在mysql的配置文件中添加general-log=1,然后执行mysqldump命令，然后查看查询日志文件。

6、flush tables只是关闭所有打开的表，并不获取锁。如果没有长事务，命令会很快执行完成（因为长事务会导致表无法关闭。但长时间未提交的事务不会导致表无法关闭）。长时间未提交的事务也不会阻塞flush tables。flush tables也不会阻塞后续其他客户端的事务操作；

7、flush tables with read lock才会获取读锁。长时间未提交的事务不会阻塞FTWRL，但是FTWRL会阻塞活动的事务执行后续的更改和提交等操作（比如长时间未提交的事务提交，或者继续update），以及阻塞后续的事务开始执行，数据库处于stall状态；

8、SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ。设置当前会话的事务隔离等级为RR，RR可避免不可重复读。

9、START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */。开启一个事务。START TRANSACTION 与 START TRANSACTION WITH CONSISTENT SNAPSHOT区别是什么？如果是start transaction，事务A开启，然后事务B插入多条记录records并提交事务B，然后事务A可以查询到事务B刚刚提交的那些记录；

如果是start transaction with constent snapshot，事务A开启，然后事务B插入多条记录records并提交事务B，然后事务A不能查询到事务B刚刚提交的那些记录，只能查询到执行start transaction with constent snapshot这条语句之前的所有事务提交后的结果；

10、SHOW MASTER STATUS。记录备份开始时的二进制日志文件名和开始备份时二进制日志文件的位置。

1 mysql -hXXX -e 'show databases;' | grep -Ev 'Database|information_schema|mysql|performance_schema|sys' | xargs mysqldump56 -hXXX --single-transaction --master-data=2  --databases > XXX.sql
#其中XXX代表主机名称

10、UNLOCK TABLES。释放锁。为啥备份innodb表之前，就已经将锁释放掉了，这实际上是利用了innodb引擎的MVCC机制，开启快照读后，就能获取那个时间的一致的数据，无论需要备份多长时间，直到整个事务结束(commit)为止。

11、SHOW CREATE DATABASE IF NOT EXISTS `sdxs`。生成建库语句。

13、SAVEPOINT sp。mysqldump利用保存点机制，每备份完一个表就将一个表上的MDL锁释放，避免对一张表锁更长的时间。

13、show tables。获取所有的表。

13、show table status like 't1'。获取表的记录行数。

14、show create table `停车场`。生成建表语句。

15、show fields from `t1`。获取表的字段信息。

11、SELECT /*!40001 SQL_NO_CACHE */ * FROM `停车场`。这是备份的核心语句。该语句会查询到表`停车场`的所有数据，在备份文件中会生成相应的insert语句。其中SQL_NO_CACHE的作用是查询的结果并不会缓存到查询缓存中。这样所有数据都获取到。mysqldump的本质是通过select * from tab来获取表的数据的。