MySQL集群搭建详解 - 高飞网
98 人阅读

MySQL集群搭建详解

2017-08-17 11:43:27

    最近公司打算搭建MySQL集群,以前只用过一主二从,没有用过集群,正好学习一下。首先,我不明白为什么要做集群,一主二从不就可以了吗?不过为什么用先不考虑了,公司想用,那就研究吧,先搭建起来,再研究原理吧。

1 简介

    不准备讲原理,不过先来一张图,有个大概的印象:

    图片来源于官网中关于mysql5.7版本文档:https://dev.mysql.com/doc/refman/5.7/en/

    图中最上方的是连接mysql服务的应用客户端,如mysql client,mysql面向各语言的驱动客户端,这些客户端会发SQL请求到SQL服务层,即图中的SQL Nodes,可以把这些节点理解为应用层到数据层的窗口,应用就是通过这些服务与数据打交道的。SQL服务层会解析SQL请求,查询优化……等一系列操作,最后把请求转发到数据层,即图中的Data Nodes节点,这些就是数据节点,是真正保存数据的地方。除了物理上用到的这些SQL和Data节点外,右侧还有个NDB管理节点,这个节点管理着数据结点,可以通过它控制数据结节。简单讲这么多,下面重点看一下服务的搭建。

2 准备环境

2.1 集群节点

    既然是集群,当然至少要两个以上的节点了,因此部署的节点如下:

管理节点mysql-mgm192.168.23.130
数据节点1mysql-bndb-1192.168.23.131
数据节点2mysql-bndb-2192.168.23.132
SQL节点1mysql-sql-1192.168.23.133
SQL节点2mysql-sql-2192.168.23.134

2.2 下载

    下载地址:https://cdn.mysql.com//Downloads/MySQL-Cluster-7.4/mysql-cluster-gpl-7.4.16-linux-glibc2.12-x86_64.tar.gz

    注意:mysql集群只需要这个安装包就可以了,不用再下载日常使用的mysql服务端的安装包。另外,mysql cluster有两种版本,企业收费版和社区免费版,企业收费版即 mysql cluster CGE,这里使用社区免费版即可。因此用我上面提供的地址吧。

2.3 准备虚拟机

    2.1说了要准备5个节点,如果你的笔记本内存足够用,就用5个虚拟机吧,下图中,显示了我安装的虚拟机,对应关系:

CentOS 64 位_0
192.168.23.130
CentOS 64 位_1
192.168.23.131
CentOS 64 位_2
192.168.23.132
CentOS 64 位_3
192.168.23.133
CentOS 64 位_4
192.168.23.134

    NAT最好配置成固定IP,如果使用DHCP自动获取,IP启动时可能会改变,而节点配置时要指定IP,如果IP变动会很麻烦。


3. 安装与配置

3.1 上传安装包

到/root/software下,然后解压,拷贝到/usr/local/mysql下

cd /root/software/
tar -xzvf mysql-cluster-gpl-7.4.6-linux-glibc2.5-i686.tar.gz
mv mysql-cluster-gpl-7.4.6-linux-glibc2.5-i686 /usr/local/mysql

3.2 关闭防火墙

修改selinux安全策略

vim /etc/selinux/config

修改其中的启用标记为disable

SELINUX=disabled 

之后重启系统。

其他几个虚拟机也是类似的操作。

3.3 配置管理节点(192.168.23.130)

配置config.ini配置文件

mkdir /var/lib/mysql-cluster 
cd /var/lib/mysql-cluster 
gedit config.ini 

配置文件config.ini内容如下:

[ndbd default]
NoOfReplicas=2
DataMemory=88M
IndexMemory=18M
[ndb_mgmd]
NodeId=1
hostname=192.168.23.130
datadir=/var/lib/mysql-cluster
[ndbd]
NodeId=2
hostname=192.168.23.131
datadir=/usr/local/mysql/data
[ndbd]
NodeId=3
hostname=192.168.23.132
datadir=/usr/local/mysql/data
[mysqld]
NodeId=4
hostname=192.168.23.133
[mysqld]
NodeId=5
hostname=192.168.23.134

安装管理节点

安装管理节点,不需要mysqld二进制文件,只需要MySQL Cluster服务端程序(ndb_mgmd)和监听客户端程序(ndb_mgm)。在shell中运行以下命令:

cp /usr/local/mysql/bin/ndb_mgm* /usr/local/bin 
cd /usr/local/bin 
chmod +x ndb_mgm* 

3.4 配置数据节点(192.168.23.131,192.168.23.132)

添加mysql组和用户

groupadd mysql 
useradd -g mysql mysql 

配置my.cnf配置文件

vim /etc/my.cnf 

配置文件my.cnf的内容如下:

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/sock/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
ndbcluster
ndb-connectstring=192.168.23.130

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqld_cluster]
ndb-connectstring=192.168.23.130

创建系统数据库
cd /usr/local/mysql 
mkdir sock 
scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

设置数据目录

chown -R root . 
chown -R mysql.mysql /usr/local/mysql/data 
chown -R mysql.mysql /usr/local/mysql/sock 
chgrp -R mysql . 

配置MySQL服务

cp support-files/mysql.server /etc/rc.d/init.d/ 
chmod +x /etc/rc.d/init.d/mysql.server 
chkconfig --add mysql.server 

3.5 配置SQL节点(192.168.23.133,192.168.23.134)

添加mysql组和用户

groupadd mysql 
useradd -g mysql mysql 

配置my.cnf配置文件

vim /etc/my.cnf

配置如下:

[client]
socket=/usr/local/mysql/sock/mysql.sock
[mysqld]
ndbcluster
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/sock/mysql.sock
ndb-connectstring=192.168.23.130
[mysql_cluster]
ndb-connectstring=192.168.23.130

创建数据库系统

cd /usr/local/mysql 
mkdir sock 
scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

设置数据目录

chown -R root . 
chown -R mysql.mysql /usr/local/mysql/data 
chown -R mysql.mysql /usr/local/mysql/sock
chgrp -R mysql . 
配置MySQL服务
cp support-files/mysql.server /etc/rc.d/init.d/ 
chmod +x /etc/rc.d/init.d/mysql.server 
chkconfig --add mysql.server 

4. 启动集群

注意启动顺序:首先是管理节点,然后是数据节点,最后是SQL节点。

4.1 启动管理结点

ndb_mgmd -f /var/lib/mysql-cluster/config.ini 

4.2 启动数据结点

首次启动,则需要添加--initial参数,以便进行NDB节点的初始化工作。在以后的启动过程中,则是不能添加该参数的,否则ndbd程序会清除在之前建立的所有用于恢复的数据文件和日志文件。

/usr/local/mysql/bin/ndbd --initial 

如果不是首次启动,则执行下面的命令。

/usr/local/mysql/bin/ndbd 

注意:笔者在此处曾遇到过无法与管理节点连通的问题:

Unable to connect with connect string: nodeid=0,localhost:1186
Retrying every 5 seconds. Attempts left

可以通过-c参数解决:

/usr/local/mysql/bin/ndbd -c 192.168.23.130

4.3 启动SQL结点

/usr/local/mysql/bin/mysqld_safe --user=mysql & 

4.4 启动测试

执行下面的命令

ndb_mgm -e show

将显示节点的状态

Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=2	@192.168.23.131  (mysql-5.6.37 ndb-7.4.16, Nodegroup: 0, *)
id=3	@192.168.23.132  (mysql-5.6.37 ndb-7.4.16, Nodegroup: 0)

[ndb_mgmd(MGM)]	1 node(s)
id=1	@192.168.23.130  (mysql-5.6.37 ndb-7.4.16)

[mysqld(API)]	2 node(s)
id=4	@192.168.23.133  (mysql-5.6.37 ndb-7.4.16)
id=5	@192.168.23.134  (mysql-5.6.37 ndb-7.4.16)


5 集群测试

5.1 通过控制台sql来测试

在sql节点,如192.168.23.133连接mysql服务,创建数据库和表

/usr/local/mysql/bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.37-ndb-7.4.16-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database mysql_cluster_test;
Query OK, 1 row affected (0.12 sec)

mysql> use mysql_cluster_test;
Database changed
mysql> CREATE TABLE `user` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(255) NOT NULL,PRIMARY KEY (`id`)) ENGINE=ndbcluster
    -> ;
Query OK, 0 rows affected (0.23 sec)

然后在另一个sql节点查看数据库和表有没有创建成功:

/usr/local/mysql/bin/mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.6.37-ndb-7.4.16-cluster-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql_cluster_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc user;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)     | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
我们也可以通过可视化客户端工具验证这一点:

6 集群关闭

6.1 关闭管理节点mgm和数据节点ndb

/usr/local/mysql/bin/ndb_mgm -e shutdown 
Connected to Management Server at: localhost:1186
3 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.


6.2 关闭sql节点

/etc/init.d/mysql.server stop
Shutting down MySQL......170817 08:17:17 mysqld_safe mysqld from pid file /usr/local/mysql/data/bogon.pid ended
 SUCCESS! 


7 jdbc测试

    为了测试mysql cluster是否稳定,写了几行java代码,循环插入数据测试

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Hello world!
 *
 */
public class App2 {
    public static void main(String[] args) {
        String url = "jdbc:mysql:loadbalance://192.168.23.133:3306,192.168.23.134:3306/c_test?roundRobinLoadBalance=true";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, "root", "admin");
            String sql = "insert into user (name) values(?)";
            PreparedStatement pstmt = connection.prepareStatement(sql);
            for (int i = 0; i < 1000000; i++) {
                System.out.println("-->" + i);
                pstmt.setString(1, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa");
                pstmt.executeUpdate();
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

    但数据走到60多万的时候,就报表满了

-->659947
java.sql.SQLException: The table 'user' is full
     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
     at com.mysql.jdbc.LoadBalancedMySQLConnection.execSQL(LoadBalancedMySQLConnection.java:155)
     at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)
     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)
     at com.zc.dbcluster.zc_dbcluster.App2.main(App2.java:23)
    可以查看当前的集群的状态:
ndb_mgm> all report memoryusage
Connected to Management Server at: localhost:1186
Node 2: Data usage is 95%(2432 32K pages of total 2560)
Node 2: Index usage is 54%(1281 8K pages of total 2336)
Node 3: Data usage is 94%(2429 32K pages of total 2560)
Node 3: Index usage is 54%(1280 8K pages of total 2336)
    可见数据节点已经到95%,报了表满的错误。
    在网上查找资料,因为mysql cluster使用的是NDB存储引擎,因此数据需要load到内存里,如果内存不够用,就会报表满错误,需要修改/var/lib/mysql-cluster/config.ini 文件,并将其中的DataMemory、
IndexMemory值改大。
    但是奇怪的是,将值改大重启后,并没有生效,原因是,如果修改了这个配置文件,再启动时,需要加--initial参数:
ndb_mgmd --initial -f /var/lib/mysql-cluster/config.ini

8 参考资料

MySQL集群搭建详解 - 51CTO.COM

mysql-cluster集群(亲测) - 这个名字想了很久~ - 博客园

配置mysql cluster集群 | Rootop 服务器运维与web架构

还没有评论!
54.156.93.60