MySQL MGR集群部署运维常用脚本

标签：group 运维 replication MySQL MGR MASTER user mysql 集群

Linux系统-部署-运维系列导航

 

安装、初始化操作汇总

注意：Pass.Test.123替换为真实的root账号密码，Repl.pass.123替换为真实的mgr集群创建时 group_replication_recovery 通道的密码   #初始化、重置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

 

所有节点离线重启后运维操作

注意：Pass.Test.123替换为真实的root账号密码，Repl.pass.123替换为真实的mgr集群创建时 group_replication_recovery 通道的密码   #第一个节点创建集群，设置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;"

 

 

标签：group,运维,replication,MySQL,MGR,MASTER,user,mysql,集群
From： https://www.cnblogs.com/xiaoyaozhe/p/17671337.html