当前位置: 首页 > 编程笔记 >

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

邹齐智
2023-03-14
本文向大家介绍MySQL-group-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 配置步骤(推荐)就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持小牛知识库。

 类似资料:
  • 本文向大家介绍MySQL解压版配置步骤详细教程,包括了MySQL解压版配置步骤详细教程的使用技巧和注意事项,需要的朋友参考一下 mysql-5.7.14-winx64\bin配置到Path中 在解压路径下复制my-default.ini,修改名称为my.ini 在my.ini添加如下 basedir:是上述mysql的解压路径 datadir:后续初始化等数据都会保存在该目录下,在该文件目录下新建

  • 本文向大家介绍Ubuntu 18.04配置mysql以及配置远程连接的步骤,包括了Ubuntu 18.04配置mysql以及配置远程连接的步骤的使用技巧和注意事项,需要的朋友参考一下 前言 本文主要跟大家分享了关于Ubuntu 18.04配置mysql及配置远程连接的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧 配置教程如下: 首先是大家都知道的老三套,啥也不说上来就放

  • python-mysql-replication 是一个纯 Python 实现的 MySQL 复制协议,基于 PyMYSQL 构建,可以让你接收到注入插入、更新和删除数据的事件。 使用场景:   MySQL 到 NoSQL 的数据库复制 MySQL 到搜索引擎的复制 数据库数据更改后清除缓存数据 数据审计 实时分析 项目要求: MySQL 5.5 and 5.6 Python 2.7 Python 3.2

  • 本文向大家介绍docker完整配置nginx+php+mysql的方法步骤,包括了docker完整配置nginx+php+mysql的方法步骤的使用技巧和注意事项,需要的朋友参考一下 首先了解一个方法: 使用docker exec进入Docker容器 docker在1.3.X版本之后还提供了一个新的命令exec用于进入容器,这种方式相对更简单一些,下面我们来看一下该命令的使用: 接下来我们使用该命

  • 本文向大家介绍linux下vsftpd的安装及配置使用详细步骤(推荐),包括了linux下vsftpd的安装及配置使用详细步骤(推荐)的使用技巧和注意事项,需要的朋友参考一下 vsftpd 是“very secure FTP daemon”的缩写,安全性是它的一个最大的特点。 vsftpd 是一个 UNIX 类操作系统上运行的服务器的名字,它可以运行在诸如 Linux、BSD、Solaris、 H

  • 本文向大家介绍MySQL 5.7 zip版本(zip版)安装配置步骤详解,包括了MySQL 5.7 zip版本(zip版)安装配置步骤详解的使用技巧和注意事项,需要的朋友参考一下 前言:重装win10顺带整理文件,然而装个MySQL用了我一下午,真是不值,所以写这篇来帮助大家。 一、下载 https://dev.mysql.com/downloads/mysql/ 二、解压缩zip包 三、配置环境