根据官方文档搭建MySQL Innodb Cluster

MySQL Innodb Cluster搭建

本次测试使用三台机器搭建mysql innodb cluster production mode,官方架构如下图所示：

机器列表：

1、创建数据库实例

首先在三台主机上创建3307数据库实例，数据库版本为mysql-8.0.13，配置文件如下：

[mysqld]

server-id = 1

port = 3307

socket = /data/mysql8/3306/mysql.sock

basedir = /data/mysql8/mysql8

datadir = /data/mysql8/3306/data

pid-file = /data/mysql8/3306/mysql.pid

log-error = /data/mysql8/3306/mysql-error.log

plugin-dir = /data/mysql8/mysql8/lib/plugin

#gernal setting

lower_case_table_names = 1

max_binlog_size = 1G

sync_binlog=1

innodb_flush_log_at_trx_commit = 1

#semi-sync

#rpl_semi_sync_master_enabled=1

#rpl_semi_sync_master_timeout=1000

#rpl_semi_sync_slave_enabled=1

#innodb

innodb_buffer_pool_size = 8G

innodb_buffer_pool_instances =8

#MGR

log-bin = /data/mysql8/3306/bin/mysql-bin

relay-log = /data/mysql8/3306/bin/relay-log

log-slave-updates

binlog-format=row

gtid-mode=ON

enforce-gtid-consistency=true

master-info-repository=table

relay-log-info-repository=table

transaction-write-set-extraction=XXHASH64

标红为必须设置的参数，其他GR配置前提：

必须使用innodb存储引擎，主要为了避免事务提交时冲突。

必须定义主键。

节点间网络延迟小。

使用IPV4协议。

每个节点上启动MySQL数据库实例，注意serverid配置：

./bin/mysqld --defaults-file=/data/mysql8/3306/3306.cfg --initialize-insecure

./bin/mysqld_safe --defaults-file=/data/mysql8/3306/3306.cfg --user=mysql &

mysql -uroot -p -S /data/mysql8/3306/mysql.sock

mysql> alter user root@’localhost’ identified by ‘xxxxxxx’;

mysql>flush privileges;

2、安装mysql shell

需要使用python2.7及以上。

1)安装python2.7

./configure

make && make install

2)解压mysql shell

tar zxvf  mysql-shell-8.0.13-linux-glibc2.12-x86-64bit.tar.gz

3)安装router

Router安装包被封装在mysql8.0.13压缩包中，直接解压使用即可。

3、部署innodb cluster

首先在10.191.143.24创建cluster

> mysqlsh --log-level=DEBUG3

JS> \connect mysql://root@localhost:3307?socket=(/data/mysql8/3306/mysql.sock)

JS> dba.verbose=2

JS > dba.configureInstance()

Mysqlsh自动检查当前实例配置是否满足创建cluster

Configuring local MySQL instance listening at port 3307 for use in an InnoDB cluster...

This instance reports its own address as OaasSvr

Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

WARNING: User 'root' can only connect from localhost.

If you need to manage this instance while connected from other hosts, new account(s) with the proper source address specification must be created.

1) Create remotely usable account for 'root' with same grants and password

2) Create a new admin account for InnoDB cluster with minimal required grants

3) Ignore and continue

4) Cancel

Please select an option [1]: 2

检测到root用户只限定本地登陆，需要重新创建用户

Account Name: cluster@10.191.143.%

继续检测

Some configuration options need to be fixed:

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

| Variable        | Current Value | Required Value | Note                       |

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

| binlog_checksum | CRC32         | NONE           | Update the server variable |

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

Do you want to perform the required configuration changes? [y/n]: y

发现参数需要调整，自动调整。

创建用户后重新使用新用户验证后创建集群

JS > \connect mysql://cluster@10.191.143.24:3307

Please provide the password for 'cluster@10.191.143.24:3307': *********

Save password for 'cluster@10.191.143.24:3307'? [Y]es/[N]o/Ne[v]er (default No): YY

Fetching schema names for autocompletion... Press ^C to stop.

Your MySQL connection id is 22

Server version: 8.0.13 MySQL Community Server - GPL

No default schema selected; type \use <schema> to set one.

JS > dba.configureInstance()dba.configureInstance()

Configuring local MySQL instance listening at port 3307 for use in an InnoDB cluster...

This instance reports its own address as OaasSvr

Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

The instance '10.191.143.24:3307' is valid for InnoDB cluster usage.

JS > var cluster = dba.createCluster('testCluster')

Validating instance at 10.191.143.24:3307...

This instance reports its own address as OaasSvr

Instance configuration is suitable.

Creating InnoDB cluster 'testCluster' on 'cluster@10.191.143.24:3307'...

Adding Seed Instance...

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

At least 3 instances are needed for the cluster to be able to withstand up to

one server failure.

 MySQL  10.191.143.24:3307 ssl  JS > cluster.status()cluster.status()

{

    "clusterName": "testCluster",

    "defaultReplicaSet": {

        "name": "default",

        "primary": "10.191.143.24:3307",

        "ssl": "REQUIRED",

        "status": "OK_NO_TOLERANCE",

        "statusText": "Cluster is NOT tolerant to any failures.",

        "topology": {

            "10.191.143.24:3307": {

                "address": "10.191.143.24:3307",

                "mode": "R/W",

                "readReplicas": {},

                "role": "HA",

                "status": "ONLINE"

            }

        }

    },

    "groupInformationSourceMember": "mysql://cluster@10.191.143.24:3307"

}

作者：zyxchaos
链接：https://www.jianshu.com/p/1375966561f9