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
随时随地看视频
