MySQL-group-replication 配置步骤(推荐)

数据库  /  管理员 发布于 6年前   187

MySQL-Group-Replication 是mysql-5.7.17版本开发出来的新特性；它在master-slave 之间实现了强一致性，

但是就目前来说主要是性能不太好。

【1】确定当前的mysql数据库版本为5.7.17及以上

/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】实验环境为一台主机上安装3台mysql，它们三个组成一个group-replication 组

/tmp/4406.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】初始化三个数据库实例

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】配置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】5506 实例的配置过程如下：

/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】6606 实例的操作与5506的操作一样，这样group replication 的配置就完成了。

以上这篇MySQL-group-replication 配置步骤(推荐)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持。