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
主备系统:有高可用性,但是需要切换,是单活的架构
KeepAlive,
MMM,
MHA*****, TMHA
真正高可用(多活系统):
MySQL NDB Cluster
Oracle RAC
Sysbase cluster
PXC, ***
MGC, ***
InnoDB Cluster(MGR 5.7.17) ****
1. 主从复制简介 **
1.1 基于二进制日志复制的
1.2 主库的修改操作会记录二进制日志
1.3 从库会请求新的二进制日志并回放,最终达到主从数据同步
1.4 主从复制核心功能:
辅助备份,处理物理损坏
扩展新型的架构:高可用,高性能,分布式架构等
2. 主从复制的前提(主从复制的规划,实施过程)
2.1 至少2个数据库实例
2.2 主库要开启binlog,不同server_id,server_uuid
2.3 主库要有一个专门用作复制的用户(replication slave)
2.4 通过备份将源库数据补偿到从库
2.5 告知从库,用户名,密码,ip,port,自动复制的起点
2.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 '123';"
[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 ~]# mysql -S /data/3308/mysql.sock </tmp/full.sql
3.5 告知从库,用户名,密码,ip,port,自动复制的起点
\# 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='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000011',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
vim /tmp/full.sql ##备份到哪个pos号,也就是主从复制的起点
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=444;
3.6 启动主从线程
[root@db01 ~]# mysql -S /data/3308/mysql.sock
oldguo[(none)]>start slave;
3.7 检测主从状态
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|egrep "Running:"
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
oldguo[(none)]>stop slave ;
oldguo[(none)]>reset slave all;
oldguo[(none)]> CHANGE MASTER TO xxxx
oldguo[(none)]>start slave;
4. 主从复制原理
4.1 主从复制过程中涉及到的文件
4.1.1 主库:
binlog 日志
/data/3307:
mysql-bin.000001
mysql-bin.000002
4.1.2 从库:
relaylog 中继日志
临时存储日志信息的文件
/data/3308/data
db01-relay-bin.000001
db01-relay-bin.000002
master.info 信息文件
主库信息文件
relay-log.info 信息文件
中继日志信息文件
4.2 主从复制中涉及到的线程
主库:
Binlog_Dump_Thread(二进制日志投递线程)
[root@db01 /data/3308/data]# mysql -S /data/3307/mysql.sock -e "show processlist;"
从库:
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|egrep "Running:"
Slave_IO_Thread
Slave_SQL_Thread
4.3 手撕主从复制原理
略
https://www.jianshu.com/p/6ed2cc292077
1. 主从复制的监控
从库
show slave status \G
oldguo[(none)]>
oldguo[(none)]>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.000012
Read_Master_Log_Pos: 154
Relay_Log_File: db01-relay-bin.000005
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000012
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 739
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 7
Master_UUID: c4af0dce-b834-11e9-9e5f-000c2980e248
Master_Info_File: /data/3308/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
oldguo[(none)]>
1.1 线程状态
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Running:"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
1.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 ~]#
1.3 查看主库连接信息有关
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Master"
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 154
Relay_Master_Log_File: mysql-bin.000012
Exec_Master_Log_Pos: 154
.......
[root@db01 ~]#
1.4 从库和主库延时的时间
mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Seconds_Behind_Master"
1.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 ~]#
1.6 延时从库的状态信息
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Delay:"
SQL_Delay: 0
SQL_Remaining_Delay: NULL
1.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 ~]#
1.8 中继日志监控
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Relay"
Relay_Log_File: db01-relay-bin.000005
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000012
Relay_Log_Space: 739
[root@db01 ~]#
2. 主从复制故障
2.1 IO线程故障
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status \G"|grep "Running:"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
(1) 读取master.info
损坏
信息错误 change master to信息错误
(2) 连接主库
网络
防火墙
主库没启动
连接数上限了
以上问题:
Slave_IO_Running: Connecting
Last_IO_Error: xxxxxx
排查方法:
通过复制用户,手工连接主库,看报错信息.
修复:
stop slave
reset slave all
change master to
start slave
(3) 请求日志 *****
master.info 复制起点
主库: 损坏,误删除等操作
(4) 接收日志
relaylog损坏
修复:
stop slave
reset slave all
change master to
start slave
(5) 更新master.info
2.2 SQL线程故障 *****
(1) relay.info
(2) 回放relaylog中的日志 *****
SQL语句为什么会失败?
(1) 语法,SQL_Mode
版本,sql_mode不一致
(2) DDL DML 为什么会失败
create database /table 创建的对象已经存在了.
从库被提前写入了
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:主键冲突,或约束冲突
但是,以上操作有时是有风险的,最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.
2.3 防止从库写入
(1) 可以设置从库只读.
oldguo[(none)]>show variables like '%read_only%';
+-----------------------+-------+
| read_only | OFF | #普通用户
| super_read_only | OFF | #管理员
5 rows in set (0.01 sec)
注意:
只会影响到普通用户,对管理员用户无效。
(2)加中间件
读写分离。
2.4 自己扩展
pt-xxx 关于主从方面的工具
检查主从数据一致性
实现主从数据同步
3. 主从延时 *****
3.1 什么是主从延时
主库做的事,从库很久才执行.
3.2 主从延时的现象
(1) 最直观: 主库做变更,从库看数据状态
(2) Seconds_Behind_Master: 0 (只能证明,有或者没有) ***
3.3 主从延时的原因 *****
3.3.1 外部因素
网络
硬件
版本差异
参数差异
3.3.2 内部因素
主库:
(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
从库:
(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=16
slave_parallel_type=logical_clock
注意: 必须依赖于GTID复制
如何监控 :
(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
3. 主从基础小结
3.1 主从前提
1.至少2个数据库实例.
2.主库要开启binlog,不同server_id,server_uuid
3.主库要有一个专门用作复制的用户
4.通过备份将源库数据补偿给从库
5.告知从库,用户名,密码,ip,port ,自动复制的起点
6.需要专门的复制线程(start slave)
3.2 主从原理 *****
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根据masert.info 记录的binglog文件名和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线程再请求。
3.3 主从监控
show master status;
show processlist;
show slave status\G
master.info
relay.info
3.4 主从故障
IO
连接
binlog
SQL
从库写入
DML,insert ,update ,delete
3.5 主从延时
主
dump 串行 : GTID, 双一 并行
show slave status\G
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1084
show master status \G
从
SQL 串行 : MTS
已经拿过来的日志:
show slave status\G
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1084
已经执行过的:
./db01-relay-bin.000003
920
mysql-bin.000001
800
====================
主从复制-高级进阶
1. 延时从库
1.1 作用
处理逻辑损坏
1.2 怎么实现的
SQL线程延时:数据已经写入relaylog中了,SQL线程"慢点"运行
一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;
mysql>start slave;
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL
1.3 处理逻辑损坏, 恢复数据思路
drop 操0作;
(1) 感知到故障.
(2) 停相关业务,挂维护页.
(3) 停延时从库SQL线程.
(4) 手工模拟SQL线程恢复后续的relaylog
起点: relaylog.info
终点: drop
(5) 直接解除从库替代主库工作
或者,导出故障数据库,倒回到生产.
1.4故障模拟
1)模拟数据
create database oldboy chaset utf8mb4;
use oldboy;
create table t1 (id int);
insert into t1 values(1);
insert into t1 values(11);
insert into t1 values(111);
insert into t1 values(11111);
insert into t1 values(111111);
最终效果:
oldguo[oldboy]>select * from t1;'
+--------+
| id |
+--------+
| 1 |
| 11 |
|111 |
| 11111 |
| 111111 |
- 模拟误操作
drop database oldboy;
- 停从库线程
stop slave;
- 截取reaylog日志
起点:
oldguo[(none)]>show slave status\G
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 476
终点:
oldguo[(none)]>oldguo[(none)]>show relaylog events in 'db01-relay-bin.000002';
db01-relay-bin.000002 | 1993 | Query | 8 |1925 | drop database oldboy
命令
mysqlbinlog --start-position=476 --stop-position=1993 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql
5)source 恢复数据
oldguo[(none)]>source /tmp/relay.sql
oldguo[oldboy]>select * from t1;
+--------+
| id |
+--------+
| 1 |
| 11 |
|111 |
|11111 |
|111111 |
2.过滤复制
2.1 主库方面
show master status; 查看
Binlog_Do_DB 白名单
Binlog_Ignore_DB 黑名单
2.2 从库方面参数
Replicate_Do_DB: 白名单库级别
Replicate_Ignore_DB: 黑名单库级别
Replicate_Do_Table: 白名单表级别
Replicate_Ignore_Table: 黑名单表级别
Replicate_Wild_Do_Table: 白名单
Replicate_Wild_Ignore_Table:
2.3过滤复制实现
例子:只复制oldguo库的数据:
从库操作如下
[root@db01 /data/3309/data]# vim ../my.cnf
[mysqld]
replicate_do_db=oldguo
oldguo[(none)]>show slave status\G
Replicate_Do_DB: oldguo
主库操作如下
oldguo[oldboy]>create database oldguo;
oldguo[oldboy]>create database test;
从库检查
oldguo[(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| OLD |
| mysql |
| oldguo |
| performance_schema |
| sys |
3.gtid复制
3.3 GTID复制配置过程
环境准备
1.1 清理环境
pkill mysqld
\rm -rf /data/mysql/data/*
\rm -rf /data/binlog/*
1.2 准备配置文件
主库db01:
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>
EOF
slave1(db02):
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF
slave2(db03):
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF
3. GTID复制
3.1 GTID介绍
GTID(Global Transaction ID)是对于一个已提交事务的唯一编号,并且是一个全局(主从复制)唯一的编号。
它的官方定义如下:
GTID = source_id :transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
什么是sever_uuid,和Server-id 区别?
核心特性: 全局唯一,具备幂等性
3.2 GTID核心参数
重要参数:
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
gtid-mode=on --启用gtid类型,否则就是普通的复制架构
enforce-gtid-consistency=true --强制GTID的一致性
log-slave-updates=1 --slave更新是否记入日志
3.3.3 初始化数据
mkdir -p /data/mysql/data
chown -R mysql.mysql /data/*
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
3.3.4 启动数据库
/etc/init.d/mysqld start
3.3.5 构建主从:
master:51
slave:52,53
51:
mysql -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
52\53:
mysql -e "change master to master_host='10.0.0.51',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;"
mysql -e "start slave;"
[root@db02 ~]# mysql -e "show slave status \G"|grep "Running:"
3.4 GTID 复制和普通复制的区别
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;
change master to
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;
(0)在主从复制环境中,主库发生过的事务,在全局都是由唯一GTID记录的,更方便Failover(故障切换)
(1)额外功能参数(3个)
(2)change master to 的时候不再需要binlog 文件名和position号,MASTER_AUTO_POSITION=1;
(3)在复制过程中,从库不再依赖master.info文件,而是直接读取最后一个relaylog的 GTID号
(4) mysqldump备份时,默认会将备份中包含的事务操作,以以下方式
SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1';
告诉从库,我的备份中已经有以上事务,你就不用运行了,直接从下一个GTID开始请求binlog就行。