目录摘要mysql路由组复制MGR–MySQL Group Replication单主模式配置mysql shellmysql routerkeepalived 高可用MHA部署准备工作详细配置MHA部署MHA故障手动切换MHA故障自动切换RedisRedis安装redis主从复制redis高可用redis哨兵模式Sentinelredis集群摘要本文详细介绍了在 CentOS 7 环境下构建高可用 MySQL 数据库及 Redis 缓存层的完整解决方案。主要内容包括MySQL Group Replication (MGR)配置单主模式集群实现数据强一致性复制。MySQL Router部署读写分离代理自动路由请求至 MGR 集群的正确节点。Keepalived LVS为 MySQL Router 提供虚拟 IP (VIP) 漂移消除单点故障实现高可用访问入口。MHA (Master High Availability)作为传统主从架构的自动故障切换方案实现主库故障时的快速切换与从库重定向。Redis部署主从复制、哨兵模式及集群模式满足不同规模下的高性能缓存与高可用需求。通过分步实操演示从环境准备、服务配置到故障模拟切换完整呈现了企业级数据库高可用架构的搭建与验证过程。关键词MySQL 高可用MGRMySQL RouterMHARedis 集群mysql路由Mysql Router 部署·Router 通过读取元数据来自MGR集群自动感知节点角色将写请求路由到主节点读请求分发到从节点·使用 mysqlrouter --bootstrap 命令可自动生成配置文件简化手工配置。#mysql路由部署rpm-ivhmysql-router-community-8.0.46-1.el7.x86_64.rpmcd/etc/mysqlrouter/vimmysqlrouter.confsystemctlenable--nowmysqlrouter.service yuminstall-ymariadb create userlzc%identified by654321;mysql-h192.168.88.146-P7001-ulzc-p654321--default-authmysql_native_password子节点查看网络连接yuminstall-ylsoflsof-i:3306组复制MGR–MySQL Group Replication单主模式配置·server-id 必须全局唯一gtid_modeON 和 enforce-gtid-consistencyON 开启GTID便于故障恢复·group_replication_group_name 使用UUID格式集群内所有节点必须一致·group_replication_local_address 和 group_replication_group_seeds 分别定义本节点通信地址和集群成员列表·启动第一个节点时需临时开启 group_replication_bootstrap_groupON后续节点仅需执行 START GROUP_REPLICATION·注意检查防火墙/安全组放行 33061 端口组复制内部通信和 3306 端口。systemctl stop mysqldrm-fr*vim/etc/mysql/my.cnfserver1主节点disabled_storage_enginesMyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORYserver-id1gtid_modeON enforce-gtid-consistencyONbinlog_checksumNONElog_binbinloglog_replica_updatesONplugin_load_addgroup_replication.sogroup_replication_group_nameaaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaagroup_replication_start_on_bootoffgroup_replication_local_address192.168.88.143:33061group_replication_group_seeds192.168.88.143:33061,192.168.88.144/:33061,192.168.88.145:33061group_replication_bootstrap_groupoffgroup_replication_ip_whitelist192.168.88.0/24,127.0.0.1/8group_replication_single_primary_modeON;group_replication_enforce_update_everywhere_checksOFF;mysqld --defaults-file/etc/mysql/my.cnf--initializemysqlSETGLOBALgroup_replication_bootstrap_groupON;Query OK,0rowsaffected(0.00sec)mysqlSTARTGROUP_REPLICATION;Query OK,0rowsaffected,1warning(2.09sec)mysqlSETGLOBALgroup_replication_bootstrap_groupOFF;Query OK,0rowsaffected(0.00sec)mysqlSELECT*FROMperformance_schema.replication_group_members;------------------------------------------------------------------------------------------------------------------------------------------------------------------|CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|MEMBER_ROLE|MEMBER_VERSION|MEMBER_COMMUNICATION_STACK|------------------------------------------------------------------------------------------------------------------------------------------------------------------|group_replication_applier|01e2c898-8c06-11f1-b570-000c292077de|server1|3306|ONLINE|PRIMARY|8.0.36|XCom|------------------------------------------------------------------------------------------------------------------------------------------------------------------1rowinset(0.03sec)server2 slavemysqlSTARTGROUP_REPLICATION;Query OK,0rowsaffected,1warning(2.02sec)mysqlSELECT*FROMperformance_schema.replication_group_members;------------------------------------------------------------------------------------------------------------------------------------------------------------------|CHANNEL_NAME|MEMBER_ID|MEMBER_HOST|MEMBER_PORT|MEMBER_STATE|MEMBER_ROLE|MEMBER_VERSION|MEMBER_COMMUNICATION_STACK|------------------------------------------------------------------------------------------------------------------------------------------------------------------|group_replication_applier|01e2c898-8c06-11f1-b570-000c292077de|server1|3306|ONLINE|PRIMARY|8.0.36|XCom||group_replication_applier|cd3c8329-8c09-11f1-b547-000c294eca46|server2|3306|RECOVERING|SECONDARY|8.0.36|XCom|------------------------------------------------------------------------------------------------------------------------------------------------------------------2rowsinset(0.12sec)再添加一个slave,server3server3与server2进行相同操作mysql shellserver4-mysql routeryuminstall-ymysql-shell-8.0.46-1.el7.x86_64.rpm mysqlsh icadmin192.168.88.143:3306server1–主节点mysqlCREATEUSERicadmin%IDENTIFIEDBY654321;Query OK,0rowsaffected(0.09sec)mysqlGRANTALLPRIVILEGESON*.*TOicadmin%WITHGRANTOPTION;Query OK,0rowsaffected(0.03sec)mysqlFLUSHPRIVILEGES;Query OK,0rowsaffected(0.02sec)server4:路由器接管MGR集群MySQL192.168.88.143:3306ssl JSvar clusterdba.createCluster(mgr_single_cluster,{adoptFromGR:true,force:true});#查看集群状态cluster.status();mysqlrouter--bootstrapicadmin192.168.88.143:3306--usermysqlrouter systemctl restart mysqlrouter.servicemysql routerkeepalived 高可用·在 server6 和 server7 上部署 Keepalived通过 VRRP 虚拟出一个 IP192.168.88.100并配置 LVS 将流量分发至两台Routerserver4, server5master:server6·lb_algo wrr 采用加权轮询调度persistence_timeout 保持用户会话粘性·通过 TCP_CHECK 检测后端Router服务的存活状态实现自动剔除故障节点。部署另一台路由器yuminstall-ykeepalivedvim/etc/keepalived/keepalived.conf global_defs{notification_email{rootlocalhost}notification_email_from keepalivedlocalhost smtp_server127.0.0.1 smtp_connect_timeout30router_id LVS_DEVEL vrrp_skip_check_adv_addr#vrrp_strictvrrp_garp_interval0vrrp_gna_interval0}vrrp_instance VI_1{state MASTER interface ens32 virtual_router_id51priority100advert_int1authentication{auth_type PASS auth_pass1111}virtual_ipaddress{192.168.88.100}}virtual_server192.168.88.1006446{delay_loop3lb_algo wrr lb_kind DR persistence_timeout300protocol TCP real_server192.168.88.1466446{weight1TCP_CHECK{connect_timeout3connect_port6446}}real_server192.168.88.1476446{weight1TCP_CHECK{connect_timeout3connect_port6446}}virtual_server192.168.88.1006447{delay_loop3lb_algo wrr lb_kind DR persistence_timeout300protocol TCP real_server192.168.88.1466447{weight1TCP_CHECK{connect_timeout3connect_port6447}}real_server192.168.88.1476447{weight1TCP_CHECK{connect_timeout3connect_port6447}}}}backup:server7global_defs{notification_email{rootlocalhost}notification_email_from keepalivedlocalhost smtp_server127.0.0.1smtp_connect_timeout30router_id LVS_DEVEL vrrp_skip_check_adv_addr#vrrp_strictvrrp_garp_interval0vrrp_gna_interval0}vrrp_instance VI_1{state MASTER interface ens32 virtual_router_id51priority90#权重比master小 advert_int1authentication{auth_type PASS auth_pass1111}virtual_ipaddress{192.168.88.100}}virtual_server192.168.88.1006446{delay_loop3lb_algo wrr lb_kind DR persistence_timeout300protocol TCP real_server192.168.88.1466446{weight1TCP_CHECK{connect_timeout3connect_port6446}}real_server192.168.88.1476446{weight1TCP_CHECK{connect_timeout3connect_port6446}}virtual_server192.168.88.1006447{delay_loop3lb_algo wrr lb_kind DR persistence_timeout300protocol TCP real_server192.168.88.1466447{weight1TCP_CHECK{connect_timeout3connect_port6447}}real_server192.168.88.1476447{weight1TCP_CHECK{connect_timeout3connect_port6447}}}MHA部署准备工作·在 server1、server2、server3 上配置一主两从异步复制均开启GTID简化位置管理·创建用于复制的专用用户 repl 和 MHA管理用户 root需具备管理权限·所有节点需配置SSH免密登录MHA依赖SSH进行日志传输和故障切换。详细配置primarysystemctl stop mysqld[rootserver1 mysql]# cd /data/mysql[rootserver1 mysql]# rm -fr *[rootserver1 mysql]# vim /etc/mysql/my.cnf[mysqld]basedir/usr/local/mysqldatadir/data/mysqlsocket/data/mysql/mysql.sock log-binmysql-binport3306usermysql pid-file/data/mysql/mysql.pid character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci log-error/data/mysql/mysql-error.loginnodb_buffer_pool_size1Gmax_connections1000default_authentication_pluginmysql_native_passwordserver_id1gtid_modeONenforce_gtid_consistencyONlog_slave_updatesONlog_binbinlog[client]socket/data/mysql/mysql.sock[rootserver1 mysql]# mysqld --defaults-file/etc/mysql/my.cnf --initialize[rootserver1 mysql]# systemctl start mysqldmysql alter user rootlocalhost identified by ‘654321’;Query OK, 0 rows affected (0.01 sec)mysqlCREATEUSERrepl%IDENTIFIEDWITHmysql_native_passwordBY654321;Query OK,0rowsaffected(0.01sec)mysqlGRANTREPLICATIONSLAVEON*.*TOrepl%;Query OK,0rowsaffected(0.01sec)mysqlshowmasterstatus;---------------------------------------------------------------------------------------------------|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|---------------------------------------------------------------------------------------------------|binlog.000002|950|||30a2d7e4-8e7c-11f1-863a-000c292077de:1-3|---------------------------------------------------------------------------------------------------1rowinset(0.00sec)slavesystemctl stop mysqldcd/data/mysqlrm-fr*vim/etc/mysql/my.cnf mysqld --defaults-file/etc/mysql/my.cnf--initializegreptemporary password/data/mysql/mysql-error.logmysqlalteruserrootlocalhostidentifiedby654321;Query OK,0rowsaffected(0.03sec)mysqlchange mastertomaster_host192.168.36.173,master_userrepl,master_password654321,master_auto_position1;Query OK,0rowsaffected,7warnings(0.03sec)mysqlstartslave;Query OK,0rowsaffected,1warning(0.03sec)mysqlshowslavestatus\G;mysqlcreatedatabasewestos;Query OK,1rowaffected(0.02sec)mysqlusewestosDatabasechanged mysqlcreatetableuser_tb(usernamevarchar(25)notnull,-passwordvarchar(50)notnull);Query OK,0rowsaffected(0.08sec)mysqlinsertintouser_tbvalues(user1,111);Query OK,1rowaffected(0.21sec)mysqlinsertintouser_tbvalues(user2,222);Query OK,1rowaffected(0.01sec)mysqlselect*fromwestos.user_tb;MHA部署server4systemctl stop mysqlrouter.service ssh-keygen ssh-copy-id server4scp-r.ssh/ server1:scp-r.ssh/ server2:scp-r.ssh/ server3: yuminstall-yha4mysql-node-0.58-0.el7.centos.noarch.rpm#若果安装失败添加epel源wget-O/etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-7.repo yuminstall-ymha4mysql-manager-0.58-0.el7.centos.noarch.rpm#集群安装客户端软件scpmha4mysql-node-0.58-0.el7.centos.noarch.rpm server1:scpmha4mysql-node-0.58-0.el7.centos.noarch.rpm server2:scpmha4mysql-node-0.58-0.el7.centos.noarch.rpm server3:mkdir/etc/masterhacd/etc/masterhavim/etc/masterha/app1.cnf[server default]userrootpassword654321ssh_userrootrepl_userreplrepl_password654321master_binlog_dir/data/mysqlremote_workdir/tmpsecondary_check_scriptmasterha_secondary_check-s192.168.88.144-s192.168.88.145ping_interval3manager_workdir/etc/masterha/app1manager_log/etc/masterha/app1/manager.log[server1]hostname192.168.88.143candidate_master1check_repl_delay0[server2]hostname192.168.88.144no_master1[server3]hostname192.168.88.145candidate_master1check_repl_delay0检查免密连接 masterha_check_ssh--conf/etc/masterha/app1.cnf检查集群masterha_check_repl--conf/etc/masterha/app1.cnfMHA故障手动切换MHA故障自动切换·监控进程 masterha_manager 定期通过 ping_interval 检测主库健康·当主库故障时MHA自动执行 master_ip_failover 脚本将VIP漂移到新主库并将所有从库重新指向新主·切换完成后原主库恢复后可作为新的从库加入集群。授予可执行权限chmod 755 master_ip_failoverchmod 755 master_ip_online_changevim master_ip_failover当 MHA 完成主库切换后自动执行这些命令将虚拟 IPVIP从旧主库漂移到新主库上。masterha_manager --conf/etc/masterha/app1.cnf 这条命令启动了 MHAMaster High Availability的核心监控进程它根据 app1.cnf 配置文件在后台静默运行。查看从库状态已切换至新的主节点。RedisRedisRemote Dictionary Server是一个开源的内存数据结构存储系统支持网络访问可用作数据库、缓存、消息代理和流处理引擎。特性说明内存存储数据主要存在内存中读写速度极快10万 QPS数据结构丰富不仅是 key-value还支持 String、Hash、List、Set、Sorted Set、Bitmap、HyperLogLog、Stream、Geo 等持久化支持 RDB快照和 AOF日志两种持久化方式单线程模型核心命令处理是单线程的避免锁竞争保证原子性高可用支持主从复制Replication、Sentinel 哨兵、Cluster 集群模式原子操作所有命令都是原子的适合并发场景Redis安装tarzxf redis-6.2.4.tar.gzcdredis-6.2.4makemakeinstallcdutils/viminstall_server.sh#运行安装脚本./install_server.sh改为监听所有端口redis主从复制·从节点通过配置 replicaof master_ip master_port 指向主库启动后自动同步数据·主从复制为异步适用于读多写少场景可横向扩展读能力。server1rsync-aredis-* server2:/usr/local/binrsync-aredis-6.2.4 server2:server2cdredis-6.2.4/utils ./install_server.shvim/etc/redis/6379.conf replicaof192.168.88.1436379systemctl restart redis_6379测试redis-cliinforedis高可用redis哨兵模式Sentinel·在 server1、server2、server3 上分别部署哨兵进程监控主库状态·哨兵之间相互通信通过投票机制判断主库是否客观下线并自动执行故障转移server2[rootserver1 redis]# redis-cli shutdownredis集群./create-cluster start·集群通过哈希槽16384个槽位分区数据自动分布在多个主节点上./create-cluster create连接集群redis-cli-c-p30001info127.0.0.1:30004setname ljx -Redirected to slot[5798]located at127.0.0.1:30002 OK#添加新节点30007、30008后需执行 reshard 迁移槽位才能均匀数据分布redis-cli--clusteradd-node127.0.0.1:30007127.0.0.1:30001·添加从节点时需指定主节点ID实现副本备份。redis-cli--clusteradd-node127.0.0.1:30008127.0.0.1:30001 --cluster-slave --cluster-master-id ef99a4fc501bce05f725ba926510c2816d1c9f46redis-cli--clustercheck127.0.0.1:30001#迁移hash槽redis-cli--clusterreshard127.0.0.1:30001
)
