MySQL主从复制原理及实现

Posted by jintang on 2017-01-28

主从复制原理

MySQL主从复制是通过主库的二进制日志到从库上重放的方式实现的。

MySQL主从复制的工作方式

  1. 主服务器把变更修改写入二进制日志
  2. 从服务器读取主的二进制日志变更并写入relay_log中
  3. 在从服务器上重放relay_log中的日志

二进制日志格式的配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# 打开binlog功能
server_id=xxx
log_bin=mysql-bin
binlog_row_image=minimal

binlog_format=STATEMENT
# 基于段的格式
# 优点:
# 1. 日志记录量相对较小,节约磁盘及网络I/O。
# 但如果只读一条记录修改或者插入row格式所产生的日志量小于段产生的日质量
# 2. 并不强制要求主从数据库的表定义完全相同,更加灵活
#
# 缺点:
# 1. 必须记录上下文信息,保证语句在从服务器上执行结果和在主服务器上相同。
# 但是特定函数:UUID(),user()函数还是无法复制,导致主从数据不一致
# 2. 对于存储过程,触发器,自定函数进行的修改也可能造成数据不一致
# 3. 在从上执行需要更多的行锁

binlog_format=ROW (建议)
# 基于行的格式
# 特点:基于行的格式,会记录改行的增删改的日志
# 如果使用范围修改,比如修改1w条数据,基于行日志会有1w条记录分别记录每一行数据的修改
# 优点:
# 1. 使MysQL主从复制更加安全
# 2. 对于每一行数据的修改比基于段的复制更高效
# 3. 减少数据库锁的使用
# 缺点:
# 1. 记录日志量较大,可以用binlog_row_image=FULL|minimal|noblob参数来修改行日志更是的记录方式
# 2. 会增加一定的io资源的损耗(但是,一般都是顺序写入的,性能上也不会影响太大)
# 3. 要求主从数据库的表结构相同
# 4. 无法在单独执行触发器

binlog_format=MIXED (建议)
# 使用混合日志格式
# 特点:
# 根据SQL语句由系统基于段和基于行的日志格式中进行选择
# 数据量的大小由所执行的SQL语句决定

复制解决了什么问题:

  1. 实现在不同服务器上的数据分布
    • 利用二进制日志增量分批进行,不需要太多的带宽,如果我们使用基于行格式的复制进行
    • 大批量的更改时,会有一定的带宽压力。特别是在跨IDC环境下进行复制。
  2. 实现数据读取的负载均衡
    • 需要其他组件配合完成,利用DNS轮询的方式把程序的读连接到不同的备份数据库使用LVS,haproxy这样的代理方式
  3. 增强了数据安全性
    • 利用备库的备份来减少主库负载,复制并不能代替备份
  4. 实现数据库高可用和故障切换
  5. 实现数据库在线升级

配置MYSQL主从复制

基于日志点的复制配置步骤

在主DB服务器上建立复制账号

1
2
create user `repl`@`ip段` indentified by 'password';
grant replication slave on *.* to `relp`@`ip段`

主库上的配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
bin_log = mysql-bin # 用于打开二进制日志,注意用户是否有目录写权限的问题
server_id = 100 # 服务ID,必须是唯一

# 从服务器上的配置

bin_log = mysql-bin
server_id = 101
relay_log = mysql-relay-bin # 中介日志,必须固定,不然会以主机名命名
log_slave_update = on # 可选
read_only = on # 可以保证从数据不被修改,保证主从数据的一致

# 初始化从库数据

mysqldump --master-data=2 -single-transaction
# mysqldump在使用的时候会对表进行加锁,所以做热备需要注意
xtrabackup --slave-info

# 启动复制连路
change master to master_host='master_host_ip', master_user='repl', master_password='password', master_log_file='mysql_log_file_name', master_log_pos=4;

基于GTID的复制配置步骤

在主DB服务器上建立复制账号

1
2
create user `repl`@`ip段` indentified by 'password';
grant replication slave on *.* to `relp`@`ip段`

主库上的配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
bin_log = mysql-bin # 用于打开二进制日志,注意用户是否有目录写权限的问题
server_id = 100 # 服务ID,必须是唯一
gtid_mode = ON # 启用gtid模式
enforce-gtid-consistency = ON # 强制gtid一致性

# 从服务器上的配置

bin_log = mysql-bin
server_id = 101
relay_log = mysql-relay-bin # 中介日志,必须固定,不然会以主机名命名
log_slave_update = on # 可选
read_only = on # 可以保证从数据不被修改,保证主从数据的一致
enforce-gtid-consistency = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE

# 初始化从库数据

mysqldump --master-data=2 -single-transaction
# mysqldump在使用的时候会对表进行加锁,所以做热备需要注意
xtrabackup --slave-info

# 启动复制连路
change master to master_host='master_host_ip', master_user='repl', master_password='password', master_auto_position=1;

基于GTID的复制方式的优缺点

GTID的优点:

1
2
1. 可以很方便的进行故障转移
2. 从库不会丢失主库上的任何修改

缺点:

1
2
1. 故障处理比较复杂(必须通过插入空事务的方式)
2. 对执行的sql有一点的限制

选择:

1
2
3
4
5
考虑的因数:
1. 所使用的MySQL版本(5.6以上)
2. 复制架构及主从切换的方式
3. 所使用的高可用管理组件
4. 对应用的支持程度

MySQL复制性能优化

影响主从延时的因素

  1. 主库写入二进制日志的时间(控制事务大小,分割大事务)
  2. 二进制传输时间(控制日志量的大小)
  3. 从服务器只有一个SQL线程(可以使用5.6版本后的多线程复制)
    1
    2
    3
    4
    5
    6
    7
    # mysql5.7中使用按照逻辑时钟的方式来分配sql线程
    # 如何配置:

    stop slave;
    set global slave_parallel_type = 'logical_clock';
    set global slave_parallel_workers = 4;
    start slave;