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/mysqldVer5.7.17forlinux-glibc2.5onx86_64(MySQLCommunityServer(GPL))
【2】实验环境为一台主机上安装3台mysql,它们三个组成一个group-replication组
/tmp/4406.cnf内容如下:
[mysqld] ####:forglobal 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 ####:forbinlog 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 ####:forerror-log log_error=mysql-err.log#/usr/local/mysql/data/localhost.localdomain.err ####:forslowquerylog ####:forgtid gtid_mode=on#off enforce_gtid_consistency=on#off ####:forreplication master_info_repository=table#file relay_log_info_repository=table#file ####:forgroupreplication 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 ####:forinnodb 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] ####:forglobal 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 ####:forbinlog 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 ####:forerror-log log_error=mysql-err.log#/usr/local/mysql/data/localhost.localdomain.err ####:forslowquerylog ####:forgtid gtid_mode=on#off enforce_gtid_consistency=on#off ####:forreplication master_info_repository=table#file relay_log_info_repository=table#file ####:forgroupreplication 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 ####:forinnodb 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] ####:forglobal 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 ####:forbinlog 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 ####:forerror-log log_error=mysql-err.log#/usr/local/mysql/data/localhost.localdomain.err ####:forslowquerylog ####:forgtid gtid_mode=on#off enforce_gtid_consistency=on#off ####:forreplication master_info_repository=table#file relay_log_info_repository=table#file ####:forgroupreplication 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 ####:forinnodb 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 --增加用户 setsql_log_bin=0; createuserrpl_user@'%'identifiedby'123456'; grantreplicationslave,replicationclienton*.*torpl_user@'%'; createuserrpl_user@'127.0.0.1'identifiedby'123456'; grantreplicationslave,replicationclienton*.*torpl_user@'127.0.0.1'; createuserrpl_user@'localhost'identifiedby'123456'; grantreplicationslave,replicationclienton*.*torpl_user@'localhost'; setsql_log_bin=1; --增加复制凭证 changemasterto master_user='rpl_user', master_password='123456' forchannel'group_replication_recovery'; --安装组复制物件 installplugingroup_replicationsoname'group_replication.so'; --启动组复制 setglobalgroup_replication_bootstrap_group=on; startgroup_replication; setglobalgroup_replication_bootstrap_group=off;
【5】5506实例的配置过程如下:
/usr/local/mysql/bin/mysqld--defaults-file=/tmp/5506.cnf&
mysql-h127.0.0.1-uroot-P5506 --增加用户 setsql_log_bin=0; createuserrpl_user@'%'identifiedby'123456'; grantreplicationslave,replicationclienton*.*torpl_user@'%'; createuserrpl_user@'127.0.0.1'identifiedby'123456'; grantreplicationslave,replicationclienton*.*torpl_user@'127.0.0.1'; createuserrpl_user@'localhost'identifiedby'123456'; grantreplicationslave,replicationclienton*.*torpl_user@'localhost'; setsql_log_bin=1; --增加复制凭证 changemasterto master_user='rpl_user', master_password='123456' forchannel'group_replication_recovery'; --安装组复制物件 installplugingroup_replicationsoname'group_replication.so'; --启动组复制 startgroup_replication;#注意这里不是初始化了,只要加入就行
【6】6606实例的操作与5506的操作一样,这样groupreplication的配置就完成了。
以上这篇MySQL-group-replication配置步骤(推荐)就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持毛票票。