溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
下文我給大家簡單講講關于MySQL 5.7.17在單機多實例基礎上如何搭建組復制測試環境，大家之前了解過相關類似主題內容嗎？感興趣的話就一起來看看這篇文章吧，相信看完MySQL 5.7.17在單機多實例基礎上如何搭建組復制測試環境對大家多少有點幫助吧。
環境說明：
操作系統:
CentOS Linux release 7.3.1611 (Core)
內核版本:
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
同時需要關閉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
添加環境變量
echo 'PATH=/usr/local/mysql5.7.17/bin:$PATH'>>/etc/profile source /etc/profile
手動創建mysql用戶
useradd -s /sbin/nologin -d /dev/null mysql
添加本機主機名解析
echo "127.0.0.1 $HOSTNAME">>/etc/host
MySQL多實例:(三個實例)
我們將實例建3306、3307、3308三個實例
創建數據文件目錄
mkdir -p /usr/local/mysql5.7.17/data/330{6,7,8}創建配置文件目錄
mkdir -p /usr/local/mysql5.7.17/conf
創建配置文件
3306實例的配置文件
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 #組復制基本配置 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″
說明:
group_replication變量使用的loose-前綴是指示Server啟用時尚未加載復制插件也將繼續啟動
transaction_write_set_extraction = XXHASH64指示Server必須為每個事務收集寫集合，并使用XXHASH64哈希算法將其編碼為散列
loose-group_replication_group_name="01e5fb97-be64-41f7-bafd-3afc7a6ab555"表示將加入或者創建的復制組命名為01e5fb97-be64-41f7-bafd-3afc7a6ab555,可自定義(通過cat /proc/sys/kernel/random/uuid)
loose-group_replication_start_on_boot=off 設置為Server啟動時不自動啟動組復制
loose-group_replication_local_address="127.0.0.1:13306" 綁定本地的127.0.0.1及13306端口接受其他組成員的連接，IP地址必須為其他組成員可正常訪問
loose-group_replication_group_seeds="127.0.0.1:13306,127.0.0.1:13307,127.0.0.1:13308"本行為告訴云服務器當云服務器加入組時，應當連接到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″默認情況下只允許127.0.0.1連接到復制組，如果是其他IP則需要配置。
3307實例的配置文件
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實例配置文件
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″
初始化數據庫：
/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/*
啟動數據庫:
/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實例:
mysql -S /usr/local/mysql5.7.17/data/3306/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; #啟動組復制 set global group_replication_bootstrap_group=ON; START group_replication; set global group_replication_bootstrap_group=OFF; exit;
3307實例：
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; #啟動組復制 START group_replication; exit;
3308實例:
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; #啟動組復制 START group_replication; exit
注意:3306、3307、3308啟動組復制的不同
| 實例名 | 啟動組復制命令 |
| 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; |
查看組復制結果:
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，組復制配置成功
組復制測試：
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. #報錯，那是因為group_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');
在其他實例測試:
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
在單主模式下，只有主機才允許寫入，其他都為只讀模式；
在單主模式下尋找主實例
#查找主實例 mysql> select variable_value from performance_schema.global_status where variable_name ='group_replication_primary_member'; +--------------------------------------+ | variable_value | +--------------------------------------+ | 51f188a1-5626-11e7-a2f3-000c29335f88 | +--------------------------------------+ #獲取實例信息 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端口為主實例
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實例： 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實例： 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實例: 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;
經過以上配置后你就可以在每個實例上都進行增刪改
增加節點
我們已三個成員的組復制分別為3306、3307、3308現在我們增加3309實例
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″
初始化數據庫并啟動數據庫
/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 | +---------------------------+--------------------------------------+-------------+-------------+--------------+
如果數據量大的話，3309的狀態可能為recovering
問題處理:
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默認值為127.0.0.1/8，設置為需要的配置
處理：set global group_replication_ip_whitelist='192.168.0.0/24' (多個網段使用逗號隔開)
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
原因：沒有配置同步賬號跟密碼，使用的是空密碼進行同步。 需要為復制通道group_replication_recovery 設置同步信息,一定要注意同步通道group_replication_recovery
處理：change master to master_user='repluser',master_password='replpass' for channel 'group_replication_recovery';
大家覺得MySQL 5.7.17在單機多實例基礎上如何搭建組復制測試環境這篇文章怎么樣，是否有所收獲。如果想要了解更多相關，可以繼續關注我們的行業資訊板塊。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
