美文网首页运维
MySQL replication

MySQL replication

作者: 麟之趾a | 来源:发表于2020-03-23 16:24 被阅读0次

高可用架构方案

  • 负载均衡系统
    有一定的高可用,如lvs nginx
  • 主备系统
    有高可用,但是需要切换,是单活系统。keepalive,MHA,MMM
  • 真正高可用系统
    NDB cluster,Oracle RAC,Sysbasebe cluster,Innodb cluster(MGR) 官方,PXC (percona),MGC(mariadb)

MySQL Replication(主从复制)

职责介绍

  • 搭建主从复制
  • 主从原理熟悉(重点 )
  • 主从的故障处理(重点)
  • 主从延时 (重点)
  • 主从架构演变

主从复制介绍

可以试想一下,如果我们拥有全部的binlog日志,那么我们可以把数据库恢复到任意时刻。所以,我们把binlog 实时同步到另一台数据库,另一个数据库,直接source一下同步过来的binlog,即两台数据库的内容就一样了。因为主库的binlog写到磁盘上,才能同步。所以主从复制是异步的

  • 主从复制是基于binlog实现的
  • 主库发生新的操作,都会记录binlog
  • 从库取得,主库的binlog进行回放
  • 主从复制是异步的

主从复制的前提(搭建主从复制的过程)

1 .准备两个以上数据库实例
2 .主库开启binlog
3 .主库和从库的server_id不同
4 .主库创建复制用户(从库需要连接到主库,获取binlog TCP/IP)
5 .备份主库数据,在从库恢复(mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers > /tmp/full.sql)
6 .告诉从库,主库的信息(ip user password port binlog pos),binlog pos,在主库备份文件中找
7 .启动从库复制线程start slave

主从复制搭建过程

准备多实例

 mkdir /data/mysql3307/data -p
 chown -R mysql.mysql /data/
 cp /etc/my.cnf /data/mysql3307

vim my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/mysql3307/data
user=mysql
port=3307
socket=/data/mysql3307/mysql.socket
server_id=6
log_bin=mysql-bin

mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql3307/data

 vim /etc/systemd/system/mysqld3307.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/mysql3307/my.cnf
LimitNOFILE = 5000

systemctl daemon-reload 
systemctl start mysqld3307.service 

检查配置文件

主库的二进制文件是否开启,两个节点的server_id 是否不同(建议主库的server_id要比从库小)

在主库创建复制用户

grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

追平数据

# 主库
mysqldump -uroot -p123 -S /tmp/mysql.socket -A --master-data=2 --single-transaction -R -E --triggers > /tmp/full.sql
# 从库
mysql> source /tmp/full.sql

告诉从库主库信息

# help change master to  查看 语句
# binlog 和pos 节点通过 full.sql 查看
CHANGE MASTER TO
MASTER_HOST='10.0.0.11',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000007',
MASTER_LOG_POS=1782,
MASTER_CONNECT_RETRY=10;
  • MASTER_CONNECT_RETRY: 代表当主从断开后,从库主动连接主库的次数

开启从库复制线程

start slave;

检查主从状态

show slave status\G;
SQL和IO 线程
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

重新使用一下change master

stop slave;
reset  slave all;
change master to ……
start slave;

主从复制的原理

主从复制需要的文件

  • 主库: binlog文件
  • 从库: relay.log 中继日志 , master.info 保存主库信息 , relay_log.info 保存relay log 信息

主从复制需要的线程

  • 主库: dump_thread dump_T
  • 从库: IO_thread IO_T ,SQL_Thread SQL_T

主从复制原理

image.png

主从复制监控

show slave status\G 监控主从状态

show slave status\G;
*************************** 1. row ***************************
# 和主库相关
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000007       //从库以及获取到的binlog文件
Read_Master_Log_Pos: 1782                //从库获取到binlog文件中的pos节点号


# 从库relay log 相关的信息
Relay_Log_File: mysql-relay-bin.000003            //从库以经回放的relay log文件
Relay_Log_Pos: 320                                // 从库回放relay log的pos节点号
Relay_Master_Log_File: mysql-bin.000007  //从库回放的relay log 对应的binlog 文件

#  从库运行的状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:           //IO_T的错误提示
Last_SQL_Errno: 0
Last_SQL_Error:       //SQL_T的错误提示


# 过滤复制相关的信息
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 

                
# 从库延时多少秒
Seconds_Behind_Master: 0   //这只是片面的信息,主要看主从的数据有没有同步,即主从的binlog量的计算,才是最直观的表现

# 延时从库
SQL_Delay: 0
SQL_Remaining_Delay: NULL
延时从库防止错操作产生

# GTID相关的信息
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0

主从复制故障

分析,主从复制主要依赖于主 dump_T,从 IO_T 和SQL_T 三个线程,即出现故障。也就是这三个线程的故障。而dump_T一般不会出现问题,如果出现问题,下次从库IO_T连接主库时,会重新生成一个新的dump_T

从库

IO线程故障

  • 连接问题(connecting)

连接主库,连接信息错误或变更了,防火墙问题,连接数上限了。排查思路:使用复制用户,手工mysql登录。解决:stop slave; reset slave all,change master to ...,start slave;

  • 请求binlog问题(NO)
    1 .binlog没开
    2 .binlog 损坏,不存在
    3 .reset master;主执行这条语句。解决,从库重新change master to
  • 存储binlog到relay log(很少)
    relay log 不能写入

SQL线程故障

SQL线程主要作用是对relay log的回放,如果SQL_T故障,即回放SQL语句失败

  • SQL语句执行失败例子
insert,update,delete      ----> t1 表
t1表不存在,会执行失败
create table t1             -----> t1表
t1表不存在会执行失败
# 开发在从库建立一个库,然后又在主库创建一个相同的库,导致从库sql线程回放语句失败
# 处理方法:
把握一个原则,一切以主库为准。可以先在从库drop 此库,从库 start slave; 重新同步。最稳妥的办法,重新构建主从
暴力解决方法
方法一
stop slave;
set global sql_slave_skip_counter=1
start slave
将同步指针向下一个移动,如果多次不同步,可以反复操作
方法二
vim /etc/my.cnf
slave_skip_errors = 1032,1007,1062
常见的错误码
1007:对象已经存在
1032:无法执行DML
10062:主键冲突或约束冲突
但是方法一和方法二都是有风险的,都是以从库为主。最安全的方法就是重新构建主从,把握一个原则一切以主库为准

约束冲突(例:列为非空,确插入空值。主键列插入重复值)
# 对于主键冲突问题
1 .先校对主库和从库冲突主键的数据,把从库主键类update成主库的数据
2 .stop slave, set global  sql_slave_skip=1,跳过冲突键,start slave
方法二
pt-table-check 检查表差异
pt-table-sync 同步表差异
所有语句能在主库执行成功,但在从库执行失败

SQL线程故障,大部分原因是从库被写入

  • 从库只读
    read-only 只控制普通用户
    super-read-only 控制root用户
  • 使用读写分离
    altas,mycat,proxySQL,Maxscale

主从延时监控及原因

主库方面

  • binlog 写入不及时
    解决:sync_binlog=1
  • dump_T在传统过程中,是按照事务的pos号进行传输的,是串行的。传输binlog的不是并发的。所以在事务量比较大,或者大事务前。由于dump_t传输不及时,从而导致主从延时
    解决: 必须使用GTID,使用group commit(把多个事务打包,进行提交),可支持dump_T的并行事务,必须开启双一标准,才能进行
  • 主库极其繁忙的时候
  • 慢语句过多,锁等待
  • 从库个数比较多
  • 网络延时

从库方面

在传统过程中,只有单个SQL线程,只能处理一个事务,当主库发生并发事务或大事务,传到从库的relay log中。事务的回放是串行操作,所以导致主从延时

解决: 5.6 版本(database模式)有了GTID,可以实现多线程SQL,但只是给事务加上一个标签,只能基于不同库的并发回放。相同库的事务,还是串行操作

例: create table t1;        (1)
insert t1 1 2 3             (2)
drop t1                     (3)
当从库如果并发回放以上语句,如果(3)先执行完,那么(1)和(2)就执行不了

5.7版本(MTS):有了增强的GTID,增加了seq_no(逻辑序列号),保证了事务执行的顺序,新型的并发SQL线程模式,logical_clock(逻辑时钟)。称为MTS技术

  • 主从硬件差异太大
  • 主从参数配置不一样
  • 主库和从库的索引不一样(在读写分离的架构中,从库只读,主库主修改,造成从库和主库的索引不一样,在主库进行操作导致从库锁表)
  • 主从版本不一致

监控主从延时

  • Seconds_Behind_Master: 0 但这只是片面信息,不能作为全部

主库方面监控

看从库拿的binlog是否和主库现有的binlog对的上号

# 主库
show master status;  //查看主库现在的binlog位置
# 从库
show slave status\G;
Master_Log_File:       //从库以及获取到的binlog文件
Read_Master_Log_Pos:     //从库获取到binlog文件中的pos节点号
# 对比以上从库和主库的binlog和pos号是否有差异

从库

mysql> show slave status\G;      
# 从库拿了多少binlog                     
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 1782

# 从库执行的relay log 和 pos 位置号
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 320

#  从库执行的realy log和pos号对应主库的binlog 和 pos 号
Relay_Master_Log_File: mysql-bin.000007          
Exec_Master_Log_Pos: 1782
Relay_Log_Space: 693      

------------------------------------------------------------------------------
[root@mysql data]# cat relay-log.info   # 看relay log info 也能看出 relay log 对应的binlog 和pos号
7
./mysql-relay-bin.000004
320
mysql-bin.000007
1782
0
0
1

如果出现延时,就看Exec_Master_Log_Pos执行位置的sql语句,是否是大事务。或锁表,让开发该语句

相关文章

网友评论

    本文标题:MySQL replication

    本文链接:https://www.haomeiwen.com/subject/obqbyhtx.html