MYSQL MGR 至少 mysql mgr配置

转载

云端筑梦师 2023-06-13 21:23:54

文章标签 MYSQL MGR 至少 mysql bootstrap 配置文件 文章分类 MySQL 数据库

说明：

此次配置的是异机的三台MGR环境，多实例的方式可能有些差异，建议根据实际情况调整。

主机 hostname 数据库端口 MGR配置端口 linux版本

192.169.100.160 mgr1 3307 33071 centos 6.5

192.169.100.161 mgr2 3307 33071 centos 6.5

192.169.100.162 mgr3 3307 33071 centos 6.5

配置步骤

1. 配置主机名和hosts文件(所有主机)

vi /etc/sysconfig/network
  NETWORKING=yes
  HOSTNAME=mgr1-3

vi /etc/hosts
 127.0.0.1 localhost localhost.localdomain
 192.169.100.160 mgr1
 192.169.100.161 mgr2
 192.169.100.162 mgr3

2.关闭防火墙和selinx

service iptables stop
chkconfig disalbe iptables

vi /etc/selinux/config 
 SELINUX=disabled

3. 配置数据库配置文件（数据库安装部分略过）

192.169.100.160 (以下是简单的MGR配置文件)

vi /etc/mymgr.cnf

[mysqld]
user=mysql
datadir=/data/mysql3307/data
basedir=/usr/local/mysql
port=3307
socket=/data/mysql3307/data/mysql.sock
log-error = /data/mysql3307/logs/mysql3306.err
log-bin = /data/mysql3307/logs/binary/mybinlog
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="e2f6a3b1-2e19-4873-a5d6-9ab8455f5ce2" #该名称的配置是三个节点须一致，否者将无法加入其它节点
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.169.100.160:33071" #对应自己的ip和端口
loose-group_replication_group_seeds="192.169.100.160:33071,192.169.100.161:33071,192.169.100.162:33071"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
relay-log-recovery=1
#skip-grant-tables=1

192.169.100.161 (以下是简单的MGR配置文件)

[mysqld]
user=mysql
datadir=/data/mysql3307/data
basedir=/usr/local/mysql
port=3307
socket=/data/mysql3307/data/mysql.sock
log-error = /data/mysql3307/logs/mysql3306.err
log-bin = /data/mysql3307/logs/binary/mybinlog
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
#skip-grant-tables=1
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="e2f6a3b1-2e19-4873-a5d6-9ab8455f5ce2"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.169.100.161:33071"
loose-group_replication_group_seeds="192.169.100.160:33071,192.169.100.161:33071,192.169.100.162:33071"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
relay-log-recovery=1

192.169.100.162 (以下是简单的MGR配置文件)

[mysqld]
user=mysql
datadir=/data/mysql3307/data
basedir=/usr/local/mysql
port=3307
socket=/data/mysql3307/data/mysql.sock
log-error = /data/mysql3307/logs/mysql3306.err
log-bin = /data/mysql3307/logs/binary/mybinlog
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
#skip-grant-tables=1
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="e2f6a3b1-2e19-4873-a5d6-9ab8455f5ce2"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.169.100.162:33071"
loose-group_replication_group_seeds="192.169.100.160:33071,192.169.100.161:33071,192.169.100.162:33071"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
relay-log-recovery=1

4. 初始化三台mysql 数据库（数据库安装部分略过）

/usr/local/mysql/bin/mysqld --defaults-file=/etc/mymgr.cnf --basedir=/usr/local/mysql --datadir=/data/mysql3307/data --user=mysql --initialize &

5.启动数据库

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/mymgr.cnf &
配置密码之后登录：
mysql -uroot -proot -S /data/mysql3307/data/mysql.sock

6. 添加mgr用户用于复制：（所有节点）

set sql_log_bin=0;
grant replication slave,replication client on *.* to repl@'localhost' identified by 'repl';
grant replication slave,replication client on *.* to repl@'127.0.0.1' identified by 'repl';
grant replication slave,replication client on *.* to repl@'192.169.100.%' identified by 'repl';
SET SQL_LOG_BIN=1;
flush privileges;

7.192.169.100.160 上执行（开启分布式复制)：

开启分布式复制
change master to master_user='repl',master_password='repl' for channel 'group_replication_recovery';

加载GR插件
install plugin group_replication soname 'group_replication.so';
show plugins;

启动复制程序(主)

set global group_replication_bootstrap_group=ON;

启动单主模式：

set global group_replication_enforce_update_everywhere_checks=OFF;

set global group_replication_single_primary_mode=on;

允许其它节点加入：

set global group_replication_allow_local_disjoint_gtids_join=ON;

设置白名单：
SET GLOBAL group_replication_ip_whitelist="192.169.100.160,192.169.100.161,192.169.100.162";
start group_replication;
set global group_replication_bootstrap_group=OFF;

#检测组是否创建并已加入新成员
select * from performance_schema.replication_group_members;

8.192.169.100.161 和192.169.100.162 上执行：

备库启动
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=on;
set global group_replication_allow_local_disjoint_gtids_join=ON;
SET GLOBAL group_replication_ip_whitelist="192.169.100.160,192.169.100.161,192.169.100.162";
START GROUP_REPLICATION;

9.MGR 环境下设置vip

*/1 * * * * sh /etc/mysqlvip/call_setvip.sh

[root@mysql1 ~]# cat /etc/mysqlvip/call_setvip.sh

#!/bin/sh
export LANG=en_US.UTF-8
dir=`dirname $0`
ps -ef |grep  -w "setvip.sh"
CNT=`ps -ef |grep -w "setvip.sh"|grep -v grep|wc -l`
echo count:  $CNT
cd ${curdir}
if [ $CNT -eq 0 ] ; then
cd ${dir}
nohup sh  ${dir}/setvip.sh  &
echo 'start setvip  at '`date "+%Y-%m-%d %H:%M"` >> ${dir}/start_setvip.log
fi

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。