溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》
您好，登錄后才能下訂單哦！
MySQL5.7.24 Gtid+MGR
由于服務(wù)器的資源有限，本次測(cè)試采用的是1臺(tái)機(jī)器測(cè)試：
一臺(tái)硬件物理機(jī)器：16G內(nèi)存，一塊120G的ssd盤(pán) 系統(tǒng)是CentOS6.9 x86_64最小化安裝
系統(tǒng)內(nèi)核：
[root@localhost ~]# uname -a
Linux localhost.localdomain 2.6.32-696.el6.x86_64 #1 SMP Tue Mar 21 19:29:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux關(guān)閉selinux,關(guān)閉iptables
服務(wù)部署規(guī)劃說(shuō)明：
物理機(jī)器上以不同的mysql配置文件my.cnf來(lái)初始化mysql ，并且以三個(gè)不同的端口和添加MGR的配置參數(shù)來(lái)開(kāi)啟3個(gè)MySQL實(shí)例
mysql的版本為mysql5.7.24 二進(jìn)制安裝
第一，物理機(jī)器上開(kāi)啟三個(gè)mysql5.7.24 實(shí)例：
初始化mysql
下載軟件：
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
tar xf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz -C /usr/local
mv /usr/local/mysql-5.7.24-linux-glibc2.12 /usr/local/mysql
useradd mysql -s /sbin/nologin -M
chown -R mysql.mysql /usr/local/mysql
yum install numactl -y創(chuàng)建MySQL初始化數(shù)據(jù)存放路徑：
mkdir -p /home/mysql3306/{data,binlog,logs}
mkdir -p /home/mysql3307/{data,binlog,logs}
mkdir -p /home/mysql3308/{data,binlog,logs}
chown -R mysql.mysql /home/{mysql3306,mysql3307,mysql3308}準(zhǔn)備初始化mysql的my.cnf的配置文件：
[root@localhost ~]# ll /opt/my330*
-rw-r--r-- 1 root root 9227 12月 26 18:11 /opt/my3306.cnf
-rw-r--r-- 1 root root 9294 12月 27 18:09 /opt/my3307.cnf
-rw-r--r-- 1 root root 9294 12月 27 18:10 /opt/my3308.cnf
[root@localhost ~]# mysql5.7.17開(kāi)始引入mysql的MGR特性簡(jiǎn)單介紹MySQL5.7開(kāi)啟Gtid+MGR特性的參數(shù)說(shuō)明：
此次演示環(huán)境以mysql3306實(shí)例作為第一個(gè)MGR的啟動(dòng)實(shí)例，
MySQL5.7開(kāi)啟Gtid+MGR特性my3306cnf配置文件必須開(kāi)啟參數(shù)如下：
server_id =1
socket =/tmp/mysql3306.sock
##:for binlog
binlog_format =row
log_bin =/home/mysql3306/binlog/mysql-bin
binlog_checksum=NONE
log_slave_updates=ON
##:for gtid
#gtid_executed_compression_period =1000
gtid_mode =on
enforce_gtid_consistency =on
####for group_replication
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="3db33b36-0e51-409f-a61d-c99756e90155" ##格式要和server-uuid一致，但是不能和機(jī)器上mysql實(shí)例的uuid重復(fù)
loose-group_replication_start_on_boot=off ##禁止在重啟mysql服務(wù)時(shí)開(kāi)啟組復(fù)制
loose-group_replication_local_address= "192.168.1.233:33061" #####端口隨便指定一個(gè)，但是不要和機(jī)上其他服務(wù)的端口沖突就行，告訴插件本機(jī)使用網(wǎng)絡(luò)地址192.168.1.233和端口33061與組中的其他成員進(jìn)行內(nèi)部通信。
loose-group_replication_group_seeds= "192.168.1.233:33061,192.168.1.233:33071,192.168.1.233:33081"
loose-group_replication_ip_whitelist="192.168.1.233/24" ##此處必須設(shè)置ip白名單，否則在開(kāi)啟MGR時(shí)，會(huì)報(bào)錯(cuò)。
loose-group_replication_bootstrap_group= off ##
##loose-group_replication_single_primary_mode=off ##在配置MGR的mutl-master模式時(shí)開(kāi)啟
##loose-group_replication_enforce_update_everywhere_checks=on ##在配置MGR的mutl-master模式時(shí)開(kāi)啟
my3307.cnf配置文件和my3306.cnf中不同的是把loose-group_replication_local_address修改為 "192.168.1.233:33071"
my3308.cnf配置文件和my3306.cnf中不同的是把loose-group_replication_local_address修改為 "192.168.1.233:33081"啟動(dòng)mysql實(shí)例：
/usr/local/mysql/bin/mysqld --defaults-file=/opt/my3306.cnf --initialize
/usr/local/mysql/bin/mysqld --defaults-file=/opt/my3307.cnf --initialize
/usr/local/mysql/bin/mysqld --defaults-file=/opt/my3308.cnf --initialize為了在配置MGR的過(guò)程中防止出錯(cuò)，在啟動(dòng)mysql之前把a(bǔ)uto.cnf 修改為3個(gè)不相同的uuid
[root@localhost ~]# cat /home/mysql3306/data/auto.cnf
[auto]
server-uuid=1ec3ac79-08ed-11e9-8da8-bcaec502b368
[root@localhost ~]# cat /home/mysql3307/data/auto.cnf
[auto]
server-uuid=288e7bbe-08f3-11e9-a605-bcaec502b311
[root@localhost ~]# cat /home/mysql3308/data/auto.cnf
[auto]
server-uuid=883c9421-08f5-11e9-8d47-bcaec502b333啟動(dòng)mysql服務(wù)：
/usr/local/mysql/bin/mysqld --defaults-file=/opt/my3306.cnf &
/usr/local/mysql/bin/mysqld --defaults-file=/opt/my3307.cnf &
/usr/local/mysql/bin/mysqld --defaults-file=/opt/my3308.cnf &第二.開(kāi)啟MGR配置過(guò)程(默認(rèn)是single-master模式)：
mysql3306作為第一個(gè)節(jié)點(diǎn)開(kāi)啟MGR:
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'Zykjwujianwei';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Zykjwujianwei' FOR CHANNEL 'group_replication_recovery';
install PLUGIN group_replication SONAME 'group_replication.so';
set global group_replication_bootstrap_group=ON;
start group_replication;
select * from performance_schema.replication_group_members;注意：只有在第一個(gè)開(kāi)啟MGR的mysql3306節(jié)點(diǎn)上才執(zhí)行這個(gè)參數(shù)set global group_replication_bootstrap_group=ON;
root@localhost [(none)]>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 1ec3ac79-08ed-11e9-8da8-bcaec502b368 | localhost.localdomain | 3306 | ONLINE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
3 rows in set (0.00 sec)
root@localhost [(none)]>查看當(dāng)前的leader：
select *from performance_schema.replication_group_members where member_id =(select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member');
select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member'；節(jié)點(diǎn)mysql3307操作：
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'Zykjwujianwei';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Zykjwujianwei' FOR CHANNEL 'group_replication_recovery';
install PLUGIN group_replication SONAME 'group_replication.so';
start group_replication;
select * from performance_schema.replication_group_members;
root@localhost [(none)]>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 1ec3ac79-08ed-11e9-8da8-bcaec502b368 | localhost.localdomain | 3306 | ONLINE |
| group_replication_applier | 288e7bbe-08f3-11e9-a605-bcaec502b311 | localhost.localdomain | 3307 | ONLINE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+節(jié)點(diǎn)mysql3308操作：
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'Zykjwujianwei';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='Zykjwujianwei' FOR CHANNEL 'group_replication_recovery';
install PLUGIN group_replication SONAME 'group_replication.so';
start group_replication;
select * from performance_schema.replication_group_members;
root@localhost [(none)]>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 1ec3ac79-08ed-11e9-8da8-bcaec502b368 | localhost.localdomain | 3306 | ONLINE |
| group_replication_applier | 288e7bbe-08f3-11e9-a605-bcaec502b311 | localhost.localdomain | 3307 | ONLINE |
| group_replication_applier | 883c9421-08f5-11e9-8d47-bcaec502b333 | localhost.localdomain | 3308 | ONLINE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
3 rows in set (0.00 sec)第三.測(cè)試效果：
root@localhost [(none)]>select @@port;create database test01;show databases;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.00 sec)
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test01 |
+--------------------+
5 rows in set (0.00 sec)
root@localhost [(none)]>select @@port;create database test02;show databases;
+--------+
| @@port |
+--------+
| 3307 |
+--------+
1 row in set (0.00 sec)
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test01 |
+--------------------+
5 rows in set (0.00 sec)
root@localhost [(none)]>
root@localhost [(none)]>select @@port;create database test02;show databases;
+--------+
| @@port |
+--------+
| 3308 |
+--------+
1 row in set (0.00 sec)
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test01 |
+--------------------+
5 rows in set (0.00 sec)
root@localhost [(none)]>測(cè)試說(shuō)明：
發(fā)現(xiàn)mysql3306上創(chuàng)建的test01同步到了其他的2個(gè)mysql。然而在MGR組中mysql3307 和mysql3308是只讀，不能寫(xiě)入的。
關(guān)掉mysql3306服務(wù)會(huì)發(fā)生什么？？ 直接會(huì)從mysql3307和mysql3308實(shí)例中選擇一個(gè)作為mysql主庫(kù)，另外一個(gè)作為新主庫(kù)的slave庫(kù)
關(guān)掉mysql3306服務(wù)一段時(shí)間后，有重新開(kāi)啟mysql3306，并且start group_replication;重新加入到MGR組會(huì)發(fā)生什么？？
此時(shí)mysql3306不會(huì)切換為master庫(kù)，而是成為了宕機(jī)后新主庫(kù)的slave庫(kù)。
MGR默認(rèn)的模式也就是咱們演示的single-master模式（單主寫(xiě)入模式）。那如何開(kāi)啟mysql的mult-master多主寫(xiě)入的模式呢？？下面簡(jiǎn)單介紹下：
首先要先關(guān)閉只讀的從庫(kù)mysql3307和mysql3308實(shí)例最后關(guān)閉mysql3306
其次在各自的my.cnf配置文件中開(kāi)啟如下參數(shù)：
loose-group_replication_single_primary_mode=off ##在配置MGR的mutl-master模式時(shí)開(kāi)啟
loose-group_replication_enforce_update_everywhere_checks=on ##在配置MGR的mutl-master模式時(shí)開(kāi)啟 最后啟動(dòng)mysql實(shí)例，按照配置MGR-single-master的順序步驟來(lái)進(jìn)行配置MGR的multi-master模式
特別提示：
multi-master模式下，3個(gè)節(jié)點(diǎn)mysql實(shí)例都是可讀寫(xiě)的。但是在第一個(gè)啟動(dòng)的mysql實(shí)例上需要執(zhí)行set global group_replication_bootstrap_group=ON;（注意：后面的節(jié)點(diǎn)不需要執(zhí)行這個(gè)sql）
mysql5.7 開(kāi)啟MGR multi-master模式后，查看實(shí)例的狀態(tài)發(fā)現(xiàn)不存在所謂的leader
(root@localhost:mysql.sock)[(none)]>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
| group_replication_applier | 862addac-10c5-11e9-9af5-bcaec502b317 | localhost.localdomain | 3307 | ONLINE |
| group_replication_applier | a36b8f24-10c3-11e9-bf76-bcaec502b318 | localhost.localdomain | 3308 | ONLINE |
| group_replication_applier | da3809c4-10bc-11e9-bb9e-bcaec502b368 | localhost.localdomain | 3306 | ONLINE |
+---------------------------+--------------------------------------+-----------------------+-------------+--------------+
3 rows in set (0.00 sec)
(root@localhost:mysql.sock)[(none)]>select *from performance_schema.replication_group_members where member_id =(select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member');
Empty set (0.00 sec)
(root@localhost:mysql.sock)[(none)]>select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member';
+----------------+
| variable_value |
+----------------+
| |
+----------------+
1 row in set (0.00 sec)mysql3307完整的配置文件如下：
[root@localhost ~]# cat /opt/my3307.cnf
[client]
port = 3307
[mysql]
auto-rehash
prompt="\u@\h [\d]>"
#pager="less -i -n -S"
#tee=/opt/mysql/query.log
[mysqld]
####: for global
user =mysql
basedir =/usr/local/mysql/
datadir =/home/mysql3307/data
server_id =2
port =3307
character_set_server =utf8
explicit_defaults_for_timestamp =off
log_timestamps =system
socket =/tmp/mysql3307.sock
read_only =0
skip_name_resolve =1
auto_increment_increment =1
auto_increment_offset =1
lower_case_table_names =1
secure_file_priv =
open_files_limit =65536
max_connections =1000
thread_cache_size =64
table_open_cache =81920
table_definition_cache =4096
table_open_cache_instances =64
max_prepared_stmt_count =1048576
####: for binlog
binlog_format =row
log_bin =/home/mysql3307/binlog/mysql-bin
binlog_rows_query_log_events =on
#log_slave_updates =on
expire_logs_days =7
binlog_cache_size =65536
#binlog_checksum =none
sync_binlog =1
slave-preserve-commit-order =ON
####: for error-log
log_error =/home/mysql3307/logs/error.log
general_log =off
general_log_file =/home/mysql3307/logs/general.log
####: for slow query log
slow_query_log =on
slow_query_log_file =/home/mysql3307/logs/slow.log
#log_queries_not_using_indexes =on
long_query_time =1.000000
####: for gtid
#gtid_executed_compression_period =1000
gtid_mode =on
enforce_gtid_consistency =on
####: for replication
skip_slave_start =1
#master_info_repository =table
#relay_log_info_repository =table
slave_parallel_type =logical_clock
slave_parallel_workers =4
#rpl_semi_sync_master_enabled =1
#rpl_semi_sync_slave_enabled =1
#rpl_semi_sync_master_timeout =1000
#plugin_load_add =semisync_master.so
#plugin_load_add =semisync_slave.so
binlog_group_commit_sync_delay =100
binlog_group_commit_sync_no_delay_count = 10
####for group_replication
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="3db33b36-0e51-409f-a61d-c99756e90155"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.1.233:33071"
loose-group_replication_group_seeds= "192.168.1.233:33061,192.168.1.233:33071,192.168.1.233:33081"
loose-group_replication_ip_whitelist="192.168.1.233/24"
##loose-group_replication_single_primary_mode=off ##在配置MGR的mutl-master模式時(shí)開(kāi)啟
##loose-group_replication_enforce_update_everywhere_checks=on ##在配置MGR的mutl-master模式時(shí)開(kāi)啟
####: for innodb
default_storage_engine =innodb
default_tmp_storage_engine =innodb
innodb_data_file_path =ibdata1:1024M:autoextend
innodb_temp_data_file_path =ibtmp1:12M:autoextend
innodb_buffer_pool_filename =ib_buffer_pool
innodb_log_group_home_dir =/home/mysql3307/data
innodb_log_files_in_group =3
innodb_log_file_size =1024M
innodb_file_per_table =on
innodb_online_alter_log_max_size =128M
innodb_open_files =65535
innodb_page_size =16k
innodb_thread_concurrency =0
innodb_read_io_threads =4
innodb_write_io_threads =4
innodb_purge_threads =4
innodb_page_cleaners =4
# 4(刷新lru臟頁(yè))
innodb_print_all_deadlocks =on
innodb_deadlock_detect =on
innodb_lock_wait_timeout =20
innodb_spin_wait_delay =128
innodb_autoinc_lock_mode =2
innodb_io_capacity =200
innodb_io_capacity_max =2000
#--------Persistent Optimizer Statistics
innodb_stats_auto_recalc =on
innodb_stats_persistent =on
innodb_stats_persistent_sample_pages =20
innodb_adaptive_hash_index =on
innodb_change_buffering =all
innodb_change_buffer_max_size =25
innodb_flush_neighbors =1
#innodb_flush_method =
innodb_doublewrite =on
innodb_log_buffer_size =128M
innodb_flush_log_at_timeout =1
innodb_flush_log_at_trx_commit =1
innodb_buffer_pool_size =4096M
innodb_buffer_pool_instances =4
autocommit =1
#--------innodb scan resistant
innodb_old_blocks_pct =37
innodb_old_blocks_time =1000
#--------innodb read ahead
innodb_read_ahead_threshold =56
innodb_random_read_ahead =OFF
#--------innodb buffer pool state
innodb_buffer_pool_dump_pct =25
innodb_buffer_pool_dump_at_shutdown =ON
innodb_buffer_pool_load_at_startup =ON 到此處mysql5.7.24的Grid+MGR模式演示完畢
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼