mysql主从原理（异步复制）_猎魔君王

http://blog.sina.com.cn/u/5557809942

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

mysql主从原理（异步复制）

(2019-01-21 17:28:51)

分类： mysql主从及监控

主从复制作用:数据分布（异机或异地）---负载均衡----备份----高可用和故障转移----升级测试.

主从原理：

线程介绍：

主服务器的一个工作线程:

DUMP线程,作用：接收到从库发来的请求后，负责给slave服务器发送二进制日志

从服务器的两个工作线程:

I/O线程:

作用：负责读取主服务器的二进制日志，并将其保存到自己的中继日志文件中。

SQL线程:

作用：来复制执行中继日志。

注意从库的IO线程和SQL线程是分开的,互不影响.

主从同步过程：

第1步: slave发送请求:(从库IO线程负责)

在slave节点start slave后，会向master进行连接，并且请求从binlog的指定位置开始同步。

(从库的IO线程负责请求,请求中包括主库的账号/密码/端口/binlog-file+pos点等)

第2步:Master接受请求并发送数据:(主库DUMP线程负责)

master收到slave的IO线程的同步请求后,master的DUMP线程开始根据请求信息读取指定日志位置的日志信息，返回给slave端的io线程，这个信息包括了日志内容，binlog的文件名称和位置。

第3步:slave写入relay log:(由从库IO线程负责)

slave的IO线程接收到master的消息后，开始将这个消息写入到relay_log的末尾，同时把master的位置信息写入到master.info中，下次同步会读取master.info的位置信息。

第4步:Slave执行解析后的sql(由从库SQL线程负责)

slave的sql线程检测到relay log中新增的内容后，会进行相关的日志解析，生成对应的语句，并且在slave执行这些语句。同时把回放位置写入到Relay_log_info中.

最终的实质是，在slave端执行了和master同样的sql语句

IO线程作用:连接主库,并且接收主库发送的数据,并且写入到从库relay_log.

SQL线程作用: 负责回放relay_log

主从参数介绍：

主库参数（修改binlog需要重新启动数据库）:

binlog-do-db = # 需要复制的库

binlog-ignore-db = # 需要被忽略的库

max_binlog_size = 2048M # 默认为1024M

binlog_format = ROW # 必须为ROW

transaction-isolation = READ-COMMITTED ---事务隔离级别为RC

expire_logs_days = 7 # binlog保留多少天，看公司计划安排

server-id = 111 # 必须和所有从机不一样，且从机之间也不一样

binlog_cache_size = # binlog 缓存的大小，设置时要当心

sync_binlog = 1 # 必须设置为1，默认为0

innodb_flush_log_at_trx_commit = 1 # 提交事物的时候刷新日志

innodb_support_xa = 1

从库参数:

log_slave_updates # 将SQL线程回放的数据写入到从机的binlog中去(用于级联复制)

replicate-do-db = # 需要复制的库

replicate-ignore-db = # 需要忽略的库

replicate-do-table = # 需要复制的表

replicate-ignore-table = 需要忽略的表

server-id = 112 # 必须在一个复制集群环境中全局唯一,可通过看auto.cnf查看.

relay-log-recover = 1 # I/O thread crash safe – IO线程安全

relay_log_info_repository = TABLE # SQL thread crash safe – SQL线程安全

Skip-slave-start=1 当slave数据库启动的时候，不会自动开启复制.

master info 主要记录IO线程同步主库的数据的位置,以便于在重启主从的时候,可以从上次同步的位置开始同步.通过参数查看:

show variables like '%master_info_re%';

Relay_log_info主要记录SQL线程回放relay_log位置的信息.

show variables like '%relay_log_info%';

查看主库拥有的从库的个数: 在主库执行 mysql> show slave hosts;

可以有多个从库,但从库之间的uuid不能一致,若一致,可以修改配置文件中的service_id,删除数据文件中的auto.cnf,再重新开启数据库.

read_only=1 限制,就是read_only=1对有super不起作用,

super_read_only 对所有用户起作用

搭建主从后,开启read_only,保证从库其他用户不写入数据

主从全新搭建过程：

若都是空库,则直接配置主从.若主库在运行,则先将主库mysqldump导出,再导入到从库,最后配置主从复制.

第1步:主库创建用户并授权.

reset master;

grant REPLICATION CLIENT,REPLICATION SLAVE on *.* to rep@'192.168.%' identified by 'rep';

flush privileges;

然后查看主库binlog位置和pos点: show master status;

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化.

第2步:从库配置slave.

change master to master_host='192.168.88.2',master_user='rep',master_password='rep', master_log_file='mysql-bin.000002',master_log_pos=154,MASTER_PORT=3307;

其中master_log_file和master_log_pos是主库show master status的结果.

第3步:开启和查看slave.

开启主从复制: start slave;

查看主从状态: show slave status\G;

第4步:主库关闭读锁:

unlock table;

主从状态（show slave status\G;）字段含义：

主从状态字段含义

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.60.22

Master_User: rep

Master_Port: 3306

Connect_Retry: 60 ----尝试重连的时间间隔(单位s)

Master_Log_File: mysql-bin.000006 ----当前主服务器的binlog日志

Read_Master_Log_Pos: 1173 -----当前主服务器的事务pos点.

Relay_Log_File: hadoop02-relay-bin.000002 ---当前中继日志

Relay_Log_Pos: 1336 ------中继日志的写入位置.

Relay_Master_Log_File: mysql-bin.000006 ---当前同步主库的binlog日志

Slave_IO_Running: Yes-----------------此状态必须YES

Slave_SQL_Running: Yes-----------------此状态必须YES

Replicate_Do_DB: ---过滤器,同步哪个数据库.

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 1173----当前中继日志的回放位置.

Relay_Log_Space: 1512

Seconds_Behind_Master: 0 -----是否延迟.(Read_Master_Log_Pos-Exec_Master_Log_Pos)

主从参数说明：

Slave_IO_state:显示当前IO线程的状态，一般情况下就是显示等待主服务器发送二进制日志。

Master_log_file:显示当前同步的主服务器的binlog日志。

Read_master_log_pos:显示主服务器上binlog的pos点。

上面两个参数决定了目前主库的binlog的位置.

Relay_master_log_file:当前中继日志同步的主库的哪个binlog日志。

Relay_log_file:显示当前写入的中继日志。

Relay_log_pos:显示当前写入到中继日志的pos点。

上面两个参数决定了中继日志的写入位置.

Slave_IO_running:从服务器中IO线程的运行状态，yes代表正常

Slave_SQL_running:从服务器中sql线程的运行状态，YES代表正常

上面两参数决定了主从是否正常

Exec_Master_log_pos:表示从库SQL线程应用中继日志的pos点。可Read_master_log_pos相比较,若相等,则表示无延迟.

Seconds_Behind_Master:为0说明主从无延迟,为null则可能有问题,在增大则说明延迟在增加.

在线搭建主从：

可能主库的binlog已丢掉,故需先在线导出主库,导入到从库,再根据导出的binlog的pos点开始主从.

1.清除之前的主从信息.

在从库执行下面语句:

Stop slave ; ---先停止slave:

reset slave all; ---Reset slave 其实是把master.info 和relay-log.info文件删除掉，但是同步信息还存在，因此有人如果再次开启start slave，又会重新开启同步了。故用reset slave all.

2.清除当前主库的binlog文件,并做修改

reset master; insert into .... ------之前的binlog文件丢了.

3.在线数据导出导入.(这步及以后的binlog文件不能丢.)

第1步:非sql执行导出主库: mysqldump -uroot -p -S /tmp/mysql3307.sock --master-data=2 --single-transaction -A > /tmp/all.sql

第2步:在从库导入: sql里执行 source /tmp/all.sql.

导完后再对主库做一些插入修改操作,改变binlog的pos点,但导出文件时及之后的binlog文件不能丢.

4.查找导出数据时的binlog文件和pos点.

Vim /tmp/all.sql 在第22行左右有:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=422;

说明binlog文件是bin.000001,而pos点是422.

而因主库一直在做变动,故此时查看主库会发现pos点并不是422.（show master status;）

5.做主从.

根据第4步结果修改:master_log_file和master_log_pos参数.

change master to master_host='192.168.88.2',master_user='rep',master_password='rep', master_log_file='mysql-bin.000001',master_log_pos=422,MASTER_PORT=3307;

参数修改完后在数据库里执行上面语句.

6.开启并查看主从.

开启主从:Start slave; 查看主从状态: show slave status\G;

查看主库拥有的从库的个数: 在主库执行 mysql> show slave hosts;

主从有问题跳一步语句

stop slave;

set global sql_slave_skip_counter=1;

start slave;

myisam搭建主从

主库加锁:主库加读锁，防止主库有新数据写入，主要是针对MySIAM表，但是这个锁加入后，业务无法正常操作，这个还是要权衡数据库备份数据要求和业务的要求的。

flush tables with read lock;

主库数据备份:必用参数

--single-transaction 启动单一事务备份，这样保证备份是某个时间点的数据，而不会造成数据的不一致

—master-data 这个参数会在备份文件中，记录备份时的binlog的pos位置，用于从库恢复数据后的主从搭建

/usr/local/mysql/bin/mysqldump -uroot -pLR9fjxm3g -S /tmp/mysql3307.sock -A --single-transaction --master-data=2 >/tmp/20160304alls.sql

传输到从库: 将备份文件scp或者ftp到从库使用

scp /tmp/20160303all2.sql root@192.168.88.3:/tmp/

从库恢复:在从库恢复主库备份过得数据文件

mysql -uroot -p -S /tmp/mysql3307.sock

或者进入数据库后使用source命令（建议使用source命令，因为source命令会打印结果）

查找主库pos:

使用head命令查找前35行备份文件，然后找到描述master 的binlog的位置

head -n 35 /tmp/20160303all2.sql

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=88940;

从库进行指定:

change master to master_host='192.168.95.100',master_user='rep',master_password='rep', master_log_file='mysql-bin.000007',master_log_pos=88940,MASTER_PORT=3307;

开启:start slave

进行start slave后，从库会有一个追逐主库的过程，在追完主库后，可以进行业务的迁移或者作为从库使用.

并行复制原理及版本变迁的主从变化

1.主从延迟发展:

5.5版本单线程复制

5.6版本并行复制,只实现库级别的并行复制,业务,不会跨库访问

但大多数操作都是单库多表,而不是多库多表,意义不大.

5.7 推出表级别并行复制,可以实现表级别的并行复制

8.0 推出了基于writeset(写集)的复制模式(表里面必须有主键或者唯一索引)

2.mysql5.7并行复制原理

Binlog内容增加了,增加了是否可并行复制的记录内容:

较之原来的二进制日志内容多了last_committed和sequence_number，last_committed表示事务提交的时候，上次事务提交的编号，如果事务具有相同的last_committed，表示这些事务都在一组内，可以进行并行的回放。例如上述last_committed为0的事务有6个，表示

组提交时提交了6个事务，而这6个事务在从机是可以进行并行回放的。

多线程的思路就是把sql_thread 变成分发线程，然后由一组worker_thread来负责执行

Sql_thread的任务是分发线程,worker_thread线程的作用是回放中继日志.

5.7的表级别并行复制基于last_committed,相同的last_committed越多,则并行复制效率越高,故从库的并发复制能力受制于主库的并发能力.主库的并发越高,从库的并发复制能力一般越高,性能越好.

3.并行复制参数介绍

MySQL 5.7基于表的并行复制

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=16

master_info_repository=TABLE

relay_log_info_repository=TABLE

slave_preserve_commit_order=1

Slave上commit的顺序保持一致，必须为1，否则可能会有GAP锁产生

参数介绍:

relay_log_info_repository和master_info_repository在5.7设置为保存到TABLE比较合适

slave-parallel-type:

5.7为了兼容5.6版本的库复制，增加了slave-parallel-type=LOGICAL_CLOCK参数

5.6库级复制参数值为DATABASE 5.7表级别复制参数值为LOGICAL_CLOCK

slave-parallel-workers :从库并发数目，设置大于4比较合适

在线调整:

mysql> set global slave_parallel_workers=8;

mysql> stop slave; -- 一定要重启一下slave才能有效

mysql> start slave;

故主库的并发越高,从库的并发复制能力一般越高,性能越好.

主从延迟的判定

1.show slave status显示参数Seconds_Behind_Master不为0，这个数值可能会很大,

2.Read_Master_Log_Pos和Exec_Master_Log_Pos显示bin-log的编号相差很大，说明bin-log在从库上没有及时同步，所以近期执行的bin-log和当前IO线程所读的bin-log相差很大

3.、MySQL的从库数据目录下存在大量mysql-relay-log日志，该日志同步完成之后就会被系统自动删除，存在大量日志，说明主从同步延迟很厉害.

主从产生延迟的原因和解决方法

1.产生延迟原因:

1.低版本的串行复制导致:

当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。故需尽量避免在主库频繁的执行大事务(可以将大事务拆成小事务执行)

2.主库压力太大.

3.主从走公网,带宽流量不够,造成了延迟.

首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高

次要原因：读写binlog带来的性能影响，网络传输延迟。

2.解决办法:

硬件方面考虑:

1.更换从库更快的硬盘

2.网络，网卡，更换带宽更大的网卡

mysql考虑 :从库作为备份数据库来说

3.增加从库的innodb_buffer_pool_size,可以缓存更多数据防止由于转换造成的IO压力.

4.增加innodb_log_file_size和innodb_log_files_in_group，减少buffer落盘.

5.修改参数innodb_flush_method，提高写入性能（SSD强烈推荐使用）

6.从库binlog关闭（如果可以）, log_slave_updates关闭.

7.修改innodb_flush_log_at_trx_commit和sync_binlog 为0或者2

8.修改master_info_repository和relay_log_info_repository为TABLE，防止直接落盘压力

9.升级5.7.因为:mysql5.5只支持一个线程复制. mysql5.6 支持库之间的同步复制. mysql5.7支持表之间的同步复制.

3.宕机导致从机无法启动的解决方法

从库宕机后,因为主从报错,导致从库无法启动,

解决方法:启动的加一个参数--skip-slave-start

mysqld_safe --skip-slave-start --user=mysql &

4.宕机丢数据的情况

mysql主库异常宕机情况下，如果未设置sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能出现binlog或者relaylog文件出现损坏，导致主从不一致。

1. redo的trx_prepare未写入，但binlog写入，造成从库数据量比主库多。

2. redo的trx_prepare与commit都写入了，但是binlog未写入，造成从库数据量比主库少。

很显然，如果我们弱化配置的持久性( innodb_flush_log_at_trx_commit != 1 或者sync_binlog != 1 )，宕机可能导致两种丢数据的场景：

引擎层提交了，但binlog没写入，备库丢事务；

引擎层没有prepare，但binlog写入了，主库丢事务。

即使我们将参数设置成innodb_flush_log_at_trx_commit =1 和 sync_binlog = 1，也还会面临这样一种情况：主库crash时还有binlog没传递到备库，如果我们直接提升备库为主库，同样会导致主备不一致，老主库必须根据新主库重做，才能恢复到一致的状态。

一般主从问题总结

IO线程出问题:

1.主库的binlog问题比如找不到主库的binlog

2.主库的权限不对,IO connectiong...

3.relay log写入可能出问题,具体出问题的位置,看报错

SQL线程出问题:

1.主键冲突

2.从库少数据

3.主从的数据的格式不对(int char vvarchar)

ERROR 1062 :从库插入数据，发生唯一性冲突

ERROR 1032:从库找不到要删除的数据

ERROR 1452:无法在外键的表中插入或者更新参考主键没有的数据

主从一致问题原理(IO|SQL线程高可靠性)

如何保证主从数据一致性:保证IO线程高可靠性和SQL线程高可靠性.

1.IO线程高可靠性:

IO线程接收一个个的event ，将接收到的event，通过设置参数master_info_repository 可以将 master-info 信息(IO线程接收到的位置，Master_log_name和Master_log_pos )写入到文件( FILE )或者数据库( TABLE )中。然后将接收到的event写入relay log file

参数 sync_master_info=10000 表示接收10000个event，写一次master-info

存在问题： master-info.log 和 relay-log 无法保证一致性。

relay-log-recover = 1:该参数表示当前接收到的relay-log全部删除，然后从SQL线程回放到的位置重新拉取(SQL线程通过配置后是可靠的).即不再从master_info_repository里查找,而是从SQL线程回放的位置重新开始执行.

2.SQL线程高可靠性:

relay_log_info_repository=TABLE

将event的回放和relay-info的更新放在同一个事物里面，变成原操作，从而保证一致性(要么都写入，要么都不写)。每一次事物提交，都会写入mysql.slave_relay_log_info中，sync_relay_log_info=N将被忽略(原本sync_relay_log_info是设置回放多少次才落盘relay_log_info相应文件中.),回放的具体pos点必定就是relay_log_info中的值了.相当于sync_relay_log_info=1的情况.

三个参数可以保证复制的安全性:

relay-log-recover = 1 ----保证IO安全性

relay_log_info_repository = TABLE ----保证SQL线程安装性

master_info_repository = TABLE ----提高性能

master_info_repository设置为TABLE或者FILE对复制的可靠性是没有帮助的，因为设置relay-log-recover = 1后，会重新通过SQL线程回放到的位置进行拉取。但是 master_info_repository 也一定要设置为 TABLE ，性能上比设置为 FILE 有很高的提升(官方BUG) , 设置为 TABLE 后， master-info将信息保存到 mysql.slave_master_info 中.

总结:通过relay_log_info_repository=table保证了SQL线程回放点的准确性,再让IO线程依赖SQL线程的准确回放点开始写入中继日志.保证了中继日志的准确性.

主从跨版本搭建数据库需要注意的情况:

1. 主库的MySQL版本应该低于从库的MySQL版本

2. mariadb和mysql的binlog格式不一样，不能进行主从的搭建

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：mysqldump导出导入时长对比

后一篇：mysql主从(半同步及增强半同步)

新浪BLOG意见反馈留言板　欢迎批评指正