mysql cluster group_搭建一套完整的Mysql5.7innodbcluster(GroupReplication+mysqlrouter)

先说三个大步骤：

搭Mysql5.7 Group Replication ,配置成单主模式

安装Mysqlshell,配innodbcluster

安装Mysql-router

第一步：搭Mysql5.7 Group Replication ,配置成单主模式

为了节省步骤，我们用RPM方式安装

yum install -y libaioyum install libnuma* -y

rpm-qa | grep -i mysql # 这一步找到旧的mysql

rpm-e mysql-libs-5.1.73-8.el6_8.x86_64 --nodeps #我测试的实例上只有这个，删了

rpm-ivh mysql-community-common-5.7.20-1.el6.x86_64.rpm

rpm-ivh mysql-community-libs-5.7.20-1.el6.x86_64.rpm

rpm-ivh mysql-community-devel-5.7.20-1.el6.x86_64.rpm

rpm-ivh mysql-community-client-5.7.20-1.el6.x86_64.rpm

rpm-ivh mysql-community-libs-compat-5.7.20-1.el6.x86_64.rpm

rpm-ivh mysql-community-embedded-5.7.20-1.el6.x86_64.rpm

rpm-ivh mysql-community-server-5.7.20-1.el6.x86_64.rpm

rpm-qa | grep mysql　#验证

拷贝cnf.和服务文件

cp mysqld33* /etc/init.d/

cp -r mysql33* /home/wokofo/

配置文件：

[mysqld]

#skip-grant-tables=1datadir=/home/wokofo/mysql3306/data

socket=/home/wokofo/mysql3306/mysql.sock

log-error=/home/wokofo/mysql3306/mysqld.log

pid-file=/home/wokofo/mysql3306/mysqld.pid

port=3306symbolic-links=0user=mysql

server_id= 6skip_ssl

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

expire_logs_days=7relay_log=relay-log

character_set_server=utf8mb4

max_connections=10000open_files_limit=10000slow_query_log=1long_query_time=1.000skip_name_resolve=on

sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER

max_allowed_packet=20480000transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="81125aaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"loose-group_replication_start_on_boot=off

loose-group_replication_local_address="172.16.81.127:13306"loose-group_replication_group_seeds="172.16.81.125:13306,172.16.81.126:13306,172.16.81.127:13306"loose-group_replication_ip_whitelist = "127.0.0.1,172.16.0.1/16"

启动服务:

在主节点上执行：

set sql_log_bin=0;

grant replication slave,replication client on*.* to 'repluser'@'%' identified by '*************';

flush privileges;

set sql_log_bin=1;install plugin group_replication soname 'group_replication.so';

change master to master_user='repluser',master_password='***********************' for channel 'group_replication_recovery';

set global group_replication_bootstrap_group=ON;

START group_replication;

set global group_replication_bootstrap_group=OFF;

exit;

其他节点上执行：

change master to master_user='repluser',master_password='****************' for channel 'group_replication_recovery'; #这一步如果不是空库，需要自己调

set global group_replication_allow_local_disjoint_gtids_join=ON;

START group_replication;

任意节点上执行

select * from performance_schema.replication_group_members;

验证成功，步骤一完成，GR集群搭建成功

第二步：安装Mysqlshell,配innodbcluster

rpm -ivh mysql57-community-release-el6-11.noarch.rpmyum install -y mysql-shell

python-V #如果低于2.7需要升级

mysqlsh --uri dba@172.16.81.125:3306Creating a Session to'dba@172.16.81.125:3306'Enter password:

mysql-js> var cluster=dba.createCluster('fvt01',{adoptFromGR:true});

Anew InnoDB cluster will be created on instance 'dba@172.16.81.125:3306'.

Creating InnoDB cluster'fvt01' on 'dba@172.16.81.125:3306'...

Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.

At least3 instances are needed forthe cluster to be able to withstand up to

one server failure.

mysql-js>cluster.status()

{"clusterName": "fvt01","defaultReplicaSet": {"name": "default","primary": "172.16.81.125:3306","status": "OK","statusText": "Cluster is ONLINE and can tolerate up to ONE failure.","topology": {"172.16.81.125:3306": {"address": "172.16.81.125:3306","mode": "R/W","readReplicas": {},"role": "HA","status": "ONLINE"},"l-fvt-mysql2.beta0.cn2:3306": {"address": "l-fvt-mysql2.beta0.cn2:3306","mode": "R/O","readReplicas": {},"role": "HA","status": "ONLINE"},"l-fvt-mysql3.beta0.cn2:3306": {"address": "l-fvt-mysql3.beta0.cn2:3306","mode": "R/O","readReplicas": {},"role": "HA","status": "ONLINE"}

}

}

}

mysql-js> dba.configureLocalInstance("dba@127.0.0.1:3306")

Please provide the passwordfor 'dba@127.0.0.1:3306':

Detecting the configuration file...

Default file not found at the standard locations.

Please specify the path to the MySQL configuration file:/home/wokofo/mysql3306/my.cnf

Validating instance...

The instance'127.0.0.1:3306' is valid forCluster usage

You can now use itinan InnoDB Cluster.

{"status": "ok"}

mysql-js>ctrl c

Bye!

第三步：安装Mysql-router

yum install mysql-router

mysqlrouter--bootstrap 172.16.28.105

chown mysql:mysql /etc/mysqlrouter/mysqlrouter.conf

cd/usr/local/mysqlrouter--bootstrap dba@172.16.81.125:3306 --directory myrouter --user mysql --force

cd myrouter/

catmysqlrouter.confsh start.shnetstat-anp |grep router