99热在线精品一区二区三区_国产伦精品一区二区三区女破破_亚洲一区二区三区无码_精品国产欧美日韩另类一区

這篇文章主要介紹“怎么部署MySQL Group Replication”，在日常操作中，相信很多人在怎么部署MySQL Group Replication問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”怎么部署MySQL Group Replication”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

在無(wú)極等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作 網(wǎng)站設(shè)計(jì)制作按需求定制網(wǎng)站,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站設(shè)計(jì),網(wǎng)絡(luò)營(yíng)銷推廣,外貿(mào)網(wǎng)站建設(shè),無(wú)極網(wǎng)站建設(shè)費(fèi)用合理。

一、環(huán)境準(zhǔn)備

名稱	版本	備注
操作系統(tǒng)	RHEL6.5_X86_64
數(shù)據(jù)庫(kù)	5.7.18-15	Percona二進(jìn)制版本
復(fù)制節(jié)點(diǎn)	10.26.7.129	node1
	10.26.7.142	node2
	10.26.7.166	node3

二、MGR詳細(xì)部署步驟
1、MYSQL5.7安裝
本次學(xué)習(xí)實(shí)驗(yàn)采用的是Percona-Server-5.7.18-15-Linux.x86_64.ssl101二進(jìn)制版本，具體安裝過(guò)程略
2、節(jié)點(diǎn)1my.cnf參數(shù)配置（主寫節(jié)點(diǎn)）
#replicate
server-id=1001
skip-slave-start = false
read-only = false
expire_logs_days = 2
max_binlog_size = 1G
max_binlog_cache_size = 2G
log-bin = /home/mysql/mysql-bin
log-bin-index = /home/mysql/bin-index
binlog_format = row
log-slave-updates = 1
sync_binlog = 1
log-slow-slave-statements = 1
max-relay-log-size = 1G
relay-log = /home/mysql/mysql-relay
relay-log-index = /home/mysql/relay-index
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
#group replication
transaction_write_set_extraction  =XXHASH64       
loose-group_replication_group_name  ="2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec"  #務(wù)必以u(píng)uid形式配置
loose-group_replication_start_on_boot  =off
loose-group_replication_local_address  ="10.26.7.129:24001"                                    #不同節(jié)點(diǎn)配置不同節(jié)點(diǎn)本身的IP地址和端口，區(qū)分MYSQL自身的3306端口
loose-group_replication_group_seeds  ="10.26.7.129:24001,10.26.7.142:24001,10.26.7.166:24001"
loose-group_replication_bootstrap_group  =off
3、創(chuàng)建復(fù)制賬戶（主寫節(jié)點(diǎn)）
set sql_log_bin=0;
create user rpl_user@'%';
grant replication slave on *.* to rpl_user@'%'  identified by 'rpl_pass';
flush privileges;
set sql_log_bin=1;
change master to master_user='rpl_user',master_password='rpl_pass' for channel 'group_replication_recovery';
4、安裝組復(fù)制插件并啟動(dòng)組復(fù)制（主寫節(jié)點(diǎn)）
安裝插件
install plugin group_replication soname 'group_replication.so';
檢查插件是否正確安裝
show plugins
+-----------------------------+----------+--------------------+----------------------+---------+
| Name                        | Status   | Type               | Library              | License |
+-----------------------------+----------+--------------------+----------------------+---------+
| group_replication           | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+-----------------------------+----------+--------------------+----------------------+---------+
啟動(dòng)組復(fù)制
set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;
檢查組復(fù)制成員及狀態(tài)
select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec | node1       |        3306 | ONLINE       |
5、添加組復(fù)制成員實(shí)例node2 和node3
***添加節(jié)點(diǎn)前，務(wù)必做DNS解析，如果沒(méi)有配置DNS解析服務(wù)器，需要在每個(gè)節(jié)點(diǎn)配置hosts解析  /etc/hosts***
10.26.7.166 node3
10.26.7.142 node2
10.26.7.129 node1

***節(jié)點(diǎn)my.cnf參數(shù)文件server-id和loose-group_replication_local_address ="node2:24001需要分別更改"***

node2
set sql_log_bin=0;
create user rpl_user@'%';
grant replication slave on *.* to rpl_user@'%'  identified by 'rpl_pass';
flush privileges;

set sql_log_bin=1;
change master to master_user='rpl_user',master_password='rpl_pass' for channel 'group_replication_recovery';

install plugin group_replication soname 'group_replication.so';
show plugins

set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;

node3同樣執(zhí)行上述命令

然后檢查組復(fù)制信息
select * from performance_schema.replication_group_members;     
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec | node1       |        3306 | ONLINE       |
| group_replication_applier | 35e38786-66bb-11e7-bcc3-b8ca3a6a61a4 | node2       |        3306 | ONLINE       |
| group_replication_applier | 3bbedb1e-66bb-11e7-8fc0-b8ca3a6a7c48 | node3       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

測(cè)試組復(fù)制是否正常：
(root:localhost:Sat Jul 15 13:26:33 2017)[(none)]>create database dbtest;
Query OK, 1 row affected (0.01 sec)
(root:localhost:Sat Jul 15 13:26:40 2017)[(none)]>use dbtest;
Database changed
(root:localhost:Sat Jul 15 13:26:45 2017)[dbtest]>create table t1(id int primary key);
Query OK, 0 rows affected (0.01 sec)
(root:localhost:Sat Jul 15 13:26:54 2017)[dbtest]>insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
node2和node3執(zhí)行查詢
(root:localhost:Sat Jul 15 12:57:32 2017)[db01]>use dbtest;
Database changed
(root:localhost:Sat Jul 15 13:27:26 2017)[dbtest]>select * from t1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
三、錯(cuò)誤問(wèn)題以及匯總：
1、錯(cuò)誤案例01
錯(cuò)誤信息：2017-07-15T01:36:06.929941Z 4 [ERROR] Plugin group_replication reported: 'The group name 'group-replication-test' is not a valid UUID'
錯(cuò)誤原因：loose-group_replication_group_name參數(shù)沒(méi)有按照UUID格式指定，被認(rèn)為設(shè)置該參數(shù)無(wú)效
解決方案：更改loose-group_replication_group_name參數(shù)值為，loose-group_replication_group_name  ="2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec"
2、錯(cuò)誤案例02
錯(cuò)誤信息：
2017-07-15T01:29:27.271909Z 0 [Warning] unknown variable 'loose-group_replication_group_name=group-replication-test'
2017-07-15T01:29:27.271926Z 0 [Warning] unknown variable 'loose-group_replication_start_on_boot=off'
2017-07-15T01:29:27.271930Z 0 [Warning] unknown variable 'loose-group_replication_local_address=10.26.7.129:3306'
2017-07-15T01:29:27.271935Z 0 [Warning] unknown variable 'loose-group_replication_group_seeds=10.26.7.129:3306,10.26.7.142:3306,10.26.7.166:3306'
2017-07-15T01:29:27.271939Z 0 [Warning] unknown variable 'loose-group_replication_bootstrap_group=off'
錯(cuò)誤原因：因?yàn)橄仍O(shè)置了這些參數(shù)，而沒(méi)有裝group_replication插件，導(dǎo)致數(shù)據(jù)庫(kù)實(shí)例無(wú)法識(shí)別這些參數(shù)
解決方案：安裝group replication插件，install plugin group_replication soname 'group_replication.so'; （uninstall plugin group_replication 卸載，show plugins查看）
3、錯(cuò)誤案例03
錯(cuò)誤信息：
2017-07-15T01:54:54.447829Z 0 [Note] Plugin group_replication reported: 'Unable to bind to 0.0.0.0:3306 (socket=60, errno=98)!'
2017-07-15T01:54:54.447948Z 0 [ERROR] Plugin group_replication reported: 'Unable to announce tcp port 3306. Port already in use?'
2017-07-15T01:54:54.448101Z 0 [ERROR] Plugin group_replication reported: '[GCS] Error joining the group while waiting for the network layer to become ready.'
錯(cuò)誤原因：配置的組復(fù)制監(jiān)聽(tīng)端口和MYSQL實(shí)例端口沖突
解決方案：調(diào)整下面參數(shù)
loose-group_replication_local_address  ="10.26.7.129:24001"                                    #不同節(jié)點(diǎn)配置不同節(jié)點(diǎn)本身的IP地址和端口，區(qū)分MYSQL自身的3306端口
loose-group_replication_group_seeds  ="10.26.7.129:24001,10.26.7.142:24001,10.26.7.166:24001"
4、錯(cuò)誤案例04
錯(cuò)誤信息：
2017-07-15T04:20:01.249529Z 21 [ERROR] Slave I/O for channel 'group_replication_recovery': error connecting to master 'rpl_user@node2:3306' - retry-time: 60  retries: 1, Error_code: 2005
錯(cuò)誤原因：沒(méi)有配置DNS解析或者h(yuǎn)osts解析，節(jié)點(diǎn)無(wú)法連接其他數(shù)據(jù)庫(kù)
解決方案：配置hosts解析，每個(gè)節(jié)點(diǎn)/etc/hosts添加如下內(nèi)容
10.26.7.166 node3
10.26.7.142 node2
10.26.7.129 node1
5、錯(cuò)誤案例05
錯(cuò)誤信息
2017-07-15T03:42:45.395407Z 288 [ERROR] Slave SQL for channel 'group_replication_recovery': Error 'Can't create database 'db01'; database exists' on query. Default database: 'db01'. Query: 'create database db01', Error_code: 1007
2017-07-15T03:42:45.395472Z 288 [Warning] Slave: Can't create database 'db01'; database exists Error_code: 1007
2017-07-15T03:42:45.395503Z 288 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000005' position 434
錯(cuò)誤原因：這個(gè)錯(cuò)誤是由于節(jié)點(diǎn)由于各種原因退出mgr組，后面又加入了mgr組，但之前存在的數(shù)據(jù)依舊存在
解決方案：刪除要加入組復(fù)制節(jié)點(diǎn)存在的數(shù)據(jù)庫(kù)即可，但其他節(jié)點(diǎn)不是主寫節(jié)點(diǎn)，需要先調(diào)整參數(shù)set global super_read_only=0;然后執(zhí)行drop database db01;再重新加入組
set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;
6、錯(cuò)誤案例06
錯(cuò)誤信息：
2017-07-15T03:44:09.982428Z 18 [ERROR] Slave SQL for channel 'group_replication_recovery': Error 'Can't create database 'db01'; database exists' on query. Default database: 'db01'. Query: 'create database db01', Error_code: 1007
2017-07-15T03:44:09.982493Z 18 [Warning] Slave: Can't create database 'db01'; database exists Error_code: 1007
2017-07-15T03:44:09.982522Z 18 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000005' position 434
錯(cuò)誤原因：同上錯(cuò)誤案例05
解決方案：同上錯(cuò)誤案例05
7、錯(cuò)誤案例07
錯(cuò)誤信息：
2017-07-15T03:49:10.370846Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec:1-4,35e38786-66bb-11e7-bcc3-b8ca3a6a61a4:1 > Group transactions: 2faa74b8-66bb-11e7-ab7e-b8ca3a6a61ec:1-16'
錯(cuò)誤原因：同上錯(cuò)誤案例05，在從庫(kù)執(zhí)行了多余的事務(wù)
解決方案：同上錯(cuò)誤案例05，直接重新加入節(jié)點(diǎn)即可
set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;
8、錯(cuò)誤案例08
錯(cuò)誤信息
ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.
錯(cuò)誤原因：由于主節(jié)點(diǎn)創(chuàng)建了表t1，但沒(méi)有指定主鍵（此時(shí)表結(jié)構(gòu)可以復(fù)制到各節(jié)點(diǎn)，一旦插入數(shù)據(jù)DML操作即會(huì)報(bào)錯(cuò)）
解決方案：為表增加主鍵，然后做DML操作（MGR需要各表都有主鍵）
alter table t1 add primary key(id);
insert into t1 values(1),(2);    
9、錯(cuò)誤案例09
錯(cuò)誤信息:
mysqldump -R -E --triggers --single-transaction  --master-data=2 -B db01 >db01.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
mysqldump: Couldn't execute 'SAVEPOINT sp': The MySQL server is running with the --transaction-write-set-extraction!=OFF option so it cannot execute this statement (1290)
錯(cuò)誤原因：mgr不支持mysqldump的事務(wù)一致性備份，因?yàn)槠洳恢С謘avepoint
解決方案：通過(guò)xtrabackup或者不加--single-transaction備份
10、錯(cuò)誤案例10
錯(cuò)誤信息：
create table t2 as select * from t1;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
錯(cuò)誤原因：配置MGR，開(kāi)啟了GTID，所有GTID不支持的操作，MGR也不支持
解決方案：使用create table t2 like t1; insert into t2 select * from t;分開(kāi)兩個(gè)事務(wù)執(zhí)行

到此，關(guān)于“怎么部署MySQL Group Replication”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！

當(dāng)前標(biāo)題：怎么部署MySQLGroupReplication
標(biāo)題鏈接：http://www.ef60e0e.cn/article/gpoegg.html