Mysql-Innodb-Cluster项目实战

引言

近期由于数据库需要升级，将原有主从模式改为集群模式，实现搞可用HA。作为没有使用过mysql的小白，花了三周时间研究了下mysql，并了解原理后，完成了公司分派的任务，将mysql-innodb-cluster部署在生产环境。下面就将我这次采坑实战记录下来。

先讲解下mysql-innodb-cluster的架构吧（mysql相关知识请自行脑补，后期会写些相关专题讲解）

*MySQL 5.7 引入了 Group Replication 功能，可以在一组 MySQL 服务器之间实现自动主机选举，形成一主多从结构。经过高级配置后，可以实现多主多从结构。

* MySQL Router 是一个轻量级透明中间件，可以自动获取上述集群的状态，规划 SQL 语句，分配到合理的 MySQL 后端进行执行。

* MySQL Shell 是一个同时支持 JavaScript 和 SQL 的交互程序，可以快速配置 InnoDB Cluster。

核心架构图

mysql-shell

先讲解下mysql-shell的使用，说白了就是个mysql客户端，可以操作mysql数据库，通过命令行界面进行操作。

mysql-router

而mysql-router的作用就是起到路由的作用，虽然我们可以通过下面的SQL语句获取主节点的IP地址

SELECT * FROM performance_schema.replication_group_members

WHERE MEMBER_ID = (

SELECT VARIABLE_VALUE

FROM performance_schema.global_status

WHERE VARIABLE_NAME= 'group_replication_primary_member'

);

但是通过应用程序动态的获取可用数据库的IP地址. 这种方式感觉不怎么好. 很麻烦, 要写多余的代码。

配置MySQL Router首先需要MySQL Shell 工具, 在 MySQL Shell 部分有详细的说明

下面是MySQL Router 和集群的基本关系图

mysql-router与集群的关系

从上图知道, MySQL Router 在InnoDB集群里面主要作用是为数据库集群提供一个虚拟IP.

我们的应用程序 通过这个单一的连接点实现负载均衡, 读写分离, 故障转移等数据库高可用方案.

推荐安装在应用程序所在的机器上, 原因包括:

通过Unix套接字连接, 而不是TCP/IP, 提升性能

降低网络延迟

MySQL实例不需要额外的账号, 只需要一个 router@198.51.100.45, 而不是 myapp@%

提升应用程序服务器的弹性

组复制 mysql group replication

下面介绍下mysql的新特性组复制

1.Mysql Group Replication简介

Mysql Group Replication(MGR)是一个全新的高可用和高扩张的MySQL集群服务。

高一致性，基于原生复制及paxos协议的组复制技术，以插件方式提供一致数据安全保证；

高容错性，大多数服务正常就可继续工作，自动不同节点检测资源征用冲突，按顺序优先处理，内置自动防脑裂机制；

高扩展性，自动添加移除节点，并更新组信息；

高灵活性，单主模式和多主模式。单主模式自动选主，所有更新操作在主进行；多主模式，所有server同时更新。

Mysql-innodb-cluster内部就是基于组复制实现高可用集群的

2.组复制的限制

存储引擎必须为Innodb

每个表必须提供主键

只支持ipv4，网络需求较高

一个组最多只能有9台服务器

不支持 Replication event checksums

不支持 Savepoints

多主模式不支持SERIALIZABLE事务隔离级别

多主模式不能完全支持级联外键约束

多主模式不支持在不同节点上对同一个数据库对象并发执行DDL(在不同节点上对同一行并发进行RW事务，后发起的事务会失败)

3.Mysql Group Replication与传统复制的区别和大幅改进

1.传统复制

主-从复制：有一个主和不等数量的从。主节点执行的事务会异步发送给从节点，在从节点重新执行。（异步和半同步）

（半同步相对异步Master会确认Slave是否接到数据，更加安全）

2.并行复制

并行复制：复制->广播->正式复制

优势：

弹性复制（高扩展性）：server动态添加移除

高可用分片（高扩展性）：分片实现写扩展，每个分片是一个复制组。

替代主从复制（高扩展性）：整组写入，避免单点争用。

自动化系统：自动化部署Mysql复制到已有复制协议的自动化系统。

故障检测与容错：自动检测，若服务faild,组内成员大多数达成认为该服务已不正常，则自动隔离。

接下来我们将实战安装教程。

一、安装mysql5.7

在我们的三台机器上分别安转mysql5.7版本mysql

tar -xvf mysql-5.7.22-linux-glibc2.12-x86_64.tar

tar -zxvf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

mv mysql-5.7.22-linux-glibc2.12-x86_64 mysql-5.7.22

进入mysql-5.7.22 mkdir data

配置权限

chown -R remind:remind /app/remind/mysql-5.7.22/data/

chown -R remind:remind /app/remind/mysql-5.7.22/

chmod -R 755 /app/remind/mysql-5.7.22/

chmod -R 750 /app/remind/mysql-5.7.22/my.cnf

修改my.cnf 配置我们自定义的数据存储目录，根据需要自定义

vi /app/remind/mysql-5.7.22/my.cnf 添加如下:

basedir=/app/remind/mysql-5.7.22/

datadir=/app/remind/mysql-5.7.22/data/mysql

添加如下字段，mysql-innodb-cluster根据此实现其他主机查找

-report-host=IP

三台主机分别操作

report-host=10.4.63.16

report-host=10.4.63.28

report-host=10.4.63.8

初始化数据库

./mysqld --defaults-file=/app/remind/mysql-5.7.22/my.cnf --initialize

./mysqld --defaults-file=/app/remind/mysql-5.7.22/my.cnf --basedir=/app/remind/mysql-5.7.22/ --datadir=/app/remind/mysql-5.7.22/data/mysql --initialize

执行后5.7会在控制台生成临时密码，复制保存后面会用到 A temporary password is generated for root@localhost: c8x6edth=k6C

启动数据库

#./mysqld_safe --defaults-file=/app/remind/mysql-5.7.22/my.cnf --skip-grant-table --skip-networking &

./mysqld_safe --defaults-file=/app/remind/mysql-5.7.22/my.cnf & 启动后额外在打开个窗口

修改root新密码

进入mysql-5.7.22/bin ./mysql -uroot -p 输入之前设置的临时密码

#SET PASSWORD='123456';

SET PASSWORD='khjhzx-!@34';

ALTER USER 'root'@'localhost' password expire never;

flush privileges;

innonDB集群授权

grant all privileges on *.* to 'root'@'%' identified by 'khjhzx-!@34';

GRANT ALL PRIVILEGES ON mysql_innodb_cluster_metadata.* TO root@'%' WITH GRANT OPTION;

GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, \

CREATE USER ON *.* TO root@'%' WITH GRANT OPTION;

GRANT SELECT ON *.* TO root@'%' WITH GRANT OPTION;

flush privileges;

可以查看添加是否成功

use mysql;

select user,host from user;如果第一次安装配置过report_host可以查看

SELECT coalesce(@@report_host, @@hostname);是否配置成功

以上是安装5.7教程

./mysqladmin -uroot -p shutdown 停止服务

./mysqld_safe --defaults-file=/app/remind/mysql-5.7.22/my.cnf & 启动服务

二 安装mysql-shell

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

mv mysql-shell-8.0.11-linux-glibc2.12-x86-64bit mysql-shell

设置环境变量

export PATH=/app/remind/mysql-shell/bin/:$PATH

export PATH=/app/remind/mysql-5.7.22/bin/:$PATH

进入mysql-shell/bin执行

./mysqlsh

## 检查mysql 配置文件 (3台主机都要操作此步骤)输入root密码

dba.checkInstanceConfiguration('root@10.4.65.165:3307')

dba.checkInstanceConfiguration('root@10.4.65.164:3307')

dba.checkInstanceConfiguration('root@10.4.65.163:3307')

khjhzx-!@34

## 修复mysql 配置文件， 必须用 root(3台主机都要操作此步骤)dba.configureLocalInstance()

dba.configureLocalInstance('root@10.4.65.165:3307')

dba.configureLocalInstance('root@10.4.65.164:3306')

dba.configureLocalInstance('root@10.4.65.163:3307')

dba.configureLocalInstance('root@10.4.65.164:3307')

校验配置文件目录，输入自己的配置目录/app/remind/mysql-5.7.22/my.cnf

提示重启mysql 开新窗口

./mysqladmin -uroot -p shutdown 停止服务 khjhzx-!@34

./mysqld_safe --defaults-file=/app/remind/mysql-5.7.22/my.cnf & 启动服务

ps -ef |grep mysql查看服务

## 重新检查 (3台主机都要操作此步骤)

dba.checkInstanceConfiguration('root@10.4.65.165:3307')

dba.checkInstanceConfiguration('root@10.4.65.164:3307')

dba.checkInstanceConfiguration('root@10.4.65.163:3307')

在自己的一台优先主机上登录 khjhzx-!@34

## 登陆 ./mysqlsh --uri root@10.4.65.164:3307

./mysqlsh --uri root@10.4.63.16:3306 ./mysqlsh --uri root@10.4.65.165:3307

./mysqlsh --uri root@10.4.65.164:3306

./mysqlsh --uri root@10.4.65.163:3307

## 创建集群 main

mysql-js> var cluster = dba.createCluster('main') var cluster = dba.createCluster('prod')

A new InnoDB cluster will be created on instance 'hequan@db1:3306'.

如果查看已有集群 dba.getCluster() 报错reset master;

var cluster = dba.getCluster() #根据需要决定删除 cluster.dissolve({force:true})删除集群分片 删除实例cluster.removeInstance('root@10.4.63.38:3306')

dba.removeCluster('main')

Creating InnoDB cluster 'main' on 'hequan@db1:3306'...

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.

## 添加子节点 khjhzx-!@34 如果出现报错3092，active member报错去对应登录mysql -uroot -p，执行命令 reset master;

mysql-js> cluster.addInstance('root@10.4.63.8:3306')

mysql-js> cluster.addInstance('root@10.4.63.28:3306')

mysql-js> cluster.addInstance('root@10.4.63.16:3306')

mysql-js> cluster.addInstance('root@10.4.63.38:3306')

mysql-js> cluster.addInstance('root@10.4.65.165:3307')

mysql-js> cluster.addInstance('root@10.4.65.164:3307')

mysql-js> cluster.addInstance('root@10.4.65.163:3307')

## 查看节点信息

mysql-js> cluster.status()

## 将配置 持久化，写入到 my.cnf

mysql-js> \connect 10.4.65.164:3307

mysql-js> dba.configureLocalInstance('10.4.65.164:3307')

## 查看基本信息

mysql-js> cluster.describe();

## 退出之后，再查看节点信息

var cluster = dba.getCluster();

cluster.status();

三 安装mysql-router

tar -zxvf mysql-router-8.0.11-linux-glibc2.12-x86-64bit.tar.gz

mv mysql-router-8.0.11-linux-glibc2.12-x86-64bit mysql-router1

mysqlrouter.conf这里更改为bind_port=3306

命令会更新 /etc/mysqlrouter/mysqlrouter.conf 中的配置信息, 可以是别的机器 这里选择的为db2

这里切换为root用户操作 khjhzx-!@34

khjhzx-!@34 3330

./mysqlrouter --bootstrap root@10.4.65.164:3307 --directory /app/remind/mysql-router/data/mysqlrouter --conf-base-port 3306 --force

./mysqlrouter --bootstrap root@10.4.65.164:3307 --directory /app/remind/mysql-router1/data/mysqlrouter --conf-base-port 3306 --force

-c /app/remind/mysql-router/mysqlrouter.conf

khjhzx-12#$ remind用户操作

Please enter MySQL password for root:

WARNING: The MySQL server does not have SSL configured and metadata used by the router may be transmitted unencrypted.

Bootstrapping system MySQL Router instance...

MySQL Router has now been configured for the InnoDB cluster 'main'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'main':

- Read/Write Connections: localhost:6446 读写

- Read/Only Connections: localhost:6447 只读

X protocol connections to cluster 'main':

- Read/Write Connections: localhost:64460

- Read/Only Connections: localhost:64470

Existing configurations backed up to /etc/mysqlrouter/mysqlrouter.conf.bak

[root@db2 ~]# systemctl start mysqlrouter

## 启动推荐方式

cd data/mysqlrouter 目录下 执行./start.sh

或者用

./mysqlrouter &

./mysqlrouter -c /app/remind/mysql-router/mysqlrouter.conf & 方式启动

查看端口方法一 khjhzx-!@34

shell> mysqlsh --uri root@10.4.63.16:6442 或者 ./mysqlsh --uri root@10.4.63.16:3306

To verify which instance you are actually connected to, simply issue an SQL query against the port status variable.

mysql-js> \sql

Switching to SQL mode... Commands end with ;

mysql-sql> select @@port;

+--------+

| @@port |

+--------+

| 3310 |

+--------+

## 查看端口二

[root@db2 ~]# netstat -lntup

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program

name

tcp 0 0 0.0.0.0:64460 0.0.0.0:* LISTEN 2958/mysqlrouter

tcp 0 0 0.0.0.0:6446 0.0.0.0:* LISTEN 2958/mysqlrouter

tcp 0 0 0.0.0.0:6447 0.0.0.0:* LISTEN 2958/mysqlrouter

tcp 0 0 0.0.0.0:64470 0.0.0.0:* LISTEN 2958/mysqlrouter

## 验证

./mysql -u root -h 127.0.0.1 -P 6446 -p

./mysql -u root -h 127.0.0.1 -P 6446 -p khjhzx-!@34

./mysql -u root -h 10.4.65.164 -P 6446 -p

./mysql -u remind -h 10.4.65.164 -P 6446 -p khjhzx-12#$

./mysql -u remind -h 10.4.65.164 -P 3306 -p khjhzx-12#$

./mysql -u root -h 127.0.0.1 -P 3330 -p khjhzx-!@34

select @@port;

select @@hostname;

#添加用户组

groupadd mysql

#添加用户mysql 到用户组mysql

useradd -g mysql mysql

提示重启mysql 开新窗口 khjhzx-!@34

./mysqladmin -uroot -p shutdown 停止服务

./mysqld_safe --defaults-file=/app/remind/mysql-5.7.22/my.cnf &启动服务

ps -ef |grep mysql查看服务

mysql5.6数据迁移到mysql5.7

./mysqldump -uroot -p --single-transaction --master-data -A >all.sql

./mysql -uroot -p

./mysql_upgrade -uroot -p

添加用户remind grant all on test.* to 'remind'@'%' identified by '123456' with grant option;

create user remind@'%' identified by 'khjhzx-12#$';

grant all on *.* to 'remind'@'%' identified by 'khjhzx-12#$' with grant option;

flush privileges;

mysql>use dbtest;

mysql>set names utf8;

./mysqldump -uroot --set-gtid-purged=OFF remind_db> remind_db.sql

create tablespace remind_db add datafile 'xxxx' engine=innodb;

./mysql -uroot remind_db < /app/remind/mysql-5.6.33/bin/remind_db.sql

./mysql -uroot remind_db < /app/remind/mysql-5.6.33/bin/remind_db.sql

./mysql -uroot remind_db < /app/remind/mysql-5.7.22/bin/db_struct.sql

./mysql -u root -h 127.0.0.1 -P 3330 -p remind_db < /app/remind/mysql-5.7.22/bin/db_struct.sql

khjhzx-!@34

Mysql导出表结构及表数据 mysqldump用法

命令行下具体用法如下： mysqldump -u用戶名 -p密码 -d 數據库名 表名 脚本名;

1、导出數據库為dbname的表结构（其中用戶名為root,密码為dbpasswd,生成的脚本名為db.sql）

mysqldump -uroot -pdbpasswd -d dbname >db.sql;

2、导出數據库為dbname某张表(test)结构

mysqldump -uroot -pdbpasswd -d dbname test>db.sql;

3、导出數據库為dbname所有表结构及表數據（不加-d）

mysqldump -uroot -pdbpasswd dbname >db.sql;

4、导出數據库為dbname某张表(test)结构及表數據（不加-d）

mysqldump -uroot -pdbpasswd dbname test>db.sql;

异常问题解决：

##如果节点在加入集群前，执行了写操作，加入集群时会报错ERROR: Error joining instance to cluster: 'db2:3306' - Query failed. MySQL Error (3092): The server isnot configured properly to be an active member of the group. Please see more details on error log.. Query: START group_replication (RuntimeError)##登陆 db2 数据库 执行 reset master;

## 如果出现了 "status": "NO_QUORUM" 执行修复，重新加入 ## 暂未测试cluster.forceQuorumUsingPartitionOf("db1:3306")mysql-js> cluster.rejoinInstance('root@db2:3306')mysql-js> cluster.rejoinInstance('root@db3:3306')

节点状态

节点有哪状态 * ONLINE - 节点状态正常。 * OFFLINE - 实例在运行，但没有加入任何Cluster。 * RECOVERING - 实例已加入Cluster，正在同步数据。

* ERROR - 同步数据发生异常。 * UNREACHABLE - 与其他节点通讯中断，可能是网络问题，可能是节点crash。

* MISSING 节点已加入集群，但未启动group replication集群有哪些状态 * OK – 所有节点处于online状态，有冗余节点。

* OK_PARTIAL – 有节点不可用，但仍有冗余节点。 * OK_NO_TOLERANCE – 有足够的online节点，但没有冗余，例如：两个节点的Cluster，其中一个挂了，集群就不可用了。

* NO_QUORUM – 有节点处于online状态，但达不到法定节点数，此状态下Cluster无法写入，只能读取。 * UNKNOWN – 不是online或recovering状态，尝试连接其他实例查看状态。

* UNAVAILABLE – 组内节点全是offline状态，但实例在运行，可能实例刚重启还没加入Cluster。

以上就是mysql-innodb-cluster安装教程，具体可参考官方文档。

END