分布式MySQL——InnoDB cluster和性能测试

作者: dreamguys | 来源:发表于2018-11-19 15:49 被阅读63次

前言

MySQ是Oracle旗下的一个关系型数据库管理系统。MySQL软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

随着对数据库的访问量增大，使用分表、优化SQL语句等等优化方法也不能解决高并发，且高并发带来的数据库可用性降低的问题时，使用分布式数据库就势在必行了。

首先这里介绍一下分布式和集群两个概念的不同，集群是个物理形态，分布式是个工作方式。只要是一堆机器，就可以叫集群，并不关心它们是否一起协作着干活；一个程序或系统，只要运行在不同的机器上，就可以叫分布式，C/S架构也可以叫分布式。

分布式MySQL主要有InnoDB和NDB模式，官网上的MySQL Cluster安装包使用的是NDB模式。

我们也可以通过两者的架构图来分析他们的不同

innodb cluster

NDB cluster

从中我们可以最直接的看出，在NDB中在SQL节点运行完SQL后还用通过网络传输到数据节点对数据操作，而InnoDB中则可在单台服务器直接运行完，所以可以看出InnoDB的效率要高于NDB模式，但是因为NDB的模式下把各个功能独立出来，且每个功能横向扩展，所以整个数据库系统的可用性也提高，适合于很高并发的场景。

下面是两种模式的不同点：

InnoDB和NDB模式的区别

下面我们先介绍InnoDB模式。

一、分布式MySQL-InnoDB模式

1.1、InnoDB集群的安装介绍

1.1.1 安装包准备

在主节点上需要安装mysql,mysql-shell和mysql-router；

在从节点上则只需安装mysql和mysql-shell。

在本文例子中用的是：

mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
mysql-shell-8.0.13-linux-glibc2.12-x86-64bit.tar.gz
mysql-router-8.0.13-linux-glibc2.12-x86_64.tar.xz

下载的官网地址

mysql可在MySQL Community Server中下载，
mysql shell和mysql router在相应模块就可下载

1.1.2 MySQL Router和MySQL Shell介绍

下面是官网对于这两个应用的介绍：

1.1.2.1 Mysql Shell

The MySQL Shell is an interactive Javascript, Python, or SQL interface supporting development and administration for the MySQL Server and is a component of the MySQL Server. You can use the MySQL Shell to perform data queries and updates as well as various administration operations.

The MySQL Shell provides:

Both Interactive and Batch operations
Javascript, Python, and SQL language modes
Document and Relational Models
CRUD Document and Relational APIs via scripting
Traditional Table, JSON, Tab Separated output results formats
Stored Sessions
MySQL Standard and X Protocols

中文可理解为mysql shell可用Javascript, Python或者SQL语言来支持mysql服务器开发和管理的接口。

1.1.2.2 MySQL Router

MySQL Router is lightweight middleware that provides transparent routing between your application and any backend MySQL Servers. It can be used for a wide variety of use cases, such as providing high availability and scalability by effectively routing database traffic to appropriate backend MySQL Servers.

中文理解为MySQL Router是轻量级中间件，可在应用程序和任何后端MySQL服务器之间提供透明路由。

1.1.3 安装过程介绍

为了更好的介绍安装过程和可能会出现的坑，我先介绍下将要搭建的集群信息:

本例子中将搭建一主一从两台mysql服务器的集群

主节点:

IP:192.168.1.1 主机名：test1 集群中的节点名：node01

从节点：

IP:192.168.1.2 主机名：test2 集群中的节点名:node02

1.1.3.1 mysql安装

1.host文件的配置

使用命令:vi /etc/hosts修改配置文件

把以下信息加上:

192.168.1.1 test1
192.168.1.1 node01
192.168.1.2 test2
192.168.1.2 node02

要这样加一是因为连接节点要用node01、node02来接连，但在节点通信时会使用主机名进行通信，所以要把主机名也加上。

2.解压安装文件

使用命令tar xvf *.tar.gz把每个压缩包都解压出来

可再用命令mv 解压出的mysql文件夹 mysql来修改文件夹名

3.新建组和用户

使用命令groupadd mysql新建组；

再使用命令useradd -g mysql mysql新建用户。

4.新建my.cnf配置文件

使用命令vi /etc/my.cnf新建和编辑配置文件

[mysql]
#设置mysql客户端默认字符集  
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
#skip-name-resolve  
#设置3306端口  
port = 3306
socket=/var/lib/mysql/mysql.sock
# 设置mysql的安装目录  
basedir=/opt/mysql-innoDB-cluster/mysql
# 设置mysql数据库的数据的存放目录  
datadir=/opt/mysql-innoDB-cluster/mysql/data
# 允许最大连接数  
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集  
character-set-server=utf8
# 创建新表时将使用的默认存储引擎  
default-storage-engine=INNODB
max_allowed_packet=16M  

#主从复制配置
loose-group_replication_group_name=875fab7b-dde1-11e8-a426-005056bf708d
loose-group_replication_local_address= node01:33061
loose-group_replication_group_seeds
loose-group_replication_single_primary_mode=ON

loose-group_replication_group_name要和主节点的group_name保持一致，loose-group_replication_local_address根据自己节点修改，不同节点的server_id也要不同。

要在[mysql]、[client]、[mysqld]三者中都配置的目的是不配置全的话关闭mysql服务时可能会出现配置中的目录为/var/lib/mysql/mysql.sock且明确成功加载的情况下仍去找/tem/mysql/mysql.sock，而，所以我就按此配置修改后就修复问题了。

使用一下命令修改配置文件的所属用户和权限

chown 常用登陆用户:mysql my.cnf

chmod 644 my.cnf

使用这两个命令的目的有两个：1.为了让后面的安装过程能够顺利的读写该配置文件；2.如果配置文件不是644，则在运行mysql时会ignore该配置文件，也就是配置文件不会生效（可在日志文件中查看到该问题）

5.进入到mysql软件目录中

执行命令：bin/mysqld --initialize --user=mysql --basedir=/opt/mysql-innoDB-cluster/mysql/ --datadir=/opt/mysql-innoDB-cluster/mysql/data/

该命令需在mysql下执行，而不行进入到bin目录中直接./mysqld，因为会报找不到文件的错误（初始化数据库的时候会在./目录下查找相关的文件,所以需要保持当前文件路劲为mysql安装目录下）

该命令执行完后会给出数据库的初始密码，示例代码如下：

2018-11-08T06:53:39.649121Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2018-11-08T06:53:39.649326Z 0 [Warning] Changed limits: table_open_cache: 407 (requested 2000)
2018-11-08T06:53:39.649651Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-11-08T06:53:39.652903Z 0 [Warning] One can only use the --user switch if running as root

2018-11-08T06:53:40.057717Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-11-08T06:53:40.127602Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-11-08T06:53:40.191929Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 062f3a4d-e323-11e8-8191-005056bf9f63.
2018-11-08T06:53:40.194960Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-11-08T06:53:42.294108Z 1 [Note] A temporary password is generated for root@localhost: VF3R7q7rt3?%

6.修改data文件夹的权限

chown -R mysql:mysql data

7.新增文件夹并修改权限

cd /var/lib
mkdir mysql
chown 常用登陆用户：mysql mysql
chown 重用登陆用户：mysql mysql/*

7.测试mysql和修改密码

进入到mysql目录的bin目录下执行./mysqld_safe &

如果mysql成功执行的话再执行./mysql -uroot -p，输入密码后进去可看到

mysql>

这时候如果实行其他命令会报错，需要修改密码后才能执行其他命令，所以直接运行该命令alter user user() identified by '新密码';

8.设置表的权限

在mysql>的界面中使用命令：

grant all privileges on *.* to root@'192.168.1.1' identified by '密码' with grant option;
grant all privileges on *.* ro root@'192.168.1.2' identified by '密码' with grant option;
grant all privileges on *.* to root@'你实际使用电脑的IP' identified by '密码'
flush privileges;

执行以上命令的目的有以下两点：1.执行第1，2行命令是为了在后续的操作顺利，否则在使用mysql shell的dba.checkoutInstanceConfiguration时会报****主机没有权限;2.执行第三条命令是为了个人电脑能在远程通过navicat之类的工具直接连接mysql，否则会报个人主机不被允许的错误。

9.mysql加入环境变量

cd /etc/profile.d
sudo vi mysql.sh

#mysql.sh
MYSQL=/opt/mysql-innoDB-cluster/mysql/bin
export PATH=$PATH:$MYSQL

source /etc/profile.d/mysql.sh

mysql在启动或者后续加入集群中的错误都可在data文件夹中的主机名.err日志文件中查询。

如有其他错误可参考最后章节——“错误参考”

1.1.3.2 mysql shell的安装

1.解压完mysql-shell后进入bin目录后运行命令：

./mysqlsh
mysql-js>shell.connect('root@node01:3306')

2.按要求输入密码登录成功后再输入

dba.configureLocalInstance()

输入该命令后可能会出现4个选项，选其中第一个（创建一个可用于远程连接的账号），按要求输入账号：root@192.168.1.1（本服务器ip）。

输入完账号后会要求修改my.cnf文件，按要求分别输入两次y后即可。这里如果出现无法读取或写入那是因为/etc/my.conf配置文件的权限问题，根据之前我介绍的内容参考着修改。

成功配置会要求重启mysql，这时候只要执行mysqladmin -uroot -p shutdown关闭mysql，再执行mysqld_safe &即可。

3.其他从节点也按上述要求修改成功后再执行

dba.checkInstanceConfiguration('root@node01:3306')
dba.checkInstanceConfiguration('root@node02:3306')

在主节点上检查每个节点的状态，要求每个节点的检查结果是

｛
    "status":"ok"
｝

这里可能会出现的错误是***主机没有权限，可用前几步介绍的方法给要求的账号授权，但不能用root@%来代替所以账户（eg:提示root@192.168.1.1账号没有权限，此时在mysql授权的账号不能grants *** to root@% ***,而要用grants *** to root@192.168.1.1 ***，查看具体账号的授权情况的命令：show grants for 'root'@'192.168.1.1';）

4.如果所有节点的显示状态OK，则开始创建集群，在主节点上运行：

var cluster = dba.createCluster('testCluster')
cluster.status()

cluster.status()可查看集群状态；
5.加入其它结点：

cluster.addInstance('root@node02:3306')

这里可能会出现的问题：提示The server is not configured properly to be an active member of the group，可能是因为从节点的配置文件/etc/my.cnf中的group-name不一致，检查文件并修改和主节点一致。

如果添加成功了，可再次调用该命令查看集群状态

cluster.status()

集群成功的示例结果：

{
    "clusterName": "testCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "node01:3306", 
        "ssl": "DISABLED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "node01:3306": {
                "address": "node01:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "node02:3306": {
                "address": "node02:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@node02:3306"
}

mysql-shell还有其他几种情况和命令需要阐述

1.在退出mysql-shell再进入后直接运行cluster.*的命令会提示找不到方法，所以这时候使用该命令可以重新获取到cluster

var cluster = dba.getCluster('testCluster')

2.在调用dba.getCluster时报错：Dba.getCluster: This function is not available through a session to a standalone instance (RuntimeError)，这时可使用如下命令：

mysql-js> dba.rebootClusterFromCompleteOutage('testCluster')

3.单节点重启

cluster.rejoinInstance('root@node02:3306')

4.状态属性

节点状态

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。

1.1.3.3 MySQL Router的安装

注：官方文档上建议把mysql router和应用程序安装到同一台服务器上

1.解压完mysql-router后进去目录bin中，运行命令

./mysqlrouter --bootstrap root@node01:3306 --directory /opt/mysql-innoDB-cluster/mysql-router --conf-use-sockets

root@node01:3306这里使用了主库，因为如果用分库会报节点为只读的错误；

--directory就是指定安装目录；

如果出现One can only use the -u/--user switch if running as root的错误，则在命令前加上sudo即可。

使用该命令后系统会让输入node01节点的数据库密码，并且把该密码存入其他节点的数据库中（相当于修改了其他节点的密码），之后在通过127.0.0.1:6446和127.0.0.1:6447访问的时候都采用这个密码就可以访问得到。

2.回到上一级目录，可发现多了mysqlrouter.conf的配置文件，vi ./mysqlrouter.conf编辑配置文件，这时可看到配置中的节点信息为：

bootstrap_server_addresses=mysql://node02:3306,mysql://node01:3306

所以为了能顺利的链接到数据节点，最好在host文件中把node01和node02的路由信息加上。

运行mysql router

./start.sh

测试方法：在mysql router服务器上运行以下命令：

mysql -uroot -p -h 127.0.0.1 -P 6446

能够进入并且插入数据表示成功，如果要连接从节点则用以下命令

mysql -uroot -p -h 127.0.0.1 -P 6447

测试方法同样插入数据，但会提示

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

1.1.3.4 大致问题汇总

大多数问题都为以下几种：

要查看或写的文件权限不够，把chown或者chmod灵活使用一般都可解决，对于系统文件和mysql特定文件（eg:/etc/my.cnf）的权限不可修改，如特殊情况要修改编辑，之后记得修改回去；
使用了node01,node02或者主机名导致的网络不通，需要配置host文件
部分权限不够的情况在配合sudo命令一起执行；
数据库的账号权限不够，使用grant all privileges on . to 'root'@'主机IP' identified by '数据库密码' with grant option，修改后记得使用flush privileges命令;

1.2、mysql集群的主从机制介绍

数据一致性：

MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

二、MySQL性能测试

2.1 mysqlslap

对mysql进行性能测试的工具主要有mysql自带的mysqlslap，使用方法也简单，如果在设置了环境变量的情况下直接使用命令：

mysqlslap ***（接具体命令参数即可）

具体的命令及说明可到官网查看

2.2 Jmeter

总所周知，Jmeter是Apache组织开发的基于Java的压力测试工具。用于对软件做压力测试，它最初被设计用于Web应用测试，但后来扩展到其他测试领域。它可以用于测试静态和动态资源。

对于我们来说，它除了有以上描述的优势点，它是有图形化界面的，相比mysqlslap来说，我们可以更直观的看到测试结果。

3.2.1 Jmeter的使用

1.可到官网下载，解压到想要的目录下；

2.设置环境变量：先设置

JMETER_HOME
D:\apache-jmeter-5.0\

在path中追加

path
%JMETER_HOME%\lib\ext\ApacheJMeter_core.jar;%JMETER_HOME%\lib\jorphan.jar;

3.打开解压后的目录，打开bin文件夹下的Jmter.bat，可打开Jmeter图形化界面；

4.点击Test Plan，再点击浏览,把mysql-connector-java加入进来，该jar文件可去自己本地的或者线上maven仓库中找，我这里是把找到的jar文件复制到Jmeter的目录下而已。

添加链接文件

5.对Test Plan右键添加一个线程组（全英文界面选择:add->Threads->Thread Group），这里命名为connect mysql,然后再设置线程数(Number of Threads)、启动时间(Ramp-up Period,在该时间内启动完所有线程)、循环次数（loop count）

添加配置线程组

6.右键connect mysql添加一个配置元件：JDBC Connection Configuration（add->config element->JDBC Connection Configuration)

7.配置该元件：主要是变量名，数据源。如果要测试mysql router+mysql的性能，这里可以改成相应的mysql router的路由信息

设置配置元件

8.右键connect mysql添加采样器：JDBC request（add->sample->JDBC Request）

9.配置该采样器

保持变量名和配置原件中的变量名一致；根据不同的sql语句选择相应的query type,update和insert 都是选update statement，select就选select statement

配置采样器

10.添加监听器(listener)：添加图形结果(Graph Results)、结果树(view Results tree)、聚合报告(summary report)

结果可以如下图所示（我创建了两个配置元件，一个写一个读同时运行）

结果数

聚合报告

图形结果