MySQL主从复制
MySQL 主从复制是其最重要的功能之一. 主从复制是指一台服务器充当主数据库服务器, 另一台或多台服务器充当从数据库服务器, 主服务器中的数据自动复制到从服务器之中. 对于多级复制, 数据库服务器即可充当主机, 也可充当从机. MySQL 主从复制的基础是主服务器对数据库修改记录二进制日志, 从服务器通过主服务器的二进制日志自动执行更新.
1. 数据库主从复制配置
1 修改 master 数据库配置.
[mysqld]
server-id=1 #设置server-id
log-bin=mysql-bin #开启二进制日志
binlog-do-db=database1 #如果备份多个数据库, 重复设置这个选项即可. 如果没有本行, 即表示同步所有数据库.
binlog-ignore-db=mysql #被忽略的数据库
2 修改 slave 数据库配置
[mysqld]
server-id=2 #设置server-id, 此处不能与master的service-id相同
3 重启 master 和 slave 的 mysql 服务
4 检查 master 的 binlog 文件及位置
mysql> show master status\G
***************** 1. row ****************
File: mysql-bin.000001 #当前记录的日志
Position: 0 #日志中记录的位置
Binlog_Do_DB:
Binlog_Ignore_DB:
5 打开 slave 的 mysql 会话, 执行同步 sql
mysql> change master to
-> master_host='192.168.1.2',
-> master_user='user',
-> master_password='password',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=0;
mysql> start slave; # 开启同步.
mysql> show slave status\G # 查看同步状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.2
Master_User: user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 0
Relay_Log_File: db2-relay-bin.000002
Relay_Log_Pos: 337686
Relay_Master_Log_File: mysql-bin.000033
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
...
当同步状态中的 Slave_IO_Running 及 Slave_SQL_Running 进程为 Yes 状态, 说明同步成功, 否则说明同步失败.
6 关闭同步并清除 binlog
mysql> stop slave; # 启用新复制同步之前, 最好清除日志.
mysql> rest slave all;
2. 主从复制的实现原理
2.1 主从复制原理
MySQL 主从复制涉及到三个线程, 一个运行在 master (log dump thread)节点上, 其余两个(IO thread, sql thread)运行在 slave 节点上. 对于每一个主从连接, 都需要三个线程来完成, 当 master 节点有多个 slave 时, master 会为每一个 slave 创建一个 log dump 线程.
master 节点 log dump 线程
当 slave 连接到 master 之后, master 会创建一个 log dump 线程, 用于发送 binlog 内容.
slave 节点 IO 线程
当 slave 节点执行start slave命令后, slave 会创建一个 IO 线程用于连接主节点并读取 master 中更新的 binlog, 将接收到的 binlog 内容保存在中继日志中.
slave 节点 SQL 线程
SQL 线程负责读取中继日志中的内容, 解析成具体的操作并执行, 最终保证主从数据的一致性.
2.2 主从复制过程
- slave 上的 IO 线程连接 master, 并请求从指定日志文件的指定位置(执行 SQL 中指定的 master_log_file 以及 master_log_pos )之后的内容.
- master 接收到 slave 的 IO 请求后, master 节点的 log dump 线程读取日志文件中指定位置之后的日志信息, 返回给 slave. 返回信息中除了日志包含的信息外, 还包含本次返回信息的 binlog 和 binlog position.
- slave 节点的 IO线程接收到内容后, 将接收到的日志内容更新到本机的中继日志中, 并将读取到的 binlog 文件名和位置保存在 master-info 文件中, 以便下次读取时返回给 master.
- slave 节点的 SQL线程检测到中继日志中新添加了内容后, 将中继日志中的内容解析并在本数据库中执行.
2.3 主从复制方式
- 基于语句的复制(statement-based replication, SBR)
记录 SQL 语句在 binlog 中, Mysql 5.1.4 及之前的版本都是使用的这种复制格式.
优点是只需要记录会修改数据的 SQL 语句到 binlog 中, 减少了 binlog 日志量, 节约 I/O, 提高性能.
缺点是在某些情况下, 会导致主从节点中数据不一致(比如使用 now() 等函数时). - 基于行的复制(row-based replication, RBR)
master 将 SQL 语句分解为基于行更改的语句并记录在 binlog 中, 也就是只记录哪条数据被修改了, 修改成什么样.
优点是不会出现某些特定情况下的存储过程或者函数或者触发器的调用或者触发无法被正确复制的问题.
缺点是会产生大量的日志, 尤其是修改 table 的时候会让日志暴增, 同时增加 binlog 同步时间, 也不能通过 binlog 解析获取执行过的 sql 语句, 只能看到发生的 data 变更. - 混合模式 Mixed-format Replication (MBR)
MySQL NDB cluster 7.3和7.4使用的 MBR. 是以上两种模式的混合, 对于一般的复制使用 STATEMENT 模式保存到 binlog, 对于 STATEMENT 模式无法复制的操作则使用 ROW 模式来保存, MySQL 会根据执行的 SQL 语句选择日志保存方式.
2.4 主从复制模式
- 异步复制
MySQL 的默认模式. 在 master 写入日志后立即向客户端返回成功. - 半同步复制
这种模式下 master 只需要接收到其中一台 slave 的返回信息, 就会向客户端返回成功, 否则需要等待直到超时时间然后切换成异步模式再提交. 这样做的目的可以可以提高数据安全性, 确保事务提交后, binlog 至少传输到了一个从节点上, 不能保证从节点将此事务更新到 db 中.
半同步模式不是 mysql 内置的, 从 mysql 5.5 开始集成, 需要 master 和 slave 安装插件开启半同步模式. - 同步复制
master 和 slave 全部执行成功之后才会向客户端返回成功.
关于如何使用半同步复制, 请点击此处
2.5 MySQL并行复制
当 master 的并发较高时, 产生的 DDL 数量超过 slave 单个 SQL 线程所能承受的范围, 那么就产生延时了, 当然还有可能与 slave 的大型 query 语句产生了锁等待有关.
MySQL 5.6 并行复制
在 MySQL 5.6 版本中已经支持了并行复制, 但是只是基于 schema, 也就是基于库的. 如果用户的 MySQL 数据库实例中存在多个库, 对于 slave 复制的速度会提升比较大. 但在一般的 MySQL 使用中, 一库多表比较常见, 此时的并行复制对于延迟并没有太大的改进.
在 MySQL 5.6 版本之前, slave 上有两个线程: IO 线程和 SQL 线程. IO 线程负责接收 binlog 日志, SQL 线程进行回放 binlog 日志. 如果在 MySQL 5.6 版本开启并行复制功能, 那么 SQL 线程就变为了 coordinator (协调者)线程, coordinator 线程主要负责以前两部分的内容:
- 若判断可以并行执行, 那么选择 worker 线程执行事务的二进制日志.
- 若判断不可以并行执行, 如该操作是 DDL 亦或者是事务跨 schema 操作, 则等待所有的 worker 线程执行完成之后, 再执行当前的日志.
这意味着 coordinator 线程并不是仅将日志发送给 worker 线程, 自己也可以回放日志, 但是所有可以并行的操作交付由 worker 线程完成. coordinator 线程与 worker 是典型的生产者与消费者模型.
上述机制实现了基于 schema 的并行复制存在两个问题, 首先是 crash safe 功能不好做, 因为可能之后执行的事务由于并行复制的关系先完成执行, 那么当发生 crash 的时候, 这部分的处理逻辑是比较复杂的. 从代码上看, 5.6这里引入了 Low-Water-Mark 标记来解决该问题, 从设计上看, 其是希望借助于日志的幂等性来解决该问题, 不过5.6的二进制日志回放还不能实现幂等性. 另一个最为关键的问题是这样设计的并行复制效果并不高, 如果用户实例仅有一个库, 那么就无法实现并行回放, 甚至性能会比原来的单线程更差. 而单库多表是比多库多表更为常见的一种情形.
MySQL 5.6 主从延迟优化
- 分库, 降低 master 的写并发从而降低主从延迟
- 开启并行复制有效降低同步延迟(如果写并发特别高, 不会有显著的降低)
- 修改代码, 尽量减少耗时的 sql 以及锁的争抢.
- master 对数据安全性比较高, 但 slave 不需要那么高的安全性, 可以关闭 slave 的 binlog 或者修改 sync_binlog 和 innodb_flush_log_at_trx_commit 参数从而提高 sql 的执行效率
sync_binlog: sync_binlog = 0 ,表示 MySQL 不控制 binlog 的刷新, 由文件系统自己控制它的缓存的刷新. 如果sync_binlog > 0, 表示每 sync_binlog 次事务提交, MySQL 调用文件系统的刷新操作将缓存刷下去. 最安全的就是sync_binlog = 1了, 表示每次事务提交, MySQL 都会把 binlog 刷下去, 是最安全但是性能损耗最大的设置.
innodb_flush_log_at_trx_commit: 当 innodb_flush_log_at_trx_commit 取值为 0 的时候, log buffer 会每秒写入到日志文件并刷写 (flush) 到磁盘. 当取值为1时, 每次事务提交时, log buffer 会被写入到日志文件并刷写到磁盘. 这也是默认值. 这是最安全的配置, 但由于每次事务都需要进行磁盘 I/O, 所以也最慢. 当取值为2时, 每次事务提交会写入日志文件, 但并不会立即刷写到磁盘, 日志文件会每秒刷写一次到磁盘.
查看: show global variables where Variable_name like "sync_binlog%"
设置: set global sync_binlog = 1
MySQL 5.7 并行复制
MySQL 5.7 才可称为真正的并行复制, 这其中最为主要的原因就是 slave 服务器的回放与主机是一致的, 即 master 服务器上是怎么并行执行的, slave 上就怎样进行并行回放. 不再有库的并行复制限制, 对于二进制日志格式也无特殊的要求(基于库的并行复制也没有要求).
在MySQL 5.7上开启并行复制
MySQL 5.7 的并行复制建立在组提交的基础上, 所有在主库上能够完成 Prepared 的语句表示没有数据冲突, 就可以在 Slave 节点并行复制. 关于 MySQL 5.7 的组提交, 我们要看下以下的参数：
mysql> show global variables like '%group_commit%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)
要开启 MySQL 5.7 并行复制需要以下二步, 首先在主库设置 binlog_group_commit_sync_delay 的值大于 0.
mysql> set global binlog_group_commit_sync_delay=10;
这里简要说明下 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数的作用.
binlog_group_commit_sync_delay: 全局动态变量, 单位微妙, 默认 0, 范围: 0～1000000(1秒).
表示 binlog 提交后等待延迟多少时间再同步到磁盘, 默认 0, 不延迟. 当设置为 0 以上的时候, 就允许多个事务的日志同时一起提交, 也就是我们说的组提交. 组提交是并行复制的基础, 我们设置这个值的大于 0 就代表打开了组提交的功能.
binlog_group_commit_sync_no_delay_count: 全局动态变量, 单位个数, 默认 0, 范围: 0～1000000. 表示等待延迟提交的最大事务数, 如果上面参数的时间没到, 但事务数到了, 则直接同步到磁盘. 若 binlog_group_commit_sync_delay 没有开启, 则该参数也不会开启.
其次要在 Slave 主机上设置如下几个参数：
# 过多的线程会增加线程间同步的开销, 建议4-8个Slave线程.
mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)
mysql> set global slave_parallel_type='LOGICAL_CLOCK';
Query OK, 0 rows affected (0.00 sec)
mysql> set global slave_parallel_workers=4;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.06 sec)
mysql> show variables like 'slave_parallel_%';
+------------------------+---------------+
| Variable_name | Value |
+------------------------+---------------+
| slave_parallel_type | LOGICAL_CLOCK |
| slave_parallel_workers | 4 |
+------------------------+---------------+
2 rows in set (0.00 sec)
检查 Worker 线程的状态
当前 slave 的 SQL 线程为 Coordinator(协调器), 执行中继日志的线程为 Worker(当前的 SQL 线程不仅起到协调器的作用, 同时也可以重放 Relay log 中主库提交的事务). 我们上面设置的线程数是 4, 从库就能看到 4 个 Coordinator(协调器)进程.
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 4 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 7 | system user | | NULL | Connect | 48 | Waiting for master to send event | NULL |
| 8 | system user | | NULL | Connect | 48 | Slave has read all relay log; waiting for more updates | NULL |
| 9 | system user | | NULL | Connect | 48 | Waiting for master to send event | NULL |
| 10 | system user | | NULL | Connect | 48 | Waiting for master to send event | NULL |
| 11 | system user | | NULL | Connect | 48 | Waiting for master to send event | NULL |
| 12 | system user | | NULL | Connect | 48 | Waiting for master to send event | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
7 rows in set (0.00 sec)
并行复制配置与调优
开启 MTS 功能后, 务必将参数 master-info-repository 设置为 TABLE , 这样性能可以有 50%~80% 的提升. 这是因为并行复制开启后对于 master.info 这个文件的更新将会大幅提升, 资源的竞争也会变大.
在 MySQL 5.7 中, 推荐将 master-info-repository 和 relay-log-info-repository 设置为 TABLE , 来减小这部分的开销.
master-info-repository = table
relay-log-info-repository = table
relay-log-recovery = ON
浙公网安备 33010602011771号