小菜鸟

java菜鸟号正在起航

0%

MySQL主从复制

发表于 更新于 分类于 阅读次数： Valine：
本文字数： 9.1k 阅读时长 ≈ 8 分钟

MySQL主从复制

由于数据的大量增加，访问量越来越复杂，有时候需要来部署多个数据库，通过数据库的复制策略，可以将一台mysql数据库服务器中的数据复制到其他的mysql数据库服务器之上，当各台数据库服务器上都包含相同数据的时候，前端应用通过访问mysql集群中任意一台服务器，都能够读取到相同的数据，这样，每台mysql服务器所需要承担的负载就会大大降低，从而提高整个系统的承载能力，达到系统扩展的目的

MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，另外一个或多个其它服务器充当从服务器。

Mysql的主从复制中主要有三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread）

master（binlog dump thread）主要负责Master库中有数据更新时，将更新事件写入到binlog中，并且Master会创建log dump线程通知slave库

slave（I/O thread）线程用于请求Master库，Master会返回binlog的名称以及当前数据更新的位置、binlog文件位置，并将Master库中的binlog保存在relay log中

slave（SQL thread）线程检测relay log更新，将更新的内容同步到数据库中，保证主从数据的同步

复制方式

主从复制的几种方式:

同步复制:

所谓的同步复制，意思是master的变化，必须等待slave-1,slave-2,…,slave-n完成后才能返回。这样，显然不可取，也不是MySQL复制的默认设置。比如，在WEB前端页面上，用户增加了条记录，需要等待很长时间。

工作过程
  • 当执行提交语句时，事务会被发送到slave，slave开始准备事务的提交
  • 每个slave都要准备事务，然后向master发送ok(或abort)消息，表示事务已经准备好(或无法准备该事务)
  • master等待所有的slave发送ok(或abort)消息
    • 如果master接收到所有slave的ok消息，会向所有slave发送提交消息，告诉slave提交该事务
    • 如果master收到来自任何一个slave的abort消息，就会向所有的slave发送abort消息，告知slave去中止事务
  • 每个slave等待来自master的ok(或abort)消息
    • 如果slave收到提交请求，他们就会提交事务，并向master发送事务已提交的确认
    • 如果slave收到取消请求，他们就会撤销所有改变并释放所占用的资源，从而中止事务，并向master发送事务已中止的确认
  • master接收到所有来自slave的确认后，才会报告该事务被提交(或中止)，然后处理下一个事务

异步复制:

如同AJAX请求一样。master把binlog日志发送给slave，并不会验证slave是否接收完毕

异步复制的性能提升是以牺牲一致性为代价的，当master或slave发生故障时，有可能slave并没有接收到master发送的binlog日志，会造成主从数据不一致

半同步复制:(5.5新增)

master只保证slaves中的一个操作成功，就返回，其他slave不管，半同步复制只与IO线程有直接关系，与SQL线程没有关系，从库接收到二进制日志后就会给主库回复确认信息，并不会管relaylog是否执行完。这个功能，是由google为MySQL引入的，为了解决异步复制的一致性问题

半同步复制

在事务被提交到存储引擎之后但还没有提交给slave之前，如果系统发生崩溃，由于事务是在已被提交的slave后再被确认已提交给客户端的，因此最多只会丢失一个事务

半同步复制的配置

  • master安装master 插件 install plugin rpl_semi_sync_master soname 'semisync_master.so';

  • slave安装slave插件 ``install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’;`

  • master启用该插件 在配置文件中配置

    1
    2
    3
    4
    [mysqld]
    rpl-semi-sync-master-enabled=1
    rpl-semi-sync-master-timeout=200 # 防止半同步复制在没有收到确认的情况下发生堵塞，可以设置一个超时时间，超过该时间将降级为异步复制模式，如果主库再次探测到从库恢复了，会自动回到半同步复制模式，单位毫秒
    rpl-semi-sync-master-wait-no-slave=off  # 如果一个事务被提交，但是master没有任何slave连接，这时master不可能将事务发送到slave，默认情况下master会在时间限制范围内继续等待slave的连接，并确认该事务已被正确写入到磁盘上，可以使用该选项来关闭这种行为，如果没有slave连接，则恢复到异步复制

  • slave启用该插件 在配置文件中配置

    1
    2
    [mysqld]
    rpl-semi-sync-slave-enabled=1

半同步相关的变量

1
2
-- 查看状态变量
show status like '%semi%'
  • rpl_semi_sync_master_clients 展示支持和注册半同步复制的已连接的slave数量
  • rpl_semi_sync_master_status master的半同步复制状态，1是活动状态，0是非活动状态
  • rpl_semi_sync_slave_status slave的半同步复制状态，1是活动状态，0是非活动状态
  • rpl_semi_sync_master_yes_tx 显示从服务器确认的成功提交数量
  • rpl_semi_sync_master_no_tx 显示从服务器确认的不成功提交数量

基本原理

MySQL复制是基于主服务器在二进制日志中跟踪所有对数据库的更改。因此，要进行复制，必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器已经记录到日志中的数据。

当一个从服务器连接主服务器时，它通知主服务器最后一次成功更新的位置。从服务器接收那时起发生的任何更新，保存在中继日志(relay log)中，重做中继日志的的更新时间。然后封锁并等待主服务器通知新的更新。从服务器执行备份并不干扰主服务器，灾备份过程中主服务器可以继续处理更新。

主从复制的数据完整性依赖于主库的binlog，只要主库的binlog不丢失，数据就不会丢失

mysqlbinlog可以访问到主库的binlog

过程

1
2
3
4
5
-- 查看binlog刷新到磁盘的频率，默认是1
-- sync_binlog为1，表示事务提交之前，将binlog刷新到磁盘，这样即使主机突然宕机，也只会损失prepared状态的事务，尽可能的保证了数据安全
-- sync_binlog为0，表示MySQL不控制binlog的刷新，由文件系统自己控制文件缓存的刷新
-- sync_binlog不为0和1时，表示n次binlog提交组之后才会刷新到磁盘
show variables like 'sync_binlog';
  • master将改变记录到bin log中，这些记录过程叫做二进制日志事件，binary log events
  • 事件写入binlog之后，master的转储线程(dump thread)从binlog中读取事件，将它们发送给slave的IO线程
  • slave的io线程接收到事件之后写入到它的中继日志(relay log)
  • slave的sql执行线程执行中继日志中的sql，将改变应用到自己的数据库中，MySQL的复制是异步且串行化的

由于复制过程是异步的，因此，master和slave之间的数据有可能存在延迟的现象，此时只能够保证数据最终的一致性

复制过程

用到的命令

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
-- 查看复制状态信息，展示连接到master的slave信息，包括通过中继连接到master的slave
show slave hosts;
--表字段 Server_id(连接的slave服务器id)     Host(参数指定的主机名)    Port(参数指定的端口号)    Master_id(slave复制的哪台master的数据)    Slave_UUID

-- 查看binlog目前有多少文件
show master logs;
-- 下一次事件即将写入binlog的位置
show master status;
-- File  当前binlog文件的名称
-- Position 二进制日志的当前位置
-- Binlog_Do_DB  binlog-do-db选项指定的数据库
-- Binlog_Ignore_DB binlog-ingore-db 选项指定的数据库

-- 查看slave线程的状态
show slave status;
-- 重要字段有
-- Slave_IO_State 当前运行的IO线程状态 
-- Master_Log_File、Read_Master_Log_Pos master的binlog读取位置
-- Relay_Log_File、Relay_Log_Pos 从服务器中继日志位置
-- Relay_Master_Log_File、Exec_Master_Log_Pos master的执行位置，如果从服务器有延迟的话，Exec_Master_Log_Pos的值可能会小于Read_Master_Log_Pos的值
-- Slave_SQL_Running 表示slave的sql线程是否在运行
-- Slave_IO_Running 表示slave的IO线程是否在运行
-- Last_IO_Errno、Last_IO_Error  显示IO线程最近的错误
-- Last_SQL_Errno、Last_SQL_Error  显示SQL线程最近的错误
-- Connect_Retry 两次重连的时间间隔
-- Relay_Log_Space  所有中继日志文件的总大小
-- Seconds_Behind_Master  事件执行和事件写入Master二进制日志之间的间隔时间，从服务器落后主服务器的时间，指的是从服务器上最后执行的事件和中继日志中复制过来的主服务器二进制日志的最后一个事件之间相隔的秒数

master的状态变量

1
2
3
4
-- 显示show master status命令执行的次数
show status like 'com_show_master_status'
-- 显示change master命令执行的次数
show status like 'com_change_master'

slave的状态变量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- show slave status命令执行的次数
show status like 'com_show_slave_status'
-- show slave hosts命令执行的次数
show status like 'com_show_slave_hosts'
-- slave start命令执行的次数
show status like 'com_slave_start'
-- slave stop命令执行的次数
show status like 'com_slave_stop'
-- 当前master的心跳检测的间隔时间的配置信息
show status like 'slave_heartbeat_period'
-- slave的sql线程使用临时表的数量，如果该值过高，说明slave负载过重
show status like 'slave_open_temp_tables'
-- 从master回复的心跳数，该值应该与心跳检测间隔时间一致，因为slave重新启动会在心跳间隔时断开
show status like 'slave_received_heartbeats'
-- slave启动后sql线程重试事务的次数
show status like 'slave_retried_transactions'
-- 如果slave连接到master，而且io线程和sql线程无错误地执行，则该值为on
show status like 'slave_running'

主从一致性检验工具

checksum、mysqldiff、pt-table-checksum等

主从架构

一主一从、一主多从、双主、级联同步、环形多主

主从架构

一主一从配置

如果读多写少的情况下也可以配置为一主多从

主MySQL

修改my.cnf,在 [mysqld]模块下面添加以下几行

1
2
3
4
5
server-id = 11      //主服务器编号，id要唯一
log-bin=master_bin  //指定二进制日志
log-bin-index=master_bin.index  // 二进制索引文件，保存了binlog文件的列表，默认是与binlog文件的基本名一致
binlog-do-db=exam_admin  //需要同步的二进制库名
binlog-ignore-db=mysql  //不同步的二进制库名，与binlog-do-db互斥
1
2
3
4
5
6
7
-- 为所有从服务器授权所有数据库
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.80.%' IDENTIFIED BY '123456'; 
-- 刷新权限
FLUSH PRIVILEGES; 
-- 要记下 Position 列的值 
show master status;

1
2
-- 删除所有的二进制文件并清空二进制索引文件，执行该命令前要确保当前没有slave连接该master
reset master;

从MySQL

修改my.cnf,在 [mysqld]模块下面添加以下几行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
server-id = 22      //从服务器编号
read_only=1  //只读
relay_log=relay-log-bin //指定从服务器的中继日志
relay_log_index=slave-relay-bin.index   //定义中继日志索引文件
// 数据库级别的复制操作
replication-do-db=exam_admin  // 指定slave数据库上哪些数据库可以从中继日志读取然后执行
replication-ignore-db=mysql // 指定slave数据库上哪些数据库的事件被忽略
// slave除了数据库级别的复制操作还可以进行表级别的复制操作
replication-do-table=user 
replication-ignore-table=test
// 表级操作还支持通配符
replication-wild-do-table=t% 
replication-wild-ignore-table=d%

// 防止更新不及时，SLAVE 重启后导致的主从复制出错
master_info_repository=TABLE
relay_log_info_repository=TABLE
skip_slave_start //阻止备库在崩溃后自动启动复制
1
2
3
4
5
6
7
-- 指定主服务器  master_log_pos是在show master status;时的position
change master to master_host='192.168.80.100',master_user='mysyslave',master_password='123456',master_log_file='master_bin.000001',master_log_pos=100; 

-- 开启从服务器
start slave;    
-- 要保证slave_IO_Running和slave_SQL_Running都是yes，主从配置成功
show slave status \G

如果要停止主从复制

1
stop slave;

如果要重新配置主从

1
2
3
stop slave;
-- 删除了slave复制所用的所有文件，重新开始，执行该命令前要确保已经执行了stop slave;保证slave上没有活动的复制
reset master;

双主配置

由于一主一从存在单点问题，如果主挂掉会导致无法写入

双主有两种不同的配置方法

  • active-active 该配置方法，操作同时写入两个服务器，然后将更改转移到其他master

    存在的问题：

    • 如果同样的信息在两个master上都被更新，这两个更新之间会有冲突，并很可能导致复制停止
    • 当两个master不一致时，如果系统发生崩溃，一些交互将会丢失

  • active-assive 该配置方法，其中一个Master处理写入，称为主动master；另一个服务器称为被动master，只是和主动master保持一致

    存在的问题

    • 由于网络原因，导致主动master无法连接，此时被动master主动提升为主动master，但是随后，原来的主动master又再次连接成功，此时两台服务器都作为主动master，此时的更改在两台master上都被执行，就有可能发生冲突

master1

1
2
3
4
5
6
7
8
9
10
11
12
server-id = 11      //主服务器编号，id要唯一
log-bin=master_bin  //指定二进制日志
log_slave_updates=1      //允许备库将重放的事件也记录到自身的bin-log中
binlog-do-db=exam_admin  //需要同步的二进制库名
binlog-ignore-db=mysql  //不同步的二进制库名，与binlog-do-db互斥

# 作为从数据库时，有写入操作也需要进行二进制文件的修改
log-slave-updates
# 表示自增长字段每次递增的量
auto-increment-increment=2
# 自增长字段的起始值，两个主机的起始值配置不同
auto-increment-offset=1

master2

1
2
3
4
5
6
7
8
9
10
11
12
server-id = 12      //主服务器编号，id要唯一
log-bin=master_bin  //指定二进制日志
log_slave_updates=1      //允许备库将重放的事件也记录到自身的bin-log中
binlog-do-db=exam_admin  //需要同步的二进制库名
binlog-ignore-db=mysql  //不同步的二进制库名，与binlog-do-db互斥

# 作为从数据库时，有写入操作也需要进行二进制文件的修改
log-slave-updates
# 表示自增长字段每次递增的量
auto-increment-increment=2
# 自增长字段的起始值，两个主机的起始值配置不同
auto-increment-offset=2

两个主机之间需要相互复制

1
2
3
4
5
6
7
8
-- 两个mysql服务器都需要执行
-- 指定主服务器  master_log_pos是在show master status;时的position
change master to master_host='192.168.80.100',master_user='mysyslave',master_password='123456',master_log_file='master_bin.000001',master_log_pos=100; 

-- 开启从服务器
start slave;    
-- 要保证slave_IO_Running和slave_SQL_Running都是yes，主从配置成功
show slave status \G

为了防止两台MySQL之间循环复制，会在MySQL的binlog中记录当前MySQL的server-id，且两台机器的server-id参数不同，MySQL就可以判断出哪个变更是哪一个MySQL Server产生的，从而避免了循环复制的情况

为了可以更好的增加机器，id自己生成，不再使用自增id，否则在配置auto-increment-increment和auto-increment-offset时每次添加机器都要修改

级联复制

有时候在一些应用场景中，会存在读写压力差别比较大，读压力特别大，一个Master可能需要多台Slave才能支撑读的压力，但是一台Master给多台slave复制会消耗较多的资源，很容易造成复制的延时。

此时可以先有几台MySQL从master来进行复制，这几台MySQL称为第一级slave集群，也可以成为中继slave，然后其他slave再从第一级slave集群中进行复制，这些slave称为第二级slave集群

中继slave

中继slave需要binlog来保存所有改变，因为中继slave需要将改变传递给其他slave，但是中继slave又不需要应用这些改变，因为它并不响应任何查询

所以中继slave中的所有表的存储引擎改为BLACKHOLE，该存储引擎是黑洞引擎，写入的任何数据都会消失，专门用于记录binlog做复制的中继存储

相关文章

欢迎关注我的其它发布渠道