原 【DB宝45】MySQL高可用之MGR+Consul架构部署
👉 本文共约2866个字，系统预计阅读时间或需11分钟。
目录
一、MGR+Consul架构简介
基于目前存在很多MySQL数据库单点故障，传统的MHA，PXC等方案用VIP或者DNS切换的方式可以实现、基于数据库的数据强一致性考虑，采用MGR集群，采用consul服务注册发现实现应用端通过动态DNS 访问MGR集群，实现数据库高可用，自动化切换的方案。
有关MGR之前发布过几篇文章，连接如下：
【DB宝18】在Docker中安装使用MySQL高可用之MGR
【DB宝35】使用MySQL 8.0 克隆(clone)插件快速添加MGR节点
本次环境一共包括7台机器，其中3台做MySQL的MGR数据节点（其上也需要安装Consul，作为Consul Client），单主模式，还有3台做Consul Server集群，实现Consul的高可用，剩下一台做DNS服务器，用来解析MGR节点域名，规划如下表所示：
二、搭建MGR
2.1、申请3台MGR机器
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | -- 拉取镜像 docker pull lhrbest/lhrcentos76:8.0 -- 创建专用网络 docker network create --subnet=192.168.68.0/16 mhalhr docker network inspect mhalhr -- 生成3台CentOS的环境 docker rm -f mysql8022mgr33060 mysql8022mgr33061 mysql8022mgr33062 docker run -d --name mysql8022mgr33060 -h lhrmgr60 \ --network mhalhr --ip 192.168.68.60 --privileged=true \ -p 33060:3306 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ lhrbest/lhrcentos76:8.0 init docker run -d --name mysql8022mgr33061 -h lhrmgr61 \ --network mhalhr --ip 192.168.68.61 --privileged=true \ -p 33061:3306 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ lhrbest/lhrcentos76:8.0 init docker run -d --name mysql8022mgr33062 -h lhrmgr62 \ --network mhalhr --ip 192.168.68.62 --privileged=true \ -p 33062:3306 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ lhrbest/lhrcentos76:8.0 init -- 拷贝MySQL安装文件到MySQL容器内 docker cp mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar mysql8022mgr33060:/ docker cp mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar mysql8022mgr33061:/ docker cp mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar mysql8022mgr33062:/ docker network connect bridge mysql8022mgr33060 docker network connect bridge mysql8022mgr33061 docker network connect bridge mysql8022mgr33062 docker restart mysql8022mgr33060 mysql8022mgr33061 mysql8022mgr33062 |
2.2、3台主机安装MySQL环境
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | docker exec -it mysql8022mgr33060 bash docker exec -it mysql8022mgr33061 bash docker exec -it mysql8022mgr33062 bash tar -xvf mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar yum localinstall -y mysql-community-*.rpm --去掉密码验证策略 mv /usr/lib64/mysql/plugin/validate_password.so /usr/lib64/mysql/plugin/validate_password.so_bk mv /usr/lib64/mysql/plugin/component_validate_password.so /usr/lib64/mysql/plugin/component_validate_password.so_bk -- 启动mysql systemctl start mysqld systemctl status mysqld -- 修改密码 grep 'temporary password' /var/log/mysqld.log mysql -uroot -p alter user root@'localhost' identified with mysql_native_password by 'lhr'; grant all on *.* to root@'localhost' with grant option; create user root@'%' identified with mysql_native_password by 'lhr'; grant all on *.* to root@'%' with grant option; flush privileges; select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user; |
2.3、修改MySQL参数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 | -- 节点1 cat > /etc/my.cnf <<"EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv= server-id = 802233060 default-time-zone = '+8:00' log_timestamps = SYSTEM log-bin = binlog_format=row binlog_checksum=NONE skip-name-resolve log_slave_updates = 1 gtid-mode=ON enforce-gtid-consistency=on default_authentication_plugin=mysql_native_password max_allowed_packet = 500M master_info_repository=TABLE relay_log_info_repository=TABLE relay_log=lhrmgr60-relay-bin transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=OFF loose-group_replication_local_address= "192.168.68.60:33061" loose-group_replication_group_seeds= "192.168.68.60:33061,192.168.68.61:33061,192.168.68.62:33061" loose-group_replication_bootstrap_group=OFF loose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62" report_host=192.168.68.60 report_port=3306 EOF -- 节点2 cat > /etc/my.cnf <<"EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv= server-id = 802233061 default-time-zone = '+8:00' log_timestamps = SYSTEM log-bin = binlog_format=row binlog_checksum=NONE log_slave_updates = 1 gtid-mode=ON enforce-gtid-consistency=ON skip_name_resolve default_authentication_plugin=mysql_native_password max_allowed_packet = 500M master_info_repository=TABLE relay_log_info_repository=TABLE relay_log=lhrmgr61-relay-bin transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=OFF loose-group_replication_local_address= "192.168.68.61:33061" loose-group_replication_group_seeds= "192.168.68.60:33061,192.168.68.61:33061,192.168.68.62:33061" loose-group_replication_bootstrap_group=OFF loose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62" report_host=192.168.68.61 report_port=3306 EOF -- 节点3 cat > /etc/my.cnf <<"EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv= server-id = 802233062 default-time-zone = '+8:00' log_timestamps = SYSTEM log-bin = binlog_format=row binlog_checksum=NONE log_slave_updates = 1 gtid-mode=ON enforce-gtid-consistency=ON skip_name_resolve default_authentication_plugin=mysql_native_password max_allowed_packet = 500M master_info_repository=TABLE relay_log_info_repository=TABLE relay_log=lhrmgr62-relay-bin transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=OFF loose-group_replication_local_address= "192.168.68.62:33061" loose-group_replication_group_seeds= "192.168.68.60:33061,192.168.68.61:33061,192.168.68.62:33061" loose-group_replication_bootstrap_group=OFF loose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62" report_host=192.168.68.62 report_port=3306 EOF |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
