基于組復(fù)制的強(qiáng)大功能在MySQL 5.7.17之后以插件的形式實(shí)現(xiàn)，本文講述在單機(jī)多實(shí)例基礎(chǔ)上搭建組復(fù)制測(cè)試環(huán)境
創(chuàng)新互聯(lián)長(zhǎng)期為上千多家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為曲周企業(yè)提供專業(yè)的做網(wǎng)站、網(wǎng)站建設(shè)，曲周網(wǎng)站改版等技術(shù)服務(wù)。擁有10多年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。
環(huán)境說(shuō)明：
操作系統(tǒng):
CentOS Linux release 7.3.1611 (Core)
內(nèi)核版本:
Linux version 3.10.0-514.6.2.el7.x86_64
MySQL版本:
mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
依賴包安裝:
yum -y install gcc gcc-c++ libaio-devel boost-devel autoconf automake zlib-devel libxml2-devel ncurses-devel libgcrypt-devel libtool-devel openssl-devel bison-devel unzip numactl-devel
同時(shí)需要關(guān)閉selinux及防火墻
setenforce 0 systemctl stop firewalld systemctl disabled firewalld
MySQL5.7.17安裝及配置
下載解壓至安裝目錄
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar tar xf mysql-5.7.17-linux-glibc2.5-x86_64.tar mv mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql5.7.17
添加環(huán)境變量
echo 'PATH=/usr/local/mysql5.7.17/bin:$PATH'>>/etc/profile source /etc/profile
手動(dòng)創(chuàng)建mysql用戶
useradd -s /sbin/nologin -d /dev/null mysql
添加本機(jī)主機(jī)名解析
echo "127.0.0.1 $HOSTNAME">>/etc/host
MySQL多實(shí)例:(三個(gè)實(shí)例)
我們將實(shí)例建3306、3307、3308三個(gè)實(shí)例
創(chuàng)建數(shù)據(jù)文件目錄
重慶分公司
重慶分公司
mkdir -p /usr/local/mysql5.7.17/data/330{6,7,8}創(chuàng)建配置文件目錄
mkdir -p /usr/local/mysql5.7.17/conf
創(chuàng)建配置文件
3306實(shí)例的配置文件
cat /usr/local/mysql5.7.17/conf/3306.cnf
[mysqld] basedir=/usr/local/mysql5.7.17 datadir=/usr/local/mysql5.7.17/data/3306 port = 3306 socket =/usr/local/mysql5.7.17/data/3306/mysqld.sock log-error=/usr/local/mysql5.7.17/data/3306/mysqld.log user = mysql server_id = 3306 gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay_log_info_repository = TABLE binlog_checksum = NONE log_slave_updates = ON log_bin = binlog binlog_format = ROW #組復(fù)制基本配置 transaction_write_set_extraction = XXHASH64 loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555" loose-group_replication_start_on_boot=off loose-group_replication_local_address="127.0.0.1:13306" loose-group_replication_group_seeds="127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308" loose-group_replication_bootstrap_group = off #loose-group_replication_ip_whitelist=”10.30.0.0/16,10.31.0..0/16,10.27.0.0/16″
說(shuō)明:
group_replication變量使用的loose-前綴是指示Server啟用時(shí)尚未加載復(fù)制插件也將繼續(xù)啟動(dòng)
transaction_write_set_extraction = XXHASH64指示Server必須為每個(gè)事務(wù)收集寫集合，并使用XXHASH64哈希算法將其編碼為散列
loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555"表示將加入或者創(chuàng)建的復(fù)制組命名為01e5fb97-be64-41f7-bafd-3afc7a6ab555,可自定義(通過(guò)cat /proc/sys/kernel/random/uuid)
loose-group_replication_start_on_boot=off 設(shè)置為Server啟動(dòng)時(shí)不自動(dòng)啟動(dòng)組復(fù)制
loose-group_replication_local_address="127.0.0.1:13306" 綁定本地的127.0.0.1及13306端口接受其他組成員的連接，IP地址必須為其他組成員可正常訪問(wèn)
loose-group_replication_group_seeds="127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308"本行為告訴服務(wù)器當(dāng)服務(wù)器加入組時(shí)，應(yīng)當(dāng)連接到127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308這些種子服務(wù)器進(jìn)行配置。本設(shè)置可以不是全部的組成員服務(wù)地址
loose-group_replication_bootstrap_group = off 配置是否自動(dòng)引導(dǎo)組
loose-group_replication_ip_whitelist=”10.30.0.0/16,10.31.0..0/16,10.27.0.0/16″默認(rèn)情況下只允許127.0.0.1連接到復(fù)制組，如果是其他IP則需要配置。
3307實(shí)例的配置文件
cat /usr/local/mysql5.7.17/conf/3307.cnf
[mysqld] basedir=/usr/local/mysql5.7.17 datadir=/usr/local/mysql5.7.17/data/3307 port = 3307 socket =/usr/local/mysql5.7.17/data/3307/mysqld.sock log-error=/usr/local/mysql5.7.17/data/3307/mysqld.log user = mysql server_id = 3307 gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay_log_info_repository = TABLE binlog_checksum = NONE log_slave_updates = ON log_bin = binlog binlog_format = ROW transaction_write_set_extraction = XXHASH64 loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555" loose-group_replication_start_on_boot=off loose-group_replication_local_address="127.0.0.1:13307" loose-group_replication_group_seeds="127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308" loose-group_replication_bootstrap_group = off loose-group_replication_ip_whitelist=”10.30.0.0/16,10.31.0..0/16,10.27.0.0/16″
3308實(shí)例配置文件
cat /usr/local/mysql5.7.17/conf/3308.cnf
[mysqld] basedir=/usr/local/mysql5.7.17 datadir=/usr/local/mysql5.7.17/data/3308 port = 3308 socket =/usr/local/mysql5.7.17/data/3308/mysqld.sock log-error=/usr/local/mysql5.7.17/data/3308/mysqld.log user = mysql server_id = 3308 gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay_log_info_repository = TABLE binlog_checksum = NONE log_slave_updates = ON log_bin = binlog binlog_format = ROW transaction_write_set_extraction = XXHASH64 loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555" loose-group_replication_start_on_boot=off loose-group_replication_local_address="127.0.0.1:13308" loose-group_replication_group_seeds="127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308" loose-group_replication_bootstrap_group = off loose-group_replication_ip_whitelist=”10.30.0.0/16,10.31.0..0/16,10.27.0.0/16″
初始化數(shù)據(jù)庫(kù)：
/usr/local/mysql5.7.17/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql5.7.17 --datadir=/usr/local/mysql5.7.17/data/3306 /usr/local/mysql5.7.17/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql5.7.17 --datadir=/usr/local/mysql5.7.17/data/3307 /usr/local/mysql5.7.17/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql5.7.17 --datadir=/usr/local/mysql5.7.17/data/3308 chown -R mysql.mysql /usr/local/mysql5.7.17/data/*
啟動(dòng)數(shù)據(jù)庫(kù):
/usr/local/mysql5.7.17/bin/mysqld --defaults-file=/usr/local/mysql5.7.17/conf/3306.cnf & /usr/local/mysql5.7.17/bin/mysqld --defaults-file=/usr/local/mysql5.7.17/conf/3307.cnf & /usr/local/mysql5.7.17/bin/mysqld --defaults-file=/usr/local/mysql5.7.17/conf/3308.cnf &
3306實(shí)例:
mysql -S /usr/local/mysql5.7.17/data/3306/mysqld.sock #創(chuàng)建復(fù)制用戶及密碼 set sql_log_bin=0; grant replication slave,replication client on *.* to 'repluser'@'%' identified by 'replpass'; flush privileges; set sql_log_bin=1; #設(shè)置復(fù)制用戶名及密碼 change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery'; #安裝組復(fù)制插件 install plugin group_replication soname 'group_replication.so'; #檢查插件是否安裝成功 show plugins; #啟動(dòng)組復(fù)制 set global group_replication_bootstrap_group=ON; START group_replication; set global group_replication_bootstrap_group=OFF; exit;
3307實(shí)例：
mysql -S /usr/local/mysql5.7.17/data/3307/mysqld.sock set sql_log_bin=0; grant replication slave,replication client on *.* to 'repluser'@'%' identified by 'replpass'; flush privileges; set sql_log_bin=1; change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery'; install plugin group_replication soname 'group_replication.so'; show plugins; #啟動(dòng)組復(fù)制 START group_replication; exit;
3308實(shí)例:
mysql -S /usr/local/mysql5.7.17/data/3308/mysqld.sock set sql_log_bin=0; grant replication slave,replication client on *.* to 'repluser'@'%' identified by 'replpass'; flush privileges; set sql_log_bin=1; change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery'; install plugin group_replication soname 'group_replication.so'; show plugins; #啟動(dòng)組復(fù)制 START group_replication; exit
注意:3306、3307、3308啟動(dòng)組復(fù)制的不同
| 實(shí)例名 | 啟動(dòng)組復(fù)制命令 |
| 3306 | set global group_replication_bootstrap_group=ON; START group_replication; set global group_replication_bootstrap_group=OFF; |
| 3307 | START group_replication; |
| 3308 | START group_replication; |
查看組復(fù)制結(jié)果:
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 51f188a1-5626-11e7-a2f3-000c29335f88 | haproxy206 | 3306 | ONLINE | | group_replication_applier | 5b45cd3f-5626-11e7-a435-000c29335f88 | haproxy206 | 3307 | ONLINE | | group_replication_applier | 6374c473-5626-11e7-a5aa-000c29335f88 | haproxy206 | 3308 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+
可以看到MEMBER_STATE都是ONLINE，組復(fù)制配置成功
組復(fù)制測(cè)試：
mysql -S /usr/local/mysql5.7.17/data/3306/mysqld.sock mysql > create database test; mysql > create table tb1 (id int not null); mysql > insert into tb1 values(1); The table does not comply with the requirements by an external plugin. #報(bào)錯(cuò)，那是因?yàn)間roup_replication中的表必須有主鍵保證唯一性，否則失敗。 mysql > drop table tb1; mysql > create table tb1 (id int not null primary key auto_increment,username varchar(300)); mysql > insert into tb1 values(1,'guest');
在其他實(shí)例測(cè)試:
mysql -S /usr/local/mysql5.7.17/data/3307/mysqld.sock mysql> select * from test.tb1; +----+----------+ | id | username | +----+----------+ | 1 | guest | +----+----------+ mysql -S /usr/local/mysql5.7.17/data/3308/mysqld.sock mysql> select * from test.tb1; +----+----------+ | id | username | +----+----------+ | 1 | guest | +----+----------+ mysql> insert into test.tb1 values(2,'admin'); The MySQL server is running with the --super-read-only option so it cannot execute this statement
在單主模式下，只有主機(jī)才允許寫入，其他都為只讀模式；
在單主模式下尋找主實(shí)例
#查找主實(shí)例 mysql> select variable_value from performance_schema.global_status where variable_name ='group_replication_primary_member'; +--------------------------------------+ | variable_value | +--------------------------------------+ | 51f188a1-5626-11e7-a2f3-000c29335f88 | +--------------------------------------+ #獲取實(shí)例信息 mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 51f188a1-5626-11e7-a2f3-000c29335f88 | haproxy206 | 3306 | ONLINE | | group_replication_applier | 5b45cd3f-5626-11e7-a435-000c29335f88 | haproxy206 | 3307 | ONLINE | | group_replication_applier | 6374c473-5626-11e7-a5aa-000c29335f88 | haproxy206 | 3308 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+
則可以知道haproxy206的3306端口為主實(shí)例
mysql> show variables like '%group_replication%'; +----------------------------------------------------+-------------------------------------------------+ | Variable_name | Value | +----------------------------------------------------+-------------------------------------------------+ | group_replication_allow_local_disjoint_gtids_join | OFF | | group_replication_allow_local_lower_version_join | OFF | | group_replication_auto_increment_increment | 7 | | group_replication_bootstrap_group | OFF | | group_replication_components_stop_timeout | 31536000 | | group_replication_compression_threshold | 1000000 | | group_replication_enforce_update_everywhere_checks | OFF | | group_replication_flow_control_applier_threshold | 25000 | | group_replication_flow_control_certifier_threshold | 25000 | | group_replication_flow_control_mode | QUOTA | | group_replication_force_members | | | group_replication_group_name | 01e5fb97-be64-41f7-bafd-3afc7a6ab555 | | group_replication_group_seeds | 127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308 | | group_replication_gtid_assignment_block_size | 1000000 | | group_replication_ip_whitelist | AUTOMATIC | | group_replication_local_address | 127.0.0.1:13306 | | group_replication_poll_spin_loops | 0 | | group_replication_recovery_complete_at | TRANSACTIONS_APPLIED | | group_replication_recovery_reconnect_interval | 60 | | group_replication_recovery_retry_count | 10 | | group_replication_recovery_ssl_ca | | | group_replication_recovery_ssl_capath | | | group_replication_recovery_ssl_cert | | | group_replication_recovery_ssl_cipher | | | group_replication_recovery_ssl_crl | | | group_replication_recovery_ssl_crlpath | | | group_replication_recovery_ssl_key | | | group_replication_recovery_ssl_verify_server_cert | OFF | | group_replication_recovery_use_ssl | OFF | | group_replication_single_primary_mode | ON | | group_replication_ssl_mode | DISABLED | | group_replication_start_on_boot | OFF | +----------------------------------------------------+-------------------------------------------------+
注意上面的
group_replication_enforce_update_everywhere_checks | OFF group_replication_single_primary_mode | ON
單主模式變更為多主模式
3306實(shí)例： mysql -S /usr/local/mysql5.7.17/data/3306/mysqld.sock STOP group_replication; set global group_replication_single_primary_mode=OFF; set global group_replication_enforce_update_everywhere_checks= ON; set global group_replication_bootstrap_group=ON; START group_replication; set global group_replication_bootstrap_group=OFF; 3307實(shí)例： mysql -S /usr/local/mysql5.7.17/data/3307/mysqld.sock STOP group_replication; set global group_replication_single_primary_mode=OFF; set global group_replication_enforce_update_everywhere_checks= ON; START group_replication; 3308實(shí)例: mysql -S /usr/local/mysql5.7.17/data/3308/mysqld.sock STOP group_replication; set global group_replication_single_primary_mode=OFF; set global group_replication_enforce_update_everywhere_checks= ON; START group_replication;
經(jīng)過(guò)以上配置后你就可以在每個(gè)實(shí)例上都進(jìn)行增刪改
增加節(jié)點(diǎn)
我們已三個(gè)成員的組復(fù)制分別為3306、3307、3308現(xiàn)在我們?cè)黾?309實(shí)例
mkdir -p /usr/local/mysql5.7.17/data/3309
cat /usr/local/mysql5.7.17/conf/3309.cnf
[mysqld] basedir=/usr/local/mysql5.7.17 datadir=/usr/local/mysql5.7.17/data/3309 port = 3309 socket =/usr/local/mysql5.7.17/data/3309/mysqld.sock log-error=/usr/local/mysql5.7.17/data/3309/mysqld.log user = mysql server_id = 3309 gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay_log_info_repository = TABLE binlog_checksum = NONE log_slave_updates = ON log_bin = binlog binlog_format = ROW transaction_write_set_extraction = XXHASH64 loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555" loose-group_replication_start_on_boot=off loose-group_replication_local_address="127.0.0.1:13309" loose-group_replication_group_seeds="127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308" loose-group_replication_bootstrap_group = off #loose-group_replication_ip_whitelist=”10.30.0.0/16,10.31.0..0/16,10.27.0.0/16″
初始化數(shù)據(jù)庫(kù)并啟動(dòng)數(shù)據(jù)庫(kù)
/usr/local/mysql5.7.17/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql5.7.17 --datadir=/usr/local/mysql5.7.17/data/3309 chown -R mysql.mysql /usr/local/mysql5.7.17/data/3309 /usr/local/mysql5.7.17/bin/mysqld --defaults-file=/usr/local/mysql5.7.17/conf/3309.cnf &
配置
mysql -S /usr/local/mysql5.7.17/data/3309/mysqld.sock set sql_log_bin=0; grant replication slave,replication client on *.* to 'repluser'@'%' identified by 'replpass'; flush privileges; set sql_log_bin=1; change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery'; install plugin group_replication soname 'group_replication.so'; show plugins; START group_replication;
查看組成員
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 51f188a1-5626-11e7-a2f3-000c29335f88 | haproxy206 | 3306 | ONLINE | | group_replication_applier | 5b45cd3f-5626-11e7-a435-000c29335f88 | haproxy206 | 3307 | ONLINE | | group_replication_applier | 6374c473-5626-11e7-a5aa-000c29335f88 | haproxy206 | 3308 | ONLINE | | group_replication_applier | b19bea84-5629-11e7-8b05-000c29335f88 | haproxy206 | 3309 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+
如果數(shù)據(jù)量大的話，3309的狀態(tài)可能為recovering
問(wèn)題處理:
2017-06-21T15:06:01.854288+08:00 0 [Warning] Plugin group_replication reported: ‘[GCS] Connection attempt from IP address 192.168.0.2 refused. Address is not in the IP whitelist.’
原因：group_replication_ip_whitelist默認(rèn)值為127.0.0.1/8，設(shè)置為需要的配置
處理：set global group_replication_ip_whitelist='192.168.0.0/24' (多個(gè)網(wǎng)段使用逗號(hào)隔開)
Slave I/O for channel 'group_replication_recovery': Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated. Error_code: 1593
原因：沒(méi)有配置同步賬號(hào)跟密碼，使用的是空密碼進(jìn)行同步。 需要為復(fù)制通道group_replication_recovery 設(shè)置同步信息,一定要注意同步通道group_replication_recovery
處理：change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery';
