mysql 高可用

 

 

 

 

MySQL 高可用技术

 

 

什么是高可用

 

 

无故障时间	故障时间
99.9% ：人为干预	0.1%	= 525.6	min	KA+双主
99.99%	0.01%	= 52.56	min	MHA 、

 

 

 

 

 

 

 

数据库容灾级别

 

 

级别	方式	RPO	RTO
L0	无备源中心: 没有灾难恢复能力，只在本地进行数据备份	24 小时 +	4小时+
L1	本地备份+异地保存： 本地将关键数据备份，然后送到异地保存。 灾难发生后，按预定数据恢复程序恢复系统和数据。	24 小时 +	8小时+
L2	双中心主备模式: 在异地建立一个热备份点，通过网络进行数据备份。 当出现灾难时，备份站点接替主站点的业务，维护业务连续性	秒级	数分钟到半小时
L3	双中心双活 在相隔较远的地方分别建立两个数据中心，进行相互数据备份。 当某个数据中心发生灾难时，另一个数据中心接替其工作任务。	秒级	秒级
L4	双中心双活 + 异地热备 = 两地三中心 在同城分别建立两个数据中心，进行相互数据备份。 当该城市的2个中心同时不可用（地震/大面积停电/网络等），快速切换到异地	秒级	分钟级

 

 

 

1. MHA高可用技术

 

 

1.0 MHA高可用架构介绍及搭建过程

 

# 规划:

主库:

51 node

从库:

52 node

53 node manager # 准备环境

略。1主2从GTID

 

# 配置关键程序软连接

ln -s /data/app/mysql/bin/mysqlbinlog

/usr/bin/mysqlbinlog

ln -s /data/app/mysql/bin/mysql /usr/bin/mysql

 

 

# 配置各节点互信(各节点之间无密码SSH) ## db01：

rm -rf /root/.ssh ssh-keygen

cd /root/.ssh

mv id_rsa.pub authorized_keys

scp -r /root/.ssh 10.0.0.52:/root scp -r /root/.ssh 10.0.0.53:/root ## 各节点验证

## db01:

ssh 10.0.0.51 date

ssh 10.0.0.52 date

ssh 10.0.0.53 date ## db02:

ssh 10.0.0.51 date

ssh 10.0.0.52 date

ssh 10.0.0.53 date ## db03:

ssh 10.0.0.51 date

ssh 10.0.0.52 date

ssh 10.0.0.53 date

 

 

#安装软件

## 下载mha软件

mha官网：https://code.google.com/archive/p/mysql-master-ha/

 

github下载地址：https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

 

 

 

## 所有节点安装Node软件依赖包

yum install perl-DBD-MySQL -y rpm -ivh mha4mysql-node*.rpm

 

## 在db01主库中创建mha需要的用户

create user mha@'10.0.0.%' identified with mysql_native_password by 'mha';

grant all privileges on *.* to mha@'10.0.0.%';

 

 

## Manager软件安装（db03）

yum install -y perl-Config-Tiny epel-release perl-Log-

Dispatch perl-Parallel-ForkManager perl-Time-HiRes rpm -ivh mha4mysql-manager*.rpm

 

## 配置文件准备(db03) ### 创建配置文件目录 mkdir -p /etc/mha

 

### 创建日志目录

mkdir -p /var/log/mha/app1

 

 

### 编辑mha配置文件

vim /etc/mha/app1.cnf [server default]

manager_log=/var/log/mha/app1/manager manager_workdir=/var/log/mha/app1 master_binlog_dir=/data/binlog/ user=mha

password=mha ping_interval=2 repl_password=123 repl_user=repl ssh_user=root

 

[server1] hostname=10.0.0.51 port=3306

 

 

 

1.1 MHA软件结构介绍

 

 

 

 

1.2 应用透明---VIP

 

vip : 10.0.0.55/24

vip 故障转移脚本

上传脚本文件到/usr/local/bin 解压

[root@db03 mha_script]# cp -a /data/mha_script/*

/usr/local/bin

 

 

修改权限

[root@db03 bin]# chmod +x /usr/local/bin/*

 

 

修改内容

[root@db03 bin]# cp master_ip_failover master_ip_failover.bak

my $vip = '10.0.0.55/24'; my $key = '1';

my $if = 'ens33';

my $ssh_start_vip = "/sbin/ifconfig $if:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig $if:$key down"; my $ssh_Bcast_arp= "/sbin/arping -I $if -c 3 -A 10.0.0.55";

 

修改Manager 配置文件

vim /etc/mha/app1.cnf master_ip_failover_script=/usr/local/bin/master_ip_failove r

 

 

 

重启MHA

[root@db03 bin]# masterha_stop --conf=/etc/mha/app1.cnf [root@db03 bin]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null>

/var/log/mha/app1/manager.log 2>&1 &

 

 

 

手工在主库添加VIP

[root@db03 bin]# masterha_check_status --conf=/etc/mha/app1.cnf

[root@db02 ~]# ifconfig ens33:1 10.0.0.55/24

 

 

 

1.3.0 故障提醒功能

 

 

1.3.1 故障如何半自愈？

 

# 1. 检查数据库是否启动

[root@db01 app]# mysqladmin -umha -pmha -h 10.0.0.51 ping

 

 

# 2. 恢复主从

## 2.1 缺失少部分日志

如果没有缺失GTID事务。直接change master

 

## 2.2 缺失大部分日志

备份主库数据，恢复至从库。change master clone plugin + change master to

 

# 3. 配置文件添加修复节点

masterha_conf_host --command=add --conf=/etc/mha/app1.cnf

--hostname=10.0.0.51 --block=server10 --params="port=3306" # 4. 检查

## 互信检查

masterha_check_ssh --conf=/etc/mha/app1.cnf ## 主从状态检查

masterha_check_repl --conf=/etc/mha/app1.cnf

 

 

# 启动MHA

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover <

/dev/null> /var/log/mha/app1/manager.log 2>&1 &

 

 

手工恢复具体步骤：

1. 修改Linux服务器
2. 安装MySQL软件
3. 初始化数据，并启动
4. clone 数据到新节点，并构建主从主库：

mysql -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so';create user test@'%' identified by '123';grant backup_admin on *.* to test@'%';"

从库：

mysql -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so';create user test@'%' identified by '123';grant clone_admin on *.* to test@'%';SET GLOBAL clone_valid_donor_list='10.0.0.52:3306';"

 

mysql -utest -p123 -h10.0.0.51 -P3306 -e "CLONE INSTANCE FROM test@'10.0.0.52':3306 IDENTIFIED BY '123';"

mysql -e " SELECT STAGE, STATE, END_TIME FROM

performance_schema.clone_progress;" mysql -e \

"CHANGE MASTER TO \ MASTER_HOST='10.0.0.52',\ MASTER_USER='repl', \ MASTER_PASSWORD='123', \ MASTER_PORT=3306, \ MASTER_AUTO_POSITION=1;"

mysql -e "start slave;"

mysql -e "show slave status \G"|grep "Running:"

 

 

1. 添加节点至配置文件

masterha_conf_host --command=add --conf=/etc/mha/app1.cnf

--hostname=10.0.0.51 --block=server10 --params="port=3306"

 

 

 

1. 修复binlogserver

mysqlbinlog  -R --host=10.0.0.52 --user=mha --password=mha

--raw  --stop-never mysql-bin.000003 &

 

 

1. 状态检查

## 互信检查

masterha_check_ssh --conf=/etc/mha/app1.cnf ## 主从状态检查

masterha_check_repl --conf=/etc/mha/app1.cnf

 

 

1. 启动MHA

[root@db03 app1]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null>

/var/log/mha/app1/manager.log 2>&1 & [root@db03 app1]# masterha_check_status --conf=/etc/mha/app1.cnf

 

扩展作业：

写脚本实现半自愈功能。

 

1.4 日志补偿的冗余方案--binlog_server

 

创建必要目录(db03)

mkdir -p /data/binlog_server/ chown -R mysql.mysql /data/*

 

cd /data/binlog_server/

 

 

[root@db03 ~]# mysql -e "show slave status \G"|grep "Master_Log"

Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 194

Relay_Master_Log_File: mysql-bin.000008 Exec_Master_Log_Pos: 194

[root@db03 ~]#

[root@db03 binlog_server]# masterha_check_status --conf=/etc/mha/app1.cnf

app1 (pid:7057) is running(0:PING_OK), master:10.0.0.52

 

 

mysqlbinlog  -R --host=10.0.0.52 --user=mha --password=mha

--raw  --stop-never mysql-bin.000003 &

 

 

注意：

拉取日志的起点,需要按照目前从库的已经获取到的二进制日志点为起点配置文件设置

vim /etc/mha/app1.cnf [binlog1]

no_master=1 hostname=10.0.0.53

master_binlog_dir=/data/binlog_server/

 

 

重启MHA

[root@db03 bin]# masterha_stop --conf=/etc/mha/app1.cnf [root@db03 bin]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null>

/var/log/mha/app1/manager.log 2>&1 &

 

 

 

1.5 MHA的维护操作 - 在线切换功能

 

 

修改MHA配置文件

vim /etc/mha/app1.cnf master_ip_online_change_script=/usr/local/bin/master_ip_on line_change

 

停 MHA

[root@db03 bin]# masterha_stop  --conf=/etc/mha/app1.cnf

 

 

检查repl

[root@db03 bin]# masterha_check_repl --conf=/etc/mha/app1.cnf

 

在线切换

masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=10.0.0.51 --orig_master_is_new_slave --running_updates_limit=10000

 

重构binlogserver

[root@db03 bin]# ps -ef |grep mysqlbinlog

root 28144  16272  0 17:50 pts/1 00:00:00

mysqlbinlog -R --host=10.0.0.52 --user=mha --password=x x

--raw --stop-never mysql-bin.000005

root 28529 16272 0 18:03 pts/1 00:00:00 grep --color=auto mysqlbinlog

[root@db03 bin]# kill -9 28144 [root@db03 bin]# cd /data/binlog_server/ [root@db03 binlog_server]# ll

total 4

-rw-r----- 1 root root 194 Apr 1 17:50 mysql-bin.000005 [root@db03 binlog_server]# rm -rf *

[root@db03 binlog_server]# mysqlbinlog -R --host=10.0.0.51 --user=mha --password=mha --raw --stop-never mysql-bin.000009 &

[1] 28534

 

 

启动MHA

[root@db03 bin]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null>

/var/log/mha/app1/manager.log 2>&1 &

 

 

 

1.6 MHA如何防止脑裂

 

 

1.7 MHA的数据一致性保证

 

 

 

 

 

2、ProxySQL中间件

 

2.1 介绍

 

 

 

基本架构

 

 

 

安装ProxySQL

 

 

 

 

ProxySQL中管理结构自带系统库

 

 

ProxySQL管理接口的多层配置关系

 

 

 

 

在不同层次间移动配置

 

 

##  runtime 保存至 MEM

SAVE MYSQL SERVERS TO MEMORY;

 

 

## disk 加载到 MEM

LOAD MYSQL SERVERS FROM DISK;

 

 

## MEM 到 disk

SAVE MYSQL SERVERS TO DISK;

 

 

## CFG 到 MEM

LOAD MYSQL SERVERS FROM CONFIG

===============================

1. mysql query rules配置 ## MEM 加载到runtime

LOAD MYSQL QUERY RULES TO RUNTIME;

 

 

##  runtime 保存至 MEM

SAVE MYSQL QUERY RULES TO MEMORY;

 

 

## disk 加载到 MEM

LOAD MYSQL QUERY RULES FROM DISK;

 

 

## MEM 到 disk

SAVE MYSQL QUERY RULES TO DISK;

 

 

## CFG 到 MEM

LOAD MYSQL QUERY RULES FROM CONFIG

 

 

=================================

1. MySQL variables配置 ## MEM 加载到runtime

LOAD MYSQL VARIABLES TO RUNTIME;

 

 

##  runtime 保存至 MEM

SAVE MYSQL VARIABLES TO MEMORY;

 

 

## disk 加载到 MEM

LOAD MYSQL VARIABLES FROM DISK;

 

 

## MEM 到 disk

SAVE MYSQL VARIABLES TO DISK;

 

 

 

ProxySQL应用——基于SQL的读写分离

 

 

ProxySQL 会根据server 的read_only 的取值将服务器进行分组。 read_only=0 的server，master被分到编号为10的写组，read_only=1的server，slave则被分到编号20的读组。

所以需要将从库设置：

set global read_only=1;

set global super_read_only=1;

 

 

 

 

 

1. 添加主机到ProxySQL

insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.0.0.51',3306);

insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.52',3306);

insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.53',3306);

 

load mysql servers to runtime; save mysql servers to disk;

 

 

 

1. 创建监控用户，并开启监控

# 主库创建监控用户

create user monitor@'%' identified with mysql_native_password by '123';

grant replication client on *.* to monitor@'%';

 

 

 

# proxySQL修改variables表

set mysql-monitor_username='monitor'; set mysql-monitor_password='123';

 

或者 ：

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';

UPDATE global_variables SET variable_value='123' WHERE variable_name='mysql-monitor_password';

 

 

 

load mysql variables to runtime; save mysql variables to disk;

 

 

# 查询监控日志

db03 [(none)]>select * from mysql_server_connect_log; db03 [(none)]>select * from mysql_server_ping_log;

db03 [(none)]>select * from mysql_server_read_only_log; db03 [(none)]>select * from mysql_server_replication_lag_log;

 

1. 配置应用用户

create user root@'%' identified with mysql_native_password by '123';

grant all on *.* to root@'%';

 

 

 

insert into mysql_users(username,password,default_hostgroup) values('root','123',10);

 

load mysql users to runtime; save mysql users to disk;

 

早期版本，需要开启事务持续化。

update mysql_users set transaction_persistent=1 where username='root';

load mysql users to runtime; save mysql users to disk;

 

begin; select update delete select commit

 

select update

 

DDL ---》 30 ---》 SQL平台

 

 

1. 实用的读写规则

 

 

 

ProxySQL应用扩展——花式路由规则

 

 

delete from mysql_query_rules; # 为了测试，先清空已有规则

 

insert into mysql_query_rules(rule_id,active,proxy_port,destination_ho stgroup,apply)

values(1,1,6033,10,1), (2,1,6034,20,1);

 

 

load mysql query rules to runtime; save mysql query rules to disk;

 

说明：

除了基于端口进行分离，还可以基于监听地址(修改字段proxy_addr即可)，也可以基于客户端地址(修改字段client_addr字段即可)。

 

1. 基于用户的路由

nsert into mysql_users(username,password,default_hostgroup) values('writer','123',10),('reader','123',20); load mysql users to runtime;

save mysql users to disk;

 

 

delete from mysql_query_rules; # 为了测试，先清空已有规则

 

insert into mysql_query_rules(rule_id,active,username,destination_host group,apply) values(1,1,'writer',10,1),(2,1,'reader',20,1);

 

load mysql query rules to runtime; save mysql query rules to disk;

 

 

 

1. 配置规则将DROP语句屏蔽。

insert into mysql_query_rules(rule_id,active,match_pattern,destination

_hostgroup,apply) values (3,1,'^drop',30,1); load mysql query rules to runtime;

save mysql query rules to disk;

 

3、 ORCH可视化高可用集群应用 (ORCH+5.7.26+GTID+SEMISYNC+MTS+

ProxySQL)

 

3.1 ORCH介绍

Orchestrator（orch）：go编写的MySQL高可用性和复制拓扑管理工具，支持复制拓扑结构的调整，自动故障转移和手动主从切换等。后端数据库用MySQL或SQLite存储元数据，并提供Web界面展示MySQL复制的拓扑关系及状态，通过Web可更改MySQL实例的复制关系和部分配置信息，同时也提供命令行和api接口，方便运维管理。相对比MHA来看最重要的是解决了管理节点的单点问题，其通过raft协议保证本身的高可用。GitHub的一部分管理也在用该工具进行管理。

① 自动发现MySQL的复制拓扑，并且在web上展示。

② 重构复制关系，可以在web进行拖图来进行复制关系变更。

③ 检测主异常，并可以自动或手动恢复，通过Hooks进行自定义脚本。

④ 支持命令行和web界面管理复制。

3.2 部署

a. 节点规划表

 

IP地址	主机名	安装软件	数据库端口
10.0.0.51	db01	mysql、orchestrator	3306、3307
10.0.0.52	db02	mysql、orchestrator	3306、3307
10.0.0.53	db03	mysql、orchestrator	3306、3307

根据以上表格安装软件、配置地址和主机名、初始化6个MySQL实例。

orch默认是用主机名来进行管理的，需要在mysql的配置文件里添加： report_host和report_port参数。

 

 

[root@db02 ~]# mysql -V

mysql Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL)

[root@db03 ~]# mysql -V

mysql Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL)

 

[root@db01 ~]# hostname db01

[root@db01 ~]# cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

10.0.0.53 db03

10.0.0.52 db02

10.0.0.51 db01

 

 

[root@db02 ~]# cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

10.0.0.53 db03

10.0.0.52 db02

10.0.0.51 db01

[root@db03 ~]# cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

10.0.0.53 db03

10.0.0.52 db02

10.0.0.51 db01

 

 

# 部署6个 MySQL实例 pkill mysqld

rm -rf /etc/my.cnf

rm -rf /data/3306/data /data/3306/binlog* mkdir -p /data/3306/data /data/3306/binlog

 

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data

 

pkill mysqld

rm -rf /etc/my3307.cnf

rm -rf /data/3307/data /data/3307/binlog* mkdir -p /data/3307/data /data/3307/binlog chown -R mysql.mysql /data

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data

 

cat > /etc/my3306.cnf <<EOF [client] socket=/tmp/mysql3306.sock [mysqld]

user=mysql basedir=/usr/local/mysql datadir=/data/3306/data

log_bin=/data/3306/binlog/mysql-bin server_id=6

socket=/tmp/mysql.sock gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE log_slave_updates=ON

tmpdir = /tmp

default-storage-engine=INNODB disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE

max_connections=500 max_allowed_packet=32M

default_authentication_plugin=mysql_native_password report_host=10.0.0.51

report_port=3306 mysqlx=0

EOF

 

 

 

cat > /etc/my3307.cnf <<EOF [client]

 

 

 

b. 获取软件和相关脚本

 

 

1. 安装软件

 

 

1. 配置orch 后端数据库及用户(所有3306节点)

 

 

 

1. 配置被管理节点主从关系(3307)

 

# 主库

CREATE USER 'repl'@'%' IDENTIFIED with

mysql_native_password BY '123456';

GRANT replication slave  on *.* TO 'repl'@'%';

 

 

# 从库

change master to master_host='10.0.0.51',master_port=3307,master_user='repl ',master_password='123456',master_auto_position=1,MASTER_H EARTBEAT_PERIOD=2,MASTER_CONNECT_RETRY=1, MASTER_RETRY_COUNT=86400;

start slave;

set global slave_net_timeout=8; set global read_only=1;

set global super_read_only=1;

 

 

#说明：

1、orch检测主库宕机依赖从库的IO线程。默认change master to时， MASTER_HEARTBEAT_PERIOD过长，会导致切换判断过久。所以，需要修改 MASTER_HEARTBEAT_PERIOD 优化故障感知速度。

2、另外slave_net_timeout,参数定义了从库从主库获取数据等待的秒数，超过这个时间从库会主动退出读取，中断连接，并尝试重连。

 

f. 配置被管理节点用户（主库）

 

 

 

1. orch配置文件定制

 

cp orchestrator-sample.conf.json

/etc/orchestrator.conf.json

 

 

vim /etc/orchestrator.conf.json

{

"Debug": true, "EnableSyslog": false, "ListenAddress": ":3000",

"MySQLTopologyUser": "orchestrator", "MySQLTopologyPassword": "Aa123456", "MySQLTopologyCredentialsConfigFile": "", "MySQLTopologySSLPrivateKeyFile": "", "MySQLTopologySSLCertFile": "", "MySQLTopologySSLCAFile": "", "MySQLTopologySSLSkipVerify": true, "MySQLTopologyUseMutualTLS": false, "MySQLOrchestratorHost": "127.0.0.1",

"MySQLOrchestratorPort": 3306, "MySQLOrchestratorDatabase": "orchestrator", "MySQLOrchestratorUser": "orchestrator", "MySQLOrchestratorPassword": "123456", "MySQLOrchestratorCredentialsConfigFile": "", "MySQLOrchestratorSSLPrivateKeyFile": "", "MySQLOrchestratorSSLCertFile": "", "MySQLOrchestratorSSLCAFile": "", "MySQLOrchestratorSSLSkipVerify": true, "MySQLOrchestratorUseMutualTLS": false, "MySQLConnectTimeoutSeconds": 1,

"MySQLTopologyReadTimeoutSeconds": 3,

"MySQLDiscoveryReadTimeoutSeconds": 3,

"DefaultInstancePort": 3306,

 

"DiscoverByShowSlaveHosts": true, "InstancePollSeconds": 3,

"UnseenInstanceForgetHours": 240,

"SnapshotTopologiesIntervalHours": 0,

"InstanceBulkOperationsWaitTimeoutSeconds": 10, "HostnameResolveMethod": "default", "MySQLHostnameResolveMethod": "@@hostname", "SkipBinlogServerUnresolveCheck": true, "SkipMaxScaleCheck":true, "ExpiryHostnameResolvesMinutes": 60, "RejectHostnameResolvePattern": "", "ReasonableReplicationLagSeconds": 10, "ProblemIgnoreHostnameFilters": [], "VerifyReplicationFilters": false, "ReasonableMaintenanceReplicationLagSeconds": 20,

"CandidateInstanceExpireMinutes": 1440, "AuditLogFile": "",

"AuditToSyslog": false, "RemoveTextFromHostnameDisplay": ":3306", "ReadOnly": false, "AuthenticationMethod": "", "HTTPAuthUser": "",

"HTTPAuthPassword": "", "AuthUserHeader": "", "PowerAuthUsers": ["*"],

"ClusterNameToAlias": {"127.0.0.1": "test suite"}, "SlaveLagQuery": "",

"DetectClusterAliasQuery": "SELECT cluster_name FROM meta.cluster WHERE cluster_name = left(@@hostname,4) ", "DetectClusterDomainQuery": "SELECT cluster_domain FROM meta.cluster WHERE cluster_name = left(@@hostname,4) ", "DetectInstanceAliasQuery": "SELECT @@hostname as instance_alias",

"DetectPromotionRuleQuery": "", "DetectDataCenterQuery": "SELECT data_center FROM meta.cluster WHERE cluster_name = left(@@hostname,4) ", "PhysicalEnvironmentPattern": "", "PromotionIgnoreHostnameFilters": [], "DetachLostReplicasAfterMasterFailover": true,

 

"DetectSemiSyncEnforcedQuery": "SELECT 0 AS semisync FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM

performance_schema.global_variables WHERE VARIABLE_NAME = 'rpl_semi_sync_master_wait_no_slave' AND VARIABLE_VALUE = 'ON') UNION SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM

performance_schema.global_variables WHERE VARIABLE_NAME = 'rpl_semi_sync_master_wait_no_slave' AND VARIABLE_VALUE = 'ON')",

"ServeAgentsHttp": false, "AgentsServerPort": ":3001", "AgentsUseSSL": false, "AgentsUseMutualTLS": false, "AgentSSLSkipVerify": false, "AgentSSLPrivateKeyFile": "", "AgentSSLCertFile": "", "AgentSSLCAFile": "", "AgentSSLValidOUs": [], "UseSSL": false, "UseMutualTLS": false, "SSLSkipVerify": false, "SSLPrivateKeyFile": "", "SSLCertFile": "",

"SSLCAFile": "",

"SSLValidOUs": [],

"URLPrefix": "", "StatusEndpoint": "/api/status", "StatusSimpleHealth": true, "StatusOUVerify": false, "AgentPollMinutes": 60,

"UnseenAgentForgetHours": 6,

"StaleSeedFailMinutes": 60,

"SeedAcceptableBytesDiff": 8192, "AutoPseudoGTID":true, "PseudoGTIDPattern": "drop view if exists

`meta`.`_pseudo_gtid_hintasc:", "PseudoGTIDPatternIsFixedSubstring": true, "PseudoGTIDMonotonicHint": "asc:", "DetectPseudoGTIDQuery": "select count(*) as pseudo_gtid_exists from meta.pseudo_gtid_status where anchor = 1 and time_generated > now() - interval 2 hour", "BinlogEventsChunkSize": 10000,

 

"SkipBinlogEventsContaining": [], "ReduceReplicationAnalysisCount": true, "FailureDetectionPeriodBlockMinutes": 60,

"RecoveryPeriodBlockSeconds": 31, "RecoveryIgnoreHostnameFilters": [], "RecoverMasterClusterFilters": ["*"], "RecoverIntermediateMasterClusterFilters": ["*"], "OnFailureDetectionProcesses": ["echo ' Detected

{failureType} on {failureCluster}. Affected replicas:

{countSlaves}' >> /tmp/recovery.log"], "PreGracefulTakeoverProcesses": ["echo ' Planned takeover about to take place on {failureCluster}. Master will switch to read_only' >> /tmp/recovery.log"], "PreFailoverProcesses": ["echo ' Will recover from

{failureType} on {failureCluster}' >> /tmp/recovery.log"], "PostFailoverProcesses": ["echo ' (for all types) Recovered from {failureType} on {failureCluster}. Failed:

{failedHost}:{failedPort}; Successor: {successorHost}:

{successorPort}; failureClusterAlias:

{failureClusterAlias}' >>

/tmp/recovery.log","/usr/local/orchestrator/orch_hook.sh

{failureType} {failureClusterAlias} {failedHost}

{successorHost} >> /tmp/orch.log"], "PostUnsuccessfulFailoverProcesses": [ "echo ' Unsuccessful Failover ' >> /tmp/recovery.log"], "PostMasterFailoverProcesses": ["echo ' Recovered from

{failureType} on {failureCluster}. Failed: {failedHost}:

{failedPort}; Promoted: {successorHost}:{successorPort}'

>>/tmp/recovery.log"], "PostIntermediateMasterFailoverProcesses": ["echo ' Recovered from {failureType} on {failureCluster}. Failed:

{failedHost}:{failedPort}; Successor: {successorHost}:

{successorPort}' >> /tmp/recovery.log"], "PostGracefulTakeoverProcesses": ["echo ' Planned takeover complete' >> /tmp/recovery.log"], "CoMasterRecoveryMustPromoteOtherCoMaster": true, "DetachLostSlavesAfterMasterFailover": true, "ApplyMySQLPromotionAfterMasterFailover": true, "PreventCrossDataCenterMasterFailover": false, "MasterFailoverDetachSlaveMasterHost": false, "MasterFailoverLostInstancesDowntimeMinutes": 0,

 

 

 

1. 启动orch

 

 

i. 配置自动切换脚本

 

 

1. 免交互管理orch

 

 

 

4、MySQL InnoDB Cluster (MIC)应用

 

4.1 介绍

 

 

 

 

 

4.2 构建过程

4.2.1 主机 规划列表

 

IP	HOSTNAME	主机角色	安装软件
10.0.0.51	master	mic-master	mysql、mysqlsh
10.0.0.52	slave1	mic-slave1	mysql、mysqlsh
10.0.0.53	slave2	mic-slave2	mysql、mysqlsh
10.0.0.54	manager	mic-manager	mysqlsh、mysql-router

 

4.2.2 准备MIC基础环境

 

准备三台虚拟机并上传软件

 

 

准备三台数据库节点（51、52、53）

 

 

## master节点

[root@master app]# cat > /etc/my.cnf <<EOF [client]

socket=/tmp/mysql.sock [mysqld]

user=mysql basedir=/usr/local/mysql datadir=/data/3306/data log_bin=/data/3306/binlog server_id=151 socket=/tmp/mysql.sock gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE log_slave_updates=ON

 

tmpdir = /tmp

default-storage-engine=INNODB disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE

max_connections=500 max_allowed_packet=32M

 

default_authentication_plugin=mysql_native_password binlog_checksum=NONE binlog_transaction_dependency_tracking = WRITESET transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="ca842376-1c50-42ac-bb57-a5adc7da7a12"

loose-group_replication_start_on_boot=OFF

loose-group_replication_local_address= "master:33061" loose-group_replication_group_seeds= "master:33061,slave1:33062,slave2:33063"

loose-group_replication_bootstrap_group=OFF loose-

group_replication_ip_whitelist="master,slave1,slave2,manag er"

EOF

## slave1 节点

[root@slave1 data]# cat >/etc/my.cnf <<EOF

 

[client] socket=/tmp/mysql.sock [mysqld]

user=mysql basedir=/usr/local/mysql datadir=/data/3306/data log_bin=/data/3306/binlog server_id=152 socket=/tmp/mysql.sock

 

gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE log_slave_updates=ON

tmpdir = /tmp

default-storage-engine=INNODB disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE

max_connections=500 max_allowed_packet=16M

 

default_authentication_plugin=mysql_native_password binlog_checksum=NONE binlog_transaction_dependency_tracking = WRITESET transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="ca842376-1c50-42ac-bb57-a5adc7da7a12"

loose-group_replication_start_on_boot=OFF

loose-group_replication_local_address= "slave1:33062" loose-group_replication_group_seeds= "master:33061,slave1:33062,slave2:33063"

loose-group_replication_bootstrap_group=OFF loose-

group_replication_ip_whitelist="master,slave1,slave2,manag er"

loose-group_replication_allow_local_disjoint_gtids_join=ON EOF

 

## slave2 节点

[root@slave2 data]# cat > /etc/my.cnf <<EOF

 

[client] socket=/tmp/mysql.sock [mysqld]

user=mysql basedir=/usr/local/mysql datadir=/data/3306/data log_bin=/data/3306/binlog server_id=153 socket=/tmp/mysql.sock gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE log_slave_updates=ON

tmpdir = /tmp

default-storage-engine=INNODB disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE

max_connections=200 max_allowed_packet=16M

 

default_authentication_plugin=mysql_native_password binlog_checksum=NONE binlog_transaction_dependency_tracking = WRITESET transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="ca842376-1c50-42ac-bb57-a5adc7da7a12"

loose-group_replication_start_on_boot=OFF

loose-group_replication_local_address= "slave2:33063" loose-group_replication_group_seeds= "master:33061,slave1:33062,slave2:33063"

loose-group_replication_bootstrap_group=OFF loose-

group_replication_ip_whitelist="master,slave1,slave2,manag er"

loose-group_replication_allow_local_disjoint_gtids_join=ON EOF

 

4.2.3 所有数据库节点初始化集群实例

设置root@'localhost'密码

 

 

 

初始化配置集群实例(51，52，53)

 

 

 

 

4.2.4 创建集群并添加节点（54）

创建集群并添加主节点

 

 

添加slave1\slave2节点

 

 

 

配置启动 mysql-router（154）

 

 

 

验证连接router

 

 

验证cluster集群

 

 

可能遇到的问题

 

 

4.3 MIC集群常用管理命令汇总

 

4.3.1 信息查询

 

 

4.3.2 集群创建及节点管理

 

 

 

 

4.3.3 集群故障处理