MySQL-group-replication configuration steps (recommended), groupreplication

MySQL-group-replication configuration steps (recommended), groupreplication

MySQL-Group-Replication is a new feature developed by the mysql-5.7.17 version; it achieves strong consistency between master-slave,

However, for the moment, the performance is not very good.

[1] determine that the current mysql database version is 5.7.17 or later.

/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] Three mysql instances are installed on one host in the experimental environment, which form a group-replication group.

/Tmp/ipv6.cnf:

[mysqld]####: for globaluser          =jianglexing          #  mysqlbasedir          =/usr/local/mysql        #  /usr/local/mysql/datadir          =/tmp/4406/          #  /usr/local/mysql/dataserver_id        =4406            #  0port          =4406            #  3306socket          =/tmp/4406/mysql.sock        #  /tmp/mysql.sockauto_increment_increment    =1            #  1auto_increment_offset      =1            #  1lower_case_table_names      =1            #  0secure_file_priv      =            #  null####: for binlogbinlog_format        =row            #  rowlog_bin          =mysql-bin          #  offbinlog_rows_query_log_events    =on            #  offlog_slave_updates      =on            #  offexpire_logs_days      =4            #  0binlog_cache_size      =32768            #  32768(32k)binlog_checksum        =none            #  CRC32sync_binlog        =1            #  1####: for error-loglog_error        =mysql-err.log          #  /usr/local/mysql/data/localhost.localdomain.err####: for slow query log####: for gtidgtid_mode        =on            #  offenforce_gtid_consistency    =on            #  off####: for replicationmaster_info_repository      =table            #  filerelay_log_info_repository    =table            #  file####: for group replicationtransaction_write_set_extraction  =XXHASH64          #  offloose-group_replication_group_name  ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"    #  loose-group_replication_start_on_boot  =off            #  offloose-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 innodbdefault_storage_engine      =innodb            #  innodbdefault_tmp_storage_engine    =innodb            #  innodbinnodb_data_file_path      =ibdata1:12M:autoextend        #  ibdata1:12M:autoextendinnodb_temp_data_file_path    =ibtmp1:12M:autoextend        #  ibtmp1:12M:autoextendinnodb_log_group_home_dir    =./            #  ./innodb_log_files_in_group    =2            #  2innodb_log_file_size      =48M            #  50331648(48M)innodb_file_format      =Barracuda          #  Barracudainnodb_file_per_table      =on             #  oninnodb_page_size      =16k            #  16384(16k)innodb_thread_concurrency    =0            #  0innodb_read_io_threads      =4            #  4innodb_write_io_threads      =4            #  4innodb_purge_threads      =4            #  4innodb_print_all_deadlocks    =on            #  offinnodb_deadlock_detect      =on            #  oninnodb_lock_wait_timeout    =50            #  50innodb_spin_wait_delay      =6            #  6innodb_autoinc_lock_mode    =2            #  1innodb_stats_persistent      =on            #  oninnodb_stats_persistent_sample_pages  =20            #  20innodb_adaptive_hash_index    =on            #  oninnodb_change_buffering      =all            #  allinnodb_change_buffer_max_size    =25            #  25innodb_flush_neighbors      =1            #  1innodb_flush_method      =O_DIRECT          #  innodb_doublewrite      =on            #  oninnodb_log_buffer_size      =16M            #  16777216(16M)innodb_flush_log_at_timeout    =1            #  1innodb_flush_log_at_trx_commit    =1            #  1autocommit        =1            #  1[client]auto-rehash

/Tmp/5506.cnf:

 

[mysqld]####: for globaluser          =jianglexing          #  mysqlbasedir          =/usr/local/mysql        #  /usr/local/mysql/datadir          =/tmp/5506        #  /usr/local/mysql/dataserver_id        =5506            #  0port          =5506            #  3306socket          =/tmp/5506/mysql.sock        #  /tmp/mysql.sockauto_increment_increment    =1            #  1auto_increment_offset      =1            #  1lower_case_table_names      =1            #  0secure_file_priv      =            #  null####: for binlogbinlog_format        =row            #  rowlog_bin          =mysql-bin          #  offbinlog_rows_query_log_events    =on            #  offlog_slave_updates      =on            #  offexpire_logs_days      =4            #  0binlog_cache_size      =32768            #  32768(32k)binlog_checksum        =none            #  CRC32sync_binlog        =1            #  1####: for error-loglog_error        =mysql-err.log          #  /usr/local/mysql/data/localhost.localdomain.err####: for slow query log####: for gtidgtid_mode        =on            #  offenforce_gtid_consistency    =on            #  off####: for replicationmaster_info_repository      =table            #  filerelay_log_info_repository    =table            #  file####: for group replicationtransaction_write_set_extraction  =XXHASH64          #  offloose-group_replication_group_name  ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"    #  loose-group_replication_start_on_boot  =off            #  offloose-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 innodbdefault_storage_engine      =innodb            #  innodbdefault_tmp_storage_engine    =innodb            #  innodbinnodb_data_file_path      =ibdata1:12M:autoextend        #  ibdata1:12M:autoextendinnodb_temp_data_file_path    =ibtmp1:12M:autoextend        #  ibtmp1:12M:autoextendinnodb_log_group_home_dir    =./            #  ./innodb_log_files_in_group    =2            #  2innodb_log_file_size      =48M            #  50331648(48M)innodb_file_format      =Barracuda          #  Barracudainnodb_file_per_table      =on             #  oninnodb_page_size      =16k            #  16384(16k)innodb_thread_concurrency    =0            #  0innodb_read_io_threads      =4            #  4innodb_write_io_threads      =4            #  4innodb_purge_threads      =4            #  4innodb_print_all_deadlocks    =on            #  offinnodb_deadlock_detect      =on            #  oninnodb_lock_wait_timeout    =50            #  50innodb_spin_wait_delay      =6            #  6innodb_autoinc_lock_mode    =2            #  1innodb_stats_persistent      =on            #  oninnodb_stats_persistent_sample_pages  =20            #  20innodb_adaptive_hash_index    =on            #  oninnodb_change_buffering      =all            #  allinnodb_change_buffer_max_size    =25            #  25innodb_flush_neighbors      =1            #  1innodb_flush_method      =O_DIRECT          #  innodb_doublewrite      =on            #  oninnodb_log_buffer_size      =16M            #  16777216(16M)innodb_flush_log_at_timeout    =1            #  1innodb_flush_log_at_trx_commit    =1            #  1autocommit        =1            #  1

/Tmp/6606.cnf:

[mysqld]####: for globaluser          =jianglexing          #  mysqlbasedir          =/usr/local/mysql        #  /usr/local/mysql/datadir          =/tmp/6606/        #  /usr/local/mysql/dataserver_id        =6606            #  0port          =6606            #  3306socket          =/tmp/6606/mysql.sock        #  /tmp/mysql.sockauto_increment_increment    =1            #  1auto_increment_offset      =1            #  1lower_case_table_names      =1            #  0secure_file_priv      =            #  null####: for binlogbinlog_format        =row            #  rowlog_bin          =mysql-bin          #  offbinlog_rows_query_log_events    =on            #  offlog_slave_updates      =on            #  offexpire_logs_days      =4            #  0binlog_cache_size      =32768            #  32768(32k)binlog_checksum        =none            #  CRC32sync_binlog        =1            #  1####: for error-loglog_error        =mysql-err.log          #  /usr/local/mysql/data/localhost.localdomain.err####: for slow query log####: for gtidgtid_mode        =on            #  offenforce_gtid_consistency    =on            #  off####: for replicationmaster_info_repository      =table            #  filerelay_log_info_repository    =table            #  file####: for group replicationtransaction_write_set_extraction  =XXHASH64          #  offloose-group_replication_group_name  ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"    #  loose-group_replication_start_on_boot  =off            #  offloose-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 innodbdefault_storage_engine      =innodb            #  innodbdefault_tmp_storage_engine    =innodb            #  innodbinnodb_data_file_path      =ibdata1:12M:autoextend        #  ibdata1:12M:autoextendinnodb_temp_data_file_path    =ibtmp1:12M:autoextend        #  ibtmp1:12M:autoextendinnodb_log_group_home_dir    =./            #  ./innodb_log_files_in_group    =2            #  2innodb_log_file_size      =48M            #  50331648(48M)innodb_file_format      =Barracuda          #  Barracudainnodb_file_per_table      =on             #  oninnodb_page_size      =16k            #  16384(16k)innodb_thread_concurrency    =0            #  0innodb_read_io_threads      =4            #  4innodb_write_io_threads      =4            #  4innodb_purge_threads      =4            #  4innodb_print_all_deadlocks    =on            #  offinnodb_deadlock_detect      =on            #  oninnodb_lock_wait_timeout    =50            #  50innodb_spin_wait_delay      =6            #  6innodb_autoinc_lock_mode    =2            #  1innodb_stats_persistent      =on            #  oninnodb_stats_persistent_sample_pages  =20            #  20innodb_adaptive_hash_index    =on            #  oninnodb_change_buffering      =all            #  allinnodb_change_buffer_max_size    =25            #  25innodb_flush_neighbors      =1            #  1innodb_flush_method      =O_DIRECT          #  innodb_doublewrite      =on            #  oninnodb_log_buffer_size      =16M            #  16777216(16M)innodb_flush_log_at_timeout    =1            #  1innodb_flush_log_at_trx_commit    =1            #  1autocommit        =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 the user set SQL _log_bin = 0; create user rpl_user @ '%' identified by '123'; grant replication slave, replication client on *. * to rpl_user @ '%'; create user rpl_user @ '127. 0.0.1 'identified by '20140901'; grant replication slave, replication client on *. * to rpl_user @ '127. 0.0.1 '; create user rpl_user @ 'localhost' identified by '000000'; grant replication slave, replication client on *. * to rpl_user @ 'localhost'; set SQL _log_bin = 1; -- add the copy credential change master to master_user = 'rpl _ user ', master_password = '000000' for channel' group _ replication_recovery '; -- install the group copy object 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 -- added the user set SQL _log_bin = 0; create user rpl_user @ '%' identified by '123'; grant replication slave, replication client on *. * to rpl_user @ '%'; create user rpl_user @ '127. 0.0.1 'identified by '20140901'; grant replication slave, replication client on *. * to rpl_user @ '127. 0.0.1 '; create user rpl_user @ 'localhost' identified by '000000'; grant replication slave, replication client on *. * to rpl_user @ 'localhost'; set SQL _log_bin = 1; -- add the copy credential change master to master_user = 'rpl _ user ', master_password = '000000' for channel' group _ replication_recovery '; -- install the group copy object install plugin group_replication soname' group _ replication. so '; -- start group replication start group_replication; # note that this is not initialization. You only need to add it.

[6] the operations on the 6606 instance are the same as those on the 5506 instance, so that the configuration of group replication is complete.

The above MySQL-group-replication configuration step (recommended) is all the content shared by the editor. I hope to give you a reference and support for the customer's house.