MySQL-group-replication設(shè)置步驟的示例代碼分享
發(fā)表時間：2023-09-10 來源：明輝站整理相關(guān)軟件相關(guān)文章人氣：
[摘要]下面小編就為大家?guī)硪黄狹ySQL-group-replication 配置步驟(推薦)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧MySQL-Group-Replication 是mysql-5.7.17版本開發(fā)出來的新特性；它在master-slave 之間實...
下面小編就為大家?guī)硪黄?a target="_blank">MySQL-group-replication 配置步驟(推薦)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
MySQL-Group-Replication 是mysql-5.7.17版本開發(fā)出來的新特性；它在master-slave 之間實現(xiàn)了強一致性，
但是就目前來說主要是性能不太好。
【1】確定當前的mysql數(shù)據(jù)庫版本為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】實驗環(huán)境為一臺主機上安裝3臺mysql，它們?nèi)齻�組成一個group-replication 組
/tmp/4406.cnf 內(nèi)容如下：
[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/5506.cnf 內(nèi)容如下：
[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 內(nèi)容如下：
[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】初始化三個數(shù)據(jù)庫實例
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配置步驟的示例代碼分享的詳細內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！
學習教程快速掌握從入門到精通的SQL知識。
