MySQL MGR單主模式詳細搭建過程

環境：?

節點1：192.168.157.128 ? CentOS 7.6

數據庫：mysql-5.7.27-linux-glibc2.12-x86_64

節點2：192.168.157.129 ? CentOS 7.6

數據庫：mysql-5.7.27-linux-glibc2.12-x86_64

節點3：192.168.157.130 ? CentOS 7.6

數據庫：mysql-5.7.27-linux-glibc2.12-x86_64

1、安裝MySQL

在三臺db服務器上面設置/etc/hosts映射，如下：

?

192.168.157.128? mgr1

192.168.157.129? mgr2

192.168.157.130? mgr3

?

安裝的數據庫服務器：

IP地址： ? ? ? ? ? ? ? ? ? ? ? ? ? ? 端口號：數據目錄： ? ? ?Server-id:

192.168.157.128（mgr1） 3306 /opt/mysql/data 20 ? ? ?

192.168.157.129（mgr2） 3306 /opt/mysql/data 21

192.168.157.130（mgr3） 3306 /opt/mysql/data 22

安裝過程略。。。。。

配置my.cnf:

注意每個節點的server_id、loose-group_replication_local_address、loose-group_replication_group_seeds都配置成自己的相應的參數

配置my.cnf:

[client]

port = 3306

socket = /opt/mysql/tmp/mysql.sock

[mysqld]

port = 3306

socket = /opt/mysql/tmp/mysql.sock

back_log = 80

basedir = /opt/mysql

tmpdir = /tmp

datadir = /opt/mysql/data

pid-file=/opt/mysql/tmp/mysqld.pid

#-------------------gobal variables------------#

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 = /opt/mysql/log/mysql-bin

transaction_write_set_extraction = XXHASH64

loose-group_replication_group_name = 'ce9be252-2b71-11e6-b8f4-00212844f856'

loose-group_replication_start_on_boot = off

loose-group_replication_local_address = '192.168.157.128:33061'

loose-group_replication_group_seeds ='192.168.157.128:33061,192.168.157.129:33061,192.168.157.130:33061'

loose-group_replication_bootstrap_group = off

loose-group_replication_single_primary_mode = true

loose-group_replication_enforce_update_everywhere_checks = false

max_connect_errors = 20000

max_connections = 2000

wait_timeout = 3600

interactive_timeout = 3600

net_read_timeout = 3600

net_write_timeout = 3600

table_open_cache = 1024

table_definition_cache = 1024

thread_cache_size = 512

open_files_limit = 10000

character-set-server = utf8

collation-server = utf8_bin

skip_external_locking

performance_schema = 1

user = mysql

myisam_recover_options = DEFAULT

skip-name-resolve

local_infile = 0

lower_case_table_names = 0

#--------------------innoDB------------#

innodb_buffer_pool_size = 2000M

innodb_data_file_path = ibdata1:1000M:autoextend

innodb_flush_log_at_trx_commit = 1

innodb_io_capacity = 600

innodb_lock_wait_timeout = 120

innodb_log_buffer_size = 8M

innodb_log_file_size = 2000M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 85

innodb_read_io_threads = 8

innodb_write_io_threads = 8

innodb_support_xa = 1

innodb_thread_concurrency = 32

innodb_file_per_table

innodb_rollback_on_timeout

#------------session variables-------#

join_buffer_size = 8M

key_buffer_size = 256M

bulk_insert_buffer_size = 8M

max_heap_table_size = 96M

tmp_table_size = 96M

read_buffer_size = 8M

sort_buffer_size = 2M

max_allowed_packet = 64M

read_rnd_buffer_size = 32M

#------------MySQL Log----------------#

log-bin = my3306-bin

binlog_format = row

sync_binlog = 1

expire_logs_days = 15

#max_binlog_cache_size = 128M

#max_binlog_size = 500M

binlog_cache_size = 64k

slow_query_log

log-slow-admin-statements

log_warnings = 1

long_query_time = 0.25

#---------------replicate--------------#

relay-log = /opt/mysql/log/mysql-relay-bin

relay-log-index = relay3306.index

relay-log = relay3306

server-id = 20

init_slave = 'set sql_mode=STRICT_ALL_TABLES'

log-slave-updates

[myisamchk]

key_buffer = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 8192

log-error = /opt/mysql/log/mysql.err

2、創建MGR?

在mgr1/mgr2/mgr3上建立復制賬號：

mysql>

set sql_log_bin=0;

grant replication slave on *.* to 'repl'@'%' identified by '123456';

flush privileges;

set sql_log_bin=1;

安裝group replication插件

在mgr1、mgr2、mgr3上依次安裝group replication插件

mysql>?INSTALL?PLUGIN?group_replication?SONAME?'group_replication.so'; ?

配置group replication參數

確保binlog_format是row格式。

mysql>?show?variables?like?'binlog_format';??????????????????????????????????????????????

+---------------+-------+??

|?Variable_name?|?Value?|??

+---------------+-------+??

|?binlog_format?|?ROW???|??

+---------------+-------+??

1?row?in?set?(0.00?sec)??

配置文件配置：

（1）??mgr1上的my.cnf配置：

server-id=20

transaction_write_set_extraction = XXHASH64

loose-group_replication_group_name = "ce9be252-2b71-11e6-b8f4-00212844f856" ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

loose-group_replication_start_on_boot = off

loose-group_replication_local_address = "192.168.157.128:33061"

loose-group_replication_group_seeds = "192.168.157.128:33061,192.168.157.129:33061,192.168.157.130:33061"

loose-group_replication_bootstrap_group = off

loose-group_replication_single_primary_mode = true

loose-group_replication_enforce_update_everywhere_checks = false

?

（2）mgr2上的my.cnf配置：

server-id=21

transaction_write_set_extraction = XXHASH64

loose-group_replication_group_name = "ce9be252-2b71-11e6-b8f4-00212844f856"

loose-group_replication_start_on_boot = off

loose-group_replication_local_address = "192.168.157.129:33061"

loose-group_replication_group_seeds =?"192.168.157.128:33061,192.168.157.129:33061,192.168.157.130:33061"

loose-group_replication_bootstrap_group = off

loose-group_replication_single_primary_mode = true

loose-group_replication_enforce_update_everywhere_checks = false

（3）mgr3上的my.cnf配置：

server-id=22

transaction_write_set_extraction = XXHASH64

loose-group_replication_group_name = "ce9be252-2b71-11e6-b8f4-00212844f856"

loose-group_replication_start_on_boot = off

loose-group_replication_local_address = "192.168.157.130 :33061"

loose-group_replication_group_seeds =?"192.168.157.128:33061,192.168.157.129:33061,192.168.157.130:33061"

loose-group_replication_bootstrap_group = off

loose-group_replication_single_primary_mode = true

loose-group_replication_enforce_update_everywhere_checks = false

配置完后，重啟3個db上的mysql服務

[root@mha01 etc]# mysqladmin -uroot -p -S /opt/mysql/tmp/mysql.sock shutdown

Enter password:

2019-08-19T07:58:22.734405Z mysqld_safe mysqld from pid file /opt/mysql/tmp/mysqld.pid ended

[1]+ ?Done ? ? ? ? ? ? ? ? ? ?/opt/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf

[root@mha01 etc]# /opt/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

[1] 85384

[root@mha01 etc]# 2019-08-19T07:58:58.536554Z mysqld_safe Logging to '/opt/mysql/log/mysql.err'.

2019-08-19T07:58:58.570079Z mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data

啟動mgr群集

開始構建group replication集群，通常操作命令

在mgr1、mgr2、mgr3上依次執行

mysql>??CHANGE?MASTER?TO?MASTER_USER='repl',?MASTER_PASSWORD='123456'?FOR?CHANNEL?'group_replication_recovery';??

Query?OK,?0?rows?affected,?2?warnings?(0.02?sec) ?

?

Db1上建立基本主庫master庫：

#?設置group_replication_bootstrap_group為ON是為了標示以后加入集群的服務器以這臺服務器為基準，以后加入的就不需要設置。??

mysql>?SET?GLOBAL?group_replication_bootstrap_group?=?ON;??

Query?OK,?0?rows?affected?(0.00?sec)??

???

mysql>??START?GROUP_REPLICATION;??

Query?OK,?0?rows?affected?(1.03?sec)??

???

mysql> select * from performance_schema.replication_group_members;?

Db2上啟動group_replication：?

Db2上mysql命令行上執行啟動：

mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;

?

mysql> start?group_replication;

?

mysql> select * from performance_schema.replication_group_members;

鄭州同濟醫院：http://jbk.39.net/yiyuanzaixian/zztjyy/

Db3上啟動group_replication：

--?Db3命令行上執行：??

mysql>?set?global?group_replication_allow_local_disjoint_gtids_join=ON; ?

???

mysql>?start?group_replication; ?

--?再去master庫mgr1上，查看group_replication成員，會有mgr3的顯示，而且已經是ONLINE了??

mysql> select * from performance_schema.replication_group_members;

?

最后查看集群狀態，都為ONLINE就表示OK：

mysql> select * from performance_schema.replication_group_members; ?