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

Detailed explanation of the new CSS display:box property

1. display:box; Setting this property on an eleme...

Solution to MySql Error 1698 (28000)

1. Problem description: MysqlERROR1698 (28000) so...

HTML Basics: The basic structure of HTML

The basic structure of HTML hypertext documents is...

Summary of relevant knowledge points of ajax in jQuery

Preface Students who learn JavaScript know that A...

Use CSS3 to implement button hover flash dynamic special effects code

We have introduced how to create a waterfall layo...

js addition, subtraction, multiplication and division precise calculation method example code

Preface Because computer numbers are floating poi...

MySQL 5.7.17 winx64 installation and configuration graphic tutorial

I summarized the previous notes on installing MyS...

HTML+CSS to achieve drop-down menu

1. Drop-down list example The code is as follows:...

...

jQuery achieves large-screen scrolling playback effect

This article shares the specific code of jQuery t...

How to build docker+jenkins+node.js automated deployment environment from scratch

This case is based on CentOS 7 system Suitable fo...

Hover zoom effect made with CSS3

Result:Implementation code: html <link href=&#...

Beginner's guide to building a website ⑦: It's so easy to make a beautiful website

I once promised that I would keep writing until pe...

Installation tutorial of mysql8.0rpm on centos7

First, download the diagram 1. First uninstall th...

Vue resets data to its initial state

In some cases, the data in data needs to be reuse...