2023年7月30日发(作者：)

MySQL备份策略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# 备库在重做数据的时候，记录⼀条 binloglog_slave_updates = 11.3 复制和备份主从复制等于多了⼀个数据副本，但是复制并不等于备份，也不能代替备份。假设在主库执⾏了

drop table 操作，会⽴刻同步到备库，并执⾏相同的操作，没有办法在出现意外的时候使⽤备库进⾏数据恢复。延迟复制也不能代替备份，但是能加快恢复的速度，是⼀种⾮常有⽤的策略。在实际使⽤中，为了不影响主库的使⽤，我们往往会在备库进⾏备份，同时记录同步点，以⽅便进⾏新备库搭建。在备库备份需要注意的是，主从复制并不能保证主备间数据是⼀致的。实际上，基于复制的

MySQL 集群并不能保证集群内部⼀致性，当前也没有⾮常好的办法，常⽤的是使⽤

pt-table-checksum 进⾏⼀致性检查。2. 全量备份全量备份介绍最常⽤的逻辑备份⼯具

mysqldump 和物理备份⼯具

xtrabackup。如果对

mysqldump 不太满意 可以使⽤

mydumper 来替代

mysqldump。2.1 mysqldumpmysqldump 是⽤得最多的⼯具，但是要⽤好的话，需要增加⼀些额外的参数。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 -u -p -A > --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 -u -p -A > --lock-all-tables 锁表备份。由于

MyISAM 不能提供⼀致性读，如果要得到⼀份⼀致性备份，只能进⾏全表锁定。2.1.1.3 备份带上压缩mysqldump -h -u -p -A | gzip >> 2.1.1.4 备份多个库mysqldump -h -u -p --databases > 2.1.2 恢复恢复⽅式⽐较简单，直接执⾏ sql 语句就可以了mysql -h -u -p < 2.1.3 mysqldump执⾏流程打开

general_log 可以查看

mysqldump 的执⾏流程，这⾥以

--single-transaction --opt -A 参数为例FLUSH /*!40101 LOCAL */ TABLESFLUSH TABLES WITH READ LOCKSET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READSTART TRANSACTIONSHOW VARIABLES LIKE 'gtid_mode'SHOW MASTER SHOW CREATE DATABASE IF NOT EXISTS `employees`SELECT /*!40001 SQL_NO_CACHE */ * FROM `departments`....2.2 xtrabackup2.2.1 安装⽅式更多安装⽅式参考官⽹这⾥我们使⽤

rpm 安装的⽅式yum install /downloads/percona-release/redhat/0.1-6/ update percona-release# qpress ⽤作压缩解压yum install percona-xtrabackup-24 qpress2.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/ --user= --password= <要备份到哪个⽬录> --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= --copy-back <备份⽂件所在⽬录> --parallel=43. 增量备份当数据了变得庞⼤时，⼀个常见策略就是做定期的增量备份。例如：周⼀做了⼀次全量备份，然后周⼆到⽇做增量备份。增量备份只包含变化的数据集，⼀般情况不会⽐原始数据量⼤，所以可以减少服务器的开销、备份时间、备份空间。当然，使⽤增量备份也会有风险，增量备份每⼀次迭代都是基于上⼀次的备份实现，意味着只要其中⼀份备份出现问题，那么就有可能导致所有备份不可⽤。下⾯介绍⼀些增量备份⽅法：3.1 使⽤ xtrabackup 做增量备份xtrabackup 允许进⾏增量备份，命令如下:innobackupex --defaults-file=/etc/ --user= --password= --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= --copy-back <全量备份⽂件所在⽬录>3.2 使⽤ binlog 做增量备份使⽤

binlog 做增量备份⽐较简单，备份的时候执⾏

FLUSH LOGS 轮转⽇志，然后把旧的

binlog 复制到备份⽬录就可以了。恢复的时候使⽤

mysqlbinlog --start-position=<备份集的pos点> binlog⽇志 | mysql -u -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，且最好定时备份 binlog4. 有条件的话可以增加⼀个 Delay 库，在做紧急恢复的时候有奇效5. 数据校验要定时去做，否则当需要备份恢复的时候⽽备份⽂件⼜失效，后悔都来不及