Shulou(Shulou.com)06/01 Report--

Environment:

Node 1VR 192.168.157.128 CentOS 7.6

Database: mysql-5.7.27-linux-glibc2.12-x86_64

Node 2VR 192.168.157.129 CentOS 7.6

Database: mysql-5.7.27-linux-glibc2.12-x86_64

Node 3VR 192.168.157.130 CentOS 7.6

Database: mysql-5.7.27-linux-glibc2.12-x86_64

1. Install MySQL

Set the / etc/hosts mapping on the three db servers as follows:

192.168.157.128 mgr1

192.168.157.129 mgr2

192.168.157.130 mgr3

Installed database server:

IP address: Port number: data directory: 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

The installation process is slight.

Configure my.cnf:

Note that the server_id, loose-group_replication_local_address and loose-group_replication_group_seeds of each node are configured with their own corresponding parameters

Configure 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 purl 33061'

Loose-group_replication_group_seeds = '192.168.157.128VR 33061192.168.157.129VR 33061192.168.157.130R 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. Create MGR

Set up a copy account on 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

Install the group replication plug-in

Install the group replication plug-in on mgr1, mgr2, and mgr3

Mysql > INSTALL PLUGIN group_replication SONAME 'group_replication.so'

Configure group replication parameters

Make sure binlog_format is in row format.

Mysql > show variables like 'binlog_format'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_format | ROW |

+-+ +

1 row in set (0.00 sec)

Profile configuration:

(1) my.cnf configuration on mgr1:

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.128purl 33061"

Loose-group_replication_group_seeds = "192.168.157.128VR 33061192.168.157.129WR 33061192.168.157.130R 33061"

Loose-group_replication_bootstrap_group = off

Loose-group_replication_single_primary_mode = true

Loose-group_replication_enforce_update_everywhere_checks = false

(2) my.cnf configuration on mgr2:

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 purl 33061"

Loose-group_replication_group_seeds = "192.168.157.128VR 33061192.168.157.129WR 33061192.168.157.130R 33061"

Loose-group_replication_bootstrap_group = off

Loose-group_replication_single_primary_mode = true

Loose-group_replication_enforce_update_everywhere_checks = false

(3) my.cnf configuration on mgr3:

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.128VR 33061192.168.157.129WR 33061192.168.157.130R 33061"

Loose-group_replication_bootstrap_group = off

Loose-group_replication_single_primary_mode = true

Loose-group_replication_enforce_update_everywhere_checks = false

After configuration, restart the mysql service on the 3 db

[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

Start the mgr cluster

Start building a group replication cluster, usually with commands

Execute sequentially on mgr1, mgr2, and 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)

Set up the basic main library master library on Db1:

# setting group_replication_bootstrap_group to ON is to indicate that the server that joins the cluster in the future is based on this server, and it does not need to be set if you join in the future.

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

Start group_replication on Db2:

Execute startup on the mysql command line on Db2:

Mysql > set global group_replication_allow_local_disjoint_gtids_join=ON

Mysql > start group_replication

Mysql > select * from performance_schema.replication_group_members

Zhengzhou Tongji Hospital: http://jbk.39.net/yiyuanzaixian/zztjyy/

Start group_replication on Db3:

-- execute on the Db3 command line:

Mysql > set global group_replication_allow_local_disjoint_gtids_join=ON

Mysql > start group_replication

-- then go to the master library mgr1 and check the group_replication members. There will be a display of mgr3, and it is already ONLINE.

Mysql > select * from performance_schema.replication_group_members

Finally, check the cluster status. If it is ONLINE, it means OK:

Mysql > select * from performance_schema.replication_group_members

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.