MySQL8.0的MGR多主搭建與測試

環境：

虛擬機VMware，

OS：CentOS Linux release 7.3.1611 (Core)

按照下述的單機Mysql8.0的安裝方式進行安裝完之后，將該機復制兩臺，分別修改IP為192.168.1.78和192.168.1.78，以及兩臺新機器配置文件/etc/my.cnf的serverid，還有要去數據路徑下刪掉auto.cnf不然配置不成。

vim /etc/hosts

192.168.1.78 mgr-mul1

192.168.1.79 mgr-mul2

192.168.1.80 mgr-mul3

要用hostname修改每臺機器的主機名確保不相同，不然配置不成功。

一、單機安裝(192.168.1.78)

官網下載8.0.11版本的tar.gz包解壓，復制更名到/usr/local/mysql

創建數據路徑及日志路徑并把他們權限改給mysql用戶：

/data/mysql3306/mysql3306

/data/mysql3306/logs

配置文件如下：注意修改server-id及組相關ip

vim /etc/my.cnf

-----------------------------------------------------------------------

[client]

port = 3306

socket = /tmp/mysql3306.sock

default_character_set = utf8mb4

[mysql]

default_character_set = utf8mb4

[mysqld]

lower_case_table_names = 1

port = 3306

basedir = /usr/local/mysql8

datadir = /data/mysql3306/mysql3306

socket = /tmp/mysql3306.sock

pid_file = /data/mysql3306/mysql3306.pid

tmpdir = /tmp/

default_authentication_plugin = mysql_native_password

skip_name_resolve = 1

character_set_server = utf8mb4

collation_server = utf8mb4_unicode_ci

max_connections = 2000

max_connect_errors = 10000

interactive_timeout = 600

wait_timeout = 600

table_open_cache = 2048

thread_cache_size = 51

max_allowed_packet = 16M

tmp_table_size = 256M

max_heap_table_size = 96M

sort_buffer_size = 4M

read_buffer_size = 4M #??IO

join_buffer_size = 4M

read_rnd_buffer_size = 64M #??簍虜

bulk_insert_buffer_size = 64M #

log_error = /data/mysql3306/logs/mysql-error.log

log_timestamps = system

slow_query_log = 1

slow_query_log_file = /data/mysql3306/logs/mysql-slow.log

long_query_time = 1

log_queries_not_using_indexes = 1

log_throttle_queries_not_using_indexes =10

log_slow_admin_statements = 1

log_slow_slave_statements = 1

min_examined_row_limit = 100

log_queries_not_using_indexes = 1

log_output = FILE

relay_log = /data/mysql3306/mysql3306/mysql-relay

#binlog

server_id = 1783306

log_bin = /data/mysql3306/mysql3306/mysql-bin

binlog_expire_logs_seconds =604800

binlog_format = row

max_binlog_size = 1024M

max_binlog_cache_size = 8G

binlog_cache_size = 4M

sync_binlog = 1

master_info_repository = TABLE

relay_log_info_repository = TABLE

relay_log_recovery = ON

log_slave_updates = 1

#innodb

default_storage_engine = InnoDB

innodb_page_size = 16384

innodb_data_home_dir = /data/mysql3306/mysql3306/

innodb_data_file_path = ibdata1:512M:autoextend

innodb_log_group_home_dir = /data/mysql3306/mysql3306

innodb_buffer_pool_instances = 1

innodb_buffer_pool_size = 1024M

innodb_buffer_pool_dump_pct = 40

innodb_page_cleaners = 8

innodb_log_file_size = 256M

innodb_log_files_in_group = 2

innodb_log_buffer_size = 32M #default 16M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 30

innodb_strict_mode = 1

innodb_print_all_deadlocks = 1

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_buffer_pool_dump_pct = 40

innodb_open_files = 65536

innodb_file_per_table = 1

innodb_lock_wait_timeout = 30

innodb_read_io_threads = 8

innodb_write_io_threads = 8

innodb_io_capacity = 200

innodb_flush_log_at_trx_commit = 1

innodb_flush_method = O_DIRECT

innodb_purge_threads = 4

innodb_max_dirty_pages_pct = 75

transaction_isolation = READ-COMMITTED

innodb_page_cleaners = 16

explicit_defaults_for_timestamp = 1

#undo

innodb_undo_directory = /data/mysql3306/mysql3306/

innodb_undo_tablespaces = 3

innodb_undo_log_truncate = 1

innodb_max_undo_log_size = 1000M

innodb_purge_rseg_truncate_frequency = 128

#rep

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=16

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

binlog_checksum=NONE

slave_preserve_commit_order=1

#group_replication_flow_control_mode=DISABLED

log_slave_updates=1

#GR

enforce_gtid_consistency=1

gtid_mode = on

transaction_write_set_extraction =XXHASH64 # off

loose-group_replication_group_name ="a876d35e-9110-11e6-a365-882b2b5s09d6" #可在庫里使用SELECT UUID()生成，一個組的所有機器都相同。

loose-group_replication_local_address ="192.168.1.78:24900" #本機IP

loose-group_replication_group_seeds ="192.168.1.78:24900,192.168.1.79:24900,192.168.1.80:24900"

loose-group_replication_bootstrap_group =off # off

loose-group_replication_start_on_boot =off #非常重要

[mysqldump]

max_allowed_packet = 16M

[mysqldump]

max_allowed_packet = 16M

-----------------------------------------------------------------------

初始化

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/mysql3306/mysql3306/ --explicit_defaults_for_timestamp --user=mysql

22個文件。

啟動

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --ledir=/usr/local/mysql/bin --datadir=/data/mysql3306/mysql3306 --user=mysql &

echo "/usr/local/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --ledir=/usr/local/mysql8/bin --datadir=/data/mysql3306/mysql3306 --user=mysql &">>/etc/rc.local

登錄：

mysql

alter user root@localhost identified by '123456';

create user root@'192.168.1.%' identified by '123456';

grant all on *.* to root@'192.168.1.%';

CREATE USER rep@'%' IDENTIFIED BY 'rep';

GRANT REPLICATION SLAVE ON *.* TO rep@'%';

flush privileges;

保存快照(before MGR-Multi)，然后復制兩臺，做以下幾處修改：

(1)修改虛擬機IP分別為192.168.1.79和192.168.1.80，并修改各自的主機名hostname與hosts文件對應;

(2)然后修改各自的my.cnf里的server_id和loose-group_replication_local_address改為本機IP;

(3)進入/data/mysql3306/mysql3306里刪除auto.cnf文件。

然后各自保存快照(before MGR-Multi);

二、多主搭建

1、所有節點上安裝組件：

install plugin group_replication soname 'group_replication.so';

show plugins;

2、在192.168.1.78上：(只需要在這個上執行，跟單主模式相同)

CHANGE MASTER TO MASTER_USER='rep',MASTER_PASSWORD='rep' FOR CHANNEL 'group_replication_recovery';

set persist group_replication_single_primary_mode=off; --單主的都是ON

臨時開啟bootstrap：

SET GLOBAL group_replication_bootstrap_group=ON;

start group_replication;

SET GLOBAL group_replication_bootstrap_group=OFF;

select * from performance_schema.replication_group_members;

3、等上述操作完成之后，在192.168.1.79和192.168.1.80依次執行：

CHANGE MASTER TO MASTER_USER='rep',MASTER_PASSWORD='rep' FOR CHANNEL 'group_replication_recovery';

set persist group_replication_single_primary_mode=off;

START GROUP_REPLICATION;

4、驗證：

任意庫執行：select * from performance_schema.replication_group_members;

select * from performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

| group_replication_applier | 5651f374-97c8-11e8-9966-000c295ce53e | mgr-mul1 | 3306 | ONLINE | PRIMARY | 8.0.11 |

| group_replication_applier | 749eb449-97e1-11e8-ab87-000c29ee3a4b | mgr-mul3 | 3306 | ONLINE | PRIMARY | 8.0.11 |

| group_replication_applier | 7753edfa-97e1-11e8-a10c-000c296952a6 | mgr-mul2 | 3306 | ONLINE | PRIMARY | 8.0.11 |

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

可以看到三臺都在線、各自主機名正確，并且都是primary則表示MGR多主配置成功。

select * from performance_schema.replication_connection_status\G，查看是否有報錯;

例如：select * from performance_schema.replication_connection_status\G;

*************************** 1. row ***************************

CHANNEL_NAME: group_replication_recovery

GROUP_NAME:

SOURCE_UUID:

THREAD_ID: NULL

SERVICE_STATE: OFF

COUNT_RECEIVED_HEARTBEATS: 0

LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000

RECEIVED_TRANSACTION_SET:

LAST_ERROR_NUMBER: 0

LAST_ERROR_MESSAGE:

LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000

LAST_QUEUED_TRANSACTION:

LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000

LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000

LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000

LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000

QUEUEING_TRANSACTION:

QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000

QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000

QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000

*************************** 2. row ***************************

CHANNEL_NAME: group_replication_applier

GROUP_NAME: 8ef01324-97ce-11e8-b4cc-000c295ce53e

SOURCE_UUID: 8ef01324-97ce-11e8-b4cc-000c295ce53e

THREAD_ID: NULL

SERVICE_STATE: ON

COUNT_RECEIVED_HEARTBEATS: 0

LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00.000000

RECEIVED_TRANSACTION_SET: 8ef01324-97ce-11e8-b4cc-000c295ce53e:1-7:1000006:2000006

LAST_ERROR_NUMBER: 0

LAST_ERROR_MESSAGE:

LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000

LAST_QUEUED_TRANSACTION: 8ef01324-97ce-11e8-b4cc-000c295ce53e:2000006

LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2018-08-08 09:30:19.410114

LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000

LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2018-08-08 09:30:19.412069

LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2018-08-08 09:30:19.412081

QUEUEING_TRANSACTION:

QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000

QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000

QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000

三臺機器都是group_replication_applier的狀態為ON，group_replication_recovery的狀態為OFF。

5、注意：#############################################################################

如果把三臺全都關閉了再重啟多主MGR的時候，要再使用bootstrap;

#############################################################################

三、測試

任意庫建庫建表，其他庫均可同步。

四、問題

感覺MGR多主似乎不太穩定，經常會出現某個庫掉線的情況又不會自動拉起來;用循環腳本測試一旦出現較高的并發，就會掉節點，不論是否用代理測試都掉節點，好像不太實用，也可能是因為我配置的什么地方參數有問題，希望有高人給指點一下。

拉起來節點只需要依次執行stop group_replication和start group_replication即可。

轉自：https://blog.csdn.net/zhangwenjiang001/article/details/81503576