mysql-mgr

关键词：MySQL高可用 原生高可用功能 MGR

步骤

#################################### 安装 ######################################################

yum直接安装

shell> grep 'temporary password' /var/log/mysqld.log
shell> mysql --connect-expired-password -uroot -p'jyjsPo.eT9g)'
mysql> SET SQL_LOG_BIN=0;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'As4k.top';
mysql> GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'As4k.top' WITH GRANT OPTION;
mysql> DROP USER 'root'@'localhost';
mysql> SET SQL_LOG_BIN=1;

如果需要清空数据，可以
rm -rf /var/lib/mysql
systemctl stop mysqld

####################################### 在机器1执行 ############################################

SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SET SQL_LOG_BIN=1;

SET GLOBAL group_replication_ip_whitelist="192.168.1.113,192.168.1.114,192.168.1.118,172.0.0.0/8";
SET GLOBAL group_replication_ip_whitelist="dp-thrall-mysql1,dp-thrall-mysql2,dp-thrall-mysql3,172.0.0.0/8";

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
SELECT * FROM performance_schema.replication_group_members;


####################################### 在机器2，机器3执行 #######################################

SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SET SQL_LOG_BIN=1;

####################################### 在机器2，机器3执行 #######################################

需要1个1个执行，前1个机器执行成功返回，再执行后1个机器
RESET MASTER;
START GROUP_REPLICATION;

####################################### 检查步骤 可以在任意节点执行 #################################

SELECT * FROM performance_schema.replication_group_members;

#####################################################################################################

配置文件

# Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

# !includedir /etc/mysql/conf.d/
# !includedir /etc/mysql/mysql.conf.d/

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
#server-id = 11
bind-address = 0.0.0.0
connect_timeout = 15
interactive_timeout = 28800
wait_timeout = 28800
net_read_timeout = 30
net_write_timeout = 30
max_connections = 250
max_allowed_packet = 100M

transaction-isolation = READ-COMMITTED

gtid_mode=on
enforce_gtid_consistency=on
skip_slave_start=1
log-slave-updates=1

# tune performance
innodb_file_per_table = 1
innodb_lock_wait_timeout = 3600
innodb_log_file_size = 128M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 2048M
query_cache_size = 48M
query_cache_limit = 8M
tmp_table_size = 64M
read_buffer_size = 256M
binlog_cache_size = 128M

init-connect = 'SET NAMES utf8mb4'
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

skip-host-cache
skip-name-resolve

# http://serverfault.com/questions/405726/max-binlog-size-log-bin-size
binlog_format = row
max_binlog_size = 1G
log-bin = mysql-bin
max_relay_log_size = 1G
relay_log_space_limit = 10G
expire_logs_days = 3
log_slave_updates = ON
relay_log=dp-thrall-mysql1
datadir = /var/lib/mysql
log-error = /var/lib/mysql/mysqld.err
binlog_checksum=NONE
master_info_repository=TABLE
relay_log_info_repository=TABLE

# Group Replication
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot = off
#loose-group_replication_local_address = 'dp-thrall-mysql1:33061'
#loose-group_replication_group_seeds ='dp-thrall-mysql1:33061,dp-thrall-mysql2:33061,dp-thrall-mysql3:33061'
loose-group_replication_bootstrap_group = off
#report_host=dp-thrall-mysql1
#report_port=3306


# 第1台机器
# server-id = 11
# loose-group_replication_local_address = 'dp-thrall-mysql1:33062'
# loose-group_replication_group_seeds ='dp-thrall-mysql1:33062,dp-thrall-mysql2:33062,dp-thrall-mysql3:33062'
# port = 3307
# loose-group_replication_enforce_update_everywhere_checks=ON
# loose-group_replication_single_primary_mode=OFF

# 第2台机器
# server-id= 22
# loose-group_replication_local_address = 'dp-thrall-mysql2:33062'
# loose-group_replication_group_seeds ='dp-thrall-mysql1:33062,dp-thrall-mysql2:33062,dp-thrall-mysql3:33062'
# port = 3307

# 第3台机器
server-id= 33
loose-group_replication_local_address = 'dp-thrall-mysql3:33062'
loose-group_replication_group_seeds ='dp-thrall-mysql1:33062,dp-thrall-mysql2:33062,dp-thrall-mysql3:33062'
# port = 3307

MySQL Group Replication on Docker

  • https://mysqlhighavailability.com/setting-up-mysql-group-replication-with-mysql-docker-images/

    ######################################## 三台机器上安装好Docker ##################################################

192.168.1.113 mysql1
192.168.1.114 mysql2
192.168.1.118 mysql3

######################################## 在机器1上操作 准备MySQL配置文件  ##################################################

cat << 'EOF' > /root/my.cnf
[mysqld]
bind_address = 0.0.0.0
binlog_checksum=NONE
binlog_format = row
datadir=/var/lib/mysql
enforce_gtid_consistency='ON'
expire_logs_days = 3
gtid_mode='ON'
log_bin='mysql-bin.log'
log_error=/var/lib/mysql/mysqld_err.log
log_slave_updates='ON'
loose-group-replication-enforce-update-everywhere-checks='ON'
loose-group-replication-group-name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'
loose-group-replication-group-seeds='mysql1:33061,mysql2:33061,mysql3:33061'
#loose-group-replication-local-address="mysql$N:33061"
loose-group-replication-single-primary-mode='OFF'
loose-group-replication-start-on-boot='OFF'
master-info-repository='TABLE'
pid-file=/var/run/mysqld/mysqld.pid
port = 3306
relay-log-info-repository='TABLE'
relay-log-recovery='ON'
#server-id=11
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
transaction-write-set-extraction='XXHASH64'

server-id=11
loose-group-replication-local-address="mysql1:33061"
EOF

######################################## 在机器1上操作 准备MySQL启动脚本  ##################################################

cd /root && wget https://busybox.net/downloads/binaries/1.31.0-defconfig-multiarch-musl/busybox-x86_64

cat << 'EOF' > /root/mysql-docker.sh 
#!/bin/bash
#filename: mysql-docker.sh

CNAME=mysql
DIR=/home/mysql-data

# 这个参数 非常关键 3个机器上的启动脚本 就是这里不一样
XHOSTNAME=mysql1

start() {
docker run --name $CNAME -d \
--hostname $XHOSTNAME \
-v $DIR:/var/lib/mysql \
-v /root/my.cnf:/etc/my.cnf \
-v /root/busybox-x86_64:/root/busybox-x86_64 \
--add-host mysql1:192.168.1.113 \
--add-host mysql2:192.168.1.114 \
--add-host mysql3:192.168.1.118 \
-p 3306:3306 \
-p 33061:33061 \
-e MYSQL_ROOT_PASSWORD=123456 daocloud.io/library/mysql:5.7.26
}

stop() {
docker rm -fv $CNAME
}

clear() {
stop
rm -rf $DIR
}

restart() {
stop
sleep 1
start    
}

$1
EOF

######################################## 在机器2上操作 准备MySQL配置文件  ##################################################

cat << 'EOF' > /root/my.cnf
[mysqld]
bind_address = 0.0.0.0
binlog_checksum=NONE
binlog_format = row
datadir=/var/lib/mysql
enforce_gtid_consistency='ON'
expire_logs_days = 3
gtid_mode='ON'
log_bin='mysql-bin.log'
log_error=/var/lib/mysql/mysqld_err.log
log_slave_updates='ON'
loose-group-replication-enforce-update-everywhere-checks='ON'
loose-group-replication-group-name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'
loose-group-replication-group-seeds='mysql1:33061,mysql2:33061,mysql3:33061'
#loose-group-replication-local-address="mysql$N:33061"
loose-group-replication-single-primary-mode='OFF'
loose-group-replication-start-on-boot='OFF'
master-info-repository='TABLE'
pid-file=/var/run/mysqld/mysqld.pid
port = 3306
relay-log-info-repository='TABLE'
relay-log-recovery='ON'
#server-id=11
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
transaction-write-set-extraction='XXHASH64'

server-id=22
loose-group-replication-local-address="mysql2:33061"
EOF

######################################## 在机器2上操作 准备MySQL启动脚本  ##################################################

cd /root && wget https://busybox.net/downloads/binaries/1.31.0-defconfig-multiarch-musl/busybox-x86_64

cat << 'EOF' > /root/mysql-docker.sh 
#!/bin/bash
#filename: mysql-docker.sh

CNAME=mysql
DIR=/home/mysql-data

# 这个参数 非常关键 3个机器上的启动脚本 就是这里不一样
XHOSTNAME=mysql2

start() {
docker run --name $CNAME -d \
--hostname $XHOSTNAME \
-v $DIR:/var/lib/mysql \
-v /root/my.cnf:/etc/my.cnf \
-v /root/busybox-x86_64:/root/busybox-x86_64 \
--add-host mysql1:192.168.1.113 \
--add-host mysql2:192.168.1.114 \
--add-host mysql3:192.168.1.118 \
-p 3306:3306 \
-p 33061:33061 \
-e MYSQL_ROOT_PASSWORD=123456 daocloud.io/library/mysql:5.7.26
}

stop() {
docker rm -fv $CNAME
}

clear() {
stop
rm -rf $DIR
}

restart() {
stop
sleep 1
start    
}

$1
EOF

######################################## 在机器3上操作 准备MySQL配置文件  ##################################################

cat << 'EOF' > /root/my.cnf
[mysqld]
bind_address = 0.0.0.0
binlog_checksum=NONE
binlog_format = row
datadir=/var/lib/mysql
enforce_gtid_consistency='ON'
expire_logs_days = 3
gtid_mode='ON'
log_bin='mysql-bin.log'
log_error=/var/lib/mysql/mysqld_err.log
log_slave_updates='ON'
loose-group-replication-enforce-update-everywhere-checks='ON'
loose-group-replication-group-name='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'
loose-group-replication-group-seeds='mysql1:33061,mysql2:33061,mysql3:33061'
#loose-group-replication-local-address="mysql$N:33061"
loose-group-replication-single-primary-mode='OFF'
loose-group-replication-start-on-boot='OFF'
master-info-repository='TABLE'
pid-file=/var/run/mysqld/mysqld.pid
port = 3306
relay-log-info-repository='TABLE'
relay-log-recovery='ON'
#server-id=11
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
transaction-write-set-extraction='XXHASH64'

server-id=33
loose-group-replication-local-address="mysql3:33061"
EOF

######################################## 在机器3上操作 准备MySQL启动脚本  ##################################################

cd /root && wget https://busybox.net/downloads/binaries/1.31.0-defconfig-multiarch-musl/busybox-x86_64

cat << 'EOF' > /root/mysql-docker.sh 
#!/bin/bash
#filename: mysql-docker.sh

CNAME=mysql
DIR=/home/mysql-data

# 这个参数 非常关键 3个机器上的启动脚本 就是这里不一样
XHOSTNAME=mysql3

start() {
docker run --name $CNAME -d \
--hostname $XHOSTNAME \
-v $DIR:/var/lib/mysql \
-v /root/my.cnf:/etc/my.cnf \
-v /root/busybox-x86_64:/root/busybox-x86_64 \
--add-host mysql1:192.168.1.113 \
--add-host mysql2:192.168.1.114 \
--add-host mysql3:192.168.1.118 \
-p 3306:3306 \
-p 33061:33061 \
-e MYSQL_ROOT_PASSWORD=123456 daocloud.io/library/mysql:5.7.26
}

stop() {
docker rm -fv $CNAME
}
 
clear() {
stop
rm -rf $DIR
}

restart() {
stop
sleep 1
start    
}

$1
EOF

####################################### 在机器1，机器2，机器3执行 ###########################

bash -x /root/mysql-docker.sh start
docker exec -it mysql mysql -p123456
此时三台机器都已经安装好MySQL，并进入MySQL终端，接下来开始配置组复制

####################################### 在机器1执行 #######################################

SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SET GLOBAL group_replication_ip_whitelist="mysql1,mysql2,mysql3,192.168.1.113,192.168.1.114,192.168.1.118,172.0.0.0/8";
SET SQL_LOG_BIN=1;

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
SELECT * FROM performance_schema.replication_group_members;


####################################### 在机器2，机器3执行 ####################################

需要1个1个执行，前1个机器执行成功返回，再执行后1个机器

SET SQL_LOG_BIN=0;
CREATE USER repl@'%' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SET GLOBAL group_replication_ip_whitelist="mysql1,mysql2,mysql3,192.168.1.113,192.168.1.114,192.168.1.118,172.0.0.0/8";
SET SQL_LOG_BIN=1;

RESET MASTER;
START GROUP_REPLICATION;

####################################### 检查步骤 可以在任意节点执行 #################################

SELECT * FROM performance_schema.replication_group_members;
show global variables like 'group_replication_ip_whitelist';
show variables like '%binlog_checksum%';
show variables like '%single_primary_mode';


#####################################################################################################

参考资料

https://mysqlhighavailability.com/setting-up-mysql-group-replication-with-mysql-docker-images/
https://www.cnblogs.com/f-ck-need-u/p/9225442.html

csdn articleId=109730165