MySQL高可用解决方案（7）replication-manager数据库高可用
一、Replication-Manager介绍
Replication-Manager是MySQL高可用性解决方案中的一种，如果维护的数据库架构为一主一从2节点模式而又想要实现自动高可用，那么推荐Replication-Manager，其它高可用方案通常至少3节点才能正常工作。除此之外还有以下特点：
· 主从复制状态监控，如果当前存在长事务未执行完成会取消切换，不会切换到状态异常的节点
· 数据库拓扑检测
· 主从手动一键切换与故障自动转移（支持Web端操作）
· 大多数故障情况下不会丢失数据
· 能管理多套数据库集群
· 切换时原主库会设置READ_ONLY，并且添加FTWRL防止数据继续写入
· 支持读写分离中间件（ProxySQL、MaxScale、HAProxy、Spider）
二、安装Replication-Manager
1、使用YUM安装（推荐）
vi /etc/yum.repos.d/signal18.repo [signal18] name=Signal18 repositories baseurl=http://repo.signal18.io/centos/$releasever/$basearch/ gpgcheck=0 enabled=1 yum install replication-manager-osc #截至2024年1月，版本为2.3.15
2、使用二进制包安装
· 通过github下载二进制包
wget https://github.com/signal18/replication-manager/archive/refs/tags/v2.2.14.tar.gz
· 解压到合适的目录并创建软连接、服务管理脚本
tar zxvf replication-manager-2.2.14.tar.gz -C /usr/local/ ln -s /usr/local/replication-manager-2.2.14 /usr/local/replication-manager cp /usr/local/replication-manager/share/replication-manager.init /etc/init.d/replication-manager
三、Replication-Manager配置与管理（以YUM安装方式进行，如果是其他安装方式注意配置文件路径）
Replication-Manager最多检查master故障N次，如果触发故障条件默认行为是发送警报并将自己置于等待模式，直到用户强制故障转移或master修复。自动故障转移中可以进行相关约束，最大限度减少自动故障转移中丢失数据
1、主配置文件
Replication-Manager的主要配置文件为/etc/replication-manager/config.toml。该文件包含服务本身全局配置，还可以将每个集群的公有配置写入在 [Default] 模块中，该模块通过include引用不同集群的自定义配置
cp /etc/replication-manager/etc/config.toml.sample.masterslave-haproxy /etc/replication-manager/config.toml cat /etc/replication-manager/config.toml [Default] include = "/etc/replication-manager/cluster.d" monitoring-save-config = false monitoring-datadir = "/var/lib/replication-manager" #monitoring-sharedir = "/usr/share/replication-manager" #监控间隔，单位为秒，这里实际影响的是控制台日志输出的间隔以及每次故障判断的间隔 monitoring-ticker = 5 monitoring-performance-schema = false monitoring-processlist = false monitoring-queries = false ######### ## LOG ## ######### log-file = "/var/log/replication-manager.log" log-heartbeat = false log-syslog = false ################# ## 仲裁配置 ## ################# #arbitration-external = false #arbitration-external-secret = "13787932529099014144" #arbitration-external-hosts = "88.191.151.84:80" #arbitration-peer-hosts ="127.0.0.1:10002" #arbitration-external-unique-id = 0 ########## ## HTTP ## ########## http-server = true http-bind-address = "0.0.0.0" http-port = "10001" http-auth = false http-session-lifetime = 3600 http-bootstrap-button = false http-refresh-interval = 30000 ######### ## API ## ######### api-credentials = "admin:repman" api-port = "10005" api-https-bind = false api-credentials-acl-allow = "admin:cluster proxy db prov,dba:cluster proxy db,foo:" api-credentials-acl-discard = false api-credentials-external = "dba:repman,foo:bar" ############ ## ALERTS ## ############ mail-from = "replication-manager@localhost" mail-smtp-addr = "localhost:25" mail-to = "replication-manager@signal18.io" mail-smtp-password="" mail-smtp-user="" alert-slack-channel = "#support" alert-slack-url = "" alert-slack-user = "svar" ########## # STATS ## ########## #graphite-metrics = false #graphite-carbon-host = "127.0.0.1" #graphite-carbon-port = 2003 #graphite-embedded = false #graphite-carbon-api-port = 10002 #graphite-carbon-server-port = 10003 #graphite-carbon-link-port = 7002 #graphite-carbon-pickle-port = 2004 #graphite-carbon-pprof-port = 7007 #backup-mydumper-path = "/usr/local/bin/mydumper" #backup-myloader-path = "/usr/local/bin/myloader" #backup-mysqlbinlog-path = "/usr/local/bin/mysqlbinlog" #backup-mysqldump-path = "/usr/local/bin/mysqldump" ############## # BENCHMARK ## ############## #sysbench-binary-path = "/usr/bin/sysbench" #sysbench-threads = 4 #sysbench-time = 100 #sysbench-v1 = true
2、数据库集群配置文件
Replication-Manager的数据库集群配置文件为/etc/replication-manager/cluster.d/[clusterN].toml。这里的[clusterN]就代表对应MySQL集群的名字，通过不同的数据库集群配置文件可以为多个数据库集群进行个性化配置。
cat /etc/replication-manager/cluster.d/cluster1.toml [test1] #prov-orchestrator = "onpremise" #prov-db-tags = "innodb,noquerycache,slow,pfs,pkg,linux,smallredolog,logtotable" #prov-db-memory = "256" #prov-db-memory-shared-pct = "threads:16,innodb:60,myisam:10,aria:10,rocksdb:1,tokudb:1,s3:1,archive:1,querycache:0" #prov-db-disk-size = "1" #prov-db-cpu-cores = "1" #prov-db-disk-iops = "300" title = "test1" db-servers-hosts = "172.20.1.190:3306,172.20.1.191:3306" db-servers-prefered-master = "172.20.1.190:3306" db-servers-credential = "dba:123456" db-servers-connect-timeout = 10 replication-credential = "repl:123456" #是否开启debug日志 verbose = 1 log-failed-election = true log-level = 7 log-rotate-max-age = 7 log-rotate-max-backup = 7 log-rotate-max-size = 5 log-sql-in-monitoring = true log-sst = true ############## ## TOPOLOGY ## ############## #多主\级联架构 replication-multi-master = false replication-multi-tier-slave = false ############ # BACKUPS ## ########### #backup-streaming = false #backup-streaming-aws-access-key-id = "admin" #backup-streaming-aws-access-secret = "xxxx" #backup-streaming-endpoint= "https://s3.signal18.io/" #backup-streaming-region= "fr-1" #backup-streaming-bucket= "repman" #backup-restic = false #backup-restic-aws = false #backup-physical-type = "mariabackup" #backup-logical-type = "mysqldump" #backup-restic-aws-access-secret = "xxxx" #backup-restic-password = "xxxx" #backup-restic-binary-path = "/usr/local/bin/restic" monitoring-scheduler = false scheduler-db-servers-logical-backup = false scheduler-db-servers-logical-backup-cron= "0 0 1 * * 6" scheduler-db-servers-logs = false scheduler-db-servers-logs-cron = "0 0 * * * *" scheduler-db-servers-logs-table-keep = 4 scheduler-db-servers-logs-table-rotate = false scheduler-db-servers-logs-table-rotate-cron = "0 0 0/6 * * *" scheduler-db-servers-optimize = false scheduler-db-servers-optimize-cron = "0 0 3 1 * 5" scheduler-db-servers-physical-backup = false scheduler-db-servers-physical-backup-cron = "0 0 0 * * *" ############## # FAILOVER # ############## #manual为手动进行故障转移，automatic是自动进行故障转移 failover-mode = "automatic" #failover-pre-script = "/usr/local/shell/rm_failover_pre.sh" #发生故障转移后需要执行的脚本 failover-post-script = "/usr/local/shell/rm_failover_post.sh" #故障转移后设置从节点为只读 failover-readonly-state = true #从库延迟大于N秒则不进行转移 failover-max-slave-delay = 0 failover-event-scheduler = false failover-event-status = false #ping失败达到多少次进行故障转移,ping间隔实际由monitoring-ticker参数控制 failover-falsepositive-ping-counter = 10 #failover-falsepositive-heartbeat-timeout = 5 failover-falsepositive-heartbeat = true # 限制故障转移次数 failover-limit = 0 # 限制两次故障转移的间隔，单位为秒 failover-time-limit = 3600 # 要完全同步才能进行转移，如果没开启半同步复制这里应该设置为false，否则会出现No candidates found的警告且不能进行故障转移 failover-at-sync = true # 如果整个集群都关闭，是否将第一个启动的节点作为主 failover-restart-unsafe = false # failover-falsepositive-maxscale = false # failover-falsepositive-maxscale-timeout = 14 # failover-falsepositive-external = false # failover-falsepositive-external-port = 80 ################ ## SWITCHOVER ## ################ #切换等待N毫秒后开始杀死主节点的线程 switchover-wait-kill = 10000 #如果无法从FTWRL返回，则N秒后超时并取消切换 switchover-wait-trx = 10 #切换时如果存在写事务超过指定秒数则取消切换 switchover-wait-write-query = 10 #从库完全同步时才能进行切换 switchover-at-equal-gtid = false switchover-at-sync = true #从库延迟达到指定秒数后取消切换 switchover-max-slave-delay = 30 ############ ## REJOIN ## ############ autorejoin = false autorejoin-script = "" autorejoin-semisync = true autorejoin-backup-binlog = false autorejoin-flashback = false autorejoin-mysqldump = false #################### ## CHECKS & FORCE ## #################### check-replication-filters = true check-binlog-filters = true #用户是否可以通过忽略复制检查来强制故障转移 check-replication-state = true force-slave-heartbeat= false force-slave-heartbeat-retry = 5 force-slave-heartbeat-time = 3 force-slave-gtid-mode = false force-slave-semisync = false force-slave-failover-readonly-state = false force-binlog-row = false force-binlog-annotate = false force-binlog-slowqueries = false force-binlog-compress = false force-binlog-checksum = false force-inmemory-binlog-cache-size = false force-disk-relaylog-size-limit = false force-disk-relaylog-size-limit-size = 1000000000 force-sync-binlog = false force-sync-innodb = false ############## ## MAXSCALE ## ############## #maxscale = false #maxscale-binlog = false #maxscale-servers = "192.168.0.201" #maxscale-port = 4003 #maxscale-user = "admin" #maxscale-pass = "mariadb" #maxscale-disable-monitor = false #maxscale-get-info-method = "maxadmin" #maxscale-maxinfo-port = 4002 #maxscale-write-port = 4007 #maxscale-read-port = 4008 #maxscale-read-write-port = 4006 #maxscale-binlog-port = 4000 ############# ## HAPROXY ## ############# #haproxy = false #haproxy-binary-path = "/usr/sbin/haproxy" #haproxy-write-port = 3306 #haproxy-read-port = 3307 #################### ## SHARDING PROXY ## #################### #mdbshardproxy = false #mdbshardproxy-hosts = "127.0.0.1:3306" #mdbshardproxy-user = "root:mariadb"
3、其他配置文件
/usr/share/replication-manager：该目录存放replication-manager Web静态文件、haproxy模板等
/var/log/replication-manager.log：默认日志文件
4、Replication-Manager 常用管理命令
systemctl start replication-manager #启动replication-manager systemctl enable replication-manager #停止replication-manager replication-manager-cli status #查看所有集群状态 replication-manager-cli status test1 #查看指定集群状态 replication-manager-cli topology --cluster=test1 #查看集群拓扑，多个集群的时候需要指定具体集群名 replication-manager-cli switchover --cluster="$cluster_name" #手动切换主从节点 replication-manager-cli failover --cluster="$cluster_name" #手动进行故障转移
5、Replication-Manager的Web管理地址
如果使用replication-manager 2.1版本，当服务启动后可以通过浏览器访问https://replication-manager-host:10005进入replication-manager管理界面，默认的管理账号密码是admin/repman
6、Replication-Manager交互式控制台管理
Ctrl-D：Print debug information
Ctrl-F：Manual Failover
Ctrl-I：Toggle automatic/manual failover mode
Ctrl-R：Set slaves read-only
Ctrl-S：手动切换主从
Ctrl-Q：退出交互菜单
Ctrl-W：Set slaves read-write
Ctrl-P | CTRL-N：切换集群
replication-manager-cli console
replication-manager-cli console --cluster=mycluster #针对指定集群
7、replication-manager高可用验证
关闭主库进行测试即可
四、Replication-Manager故障转移脚本
vi /usr/local/shell/rm_failover_post.sh
#!/bin/bash
old_master=$1
new_master=$2
old_port=$3
new_port=$4
interface=eth0
vip=172.20.1.222
ssh_user=root
ssh_password=123456
add_vip="sudo /sbin/ip address add ${vip} dev ${interface}"
del_vip="sudo /sbin/ip address del ${vip}/32 dev ${interface}"
vip_status() {
if ping -c 1 -W 5 "$vip"; then
return 0
else
return 1
fi
}
vip_stop() {
rc=0
sshpass -p ${ssh_password} ssh -tt -o StrictHostKeyChecking=no ${ssh_user}@${old_master} "${del_vip}"
rc=$?
return $rc
}
vip_start() {
rc=0
sshpass -p ${ssh_password} ssh -tt -o StrictHostKeyChecking=no ${ssh_user}@${new_master} "${add_vip}"
rc=$?
return $rc
}
if vip_status; then
if vip_stop; then
echo "`date +'%Y-%m-%d %T'`：VIP ${vip} 已从 ${old_master} 删除！" | mail -s "【测试忽略】VIP ${vip} 已从 ${old_master} 删除！" ops@huize.com
else
echo "`date +'%Y-%m-%d %T'`：VIP ${vip} 从 ${old_master} 删除失败！" | mail -s "【测试忽略】VIP ${vip} 从 ${old_master} 删除失败！" ops@huize.com
exit 11
fi
else
echo "VIP状态异常，故障转移退出!"
exit 2
fi
if vip_start; then
echo "`date +'%Y-%m-%d %T'`：VIP ${vip} 飘移至 ${new_master}!"
echo "`date +'%Y-%m-%d %T'`：VIP ${vip} 飘移至 ${new_master}!" | mail -s "【测试忽略】VIP ${vip} 飘移至 ${new_master}！" ops@huize.com
else
echo "`date +'%Y-%m-%d %T'`：VIP ${vip} 添加至 ${new_master} 失败!"
echo "`date +'%Y-%m-%d %T'`：VIP ${vip} 添加至 ${new_master} 失败！" | mail -s "【测试忽略】VIP ${vip} 添加至 ${new_master} 失败！" ops@huize.com
exit 12
fi五、Replication-Manager最佳实践和常见错误解决
· MySQL每个节点配置做到一样，避免主从切换后配置不同而发生问题。数据库版本建议MySQL 5.6以上，主从复制使用GTID方式，并且开启半同步复制
vi /etc/my.cnf log_slave_updates = 1 slave_parallel-type = LOGICAL_CLOCK slave_parallel_workers = 8 gtid_mode = ON enforce_gtid_consistency = 1 extra_port = 13306 extra_max_connections = 10 log_slow_slave_statements = 1 #当使用statement格式进行复制时，从库也记录慢查询（row格式无效）
· No log of replication queries in slow query 问题
MySQL需要设置log_slow_slave_statements = 1，当使用statement格式进行复制时，从库也记录慢查询（row格式无效）
· User must have REPLICATION SLAVE privilege 问题
主从复制账号需要在每个节点都添加好，需要注意复制账号的网段设置，需要以177.20.1.%或者172.20.% .%或者172.%.%.%或者'%'这样的网段，我一开始使用了172.20.%这样的网段，一直出现"User must have REPLICATION SLAVE privilege"的错误提示
CREATE USER 'repl'@'172.20.0.%' identified by '123456'; #建议以网段授权，方便每个节点都有权限 GRANT REPLICATION SLAVE,REPLICATION CLIENT on *.* to 'repl'@'172.20.0.%'; CHANGE MASTER TO MASTER_HOST='10.3.0.230',MASTER_PORT=3317,MASTER_USER='repl241',MASTER_PASSWORD='repl666#@888',master_auto_position=1;
· invalid character 'E' looking for beginning of value 问题
请确保Replication Manager版本不低于2.2.14，经和作者确认为2.2.14之前的BUG
版权声明：本文章版权归数据库运维网(www.ywdba.cn)所有。如需引用本站内容，请注明来源及作者。
评论