Mysql-MGR

Mysql-MGR

准备三台机器

清理环境

#三台机器都做：
#修改主机名修改/etc/hosts
#关闭和禁用防火墙
#关闭和禁用SELinux
#生成密钥对
#传输密钥对
#验证免密登陆
yum remove mysql-server -y
rm -rf /etc/my.cnf.d/
rm -rf /var/lib/mysql/
rm -rf /var/log/mysql/

每个机器下载安装数据库

mount /dev/sr0 /mnt
yum install mysql-server -y
systemctl start mysqld
systemctl stop mysqld

配置第一个服务器：（主服务器）

cd /etc/my.cnf.d/
vim mysql-server.cnf

追加内容

disabled_storage_engines=“MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”

#server_id确保每个机器不一样  server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

plugin_load_add='group_replication.so'

#uuid确保每个机器都一样，可以用uuidgen生成
group_replication_group_name="8e1969ec-3ae3-4bd1-b80f-6de58b837ff5"
group_replication_start_on_boot=off

#当前主机的主机名和复制组端口，建议用主机名group_replication_local_address= "mgr01:33061"
group_replication_group_seeds= "mgr01:33061,mgr02:33061,mgr03:33061"
group_replication_bootstrap_group=off

配置文件编辑完成

启动数据库服务

systemctl start mysqld
mysql -uroot -p
mysql> use mysql;

创建复制组的用户

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Test@1234';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;

复制用户凭据到复制组通道

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Test@1234' FOR CHANNEL 'group_replication_recovery';

查看复制组插件是否装载

mysql> SHOW PLUGINS;

如果有以下内容则表示已装载

group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL

启动复制组

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='Test@1234';
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

查看复制组

mysql> SELECT * FROM performance_schema.replication_group_members;

第二台的相关配置

cd /etc/my.cnf.d/
rm -rf mysql-server.cnf 
scp mgr01:/etc/my.cnf.d/mysql-server.cnf .

只需要编辑以下两个相关配置

server_id=2
group_replication_local_address= "mgr02:33061"

启动mysql服务器、连接服务器

systemctl start  mysqld
mysql -uroot -p

切换数据库

use mysql;

创建复制组用户

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY'Test@1234';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

复制用户凭据到复制组通道

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Test@1234' FOR CHANNEL 'group_replication_recovery';

查看复制组插件是否装载

mysql> SHOW PLUGINS;

如果有以下内容则表示已装载

group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL

启动复制组

mysql> START GROUP_REPLICATION USER='rpl_user', PASSWORD='Test@1234';

查看复制组

mysql> SELECT * FROM performance_schema.replication_group_members;

第三台和第二台服务器的配置是一样的

停止复制组

mysql> stop GROUP_REPLICATION