mysql主从原理(异步复制)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql主从原理(异步复制),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含12688字,纯文字阅读大概需要19分钟。
内容图文
主从复制作用:数据分布(异机或异地)---负载均衡----备份----高可用和故障转移----升级测试.
主从原理:
|
线程介绍: | 主服务器的一个工作线程:
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点开始主从. | 在从库执行下面语句:
Stop
slave ; ---先停止slave:
reset
slave all; ---Reset
slave 其实是把master.info 和relay-log.info文件删除掉,但是同步信息还存在,因此有人如果再次开启start slave,又会重新开启同步了。故用reset slave
all.
reset
master; insert into ....
------之前的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文件不能丢.
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;)
根据第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;
参数修改完后在数据库里执行上面语句.
开启主从:Start slave;
查看主从状态:
show slave
status\G;
查看主库拥有的从库的个数: 在主库执行 mysql>
show slave hosts;
|
stop slave; | set global
sql_slave_skip_counter=1;
start slave;
|
主库加锁:主库加读锁,防止主库有新数据写入,主要是针对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(写集)的复制模式(表里面必须有主键或者唯一索引)
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越多,则并行复制效率越高,故从库的并发复制能力受制于主库的并发能力.主库的并发越高,从库的并发复制能力一般越高,性能越好.
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.低版本的串行复制导致:
当主库的TPS并发较高时,产生的DDL数量超过slave一个sql线程所能承受的范围,那么延时就产生了,当然还有就是可能与slave的大型query语句产生了锁等待。故需尽量避免在主库频繁的执行大事务(可以将大事务拆成小事务执行)
2.主库压力太大.
3.主从走公网,带宽流量不够,造成了延迟.
首要原因:数据库在业务上读写压力太大,CPU计算负荷大,网卡负荷大,硬盘随机IO太高
次要原因:读写binlog带来的性能影响,网络传输延迟。
硬件方面考虑:
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支持表之间的同步复制.
从库宕机后,因为主从报错,导致从库无法启动,
解决方法:启动的加一个参数--skip-slave-start
mysqld_safe
--skip-slave-start --user=mysql &
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线程接收一个个的event ,将接收到的event,通过设置参数master_info_repository 可以将 master-info 信息(IO线程接收到的位置,Master_log_name</FONT
本文系统来源:https://www.cnblogs.com/lonuve/p/11015886.html
内容总结
以上是互联网集市为您收集整理的mysql主从原理(异步复制)全部内容,希望文章能够帮你解决mysql主从原理(异步复制)所遇到的程序开发问题。
如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
来源:【匿名】