MySQL MGR搭建過(guò)程中常遇見(jiàn)的問(wèn)題及解決辦法
MGR搭建過(guò)程中遇到的一些故障
實(shí)際中我一共部署了三套MGR環(huán)境,分別是單機(jī)多實(shí)例的MGR環(huán)境,多機(jī)同網(wǎng)段的MGR環(huán)境,多機(jī)不同網(wǎng)段的MGR環(huán)境,部署的過(guò)程大同小異,但是還是有一些有出入的地方,這里把部署過(guò)程遇到的故障列舉出來(lái),供大家參考,如果能有幸解決您在部署時(shí)候的問(wèn)題,那是極好的。
01 常見(jiàn)故障1
[ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: bb874065-c485-11e8-8b52-000c2934472e:1 > Group transactions: 3db33b36-0e51-409f-a61d-c99756e90155:1-11' [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.' [Note] Plugin group_replication reported: ‘To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'
解決方案:
根據(jù)提示打開(kāi)set global group_replication_allow_local_disjoint_gtids_join=ON;
02 常見(jiàn)故障2
[ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: bb874065-c485-11e8-8b52-000c2934472e:1 > Group transactions: 3db33b36-0e51-409f-a61d-c99756e90155:1-15' [Warning] Plugin group_replication reported: 'The member contains transactions not present in the group. It is only allowed to join due to group_replication_allow_local_disjoint_gtids_join option' [Note] Plugin group_replication reported: 'This server is working as secondary member with primary member address localhost.localdomaion:3306.'
解決方案:
該故障和故障1的不同之處在于該問(wèn)題出現(xiàn)時(shí),參數(shù)group_replication_allow_local_disjoint_gtids_join已經(jīng)設(shè)置成為on了。解決該問(wèn)題的方法是執(zhí)行reset master就行,然后重新在主節(jié)點(diǎn)和從節(jié)點(diǎn)開(kāi)啟通道,即
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
03 常見(jiàn)故障3
本機(jī)測(cè)試時(shí),遇到下面的問(wèn)題
[Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. [ERROR] Slave I/O for channel 'group_replication_recovery': error connecting to master 'rpl_user@localhost.localdomaion:' - retry-time: 60 retries: 1, Error_code: 2005 [ERROR] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.' [ERROR] Plugin group_replication reported: 'For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.' [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt /'
解決方案:
這個(gè)問(wèn)題是由于測(cè)試環(huán)境上三臺(tái)主機(jī)的hostname設(shè)置成為了同一個(gè)名稱(chēng),改了hostname之后,這個(gè)問(wèn)題就解決了。
04 常見(jiàn)故障4
#在線上正式環(huán)境操作時(shí),出現(xiàn)下面的錯(cuò)誤, mysql--root@localhost:(none) ::>>START GROUP_REPLICATION; ERROR (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log. #查看log文件,發(fā)現(xiàn)只有一個(gè)warning: 2019-02-20T07::30.233937Z [Warning] Plugin group_replication reported: 'Group Replication requires slave-preserve-commit-order to be set to ON when using more than 1 applier threads.
解決方案:
mysql--root@localhost:(none) ::>>show variables like "%preserve%"; +--------------------------------+---------+ | Variable_name | Value | +--------------------------------+---------+ | slave_preserve_commit_order | OFF | +--------------------------------+---------+ row in set (0.01 sec) mysql--root@localhost:(none) ::>>set global slave_preserve_commit_order=; Query OK, rows affected (0.00 sec)
05 常見(jiàn)問(wèn)題5
2019-02-20T08::31.088437Z [Warning] Plugin group_replication reported: '[GCS] Connection attempt from IP address 192.168.9.208 refused. Address is not in the IP whitelist.' 2019-02-20T08::32.088676Z [Warning] Plugin group_replication reported: '[GCS] Connection attempt from IP address 192.168.9.208 refused. Address is not in the IP whitelist.'
解決方法:
在my.cnf中配置group_replication_ip_whitelist參數(shù)即可解決
06 常見(jiàn)問(wèn)題6
2019-02-20T08::44.087492Z [Warning] Plugin group_replication reported: 'read failed' 2019-02-20T08::44.096171Z [ERROR] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 24801' 2019-02-20T08::14.065775Z [ERROR] Plugin group_replication reported: 'Timeout on wait for view after joining group
解決方案:
將my.cnf中的參數(shù)group_replication_group_seeds設(shè)置為只包含除自身外其他group成員的ip地址以及內(nèi)部通信端口,如果寫(xiě)成group所有成員的IP地址,則會(huì)出現(xiàn)這個(gè)錯(cuò)誤,這和相同網(wǎng)段的MGR部署方式有些差異。
07 常見(jiàn)問(wèn)題7
[ERROR] Plugin group_replication reported: ‘[GCS] Error on opening a connection to oceanbase07: on local port: '.' [ERROR] Plugin group_replication reported: ‘[GCS] Error on opening a connection to oceanbase08: on local port: '.' [ERROR] Plugin group_replication reported: ‘[GCS] Error on opening a connection to oceanbase07: on local port: '.'
解決方案:
未開(kāi)通防火墻上的固定端口,開(kāi)通防火墻之后即可解決
08 常見(jiàn)問(wèn)題8
[Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. [ERROR] Slave I/O for channel 'group_replication_recovery': Master command COM_REGISTER_SLAVE failed: Access denied for user 'rpl_user'@'%' (using password: YES) (Errno: 1045), Error_code: 1597 [ERROR] Slave I/O thread couldn't register on master [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'FIRST', position
解決方案:
漏掉了某個(gè)節(jié)點(diǎn)的用戶,為了保險(xiǎn)起見(jiàn),在group節(jié)點(diǎn)上執(zhí)行
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
09 常見(jiàn)問(wèn)題9
[ERROR] Failed to open the relay log './localhost-relay-bin.000011' (relay_log_pos ). [ERROR] Could not find target log file mentioned in relay log info in the index file './work_NAT_1-relay-bin. index' during relay log initialization. [ERROR] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it. [ERROR] Failed to open the relay log './localhost-relay-bin-group_replication_recovery.000001' (relay_log_pos ). [ERROR] Could not find target log file mentioned in relay log info in the index file './work_NAT_1-relay-bin-group_replication_recovery.index' during relay log initialization. [ERROR] Slave: Failed to initialize the master info structure for channel 'group_replication_recovery'; its record may still be present in 'mysql.slave_master_info' table, consider deleting it. [ERROR] Failed to create or recover replication info repositories. [ERROR] Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code: [ERROR] /usr/local/mysql/bin/mysqld: Slave failed to initialize relay log info structure from the repository [ERROR] Failed to start slave threads for channel ''
解決方案:
這個(gè)錯(cuò)誤是由于slave節(jié)點(diǎn)由于某種原因?qū)е抡也坏絩elay-log的位置了,需要重新reset slave
以上就是MySQL MGR搭建過(guò)程中常遇見(jiàn)的問(wèn)題及解決辦法的詳細(xì)內(nèi)容,更多關(guān)于MySQL MGR搭建的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
MySQL5.6 Replication主從復(fù)制(讀寫(xiě)分離) 配置完整版
這篇文章主要介紹了MySQL5.6 Replication主從復(fù)制(讀寫(xiě)分離) 配置完整版,需要的朋友可以參考下2016-04-04
登錄MySQL時(shí)出現(xiàn)SSL connection error: unknown
這篇文章主要介紹了登錄MySQL時(shí)出現(xiàn)SSL connection error: unknown error number錯(cuò)誤的解決方法,文中通過(guò)圖文結(jié)合的形式講解的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2024-12-12
快速修復(fù)損壞的MySQL數(shù)據(jù)庫(kù)
在長(zhǎng)時(shí)間使用 MySQL 后,數(shù)據(jù)庫(kù)可能會(huì)出現(xiàn)一些問(wèn)題。大多數(shù)問(wèn)題可以通過(guò)簡(jiǎn)單的操作進(jìn)行快速修復(fù)。下面介紹兩種快速檢修 MySQL 數(shù)據(jù)庫(kù)的方法。2011-06-06
MySQL單表多關(guān)鍵字模糊查詢(xún)的實(shí)現(xiàn)方法
MySQL單表多關(guān)鍵字模糊查詢(xún)實(shí)現(xiàn)方法是很多人都非常想知道的一個(gè)問(wèn)題,那么,究竟采用什么方法,才能實(shí)現(xiàn)MySQL單表多關(guān)鍵字模糊查詢(xún)呢?2011-03-03
MySQL8.0?Command?Line?Client輸入密碼后出現(xiàn)閃退現(xiàn)象的原因以及解決方法總結(jié)
我們?cè)诎惭bMYSQL數(shù)據(jù)庫(kù)時(shí),經(jīng)常會(huì)出現(xiàn)一些問(wèn)題,下面這篇文章主要給大家介紹了關(guān)于MySQL8.0?Command?Line?Client輸入密碼后出現(xiàn)閃退現(xiàn)象的原因以及解決方法的相關(guān)資料,需要的朋友可以參考下2023-03-03
MySQL 5.6 解壓縮版安裝配置方法圖文教程(win10)
這篇文章主要為大家詳細(xì)介紹了MySQL for Windows 解壓縮版安裝配置的相關(guān)資料,需要的朋友可以參考下2016-07-07
MySQL正則表達(dá)式regexp_replace函數(shù)的用法實(shí)例
regexp_replace的使用非常靈活,且容易忘記,故做此筆記,下面這篇文章主要給大家介紹了關(guān)于MySQL正則表達(dá)式regexp_replace函數(shù)的用法實(shí)例,需要的朋友可以參考下2022-09-09

