Mysql备份与恢复策略_mysql生产备份策略

备份的必要性

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

备份分类

一、逻辑备份

使用mysql自带的mysqldump工具进行备份。备份成sql文件形式。
优点：最大好处是能够与正在运行的mysql自动协同工作，在运行期间可以确保备份是当时的点，它会自动将对应操作的表锁定，不允许其他用户修改(只能访问)。可能会阻止修改操作。sql文件通用方便移植。
缺点：备份的速度比较慢。如果是数据量很多的时候。就很耗时间。如果数据库服务器处在提供给用户服务状态，在这段长时间操作过程中，意味着要锁定表(一般是读锁定，只能读不能写入数据)。那么服务就会影响的。
备注：所谓的与mysql服务器能够自动协同工作，实际上是指加参数来控制mysql服务器，比如锁定所有表只能进行读，不能进行写操作。
--lock-all-tables

二、物理备份

直接拷贝mysql的数据目录。
缺点，不能去操作正在运行的mysql服务器(在拷贝的过程中有用户通过应用程序访问更新数据，这样就无法备份当时的数据)可能无法移植到其它机器上去。直接拷贝只适用于myisam类型的表。这种类型的表是与机器独立的。但实际情况是，你设计数据库的时候不可能全部使用myisam类型表。你也不可能：因为myisam类型表与机器独立，方便移植，于是就选择这种表，这并不是选择它的理由。
更多的情况是，你会根据业务特点(比如你需要支持事务机制就必须使用innodb)，查询速度和服务性能来选择表类型的。
必须保证表不被使用中。
如果服务器在你则正在拷贝一个表时改变它，拷贝就失去意义。
如果数据库表在文件系统备份过程中被修改，进入备份的表文件主语不一致的状态，而对以后的恢复表将失去意义。
保证你的拷贝完整性的最好方法是：关闭服务器，拷贝文件，然后重启服务器。
或者是，要锁定对应的表(对前端用户造成访问问题)。
解释直接拷贝文件，为什么不具备可移植性？
Mysqldump 产生可移植到其他机器、甚至具有不同硬件结构的机器上的文本文件。直接拷贝文件不能够移植到其他机器上，除非要拷贝的表使用MyISAM 存储格式。ISAM 表只能在具有相同硬件结构的机器之间进行拷贝。例如，将文件从S PARC 的Solaris 机器拷贝到Intel 的Solaris 机器（或者相反）是行不通的。由MySQL3.23 引进的MyISAM 表存储格式可以解决这个问题，因为该格式与机器独立。因此，如果以下两个条件都满足的话，直接拷贝文件可以移植到具有不同硬件结构的机器上：即另一台机器 上也必须运行MySQL3.23 以上的版本，并且文件必须表示成MyISAM 表，而不是ISAM 表。

三、双机热备份

mysql数据库没有增量备份的机制。当数据量太大的时候备份是一个很大的问题。还好mysql数据库提供了一种主从备份的机制(也就是双机热备)。
优点：适合数据量大的时候。现在明白了。大的互联网公司对于mysql数据备份，都是采用热机备份。搭建多台数据库服务器，进行主从复制。
主从复制经常遇到的问题就是，如何保证数据不堵塞，不延迟。这个问题还是可以容忍的，有一些方案可以改善。毕竟有得有失的。这已经是很省心省力的方式了。

备份与恢复策略

备份

全量备份

mysqldump

mysqldump 是用得最多的工具，但是要用好的话，需要增加一些额外的参数。

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文件中注释掉这一行，在使用备份文件做新备库时会用到
gzip >> backup.sql.gz  压缩
--databases <dbname1> <dbname2> 备份多个库

增量备份

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

增量备份只包含变化的数据集，一般情况不会比原始数据量大，所以可以减少服务器的开销、备份时间、备份空间。

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

binlog

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

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

延迟同步

延迟同步是常见的使用主从复制使用模式，在遇到误操作的时候，无论是用于恢复数据，还是使用跳过的方式跳过错误都是非常有用。

常见的延迟同步复制模式有:

一主带三从

有时候为了减少主库压力，会把延迟库放在备节点之后 

延迟同步开启方式如下:

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

恢复

全量恢复

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

增量恢复

可以基于位置恢复、基于时间点恢复。

 mysqlbinlog --start-position=<备份集的pos点> binlog日志 | mysql -u<user> -p 

生产策略

1、中小数据库，全量一般是一天一次，业务流量低谷执行全备，执行前锁表。
使用rsync定时把所有的binlog备份到远程服务器。

2、大型数据库周备，周六00点一次全量，周日--下周六00点前都是增量。

3、一主五从，会有一个从库做备份，延迟同步。