MySQL 搭建 MGR 集群(docker版)

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_seedsloose-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.’

https://forums.percona.com/t/mysql-group-replication-error-on-opening-a-connection-to-192-168-2-1-33061-on-local-port-33061/8087

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)境搭建

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市怎顾,隨后出現(xiàn)的幾起案子读慎,更是在濱河造成了極大的恐慌,老刑警劉巖槐雾,帶你破解...
    沈念sama閱讀 206,723評論 6 481
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件夭委,死亡現(xiàn)場離奇詭異,居然都是意外死亡募强,警方通過查閱死者的電腦和手機(jī)闰靴,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 88,485評論 2 382
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來钻注,“玉大人蚂且,你說我怎么就攤上這事》担” “怎么了杏死?”我有些...
    開封第一講書人閱讀 152,998評論 0 344
  • 文/不壞的土叔 我叫張陵,是天一觀的道長捆交。 經(jīng)常有香客問我淑翼,道長,這世上最難降的妖魔是什么品追? 我笑而不...
    開封第一講書人閱讀 55,323評論 1 279
  • 正文 為了忘掉前任玄括,我火速辦了婚禮,結(jié)果婚禮上肉瓦,老公的妹妹穿的比我還像新娘遭京。我一直安慰自己,他們只是感情好泞莉,可當(dāng)我...
    茶點(diǎn)故事閱讀 64,355評論 5 374
  • 文/花漫 我一把揭開白布哪雕。 她就那樣靜靜地躺著,像睡著了一般鲫趁。 火紅的嫁衣襯著肌膚如雪斯嚎。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 49,079評論 1 285
  • 那天,我揣著相機(jī)與錄音堡僻,去河邊找鬼糠惫。 笑死,一個(gè)胖子當(dāng)著我的面吹牛钉疫,可吹牛的內(nèi)容都是我干的寞钥。 我是一名探鬼主播,決...
    沈念sama閱讀 38,389評論 3 400
  • 文/蒼蘭香墨 我猛地睜開眼陌选,長吁一口氣:“原來是場噩夢啊……” “哼!你這毒婦竟也來了蹄溉?” 一聲冷哼從身側(cè)響起咨油,我...
    開封第一講書人閱讀 37,019評論 0 259
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎柒爵,沒想到半個(gè)月后役电,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 43,519評論 1 300
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡棉胀,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 35,971評論 2 325
  • 正文 我和宋清朗相戀三年法瑟,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片唁奢。...
    茶點(diǎn)故事閱讀 38,100評論 1 333
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡霎挟,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出麻掸,到底是詐尸還是另有隱情酥夭,我是刑警寧澤,帶...
    沈念sama閱讀 33,738評論 4 324
  • 正文 年R本政府宣布脊奋,位于F島的核電站熬北,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏诚隙。R本人自食惡果不足惜讶隐,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,293評論 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望久又。 院中可真熱鬧巫延,春花似錦、人聲如沸地消。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,289評論 0 19
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽犯建。三九已至讲冠,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間适瓦,已是汗流浹背竿开。 一陣腳步聲響...
    開封第一講書人閱讀 31,517評論 1 262
  • 我被黑心中介騙來泰國打工谱仪, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人否彩。 一個(gè)月前我還...
    沈念sama閱讀 45,547評論 2 354
  • 正文 我出身青樓疯攒,卻偏偏與公主長得像,于是被迫代替她去往敵國和親列荔。 傳聞我的和親對象是個(gè)殘疾皇子敬尺,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 42,834評論 2 345

推薦閱讀更多精彩內(nèi)容