原 【DB宝18】在Docker中安装使用MySQL高可用之MGR
八、测试同步
在主节点上执行以下命令,然后在其它节点查询:
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 | lhrmgr16 | 802033066 | +----+----------+-----------+ 1 row in set (0.02 sec) |
九、MGR新增节点
9.1、创建新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 | mkdir -p /usr/local/mysql/lhrmgr18/conf.d mkdir -p /usr/local/mysql/lhrmgr18/data docker run -d --name mysql8020mgr33068 \ -h lhrmgr18 -p 33068:3306 --net=mysql-network --ip 172.72.0.18 \ -v /usr/local/mysql/lhrmgr18/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/lhrmgr18/data:/var/lib/mysql/ \ -e MYSQL_ROOT_PASSWORD=lhr \ -e TZ=Asia/Shanghai \ mysql:8.0.20 cat > /usr/local/mysql/lhrmgr18/conf.d/my.cnf <<"EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv='' server-id = 802033068 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 log_slave_updates=on master_info_repository=TABLE relay_log_info_repository=TABLE relay_log=lhrmgr18-relay-bin-ip18 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.18:33064" loose-group_replication_group_seeds= "172.72.0.15:33061,172.72.0.16:33062,172.72.0.17:33063,172.72.0.18:33064" loose-group_replication_bootstrap_group=OFF loose-group_replication_ip_whitelist="172.72.0.15,172.72.0.16,172.72.0.17,172.72.0.18" report_host=172.72.0.18 report_port=3306 EOF docker restart mysql8020mgr33068 docker ps 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" mysql -uroot -plhr -h192.168.1.35 -P33068 -e "select @@hostname,@@server_id,@@server_uuid" 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 -uroot -plhr -h192.168.1.35 -P33068 docker logs -f --tail 10 mysql8020mgr33065 docker logs -f --tail 10 mysql8020mgr33066 docker logs -f --tail 10 mysql8020mgr33067 docker logs -f --tail 10 mysql8020mgr33068 |
9.2、新节点安装MGR插件
1 2 3 | -- 安装MGR插件(新增节点执行) INSTALL PLUGIN group_replication SONAME 'group_replication.so'; show plugins; |
9.3、新节点设置复制账号
1 2 3 4 5 6 7 | -- 设置复制账号(新增节点执行) SET SQL_LOG_BIN=0; CREATE USER repl@'%' IDENTIFIED BY 'lhr'; GRANT REPLICATION SLAVE ON *.* TO repl@'%'; FLUSH PRIVILEGES; SET SQL_LOG_BIN=1; CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery'; |
牛掰
你抢我名字啊