面试题：如何确保MySQL主备库使用不同存储引擎时复制的高性能与高可靠性？

Question

假设主库使用InnoDB，备库使用Memory存储引擎，阐述从网络配置、参数调优、数据同步机制等多方面，怎样保障主备库复制过程的高性能与高可靠性，同时要考虑到可能出现的故障场景及应对措施。

Answer 1

网络配置

1. 高带宽与低延迟：
   • 确保主库和备库之间的网络连接具备足够的带宽，以快速传输大量的二进制日志（binlog）。例如，使用万兆以太网连接，减少数据传输瓶颈。
   • 优化网络路由，通过合理设置网络拓扑和路由策略，降低网络延迟，保证主备库之间数据传输的及时性。
2. 冗余网络：
   • 采用双网卡绑定（bonding）技术，为主备库服务器配置多个网络接口，并将它们绑定在一起，提供冗余链路。若一条链路出现故障，另一条链路可继续工作，确保网络连接的不间断。
   • 部署网络负载均衡器（如F5、Nginx等），将主库的复制流量均匀分配到多个网络链路或服务器上，避免单点故障，并提高整体网络性能。

参数调优

1. 主库参数：
   • sync_binlog：设置为1，确保每次事务提交时都将binlog同步到磁盘，保证数据一致性，但可能会对性能有一定影响。若对性能要求极高且能接受一定的数据丢失风险，可适当设置为0或较大的值（如1000），但要注意在系统崩溃时可能丢失部分未同步的binlog。
   • innodb_flush_log_at_trx_commit：设置为1，保证每次事务提交时，InnoDB将日志缓冲写入日志文件并刷新到磁盘，确保数据的持久性。同样，若追求高性能可设置为2（每秒刷新一次日志到磁盘）或0（由操作系统控制刷新），但存在崩溃时丢失数据的风险。
   • binlog_format：选择ROW格式，它记录每一行数据的变化，相比STATEMENT格式能更准确地进行数据复制，尤其是在处理复杂数据操作（如触发器、存储过程等）时，避免主备库数据不一致问题。
2. 备库参数：
   • Memory存储引擎参数：max_heap_table_size设置足够大，以容纳从主库同步过来的数据。根据实际业务数据量评估，确保不会因为内存不足导致复制失败。例如，如果预估同步过来的数据量最大为1GB，可将该参数设置为1.5GB左右，预留一定的冗余空间。
   • 复制相关参数：slave_parallel_workers：根据备库的CPU核心数合理设置，开启并行复制，提高复制效率。例如，若备库是8核CPU，可设置为4 - 6个并行线程，具体数值需通过性能测试确定。
   • slave_net_timeout：设置合适的值（如60 - 120秒），控制从库等待主库发送数据的超时时间。若网络不稳定，可适当调大该值，防止因短暂网络波动导致复制中断。

数据同步机制

1. 基于GTID（全局事务标识符）的复制：
   • 在主备库上开启GTID模式，确保每个事务在主库上生成唯一的GTID。备库通过GTID来识别和应用主库上的事务，避免传统基于日志文件名和位置的复制方式可能出现的主备库不一致问题，提高数据同步的准确性和可靠性。
   • 配置主备库时，使用CHANGE MASTER TO语句指定主库的地址、端口、用户名、密码以及GTID_MODE = ON等参数，使备库能够正确连接主库并基于GTID进行复制。
2. 半同步复制：
   • 在主备库上启用半同步复制插件，主库在提交事务前，等待至少一个备库接收并写入relay log（中继日志）后才返回成功给客户端。这确保了在主库故障时，至少有一个备库保存了最新的事务数据，提高数据安全性。
   • 配置半同步复制时，主库需加载rpl_semi_sync_master插件，并设置rpl_semi_sync_master_wait_for_slave_count为1（等待一个备库确认），rpl_semi_sync_master_timeout设置合理的等待超时时间（如10000毫秒）。备库加载rpl_semi_sync_slave插件并启动。

故障场景及应对措施

1. 网络故障：
   • 检测：通过定期的网络ping测试、带宽监测工具（如iperf）以及数据库复制状态监控（如SHOW SLAVE STATUS中的Seconds_Behind_Master等字段）来及时发现网络故障。
   • 应对：利用冗余网络链路，自动切换到备用链路继续复制。若网络故障导致复制中断，当网络恢复后，从库会自动从断点处继续复制。对于长时间网络故障，可通过重新配置主备关系（如重新执行CHANGE MASTER TO语句）来恢复复制。
2. 主库故障：
   • 检测：通过监控主库的数据库进程状态（如使用操作系统命令ps -ef | grep mysqld查看MySQL进程是否存活）、心跳检测（如设置定时任务ping主库IP）以及应用层对数据库连接的检测等方式发现主库故障。
   • 应对：
     • 故障转移：若主库故障，需要将备库提升为主库。首先停止备库的复制进程（STOP SLAVE），然后执行RESET MASTER（若开启GTID模式则不需要），重新配置应用程序连接新的主库地址。
     • 数据一致性：如果采用半同步复制，新主库的数据丢失风险较小。若未采用半同步复制，可能存在部分未同步到备库的事务。此时可通过人工介入，从主库备份中恢复缺失的事务数据，并在新主库上重新应用。
3. 备库故障：
   • 检测：通过监控备库的数据库进程状态、复制状态（SHOW SLAVE STATUS查看是否有错误信息）以及与主库的连接状态等方式发现备库故障。
   • 应对：重启备库服务器，如果是硬件故障，更换硬件后重新配置备库。从主库获取最新的备份数据，恢复到备库，然后重新配置复制关系（CHANGE MASTER TO），启动备库复制进程（START SLAVE）。同时，监控主库是否因为备库故障导致复制积压，必要时调整主库参数或增加备库数量以减轻压力。