目录一.mysql路由器1.1简单介绍1.2实现mysql路由器1.2.1准备工作1.2.2下载mysql路由器软件并解压安装1.2.3修改文件配置1.2.4恢复多组复制1.2.5创建远程登录用户1.2.6使用路由器远程访问数据库1.2.7查看当前路由器连接的数据库二.MySQL InnoDB Cluster集群2.1简单介绍2.2部署单组复制2.2.1简单介绍2.2.2编辑my.cnf文件调整为单组模式2.2.3选择任一节点启动临时引导模式开启组群2.2.4开启其他两个组群2.2.5查看集群成员状态2.2.6测试2.3部署mysql shell2.3.1简单介绍2.3.2官网下载mysql shell软件包并解压安装到路由器上2.3.3创建一个集群管理员2.3.4配置集群2.4自动部署mysqlrouter2.4.1简单介绍2.4.2自动生成相关的配置文件信息并重启路由服务2.4.3测试三.部署Mysql InnoDB Cluster高可用3.1简单介绍3.2搭建高可用、路由器与数据库架构3.2.1准备工作3.2.2server10上安装mysqlrouter并完成自动部署工作3.2.2部署中出现以下错误补充3.2.3server11、server12完成高可用的相关配置3.2.4为两个路由器添加VIP3.2.5在两个路由器上设定arptables规则3.2.6测试四.MHA高可用集群4.1简单介绍4.2部署MHA高可用集群4.2.1创建一主两从集群4.2.2MHA部署4.2.3测试手动故障切换4.2.4测试故障自动切换一.mysql路由器1.1简单介绍1.路由器是一种网络硬件负责连接不同的网络并为数据包寻找最佳传输路径是家庭或企业上网的“枢纽”。2.Mysql路由器是一种软件中间件它位于应用程序和MySQL数据库之间负责将数据库连接请求透明地路由到合适的后端MySQL服务器以实现负载均衡和故障转移高可用。3.Mysql路由器的配置决定了路由策略如读写分离、负载均衡但它本身并不会“坚持”把连接固定到某个具体的数据库实例。4.重新以只读方式连接数据库或者停止当前的数据库服务会发现路由器会按照文件的设置要求连接新的数据库这就是路由器的作用。1.2实现mysql路由器1.2.1准备工作1.三台数据库一台路由器。2.三台数据库上要有mysql8.0.363.相关定义可以自定以server5,server6,server9:数据库192.168.7.141192.168.7.142192.168.7.145server4路由器ip192.168.7.140这四个系统互有对方解析1.2.2下载mysql路由器软件并解压安装1.进入mysql 路由器下载官网下载8.0.46版本的msyqlrouter.rpm2.在路由器的系统上安装mysql路由器rpm -ivh mysql-router-community-8.0.46-1.el7.x86_64.rpm1.2.3修改文件配置vim /etc/mysqlrouter/mysqlrouter.conf systemctl enable --now mysqlrouter.service1.2.4恢复多组复制1.任选一台数据库系统恢复组通道认证临时启动引导模式开启组群CHANGE MASTER TO MASTER_USERrpl_user, MASTER_PASSWORDpassword FOR CHANNEL group_replication_recovery; #恢复组通道认证 SET GLOBAL group_replication_bootstart_groupON; START GROUP_REPLICATION; #开启多组复制 SET GLOCAL group_replication_bootstart_goupOFF;2.其余两个数据库系统只需开启组群就可自动加入START GROUP_REPLICATION;3.查看组成员SELECT * FROM performance_schema.replication_group_members; #查看组群成员1.2.5创建远程登录用户在数据库上创建一个名为wzq的用户允许全部IP访问CREATE USER wxh% IDENTIFIED WITH mysql_native_password BY westos; #创建wzq用户 GRANT ALL ON test.* to wxh% #允许用户对test数据进行读写操作1.2.6使用路由器远程访问数据库1.检查路由器端口是否开启netstat -antlp | grep 7002.访问路由器使用账号和密码远程访问数据库mysql -uwxh -pwestos -P 7001 -h 192.168.7.140 #7001端口 mysql -uwxh -pwestos -P 7002 -h 192.168.7.140 #7002端口7001只读端口进入数据库后无法修改数据轮询访问server9-server5-server67002读写端口进入数据库后可以修改数据顺序访问server9-server5-server6-server91.2.7查看当前路由器连接的数据库1.用只读方式连接数据库后逐一在数据库系统上输入losf检测端口命令查看路由器连接的是哪一台数据库。之后中断连接重新以只读方式连接数据库或者停止当前的数据库服务会发现路由器会按照文件的设置要求连接新的数据库这就是路由器的作用。server4:mysql -P 7001 -u root -pwestos -h 192.168.7.140 #以只读模式连接路由器数据库lsof -i :3306 #3306是数据库监听端口号2.1.用读写方式连接数据库后逐一在数据库系统上输入losf检测端口命令查看路由器连接的是哪一台数据库。之后中断连接重新以只读方式连接数据库或者停止当前的数据库服务会发现路由器会按照文件的设置要求连接新的数据库。server4:mysql -P 7002 -u root -pwestos -h 192.168.7.140 #以读写模式连接路由器实际访问的是数据库数据库路由器的本质缺点不同的是如果集群中新分配的主节点与你文件中定义的下一个节点不同则会出现故障。所以简单的路由器一般只搭配多组复制。二.MySQL InnoDB Cluster集群2.1简单介绍1.介绍MYSQL的官方高可用解决方案其中整合了多种MYSQL技术能够快速部署和调试集群。核心功能接入 InnoDB Cluster 元数据模式metadata_cache让 MySQL Router 自动感知集群拓扑不再使用静态节点列表。2.特点1.自动化部署便于添加或移除集群中的节点且新节点会自动同步集群中其他节点的数据2.自动障碍转移当集群中的主节点发生故障时会自动从现有的从节点当中选出数据与主节点最接近的一个节点作为集群中的新主节点3.高级扩展将主机群与数据中心的其他复制集群相连接提高容灾能力3.静态路由器与动态路由器静态路由器它是无感知的。它只知道你配置的IP列表里的服务器是通还是不通完全不知道这些服务器在集群里扮演什么角色比如哪个是主节点。如果主节点挂了它只能机械地尝试列表里的下一个但这下一个可能是个备节点导致你的写请求失败。并且路由器只能读取文件里的固定节点每次新加入数据库都要向文件写入它的IP。动态路由器它会实时监测集群中的状态。它保证了默认的读端口只会访问集群中的从节点默认的读写端口会访问集群中的主节点。当主节点故障MGR自动选举出新主后Router会实时感知这一变化。后续通过读写端口发起的新连接会被自动、正确地路由到新的主节点。同时动态路由器会自动检测新加入的数据库节点判断它在集群中的位置。2.2部署单组复制2.2.1简单介绍1.单组复制只有一台主机作为主节点支持读写其他节点都是从节点仅能读数据。2.当主节点出现故障时数据库中会新选举出一个文件中权重较高的备用从节点成为数据库的新主节点。innodb cluster集群中3.在之前的主节点恢复成功重新加入集群后会成为一个新的从节点。2.2.2编辑my.cnf文件调整为单组模式vim /etc/mysql/my.conf systemctl restart mysqld2.2.3选择任一节点启动临时引导模式开启组群SET GLOBAL group_replication_bootstrap_groupON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_groupOFF;2.2.4开启其他两个组群START GROUP_REPLICATION;2.2.5查看集群成员状态SELECT * FROM performance_schema.replication_group_members;2.2.6测试在主节点上创建用户在从节点上查看主节点CREATE DATABASE test; #创建test数据库 USE test; #切换到test库 #创建测试表 CREATE TABLE user ( username VARCHAR(50) PRIMARY KEY, password VARCHAR(50) ); #插入测试数据 INSERT INTO user VALUES (user1, 111); INSERT INTO user VALUES (user2, 222); INSERT INTO user VALUES (user3, 333); #查询验证 SELECT * FROM user; SHOW STATUS LIKE Rpl_semi_sync%; ———————————————— 版权声明本文为CSDN博主「wzq11_666」的原创文章遵循CC 4.0 BY-SA版权协议转载请附上原文出处链接及本声明。 原文链接https://blog.csdn.net/2302_80987538/article/details/163375289从节点SELECT * FROM test.user;2.3部署mysql shell2.3.1简单介绍1.作用InnoDB Cluester的核心管理工具里面包含多种脚本用于MYSQL的管理。核心作用构建元数据集群接管mysql集群自动感知集群上的变化2.功能1.多语言编辑模式可自由切换javascripts模式、MYSQL模式、Python模式2.强大的管理API设置了一个全局变量dba方便我们快速创建和部署高可用集群2.3.2官网下载mysql shell软件包并解压安装到路由器上yum install -y mysql-shell-8.0.46-1.e17.x86_64.rpm2.3.3创建一个集群管理员CREATE USER icadmin% IDENTIFIED WITH mysql_native_passwordBY westos; GRANT ALL PRIVILIEGES on *.* TO icadmin% WITH GRANT OPTION; #赋予这个用户所有权限 FLASH PRIVILEGES;2.3.4配置集群1.通过路由器远程登录MYSQL的主节点输入集群管理员的密码进入javascripts模式msyqlsh icadmin192.168.7.145 #你的管理员用户主节点IP2.接管当前已有的MSRMYSQL组复制集群延用组复制的模式var cluster dba.createCluster(mgr_single_cluster, {adoptFromGR:true, force:true});3.查看当前的集群状态,看到OK说明接管成功cluter.status();2.4自动部署mysqlrouter2.4.1简单介绍作用 MySQL Router 自动接入并识别你现有 InnoDB Cluster。该操作会自动根据你当前数据库中的集群状态修改你当前的路由器的配置文件实现自动感知集群中的节点并根据需求自动切换。1.自动读取集群拓扑Router 会使用你提供的账户连接到集群从集群的元数据中获取所有成员节点的地址和角色主/从信息。2.自动生成配置文件根据读取到的拓扑信息Router 会自动生成一个名为mysqlrouter.conf的配置文件里面包含了读写分离的路由规则。3.预设端口路由器默认会从下面两个端口分流6446用于读写请求所有写操作都会通过此端口自动路由到集群的主节点。6447用于只读请求所有读操作都会通过此端口自动路由到集群的从节点。4.自动创建专用用户自动创建一个专用数据库的用户2.4.2自动生成相关的配置文件信息并重启路由服务mysqlrouter --bootstrap icadmin192.168.7.145:3306 --usermysqlrouter #自动完成文件配置 systemctl restart mysqlrouter.service #重启服务注意在我部署过程中主节点在192.168.7.145这个主机上2.4.3测试1.以读写方式连接路由器接着停止集群中的主节点数据库路由器会自动寻找集群中的新的主节点数据库并连接它。并且读写模式下他只会连接主节点数据库。server4:mysql -h 192.168.7.140 -P 6446 -u wxh -pwestos数据库lsof -i :3306 systemctl stop mysqld2.以只读方式连接路由器接着停止当前正在连接的数据库路由器会自动寻找集群中的新的主节点数据库并连接它。并且只读模式下他只会连接从节点数据库。server4:mysql -h 192.168.7.140 -P 6447 -u wxh -pwestosmysql:三.部署Mysql InnoDB Cluster高可用3.1简单介绍高可用当主节点的高可用出现故障备用的从节点高可用立刻接管工作正常运行横向扩容通过增加数据库的数量提高整体集群的性能工作原理客户端通过访问VIP进入高可用集群中接着高可用把请求轮询发给路由器最后通过路由器根据你当前的需求读写还是只读连接到对应的数据库中。整体架构如下3.2搭建高可用、路由器与数据库架构3.2.1准备工作1.准备七台虚拟机或者Linux系统:两个高可用、两个路由器和三个数据库server4,server10路由器server11、server12高可用server5、server6、server9数据库IP地址与server的关系如下2.新建的虚拟机要求配置静态IP、时间同步、域名解析、关闭防火墙和selinux静态IPvim /etc/sysconfig/network-scripts/ifcfg-ens32 #编辑网络配置文件 nmcli c up ens32 #刷新网络 systemctl restart network #重启网络服务时间同步vim /etc/chrony.conf域名解析vim /etc/hosts关闭防火墙和selinuxvim /etc/sysconfig/selinux #关闭selinux需要重启系统生效 systemctl disable --now firewalld #关闭防火墙3.2.2server10上安装mysqlrouter并完成自动部署工作1.将server4上的mysqlrouter安装包远程传输到server10上,并安装启动路由server4:scp mysql-router-community-8.0.46-1.e17.x86_64.rpm server10:server10:yum install -y mysql-router-community-8.0.46-1.e17.x86_64.rpm systemctl start mysqlrouter2.连接当前集群中的主节点数据库系统自动部署路由器mysqlrouter --bootstrap icadmin192.168.7.145:3306 --usermysqlrouter #自动完成文件配置 systemctl restart mysqlrouter #重启路由器3.2.2部署中出现以下错误补充问题系统中已经存在名为system的mysql router实例你需要使用--force强制覆盖掉他解决办法部署命令后加上--force参数mysqlrouter --bootstrap icadmin192.168.7.145:3306 --usermysqlrouter --force #强制覆盖3.2.3server11、server12完成高可用的相关配置1.安装mariadb用来测试server10路由器是否成功连接上后端数据库yum install -y mariadb #mariadb用于测试 mysql -uwxh -pwestos -P 6446 192.168.7.146 #连接server10路由器的IP2.安装高可用软件keepalived、ipvsadmyum install -y keepalived ipvsadm #安装相关软件3.确保两个高可用时间同步date4.如果时间不同步可以试着刷新或者重启一下chronyd服务错误改正systemctl restart chronyd chronyc makestep #强制跳进到正常的时间 date5.修改server11的keepalived.conf文件完成主机的高可用配置vim /etc/keepalived/keepalived.conf ipvsadm-save /etc/sysconfig/ipvsadm #保存配置 systemctl restart keepalived ipvsadm -ln scp /etc/keepalived/keepalived.conf server12:/etc/keepalived/keepalived.conf #复制配置文件给server12 ip a #查看VIP是否加入6.修改server12的keepalived.conf文件完成备机高可用的配置vim /etc/keepalived/keepalived.conf ipvsadm-save /etc/keepalived/keepalived.conf systemctl restart keepalived ipvsadm -ln ip a #两台高可用只有一台拥有VIP3.2.4为两个路由器添加VIPserver4,server10:ip a a 192.168.7.100 dev ens32 #这个IP是临时添加的重启系统会丢失 ip a3.2.5在两个路由器上设定arptables规则1.安装软件yum install -y arptables2.添加arptables规则路由器不响应外部对于自身VIP的ARP查询修改本机发出的 ARP 包的源 IP从VIP伪装成本机的真实IParptables -A INPUT -d 192.168.7.100 -j DROP #拒绝外机对于本机VIP的访问 arptables -A OUTPUT -s 192.168.7.100 -j mangle --mangle-ip-s 192.168.7.140(你当前路由器的IP) #将源VIP伪装成真实的路由器本机IP arptables -nL arptables-save /etc/sysconfig/arptables #将修改规则写入文件中保存 systemctl restart arptables3.2.6测试1.在没有VIP的那台高可用机上访问VIP或者一台处于同VLn的系统访问VIP观察是否能够连接到后端数据库mysql -uwxh -pwestos -P 6446 192.168.7.100 #以读写方式访问数据库 mysql -uwxh -pwestos -P 6447 192.168.7.100 #以只读方式访问数据库注意如果在存在VIP的高可用上访问VIP会出现无响应的情况因为你正在访问本机二不是路由器2.停止当前server11的keepalived服务会发现VIP自动漂移到server12上这时候可用ipvsadm查看或者直接在server12上用IP查看VIP是否存在接着用server12或外机测试能否访问到后端数据库server11:systemctl stop keepalived mysql -uwxh -pwestos -P 6446 192.168.7.100 #观察是否能够连接后端数据库 mysql -uwxh -pwestos -P 6447 192.168.7.100server12:ipvsadm -nl #查看VIP是否转移 ip a #查看系统上是否存在VIP3.关闭一个路由器后观察还能否正常访问后端数据库接着打开路由器关闭另一台路由器观察是否能够访问后端数据库server4:systemctl stop mysqlrouter #关闭路由器服务测试机mysql -uwxh -pwestos -P 6446 192.168.7.100 #观察是否能够连接后端数据库 mysql -uwxh -pwestos -P 6447 192.168.7.100server10:测试前先打开server4的路由器systemctl stop mysqlrouter #关闭路由器服务测试机mysql -uwxh -pwestos -P 6446 192.168.7.100 #观察是否能够连接后端数据库 mysql -uwxh -pwestos -P 6447 192.168.7.100四.MHA高可用集群4.1简单介绍1.作用实现 MySQL 数据库的主库故障自动切换最大限度保证数据库服务的高可用性。2.与InnoDB Cluster的区别MHA高可用采用异步复制主节点断开后集群中会选择数据最接近的从节点作为新的主节点抢修日志。事后从日志堆里挑数据最全的并补日志。InnoDB Cluster采用组复制集群在每次提交事务前需要多个从节点的确认。在主节点断开后集群通过投票确认新的主节点。4.2部署MHA高可用集群4.2.1创建一主两从集群1.准备工作三个数据库一个作为主节点server5两个作为从节点server6,server9一个Manger管理节点server42.编辑my.cnf文件vim /data/mysql/my.cnf #编辑my.cnf文件3.mysql进行初始化开启mysql服务mysqld --defaults-file/etc/mysql/my.cnf --initialize #进行mysql初始化 systemctl start mysqld grep password /data/mysql/my.cnf mysql -p4.创建数据库主从复制用户alter user rootlocalhost identify by westos; create user repl% identify with my_native_password by westos; grant replication slave on *.* to repl%; show master status;5.从库连接主库change master to master_host192.168.7.141,master_passwordwestos,master_auto_position1; start slaves; show slaves status\G4.2.2MHA部署1.server4安装mha软件yum install -y mha4mysql-node-0.58-0.e17.centos.noarch.rpm2.server4和三台数据库配对免密登录ssh-keygen ssh-copy-id server4 scp -r .ssh/ server5: #复制密钥给server5 scp -r .ssh/ server6: #复制密钥给server6 scp -r .ssh/ server7: #复制密钥给sercer93.编辑server4 manger文件配置mkdir /etc/masterha vim /etc/masterha/app1.conf #编辑文件4.检测各节点ssh 免密连接主从复制集群状态masterha_check_ssh --conf/etc/masterha/app1 .cnf masterha_check_repl --conf/etc/masterha/app1 .cnf4.2.3测试手动故障切换停止正在工作的主数据库手动将主节点从停止工作的主数据库上移动到正常工作的从节点的数据库上这个能够作为主数据库的从节点一般在配置文件中自定义。1.手动将server5上的主数据库转移到server6上masterha_master_switch --conf/etc/masterha/app1 .cnf --master_statealive --new_master_host192.168.7.142 --new_master_port3306 --orig_master_is_new_slave --running_updates_limit10000 #设定新的主节点为server62.模拟手动故障server6上手动停止数据库服务systemctl stop mysqld.service3.故障修复手动将server6上已经停止数据库的主节点转移到server5上 server5自动升级 成从节点在server9上查看当前的主节点server6masterha_master_switch --master_statedead --conf/etc/masterha/app1 .cnf --dead_master_host192.168.7.142 --dead_master_port3306 --new_master_host192.168.7.141 --new_master_port3306 --ignore_last_failoverserver9登录数据库查看当前连接的主节点show slave status\G4.恢复停止的数据库重新启动数据库服务该数据库自动成为当前主数据库的从数据库systemctl start mysqld mysql -p change master to master_host192.168.7.142 master_passwordwestos,master_auth_position1; start slave; show slave status;4.2.4测试故障自动切换当主节点发生故障时程序会自动将主节点切换到备用的主节点上1.手动删除刚才切换生成的lock文件启动manger程序并打入后台运行完成切换任务后进程会自动退出rm -f app1 .failover.complete masterha_manager --conf/etc/masterha/app1 .cnf 2.编辑MHA配置文件开启自动切换vim /etc/masterha/app1.conf3.编辑master_ip文件指定VIP和连接设备并赋权vim master_ip_failover vim master_ip_online_change chmod 655 master_ip_failover chmod 655 master_ip_online_change4.关闭当前的主节点数据库server5在从节点数据库server9上查看当前正在连接的新的主数据库IPserver5:systemctl stop mysqldserver9