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