MySQL 搭建 MGR 集群（docker版）
2021-07-16 本文已影响0人
Saxon_323e
Installing MySQL Group Replication for Docker
1. 拉取 image
docker pull mysql/mysql-server:5.7
2. 创建虚拟网络
docker network create wl4gnet
3. 本地新建目录
mkdir -p /mnt/disk1/mysql/node1/conf/
mkdir -p /mnt/disk1/mysql/node2/conf/
mkdir -p /mnt/disk1/mysql/node3/conf/
4. 新建 my.cnf 文件，添加配置
vi /mnt/disk1/mysql/node1/conf/my.cnf
vi /mnt/disk1/mysql/node2/conf/my.cnf
vi /mnt/disk1/mysql/node3/conf/my.cnf
例如 node1 内容如下：
# Copyright (c) 2017 ~ 2025, the original author wangl.sir individual Inc,
# All rights reserved. Contact us wanglsir<wangl@gmail.com, 983708408@qq.com>
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
# @ad. https://github.com/wl4g/xcloud-dopaas or https://gitee.com/wl4g/xcloud-dopaas
# @see https://yq.aliyun.com/articles/741365
# @see https://www.jianshu.com/p/f6437e914584
# @see https://wang4ever.lofter.com/post/1cca927e_1c752cc99
#
# For more setup recommendations or green installation guidelines,
# @see http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
port = 13306
# Configured at install time. See: script/mysqld_install.sh
#basedir = /home/wl4g_mysql/mysql57-master3
#datadir = {basedir}/data
#socket = {basedir}/run/mysqld-{MYSQL_USER}.sock
#pid-file = {basedir}/run/mysqld-{MYSQL_USER}.pid
#log-error = {basedir}/log/mysqld-{MYSQL_USER}.err
# To turn on a very important data integrity option: logging changes to the binary log between backups.
#log_bin = {basedir}/data/mysqld-{MYSQL_USER}-log.bin
expire_logs_days = 15
# Warning: Unsigned value 2147483648 adjusted to 1073741824, max allowed SET to 1G ??
max_binlog_size = 1G
server_id = 1
# Ignore SQL condition case.(1:ignore)
# Note: When mysql8+, after initialization, is is not allowed to change this setting.
# @see http://blog.itpub.net/20893244/viewspace-2565069/
# @see https://bugs.mysql.com/bug.php?id=90695
#lower_case_table_names = 1
# Set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 1024M
max_allowed_packet = 1024M
#slave_max_allowed_packet = 2048M
max_connections = 200
# To SET options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
explicit_defaults_for_timestamp = true
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links = 0
# Recommended in standard MySQL setup
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# Override by using script/mysqld_green_install.sh
#[mysqld_safe]
# {datadir}/log/mysqld.log
#log-error = log/mysqld.log
# {datadir}/mysqld.pid
#pid-file = mysqld.pid
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
binlog_format=ROW
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820"
loose-group_replication_start_on_boot=off
# It's not allowed to use Double quotation marks(") and (=) No spaces after, refer to: https://bugs.mysql.com/bug.php?id=98524
loose-group_replication_local_address='localhost:23306'
loose-group_replication_group_seeds='localhost:23306,localhost:23307,localhost:23308'
loose-group_replication_ssl_mode=DISABLED
loose-group_replication_bootstrap_group=off
loose-group_replication_recovery_user=repl
# Single primary mode:
#loose-group_replication_single_primary_mode=on
#loose-group_replication_enforce_update_everywhere_checks=off
# Multi primary mode:
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist='192.168.1.0/24,10.0.0.0/24,127.0.0.1'
4.1 注意配置
loose-group_replication_local_address= "localhost:33060" #注: 本地监听地址，每个节点不同
5. 启动服务
node1:
docker run -p 13306:3306 --name=mysql_node1 \
--net=wl4gnet \
--mount type=bind,src=/mnt/disk1/mysql/node1/conf/my.cnf,dst=/etc/my.cnf \
--mount type=bind,src=/mnt/disk1/mysql/node1/data,dst=/var/lib/mysql \
-d mysql/mysql-server:5.7
node2:
docker run -p 13307:3306 --name=mysql_node2 \
--net=wl4gnet \
--mount type=bind,src=/mnt/disk1/mysql/node2/conf/my.cnf,dst=/etc/my.cnf \
--mount type=bind,src=/mnt/disk1/mysql/node2/data,dst=/var/lib/mysql \
-d mysql/mysql-server:5.7
node3:
docker run -p 13308:3306 --name=mysql_node3 \
--net=wl4gnet \
--mount type=bind,src=/mnt/disk1/mysql/node3/conf/my.cnf,dst=/etc/my.cnf \
--mount type=bind,src=/mnt/disk1/mysql/node3/data,dst=/var/lib/mysql \
-d mysql/mysql-server:5.7
6. 修改远程登录和密码
-- 初次安装必须修改密码的方式
alter user root@localhost identified by '123456';
-- Mysql5.7 sql更改密码
set password for root@localhost=password('123456');
-- 注：Mysql8+ 必须显示指定密码插件为 mysql_native_password
alter user root@localhost identified with mysql_native_password by '123456';
-- 授权允许远程登录方式1
grant all privileges on *.* to root@'%' identified by '123456';
-- 授权允许远程登录方式2
use mysql; update user set host='%' where user='root' and host='localhost';
-- 刷新权限
flush privileges;
7. 配置 MGR
7.1 node1(首先启动，引导节点)
-- 安装(mysql8+默认未安装)
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
-- 配置账户使其他node登录进来
SET SQL_LOG_BIN=0;
GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY '123456';
SET SQL_LOG_BIN=1;
-- 使用账户密码登录其他node
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
-- 只需node1需要执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
-- STOP GROUP_REPLICATION;
-- 查看集群成员状态
select * from performance_schema.replication_group_members;
7.2. node2、node3(成员节点)
-- 安装(mysql8+默认未安装)
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
-- 配置账户使其他node登录进来
SET SQL_LOG_BIN=0;
GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY '123456';
SET SQL_LOG_BIN=1;
-- 使用账户密码登录其他node
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
-- 除node1外其他需执行此配置
SET GLOBAL group_replication_allow_local_disjoint_gtids_join=ON; # 高版本已过时
SET GLOBAL group_replication_bootstrap_group=OFF;
START GROUP_REPLICATION;
-- STOP GROUP_REPLICATION;
-- 查看集群成员状态
select * from performance_schema.replication_group_members;
7.3. 其他相关指令
SET GLOBAL read_only=0; -- 1:启动/0:关闭，如：多主模式式，当stop group_replication时会自动将此节点设置为ON，启动后会自动设为OFF
FLUSH PRIVILEGES;
SHOW VARIABLES like 'read_only';
SHOW VARIABLES like '%slow_query_log%';
SHOW VARIABLES like '%long_query_time%';
SHOW VARIABLES like '%log_queries_not_using_indexes%';
-- 慢SQL统计
SET GLOBAL slow_query_log=on;
SET GLOBAL long_query_time=0.1;
SET GLOBAL log_queries_not_using_indexes=0;
8. FAQ
8.1 初始执行 start group_replication; 启动 MGR 插件失败
...
2021-04-15T12:55:23.019059Z 3 [ERROR] Plugin group_replication reported: '[GCS] Invalid hostname or IP address ( "127.0.0.1:13306") assigned to the parameter local_node!'
2021-04-15T12:55:23.019091Z 3 [ERROR] Plugin group_replication reported: 'Unable to initialize the group communication engine'
2021-04-15T12:55:23.019102Z 3 [ERROR] Plugin group_replication reported: 'Error on group communication engine initialization'
...
启动 MGR 失败，从日志看是
loose-group_replication_group_seeds和loose-group_replication_local_address相关配置有问题... 最终确认原因是 MySQL5.7 读取配置存在 bug，请参考『 MySQL 读取 my.cnf 配置键值'='号左右边空格 bug』，建议最好不要空格最靠谱.
8.2 运行期间，由于主机故障或误操作等错误情况，导致部分节点故障，且恢复执行 start group_replication; 启动 MGR 插件失败
...
2021-04-17T13:39:57.759602Z 121 [ERROR] Slave SQL for channel 'group_replication_applier': Could not execute Update_rows event on table aabbcc.t_test; Can't find record in 't_test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND, Error_code: 1032
2021-04-17T13:39:57.759627Z 121 [Warning] Slave: Can't find record in 't_test' Error_code: 1032
2021-04-17T13:39:57.759641Z 121 [ERROR] Plugin group_replication reported: 'The applier thread execution was aborted. Unable to process more transactions, this member will now leave the group.'
2021-04-17T13:39:57.759662Z 121 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'FIRST' position 0.
2021-04-17T13:39:57.759674Z 118 [ERROR] Plugin group_replication reported: 'Fatal error during execution on the Applier process of Group Replication. The server will now leave the group.'
2021-04-17T13:39:57.759727Z 118 [ERROR] Plugin group_replication reported: '[GCS] The member is already leaving or joining a group.'
2021-04-17T13:39:57.759757Z 118 [ERROR] Plugin group_replication reported: 'Unable to confirm whether the server has left the group or not. Check performance_schema.replication_group_members to check group membership information.'
2021-04-17T13:39:57.759775Z 118 [Note] Plugin group_replication reported: 'Going to wait for view modification'
2021-04-17T13:39:58.760466Z 0 [ERROR] Plugin group_replication reported: 'There was a previous plugin error while the member joined the group. The member will now exit the group.'
...
从日志分析
Can't find record in 't_test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND, Error_code: 1032说明主主(主从)之间复制存在不一致问题，解决供参考：
STOP GROUP_REPLICATION; -- 停止出现问题的节点MGR
RESET SLAVE ALL FOR CHANNEL "group_replication_applier"; -- 重置复制通道（logbin/relaylog不一致）
START GROUP_REPLICATION; -- 重启MGR
若还是不行，则尝试执行停止 stop group_replication 所有节点 MGR 复制，然后执行重置 RESET SLAVE ALL FOR CHANNEL "group_replication_applier" 再次逐个启动 start group_replication - 注：此操作需谨慎使用，操作前请先中断所有业务系统 jdbc 连接，且对各个节点数据备份。
注：已下线的节点 MGR 插件会自动设置 read_only=ON，千万不要手动 read_only=OFF 然后修改任何数据，否则就算后续恢复正常后，被强制修改的记录将无法被更新，报错
3101 - Plugin instructed the server to rollback the current transaction.
8.3 从 Master 依次启动 MGR，从节点一直是 RECOVERING 状态
[ERROR] Slave SQL for channel'group_replication_recovery': Error 'Operation CREATE USER failed for'piriineos'@'localhost'' on query. Default database: ''. Query: 'CREATE USER'piriineos'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS'*077BFD72D9E814194FBA9A90A6A8DB13BC476718'', Error_code: 1396
slave apply 了 master 操作系统表的事务，这里是 master 上创建用户的语句，忘记
set sql_log_bin=0;了，不想让 slave 执行的操作都需要设置它，这里的解决办法是直接把这个事务的 gtid 放到 purged 表中，跳过他即可。
-- 重新创建 MGR 主从复制，先停止所有节点
STOP GROUP_REPLICATION; -- 注意执行顺序，先停止从节点最后停止主节点。启动顺序则相反，是先启动主节点
-- 重置主节点
-- 作用：
-- 删除binlog索引文件中列出的所有binlog文件;
-- 清空binlog索引文件;
-- 创建一个新的binlog文件;
-- 清空系统变量gtid_purged和gtid_executed ;
-- 在MySQL 5.7.5 及后续版本中, RESET MASTER还会会清空 [mysql.gtid_executed](https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-concepts.html#replication-gtids-gtid-executed-table) 数据表;
RESET MASTER;
-- 重置从节点
-- 作用：
-- 清除slave 复制时的master binlog的位置;
-- 清空master info, relay log info ;
-- 删除所有的relay log文件，并创建一个新的relay log文件;
-- 重置复制延迟(change master to 的 MASTER_DELAY 参数指定的)为 0;
-- 另外，不会改变gtid_executed or gtid_purged ，也不会改变复制连接使用的参数，例如 master host, master port, master user, or master password ;
-- 如果是执行 RESET SLAVE ALL 则需重新执行 change master to 指定复制连接参数;
RESET SLAVE;
-- 然后重复 7.1 和 7.2，重启所有节点 MGR
8.4 START GROUPLICATION 启动报错日志: Error on opening a connection to 192.168.2.1:33061 on local port: 33061.’
mysql> START GROUP_REPLICATION;
[ERROR] Plugin group_replication reported: ‘[GCS] Error on opening a connection to 192.168.2.1:33061 on local port: 33061.’
[ERROR] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.2.2:33061 on local port: 33061.
'[ERROR] Plugin group_replication reported: ‘[GCS] Error connecting to all peers. Member join failed. Local port: 33061’
[Warning] Plugin group_replication reported: ‘read failed’
Error reading relay log event for channel ‘group_replication_applier’: slave SQL thread was killed
引导节点可能设置正确，重新设置重启 Group Replication
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;
9. MGR 后期运维细则
参考资料 1 MySQL Group Replicaiton 常见错误
参考资料 2 细细探究 MySQL Group Replicaiton 配置维护故障处理全集
参考资料 3 基于 ansible 快速 mysql 环境搭建
