Home  >  Article  >  Database  >  Sample code sharing for MySQL-group-replication configuration steps

Sample code sharing for MySQL-group-replication configuration steps

黄舟
黄舟Original
2017-03-20 13:47:301539browse

The editor below will bring you an article on MySQL-group-replication configuration steps (recommended). The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor to take a look.

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

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

【1】Make sure the current mysql database version is 5.7.17 and 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】Experiment The environment is to install three mysqls on one host. The three of them form a group-replication group

/tmp/4406.cnf. The content 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

/tmp The content of /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

/tmp/6606.cnf The content 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】Configuring the initial instance of group-replication

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

-- 增加用户
    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;

-- 增加复制凭证
    change master to 
      master_user='rpl_user',
      master_password='123456'
      for channel 'group_replication_recovery';

-- 安装组复制物件
    install plugin group_replication soname 'group_replication.so';

-- 启动组复制
    set global group_replication_bootstrap_group=on;
    start group_replication;
    set global group_replication_bootstrap_group=off;

【5】Configuration process of 5506 instance As follows:

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

-- 增加用户
    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;

-- 增加复制凭证
    change master to 
      master_user='rpl_user',
      master_password='123456'
      for channel 'group_replication_recovery';

-- 安装组复制物件
    install plugin group_replication soname 'group_replication.so';

-- 启动组复制
    start group_replication; # 注意这里不是初始化了,只要加入就行

[6] The operation of 6606 instance is the same as that of 5506, so the configuration of group replication is completed.

The above is the detailed content of Sample code sharing for MySQL-group-replication configuration steps. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn