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. 主从复制简介(MySql Replication) **
职责：
1.搭建主从复制
2.主从原理熟悉
3.主从的故障处理.
4.主从延时
5.主从的特殊架构的配置使用
6.主从架构的演变
1.1. 基于二进制日志复制的
1.2. 主库的修改操作会记录二进制日志
1.3. 从库会请求新的二进制日志并回放,最终达到主从数据同步
1.4. 主从复制的过程是异步
1.5. 主从复制核心功能:
辅助备份,处理物理损坏
扩展新型的架构:高可用,高性能,分布式架构等
2. 主从复制前提(搭建主从的过程) ***
(1) 2个或以上的数据库实例
(2) 主库需要开启二进制日志
(3) server_id要不同,区分不同的节点
(4) 主库需要建立专用的复制用户(replication slave)
(5) 从库应该通过备份主库,恢复的方法进行”补课"
(7) 人为告诉从库一些复制信息(ip port user pass,二进制日志起点)
(8) 从库应该开启专门的复制线程
3. 主从复制搭建(Classic replication) ***
3.1 清理主库数据
pkill mysqld
systemctl start mysqld3307
rm -rf /data/3308/data/*
rm -rf /data/3308/mysql-bin.*
3.2 重新初始化3308
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3308/data
systemctl start mysqld3307
systemctl start mysqld3308
mysql -uroot -p1 -S /data/3307/mysql.sock -e "select @@port"
mysql -S /data/3308/mysql.sock -e "select @@port"
3.3 主库修改my.cnf ,开启二进制日志功能，检查server_id
[root@db01 3307]# vim /data/3307/my.cnf
log_bin=/data/3307/data/mysql-bin
3.4 启动所有节点
[root@db01 3307]# systemctl start mysqld3307
[root@db01 3307]# systemctl start mysqld3308
[root@db01 3307]# systemctl start mysqld3309
[root@db01 3307]# ps -ef |grep mysqld
mysql 3684 1 4 09:59 ? 00:00:00 /app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
mysql 3719 1 7 09:59 ? 00:00:00 /app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
mysql 3754 1 8 09:59 ? 00:00:00 /app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
[root@db01 3307]# mysql -S /data/3307/mysql.sock -e "select @@server_id"
[root@db01 3307]# mysql -S /data/3308/mysql.sock -e "select @@server_id"
[root@db01 3307]# mysql -S /data/3309/mysql.sock -e "select @@server_id"
3.5 主库中创建复制用户
[root@db01 3307]# mysql -S /data/3307/mysql.sock
mysql>grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
mysql>select user,host from mysql.user;
3.6 备份主库并恢复到从库
[root@db01 ]# mysqldump -uroot -p1 -S /data/3307/mysql.sock -A --master-data=2 --single-transaction -R --triggers >/tmp/full.sql
[root@db01 ]# mysql -S /data/3308/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /backup/full.sql
3.7 告知从库关键复制信息
ip port user password binlog position
vim /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154;
[root@db01 3307]# mysql -S /data/3308/mysql.sock
db01 [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.000004',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
3.8 开启主从专用线程
mysql> start slave;
3.9 检查复制状态
mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep 'Yes'
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主库：mysql -uroot -p1 -S /data/3307/mysql.sock -e "create database maibi:"
从库：mysql -S /data/3308/mysql.sock -e "show databases"
4. 主从复制的原理 (Classic Replication)*
4.1 主从中设置到的文件和线程
4.1.1 线程
主:
Bin_Dump_THREAD:DUMP_T
从:
SLAVE_IO_THREAD:IO_T
SLAVE_SQL_THREAD:SQL_T
4.1.2 文件
主:
mysql-bin.000001 ===>二进制日志
从:
db01-relay.000001 ===>中继日志
master.info ===>主库信息记录日志
relay-log.info ===> 记录中继应用情况信息
4.2 主从复制原理
主从复制原理描述：
1.从库执行changemasterto命令(主库的连接信息+复制的起点)
2.从库会将以上信息，记录到master.info文件
3.从库执行start slave 命令,立即开启I0_T和SQL_T .
4.从库IO_T,读取master. info文件中的信息获取到IP,PORT,User,Pass,binlog的位置信息
5.从库I0_T请求连接主库,主库专门提供一个DUMP_T,负责和IO_T交互
4.从库IO_T，读取master.info文件中的信息获取到IP , PORT , User , Pass, binlog的位置信息
5.从库I0 T请求连接主库,主库专门提供一个DUMP_T,负责和IO_T交互
6. IO_ T根据binlog的位置信息(mysql-bin.000004 ，444) ,请求主库新的binlog
7.主库通过DUMP_T将最新的binlog,通过网络TP给从库的10T
8.IO_T接收到新的binlog日志,存储到TCP/IP缓存,立即返回ACK给主库,并更master.info
9.I0_T将TCP/IP缓存中数据,转储到磁盘relay1og中.
10.SQL_T读取relay.info中的信息,获取到上次己经应用过的relaylog的位置信息
11.SQI_T会按照上次的位置点回放最新的relay1og,再次更新relay.info信息
12.从库会自动purge应用过relay进行定期清理
补充说明:
--旦主从复制构建成功,主库当中发生了新的变化,都会通过dump_T发送信号给I0_T,增强了主从复制的实时性.
5. 主从故障监控\分析\处理 *
5.1 线程相关监控
主库:
show full processlist;
每个从库都会有一行dump相关的信息
HOSTS:
db01:47176
State:
Master has sent all binlog to slave; waiting for more updates
如果现实非以上信息,说明主从之间的关系出现了问题
从库:
mysql>show slave status \G
*************************** 1. row ***************************
主库相关的信息
Slave_IO_State: Waiting for master to send event
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: 316
从库relay应用有关信息（relay.info）
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 482
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
主库相关信息监控
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 444
从库中继日志的应用状态
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 485
从库复制线程有关的状态
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
5.2 主从复制故障分析
5.2.1 IO
5.2.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
地址错误
端口
image.png
image.png
处理方法
stop slave
reset slave all
change master to
start slave
主库连接数上限,或者是主库太繁忙
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
处理方法:
mysql>set global max_connections=300;
(3) 防火墙,网络不通
5.2.1.2 请求二进制日志
主库缺失日志
从库方面,二进制日志位置点不对
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000001' at 154, the last event read from '/data/3307/data/mysql-bin.000002' at 154, the last byte read from '/data/3307/data/mysql-bin.000002' at 154.'
image.png
注意: 在主从复制环境中,严令禁止主库中reset master; 可以选择expire 进行定期清理主库二进制日志
解决方案:
stop slave
reset slave all
change master to
start slave
5.2.2 SQL 线程故障
SQL线程功能：
(1)读写relay-log.info
(2)relay-log损坏,断节,找不到
(3)接收到的SQL无法执行
导致SQL线程故障原因分析：
1. 版本差异，参数设定不同，比如：数据类型的差异，SQL_MODE影响
2.要创建的数据库对象,已经存在
3.要删除或修改的对象不存在
4.DML语句不符合表定义及约束时.
归根揭底的原因都是由于从库发生了写入操作.
Last_SQL_Error: Error 'Can't create database 'db'; database exists' on query. Default database: 'db'. Query: 'create database db'
合理的处理方法
把握一个原则,一切以主库为准进行解决.
如果出现问题,尽量进行反操作
最直接稳妥办法,重新构建主从
暴力的处理方法(以从库为核心的处理方案)：
方法一：
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) 可以设置从库只读.
mysql>show variables like '%read_only%';
read_only
super_read_only
注意：
只会影响到普通用户，对管理员用户无效。
(2)加中间件读写分离。
atla s
mycat
ProxySQL
Maxscale
6. 主从延时监控及原因 *
主库做了修改操作,从库比较长时间才能追上.
6.1 外在因素
网络
主从硬件差异较大
版本差异
参数因素
6.2 主库
(1) 二进制日志写入不及时
[rep]>select @@sync_binlog;
sync_binlog=1
(2) CR的主从复制中,binlog_dump线程,事件为单元,串行传送二进制日志(5.6 5.5)
1. 主库并发事务量大,主库可以并行,传送时是串行
2. 主库发生了大事务,由于是串行传送,会产生阻塞后续的事务.
(3)主库极其繁忙，慢语句，锁等待，从库个数，网络延时
解决方案:
1. 5.6 开始,开启GTID,实现了GC(group commit)机制,可以并行传输日志给从库IO
2. 5.7 开始,不开启GTID,会自动维护匿名的GTID,也能实现GC,我们建议还是认为开启GTID
3. 大事务拆成多个小事务,可以有效的减少主从延时.
6.3 从库
（1）SQL线程导致的主从延时，传统复制(Classic)中,由于是单SQL线程,导致,不管传的日志有多少,只能一次执行一 个事务.
1. 主库如果并发事务量较大,从库只能串行回放
2. 主库发生了大事务,会阻塞后续的所有的事务的运行
解决方案:
1. 5.6 版本开启GTID之后,加入了SQL多线程的特性,但是只能针对不同库(database)下的事务进行并发回放.
2. 5.7 版本开始GTID之后,在SQL9+**********************************-方面,提供了基于逻辑时钟(logical_clock),binlog加入了seq_no机制,真正实现了基于事务级别的并发回放,这种技术我们把它称之为MTS(enhanced multi-threaded slave).
3. 大事务拆成多个小事务,可以有效的减少主从延时.
（2）主从硬件差异太大
（3）主从的参数配置
（4）从库和主库索引不一致
（5）主从版本不同
主从延时的监控:
mysq1> show slave status\G
Seconds Behind Master :0
主库方面原因的监控：
主库:
mysq1> show master status;
File:mysq1-bin.000001
Position: 1373
从库：
Master_Log_File:mysql-bin.000001
Read Master Log Pos :1373
从库方面原因的监控：
拿了多少:
Master_Log_File: mysql-bin.000001
Read Master Log Pos: 1373
执行了多少:
Relay_Log_File: db01-relay-bin.000004
Relay_Log_Pos: 320
Exec Mas ter_Log_Pos:691000
Relay_Log_Space: 691718
7. 小结
1. 主从复制原理
2. 主从复制故障
3. 主从延时：group commit MTS
