MySQL Group Replication-现有普通复制集群提升为Group Replication
来源:互联网 发布:2016党规知识网络测试 编辑:程序博客网 时间:2024/05/23 13:34
上一篇测试了各种场景下,向Group Replication中添加新节点。那么,如果原来有一套master-slave集群,需要怎么把它改造成Group Replication集群呢?
Group Replication是基于GTID来复制的,那么第一步就是需要将普通复制改造成GTID复制。有了GTID那么将Master-Slave结构改造成Group Replication就很方便了。
搭建普通复制环境
解压安装包
[root@mser02 local]# tar -zxvf mysql-5.7.15-labs-gr090-linux-glibc2.5-x86_64.tar.gz[root@mser02 local]# mv mysql-5.7.15-labs-gr090-linux-glibc2.5-x86_64 mysql5.7[root@mser02 local]# cd mysql5.7/
初始化数据
[server01]
./bin/mysqld --no-defaults --user=mysql --initialize --explicit_defaults_for_timestamp --basedir=/usr/local/mysql5.7/ --datadir=/usr/local/mysql5.7/data_1
[server02]
./bin/mysqld --no-defaults --user=mysql --initialize --explicit_defaults_for_timestamp --basedir=/usr/local/mysql5.7/ --datadir=/usr/local/mysql5.7/data_2
[server03]
./bin/mysqld --no-defaults --user=mysql --initialize --explicit_defaults_for_timestamp --basedir=/usr/local/mysql5.7/ --datadir=/usr/local/mysql5.7/data_3
编辑配置文件
[server1]
my_1.cnf
[mysqld]server-id = 1basedir=/usr/local/mysql5.7/datadir=/usr/local/mysql5.7/data_1user=mysqlexplicit_defaults_for_timestampsocket=mysql1.sockport = 3306# binloglog-bin=mysql-binbinlog-format = ROWlog-slave-updates = ONmaster-info-repository = TABLErelay-log-info-repository = TABLEbinlog-checksum = NONEslave-parallel-workers = 0
[server2]
my_2.cnf
[mysqld]server-id = 2basedir=/usr/local/mysql5.7/datadir=/usr/local/mysql5.7/data_2user=mysqlexplicit_defaults_for_timestampsocket=mysql2.sockport = 3307# binloglog-bin=mysql-binbinlog-format = ROWlog-slave-updates = ONmaster-info-repository = TABLErelay-log-info-repository = TABLEbinlog-checksum = NONEslave-parallel-workers = 0
[server3]
my_3.cnf
[mysqld]server-id = 3basedir=/usr/local/mysql5.7/datadir=/usr/local/mysql5.7/data_3user=mysqlexplicit_defaults_for_timestampsocket=mysql3.sockport = 3308# binloglog-bin=mysql-binbinlog-format = ROWlog-slave-updates = ONmaster-info-repository = TABLErelay-log-info-repository = TABLEbinlog-checksum = NONEslave-parallel-workers = 0
mysql> CREATE USER rpl_user@'%';Query OK, 0 rows affected (0.03 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';Query OK, 0 rows affected, 1 warning (0.00 sec)
启动mysql
[server01]
./bin/mysqld --defaults-file=/usr/local/mysql5.7/my_1.cnf
[server02]
./bin/mysqld --defaults-file=/usr/local/mysql5.7/my_2.cnf
[server03]
./bin/mysqld --defaults-file=/usr/local/mysql5.7/my_3.cnf
修改初始密码
[server01]
./bin/mysqladmin -u root -h 127.0.0.1 -P 3306 -p password "111111"
[server02]
./bin/mysqladmin -u root -h 127.0.0.1 -P 3307 -p password "111111"
[server03]
./bin/mysqladmin -u root -h 127.0.0.1 -P 3308 -p password "111111"
组建集群
首先在[server01]中进行如下配置:
CREATE USER rpl_user@'%';GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';FLUSH PRIVILEGES;
再在其他两个节点执行如下命令
mysql> change master to master_host='127.0.0.1',master_port=3306,master_user='rpl_user',master_password='rpl_pass',master_log_file='mysql-bin.000001',master_log_pos=386;Query OK, 0 rows affected, 2 warnings (0.14 sec)mysql> start slave;Query OK, 0 rows affected (0.01 sec)mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: rpl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 859 Relay_Log_File: mser01-relay-bin.000002 Relay_Log_Pos: 785 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes
这样普通Master-Slave结构就完成了
进行测试
Master节点上执行
mysql> create database bobo;Query OK, 1 row affected (0.01 sec)mysql> use bobo;Database changedmysql> create table test1 (id int primary key,name varchar(20));Query OK, 0 rows affected (0.19 sec)mysql> insert into test1(id,name) values (1,'aaaa');Query OK, 1 row affected (0.03 sec)mysql> insert into test1(id,name) values (2,'bbbb');Query OK, 1 row affected (0.05 sec)mysql> insert into test1(id,name) values (3,'cccc');Query OK, 1 row affected (0.03 sec)mysql> insert into test1(id,name) values (4,'dddd');Query OK, 1 row affected (0.03 sec)mysql> select * from test1;+----+------+| id | name |+----+------+| 1 | aaaa || 2 | bbbb || 3 | cccc || 4 | dddd |+----+------+4 rows in set (0.00 sec)
Slave节点执行
mysql> use bobo;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from test1;+----+------+| id | name |+----+------+| 1 | aaaa || 2 | bbbb || 3 | cccc || 4 | dddd |+----+------+4 rows in set (0.00 sec)
转换成GTID复制
MySQL 5.7.6以后的版本可以在线进行GTID的切换,但是要保证所有组中成员gtid_mode 为off状态。
实现步骤:
所有的Server执行
set @@global.enforce_gtid_consistency = warn;
特别注意: 这一步是关建的一步使用不能出现警告。
所有的server上执行:
set @@global.enforce_gtid_consistency = on;
所有的Server上执行(不关心最先最后,但要执行完):
set @@global.gtid_mode = off_permissive;
执行:
set @@global.gtid_mode=on_permissive;
实质在这一步骤生的日志都是带GTID的日志了,这个步骤号称是不关心任何节点,但从实管理上推荐在slave上先执行,然后再去master上执行。
确认传统的binlog复制完毕,该值为0
show status like ‘ongoing_anonymous_transaction_count’;
需要所有的节点都确认为0.
所有节点进行判断
show status like ‘ongoing_anonymous_transaction_count’;
为零
所有的节点也可以执行一下: flush logs; 用于切换一下日志。
所有的节点启用gtid_mode
set @@global.gtid_mode=on
把gtid_mode = on相关配置写入配置文件
gtid_mode=onenforce_gtid_consistency=on
启用Gtid的自动查找节点复制:
stop slave;change master to master_auto_position=1;start slave;
这样普通的Master-Slave就在线的切换成了基于GTID复制了。
注:能够进行在线的GTID的切换,得益于mysql5.7.6以后的版本中,gtid_mode出现了两个新的状态OFF_PERMISSIVE、ON_PERMISSIVE,以及enforce_gtid_consistency中新增Warn状态
enforce_gtid_consistency
Warn :当检测到不支持GTID的语句和事务,返回警告,并在日志中记录
gtid_mode
- OFF_PERMISSIVE: New transactions are anonymous. Replicated transactions can be eitheranonymous or GTID transactions.
ON_PERMISSIVE: New transactions are GTID transactions. Replicated transactions can be either anonymous or GTID transactions.
OFF_PERMISSIVE :不产生GTID,Slave即接受不带GTID的事务,也接受带GTID的事务
- ON_PERMISSIVE :产生GTID,Slave即接受不带GTID的事务,也接受带GTID的事务
有了不支持GTID事务的检查功能,也有了即支持GTID复制也支持普通复制的过度状态,这样就可以在线平滑的将Master-Slave普通复制切换成GTID复制。
此步骤参考:
http://wubx.net/mysql-5-7-class-repl-online-2-gtid-repl/
开启Group Replication
修改成GTID模式之后,接下来切换成Group Replication就方便了。
安装Group Replicaiton插件
在全部server上执行
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';Query OK, 0 rows affected (0.03 sec)mysql> show plugins;+----------------------------+----------+--------------------+----------------------+---------+| Name | Status | Type | Library | License |+----------------------------+----------+--------------------+----------------------+---------+| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |…………| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |+----------------------------+----------+--------------------+----------------------+---------+45 rows in set (0.00 sec)
配置参数
[server1]
mysql> set @@global.transaction_write_set_extraction = XXHASH64mysql> set @@global.group_replication_start_on_boot = OFFmysql> set @@global.group_replication_bootstrap_group = OFF mysql> set @@global.group_replication_group_name = 0c6d3e5f-90e2-11e6-802e-842b2b5909d6mysql> set @@global.group_replication_local_address = '127.0.0.1:6606'mysql> set @@global.group_replication_group_seeds = '127.0.0.1:6607,127.0.0.1:6608'
[server2]
mysql> set @@global.transaction_write_set_extraction = XXHASH64mysql> set @@global.group_replication_start_on_boot = OFFmysql> set @@global.group_replication_bootstrap_group = OFF mysql> set @@global.group_replication_group_name = 0c6d3e5f-90e2-11e6-802e-842b2b5909d6mysql> set @@global.group_replication_local_address = '127.0.0.1:6607'mysql> set @@global.group_replication_group_seeds = '127.0.0.1:6606,127.0.0.1:6608'
[server3]
mysql> set @@global.transaction_write_set_extraction = XXHASH64mysql> set @@global.group_replication_start_on_boot = OFFmysql> set @@global.group_replication_bootstrap_group = OFF mysql> set @@global.group_replication_group_name = 0c6d3e5f-90e2-11e6-802e-842b2b5909d6mysql> set @@global.group_replication_local_address = '127.0.0.1:6608'mysql> set @@global.group_replication_group_seeds = '127.0.0.1:6607,127.0.0.1:6606'
==特别注意==:如果之前没有配置transaction_write_set_extraction=XXHASH64,这里修改之后之前创建的数据库是没有办法执行插入操作的。所有如果想在线完成Group Replication的改造需要保证之前已经设置了transaction_write_set_extraction=XXHASH64。
构建集群
[server1]
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
注:在普通Master-Slave中已经配置过复制用户了,所以在构建Group Replicaiton时不需要再重新创建了。
开启group_replication
SET GLOBAL group_replication_bootstrap_group=ON;START GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=OFF;
其他节点加入group_replication集群
stop slave;START GROUP_REPLICATION;
在查看节点信息
mysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 0aab37dc-911c-11e6-ab03-842b2b5909d6 | mser01 | 3308 | ONLINE || group_replication_applier | a876d35e-9110-11e6-a365-842b2b5909d6 | mser01 | 3306 | ONLINE || group_replication_applier | b34df071-911a-11e6-9796-842b2b5909d6 | mser01 | 3307 | ONLINE |+---------------------------+--------------------------------------+-------------+-------------+--------------+3 rows in set (0.00 sec)
这样普通Master-Slave集群改造成Group Replication集群就完成了。
- MySQL Group Replication-现有普通复制集群提升为Group Replication
- mysql group replication集群搭建
- Mysql group replication复制原理
- MySQL组复制(Group Replication)
- MySQL Group Replication初测
- MySQL Group Replication 介绍
- MySQL Group Replication实践
- MySQL Group Replication 介绍
- MySQL Group Replication简介
- MySQL Group Replication 介绍
- Mysql Group Replication
- MySQL Group Replication 介绍
- MySQL Group Replication增加节点
- MySQL Group Replication 正式发布
- MySQL Group Replication 技术点
- MySQL Group Replication调研剖析
- MySQL Group Replication调研剖析
- MySQL Group Replication调研剖析
- 更新(嵌套子查询)
- 虚幻4漏光问题解决方法
- Android 获取系统prop
- 搜索进阶 hdu2181 回溯
- keil5安装stm32测试
- MySQL Group Replication-现有普通复制集群提升为Group Replication
- pod更新语句
- git 命令『添加』 and 『删除』 子模块
- Qt中Ui名字空间以及setupUi函数的原理和实现
- 软件程序员,如何学习人工智能?
- Android textView 动态设置代码字号大小,支持单位选项 dp,sp or px
- oracle里面delete,trancate,drop的区别
- JavaScript 基础
- python 脚本 .py文件执行