主从复制

企业高可用性标准

全年无故障率(非计划内故障停机)

99.9% ----> 0.001*365*24*60=525.6 min
99.99% ----> 0.0001*365*24*60=52.56 min
99.999% ----> 0.0001*365*24*60=5.256 min

高可用架构方案

负载均衡:有一定的高可用性 
LVS  Nginx
主备系统:有高可用性,但是需要切换,是单活的架构
KA ,   MHA, MMM
真正高可用(多活系统): 
NDB Cluster  Oracle RAC  Sysbase cluster   , InnoDB Cluster（MGR）,PXC , MGC

MySQL Replication(主从复制)

主从复制介绍

(1) 主从复制基于binlog来实现的
(2) 主库发生新的操作,都会记录binlog
(3) 从库取得主库的binlog进行回放
(4) 主从复制的过程是异步

主从复制的前提

(1) 2个或以上的数据库实例
(2) 主库需要开启二进制日志
(3) server_id要不同,区分不同的节点
(4) 主库需要建立专用的复制用户 (replication slave)
(5) 从库应该通过备份主库,恢复的方法进行"补课"
(6) 人为告诉从库一些复制信息(ip port user pass,二进制日志起点)
(7) 从库应该开启专门的复制线程

主从复制搭建过程

准备多实例

pkill mysqld
systemctl start mysqld3307
\rm -rf /data/3308/data/*
\rm -rf /data/3308/mysql-bin.*
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3308/data
systemctl start mysqld3308
mysql -S /data/3308/mysql.sock -e "select @@port"
mysql -S /data/3307/mysql.sock -e "select @@port";

检查配置文件

主库: 二进制日志是否开启
两个节点: server_id

[root@db01 data]# cat /data/3308/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin

[root@db01 data]# cat /data/3307/my.cnf 
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
[root@db01 data]#

主库（3307）创建复制用户

mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123'"

主库的数据备份到从库　　

主库备份数据

mysqldump -S /data/3307/mysql.sock -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql

从库导入主库的数据

[root@db01 ~]# mysql -S /data/3308/mysql.sock 
mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql

告知从库关键复制信息

[root@db01 ~]# mysql -S /data/3308/mysql.sock
# 查看帮助信息
help change master to
修改后执行以下语句
CHANGE MASTER TO 
MASTER_HOST='10.0.0.200',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=1305,
MASTER_CONNECT_RETRY=10;

从库开启复制线程(IO,SQL)

[root@db01 ~]# mysql -S /data/3308/mysql.sock 
mysql> start slave;

检查主从复制状态

[root@db01 ~]# mysql -S /data/3308/mysql.sock 
mysql> show slave status \G

主库创建新数据库alexsb

mysql -S /data/3307/mysql.sock -e "create database alexsb"

从库查看是否存在alexsb数据库　　

mysql -S /data/3308/mysql.sock -e "show databases"

主从复制原理

主从复制过程：

1.从库执行change master to 命令(主库的连接信息+复制的起点)
2.从库会将以上信息,记录到master.info文件
3.从库执行 start slave 命令,立即开启IO_T和SQL_T
4. 从库 IO_T,读取master.info文件中的信息
获取到IP,PORT,User,Pass,binlog的位置信息
5. 从库IO_T请求连接主库,主库专门提供一个DUMP_T,负责和IO_T交互
6. IO_T根据binlog的位置信息(mysql-bin.000004 , 444),请求主库新的binlog
7. 主库通过DUMP_T将最新的binlog,通过网络TP给从库的IO_T
8. IO_T接收到新的binlog日志,存储到TCP/IP缓存,立即返回ACK给主库,并更新master.info
9.IO_T将TCP/IP缓存中数据,转储到磁盘relaylog中.
10. SQL_T读取relay.info中的信息,获取到上次已经应用过的relaylog的位置信息
11. SQL_T会按照上次的位置点回放最新的relaylog,再次更新relay.info信息
12. 从库会自动purge应用过relay进行定期清理

补充说明:

一旦主从复制构建成功,主库当中发生了新的变化,都会通过dump_T发送信号给IO_T,增强了主从复制的实时性.

主从复制监控

命令（从库中运行）

mysql> show slave status \G

输出内容以下

主库有关的信息(master.info):
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
*******************************
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 609
*******************************

从库relay应用信息有关的(relay.info):
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000004

从库线程运行状态(排错)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 			
			
过滤复制有关的信息:			
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
 
从库延时主库的时间(秒):  
Seconds_Behind_Master: 0
				
延时从库:
SQL_Delay: 0
SQL_Remaining_Delay: NULL

GTID复制有关的状态信息		  
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0

主从复制故障分析

IO 线程故障

（1）连接不上主库

网络,连接信息错误或变更了,防火墙,连接数上线

排查思路

1. 使用复制用户手工登录
[root@db01 data]# mysql -urepl -p12321321 -h 10.0.0.51 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES)
[root@db01 data]# mysql -urep -p123 -h 10.0.0.51 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'rep'@'db01' (using password: YES)
[root@db01 data]# mysql -urepl -p123 -h 10.0.0.52 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.52' (113)
[root@db01 data]# mysql -urepl -p123 -h 10.0.0.51 -P 3309
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.51' (111)
[root@db01 data]# 

解决: 
1. stop slave 
2. reset slave all;
3. change master to 
4. start slave

(2) 请求Binlog

binlog 没开
binlog 损坏,不存在
主库执行 reset master 处理:

主库执行 reset master 主从不一致原因

因为bin_log文件发生了变化，从库不知道主库新的bin_log文件，故不能同步

故障演示

主库执行

# 把bin_log清零
mysql> reset master；

从库查看　　

mysql> show slave status \G

解决思路：

在主库执行reset master后，查看新生成的bin_log文件和position

mysql> show master status;

从库执行

stop slave ;
reset slave all; 

CHANGE MASTER TO
MASTER_HOST='10.0.0.200',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
start slave;

查看状态

show slave status \G

(3) 存储binlog到relaylog

relaylog权限不够

SQL线程故障

出现的大多数原因，是误在从库中进行了写的操作后，在主库中又执行同样的写入操作。

合理处理方法:

如果出现问题,尽量进行反操作
最直接稳妥办法,重新构建主从
把握一个原则,一切以主库为主.

一劳永逸的方法:

(1) 从库只读
read_only
super_read_only
(2) 使用读写分离中间件
atlas
mycat
ProxySQL
MaxScale

主从延时监控及原因

出现的问题

主库做了修改操作,从库比较长时间才能追上.

外在因素

网络
主从硬件差异较大
版本差异
参数因素

主库方面原因

(1) binlog写入不及时

sync_binlog=1

(2) 默认情况下dump_t 是串行传输binlog *****

在并发事务量大时或者大事务,由于dump_t 是串型工作的,导致传送日志较慢

如何解决问题?

必须GTID,使用Group commit方式.可以支持DUMP_T并行

(3) 主库极其繁忙

慢语句
锁等待
从库个数
网络延时

从库方面原因

(1) 传统复制(Classic)中 *****

如果主库并发事务量很大,或者出现大事务
由于从库是单SQL线程,导致,不管传的日志有多少,只能一次执行一个事务.
5.6 版本,有了GTID,可以实现多SQL线程,但是只能基于不同库的事务进行并发回放.(database)
5.7 版本中,有了增强的GTID,增加了seq_no,增加了新型的并发SQL线程模式(logical_clock),MTS技术

(2) 主从硬件差异太大

(3) 主从的参数配置

(4) 从库和主库的索引不一致

(5) 版本有差异

主从延时的监控分析

从库执行（查看主从延时时间）

show slave  status\G

主库执行

mysql> show master status ;

从库查看从主库拿了多少

show slave  status\G

从库查看从主库执行了多少

show slave  status\G

输出部分结果

Relay_Log_File: oldboyedu-relay-bin.000002
Relay_Log_Pos: 320
Exec_Master_Log_Pos: 154
Relay_Log_Space: 53

(3) 有没有及时回放

cat /data/3308/data/relay-log.info

posted @ 2020-03-07 22:50 Crazymagic 阅读(216) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Crazymagic