1 对于备份

1.1 为什么要备份

• 劫难复原 ，数据库在运行过程中，终会遇到各种各样的问题: 硬件故障、Bug 导致数据损坏、因为服务器宕机或者其余起因造成的数据库不可用。除此以外还有人为操作：DELETE 语句忘加条件、ALTER TABLE 执行错表、DROP TABLE 执行错表、黑客攻击，即便这些问题你都还没遇到，然而依据墨菲定律，总会有遇上的时候。
• 回滚 ，因为某种 Bug 或零碎被黑造成大量的损失，这个时候就须要回滚到某个状态。常见的有区块链交易所被黑而后回滚，游戏破绽被利用而后整体回滚。
• 审计 ，有时候有这样的需要：须要晓得某一个工夫点的数据是怎么样的，可能是年末审计，也可能是因为官司。
• 测试 ，一个根本的测试需要是，定时拉取线上数据到测试环境，如果有备份，就能够十分不便地拉取数据。

1.2 有哪些备份形式

1.2.1 逻辑备份

逻辑备份是最常见的形式，在数据量比拟少的时候很罕用。

逻辑备份的劣势：

• 备份复原比较简单，例如 mysqldump 就是 MySQL 自带的备份工作，无需额定装置。复原的时候能够间接应用 mysql 命令进行复原。
• 能够近程备份和复原，也就是说，能够在其余机器执行备份命令。
• 备份进去的数据十分直观，备份进去后，能够应用 sed grep 等工具进行数据提取或者批改。
• 与存储引擎无关，因为备份文件是间接从 MySQL 外面提取进去的数据，所以在直观上，备份数据数据不对引擎做辨别，能够很不便地从 MyISAM 引擎改到 InnoDB 引擎。
• 防止受到文件损坏的影响，如果间接复制原始文件，可能会受到某个文件损坏的影响而失去一个损坏的备份。应用逻辑备份，只有 MySQL 还能执行 SELECT 语句，就能够失去一份可以信赖的逻辑备份，在文件损坏的时候很有用。

逻辑备份毛病：

• 因为必须应用 MySQL 服务进行数据操作，所以备份的时候会占用更多 CPU，且备份工夫可能会更长。
• 逻辑备份在某些场景下比数据库文件更大，文本存储的数据不总是比存储引擎更高效。当然，应用压缩的话会失去一个更小的备份，然而要占用 CPU 资源。（如果索引较多，逻辑备份会比物理备份小。）
• 复原工夫更长，应用逻辑备份的数据恢复，须要占用更多资源去进行锁调配、索引构建、抵触查看、日志刷新。

逻辑备份罕用办法:

• mysqldump 是 MySQL 自带的备份工具，通用性强，十分常见。应用的应用通常要加上一些参数，前面持续介绍。
• select into outfile，以符号宰割数据创立逻辑备份，对于要导入到 CSV 等表格会比拟实用。
• mydumper，容许应用多线程进行备份，备份文件会进行表构造和数据拆散，在复原某些表或数据的时候会十分无效。

1.2.2 物理备份

物理备份在数据量较大的时候十分常见。

物理备份的劣势：

• 备份速度快，因为物理备份是基于复制进行备份，象征者复制有多快，备份就能有多快。
• 复原速度快，只须要把文件复制到数据库目录就能够实现复原，不须要查看锁、构建索引。
• 复原简略，对于 MySIAM 引擎的表，不须要停库，只须要简略地复制进数据目录就能够。对于 InnoDB，如果是每个表一个表空间，也能够不停库操作，应用卸载加载表空间的形式便可导入（不太平安）。

物理备份毛病：

• 没有官网物理热备份工具的反对。没有官网工具的反对，意味着出问题的概率较大，应用的时候就要审慎了
• InnoDB 的原始文件通常比逻辑备份要大。InnoDB 表空间往往蕴含很多未被应用的空间，InnoDB 表在删除数据后不会开释空间，所以即便数据量不大，文件有可能很大。除此以外，文件中除了数据还蕴含了索引、日志等信息。
• 物理备份不总能够跨平台跨版本。MySQL 文件和操作系统、MySQL 版本非亲非故，如果环境与原来不统一，很有可能会呈现问题。

物理备份罕用办法:

• xtrabackup 是最罕用的物理备份工具，由 percona 开源，可能实现对 InnoDB 存储引擎和 XtraDB 存储引擎非阻塞地备份（对于 MyISAM 还是要加锁），失去一份一致性备份。
• 间接复制文件 / 文件系统快照 ，这种形式对于 MySIAM 引擎是十分高效的，只须要执行 FLUSH TABLE WITH READ LOCK 就能够复制失去一份备份文件。然而对于 InnoDB 引擎就比拟艰难，因为 InnoDB 引擎应用了大量的异步技术，即便执行了 FLUSH TABLE WITH READ LOCK，它还是会持续合并日志、缓存数据。所以要用这种办法备份 InnoDB，须要确保 checkpoint 曾经最新。

1.2 为什么要备份 binlog

如果有 DBA 通知你，这个数据库可能复原到两个个月内任何状态，这阐明了，这个数据库的 binlog 日志至多保留了两个月。备份 binlog 的益处:

• 能够实现基于任意工夫点的复原
• 能够用于误操作数据闪回
• 能够用于审计

当你要进行数据恢复的时候，就会十分庆幸有做 binlog 备份。当然，应用 binlog 复原数据的前提是 binlog 格局要设为 row，不要放心空间问题，以后最不缺的资源就是硬盘空间。对于 binlog，咱们举荐的配置是

# 记录每一行数据的变动
binlog_format = row
# 备库在重做数据的时候，记录一条 binlog
log_slave_updates = 1

1.3 复制和备份

主从复制等于多了一个数据正本，然而复制并不等于备份，也不能代替备份。假如在主库执行了 drop table 操作，会立即同步到备库，并执行雷同的操作，没有方法在出现意外的时候应用备库进行数据恢复。

提早复制也不能代替备份，然而能放慢复原的速度，是一种十分有用的策略。

在理论应用中，为了不影响主库的应用，咱们往往会在备库进行备份，同时记录同步点，以不便进行新备库搭建。在备库备份须要留神的是，主从复制并不能保障主备间数据是统一的。实际上，基于复制的 MySQL 集群并不能保障集群外部一致性，以后也没有十分好的方法，罕用的是应用 pt-table-checksum 进行一致性查看。

2. 全量备份

全量备份介绍最罕用的逻辑备份工具 mysqldump 和物理备份工具 xtrabackup。如果对 mysqldump 不太称心 能够应用 mydumper 来代替 mysqldump。

2.1 mysqldump

mysqldump 是用得最多的工具，然而要用好的话，须要减少一些额定的参数。mysqldump 有很多可用参数，这里不开展，倡议间接拜访官网 mysqldump。应用 mysqldump 某些参数须要 select,reload,lock tables 权限。

2.1.1 常见例子

2.1.1.1 InnoDB 全库备份

mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8 -h<host> -u<user> -p<password> -A > backup.sql

• --opt 如果有这个参数示意同时激活了 mysqldump 命令的 quick，add-drop-table，add-locks，extended-insert，lock-tables 参数，它能够给出很快的转储操作并产生一个能够很快装入 MySQL 服务器的转储文件。当备份大表时，这个参数能够避免占用过多内存
• --single-transaction 设置事务的隔离级别为可反复读，而后备份的时候开启事务，这样能保障在一个事务中所有雷同的查问读取到同样的数据。留神，这个参数只对反对事务的引擎无效，如果有 MyISAM 的数据表，并不能保证数据一致性
• -A 导出全副数据库
• –-default-character-set=charset 指定导出数据时采纳何种字符集
• --master-data=2 示意在备份过程中记录主库的 binlog 和 pos 点，并在 dump 文件中正文掉这一行，在应用备份文件做新备库时会用到

2.1.1.2 MyISAM 全库备份

mysqldump --opt --lock-all-tables --master-data=2 --default-character-set=utf8 -h<host> -u<user> -p<password> -A > backup.sql

• --lock-all-tables 锁表备份。因为 MyISAM 不能提供一致性读，如果要失去一份一致性备份，只能进行全表锁定。

2.1.1.3 备份带上压缩

mysqldump -h<host> -u<user> -p<password> -A | gzip >> backup.sql.gz

2.1.1.4 备份多个库

mysqldump -h<host> -u<user> -p<password> --databases <dbname1> <dbname2> > backup.sql

2.1.2 复原

复原形式比较简单，间接执行 sql 语句就能够了

mysql -h<host> -u<user> -p<password> < backup.sql

2.1.3 mysqldump 执行流程

关上 general_log 能够查看 mysqldump 的执行流程，这里以 --single-transaction --opt -A 参数为例

FLUSH /*!40101 LOCAL */ TABLES
FLUSH TABLES WITH READ LOCK
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION
SHOW VARIABLES LIKE 'gtid\_mode'
SHOW MASTER STATUS
UNLOCK TABLES
...
SHOW CREATE DATABASE IF NOT EXISTS `employees`
SAVEPOINT sp
...
SELECT /*!40001 SQL_NO_CACHE */ * FROM `departments`
....

2.2 xtrabackup

2.2.1 装置形式

更多装置形式参考官网 xtrabackup

这里咱们应用 rpm 装置的形式

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm
yum update percona-release
# qpress 用作压缩解压
yum install percona-xtrabackup-24 qpress

2.2.2 应用办法

2.2.2.1 减少备份账号并受权

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'backup';
GRANT PROCESS,RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

2.2.2.2 全备

innobackupex --defaults-file=/etc/my.cnf --user=<user> --password=<pwd> < 要备份到哪个目录 > --no-timestamp --compress --compress-threads=4 --parallel=4

• --no-timestamp 不应用以后工夫建设文件夹。默认状况下会在备份目录以以后工夫创立文件夹
• --compress 压缩
• --compress-threads=N 压缩线程
• --parallel=N 备份线程

2.2.2.3 复原

# 步骤一：解压
innobackupex --decompress < 备份文件所在目录 > --parallel=4

# 步骤二：利用日志
innobackupex --apply-log < 备份文件所在目录 > --parallel=4

# 步骤三：复制备份文件到数据目录
innobackupex --datadir=<MySQL 数据目录 > --copy-back < 备份文件所在目录 > --parallel=4

3. 增量备份

当数据了变得宏大时，一个常见策略就是做定期的增量备份。例如：周一做了一次全量备份，而后周二到日做增量备份。

增量备份只蕴含变动的数据集，个别状况不会比原始数据量大，所以能够缩小服务器的开销、备份工夫、备份空间。

当然，应用增量备份也会有危险，增量备份每一次迭代都是基于上一次的备份实现，意味着只有其中一份备份呈现问题，那么就有可能导致所有备份不可用。

上面介绍一些增量备份办法：

3.1 应用 xtrabackup 做增量备份

xtrabackup 容许进行增量备份，命令如下:

innobackupex --defaults-file=/etc/my.cnf --user=<user> --password=<pwd> --no-timestamp --compress --incremental --incremental-basedir=< 全量备份的目录 > < 要增量备份到什么目录 >

复原：

# 步骤一：对全备解压
innobackupex --decompress < 全量备份文件所在目录 >

# 步骤二：对全备利用日志
innobackupex --apply-log --redo-only < 全量备份文件所在目录 >

# 步骤三：对增量备份进行解压
innobackupex --decompress < 增量文件所在目录 >

# 步骤四：合并增量数据
innobackupex --apply-log --redo-only --incremental < 全量备份文件所在目录 > --incremental-dir=< 增量文件所在目录 >

# 步骤五：对合并后的数据利用日志
innobackupex --apply-log < 全量备份文件所在目录 >

# 步骤六：复制备份文件到数据目录
innobackupex --datadir=<MySQL 数据目录 > --copy-back < 全量备份文件所在目录 >

3.2 应用 binlog 做增量备份

应用 binlog 做增量备份比较简单，备份的时候执行 FLUSH LOGS 轮转日志，而后把旧的 binlog 复制到备份目录就能够了。

复原的时候应用 mysqlbinlog --start-position=< 备份集的 pos 点 > binlog 日志 | mysql -u<user> -p 就能够了

4. 提早同步

提早同步是常见的应用主从复制应用模式，在遇到误操作的时候，无论是用于复原数据，还是应用跳过的形式跳过谬误都是十分有用。

例如在主库做了 drop 的误操作，在主库找到命令所在 binlog 日志和 pos 地位，Delay 库进行同步，而后应用 start slave until master_log_file='< 对应 file>',master_log_pos=< 误操作命令前一个 SQL 的 pos>; 期待同步到这个地位，执行跳过一条 SQL 的命令再开启同步。

常见的提早同步复制模式有:

一主带三从

有时候为了缩小主库压力，会把提早库放在备节点之后

提早同步开启形式如下:

stop slave;
CHANGE MASTER TO MASTER_DELAY = N 秒;
start slave;

5. 数据校验

除了备份，十分重要的一件事件就是验证备份数据的可用性。设想一下，当你须要进行数据恢复的时候，突然发现过来的备份数据都是有效的，那得有多好受。很多敌人在写好备份脚本加到定时工作后，只有查看到定时工作有执行，备份目录有文件就不再关注了，往往到了须要应用备份文件的时候才发现备份数据有问题。

目前对于备份文件的数据校验没有十分不便的方法，用的比拟多的还是定时把备份文件拉进去做备份复原演练，例如一个月做一次备份复原演练就能够无效进步备份文件可用性，心里也虚浮。

数据校验局部，如果是逻辑备份，往往会抽查某个表的数据，查看是否合乎预期。如果是物理备份，首先要应用 mysqlcheck 等命令查看是否有表损坏，没有损坏再抽查表数据。

6. 总结

1. 逻辑备份和物理备份能够一起应用，不同的备份周期应用不同的工具，全备周期不应该太长，至多一周一次全备
2. 如果数据量较大，能够应用增量备份的形式缩小数据量，要留神的是，增量备份危险更大
3. binlog 性能要开启，设为 row 模式，设置 log_slave_updates = 1，且最好定时备份 binlog
4. 有条件的话能够减少一个 Delay 库，在做紧急复原的时候有奇效
5. 数据校验要定时去做，否则当须要备份复原的时候而备份文件又生效，悔恨都来不及