原 【DB宝18】在Docker中安装使用MySQL高可用之MGR
👉 本文共约1510个字，系统预计阅读时间或需6分钟。
目录
[toc]
一、创建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 | # 拉取镜像 docker pull mysql:8.0.20 # 创建专用网络 docker network create --subnet=172.72.0.0/24 mysql-network # 创建目录存储数据 mkdir -p /usr/local/mysql/lhrmgr15/conf.d mkdir -p /usr/local/mysql/lhrmgr15/data mkdir -p /usr/local/mysql/lhrmgr16/conf.d mkdir -p /usr/local/mysql/lhrmgr16/data mkdir -p /usr/local/mysql/lhrmgr17/conf.d mkdir -p /usr/local/mysql/lhrmgr17/data # 创建3个节点的MySQL docker run -d --name mysql8020mgr33065 \ -h lhrmgr15 -p 33065:3306 --net=mysql-network --ip 172.72.0.15 \ -v /usr/local/mysql/lhrmgr15/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr15/data:/var/lib/mysql/ \ -e MYSQL_ROOT_PASSWORD=lhr \ -e TZ=Asia/Shanghai \ mysql:8.0.20 docker run -d --name mysql8020mgr33066 \ -h lhrmgr16 -p 33066:3306 --net=mysql-network --ip 172.72.0.16 \ -v /usr/local/mysql/lhrmgr16/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr16/data:/var/lib/mysql/ \ -e MYSQL_ROOT_PASSWORD=lhr \ -e TZ=Asia/Shanghai \ mysql:8.0.20 docker run -d --name mysql8020mgr33067 \ -h lhrmgr17 -p 33067:3306 --net=mysql-network --ip 172.72.0.17 \ -v /usr/local/mysql/lhrmgr17/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr17/data:/var/lib/mysql/ \ -e MYSQL_ROOT_PASSWORD=lhr \ -e TZ=Asia/Shanghai \ mysql:8.0.20 |
二、修改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 | cat > /usr/local/mysql/lhrmgr15/conf.d/my.cnf <<"EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv='' server-id = 802033065 default-time-zone = '+8:00' log_timestamps = SYSTEM log-bin = binlog_format=row binlog_checksum=NONE log-slave-updates=1 skip-name-resolve auto-increment-increment=2 auto-increment-offset=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=lhrmgr15-relay-bin-ip15 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= "172.72.0.15:33061" loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063" loose-group_replication_bootstrap_group=OFF loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17" report_host=172.72.0.15 report_port=3306 EOF cat > /usr/local/mysql/lhrmgr16/conf.d/my.cnf <<"EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv='' server-id = 802033066 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=lhrmgr16-relay-bin-ip16 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= "172.72.0.16:33062" loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063" loose-group_replication_bootstrap_group=OFF loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17" report_host=172.72.0.16 report_port=3306 EOF cat > /usr/local/mysql/lhrmgr17/conf.d/my.cnf <<"EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv='' server-id = 802033067 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=lhrmgr16-relay-bin-ip16 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= "172.72.0.17:33063" loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063" loose-group_replication_bootstrap_group=OFF loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17" report_host=172.72.0.17 report_port=3306 EOF |
三、重启MySQL环境
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # 重启MySQL docker restart mysql8020mgr33065 mysql8020mgr33066 mysql8020mgr33067 docker ps # 进入MySQL docker exec -it mysql8020mgr33065 bash docker exec -it mysql8020mgr33065 mysql -uroot -plhr #远程连接MySQL mysql -uroot -plhr -h192.168.1.35 -P33065 mysql -uroot -plhr -h192.168.1.35 -P33066 mysql -uroot -plhr -h192.168.1.35 -P33067 # 查看MySQL日志 docker logs -f --tail 10 mysql8020mgr33065 docker logs -f --tail 10 mysql8020mgr33066 docker logs -f --tail 10 mysql8020mgr33067 # 查看MySQL的主机名、server_id和server_uuid mysql -uroot -plhr -h192.168.1.35 -P33065 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -plhr -h192.168.1.35 -P33066 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -plhr -h192.168.1.35 -P33067 -e "select @@hostname,@@server_id,@@server_uuid" |
结果：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
牛掰
你抢我名字啊