1.MySQL 主从复制
2.OneProxy 实现 MySQL 读写分离
3.MySQL 数据库结构设计
4.MySQL 基于 GTID 主从复制的杂谈
5.MySQL 复制性能优化和常见问题分析
先来说说影响 MySQL 复制性能的几个参数吧
二进制日志文件并不是每次写的时候都会同步到磁盘, 当发生宕机的时候, 可能会有最后一部分数据没有写入到 binlog 中, 这给恢复和复制带来了问题. 当 sync_binlog=1 表示每写缓冲一次就同步到磁盘, 表示同步写磁盘的方式来写 binlog. 也就是说每当向 MySQL 提交一次事务, MySQL 将进行一次 fsync 之类的磁盘同步命令来将 binlog_cache 的数据强制刷到磁盘中 sync_binlog 的值默认为 0,sync_binlog=0 时表示采用操作系统机制进行缓冲数据同步. 采用 sync_binlog=1 时, 会增加磁盘 IO 的次数, 会影响写入性能. sync_binlog=1 时, 并不是 100% 安全, 会存在相应的问题. 比如说使用 Innodb 引擎时, 在一个事务发出 commit 前, 会将 binlog 立即刷到磁盘中. 如果这时候已经写入到 binlog 中, 但是还没有提交就已经挂了, 那么 MySQL 重启时, 会将通过 Redo log,Undo log 将这个事务回滚掉, 但是 binlog 已经记入了该事务信息, 不能回滚掉. 所以我们需要设置 innodb_support_xa=1 确保 MySQL 服务层的 binlog 和 MySQL 存储引擎层的 Redo log,Undo log 之间的数据一致性.
sync_binlog=1
指定 master_info 和 replay_log_info 信息的存储方式为 table. 如果 MySQL 挂了的后, 由于 Innodb 存储引擎的特点, 可以对这 2 张表进行故障恢复, 保证 slave 能从正确的位置进行数据恢复.
- master_info_repository=TABLE
- relay_log_info_repository=TABLE
可以看到 slave_master_info 这张表存储着 master 的 binlog 以及当前 master 写入 binlog 的位置等信息.
- MySQL> select * from slave_master_info\G
- *************************** 1. row ***************************
- Number_of_lines: 25
- Master_log_name: MySQL-bin.000001
- Master_log_pos: 914
- Host: 192.168.10.21
- User_name: gtid
- User_password: gtid
- Port: 3306
- Connect_retry: 60
- Enabled_ssl: 0
- Ssl_ca:
- Ssl_capath:
- Ssl_cert:
- Ssl_cipher:
- Ssl_key:
- Ssl_verify_server_cert: 0
- Heartbeat: 30
- Bind:
- Ignored_server_ids: 0
- Uuid: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026
- Retry_count: 86400
- Ssl_crl:
- Ssl_crlpath:
- Enabled_auto_position: 1
- Channel_name:
- Tls_version:
- 1 row in set (0.00 sec)
可以看到 slave_relay_log_info 记录了 slave 的 relay log 的位置, master binlog 的名称, master binlog 当前偏移量, relay log 当前偏移量等信息.
- MySQL> select * from slave_relay_log_info\G
- *************************** 1. row ***************************
- Number_of_lines: 7
- Relay_log_name: ./MySQL-relay-bin.000004
- Relay_log_pos: 831
- Master_log_name: MySQL-bin.000001
- Master_log_pos: 914
- Sql_delay: 0
- Number_of_workers: 16
- Id: 1
- Channel_name:
- 1 row in set (0.00 sec)
当 slave 挂了后导致中继日志损坏后, 导致一部分中继日志没有处理, 则放弃所有未执行的 relay log, 并且重新从 master 获取日志, 这样就保证了 relay log 的完整性.
relay_log_recovery=1
这些参数和 sync_binlog 参数一样, slave 的 IO 线程每次接收到 master 发送过来的日志都要写入到系统缓冲区, 然后再刷到磁盘中. 这样 master 崩溃时, 最多丢失一个事务. 虽安全, 但是会造成大量的磁盘 IO.sync_relay_log,sync_relay_log_info,sync_master_info 默认值都为 10000
- sync_relay_log=1
- sync_relay_log_info=1
- sync_master_info=1
image.PNG
默认是 1, 自动清空不需要的中继日志.
relay_log_purge=1
MySQL5.6 有基于库的并行复制, 可以设置 slave-parallel-workers 参数设置 workers 的个数. 当开启并行复制功能后, 那么 SQL 线程就变成了 Coordinator 线程.
不同 schema 下的表并发提交的数据不会有影响, slave 会对 relay log 中不同 schema 分配 workers 线程, 来回放 relay log 中 master 已经提交的事务, 保持数据一致性. 如果单实例仅有一个库, 开启并行复制功能后, 那么就无法实现并行回放, 甚至性能会比以前的单线程更差.
在 MySQL5.7, 引入了基于组提交的并行复制, 设置 slave-parallel-type=LOGICAL_CLOCK. 支持在同一个 schema 下, 支持 slave-parallel-worker 个 worker 线程并发回放 relay log 中 master 的事务. 一个组提交的事务是可以并行回放的. 在 slave 中的 relay log 中具有相同的 last_committed 值 (sequence_num 不同) 的事务是属于同一个组的.
- slave-parallel-type=LOGICAL_CLOCK
- slave-parallel-workers=16
image.PNG
选择复制模式需要考虑的几个点:
1. 所使用的 MySQL. 如果是 5.6 以上, 可以考虑 GTID 复制. 如果是 5.6 以下的, 可以考虑 binlog 复制.
2. 复制架构及主从切换的方式. 如果是一主多从, 还是推荐 GTID 复制, 不会为新 master 的 binlog 偏移量而担心.
3. 所使用的高可用管理组件. MMM 只支持基于日志的方式. 而 MHA 支持日志, GTID 复制.
在 MySQL5.7 之前, 一个从库只能有一个主库. MySQL5.7 之后, 支持一从多主架构.
一主多从的复制拓扑架构, 配置简单, 可以用多个从库分担读负载, 使数据差异最小化. 用途: 1. 为不同的业务使用不同的从库. 2. 将一台从库放到远程 IDC, 用于灾备恢复. 3. 分担主库的读负载.
主主 (双机热备) 复制拓扑架构, 并不能分担写负载. 缺点: 1. 经常产生数据冲突从而造成复制链路中断. 2. 耗费大量的时间, 造成数据丢失. 所以建议两个主库所操作的表最好能够分开, 使用 auto_increment_increment 和 auto_increment_offset=1|2 参数控制自增 ID 的生成.
主主 (单机热备) 复制拓扑架构, 只有一台主服务器对外提供服务, 另一台服务器处于只读状态并且只作为热备使用. 在对外提供服务的主库出现故障或是计划性的维护时才会进行切换. 使用这种拓扑架构, 需要注意以下几点:
1. 确保两台服务器上的初始数据相同.
2. 确保两台服务器上已经启动 binlog, 并且有不同的 server_id.
3. 在两台服务器上启动 log_slave_updates 参数.
4. 在初始备库上启动 ready_only.
影响主从延迟的因素:
1. 主库写入到二进制的时间.
2. 控制主库的事务大小, 分割大事务.
3. 二进制日志传输时间取决于传输日志量的多少. 推荐使用 mixed 日志格式.
4. 默认情况下从库只有一个 sql 线程. 也就是说 master 上并发的修改在 slave 变成了串行. 可以采用多线程复制(设置 slave-parallel-type=LOGICAL_CLOCK,slave-parallel-workers=16).
在 MySQL 主从复制过程中, 要注意以下问题:
1. 主库意外重启或者主库的二进制文件损坏. 我们可以在 slave 上通过 change master 命令来重新指定 binlog 偏移量, 同时配置 sync_binlog=1 使每次写入对 binlog 进行同步, 落地磁盘, 减少宕机所丢失的事务数.
2. 从库上的中继日志损坏.
3. 从库宕机, 引起 master.info 文件没有及时更新到磁盘上. master.info 记录着从库同步主库的相关信息, 会引起从库重复同步的操作.
4. 在从库上进行数据修改造成的主从复制错误.
5. 不唯一的 server_id 或者 server_uuid.
6.max_allowed_packet 设置引起的主从复制错误.
一般我们可以采用以下几种方法解决主从复制问题:
1. 跳过二进制日志文件.
2. 注入空事务的方式先恢复被中断的复制链路
再使用其他方法来对比主从服务器上的数据.
MySQL 复制无法解决的问题有哪些:
1. 分担主数据库的写负载.
2. 自行进行故障转移及主从切换.
3. 提供读写分离功能.
在这里, 我们要可以引出一个概念, 高可用. 高可用性 (HA,High Availability) 指的是通过尽量缩短因日常维护操作 (计划) 和突发的系统崩溃 (非计划) 所导致的停机时间, 以提高系统和应用的可用性.
我们应该避免导致系统不可用的因素, 减少系统不可用的时间.
1. 严重的主从延迟.
2. 主从复制中断.
3. 锁引起的大量阻塞.
4. 服务器磁盘空间耗尽, 比如备份或者各种查询日志快速增长导致磁盘空被占满. 或者是 MySQL 由于无法记录二进制日志, 无法处理新的请求而产生的系统不可用的故障.
5. 性能糟糕的 sql.
6. 表结构和索引没有优化.
7. 主从数据不一致.
8. 人为的操作失败等等.
那如何实现高可用呢?
1. 建立完善的监控及报警系统.
2. 对备份数据进行恢复测试.
3. 正确配置数据库环境.
4. 对不需要的数据进行归档和清理.
5. 增加系统冗余, 保证发生系统不可用时可以尽快恢复.(避免存在单点故障, 可以进行主从切换及故障转移)
在这里我们又要引出一个概念, 单点故障. 它是指在一个系统中提供相同功能的组件只有一个, 如果这个组件失效了, 就会影响整个系统功能的正常使用, 组成应用系统的各个组件都有可能成为单点. 那么我们如何避免 MySQL 单点故障呢?
1. 利用 SUN 共享存储或者 DRBD 磁盘复制来解决 MySQL 单点故障.
2.MySQL 主从复制(关于主从复制, 我们该考虑 3 个点. 一是主服务器切换后, 该如何通知应用新 master 的 ip 地址. 二是如何检查 MySQL 主服务器是否可用. 三是如何处理从服务器和新主服务器之间的那种复制关系)
为了解决 MySQL 主从复制管理的痛点, 才衍生出了 MMM,MHA 这些高可用的管理组件. 在本文章的末尾, 简单介绍一下 MMM 是什么.
MMM 的主要作用是监控和管理 MySQL 的主主复制拓扑结构, 并在当前的主服务器失效时, 进行主和主备服务器之间的主从切换和故障转移等工作.
MMM 可以在主库出现宕机时进行故障转移并且自动配置其他从服务器对新主服务器的复制.
使用 MMM 要考虑两点: 一是如何找到从库对应的新主服务器的日志同步点. 二是如果存在多个从库出现数据不一致的情况, 如何处理. 在一个繁忙的系统中, 使用 MMM 有可能会造成数据丢失.
MMM 提供了读, 写虚拟 IP. 在主从服务器出现问题时可以自动迁移虚拟 IP.
来源: http://www.jianshu.com/p/86996cdc1e02