mysqldump和xtrabackup备份原理实现说明

MySQL数据库备份分为逻辑备份和物理备份两大类，犹豫到底用那种备份方式的时候先了解下它们的差异：

逻辑备份的特点是：直接生成SQL语句，在恢复的时候执行备份的SQL语句实现数据库数据的重现。

物理备份的特点是：拷贝相关数据文件。

这二种备份差异  ：逻辑备份其备份、还原慢，但备份文件占用的空间小；物理备份其备份还原快，备份文件占用空间大。

到底选择那种备份方式，具体根据自己的实际情况，如需要的是热备还是冷备？数据量大不大？磁盘空间够不够等因素决定。

逻辑备份工具主要有：mysqldump、mysqlpump、mydumper，物理备份工具主要有：xtrabackup。

现在使用最多的备份就是mysqldump、xtrabackup（都支持支持热备），本文就mysqldump（mysqlpump）和xtrabackup的原理进行下大致的说明。

逻辑备份：mysqldump，第三方备份工具mydumper的备份原理看MySQL备份mydumper的原理

1）参数说明，具体的参数可以用mysqldump --help查看。

在说明mysqldump之前先了解下它的相关参数，可以通过mysqldump --help进行查看，也可以通过mysqldump、mysqlpump备份工具说明和mysqldump的流程进行了解。这里再重申一下几个比较重要的参数。

①：--single-transaction

通过将导出操作封装在一个事务(Repeatable Read)内来使得导出的数据是一个一致性快照。只有当表使用支持MVCC的存储引擎（目前只有InnoDB）时才可以工作；其他引擎不能保证导出是一致的。当导出开启了–single-transaction选项时，要确保导出文件有效（正确的表数据和二进制日志位置），就要保证没有其他连接会执行如下语句：ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE，这会导致一致性快照失效。这个选项开启后会自动关闭lock-tables。并且在mysql5.7.11之前，--default-parallelism大于1的时候和此参也互斥，必须使用--default-parallelism=0。5.7.11之后解决了--single-transaction和--default-parallelism的互斥问题。

②：--master-data

这个选项可以把binlog的位置和文件名添加到输出中，如果等于1，将会打印成一个CHANGE MASTER命令；如果等于2，会加上注释前缀。并且这个选项会自动打开–lock-all-tables，除非同时设置了–single-transaction（这种情况下，全局读锁只会在开始dump的时候加上一小段时间，不要忘了阅读–single-transaction的部分）。在任何情况下，所有日志中的操作都会发生在导出的准确时刻。这个选项会自动关闭–lock-tables。打开该参数需要有reload权限，并且服务器开启binlog。

③：--lock-all-tables ，-x

锁定所有库中所有的表。这是通过在整个dump的过程中持有全局读锁来实现的。会自动关闭–single-transaction 和 –lock-tables。

④：--lock-tables，-l

备份某个库就锁该库的所有表，用READ LOCAL来锁表。MyISAM允许并发写入，因为锁表只针对指定的数据库，不能保证物理上的一致性，不同库的表备份完成时会有不同的状态。用–skip-lock-tables来关闭。

⑤：--flush-logs，-F

在开始导出前刷新服务器的日志文件。注意，如果你一次性导出很多数据库（使用--databases= 或--all-databases 选项），导出每个库时都会触发日志刷新。例外是当使用了--lock-all-tables、--master-data或--single-transaction时：日志只会被刷新一次，那个时候所有表都会被锁住。所以如果你希望你的导出和日志刷新发生在同一个确定的时刻，你需要使用--lock-all-tables、--master-data和--single-transaction配合 –flush-logs。

⑥：--opt

该参数默认开启，表示快递启动--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset选项，通过 --skip-opt 关闭。

2）执行说明

①：逻辑备份就是导出SQL形式的文件，其的具体实现步骤可以直接打开genaral_log。

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

②：备份需要保证数据库的一致性，即在某一时刻，整个数据库的状态是一致的，这样可以通过备份进行恢复成为另一个从库。数据库目前使用最多的存储引擎是InnoDB，也有可能部分是MyISAM，建议把MyISAM存储引起改成InnoDB。现在重点说明关于InnoDB的备份。

MyISAM表的备份选项：上面提过因为mysqldump默认开启--opt选项，而--opt里包含--lock-tables的选项，这个选项不能保证在多个数据库下数据备份的一致性，所以要么--skip-opt，再把需要的选项添加进去，要么就再使用--lock-all-tables的选项（开启之后会关闭--lock-tables的选项），要是在从库备份则只需要添加--master-data选项（开启之后自动打开--lock-all-tables选项）即可。

备份myisam表的命令：

mysqldump -uroot -p123 --default-character-set=utf8 --master-data=1 -R -E --triggers -B dba_test dba_test2 > /home/dxy/dba_test.sql

因为开启了--lock-all-tables选项（--master-data），保证一致性读和数据的一致性。在备份开始时就会执行FLUSH TABLES WITH READ LOCK命令，这个命令是server层面的锁，这样后面任何存储引擎执行DML、DDL语句都会 Waiting for global read lock状态，这样就保证了从备份点之后数据的一致性。

InnoDB表的备份选项：和上面介绍MyISAM表的备份选项一样，在此基础上增加了--single-transaction的选项，这个选项保证了通过将导出操作封装在一个事务(Repeatable Read)内来使得导出的数据是一个一致性快照。只有当表使用支持MVCC的存储引擎（目前只有InnoDB）时才可以工作，其他引擎不能保证导出是一致的。这个选项开启后会自动关闭--lock-tables选项，而--master-data选项自动打开–lock-all-tables选项，在设置了--single-transaction这种情况下，全局读锁只会在开始dump的时候加上一小段时间（5.7之前），5.7之后不需要加锁了。