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

Three common ways to embed CSS in HTML documents

The following three methods are commonly used to d...

Introducing ECharts into the Vue project

Table of contents 1. Installation 2. Introduction...

Solution to Mysql binlog log file being too large

Table of contents 1. Related binlog configuration...

Detailed explanation of how to efficiently import multiple .sql files into MySQL

MySQL has multiple ways to import multiple .sql f...

CSS3 transition rotation perspective 2d3d animation and other effects example code

Table of contents CSS3 Box Model a. CSS3 filter b...

Example of using rem to replace px in vue project

Table of contents tool Install the plugin Add a ....

How to use bind to set up DNS server

DNS (Domain Name Server) is a server that convert...

Several common methods of CSS equal height layout

Equal height layout Refers to the layout of child...

MySQL 5.7.23 decompression version installation tutorial with pictures and text

Download the MySQL installer Official download ad...

js implements form validation function

This article example shares the specific code of ...

Steps to deploy multiple tomcat services using DockerFile on Docker container

1. [admin@JD ~]$ cd opt #Enter opt in the root di...

uniapp dynamic modification of element node style detailed explanation

Table of contents 1. Modify by binding the style ...

Detailed explanation of Nginx status monitoring and log analysis

1. Nginx status monitoring Nginx provides a built...

Ideas and codes for implementing Vuex data persistence

What is vuex vuex: is a state manager developed s...

How to view the database installation path in MySQL

We can view the installation path of mysql throug...