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 配置步骤(推荐)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持我们。
相关推荐
-
mysql5.5 master-slave(Replication)配置方法
一主一从: Master: OS:centos release 5.6 DB:mysql 5.5.8 IP:192.168.1.2 Slave: OS:centos release 5.6 DB:mysql 5.5.8 IP:192.168.1.3 修改主机Master配置文件 (/etc/my.cnf) 复制代码 代码如下: [mysqld] //至少要有server-id.与log-bin两项 server-id=1 log-bin=/var/lib/mysql/mysql-bin data
-
基于mysql replication的问题总结
接触replication后,就开始琢磨自己要配置下试试,固然出现了很多问题:经过上网查资料也好问同学也好,终于是解决了些.现在就分享下自己的经历: 首先的问题是配置master的my.cnf,按部就班的配置 复制代码 代码如下: server-id = 1 log_bin =mysql-bin #log_bin = /var/log/mysql/mysql-bin.log expir
-
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
-
Suse Linux 10中MySql安装与配置步骤
安装环境: 操作系统:suse Linux 10 数据库:MySQL 5.0.22 希望能认识更多高手,互相学习讨论技术! 首先发泄一下再这,网上看了N多安装mysql的文章,大多大同小异,都不能很好的安装mysql:多多少少都有问题:以个人之见大多为一份原文,拷贝到各自的博客或一些网上,纯属盗窃啊!上周末开始筹划安装suse以及mysql,一直到今天下午才终于到了show databases:太晕了!现在我将我安装mysql的每一步骤都放在这,以共需者查看,也以防日后自己再次观摩: 注意:在装
-
Mybatis-Spring连接mysql 8.0配置步骤出错的解决方法
本文为大家解决了Mybatis-Spring 连接 MySQL8.0 的配置步骤出错问题,供大家参考,具体内容如下 环境以及配置文件 JDBC jar版本 : 8.0.11 Mybatis jar版本 : 3.4.6 Spring jar版本 : 4.3.18 Mybatis-Spring jar版本 : 1.3.1 配置信息文件 : db.properties Spring配置文件 : applicationContext.xml 测试路径如下图 配置db.properties 配置db.pr
-
win2003 mysql单向同步配置步骤[已测]
前提条件,两台服务器都安装了mysql相同的版本,数据库名也一样,最好数据都是尽量的差不多. mysql服务器端 192.168.0.1: 新建一个备用账号,只用来复制数据的 用户名:backup 密码:wwwjb51net GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup@'192.168.0.2' IDENTIFIED BY 'wwwjb51net'; 新建用户后需要客户端来检测在客户端是否能连接服务器端. 在[mysqld]下面添加
-
MySQL主从同步、读写分离配置步骤
现在使用的两台服务器已经安装了MySQL,全是rpm包装的,能正常使用. 为了避免不必要的麻烦,主从服务器MySQL版本尽量保持一致; 环境:192.168.0.1 (Master) 192.168.0.2 (Slave) MySQL Version:Ver 14.14 Distrib 5.1.48, for pc-linux-gnu (i686) using readline 5.1 1.登录Master服务器,修改my.cnf,添加如下内容: server-id = 1 //数据库ID号,
-
Mysql双主配置的详细步骤
目录 前言 一.mysql配置文件 (1)节点A配置 (2)节点B配置 二.配置节点A为节点B的master(主从模式) 三.完成双主配置 四.测试 五.控制同步的库或表 六.一个账号多个IP 总结 前言 特点:在双主配置中,两台mysql互为主从节点.节点A是节点B的master,同时节点B也是节点A的master. 安装mysql步骤略过 一.mysql配置文件 (1)节点A配置 # 设置server-id,两节点必须不一样 server-id = 100 # 开启bin_log,模式为RO
-
MySQL 主主同步配置步骤
MySQL 主主同步配置 服务器名 IP 系统 MySQL odd.example.com 192.168.1.116 rhel-5.8 5.5.16 even.example.com 192.168.1.115 rhel-5.8 5.5.16假设要同步的库是 db_rocky ㈠ 创建同步用户 在 ODD上 复制代码 代码如下: mysql> grant replication slave on *.* to 'water'@'192.168.1.115' identified by 'cdi
-
windows8.1下Apache+Php+MySQL配置步骤
软件版本: apache:Apache 2.4.6 Win64 PHP:PHP 5.5 VC11 x64 Non Thread Safe MySql:5.5 win64 Mysql安装: 双击运行,设置用户名.密码.编码(utf8). PHP配置: 1.解压下载的zip文件到一个目录,本教程中为:D:\server\php 2.复制文件php.ini-development并改名为php.ini: 3.在php.ini文件中搜索关键字php_mysqli.dll,去掉该行前面的";&qu
-
MYSQL ZIP免安装版配置步骤及图形化管理工具mysql-workbench
1. 解压ZIP包和配置 首先,将mysql-5.5.25-winx64.zip 解压缩到D:/mysql-5.5.25 目录下,然后根据网上相关资料,将my-large.ini(当然也可以视情况选择别的ini配置文件) 复制后改名为my.ini,在此基础上,主要是在[mysqld]段中添加了: 复制代码 代码如下: #系统基本目录basedir= D:/mysql-5.5.25#用户数据目录datadir= D:/mysql-5.5.25/data#字符集设置character-set-ser
-
MySQL 5.7 zip版本(zip版)安装配置步骤详解
前言:重装win10顺带整理文件,然而装个MySQL用了我一下午,真是不值,所以写这篇来帮助大家. 一.下载 https://dev.mysql.com/downloads/mysql/ 二.解压缩zip包 三.配置环境变量 (1)配置Path (2)修改mysql-default.ini配置文件 原先的: # These are commonly set, remove the # and set as required.# basedir = .....# datadir = .....#