背景:
4 模拟数据库down机
至此,数据库集群组搭建完毕!!!!未完待续!!!!
性能测试参考姜老师博客:
http://www.innomysql.com/article/25840.html
实现原理与维护可参考acumg博客
http://mp.weixin.qq.com/s/pBAOXW7Kx_EtdRVe4VaCYg
参考:
http://dev.mysql.com/doc/refman/5.7/en/group-replication-adding-instances.html
- 环境
- host dbport
- 192.168.1.247 3307
192.168.1.14 24802
192.168.1.242 3307
1.配置hosts信息
- 192.168.1.247 sh247
- 192.168.1.14 interface.test.haodai.com
- 192.168.1.242 sh242
2 第一台数据库操作
- 2.1修改配置文件
- [client]
port = 3307
socket = /home/data/mydata/3307/mysql.sock
[mysqld]
port = 3307
socket = /home/data/mydata/3307/mysql.sock
basedir = /home/data/mysql
datadir = /home/data/mydata/3307
pid-file = /home/data/mydata/3307/mysql.pid
##group replication####
server_id = 1
gtid_mode =ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository=TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name = "77e497e2-c59a-11e6-9a68-525400159185"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.1.247:34061"
loose-group_replication_group_seeds="192.168.1.247:34061,192.168.1.14:34062,192.168.1.242:34063"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=false
loose-group_replication_enforce_update_everywhere_checks=TRUE
2.2 启动数据库进行配置
- SET SQL_LOG_BIN=0;
- CREATE USER rpl_user@'%';
- GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
- SET SQL_LOG_BIN=1;
- CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery'
2.3 安装引擎
-
- mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so'
-
mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+-------------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+-------------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY | (...) | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | PROPRIETARY | +----------------------------+----------+--------------------+----------------------+-------------+
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 550a7cbd-ccce-11e6-829a-a2d813521b2e | sh247 | 3307 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
-
2.4插入一些模拟数据
- mysql> CREATE DATABASE test;
- Query OK, 1 row affected (0.00 sec)
-
- mysql> use test;
- Database changed
- mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
- Query OK, 0 rows affected (0.00 sec)
-
- mysql> INSERT INTO t1 VALUES (1, 'Luis');
- Query OK, 1 row affected (0.01 sec)
3 往集群里面添加数据库实例
- 3.1修改第二台数据库配置文件
-
- [client]
- port = 24802
- socket = /home/data/mydata/3307/mysql.sock
-
-
- [mysqld]
- port = 24802
- socket = /home/data/mydata/3307/mysql.sock
- basedir = /home/data/mysql
- datadir = /home/data/mydata/3307
- pid-file = /home/data/mydata/3307/mysql.pid
- ##group replication####
- server_id = 2
- gtid_mode =ON
- enforce_gtid_consistency = ON
- master_info_repository = TABLE
- relay_log_info_repository=TABLE
- binlog_checksum = NONE
- log_slave_updates = ON
- log_bin=binlog
- binlog_format=ROW
-
- transaction_write_set_extraction=XXHASH64
- loose-group_replication_group_name = "77e497e2-c59a-11e6-9a68-525400159185"
- loose-group_replication_start_on_boot=off
- loose-group_replication_local_address="192.168.1.14:34062"
- loose-group_replication_group_seeds="192.168.1.247:34061,192.168.1.14:34062,192.168.1.242:34063"
- loose-group_replication_bootstrap_group=off
- loose-group_replication_single_primary_mode=false
- loose-group_replication_enforce_update_everywhere_checks=TRUE
- [client]
- 3.2启动数据库进行配置
- SET SQL_LOG_BIN=0;
- CREATE USER rpl_user@'%';
- GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
- SET SQL_LOG_BIN=1;
- CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery'
- SET SQL_LOG_BIN=0;
-
- 3.3 安装引擎,加入gp组
- mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
- mysql> START GROUP_REPLICATION;
- mysql> SELECT * FROM performance_schema.replication_group_members;
- +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
- | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
- +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
- | group_replication_applier | 29a03be3-cccf-11e6-9dac-00163e2d2c29 | interface.test.haodai.com | 24802 | ONLINE |
- | group_replication_applier | 550a7cbd-ccce-11e6-829a-a2d813521b2e | sh247 | 3307 | ONLINE |
- +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
- 注意:与2操作相比少了两个步骤,千万别执行那两个步骤!那两个步骤是在搭建gp 初始化才需要执行的两个步骤
- mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
- 3.4检验数据
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | sys |
- | test |
- +--------------------+
- 5 rows in set (0.00 sec)
-
- mysql> select * from test.t1;
- +----+------+
- | c1 | c2 |
- +----+------+
- | 1 | Luis |
- +----+------+
- 1 row in set (0.00 sec)
- mysql> show databases;
4 模拟数据库down机
- 关闭247数据库
- mysql> SELECT * FROM performance_schema.replication_group_members;
- +---------------------------+-----------+-------------+-------------+--------------+
- | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
- +---------------------------+-----------+-------------+-------------+--------------+
- | group_replication_applier | | | NULL | OFFLINE |
- +---------------------------+-----------+-------------+-------------+--------------+
- 1 row in set (0.00 sec)
-
- mysql> start GROUP_REPLICATION; ###重新打开即可
- Query OK, 0 rows affected (3.50 sec)
-
- mysql> SELECT * FROM performance_schema.replication_group_members;
- +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
- | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
- +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
- | group_replication_applier | 29a03be3-cccf-11e6-9dac-00163e2d2c29 | interface.test.haodai.com | 24802 | ONLINE |
- | group_replication_applier | 550a7cbd-ccce-11e6-829a-a2d813521b2e | sh247 | 3307 | ONLINE |
- +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
- 2 rows in set (0.00 sec)
-
- mysql> stop GROUP_REPLICATION;
- Query OK, 0 rows affected (8.47 sec)
-
- mysql> SELECT * FROM performance_schema.replication_group_members;
- +---------------------------+--------------------------------------+-------------+-------------+--------------+
- | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
- +---------------------------+--------------------------------------+-------------+-------------+--------------+
- | group_replication_applier | 550a7cbd-ccce-11e6-829a-a2d813521b2e | sh247 | 3307 | OFFLINE |
- +---------------------------+--------------------------------------+-------------+-------------+--------------+
- 1 row in set (0.00 sec)
-
- mysql> start GROUP_REPLICATION;
- Query OK, 0 rows affected (5.49 sec)
-
- mysql> SELECT * FROM performance_schema.replication_group_members;
- +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
- | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
- +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
- | group_replication_applier | 29a03be3-cccf-11e6-9dac-00163e2d2c29 | interface.test.haodai.com | 24802 | ONLINE |
- | group_replication_applier | 550a7cbd-ccce-11e6-829a-a2d813521b2e | sh247 | 3307 | ONLINE |
- +---------------------------+--------------------------------------+---------------------------+-------------+--------------+
- 2 rows in set (0.00 sec)
至此,数据库集群组搭建完毕!!!!未完待续!!!!
性能测试参考姜老师博客:
http://www.innomysql.com/article/25840.html
实现原理与维护可参考acumg博客
http://mp.weixin.qq.com/s/pBAOXW7Kx_EtdRVe4VaCYg
参考:
http://dev.mysql.com/doc/refman/5.7/en/group-replication-adding-instances.html
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29096438/viewspace-2131537/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29096438/viewspace-2131537/