【DB宝45】MySQL高可用之MGR+Consul架构部署
👉 本文共约2962个字，系统预计阅读时间或需12分钟。
一、MGR+Consul架构简介
基于目前存在很多MySQL数据库单点故障，传统的MHA，PXC等方案用VIP或者DNS切换的方式可以实现、基于数据库的数据强一致性考虑，采用MGR集群，采用consul服务注册发现实现应用端通过动态DNS 访问MGR集群，实现数据库高可用，自动化切换的方案。
有关MGR之前发布过几篇文章，连接如下：
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
【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 |
2.4、重启MySQL环境
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | -- 重启MySQL systemctl restart mysqld -- 进入MySQL docker exec -it mysql8022mgr33060 bash docker exec -it mysql8022mgr33060 mysql -uroot -plhr -- 远程连接MySQL mysql -uroot -plhr -h192.168.66.35 -P33060 mysql -uroot -plhr -h192.168.66.35 -P33061 mysql -uroot -plhr -h192.168.66.35 -P33062 -- 查看MySQL日志 docker logs -f --tail 10 mysql8022mgr33060 docker logs -f --tail 10 mysql8022mgr33061 docker logs -f --tail 10 mysql8022mgr33062 -- 查看MySQL的主机名、server_id和server_uuid mysql -uroot -plhr -h192.168.66.35 -P33060 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -plhr -h192.168.66.35 -P33061 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -plhr -h192.168.66.35 -P33062 -e "select @@hostname,@@server_id,@@server_uuid" |
结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P33060 -e "select @@hostname,@@server_id,@@server_uuid" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+-------------+--------------------------------------+ | @@hostname | @@server_id | @@server_uuid | +------------+-------------+--------------------------------------+ | lhrmgr60 | 802233060 | 44c693d8-80bb-11eb-b4bb-0242c0a8443c | +------------+-------------+--------------------------------------+ C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P33061 -e "select @@hostname,@@server_id,@@server_uuid" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+-------------+--------------------------------------+ | @@hostname | @@server_id | @@server_uuid | +------------+-------------+--------------------------------------+ | lhrmgr61 | 802233061 | 408acdb5-80bc-11eb-97a7-0242c0a8443d | +------------+-------------+--------------------------------------+ C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P33062 -e "select @@hostname,@@server_id,@@server_uuid" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+-------------+--------------------------------------+ | @@hostname | @@server_id | @@server_uuid | +------------+-------------+--------------------------------------+ | lhrmgr62 | 802233062 | 9d5c3e3e-80bc-11eb-a0b2-0242c0a8443e | +------------+-------------+--------------------------------------+ |
2.5、安装MGR插件（所有节点执行）
1 2 3 4 5 6 | mysql -uroot -plhr -h192.168.66.35 -P33060 INSTALL PLUGIN group_replication SONAME 'group_replication.so'; -- 如果MySQL版本大于8.0.17，那么建议再安装clone插件 INSTALL PLUGIN clone SONAME 'mysql_clone.so'; show plugins; |
2.6、设置复制账号
1 2 3 4 5 6 7 | -- 在主库(192.168.68.60)上执行 CREATE USER repl@'%' IDENTIFIED BY 'lhr'; GRANT REPLICATION SLAVE,BACKUP_ADMIN ON *.* TO repl@'%'; FLUSH PRIVILEGES; -- 所有节点执行 CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery'; |
2.7、启动MGR，在主库(192.168.68.60)上执行
1 2 3 4 5 6 7 | SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF; -- 查看MGR组信息 SELECT * FROM performance_schema.replication_group_members; |
2.8、其他节点加入MGR，在从库（192.168.68.61，192.168.68.62）上执行
1 2 3 4 | reset master; START GROUP_REPLICATION; -- 查看MGR组信息 SELECT * FROM performance_schema.replication_group_members; |
执行结果：
1 2 3 4 5 6 7 8 9 | MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 408acdb5-80bc-11eb-97a7-0242c0a8443d | 192.168.68.61 | 3306 | ONLINE | SECONDARY | 8.0.22 | | group_replication_applier | 44c693d8-80bb-11eb-b4bb-0242c0a8443c | 192.168.68.60 | 3306 | ONLINE | PRIMARY | 8.0.22 | | group_replication_applier | 9d5c3e3e-80bc-11eb-a0b2-0242c0a8443e | 192.168.68.62 | 3306 | ONLINE | SECONDARY | 8.0.22 | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.05 sec) |
可以看到，3个节点状态为online，并且主节点为192.168.68.60，只有主节点可以写入，其他节点只读，MGR单主模式搭建成功。
2.9、测试同步
在主节点上执行以下命令，然后在其它节点查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | create database lhrdb; CREATE TABLE lhrdb.`tb1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `hostname` varchar(100) DEFAULT NULL, `server_id` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into lhrdb.tb1(hostname,server_id) select @@hostname,@@server_id; select * from lhrdb.tb1; -- 3个节点查询出来的值一样 MySQL [(none)]> select * from lhrdb.tb1; +----+----------+-----------+ | id | hostname | server_id | +----+----------+-----------+ | 1 | lhrmgr60 | 802233060 | +----+----------+-----------+ 1 row in set (0.05 sec) |
