mysql单机多实例GTID_MySQL Group Replication单机多实例安装配置

1、 创建三个数据库实例

mkdir /data/mysql/sock

mkdir –p /data/mysql/data/{s1,s2,s3}

mkdir /data/mysql/cnf

mkdir –p /data/mysql/logs/{s1,s2,s3}

cd /data/mysql

chown -R mysql:mysql *

cd /usr/local/mysql/

chown -R mysql:mysql *

bin/mysqld --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/mysql/data/s1

bin/mysqld --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/mysql/data/s2

bin/mysqld --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/mysql/data/s3

2、 配置第一个节点

[mysqld]

#Server Configuration

basedir=/usr/local/mysql

datadir=/data/mysql/data/s1

port = 24801

socket = /data/mysql/sock/s1.sock

#Replication Framework

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=/data/mysql/logs/s1/binlog

binlog_format=ROW

relay-log=/data/mysql/logs/s1/relay-bin

#Group Replication

transaction_write_set_extraction=XXHASH64

loose- group_replication_group_name="b4668cea-d7ca-11e6-86b5-18a99b76310d"

loose- group_replication_start_on_boot=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

loose- group_replication_single_primary_mode=FALSE

loose- group_replication_enforce_update_everywhere_checks= TRUE

参数配置中除了如下参数在各节点不一样外，其他配置都一样：datadir、port、socket、server_id、log_bin、relay-log、loose- group_replication_local_address

3、启动第一个节点

cd /usr/local/mysql/

sudo chown -R mysql:mysql *

ln -s /usr/local/mysql/bin/mysql /usr/bin

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/cnf/s1.cnf

4、登录第一个节点

mysql -h127.0.0.1 -P24801 -uroot --skip-password

#登录后修改默认密码

SET PASSWORD = PASSWORD('root');

flush privileges;

5、创建group replication所需要的用户

SET SQL_LOG_BIN=0;

CREATE USER rpl_user@'%';

GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';

FLUSH PRIVILEGES;

SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

6、开启组复制

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Query OK, 0 rows affected (0.05 sec)

mysql> SHOW PLUGINS;

+----------------------------+----------+--------------------+

| Name | Status | Type |

+----------------------------+----------+--------------------+

| binlog | ACTIVE | STORAGE ENGINE |

...

| group_replication | ACTIVE | GROUP REPLICATION |

+----------------------------+----------+--------------------+

45 rows in set (0.02 sec)

7、启动group replication

SET GLOBAL group_replication_bootstrap_group=ON;

START GROUP_REPLICATION;

SET GLOBAL group_replication_bootstrap_group=OFF;

#查看组复制是否启动成功

SELECT * FROM performance_schema.replication_group_members;

8、配置第二个节点(仅列出和s1的不同之处)

datadir=/data/mysql/data/s2

port = 24802

socket = /data/mysql/sock/s2.sock

server_id=2

log_bin=/data/mysql/logs/s2/binlog

relay-log=/data/mysql/logs/s2/relay-bin

loose-group_replication_local_address= "127.0.0.1:24902"

9、启动第二个节点(实例)

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/cnf/s2.cnf

10、登录第二个节点并修改默认密码

mysql -h127.0.0.1 -P24802 -uroot --skip-password

SET PASSWORD = PASSWORD('root');

flush privileges;

11、配置group replication需要的用户

SET SQL_LOG_BIN=0;

CREATE USER rpl_user@'%';

GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';

FLUSH PRIVILEGES;

SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

12、安装group replication插件

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Show plugins;

+----------------------------+----------+--------------------+

| Name | Status | Type |

+----------------------------+----------+--------------------+

| binlog | ACTIVE | STORAGE ENGINE |

...

| group_replication | ACTIVE | GROUP REPLICATION |

+----------------------------+----------+--------------------+

45 rows in set (0.02 sec)

13、将s2添加到组中

set global group_replication_allow_local_disjoint_gtids_join=ON;

start group_replication;

#查看改group replication情况

SELECT * FROM performance_schema.replication_group_members;

14、添加第三个节点

操作同第二个节点，s3.cnf的参数略有不同

#添加完成查看各节点的运行状态

SELECT * FROM performance_schema.replication_group_members;

参考资料：

卸载mysql：http://www.cnblogs.com/kerrycode/p/4364465.html

MySQL Group Replication: http://mysqlhighavailability.com/mysqlha/gr/doc/getting_started.html