MySQL-group-replication configuration steps (recommended)

MySQL-group-replication configuration steps (recommended)

MySQL-Group-Replication is a new feature developed in MySQL-5.7.17; it achieves strong consistency between master and slave.

But for now, the main problem is that the performance is not very good.

【1】Confirm that the current MySQL database version is 5.7.17 or above

/usr/local/mysql/bin/mysqld --version
/usr/local/mysql/bin/mysqld Ver 5.7.17 for linux-glibc2.5 on x86_64 (MySQL Community Server (GPL))

[2] The experimental environment is to install three MySQL servers on one host, and the three of them form a group-replication group.

The content of /tmp/4406.cnf is as follows:

[mysqld]
####: for global
user =jianglexing #mysql
basedir =/usr/local/mysql # /usr/local/mysql/
datadir =/tmp/4406/ # /usr/local/mysql/data
server_id =4406 # 0
port =4406 # 3306
socket =/tmp/4406/mysql.sock # /tmp/mysql.sock
auto_increment_increment =1 # 1
auto_increment_offset =1 # 1
lower_case_table_names = 1 # 0
secure_file_priv = # null


####: for binlog
binlog_format =row # row
log_bin =mysql-bin # off
binlog_rows_query_log_events = on # off
log_slave_updates = on # off
expire_logs_days =4 # 0
binlog_cache_size = 32768 # 32768 (32k)
binlog_checksum =none # CRC32
sync_binlog =1 # 1


####: for error-log
log_error =mysql-err.log # /usr/local/mysql/data/localhost.localdomain.err


####: for slow query log


####: for gtid
gtid_mode = on # off
enforce_gtid_consistency = on # off


####: for replication
master_info_repository =table # file
relay_log_info_repository =table # file


####: for group replication
transaction_write_set_extraction =XXHASH64 # off
loose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #  
loose-group_replication_start_on_boot =off # off
loose-group_replication_local_address = "127.0.0.1:24901" #
loose-group_replication_group_seeds = "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
loose-group_replication_bootstrap_group =off # off


####: for innodb
default_storage_engine = innodb # innodb
default_tmp_storage_engine = innodb # innodb
innodb_data_file_path =ibdata1:12M:autoextend # ibdata1:12M:autoextend
innodb_temp_data_file_path =ibtmp1:12M:autoextend # ibtmp1:12M:autoextend
innodb_log_group_home_dir = ./ # ./
innodb_log_files_in_group = 2 # 2
innodb_log_file_size =48M # 50331648(48M)
innodb_file_format = Barracuda # Barracuda
innodb_file_per_table = on # on
innodb_page_size = 16k # 16384 (16k)
innodb_thread_concurrency = 0 # 0
innodb_read_io_threads = 4 # 4
innodb_write_io_threads = 4 # 4
innodb_purge_threads = 4 # 4
innodb_print_all_deadlocks = on # off
innodb_deadlock_detect = on # on
innodb_lock_wait_timeout = 50 # 50
innodb_spin_wait_delay = 6 # 6
innodb_autoinc_lock_mode =2 # 1
innodb_stats_persistent = on # on
innodb_stats_persistent_sample_pages = 20 # 20
innodb_adaptive_hash_index = on # on
innodb_change_buffering =all # all
innodb_change_buffer_max_size = 25 # 25
innodb_flush_neighbors = 1 # 1
innodb_flush_method = O_DIRECT #  
innodb_doublewrite = on # on
innodb_log_buffer_size =16M # 16777216(16M)
innodb_flush_log_at_timeout = 1 # 1
innodb_flush_log_at_trx_commit = 1 # 1
autocommit =1 # 1

[client]
auto-rehash

The content of /tmp/5506.cnf is as follows:

[mysqld]
####: for global
user =jianglexing #mysql
basedir =/usr/local/mysql # /usr/local/mysql/
datadir =/tmp/5506 # /usr/local/mysql/data
server_id =5506 # 0
port =5506 # 3306
socket =/tmp/5506/mysql.sock # /tmp/mysql.sock
auto_increment_increment =1 # 1
auto_increment_offset =1 # 1
lower_case_table_names = 1 # 0
secure_file_priv = # null


####: for binlog
binlog_format =row # row
log_bin =mysql-bin # off
binlog_rows_query_log_events = on # off
log_slave_updates = on # off
expire_logs_days =4 # 0
binlog_cache_size = 32768 # 32768 (32k)
binlog_checksum =none # CRC32
sync_binlog =1 # 1


####: for error-log
log_error =mysql-err.log # /usr/local/mysql/data/localhost.localdomain.err


####: for slow query log


####: for gtid
gtid_mode = on # off
enforce_gtid_consistency = on # off


####: for replication
master_info_repository =table # file
relay_log_info_repository =table # file


####: for group replication
transaction_write_set_extraction =XXHASH64 # off
loose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #  
loose-group_replication_start_on_boot =off # off
loose-group_replication_local_address = "127.0.0.1:24902" #
loose-group_replication_group_seeds = "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
loose-group_replication_bootstrap_group =off # off


####: for innodb
default_storage_engine = innodb # innodb
default_tmp_storage_engine = innodb # innodb
innodb_data_file_path =ibdata1:12M:autoextend # ibdata1:12M:autoextend
innodb_temp_data_file_path =ibtmp1:12M:autoextend # ibtmp1:12M:autoextend
innodb_log_group_home_dir = ./ # ./
innodb_log_files_in_group = 2 # 2
innodb_log_file_size =48M # 50331648(48M)
innodb_file_format = Barracuda # Barracuda
innodb_file_per_table = on # on
innodb_page_size = 16k # 16384 (16k)
innodb_thread_concurrency = 0 # 0
innodb_read_io_threads = 4 # 4
innodb_write_io_threads = 4 # 4
innodb_purge_threads = 4 # 4
innodb_print_all_deadlocks = on # off
innodb_deadlock_detect = on # on
innodb_lock_wait_timeout = 50 # 50
innodb_spin_wait_delay = 6 # 6
innodb_autoinc_lock_mode =2 # 1
innodb_stats_persistent = on # on
innodb_stats_persistent_sample_pages = 20 # 20
innodb_adaptive_hash_index = on # on
innodb_change_buffering =all # all
innodb_change_buffer_max_size = 25 # 25
innodb_flush_neighbors = 1 # 1
innodb_flush_method = O_DIRECT #  
innodb_doublewrite = on # on
innodb_log_buffer_size =16M # 16777216(16M)
innodb_flush_log_at_timeout = 1 # 1
innodb_flush_log_at_trx_commit = 1 # 1
autocommit =1 # 1

The content of /tmp/6606.cnf is as follows:

[mysqld]
####: for global
user =jianglexing #mysql
basedir =/usr/local/mysql # /usr/local/mysql/
datadir =/tmp/6606/ # /usr/local/mysql/data
server_id =6606 # 0
port =6606 # 3306
socket =/tmp/6606/mysql.sock # /tmp/mysql.sock
auto_increment_increment =1 # 1
auto_increment_offset =1 # 1
lower_case_table_names = 1 # 0
secure_file_priv = # null


####: for binlog
binlog_format =row # row
log_bin =mysql-bin # off
binlog_rows_query_log_events = on # off
log_slave_updates = on # off
expire_logs_days =4 # 0
binlog_cache_size = 32768 # 32768 (32k)
binlog_checksum =none # CRC32
sync_binlog =1 # 1


####: for error-log
log_error =mysql-err.log # /usr/local/mysql/data/localhost.localdomain.err


####: for slow query log


####: for gtid
gtid_mode = on # off
enforce_gtid_consistency = on # off


####: for replication
master_info_repository =table # file
relay_log_info_repository =table # file


####: for group replication
transaction_write_set_extraction =XXHASH64 # off
loose-group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #  
loose-group_replication_start_on_boot =off # off
loose-group_replication_local_address = "127.0.0.1:24903" #
loose-group_replication_group_seeds = "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
loose-group_replication_bootstrap_group =off # off


####: for innodb
default_storage_engine = innodb # innodb
default_tmp_storage_engine = innodb # innodb
innodb_data_file_path =ibdata1:12M:autoextend # ibdata1:12M:autoextend
innodb_temp_data_file_path =ibtmp1:12M:autoextend # ibtmp1:12M:autoextend
innodb_log_group_home_dir = ./ # ./
innodb_log_files_in_group = 2 # 2
innodb_log_file_size =48M # 50331648(48M)
innodb_file_format = Barracuda # Barracuda
innodb_file_per_table = on # on
innodb_page_size = 16k # 16384 (16k)
innodb_thread_concurrency = 0 # 0
innodb_read_io_threads = 4 # 4
innodb_write_io_threads = 4 # 4
innodb_purge_threads = 4 # 4
innodb_print_all_deadlocks = on # off
innodb_deadlock_detect = on # on
innodb_lock_wait_timeout = 50 # 50
innodb_spin_wait_delay = 6 # 6
innodb_autoinc_lock_mode =2 # 1
innodb_stats_persistent = on # on
innodb_stats_persistent_sample_pages = 20 # 20
innodb_adaptive_hash_index = on # on
innodb_change_buffering =all # all
innodb_change_buffer_max_size = 25 # 25
innodb_flush_neighbors = 1 # 1
innodb_flush_method = O_DIRECT #  
innodb_doublewrite = on # on
innodb_log_buffer_size =16M # 16777216(16M)
innodb_flush_log_at_timeout = 1 # 1
innodb_flush_log_at_trx_commit = 1 # 1
autocommit =1 # 1

【3】Initialize three database instances

cd /usr/local/mysql/
./bin/mysqld --defautls-file=/tmp/4406.cnf --datadir=/tmp/4406 --initialize-insecrue

./bin/mysqld --defautls-file=/tmp/5506.cnf --datadir=/tmp/5506 --initialize-insecrue

./bin/mysqld --defautls-file=/tmp/6606.cnf --datadir=/tmp/6606 --initialize-insecrue

【4】Configure the initial instance of group-replication

/usr/local/mysql/bin/mysqld --defaults-file=/tmp/4406.cnf &
mysql -h127.0.0.1 -uroot -P4406

--Add user set sql_log_bin=0;
    create user rpl_user@'%' identified by '123456';
    grant replication slave,replication client on *.* to rpl_user@'%';
    create user rpl_user@'127.0.0.1' identified by '123456';
    grant replication slave,replication client on *.* to rpl_user@'127.0.0.1';
    create user rpl_user@'localhost' identified by '123456';
    grant replication slave,replication client on *.* to rpl_user@'localhost';
    set sql_log_bin=1;

-- Add replication credentials change master to 
      master_user='rpl_user',
      master_password = '123456'
      for channel 'group_replication_recovery';

-- Install group replication objects install plugin group_replication soname 'group_replication.so';

-- Start group replication set global group_replication_bootstrap_group=on;
    start group_replication;
    set global group_replication_bootstrap_group=off;

【5】The configuration process of the 5506 instance is as follows:

/usr/local/mysql/bin/mysqld --defaults-file=/tmp/5506.cnf &
mysql -h127.0.0.1 -uroot -P5506

--Add user set sql_log_bin=0;
    create user rpl_user@'%' identified by '123456';
    grant replication slave,replication client on *.* to rpl_user@'%';
    create user rpl_user@'127.0.0.1' identified by '123456';
    grant replication slave,replication client on *.* to rpl_user@'127.0.0.1';
    create user rpl_user@'localhost' identified by '123456';
    grant replication slave,replication client on *.* to rpl_user@'localhost';
    set sql_log_bin=1;

-- Add replication credentials change master to 
      master_user='rpl_user',
      master_password = '123456'
      for channel 'group_replication_recovery';

-- Install group replication objects install plugin group_replication soname 'group_replication.so';

-- Start group replication start group_replication; # Note that this is not initialization, just join

【6】The operations for instance 6606 are the same as those for instance 5506. The configuration of group replication is now complete.

The above MySQL-group-replication configuration steps (recommended) are all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in:
  • Detailed explanation of replication configuration example between mysql containers
  • MySQL5.6 Replication master-slave replication (read-write separation) configuration complete version
  • Introduction to MySQL Semisynchronous Replication
  • MySQL 5.7 Enhanced Edition Semisync Replication Performance Optimization
  • MySQL-MMM Installation Guide (Multi-Master Replication Manager for MySQL)
  • Summary of issues based on mysql replication
  • mysql5.5 master-slave (Replication) configuration method
  • mysql5.5 master-slave (Replication) master-slave configuration
  • MySQL master-slave replication semi-sync replication

<<:  Scary Halloween Linux Commands

>>:  Implementation steps of js object-oriented encapsulation cascading drop-down menu list

Recommend

Building a LEMP (Linux+Nginx+MySQL+PHP) environment under CentOS 8.1 (tutorial details)

Table of contents Step 1: Update Packages on Cent...

How to bind domain name to nginx service

Configure multiple servers in nginx.conf: When pr...

JavaScript realizes the drag effect of modal box

Here is a case of modal box dragging. The functio...

Detailed explanation of the idea of ​​implementing password display and hiding function in Vue

Effect: Ideas: Use the input type attribute to di...

In-depth exploration of whether Mysql fuzzy query is case-sensitive

Preface Recently, I have been busy writing a smal...

Ubuntu 15.04 opens mysql remote port 3306

Ubuntu 15.04 opens MySQL remote port 3306. All th...

Implementation of CSS child element selection parent element

Usually a CSS selector selects from top to bottom...

XHTML Getting Started Tutorial: XHTML Hyperlinks

It is no exaggeration to say that hyperlinks conne...

Detailed tutorial on jdk installation and environment variable configuration under Win10

Table of contents Preface 1. Preparation 2. Insta...

Detailed explanation of the deployment process of SEATA transaction service Docker

1. Create a database authorization statement >...

js to achieve simple image drag effect

This article shares the specific code of js to ac...

CentOS 7.6 batch add, modify and delete virtual network cards operation introduction

1 Check whether the kernel has a tun module modin...

How to implement Linux automatic shutdown when the battery is low

Preface The electricity in my residence has been ...

A complete guide to clearing floats in CSS (summary)

1. Parent div defines pseudo-classes: after and z...

Solution to Vue's inability to watch array changes

Table of contents 1. Vue listener array 2. Situat...