MySQLMGR单主模式详细搭建过程

环境： 

成都服务器托管，成都创新互联公司提供包括服务器租用、成都二枢服务器租用托管、带宽租用、云主机、机柜租用、主机租用托管、CDN网站加速、申请域名等业务的一体化完整服务。电话咨询：13518219792

节点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;