运维

MySQL replication

2020-03-23  本文已影响0人  麟之趾a

高可用架构方案

MySQL Replication（主从复制）

职责介绍

主从复制介绍

可以试想一下，如果我们拥有全部的binlog日志，那么我们可以把数据库恢复到任意时刻。所以，我们把binlog 实时同步到另一台数据库，另一个数据库，直接source一下同步过来的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;

开启从库复制线程

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;

主从复制的原理

主从复制需要的文件

主从复制需要的线程

主从复制原理

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线程故障

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

SQL线程故障

SQL线程主要作用是对relay log的回放，如果SQL_T故障，即回放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线程故障，大部分原因是从库被写入

主从延时监控及原因

主库方面

从库方面

在传统过程中，只有单个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技术

监控主从延时

主库方面监控

看从库拿的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语句，是否是大事务。或锁表，让开发该语句

上一篇 下一篇

猜你喜欢

热点阅读