0、企业高可用性标准(全年无故障率)
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.1 企业级高可用方案
负载均衡:有一定的高可用性(LVS、Nginx)
主备系统:有高可用性,但是需要切换,是单活的架构
KeepAlive,
MMM,
MHA*****,
TMHA
真正高可用(多活系统):
MySQL NDB Cluster
Oracle RAC
Sysbase cluster
PXC, ***
MGC, ***
InnoDB Cluster(MGR 5.7.17) ****
1、主从复制简介
基于二进制日志复制的
主库的修改操作会记录二进制日志
从库会请求新的二进制日志并回放,最终达到主从数据同步
1.1 主从复制核心功能:
辅助备份,处理物理损坏
扩展新型的架构:高可用,高性能,分布式架构等
2、主从复制的前提(主从复制的规划,实施过程)
1>至少2个数据库实例。
2>主库要开启binlog,不同server_id,server_uuid。
3>主库要有一个专门用作复制的用户(replication slave)。
4>通过备份将源库数据补偿到从库。
5>告知从库,用户名,密码,ip,port,自动复制的起点。
6>需要专门的复制线程(start slave )。
3、主从复制
3.1 准备多实例环境
[root@db01 ~]# systemctl start mysqld3307
[root@db01 ~]# systemctl start mysqld3308
[root@db01 ~]# mysql -S /data/3307/mysql.sock
[root@db01 ~]# mysql -S /data/3308/mysql.sock
3.2 检查 主库binlog,不同server_id,server_uuid
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "select @@log_bin;select @@server_id"
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "select @@log_bin;select @@server_id"
3.3 主库创建复制用户
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123456';"
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "select user,host from mysql.user where user='repl';"
3.4 通过备份将源库数据补偿到从库
[root@db01 ~]# mysqldump -S /data/3307/mysql.sock -A -R -E --triggers --master-data=2 --single-transaction --max-allowed-packet=128M >/tmp/full.sql
[root@db01 ~]#
[root@db01 ~]# mysql -S /data/3308/mysql.sock </tmp/full.sql
3.5 告知从库,用户名,密码,ip,port,自动复制的起点
vim /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=444;
# change master to
[root@db01 ~]# mysql -S /data/3308/mysql.sock
oldguo[(none)]>help change master to
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
3.6 启动主从状态
[root@db01 ~]# mysql -S /data/3308/mysql.sock
wenjuan[(none)]>start slave;
Query OK, 0 rows affected (0.01 sec)
wenjuan[(none)]>
3.7 检测主从状态
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db01 ~]#
3.8 简单排错过程
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep Last
如果有问题:
[root@db01 ~]# mysql -S /data/3308/mysql.sock
wenjuan[(none)]>stop slave ;
wenjuan[(none)]>reset slave all;
wenjuan[(none)]> CHANGE MASTER TO xxxx
wenjuan[(none)]>start slave;
4、主从复制原理
4.1 主从复制过程中涉及到的文件
主库:binlog日志
存放路径:/data/3307
mysql-bin.000001
mysql-bin.000002
从库:
relaylog中继日志 ----临时存储日志信息的文件
存放路径:/data/3308/data
db01-relay-bin.000001
db01-relay-bin.000002
master.info 主库信息文件
relay-log.info 中继日志信息文件
4.2 主从复制中涉及到的线程
主库:
Binlog_Dump_Thread
查询:mysql -S /data/3307/mysql.sock -e 'show processlist'
从库:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
查询:mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep Yes
4.3 主从复制原理
![16955089-a5ed7f7b7dc4754f.png](https://i-blog.csdnimg.cn/blog_migrate/d47c535d8270d53c089f1ec6dd53eb67.png)
来自oldguo
![16955089-05f133a54d860c76.png](https://i-blog.csdnimg.cn/blog_migrate/d3e458575aabd78a8286bf2d7930ddef.png)
来自oldguo
主从复制原理描述:
1>change master to 时,ip pot user password binlog position写入到master.info进行记录
2>start slave 时,从库会启动IO线程和SQL线程
3>IO_T,读取master.info信息,获取主库信息连接主库
4>主库会生成一个准备binlog DUMP线程,来响应从库
5>IO_T根据master.info记录的binlog文件名和position号,请求主库DUMP最新日志
6>DUMP线程检查主库的binlog日志,如果有新的,TP(传送)给从从库的IO_T
7>IO_T将收到的日志存储到了TCP/IP 缓存,立即返回ACK给主库 ,主库工作完成
8>IO_T将缓存中的数据,存储到relay-log日志文件,更新master.info文件binlog 文件名和postion,IO_T工作完成
9>SQL_T读取relay-log.info文件,获取到上次执行到的relay-log的位置,作为起点,回放relay-log
10>SQL_T回放完成之后,会更新relay-log.info文件。
11>relay-log会有自动清理的功能。
细节:
1>主库一旦有新的日志生成,会发送“信号”给binlog dump ,IO线程再请求
5、主从复制的监控
从库:
show slave status\G
5.1 线程状态
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Running:"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5.2 线程报错具体信息
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Last"
Last_Errno: 0
Last_Error:
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
[root@db01 ~]#
5.3 查看主库的链接信息有关
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Master"|grep -v 'SSL'
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 154
Relay_Master_Log_File: mysql-bin.000006
Exec_Master_Log_Pos: 154
Seconds_Behind_Master: 0
Master_Server_Id: 7
Master_UUID: 12a52bf9-b835-11e9-ae87-000c290143b9
Master_Info_File: /data/3308/data/master.info
Master_Retry_Count: 86400
Master_Bind:
Master_TLS_Version:
[root@db01 ~]#
5.4 查看从库和主库延时的时间
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Master"|grep 'Seconds_Behind_Master'
Seconds_Behind_Master: 0
[root@db01 ~]#
5.5 过滤复制相关状态
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep 'Replicate'
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Replicate_Ignore_Server_Ids:
Replicate_Rewrite_DB:
[root@db01 ~]#
5.6 延时从库的状态信息
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep 'Delay'
SQL_Delay: 0
SQL_Remaining_Delay: NULL
[root@db01 ~]#
5.7 监控GTID复制状态信息
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep 'Gtid'
Retrieved_Gtid_Set:
Executed_Gtid_Set:
[root@db01 ~]#
5.8 查看中继日之间
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep 'Gtid'
Retrieved_Gtid_Set:
Executed_Gtid_Set:
[root@db01 ~]#
6、主从复制故障
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Running:"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6.1 IO线程故障
(1) 读取master.info
损坏
信息错误----change master to信息错误
(2) 连接主库
网络
防火墙
主库没启动
连接数上限了(默认151个)
以上问题会暴露以下信息:
Slave_IO_Running: Connecting
Last_IO_Error: xxxxxx
排查方法:
通过复制用户,手工连接主库,看报错信息.
修复:
stop slave
reset slave all
change master to
start slave
注意:单独启动IO方法:start slave io_thread
(3) 请求日志 (重要)
master.info 复制起点
主库: 损坏,误删除等操作
(4) 接收日志
relaylog损坏
修复:
stop slave
reset slave all
change master to
start slave
(5) 更新master.info
mysql错误所有代码:https://www.jianshu.com/p/8b5ea28609d2
6.2 SQL线程故障 ※※※※※
6.2.1 relay-log.info
6.2.2 回访relaylog中的日志 ※※※※※
SQL语句为什么会失败?
(1)语法,SQL_Mode
版本不同,sql_mode不一致。
(2)DDL、DML为什么会失败?
create database/table 创建的对象已存在
原因:从库被提前写入
解决:以主库为准,删除从库已存在的对象,然后重启主从:start slave
drop database errdb; 要删除和修改的对象不存在.
alter
insert
update
delete
处理方法(以从库为核心的处理方案):
方法一:
stop slave;
set global sql_slave_skip_counter = 1;
#将同步指针向下移动一个,如果多次不同步,可以重复操作。
start slave;
方法二:
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突
但是,以上操作有时是有风险的,最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.
6.3 防止从库写入
(1)在从库设置只读
oldguo[(none)]>show variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | OFF |
| super_read_only | OFF |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.01 sec)
注意:只会影响到普通用户,对管理员用户无效。
(2)加中间件。
读写分离
扩展:
pt-xxx 关于主从复制 的过程
检查主从数据一致性:
实现主从数据同步
7、主从延时 ※※※※※
7.1 什么是主从延时?
主库做的事,从库很久才执行。
7.2 主从延时的现象
(1)最直观:主库做变更,从库看数据状态
(2)Seconds_Behind_Master: 0(只能证明,有或者没有)
(3)计算日志的差异 ※※※※※
7.3 主从延时的原因
7.3.1 外部因素
网络、硬件、版本差异、参数差异cd
7.3.2 内部因素
1>主库:
(1)二进制日志方面
二进制日志落地不及时
解决方案:
sync_binlog=1
可以将binlog单独存放高性能存储中
(2)Dump_T(默认是串行工作模式)
主库的事务量大
主库发生大事务
解决方案:
1>GTID 模式
2>双一的保证
如何监控:
主库: show master status;
从库: show slave status \G
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 484
2> 从库:
(1) IO 线程方面
relaylog写入
解决方案:
可以将relaylog单独存放高性能存储中
| relay_log_basename | /data/3308/data/db01-relay-bin |
| relay_log_index | /data/3308/data/db01-relay-bin.index |
(2)SQL线程方面(只有一个 ,串行回放) *****
默认SQL线程,只能逐条的回放SQL
事务并发高
大事务
5.6 版本 加入了多SQL复制
按照库(database)级别,进行并发回放SQL
slave_parallel_workers=16
slave_parallel_type=DATABASE
5.7 版本 进行了多SQL复制加强(MTS)
真正按照事务级别,实现了多SQL线程回放
slave_parallel_workers=·
slave_parallel_type=logical_clock
注意: 必须依赖于GTID复制,并且binlog_format=row
如何监控:
(1)监控取了多少日志
show slave status \G
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1084
(2)回放了多少日志
[root@db01 /data/3308/data]# cat relay-log.info
7
./db01-relay-bin.000003
920
mysql-bin.000001
1084