0.企业高可用性标准
0.1 全年无故障率(非计划内故障停机)
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
0.2 高可用架构方案
负载均衡:有一定的高可用性
LVS Nginx
主备系统:有高可用性,但是需要切换,是单活的架构
KA , MHA, MMM
真正高可用(多活系统):
NDB Cluster Oracle RAC Sysbase cluster , InnoDB Cluster（MGR）,PXC , MGC
1. 主从复制简介
1.1. 基于二进制日志复制的
1.2. 主库的修改操作会记录二进制日志
1.3. 从库会请求新的二进制日志并回放,最终达到主从数据同步
1.4. 主从复制核心功能:
辅助备份,处理物理损坏
扩展新型的架构:高可用,高性能,分布式架构等
3.扩展架构中间件
3.1 读写分离
Atlas (360)
ProxySQL(percona)
MaxScale (Mariadb)
mysql router
3.2 高可用
MMM (google)
MHA (facebook taobao TMHA )
PXC,MGC
InnoDB Cluster(mysql 8.0强烈推荐)
MySQL cluster
3.3 分布式
Mycat (DBLE)
DRDS (tx)
PolarDB (ali)
4.主从复制前提(搭建主从的过程)
4.1两个或以上数据库实例(主库,从库),两台机器不同server_id
[root@db01 ~]# mysql -S /tmp/mysql3308.sock -e "select @@server_id"
[root@db01 ~]# mysql -S /tmp/mysql3307.sock -e "select @@server_id"
4.2 主库需要开启二进制日志
[root@db01 /data/3307]# mysql -S /tmp/mysql3307.sock -e "select @@log_bin"
4.3 主库要有专用复制用户(replication slave)
mysql -S /tmp/mysql3307.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123'"
4.4 从库需要追数据(mysqldump,xbk)
[root@db01 ~]# mysqldump -A --master-data=2 --single-transaction -S /tmp/mysql3307.sock >/tmp/full.sql
[root@db01 ~]# mysql -S /tmp/mysql3308.sock </tmp/full.sql
4.5 告诉从库复制信息(IP,port,user,password,binlog+pos)
change master to....
mysql -S /tmp/mysql3308.sock
mysql> help change master to
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
grep -i "change"|full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=444;
4.6开启复制线程并检查
start slave;
[root@db03 ~]#mysql -uroot -p -S /tmp/mysql.sock -e 'show slave status\G'|grep "Running:"
Enter password:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5.主从复制工作原理 (Classic Replication 传统复制)
5.1 主从中设置到的文件和线程
5.1.1线程
主:show processlist;
DUMP THREAD
从:
IO THREAD
SQL THREAD
5.1.2文件
主:
mysql-bin.000001
从:
db01-relay.000001 ===>中继日志
master.info ===》主库信息记录日志
[root@db03 ~]#cat /data/mysql/data/master.info
25
mysql-bin.000003
194
10.0.0.51
repl
123
3306
60
0
relay-log.info ===> 记录中继应用情况信息
[root@db03 ~]#cat /data/mysql/data/relay-log.info
7
./db03-relay-bin.000005
407
mysql-bin.000003
194
5.2 原理
image.png
主从复制原理描述：
(1) change master to 命令 ,指定主库的连接信息和复制起点,会被记录到master.info文件中
(2) start slave 开启 从库的 IO 和 SQL线程
(3) IO线程读取master.info,请求连接主库,建立连接后,主库分配一个dump线程和从库IO记性通信.
(4) 从库IO线程通过binlog位置点记录,向主库DUMP请求最新的binlog
(5) 主库截取全新二进制日志事件,DUMP线程发送给从库IO线程
(6) 在网络层面,二进制日志存储到TCP/IP缓存中,从库返回TCP/IP ACK确认
(7) IO线程将接收到的日志,存储到 db01-relay-bin.000001,更新master.info位置点信息.
(8) SQL线程读取relay-log.info,获取到上次执行到位置点,向后回放全新的日志
(9)回放完成后,SQL再次更新relay-log.info
(10) binlog dump实时监控binlog变化,一旦有新的,通知从库.
(11) 从库会定期删除应用过的db01-relay-bin.
6.主从故障监控\分析\处理
6.1 线程相关监控
6.1.1主库
每个从库都会有一行dump相关的信息
db01 [(none)]>show processlist;
+----+------+-----------------+------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+------------------+------+---------------------------------------------------------------+------------------+
| 6 | repl | 10.0.0.52:49406 | NULL | Binlog Dump GTID | 806 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 7 | repl | 10.0.0.53:42826 | NULL | Binlog Dump GTID | 800 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 8 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------------+------+------------------+------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
如果现实非以上信息,说明主从之间的关系出现了问题
6.1.2从库
db03 [(none)]>show slave status\G
(1)主库相关信息监控
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 444
(2)从库中继日志的应用状态
Relay_Log_File: db01-relay-bin.000002 **当前slave SQL线程正在读取并执行的relay log的文件名
Relay_Log_Pos: 485 **当前slave SQL线程正在读取并执行的relay log文件中的位置
(3)从库复制线程有关的状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
(4)过滤复制有关的状态
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
(5)从库回放的relaylog对应的主库的binlog的位置点
Exec_Master_Log_Pos: 308
slave SQL线程当前执行的事件，对应在master相应的二进制日志中的position。（结合Relay_Master_Log_File理解，而且在Relay_Master_Log_File这个值等于Master_Log_File值的时候，回放完毕。Exec_Master_Log_Pos是不可能超过Read_Master_Log_Pos的。）
Relay_Log_Space: 893
所有原有的中继日志结合起来的总大小。
(6)主从延时相关状态(非人为)
Seconds_Behind_Master: 0
(7)延时从库有关的状态(人为)
SQL_Delay: 0
SQL_Remaining_Delay: NULL
(8)GTID 复制有关的状态
Retrieved_Gtid_Set: a3f65d67-0c49-11ea-a647-000c2942a0b7:1-8
Executed_Gtid_Set: a3f65d67-0c49-11ea-a647-000c2942a0b7:1-8
Auto_Position: 1
7.主从复制故障分析及处理
7.1故障原因：IO
7.1.1连接主库
(1) 用户 密码 IP port
Last_IO_Error: error reconnecting to master 'repl@10.0.0.51:3307' - retry-time: 10 retries: 7
[root@db01 ~]# mysql -urepl -p123333 -h 10.0.0.51 -P 3307
ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES)
原因:
密码错误
用户错误
skip_name_resolve
地址错误
端口
处理方法：重新搭建主从
stop slave
reset slave all
change master to
start slave
(2)主库连接数上线,或者是主库太繁忙
show slave staus \G
Last_IO_Errno: 1040
Last_IO_Error: error reconnecting to master 'repl@10.0.0.51:3307' - retry-time: 10 retries: 7
处理思路:
拿复制用户,手工连接一下
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): Too many connections
处理方法:
db01 [(none)]>set global max_connections=300;
(3)防火墙,网络不通
7.1.2请求二进制日志
日志位置点不对.
主库日志不完整.
解决办法: 重新搭建主从
注意: 在主从复制环境中,严令禁止主库中reset master; 可以选择expire 进行定期清理主库二进制日志
解决方案:
重新构建主从
7.1.3落地日志
relaylog缺失
处理方法:
停止从库线程
判断并截取缺失部分日志,恢复到从库
（查看relaylog.info,查看已经回放到的relayog位置点对应的主库的binlog位置点）
再change master to重新建立主从（更改主库binlog位置点）
启动从库线程
7.2 SQL 线程故障
SQL线程功能：
(1)读写relay-log.info
(2)relay-log损坏,断节,找不到
(3)接收到的SQL无法执行
导致SQL线程故障原因分析：\
(1) relay无法访问
(2) 主从版本,SQL_Mode,参数不一致,系统配置不一致
(3) 需要创建的对象已经存在 , 要修改的对象不存在
原因1: 主从复制不一致(TCP/IP缓存中的日志丢失),导致SQL故障
原因2: 从库提前写入
(4) 约束冲突
主键,唯一键(数据录入时手动insert 不让其自增长)
从库提前写入
（1）stop slave;
（2）删除掉从库写入的数据
（3）start slave；
处理方法(以从库为核心的处理方案)：
方法一：
stop slave;
set global sql_slave_skip_counter = 1; #将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;
方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突
但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.
一劳永逸的方法:
(1) 可以设置从库只读.
vim /etc/my.cnf
read_only=on **普通用户只读
super_read _only=on **超级管理员只读
db01 [(none)]>show variables like '%read_only%';
(2)加中间件
读写分离。
**自己了解一下第三方工具:**
检查一致性
pt-table-sync
pt-table-checksum
检查延时:
pt-heartbeat
8.主从延时监控及原因
确认有没有延时
Seconds_Behind_Master: 0
监控延时的日志,造成延时的位置点.
Exec_Master_Log_Pos: 194 主库binlog的位置点
Relay_Log_Space: 21095298 从库binlog的位置点
主库做了修改操作,从库比较长时间才能追上.
8.1 主库
(1) 二进制日志写入不及时
[rep]>select @@sync_binlog;
(2) CR的主从复制中,binlog_dump线程,事件为单元,串行传送二进制日志(5.6 5.5)
1. 主库并发事务量大,主库可以并行,传送时是串行
2. 主库发生了大事务,由于是串行传送,会产生阻塞后续的事务.
解决方案:
1. 5.6 开始,开启GTID,实现了GC(group commit)机制,可以并行传输日志给从库IO
2. 5.7 开始,不开启GTID,会自动维护匿名的GTID,也能实现GC,我们建议还是认为开启GTID
3. 大事务拆成多个小事务,可以有效的减少主从延时.
8.2从库
SQL线程导致的主从延时
在CR复制情况下: 从库默认情况下只有一个SQL,只能串行回放事务SQL
1. 主库如果并发事务量较大,从库只能串行回放
2. 主库发生了大事务,会阻塞后续的所有的事务的运行
解决方案:
1. 5.6 版本开启GTID之后,加入了SQL多线程的特性,但是只能针对不同库(database)下的事务进行并发回放.
2. 5.7 版本开始GTID之后,在SQL方面,提供了基于逻辑时钟(logical_clock),binlog加入了seq_no机制,
真正实现了基于事务级别的并发回放,这种技术我们把它称之为MTS(enhanced multi-threaded slave).
3. 大事务拆成多个小事务,可以有效的减少主从延时.
[https://dev.mysql.com/worklog/task/?id=6314]
8.3 其他原因
网络
主从硬件差异较大
版本差异
参数因素
9.小结
1. 主从复制原理
2. 主从复制故障
3. 主从延时：group commit MTS
