Mysql MGR + Consul + Consul-template + Haproxy 搭建mysql 高可用集群 (二)
Mysql MGR 的搭建:
下载新版的mysql 8.0.13
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.13-el7-x86_64.tar
使用uuidgen命令生成个uuid:
uuidgen cc5e2627-2285-451f-86e6-0be21581539f
三台物理机部署:
修改hosts文件:
vi /etc/hosts 172.16.0.90 swarm1 172.16.0.76 swarm2 172.16.0.175 swarm3
创建工作目录:
mkdir /data/mysql_root/base_phony/40010 mkdir /data/mysql_root/data/40010 #将解压后的mysql软体放到/data/mysql_root/base_phony/40010下 chown -R mysql:mysql /data/mysql_root/base_phony/40010 chown -R mysql:mysql /data/mysql_root/data/40010
创建my.cnf文件:已swarm1的为例
(swarm1的server-id为1,swarm1的server-id为2,swarm1的server-id为3;group_replication_local_address修改为本机hostname或ip)
cd /data/mysql_root/data/40010 vi my.cnf [client] port = 40010 socket = /tmp/mysql_40010.sock [mysqld] port = 40010 socket = /tmp/mysql_40010.sock datadir = /data/mysql_root/data/40010 slow_query_log_file = /data1/mysql_root/data/40010/slow_query.log log-error = /data/mysql_root/data/40010/error.log server-id = 1 #for MGR gtid_mode=on enforce-gtid-consistency=on binlog_checksum=NONE loose-group_replication_ip_whitelist="swarm1,swarm2,swarm3" loose-group_replication_group_name="cc5e2627-2285-451f-86e6-0be21581539f" loose-group_replication_local_address="swarm1:2525" loose-group_replication_group_seeds="swarm1:2525,swarm22525,swarm3:2525"
参照官方文档,mysql8中一些默认参数不用为MGR修改。
初始化mysql:
cd /data/mysql_root/base_phony/40010/bin/ ./mysqld --defaults-file=/data/mysql_root/data/40010/my.cnf --basedir=/data/mysql_root/base_phony/40010 --initialize-insecure --user=mysql
启动mysql:
nohup /data/mysql_root/base_phony/40010/bin/mysqld --defaults-file=/data/mysql_root/data/40010/my.cnf --basedir=/data/mysql_root/base_phony/40010 --user=mysql &
安装MGR插件:
mysql -uroot -S /home/mysql/mysql40010/mysql40010.sock mysql> install plugin group_replication soname 'group_replication.so';
至此,三台MGR实例初始化完毕,接下来配置启动MGR
登录swarm1的mysql:
set sql_log_bin=0; CREATE USER repl@'%' IDENTIFIED WITH 'mysql_native_password' BY 'repl'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES; set sql_log_bin=1; CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery'; # group_replication_bootstrap_group参数仅在第一台启动的MGR实例上开启,其他实例不要操作 set global group_replication_bootstrap_group=ON; START GROUP_REPLICATION; set global group_replication_bootstrap_group=OFF; SELECT * FROM performance_schema.replication_group_members;
登录swarm2、swarm3的mysql:
set sql_log_bin=0; CREATE USER repl@'%' IDENTIFIED WITH 'mysql_native_password' BY 'repl'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES; set sql_log_bin=1; CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery'; START GROUP_REPLICATION; SELECT * FROM performance_schema.replication_group_members;
至此,一个三节点的MGR集群搭建完毕,可以实现MGR的特性了。
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | bbdb8668-1866-11e9-89a5-525400447342 | swarm1 | 40010 | ONLINE | PRIMARY | 8.0.13 | | group_replication_applier | c433a5af-1866-11e9-96b6-525400cd1c31 | swarm2 | 40010 | ONLINE | SECONDARY | 8.0.13 | | group_replication_applier | eabb341d-1895-11e9-90dd-525400dd19e5 | swarm3 | 40010 | ONLINE | SECONDARY | 8.0.13 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
遇到的问题:
发现节点重启后经常性的无法加入集群,检查报错日志如下:
2019-01-16T15:38:02.952875Z 651 [ERROR] [MY-010584] [Repl] Slave I/O for channel 'group_replication_recovery': error connecting to master 'repl@swarm3:40010' - retry-time: 60 retries: 1, Error_code: MY-002061
反复检查复制用户的账号密码,未发现错误,开始使用的创建语句是:
CREATE USER repl@'%' IDENTIFIED BY 'repl';
修改为:
CREATE USER repl@'%' IDENTIFIED WITH 'mysql_native_password' BY 'repl';
此后节点间间接未发生问题,应该是和mysql8修改了默认加密方式有关。