2020-07-29
MGR多主模式搭建
MGR是MySQL推出的新的MySQL集群模式，它解決了原來(lái)主從復(fù)制模式存在的問(wèn)題，具備故障快速轉(zhuǎn)移能力，集群中的每個(gè)server都擁有集群的全部數(shù)據(jù)。MGR分為單主模式和多主模式，本次要搭建的是后者。
搭建環(huán)境：
Windows 10
MySQL版本：8.0.17
1.創(chuàng)建配置文件
=================my.ini============
[client]
port = 3306
socket = D:/MySQL/group/data1/mysql/run/mysql.sock
[mysqld]
port = 3306
socket = D:/MySQL/group/data1/mysql/run/mysql.sock
pid_file = D:/MySQL/group/data1/mysql/run/mysql.pid
datadir = D:/MySQL/group/data1/mysql/data
basedir= "D:/MySQL/MySQL Server 8.0/"
relay-log = relay-log
default_storage_engine = InnoDB
max_allowed_packet = 512M
max_connections = 2048
open_files_limit = 65535
#skip-name-resolve # 注釋掉是為了允許localhost登錄
skip-host-cache
lower_case_table_names=1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 2048M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
key_buffer_size = 64M
log-error = D:/MySQL/group/data1/mysql/log/mysql_error.log
log-bin = D:/MySQL/group/data1/mysql/binlogs/mysql-bin
slow_query_log = 1
slow_query_log_file = D:/MySQL/group/data1/mysql/log/mysql_slow_query.log
long_query_time = 5
tmp_table_size = 32M
max_heap_table_size = 32M
#query_cache_type = 0
#query_cache_size = 0
secure-file-priv= NULL
#MGR
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
#每個(gè)id都必須唯一的
server_id=1
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
#自動(dòng)安裝插件
plugin_load_add=group_replication.dll
plugin_load_add=mysql_clone.dll
transaction_write_set_extraction=XXHASH64
#必須是一個(gè)有效的UUID, 可以使用數(shù)據(jù)庫(kù)的SELECT UUID()獲取一個(gè)有效的UUID
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#是否啟動(dòng)時(shí)就啟動(dòng)集群復(fù)制
group_replication_start_on_boot=OFF
group_replication_local_address= "127.0.0.1:33066"
group_replication_group_seeds= "127.0.0.1:33066,127.0.0.1:33067,127.0.0.1:33068,127.0.0.1:33069"
group_replication_ip_whitelist="127.0.0.1"
#指示插件是否引導(dǎo)組
group_replication_bootstrap_group=OFF
#多組模式
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
#插件配置
mysqlx_port = 33064
mysqlx_socket=D:/MySQL/group/data1/mysql/run/xplugin.sock
=================my.ini end============
2.初始化數(shù)據(jù)庫(kù)
bin\mysqld.exe --defaults-file="D:\MySQL\group\data1\mysql\etc\my.ini" --initialize --user=mysql
3.啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)
bin\mysqld.exe --defaults-file="D:\MySQL\group\data1\mysql\etc\my.ini"
4.使用臨時(shí)密碼登錄數(shù)據(jù)庫(kù)
mysql -S D:\MySQL\group\data1\mysql\run\mysql.sock -uroot -P3306 -p
#初始化數(shù)據(jù)庫(kù)后可以在error日志行“A temporary password is generated for ... ”下找到自動(dòng)創(chuàng)建臨時(shí)的root密碼。
5.首次登錄必須修改root密碼后才能使用數(shù)據(jù)庫(kù)
ALTER USER USER() IDENTIFIED BY '123456';
6.創(chuàng)建一個(gè)用于同步復(fù)制的user
#所有server上都執(zhí)行
#如果SQL_LOG_BIN=1則記錄操作過(guò)程，自動(dòng)同步到其它節(jié)點(diǎn)
#查看系統(tǒng)變量：show variables like '%log_bin%';
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED WITH mysql_native_password BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
mysql> SET SQL_LOG_BIN=1;
7.配置MGR
#如果配置文件中沒(méi)有配置需要安裝的插件，需要手動(dòng)安裝插件
#plugin_load_add=group_replication.so
#plugin_load_add=mysql_clone.so
#連接到服務(wù)執(zhí)行：INSTALL PLUGIN group_replication SONAME 'group_replication.so';
#檢查插件安裝
#mysql> SHOW PLUGINS;
#第一個(gè)主節(jié)點(diǎn)，引導(dǎo)創(chuàng)建組
#mysql> reset master;
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
#剩余其它節(jié)點(diǎn)
#mysql> reset master;
mysql> START GROUP_REPLICATION;
#查詢組狀態(tài)
mysql> SELECT * FROM performance_schema.replication_group_members;
#查當(dāng)前節(jié)點(diǎn)通信地址
mysql> SELECT @@group_replication_local_address;
#創(chuàng)建測(cè)試表
mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
mysql> INSERT INTO t1 VALUES (1, 'Luis');
mysql> SELECT * FROM t1;
mysql> SHOW BINLOG EVENTS;
mysql> SHOW DATABASES LIKE 'test';
ps: 每個(gè)server都安裝上述的步驟執(zhí)行，只是需要區(qū)別配置文件，并且第一個(gè)server作為引導(dǎo)啟動(dòng)的區(qū)別，即只有第一個(gè)引導(dǎo)server需要設(shè)置group_replication_bootstrap_group變量。
--===========================================================--
搭建注意事項(xiàng)：
1.若配置文件my.ini中設(shè)置了參數(shù)： skip-name-resolve，請(qǐng)將其注釋掉。否則登錄時(shí)會(huì)報(bào)類(lèi)似錯(cuò)誤：“ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server，從而導(dǎo)致使用“l(fā)ocalhost”不能連接到數(shù)據(jù)庫(kù)。
2.Windows下配置文件中要加載的插件改為：group_replication.dll，mysql_clone.dll。
3.若非第一個(gè)節(jié)點(diǎn)啟動(dòng)組同步復(fù)制時(shí)，報(bào)“This member has more executed transactions than those present in the group”的錯(cuò)誤，需要執(zhí)行“reset master”重置事務(wù)id。
新的server加入集群組報(bào)“error connecting to master 'rpl_user@hostname:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061”的錯(cuò)誤，要么安裝插件支持，要么執(zhí)行：
UPDATE mysql.user set host='%' where user='rpl_user';
FLUSH PRIVILEGES;
ALTER USER 'rpl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'password';集群同步復(fù)制搭建成功需要所有的server的同步復(fù)制用戶的加密方式保持一致，所有的操作在每個(gè)server上都有權(quán)限正確執(zhí)行。
6.group_replication_group_seeds的配置可以只配置部分集群成員，配置的成員的有效性是在執(zhí)行START GROUP_REPLICATION時(shí)才會(huì)去驗(yàn)證。當(dāng)前的服務(wù)會(huì)按順序的嘗試連接到列表中的每一個(gè)成員，直到成功加入集群（實(shí)際上并不是按順序連接到第一個(gè)）。因此配置的seeds必須至少有一個(gè)是有效的，否則加入集群會(huì)失敗。
As no ONLINE member has the missing data for recovering in its binary logs, this member will use cloning as its recovery strategy.
參考：https://dev.mysql.com/doc/refman/8.0/en/group-replication.html
