安装、初始化操作汇总
#初始化、重置mysql
rm -rf /data/mysql/ ; mkdir -p /data/mysql/data /data/mysql/log ; chown -R mysql:mysql /data/mysql/ ; chmod -R 755 /data/mysql/ ;
#重置mysql后第一次启动，需要更改密码，而启动复制插件即加入集群为slave状态，只读，无法执行更改密码
vim /etc/my.cnf
loose-group_replication_start_on_boot = OFF
service mysqld start
#mysql 临时密码
#获取临时密码 grep 'temporary password' /data/mysql/log/mysqld.log #临时密码登录 mysql -uroot -p
#第一次安装启动，必须修改默认密码（第一条更新操作必须是ALTER USER），先关掉binlog，避免其他节点同步时异常
SET SQL_LOG_BIN=0;ALTER USER 'root'@'localhost' IDENTIFIED BY 'Pass.Test.123';flush privileges;SET SQL_LOG_BIN=1;
#集群第一台执行：开放root账号远程登录
use mysql;update user set host='%' where user='root';flush privileges;
#集群第一台执行：创建mgr集群同步账户
grant replication slave on . to 'repl_user'@'%' identified by 'Repl.pass.123';flush privileges; use mysql;select user,host from user;
#集群第一台执行：初始化创建mgr集群，在【第一次安装】或【全部节点离线后第一次启动】时执行
stop group_replication;
set global group_replication_bootstrap_group=on;
CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='Repl.pass.123' FOR CHANNEL 'group_replication_recovery';
start group_replication;
set global group_replication_bootstrap_group=off;
#集群第一台执行：测试数据
create database mgr_test default character set utf8;
use mgr_test;
create table person (id int(10) primary key auto_increment not null,name varchar(20),age int(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into person(id,name,age) values(1,'zhangsan',21);
insert into person(id,name,age) values(2,'lisi',23);
select * from person;
create table person2 (id int(10) primary key auto_increment not null,name varchar(20),age int(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into person2(id,name,age) values(1,'zhangsan',21);
insert into person2(id,name,age) values(2,'lisi',22);
select * from person2;
#集群第二台开始执行：加入集群
stop group_replication;CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='Repl.pass.123' FOR CHANNEL 'group_replication_recovery';start group_replication;
#查询集群节点
select * from performance_schema.replication_group_members ;
#查询master节点
select * from performance_schema.global_status where variable_name like '%group%';
#设置复制插件自动启动
vim /etc/my.cnf
loose-group_replication_start_on_boot = ON
service mysqld restart
所有节点离线重启后运维操作
#第一个节点创建集群，设置master
mysql -uroot -pPass.Test.123 \
-e "stop group_replication;\
set global group_replication_bootstrap_group=on;\
CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='Repl.pass.123' FOR CHANNEL 'group_replication_recovery';\
start group_replication;\
set global group_replication_bootstrap_group=off;"
#其他节点加入集群
mysql -uroot -pPass.Test.123 \
-e "stop group_replication;\
CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='Repl.pass.123' FOR CHANNEL 'group_replication_recovery';\
start group_replication;"
#查询主节点
mysql -uroot -pPass.Test.123 -e "SELECT * FROM performance_schema.replication_group_members WHERE MEMBER_ID = (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member')" | awk 'NR==2{print($3)}'
#查询所有节点
mysql -uroot -pPass.Test.123 -e "select * from performance_schema.replication_group_members;"