MySQL学习笔记
文章目录
- 1 什么是高可用
- 2 数据库容灾级别
- 3 MHA高可用技术
- 3.1 工作原理
- 3.2 MHA高可用架构介绍及搭建过程
- 3.3 MHA软件结构介绍
- 3.4 应用透明-VIP
- 3.5 故障提醒功能
- 3.6 故障半自愈
- 3.7 日志补偿的冗余方案--binlog_server
- 3.8 MHA的维护操作 - 在线切换功能
- 3.9 MHA如何防止脑裂
- 3.10 MHA的数据一致性保证
- 4 ProxySQL读写分离中间件
- 4.1 介绍
- 4.2 基本架构-高可用+读写分离技术
- 4.3 安装ProxySQL
- 4.5 ProxySQL管理接口的多层配置关系
- 4.6 在不同层次间移动配置
- 4.7 ProxySQL应用—基于SQL的读写分离
- 4.8 ProxySQL应用扩展—花式路由规则
- 5 ORCH可视化高可用集群应用
- 5.1 ORCH 介绍
- 5.2 部署
- 5.2.1 节点规划
- 5.2.2 获取软件和相关脚本
- 5.2.3 安装软件
- 5.2.4 配置orch 后端数据库及用户(所有3306节点)
- 5.2.5 配置被管理节点主从关系(3307)
- 5.2.6 配置被管理节点用户（主库）
- 5.2.7 orch配置文件定制
- 5.2.8 启动orch
- 5.2.9 配置自动切换脚本
- 5.2.10 免交互管理orch
- 6 MySQL InnoDB Cluster (MIC)应用
- 6.1 介绍
- 6.2 构建过程
- 6.2.1 主机规划
- 6.2.2 准备MIC基础环境
- 6.2.3 所有数据库节点初始化集群实例
- 6.2.4 创建集群并添加节点（54）
- 6.3 MIC集群常用管理命令汇总
- 6.3.1 信息查询
- 6.3.2 集群创建及节点管理
- 6.3.3 集群故障处理
- 7 基于Xenon的MySQL高可用技术落地实战
- 7.1 Xenon介绍
- 7.2 代码托管
- 7.3 Xenon的架构
- 7.4 数据库节点规划
- 7.5 基础环境准备
- 7.5.1 配置MySQL主从环境
- 7.5.2 安装PXB
- 7.5.3 配置主机名和互信
- 7.5.4 安装Go环境
- 7.6 Xenon的安装及配置
- 7.6.1 安装Xenon软件
- 7.6.2 配置Xenon
- 7.6.3 配置集群节点
1 什么是高可用
企业高可用标准：全年无故障率
无故障时间 故障时间
99.9% 0.1% = 525.6 min KA+双主 ：人为干预
99.99% 0.01% = 52.56 min MHA 、RM、 consul+zk、ORCH、
xenon ：半自动化，都要配合5.7+GTID+增强半同步
99.999% 0.001% = 5.256 min PXC 、 MGR 、MIC、MGC
99.9999% 0.0001% = 0.5256 min 自动化、云化、平台化、分布式
2 数据库容灾级别
RTO (Recovery Time Objective，复原时间目标)是企业可容许服务中断的时间长度。比如说灾难发生后半天内便需要恢复，RTO值就是十二小时； RPO (Recovery Point Objective，复原点目标)是指当服务恢复后，恢复得来的数据所对应时的间点。
3 MHA高可用技术
3.1 工作原理
3.2 MHA高可用架构介绍及搭建过程
#1. 规划: 主库: 51 node 从库: 52 node 53 node manager #2. 准备环境 1主2从GTID #3. 配置关键程序软连接 ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql #4. 配置各节点互信(各节点之间无密码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 0.56 0.57 0.58 ## 所有节点安装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-ParallelForkManager 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 [server2] hostname=10.0.0.52 candidate_master=1 port=3306 [server3] hostname=10.0.0.53 port=3306 # 状态检查 ## 互信检查 masterha_check_ssh --conf=/etc/mha/app1.cnf ## 主从状态检查 masterha_check_repl --conf=/etc/mha/app1.cnf # 开启MHA(db03)： 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 & # 查看MHA状态 [root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
3.3 MHA软件结构介绍
manager 组件 masterha_manger 启动MHA masterha_check_ssh 检查MHA的SSH配置状况 masterha_check_repl 检查MySQL复制状况 masterha_master_monitor 检测master是否宕机 masterha_check_status 检测当前MHA运行状态 masterha_master_switch 控制故障转移（自动或者手动） masterha_conf_host 添加或删除配置的server信息 node 组件 save_binary_logs 保存和复制master的二进制日志 apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的 purge_relay_logs 清除中继日志（不会阻塞SQL线程）
3.4 应用透明-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_failover 重启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 效果测试 使用navicat 连接测试MHA vip功能。 生产注意： 1. 自带脚本，不能跨网段的，必须在局域网中应用。打通二层网络。而且不是特别稳定。 2. 如果需要跨网段，推荐使用智能DNS。 3. 可以配合ProxySQL类似的中间件，就可以不使用VIP了。
3.5 故障提醒功能
准备脚本 [root@db03 bin]# cp send_report send_report.bak1 my $smtp='smtp.qq.com'; # smtp服务器 my $mail_from='79522606@qq.com'; # 发件箱 my $mail_user='79522606'; # 用户名 QQ号 my $mail_pass='uijozwzxlpoicafj'; # 授权码 my $mail_to=['22654481@qq.com']; # 收件箱 #my $mail_to=['to1@qq.com','to2@qq.com']; 修改配置文件 vim /etc/mha/app1.cnf # 添加一行： report_script=/usr/local/bin/send_report 重启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 &
3.6 故障半自愈
# 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:" 5. 添加节点至配置文件 masterha_conf_host --command=add --conf=/etc/mha/app1.cnf --hostname=10.0.0.51 --block=server10 --params="port=3306" 6. 修复binlogserver mysqlbinlog -R --host=10.0.0.52 --user=mha --password=mha --raw --stop-never mysql-bin.000003 & 7. 状态检查 ## 互信检查 masterha_check_ssh --conf=/etc/mha/app1.cnf ## 主从状态检查 masterha_check_repl --conf=/etc/mha/app1.cnf 8. 启动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 扩展作业： 写脚本实现半自愈功能。
3.7 日志补偿的冗余方案--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 binlog_server]# masterha_check_status --conf=/etc/mha/app1.cnf app1 (pid:7057) is running(0:PING_OK), master:10.0.0.52 [root@db03 binlog_server]# mysqlbinlog -R --host=10.0.0.52 --user=mha --password=mha --raw --stop-never mysql-bin.000002 & 注意： 拉取日志的起点,需要按照目前从库的已经获取到的二进制日志点为起点 配置文件设置 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 & 注： binlog server不是100%可靠的。他模拟的是从库IO线程的功能（异步）。 替代方案： 5.7+GTID+增强半同步。
3.8 MHA的维护操作 - 在线切换功能
[root@db03 ~]# masterha_stop --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 注意： master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes 1. 此种方法 切换，要注意将原主库，FTWRL，否则会造成主从不一致。 2. 手工切换vip 3. 重新拉去新主库的binlog 4. 发邮件功能 master_ip_online_change_script功能实现 功能: 在线切换时，自动锁原主库，VIP自动切换 准备切换脚本 vim /usr/local/bin/master_ip_online_change my $vip = "10.0.0.55/24"; my $key = "1"; my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig ens33:$key $vip down"; my $ssh_Bcast_arp= "/sbin/arping -I ens33 -c 3 -A 10.0.0.55"; 修改MHA配置文件 vim /etc/mha/app1.cnf master_ip_online_change_script=/usr/local/bin/master_ip_online_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 & [root@db03 binlog_server]# masterha_check_status --conf=/etc/mha/app1.cnf app1 (pid:28535) is running(0:PING_OK), master:10.0.0.51
3.9 MHA如何防止脑裂
什么是脑裂？ 多个节点争抢主库角色。会导致多库写入的问题。 manager程序与现有主库之间出现网络故障。默认manager是单一心跳检查的。 解决方案： 1. 采用多心跳投票机制。例如：多条网络线路（以太网、磁盘网络）。 2. stonith是“shoot the other node in the head”的首字母简写 # shutdown_script 脚本 利用服务器的远程控制IDRAC等，使用ipmitool强制去关机，以避免fence设备重启主服务器，造成脑列现象. shutdown_script= /usr/local/bin/power_manager fence: 网络fence(ilo、idrac)、电源fence # secondary_check_script脚本 secondary_check_script = "masterha_secondary_check -s 10.0.0.52 -s 10.0.0.53"
3.10 MHA的数据一致性保证
#*************** rpl_semi_sync *************** rpl_semi_sync_master_enabled =ON rpl_semi_sync_master_timeout =5000 rpl_semi_sync_master_wait_for_slave_count =1 rpl_semi_sync_master_wait_no_slave =ON rpl_semi_sync_master_wait_point =AFTER_SYNC rpl_semi_sync_slave_enabled =ON #*************** group commit *************** binlog_group_commit_sync_delay =1 binlog_group_commit_sync_no_delay_count =1000 #*************** gtid *************** gtid_mode =ON enforce_gtid_consistency =ON log_slave_update =1 #*************** gtid *************** slave_parallel_type =LOGICAL_CLOCK slave_parallel_workers =4 master_info_repository =TABLE relay_log_info_repository =TABLE
4 ProxySQL读写分离中间件
4.1 介绍
ProxySQL是基于MySQL的一款开源的中间件的产品，是一个灵活的MySQL代理层，可以实现读写分离，支持Query路由功能，支持动态指定某个SQL进行缓存，支持动态加载配置信息（无需重启 ProxySQL 服务），支持故障切换和SQL的过滤功能。
相关 ProxySQL 的网站：
ProxySQL
https://github.com/sysown/proxysql/wiki
4.2 基本架构-高可用+读写分离技术
ProxySQLhttps://github.com/sysown/proxysql/wiki
4.3 安装ProxySQL
# 下载proxySQLProxySQLhttps://github.com/sysown/proxysql/releases # 安装proxySQL [root@db03 ~]# rpm -ivh proxysql-2.0.10-1-centos7.x86_64.rpm [root@db03 ~]# systemctl start proxysql [root@db03 ~]# netstat -tulnp tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 2115/proxysql tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 2115/proxysql [root@db03 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 补充： ProxySQL Cluster 高可用
4.4 ProxySQL中管理结构自带系统库
在ProxySQL，6032端口共五个库： main、disk、stats 、monitor、stats_history main: main 库中有如下信息： mysql_servers: 后端可以连接 MySQL 服务器的列表 mysql_users: 配置后端数据库的账号和监控的账号。 mysql_query_rules: 指定 Query 路由到后端不同服务器的规则列表。 mysql_replication_hostgroups : 节点分组配置信息 global_variables ：系统总配置 注： 表名以 runtime_开头的表示ProxySQL 当前运行的配置内容，不能直接修改。不带runtime_是下文图中Mem相关的配置。 disk ：持久化的磁盘的配置 stats：统计信息的汇总 monitor：监控的收集信息，比如数据库的健康状态等 stats_history: ProxySQL 收集的有关其内部功能的历史指标
4.5 ProxySQL管理接口的多层配置关系
整套配置系统分为三层： 顶层 RUNTIME 中间层 MEMORY （主要修改的配置表） 持久层 DISK 和 CFG FILE RUNTIME ： 代表 ProxySQL 当前正在使用的配置，无法直接修改此配置，必须要从下一层 （MEM层）“load” 进来。 MEMORY： MEMORY 层上面连接 RUNTIME 层，下面disk持久层。这层可以在线操作 ProxySQL 配置，随便修改，不会影响生产环境。确认正常之后在加载达到RUNTIME和持久化的磁盘上。修改方法： insert、update、delete、select。 DISK和CONFIG FILE： 持久化配置信息。重启时，可以从磁盘快速加载回来。
4.6 在不同层次间移动配置
为了将配置持久化到磁盘或者应用到 runtime，在管理接口下有一系列管理命令来实现它们。 1. user相关配置 ## MEM 加载到runtime LOAD MYSQL USERS TO RUNTIME; ## runtime 保存至 MEM SAVE MYSQL USERS TO MEMORY; ## disk 加载到 MEM LOAD MYSQL USERS FROM DISK; ## MEM 到 disk SAVE MYSQL USERS TO DISK; ## CFG 到 MEM LOAD MYSQL USERS FROM CONFIG 2. server 相关配置 ## MEM 加载到runtime LOAD MYSQL SERVERS TO RUNTIME; ## 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 3. 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 4. 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; ## CFG 到 MEM LOAD MYSQL VARIABLES FROM CONFIG 总结： 日常配置其实大部分时间在MEM配置，然后load到RUNTIME，然后SAVE到DIsk。cfg很少使用。 例如 ： load xxx to runtime; save xxx to disk; 注意： 只有load到 runtime 状态时才会验证配置。在保MEM或disk时，都不会发生任何警告或错误。当load到 runtime 时，如果出现错误，将恢复为之前保存得状态，这时可以去检查错误日志。
4.7 ProxySQL应用—基于SQL的读写分离
1. 在mysql_replication_hostgroup表中，配置读写组编号
insert into
mysql_replication_hostgroups
(writer_hostgroup, reader_hostgroup, comment)
values (10,20,'oldguo');
load mysql servers to runtime;
save mysql servers to disk;
db03 [main]>select * from mysql_replication_hostgroups\G
*************************** 1. row ***************************
writer_hostgroup: 10
reader_hostgroup: 20
check_type: read_only
comment: proxy
1 row in set (0.00 sec)
说明：
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;
2. 添加主机到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;
3. 创建监控用户，并开启监控
# 主库创建监控用户
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;
4.配置应用用户
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平台
5. 实用的读写规则
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply)
values (1,1,'^select.*for update$',10,1);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply)
values (2,1,'^select',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;
注： select … for update规则的rule_id必须要小于普通的select规则的rule_id，ProxySQL是根据rule_id的顺序进行规则匹配。
6. 测试读写分离
[root@db03 ~]# mysql -uroot -p123 -P 6033 -h 127.0.0.1 -e "begin;select@@server_id;commit"
[root@db03 ~]# mysql -uroot -p123 -P 6033 -h 127.0.0.1 -e "select@@server_id;"
db03 [(none)]>select * from stats_mysql_query_digest\G
https://proxysql.com/documentation/proxysql-read-write-split-howto
4.8 ProxySQL应用扩展—花式路由规则
1. 基于端口的路由
## 修改ProxySQL监听SQL流量的端口号，监听多端口上。
set mysql-interfaces='0.0.0.0:6033;0.0.0.0:6034';
save mysql variables to disk;
## 重启生效
systemctl restart proxysql
## 设定路由规则
delete from mysql_query_rules; # 为了测试，先清空已有规则
insert into mysql_query_rules(rule_id,active,proxy_port,destination_hostgroup,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字段即可)。
2. 基于用户的路由
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_hostgroup,apply) values(1,1,'writer',10,1),(2,1,'reader',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;
3. 配置规则将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;
5 ORCH可视化高可用集群应用
5.1 ORCH 介绍
ORCH可视化高可用集群应用 (ORCH+5.7.26+GTID+SEMISYNC+MTS+ProxySQL)
Orchestrator（orch）：go编写的MySQL高可用性和复制拓扑管理工具，支持复制拓扑结构的调整，自动故障转移和手动主从切换等。后端数据库用MySQL或SQLite存储元数据，并提供Web界面展示MySQL复制的拓扑关系及状态，通过Web可更改MySQL实例的复制关系和部分配置信息，同时也提供命令行和api接口，方便运维管理。相对比MHA来看最重要的是解决了管理节点的单点问题，其通过
raft协议保证本身的高可用。GitHub的一部分管理也在用该工具进行管理。
① 自动发现MySQL的复制拓扑，并且在web上展示。
② 重构复制关系，可以在web进行拖图来进行复制关系变更。
③ 检测主异常，并可以自动或手动恢复，通过Hooks进行自定义脚本。
④ 支持命令行和web界面管理复制。
5.2 部署
5.2.1 节点规划
根据以上表格安装软件、配置地址和主机名、初始化6个MySQL实例。 orch默认是用主机名来进行管理的，需要在mysql的配置文件里添加：report_host和report_port参 数。 [root@db01 ~]# mysql -V mysql Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL) [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.128 report_port=3306 mysqlx=0 EOF cat > /etc/my3307.cnf <<EOF [client] socket=/tmp/mysql3307.sock [mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/3307/data log_bin=/data/3307/binlog/mysql-bin server_id=7 socket=/tmp/mysql3307.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.128 report_port=3307 port=3307 mysqlx=0 EOF
5.2.2 获取软件和相关脚本
https://github.com/openark/orchestrator/releases
https://github.com/theTibi/orchestrator_vip
5.2.3 安装软件
yum install -y orchestrator-*.rpm
5.2.4 配置orch 后端数据库及用户(所有3306节点)
CREATE DATABASE IF NOT EXISTS orchestrator;
CREATE USER 'orchestrator'@'127.0.0.1' IDENTIFIED with mysql_native_password BY '123456';
GRANT ALL ON orchestrator.* TO 'orchestrator'@'127.0.0.1';
5.2.5 配置被管理节点主从关系(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.128',master_port=3307,master_user='repl',master_password='123456',master_auto_position=1,MASTER_HEARTBEAT_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,参数定义了从库从主库获取数据等待的秒数，超过这个时间从库会主动退出读
取，中断连接，并尝试重连。
5.2.6 配置被管理节点用户（主库）
CREATE USER 'orchestrator'@'%' IDENTIFIED with mysql_native_password BY 'Aa123456';
GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO 'orchestrator'@'%';
GRANT SELECT ON mysql.slave_master_info TO 'orchestrator'@'%';
GRANT SELECT ON meta.* TO 'orchestrator'@'%';
5.2.7 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_hint__asc:",
"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,
"PostponeSlaveRecoveryOnLagMinutes": 0,
"OSCIgnoreHostnameFilters": [],
"GraphiteAddr": "",
"GraphitePath": "",
"GraphiteConvertHostnameDotsToUnderscores": true,
"RaftEnabled": true,
"BackendDB": "mysql",
"RaftBind": "10.0.0.51",
"RaftDataDir": "/usr/local/orchestrator",
"DefaultRaftPort": 10008,
"RaftNodes": ["10.0.0.51","10.0.0.52","10.0.0.53"],
"ConsulAddress": "",
"ConsulAclToken": ""
}
5.2.8 启动orch
cd /usr/local/orchestrator && nohup ./orchestrator --config=/etc/orchestrator.conf.json http &
5.2.9 配置自动切换脚本
"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"],
5.2.10 免交互管理orch
# 查看集群信息
orchestrator-client -c clusters
# 手工发现节点
orchestrator-client -c discover -i db01:3307
# 忘记节点
orchestrator-client -c forget -i db01:3307
# 查看集群拓扑
orchestrator-client -c topology-tabulated -i db01:3307
# 手工切换主从关系
orchestrator-client -c graceful-master-takeover -a db01:3307 -d db02:3307
# 调整主从拓扑
orchestrator-client -c relocate -i db03:3307 -d db02:3307
6 MySQL InnoDB Cluster (MIC)应用
6.1 介绍
MySQL InnoDB集群提供了一个集成的，本地的，HA解决方案。Mysql Innodb Cluster是利用组复制的 paxos 协议，保障数据一致性，组复制支持单主模式和多主模式。
MySQL InnoDB集群由以下几部分组成：
- MySQL Servers with Group Replication：向集群的所有成员复制数据，同时提供容错、自动故障转移和弹性。MySQL Server 5.7.17或更高的版本。
- MySQL Router：确保客户端请求是负载平衡的，并在任何数据库故障时路由到正确的服务器。MySQLRouter 2.1.3或更高的版本。
- MySQL Shell：通过内置的管理API创建及管理Innodb集群。MySQL Shell 1.0.9或更高的版本。
yum install -y orchestrator-*.rpm
5.2.4 配置orch 后端数据库及用户(所有3306节点)
CREATE DATABASE IF NOT EXISTS orchestrator;
CREATE USER 'orchestrator'@'127.0.0.1' IDENTIFIED with mysql_native_password BY '123456';
GRANT ALL ON orchestrator.* TO 'orchestrator'@'127.0.0.1';
5.2.5 配置被管理节点主从关系(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.128',master_port=3307,master_user='repl',master_password='123456',master_auto_position=1,MASTER_HEARTBEAT_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,参数定义了从库从主库获取数据等待的秒数，超过这个时间从库会主动退出读
取，中断连接，并尝试重连。
5.2.6 配置被管理节点用户（主库）
CREATE USER 'orchestrator'@'%' IDENTIFIED with mysql_native_password BY 'Aa123456';
GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO 'orchestrator'@'%';
GRANT SELECT ON mysql.slave_master_info TO 'orchestrator'@'%';
GRANT SELECT ON meta.* TO 'orchestrator'@'%';
5.2.7 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_hint__asc:",
"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,
"PostponeSlaveRecoveryOnLagMinutes": 0,
"OSCIgnoreHostnameFilters": [],
"GraphiteAddr": "",
"GraphitePath": "",
"GraphiteConvertHostnameDotsToUnderscores": true,
"RaftEnabled": true,
"BackendDB": "mysql",
"RaftBind": "10.0.0.51",
"RaftDataDir": "/usr/local/orchestrator",
"DefaultRaftPort": 10008,
"RaftNodes": ["10.0.0.51","10.0.0.52","10.0.0.53"],
"ConsulAddress": "",
"ConsulAclToken": ""
}
5.2.8 启动orch
cd /usr/local/orchestrator && nohup ./orchestrator --config=/etc/orchestrator.conf.json http &
5.2.9 配置自动切换脚本
"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"],
5.2.10 免交互管理orch
# 查看集群信息
orchestrator-client -c clusters
# 手工发现节点
orchestrator-client -c discover -i db01:3307
# 忘记节点
orchestrator-client -c forget -i db01:3307
# 查看集群拓扑
orchestrator-client -c topology-tabulated -i db01:3307
# 手工切换主从关系
orchestrator-client -c graceful-master-takeover -a db01:3307 -d db02:3307
# 调整主从拓扑
orchestrator-client -c relocate -i db03:3307 -d db02:3307
6 MySQL InnoDB Cluster (MIC)应用
6.1 介绍
MySQL InnoDB集群提供了一个集成的，本地的，HA解决方案。Mysql Innodb Cluster是利用组复制的 paxos 协议，保障数据一致性，组复制支持单主模式和多主模式。
MySQL InnoDB集群由以下几部分组成：
- MySQL Servers with Group Replication：向集群的所有成员复制数据，同时提供容错、自动故障转移和弹性。MySQL Server 5.7.17或更高的版本。
- MySQL Router：确保客户端请求是负载平衡的，并在任何数据库故障时路由到正确的服务器。MySQLRouter 2.1.3或更高的版本。
- MySQL Shell：通过内置的管理API创建及管理Innodb集群。MySQL Shell 1.0.9或更高的版本。
# 主库 CREATE USER 'repl'@'%' IDENTIFIED with mysql_native_password BY '123456'; GRANT replication slave on *.* TO 'repl'@'%'; # 从库 change master to master_host='10.0.0.128',master_port=3307,master_user='repl',master_password='123456',master_auto_position=1,MASTER_HEARTBEAT_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,参数定义了从库从主库获取数据等待的秒数，超过这个时间从库会主动退出读 取，中断连接，并尝试重连。
5.2.6 配置被管理节点用户（主库）
CREATE USER 'orchestrator'@'%' IDENTIFIED with mysql_native_password BY 'Aa123456';
GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO 'orchestrator'@'%';
GRANT SELECT ON mysql.slave_master_info TO 'orchestrator'@'%';
GRANT SELECT ON meta.* TO 'orchestrator'@'%';
5.2.7 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_hint__asc:",
"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,
"PostponeSlaveRecoveryOnLagMinutes": 0,
"OSCIgnoreHostnameFilters": [],
"GraphiteAddr": "",
"GraphitePath": "",
"GraphiteConvertHostnameDotsToUnderscores": true,
"RaftEnabled": true,
"BackendDB": "mysql",
"RaftBind": "10.0.0.51",
"RaftDataDir": "/usr/local/orchestrator",
"DefaultRaftPort": 10008,
"RaftNodes": ["10.0.0.51","10.0.0.52","10.0.0.53"],
"ConsulAddress": "",
"ConsulAclToken": ""
}
5.2.8 启动orch
cd /usr/local/orchestrator && nohup ./orchestrator --config=/etc/orchestrator.conf.json http &
5.2.9 配置自动切换脚本
"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"],
5.2.10 免交互管理orch
# 查看集群信息
orchestrator-client -c clusters
# 手工发现节点
orchestrator-client -c discover -i db01:3307
# 忘记节点
orchestrator-client -c forget -i db01:3307
# 查看集群拓扑
orchestrator-client -c topology-tabulated -i db01:3307
# 手工切换主从关系
orchestrator-client -c graceful-master-takeover -a db01:3307 -d db02:3307
# 调整主从拓扑
orchestrator-client -c relocate -i db03:3307 -d db02:3307
6 MySQL InnoDB Cluster (MIC)应用
6.1 介绍
MySQL InnoDB集群提供了一个集成的，本地的，HA解决方案。Mysql Innodb Cluster是利用组复制的 paxos 协议，保障数据一致性，组复制支持单主模式和多主模式。
MySQL InnoDB集群由以下几部分组成：
- MySQL Servers with Group Replication：向集群的所有成员复制数据，同时提供容错、自动故障转移和弹性。MySQL Server 5.7.17或更高的版本。
- MySQL Router：确保客户端请求是负载平衡的，并在任何数据库故障时路由到正确的服务器。MySQLRouter 2.1.3或更高的版本。
- MySQL Shell：通过内置的管理API创建及管理Innodb集群。MySQL Shell 1.0.9或更高的版本。
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_hint__asc:",
"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,
"PostponeSlaveRecoveryOnLagMinutes": 0,
"OSCIgnoreHostnameFilters": [],
"GraphiteAddr": "",
"GraphitePath": "",
"GraphiteConvertHostnameDotsToUnderscores": true,
"RaftEnabled": true,
"BackendDB": "mysql",
"RaftBind": "10.0.0.51",
"RaftDataDir": "/usr/local/orchestrator",
"DefaultRaftPort": 10008,
"RaftNodes": ["10.0.0.51","10.0.0.52","10.0.0.53"],
"ConsulAddress": "",
"ConsulAclToken": ""
}
5.2.8 启动orch
cd /usr/local/orchestrator && nohup ./orchestrator --config=/etc/orchestrator.conf.json http &
5.2.9 配置自动切换脚本
"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"],
5.2.10 免交互管理orch
# 查看集群信息
orchestrator-client -c clusters
# 手工发现节点
orchestrator-client -c discover -i db01:3307
# 忘记节点
orchestrator-client -c forget -i db01:3307
# 查看集群拓扑
orchestrator-client -c topology-tabulated -i db01:3307
# 手工切换主从关系
orchestrator-client -c graceful-master-takeover -a db01:3307 -d db02:3307
# 调整主从拓扑
orchestrator-client -c relocate -i db03:3307 -d db02:3307
6 MySQL InnoDB Cluster (MIC)应用
6.1 介绍
MySQL InnoDB集群提供了一个集成的，本地的，HA解决方案。Mysql Innodb Cluster是利用组复制的 paxos 协议，保障数据一致性，组复制支持单主模式和多主模式。
MySQL InnoDB集群由以下几部分组成：
- MySQL Servers with Group Replication：向集群的所有成员复制数据，同时提供容错、自动故障转移和弹性。MySQL Server 5.7.17或更高的版本。
- MySQL Router：确保客户端请求是负载平衡的，并在任何数据库故障时路由到正确的服务器。MySQLRouter 2.1.3或更高的版本。
- MySQL Shell：通过内置的管理API创建及管理Innodb集群。MySQL Shell 1.0.9或更高的版本。
"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"],
5.2.10 免交互管理orch
# 查看集群信息
orchestrator-client -c clusters
# 手工发现节点
orchestrator-client -c discover -i db01:3307
# 忘记节点
orchestrator-client -c forget -i db01:3307
# 查看集群拓扑
orchestrator-client -c topology-tabulated -i db01:3307
# 手工切换主从关系
orchestrator-client -c graceful-master-takeover -a db01:3307 -d db02:3307
# 调整主从拓扑
orchestrator-client -c relocate -i db03:3307 -d db02:3307
6 MySQL InnoDB Cluster (MIC)应用
6.1 介绍
MySQL InnoDB集群提供了一个集成的，本地的，HA解决方案。Mysql Innodb Cluster是利用组复制的 paxos 协议，保障数据一致性，组复制支持单主模式和多主模式。
MySQL InnoDB集群由以下几部分组成：
- MySQL Servers with Group Replication：向集群的所有成员复制数据，同时提供容错、自动故障转移和弹性。MySQL Server 5.7.17或更高的版本。
- MySQL Router：确保客户端请求是负载平衡的，并在任何数据库故障时路由到正确的服务器。MySQLRouter 2.1.3或更高的版本。
- MySQL Shell：通过内置的管理API创建及管理Innodb集群。MySQL Shell 1.0.9或更高的版本。
6.1 介绍
MySQL InnoDB集群提供了一个集成的，本地的，HA解决方案。Mysql Innodb Cluster是利用组复制的 paxos 协议，保障数据一致性，组复制支持单主模式和多主模式。 MySQL InnoDB集群由以下几部分组成： - MySQL Servers with Group Replication：向集群的所有成员复制数据，同时提供容错、自动故障转移和弹性。MySQL Server 5.7.17或更高的版本。 - MySQL Router：确保客户端请求是负载平衡的，并在任何数据库故障时路由到正确的服务器。MySQLRouter 2.1.3或更高的版本。 - MySQL Shell：通过内置的管理API创建及管理Innodb集群。MySQL Shell 1.0.9或更高的版本。
6.2 构建过程
6.2.1 主机规划
6.2.2 准备MIC基础环境
准备三台虚拟机并上传软件
# 按照规划列表，将所有软件解压。 tar xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz tar xf mysql-shell-8.0.21-linux-glibc2.12-x86-64bit.tar.gz tar xf mysql-router-8.0.21-linux-glibc2.12-x86_64.tar.xz ln -s mysql-8.0.20-linux-glibc2.12-x86_64 mysql ln -s mysql-shell-8.0.21-linux-glibc2.12-x86-64bit.tar.gz mysqlsh ln -s mysql-shell-8.0.21-linux-glibc2.12-x86-64bit.tar.gz mysql-router # 配置环境遍变量 export PATH=/usr/local/mysql/bin:/usr/local/mysqlsh/bin:/usr/local/mysqlrouter/bin:$PATH # 按照规划列表，将所有主机名和hosts文件全部解析。 cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 10.0.0.51 master 10.0.0.52 slave1 10.0.0.53 slave2 10.0.0.54 manage # 将所有节点Firewalld和SELINUX关闭。 略。
准备三台数据库节点（51、52、53）
#清理环境，并重新初始化数据 pkill mysqld pkill mysqlsh rm -rf /data/3306/data /data/3306/binlog* /etc/my.cnf mkdir /data/3306/data /data/3306/binlog mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql -- datadir=/data/3306/data #配置文件准备 ## 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,manager" 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,manager" 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,manager" loose-group_replication_allow_local_disjoint_gtids_join=ON EOF
6.2.3 所有数据库节点初始化集群实例
设置root@'localhost'密码
mysql> alter user root@'localhost' identified with mysql_native_password by '123';
初始化配置集群实例(51，52，53)
mysqlsh
mysql-js> shell.connect('root@localhost:3306');
mysql-js> dba.configureLocalInstance(); #见下图。
mysql-js> dba.checkInstanceConfiguration("root@localhost:3306");# 见下图。
6.2.4 创建集群并添加节点（54）
创建集群并添加主节点
mysqlsh
mysql-js> shell.connect('root@master:3306');
mysql-js> var cluster = dba.createCluster('oldguo');
mysql-js> cluster.status();
添加slave1\slave2节点
mysqlsh
mysql-js> shell.connect('root@master:3306');
mysql-js> var cluster = dba.getCluster('oldguo');
mysql-js> cluster.addInstance('root@slave1:3306');
mysql-js> cluster.addInstance('root@slave2:3306');
mysql-js> cluster.status();
配置启动 mysql-router（154）
# 注册router到集群，生成myrouter目录, 并生成启动程序和配置文件. mysqlrouter --bootstrap root@master:3306 -d myrouter --user=root # 启动myrouter myrouter/start.sh
验证连接router
a) 管理节点本机mysql-shell连接: mysqlsh --uri root@localhost:6446 b) 管理节点本机mysql连接: mysql -u root -h 127.0.0.1 -P 6446 -p c) 远程客户机通过route连接mysql mysql -u root -h manager_ip -P 6446 -p
验证cluster集群
1.登陆后，新建一个表，往里面写进数据，查看从节点数据会不会同步； 2.关闭master的mysql服务，route将主节点自动切换到slave1，slave1从只读变为可读写，重新启动master mysql后，master变为只读模式。 3. 验证读写分离功能
可能遇到的问题
主节点： mysqlshell 清空集群 dba.dropMetadataSchema() mysql> stop group_replication; mysql> reset master; （清空日志，确保和从库的表没有冲突奥，） mysql> reset slave 其他节点(主要清理和主库的主从信息， 确保主库和从库的表没有冲突奥) mysql> stop group_replication; mysql> reset master; mysql> reset slave
6.3 MIC集群常用管理命令汇总
6.3.1 信息查询
# 检查节点是否符合集群标准
dba.checkInstanceConfiguration()
# 集群结构信息描述
cluster.describe()
# 集群状态总览
cluster.status()
# 查看集群名字
dba.getCluster();
#查看router信息
cluster.listRouters()
6.3.2 集群创建及节点管理
# dba.createCluster()
例：
var cluster = dba.createCluster('oldguo');
# Cluster.addInstance()
例：
cluster.addInstance('root@slave1:3306');
# Cluster.removeInstance()
例：
cluster.removeInstance('root@slave1:3306');
# Cluster.rejoinInstance()
如果实例离开集群，比如丢失连接，并且没有自动重新加入集群。可以通过cluster.rejoinInstance()方
法将实例重新加入到集群中。
# Cluster.setPrimaryInstance(instance)
在线切换primary 实例。
例：
cluster.setPrimaryInstance('root@slave1:3306')
cluster.status()
cluster.setPrimaryInstance('root@master:3306')
# cluster.switchToMultiPrimaryMode()
切换为多primary模式。
# cluster.switchToSinglePrimaryMode('root@master:3306')
切换为单primary模式。
注：在切换为多 primary 模式后，6447端口（默认只读）接收读写，并且可通过该端口访问所有集群成员。而6446端口（默认读写）只能连接到其中一个成员（之前的primary成员）。
6.3.3 集群故障处理
# dba.rebootClusterFromCompleteOutage()
用于在集群完全断电后重新配置集群。如果dba.rebootClusterFromCompleteOutage() 方法失败，可以通过一下方式删除所有集群元数据。
# dba.dropMetadataSchema()
删除集群元数据，然后dba.createCluster() 重建集群。
#删除已注册的router
Cluster.removeRouterMetadata(router)
7 基于Xenon的MySQL高可用技术落地实战
7.1 Xenon介绍
QingCloud 数据库开源MySQL高可用项目，go语言开发。使用Raft协议实现Failover故障检测，目前支持MySQL5.6 、5.7、 8.0版本。
7.2 代码托管
https://github.com/radondb/xenon
7.3 Xenon的架构
# dba.createCluster()
例：
var cluster = dba.createCluster('oldguo');
# Cluster.addInstance()
例：
cluster.addInstance('root@slave1:3306');
# Cluster.removeInstance()
例：
cluster.removeInstance('root@slave1:3306');
# Cluster.rejoinInstance()
如果实例离开集群，比如丢失连接，并且没有自动重新加入集群。可以通过cluster.rejoinInstance()方
法将实例重新加入到集群中。
# Cluster.setPrimaryInstance(instance)
在线切换primary 实例。
例：
cluster.setPrimaryInstance('root@slave1:3306')
cluster.status()
cluster.setPrimaryInstance('root@master:3306')
# cluster.switchToMultiPrimaryMode()
切换为多primary模式。
# cluster.switchToSinglePrimaryMode('root@master:3306')
切换为单primary模式。
注：在切换为多 primary 模式后，6447端口（默认只读）接收读写，并且可通过该端口访问所有集群成员。而6446端口（默认读写）只能连接到其中一个成员（之前的primary成员）。
6.3.3 集群故障处理
# dba.rebootClusterFromCompleteOutage()
用于在集群完全断电后重新配置集群。如果dba.rebootClusterFromCompleteOutage() 方法失败，可以通过一下方式删除所有集群元数据。
# dba.dropMetadataSchema()
删除集群元数据，然后dba.createCluster() 重建集群。
#删除已注册的router
Cluster.removeRouterMetadata(router)
7 基于Xenon的MySQL高可用技术落地实战
7.1 Xenon介绍
QingCloud 数据库开源MySQL高可用项目，go语言开发。使用Raft协议实现Failover故障检测，目前支持MySQL5.6 、5.7、 8.0版本。
7.2 代码托管
https://github.com/radondb/xenon
7.3 Xenon的架构
7.1 Xenon介绍
QingCloud 数据库开源MySQL高可用项目，go语言开发。使用Raft协议实现Failover故障检测，目前支持MySQL5.6 、5.7、 8.0版本。
7.2 代码托管
https://github.com/radondb/xenon
7.3 Xenon的架构
7.4 数据库节点规划
7.5 基础环境准备
7.5.1 配置MySQL主从环境
# 1 清理环境
pkill mysqld
\rm -rf /data/3306/data/*
\rm -rf /data/3306/binlog/*
\mv /etc/my.cnf /tmp
mkdir -p /data/3306/data /data/3306/binlog/
chown -R mysql.mysql /data/*
# 2 准备配置文件
主库db01：
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql/
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=oldguo-db01 [\\d]>
EOF
slave1(db02)：
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=oldguo-db02 [\\d]>
EOF
slave2(db03)：
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=oldguo-db03 [\\d]>
EOF
# 3 初始化数据
mysqld --initialize-insecure
# 4 启动数据库
/etc/init.d/mysqld start
# 5 所有节点配置加载半同步插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
# 6 构建主从：
master:51
slave:52,53
# 51:
create user repl@'10.0.0.%' identified with mysql_native_password by '123';
grant replication slave on *.* to repl@'10.0.0.%' ;
# 52\53:
change master to
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;
从库设置只读：
set global super_read_only=1;
set global read_only=1;
7.5.2 安装PXB
yum install -y percona-xtrabackup-80-8.0.13-1.el7.x86_64.rpm
7.5.3 配置主机名和互信
vim /etc/hosts
10.0.0.51 db01
10.0.0.52 db02
10.0.0.53 db03
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
7.5.4 安装Go环境
# 下载
https://golang.google.cn/dl/
# vim /etc/profile
export GOPATH=/opt/go/bin
export PATH=$PATH:$GOPATH
# source /etc/profile
[root@db01 bin]# go version
7.6 Xenon的安装及配置
7.6.1 安装Xenon软件
https://github.com/radondb/xenon/blob/master/docs/how_to_build_and_run_xenon.md
7.6.2 配置Xenon
mkdir -p /data/xenon
cp -r /opt/xenon-master/bin/ /data/xenon/
mkdir -p /etc/xenon
cp /opt/xenon-master/conf/xenon-sample.conf.json /etc/xenon/xenon.json
echo "/etc/xenon/xenon.json" > /data/xenon/bin/config.path
chown -R mysql:mysql /data/xenon/
chown -R mysql:mysql /etc/xenon/
[root@db01 ~]# vim /etc/xenon/xenon.json
cd /data/xenon/bin/
nohup /data/xenon/bin/xenon -c /etc/xenon/xenon.json > /data/xenon/xenon.log 2>&1 &
7.6.3 配置集群节点
./xenoncli cluster add 10.0.0.51:8801,10.0.0.52:8801,10.0.0.53:8801
MHA+proxysql+gtid+增强半同步+MTS
proxysql+gtid+增强半同步+MTS+ORCH
proxysql+gtid+增强半同步+MTS+Xenon
yum install -y percona-xtrabackup-80-8.0.13-1.el7.x86_64.rpm
7.5.3 配置主机名和互信
vim /etc/hosts
10.0.0.51 db01
10.0.0.52 db02
10.0.0.53 db03
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
7.5.4 安装Go环境
# 下载
https://golang.google.cn/dl/
# vim /etc/profile
export GOPATH=/opt/go/bin
export PATH=$PATH:$GOPATH
# source /etc/profile
[root@db01 bin]# go version
7.6 Xenon的安装及配置
7.6.1 安装Xenon软件
https://github.com/radondb/xenon/blob/master/docs/how_to_build_and_run_xenon.md
7.6.2 配置Xenon
mkdir -p /data/xenon
cp -r /opt/xenon-master/bin/ /data/xenon/
mkdir -p /etc/xenon
cp /opt/xenon-master/conf/xenon-sample.conf.json /etc/xenon/xenon.json
echo "/etc/xenon/xenon.json" > /data/xenon/bin/config.path
chown -R mysql:mysql /data/xenon/
chown -R mysql:mysql /etc/xenon/
[root@db01 ~]# vim /etc/xenon/xenon.json
cd /data/xenon/bin/
nohup /data/xenon/bin/xenon -c /etc/xenon/xenon.json > /data/xenon/xenon.log 2>&1 &
7.6.3 配置集群节点
./xenoncli cluster add 10.0.0.51:8801,10.0.0.52:8801,10.0.0.53:8801
MHA+proxysql+gtid+增强半同步+MTS
proxysql+gtid+增强半同步+MTS+ORCH
proxysql+gtid+增强半同步+MTS+Xenon
# 下载 https://golang.google.cn/dl/ # vim /etc/profile export GOPATH=/opt/go/bin export PATH=$PATH:$GOPATH # source /etc/profile [root@db01 bin]# go version
7.6 Xenon的安装及配置
7.6.1 安装Xenon软件
https://github.com/radondb/xenon/blob/master/docs/how_to_build_and_run_xenon.md
7.6.2 配置Xenon
mkdir -p /data/xenon
cp -r /opt/xenon-master/bin/ /data/xenon/
mkdir -p /etc/xenon
cp /opt/xenon-master/conf/xenon-sample.conf.json /etc/xenon/xenon.json
echo "/etc/xenon/xenon.json" > /data/xenon/bin/config.path
chown -R mysql:mysql /data/xenon/
chown -R mysql:mysql /etc/xenon/
[root@db01 ~]# vim /etc/xenon/xenon.json
cd /data/xenon/bin/
nohup /data/xenon/bin/xenon -c /etc/xenon/xenon.json > /data/xenon/xenon.log 2>&1 &
7.6.3 配置集群节点
./xenoncli cluster add 10.0.0.51:8801,10.0.0.52:8801,10.0.0.53:8801
MHA+proxysql+gtid+增强半同步+MTS
proxysql+gtid+增强半同步+MTS+ORCH
proxysql+gtid+增强半同步+MTS+Xenon
mkdir -p /data/xenon cp -r /opt/xenon-master/bin/ /data/xenon/ mkdir -p /etc/xenon cp /opt/xenon-master/conf/xenon-sample.conf.json /etc/xenon/xenon.json echo "/etc/xenon/xenon.json" > /data/xenon/bin/config.path chown -R mysql:mysql /data/xenon/ chown -R mysql:mysql /etc/xenon/ [root@db01 ~]# vim /etc/xenon/xenon.json cd /data/xenon/bin/ nohup /data/xenon/bin/xenon -c /etc/xenon/xenon.json > /data/xenon/xenon.log 2>&1 &
7.6.3 配置集群节点
./xenoncli cluster add 10.0.0.51:8801,10.0.0.52:8801,10.0.0.53:8801
MHA+proxysql+gtid+增强半同步+MTS
proxysql+gtid+增强半同步+MTS+ORCH
proxysql+gtid+增强半同步+MTS+Xenon