Installing MySQL Group Replication for Docker
1. 拉取 image
docker pull mysql/mysql-server:5.7
2. 創(chuàng)建虛擬網(wǎng)絡(luò)
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
內(nèi)容如下:
# 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 http://www.reibang.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" #注: 本地監(jiān)聽地址酸纲,每個(gè)節(jié)點(diǎn)不同
5. 啟動服務(wù)
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. 修改遠(yuǎn)程登錄和密碼
-- 初次安裝必須修改密碼的方式
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';
-- 授權(quán)允許遠(yuǎn)程登錄方式1
grant all privileges on *.* to root@'%' identified by '123456';
-- 授權(quán)允許遠(yuǎn)程登錄方式2
use mysql; update user set host='%' where user='root' and host='localhost';
-- 刷新權(quán)限
flush privileges;
7. 配置 MGR
7.1 node1(首先啟動悍手,引導(dǎo)節(jié)點(diǎn))
-- 安裝(mysql8+默認(rèn)未安裝)
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
-- 配置賬戶使其他node登錄進(jìn)來
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需要執(zhí)行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
-- STOP GROUP_REPLICATION;
-- 查看集群成員狀態(tài)
select * from performance_schema.replication_group_members;
7.2. node2产还、node3(成員節(jié)點(diǎn))
-- 安裝(mysql8+默認(rèn)未安裝)
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
-- 配置賬戶使其他node登錄進(jìn)來
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外其他需執(zhí)行此配置
SET GLOBAL group_replication_allow_local_disjoint_gtids_join=ON; # 高版本已過時(shí)
SET GLOBAL group_replication_bootstrap_group=OFF;
START GROUP_REPLICATION;
-- STOP GROUP_REPLICATION;
-- 查看集群成員狀態(tài)
select * from performance_schema.replication_group_members;
7.3. 其他相關(guān)指令
SET GLOBAL read_only=0; -- 1:啟動/0:關(guān)閉,如:多主模式式睬棚,當(dāng)stop group_replication時(shí)會自動將此節(jié)點(diǎn)設(shè)置為ON,啟動后會自動設(shè)為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統(tǒng)計(jì)
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 初始執(zhí)行 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
相關(guān)配置有問題... 最終確認(rèn)原因是 MySQL5.7 讀取配置存在 bug见秤,請參考『 MySQL 讀取 my.cnf 配置鍵值'='號左右邊空格 bug』,建議最好不要空格最靠譜.
8.2 運(yùn)行期間真椿,由于主機(jī)故障或誤操作等錯(cuò)誤情況鹃答,導(dǎo)致部分節(jié)點(diǎn)故障,且恢復(fù)執(zhí)行 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
說明主主(主從)之間復(fù)制存在不一致問題突硝,解決供參考:
STOP GROUP_REPLICATION; -- 停止出現(xiàn)問題的節(jié)點(diǎn)MGR
RESET SLAVE ALL FOR CHANNEL "group_replication_applier"; -- 重置復(fù)制通道(logbin/relaylog不一致)
START GROUP_REPLICATION; -- 重啟MGR
若還是不行测摔,則嘗試執(zhí)行停止 stop group_replication
所有節(jié)點(diǎn) MGR 復(fù)制,然后執(zhí)行重置 RESET SLAVE ALL FOR CHANNEL "group_replication_applier"
再次逐個(gè)啟動 start group_replication
- 注:此操作需謹(jǐn)慎使用解恰,操作前請先中斷所有業(yè)務(wù)系統(tǒng) jdbc 連接锋八,且對各個(gè)節(jié)點(diǎn)數(shù)據(jù)備份。
注:已下線的節(jié)點(diǎn) MGR 插件會自動設(shè)置 read_only=ON修噪,千萬不要手動 read_only=OFF 然后修改任何數(shù)據(jù)查库,否則就算后續(xù)恢復(fù)正常后,被強(qiáng)制修改的記錄將無法被更新黄琼,報(bào)錯(cuò)
3101 - Plugin instructed the server to rollback the current transaction.
8.3 從 Master 依次啟動 MGR樊销,從節(jié)點(diǎn)一直是 RECOVERING
狀態(tài)
[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 操作系統(tǒng)表的事務(wù),這里是 master 上創(chuàng)建用戶的語句脏款,忘記
set sql_log_bin=0;
了围苫,不想讓 slave 執(zhí)行的操作都需要設(shè)置它,這里的解決辦法是直接把這個(gè)事務(wù)的 gtid 放到 purged 表中撤师,跳過他即可剂府。
-- 重新創(chuàng)建 MGR 主從復(fù)制,先停止所有節(jié)點(diǎn)
STOP GROUP_REPLICATION; -- 注意執(zhí)行順序剃盾,先停止從節(jié)點(diǎn)最后停止主節(jié)點(diǎn)腺占。啟動順序則相反淤袜,是先啟動主節(jié)點(diǎn)
-- 重置主節(jié)點(diǎn)
-- 作用:
-- 刪除binlog索引文件中列出的所有binlog文件;
-- 清空binlog索引文件;
-- 創(chuàng)建一個(gè)新的binlog文件;
-- 清空系統(tǒng)變量gtid_purged和gtid_executed ;
-- 在MySQL 5.7.5 及后續(xù)版本中, RESET MASTER還會會清空 [mysql.gtid_executed](https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-concepts.html#replication-gtids-gtid-executed-table) 數(shù)據(jù)表;
RESET MASTER;
-- 重置從節(jié)點(diǎn)
-- 作用:
-- 清除slave 復(fù)制時(shí)的master binlog的位置;
-- 清空master info, relay log info ;
-- 刪除所有的relay log文件,并創(chuàng)建一個(gè)新的relay log文件;
-- 重置復(fù)制延遲(change master to 的 MASTER_DELAY 參數(shù)指定的)為 0;
-- 另外衰伯,不會改變gtid_executed or gtid_purged 铡羡,也不會改變復(fù)制連接使用的參數(shù),例如 master host, master port, master user, or master password ;
-- 如果是執(zhí)行 RESET SLAVE ALL 則需重新執(zhí)行 change master to 指定復(fù)制連接參數(shù);
RESET SLAVE;
-- 然后重復(fù) 7.1 和 7.2意鲸,重啟所有節(jié)點(diǎn) MGR
8.4 START GROUPLICATION
啟動報(bào)錯(cuò)日志: 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
引導(dǎo)節(jié)點(diǎn)可能設(shè)置正確烦周,重新設(shè)置重啟 Group Replication
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;
9. MGR 后期運(yùn)維細(xì)則
參考資料 1 MySQL Group Replicaiton 常見錯(cuò)誤
參考資料 2 細(xì)細(xì)探究 MySQL Group Replicaiton 配置維護(hù)故障處理全集
參考資料 3 基于 ansible 快速 mysql 環(huán)境搭建