——「高性能MYSQL」读书笔记（第十五章）

《高性能****MYSQL》

MYSQL经典书籍，常读常新。

• 灾难恢复

• 人们改变想法

• 审计

• 测试

• 人为错误

只有世界上最好的备份系统是没用的，还需要一个强大的恢复系统。规划备份和恢复策略的两个重要需求：恢复点目标(PRO)和恢复时间目标(RTO)

在定义PRO和RTO时，考虑以下几个问题

• 在不导致严重后果情况下，可以容忍损失多少数据？

• 恢复需要在多长时间内完成？哪种类型的宕机是可以接受的？哪种影响是应用和用户可以接受的？当那些场景发生时，又改如何持续服务？

• 需要恢复什么？整个服务器、单个数据库、单个表或是特定的事务或语句？

在规划备份时，需要考虑的性能因素

• 锁时间：需要持有锁多长时间，例如在备份期间持有的全局FLUSH TABLES WITH READ LOCK？

• 备份时间：复制备份到目的地需要多久？

• 备份负载：在复制备份到目的地时对服务器性能的影响有多少？

• 恢复时间：把备份镜像从存储位置复制到MySQL服务器，重放二进制日志，需要多久？

优点

• 可视化好

• 有助于避免数据损坏

• 与存储引擎无关

• 可通过网络备份和恢复

缺点

• 恢复时间长且不可控

• SQL语句巨大

• 不能保证数据百分百还原。可能出现浮点表示问题，软件BUG等

优点

• 恢复速度快

• 备份简单，复制粘贴即可完成备份

缺点

• 原始文件通常比相应的逻辑备份要大得多

建议混合使用，先使用物理复制，以此数据启动MySQL服务器实例并运行mysqlcheck，然后周期性🉐️使用mysqldump执行逻辑备份。

• 复制配置：备份与复制相关的文件，例如二进制日志、中继日志、日志索引文件和.info文件。

• 服务器配置

• 选定的操作系统文件

增量备份和差异备份

• 差异备份：对自上次全备份后所有改变的部分而做的备份

• 增量备份：从任意类型的上次备份后所有修改做的备份

需要考虑的两类一致性：数据一致性和文件一致性

数据一致性

对于InnoDB，直接开始事务，转储相关表，提交事务即可。只要使用RR事务隔离级别，并且没有任何DDL，就一定会有完美的一致性，以及基于时间点的数据快照，且在备份过程中不会阻塞任何后续的工作。

文件一致性

对于InnoDB，确保文件在磁盘上一致比MyISAM更加困难。即使使用FLUSH TABLES WITH READ LOCK，InnoDB依旧在后台运行：插入缓存、日志和写线程继续将变更合并到日志和表空间文件中。可通过以下方法规避

• 等待直到InnoDB的清除线程和插入缓冲合并线程完成。

• 在一个类似LVM的系统中获取数据和日志文件一致的快照，必须让数据和日志文件在快照时相互一致。

• 发送一个STOP信号给MySQL，做备份，然后再发送一个CONT信号来再次唤醒MySQL。适用于需要关闭服务器的场景，不需要再重启服务器后预热。

服务器的二进制日志是备份的最重要因素之一。他们对于基于时间点的恢复是必须的，并且通常比数据要小，所以更容易进行频繁的备份。

mysql5.6版本的mysqlbinlog有一个非常方便的特性，可连接到服务器上来实时对二进制日志做镜像，比运行一个mysqld实例要简单和轻便。

安全地清除老的二进制日志

二进制日志保留多久，应考虑设置复制、分析服务器负载、审计和从上次全备按时间点进行恢复等因素。千万不要使用rm删除日志，会导致mysql-bin.index状态文件与磁盘文件不一致，应当使用expire_log_days变量告诉My SQL定期清理日志。

逻辑备份包含SQL导出和符分隔文件两种。

一种非常好的在线备份方法。支持快照的文件系统和设备包括FreeBSD的文件系统、ZFS文件系统、GNU/Linux的逻辑卷管理(LVM)，以及许多的SAN系统和文件存储解决方案。

LVM使用写时复制的技术来创建快照——例如，对整个卷的某个瞬间的逻辑副本。

• 所有的InnoDB文件必须是在单个逻辑卷

• 如果需要备份表定义，MySQL数据目录必须在相同的逻辑卷中

• 必须在卷组中有足够的空闲空间来创建快照

1. FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;使用全局读锁将表刷到磁盘上，获取二进制日志位置；

2. 获取LVM快照并立即释放该读锁。

3. 加载快照并复制文件到备份位置。

LVM需要从原始卷中读取大部分数据，只有快照创建后修改过的数据从写时复制空间读取；因此，逻辑顺序读取快照数据也可能导致磁头来回移动。快照实际上会导致原始卷和快照都比正常的读/写性能要差——如果使用过多的写时复制空间，性能可能会差很多，最多可能会慢5倍。

为快照分配足够多的空间

前置要求

InnoDB的事务日志文件与表空间文件匹配

1. 关闭MySQL

2. 复制文件到正确位置

3. 重启MySQL

4. 观察MySQL启动错误日志。

tail -f /var/log/mysql/mysql.err

show table status;

将一份大SQL拆分成可控大小的块来加载，逐个提交事务。

全备+二进制日志重放

如果在备库执行问题语句之前发现问题，那么基于时间点的恢复就更快更容易了。

1. 停止备库，执行START SLAVE UNTIL来重放事件直到执行问题语句。

2. 执行SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n，跳过问题语句。

3. START SLAVE，备库执行剩余中继日志。

4. 检查备库，无误提升为主库。

举例：粗心的开发人员昨晚误删了表，现在想恢复此误操作；假定需要恢复的服务器叫做server1，在另一台叫做server2的服务器上恢复昨晚的备份。

1. 设置server2为日志服务器来接收server1的二进制日志

2. 改变server2的配置文件，增加replicate-do-table=${table}

3. 重启server2，使用CHANGE MASTER TO来让它成为日志服务器的备库，配置它从昨晚的二进制日志坐标读取。

4. 检测server2的SHOW SLAVE STATUS的输出，验证一切正常。

5. 在server2上执行START SLAVE UNTIL来重放事件直到问题语句。

6. 在server2上执行STOP SLAVE停止复制进程

7. 将所需表从server2复制到server1

InnoDB每次启动时都会检测数据和日志文件是否一致，若不一致则会根据日志文件将事务应用到数据文件，将未提交的变更从数据文件中回滚。

InnoDB****损坏的原因

InnoDB非常健壮可靠，并且有许多内建安全检测来防止、检测和修复损坏的数据。但是，InnoDB并不能保护自己避免一切错误。InnoDB依赖于无缓存的I/O调用和fsync()调用，直到数据完全地写入到物理介质上才会返回。很多InnoDB损坏的原因都是与硬件有关的，常见的错误配置包括打开了不包含电池备份单元的RAID卡的回写缓存，或打开了硬盘驱动器本身的回写缓存。

如何恢复损坏的InnoDB数据

• 二级索引损坏：一般可以用OPTIMIZE TABLE来修复损坏的二级索引；也可以用SELEC T INTO OUTFILE，删除和重建表，然后LOAD DATA INFILE的方法。（本质都是构建一个新表重建受影响的索引，来恢复损坏的索引数据）

• 聚簇索引损坏：也许只能使用innodb_force_recovery选项来导出表

• 损坏系统结构：系统结构包括InnoDB事务日志、表空间的撤销日志区域和数据字典。可能需要做这个数据库的导出和还原。

• MySQL** Enterprise Backup**：MySQL官方备份工具，不需要停止MySQL，也不需要设置锁或中断正常的数据库活动。

• Percona XtraBackup：与MySQL Enterprise Backup在很多方面类型，但是是开源免费的。还可以提供更多高级功能，支持类型流、增量、压缩和多线程备份操作。

• mylvmbackup：一个perl脚本，通过LVM快照帮助MySQL自动备份。此工具首先获取全局读锁，创建快照，释放锁，然后通过tar压缩数据并移除快照。

• Zmanda Recovery Manager(ZRM)：一个备份和恢复管理器，封装了自有的基于标准工具和技术，例如mysqldump、LVM快照和Percona XtraBackup等之上的功能，将许多冗长的备份和恢复工作进行了自动化。

• mysqldumper：用来替代mysqldump，多线程的备份和还原MySQL和Drizzle的工具集。

• mysqldump：与MySQL一起并发的程序，常见的逻辑备份工具。

• 目前企业版profile使用xtrabackup在线全备份，备份过程同时备份数据文件和redo日志，在恢复时应用redo日志，保证数据一致性；但people_corehr_profile_commercial距离上一次全备份时间过长。

• 无归档存储。

容灾能力不足，无容灾演练。

people_corehr_profile_commercial

people_corehr_profile

• 备份应是常态，恢复更是关键；