MySQL

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
    8
    # mysql5.7中使用按照逻辑时钟的方式来分配sql线程
    # 如何配置：

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