mysql replication配置_MySQL-group-replication 配置步骤(推荐)

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 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 内容如下：

[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 内容如下：

[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】初始化三个数据库实例

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 配置步骤(推荐)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持脚本之家。