目录
4. 安装数据节点和sql节点(192.168.17.83)
5. 安装数据节点和sql节点(192.168.17.85)
mysql-cluster(集群)提供多节点读写能力,对于具有大量读写请求的应用场景来说,选择集群部署,相对于单节点部署有明显的优势,尤其是读写请求会随着业务发展增加的场景,更是如此,在开始的时候,读写请求不是很大的情况下,可以用两个节点,当读写请求增加以后,可以增加节点来满足业务需求。
1. 下载安装包
下载地址:
MySQL :: Download MySQL Cluster
这里选择下载64位压缩版本。
2. 集群基本配置
操作系统:CentOS Linux release 7.9
软件包名:mysql-cluster-gpl-7.6.22-linux-glibc2.12-x86_64.tar.gz
管理节点:192.168.17.81
数据节点和sql节点:192.168.17.83
数据节点和sql节点:192.168.17.85
3. 安装管理节点(192.168.17.81)
3.1 安装准备
上传安装包到linux,执行下面的操作。
groupadd mysql
useradd mysql -g mysql
tar xvf mysql-cluster-gpl-7.6.22-linux-glibc2.12-x86_64.tar.gz
mv mysql-cluster-gpl-7.6.22-linux-glibc2.12-x86_64 /usr/local/mysql
3.2 配置信息
创建mysql-cluster目录,并配置config.ini
mkdir -p /apps/mysql/mysql-cluster
cd /apps/mysql/mysql-cluster
vi config.ini
配置文件内容:
[ndbd default]
NoOfReplicas=2
DataMemory=500M
IndexMemory=300M
[ndb_mgmd]
NodeId=11
hostname=192.168.17.81
datadir=/apps/mysql/mysql-cluster
[ndbd]
NodeId=12
hostname=192.168.17.83
datadir=/apps/mysql/data
[ndbd]
NodeId=22
hostname=192.168.17.85
datadir=/apps/mysql/data
[mysqld]
NodeId=13
hostname=192.168.17.83
[mysqld]
NodeId=23
hostname=192.168.17.85
管理节点初始化:
./ndb_mgmd -f /apps/mysql/mysql-cluster/config.ini --initial
从管理节点查看信息:
# ./ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=12 (not connected, accepting connect from 192.168.17.83)
id=22 (not connected, accepting connect from 192.168.17.85)
[ndb_mgmd(MGM)] 1 node(s)
id=11 @192.168.17.81 (mysql-5.7.38 ndb-7.6.22)
[mysqld(API)] 2 node(s)
id=13 (not connected, accepting connect from 192.168.17.83)
id=23 (not connected, accepting connect from 192.168.17.85)
4. 安装数据节点和sql节点(192.168.17.83)
在192.168.17.83部署数据节点和sql节点:
4.1 安装准备
上传安装包到linux,执行下面的操作。
groupadd mysql
useradd mysql -g mysql
tar xvf mysql-cluster-gpl-7.6.22-linux-glibc2.12-x86_64.tar.gz
mv mysql-cluster-gpl-7.6.22-linux-glibc2.12-x86_64 /usr/local/mysql
4.2 安装数据节点
vi /etc/my.cnf
内容如下:
# 数据节点配置
[myqld]
basedir=/usr/local/mysql
datadir=/apps/mysql/data/ndbdata
user=mysql
socket=/apps/mysql/data/ndbsock/mysql.sock
symbolic-links=0
[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql_cluster]
ndb-connectstring=192.168.17.81
初始化并启动ndb节点:
./ndbd --initial
2022-06-21 16:44:09 [ndbd] INFO -- Angel connected to '192.168.17.81:1186'
2022-06-21 16:44:09 [ndbd] INFO -- Angel allocated nodeid: 12
可以看到ndb节点连接到集群了。
4.3 安装sql节点
配置文件my.cnf
# SQL节点配置
[client]
socket=/apps/mysql/data/sqlsock/mysql.sock
[mysqld]
ndbcluster
datadir=/apps/mysql/data/sqldata
socket=/apps/mysql/data/sqlsock/mysql.sock
ndb-connectstring=192.168.17.81
[mysql_cluster]
ndb-connectstring=192.168.17.81
初始化mysql
# ./mysqld --user=root --initialize
2022-06-21T08:46:45.758523Z 0 [Warning] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line
2022-06-21T08:46:45.765892Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-06-21T08:46:45.765987Z 0 [ERROR] Can't find error-message file '/apps/mysql/data/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2022-06-21T08:46:47.892484Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-06-21T08:46:48.340240Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-06-21T08:46:48.529587Z 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: b0091a7a-f13e-11ec-88ab-f8bc127be655.
2022-06-21T08:46:48.553733Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-06-21T08:46:48.902557Z 0 [Warning]
2022-06-21T08:46:48.902576Z 0 [Warning]
2022-06-21T08:46:48.903104Z 0 [Warning] CA certificate ca.pem is self signed.
2022-06-21T08:46:49.000295Z 1 [Note] A temporary password is generated for root@localhost: 78nj;Ar
可以看到mysql初始化成功,并生成了临时密码。
启动sql节点,首先从安装包中复制启动脚本到/etc/init.d/mysqld,然后使用service mysqld start启动mysql服务。
cp support-files/mysql.server /etc/init.d/mysqld
# 加入到自启动服务项中
chkconfig --add mysqld
#启动服务
service mysqld start
5. 安装数据节点和sql节点(192.168.17.85)
安装和配置方法参考前一小节。
5.1 安装准备
上传安装包到linux,执行下面的操作。
groupadd mysql
useradd mysql -g mysql
tar xvf mysql-cluster-gpl-7.6.22-linux-glibc2.12-x86_64.tar.gz
mv mysql-cluster-gpl-7.6.22-linux-glibc2.12-x86_64 /usr/local/mysql
5.2 安装数据节点
vi /etc/my.cnf
内容如下:
# 数据节点配置
[myqld]
basedir=/usr/local/mysql
datadir=/apps/mysql/data/ndbdata
user=mysql
socket=/apps/mysql/data/ndbsock/mysql.sock
symbolic-links=0
[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql_cluster]
ndb-connectstring=192.168.17.81
初始化并启动ndb节点:
./ndbd --initial
2022-06-21 16:44:09 [ndbd] INFO -- Angel connected to '192.168.17.81:1186'
2022-06-21 16:44:09 [ndbd] INFO -- Angel allocated nodeid: 12
可以看到ndb节点连接到集群了。
5.3 安装sql节点
配置文件my.cnf
# SQL节点配置
[client]
socket=/apps/mysql/data/sqlsock/mysql.sock
[mysqld]
ndbcluster
datadir=/apps/mysql/data/sqldata
socket=/apps/mysql/data/sqlsock/mysql.sock
ndb-connectstring=192.168.17.81
[mysql_cluster]
ndb-connectstring=192.168.17.81
初始化mysql
# ./mysqld --user=root --initialize
2022-06-21T08:46:45.758523Z 0 [Warning] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line
2022-06-21T08:46:45.765892Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-06-21T08:46:45.765987Z 0 [ERROR] Can't find error-message file '/apps/mysql/data/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2022-06-21T08:46:47.892484Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-06-21T08:46:48.340240Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-06-21T08:46:48.529587Z 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: b0091a7a-f13e-11ec-88ab-f8bc127be655.
2022-06-21T08:46:48.553733Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-06-21T08:46:48.902557Z 0 [Warning]
2022-06-21T08:46:48.902576Z 0 [Warning]
2022-06-21T08:46:48.903104Z 0 [Warning] CA certificate ca.pem is self signed.
2022-06-21T08:46:49.000295Z 1 [Note] A temporary password is generated for root@localhost: 78nj;Ar
可以看到mysql初始化成功,并生成了临时密码。
启动sql节点,首先从安装包中复制启动脚本到/etc/init.d/mysqld,然后使用service mysqld start启动mysql服务。
cp support-files/mysql.server /etc/init.d/mysqld
# 加入到自启动服务项中
chkconfig --add mysqld
#启动服务
service mysqld start
6. 启动集群并执行测试
6.1 启动测试
启动顺序:启动管理节点,启动数据节点,启动sql节点。
启动完成后,在管理节点查看:
# ./ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=12 @192.168.17.83 (mysql-5.7.38 ndb-7.6.22, Nodegroup: 0, *)
id=22 @192.168.17.85 (mysql-5.7.38 ndb-7.6.22, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=11 @192.168.17.81 (mysql-5.7.38 ndb-7.6.22)
[mysqld(API)] 2 node(s)
id=13 @192.168.17.83 (mysql-5.7.38 ndb-7.6.22)
id=23 @192.168.17.85 (mysql-5.7.38 ndb-7.6.22)
可以看到,两个ndb节点和两个sql节点,都已经连接到管理节点。
6.2 创建数据库测试
在17.83节点创建数据库:
mysql> create database ndb_test;
Query OK, 1 row affected (0.01 sec)
在17.85查看数据库:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ndb_test |
说明数据库的创建是可以自动同步的。
6.3 创建表测试
在17.85创建表ndb_table_test1:
mysql> use ndb_test
Database changed
mysql>
mysql> create table ndb_table_test1(id int);
Query OK, 0 rows affected (0.17 sec)
在17.83查看表ndb_table_test1:
mysql> use ndb_test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
发现在17.85创建的表,在17.83是看不到的。
再次在17.85创建表ndb_table_test2,指定引擎NDBCLUSTER:
mysql> create table ndb_table_test2(id int) ENGINE = NDBCLUSTER;
Query OK, 0 rows affected (0.39 sec)
在17.83查看表:
mysql> show tables;
+--------------------+
| Tables_in_ndb_test |
+--------------------+
| ndb_table_test2 |
+--------------------+
1 row in set (0.00 sec)
这次可以看到创建的表了,所以只有指定了NDBCLUSTER引擎的表,才能在别的节点可见。
6.4 添加数据测试
在17.83添加数据:
mysql> insert into ndb_table_test2 values(1);
Query OK, 1 row affected (0.00 sec)
在17.85查看数据:
mysql> select * from ndb_table_test2
-> ;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
可以看到,一个节点写入的数据,另外一个节点可以看到写入的数据了。
7. 安装部署过程中遇到的问题和解决办法
7.1 防火墙阻止了数据节点与管理节点的通讯
需要管理节点的防火墙开放端口1186。
# firewall-cmd --zone=public --add-port=1186/tcp --permanent
success
# firewall-cmd --reload
success
7.2 防火墙阻止了sql节点与管理节点的通讯
开放sql节点3306端口
# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
# firewall-cmd --reload
success
7.3 sql节点启动后,需要重置密码
使用临时密码登录后,需要重置密码才能进行访问。
./bin/mysql -uroot -p
set password = password('newpassword');
7.4 mysql远程访问授权
use mysql
update user set host='%' where user = 'root';
flush privileges;
7.5 目录不存在或没有权限
有些配置的目录需要手动创建,创建目录需要mysql能够访问。启动报错的时候,注意查看对应的报错信息。
7.6 数据节点和sql节点共用配置文件
配置文件/etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/apps/mysql/data/ndbdata
user=mysql
socket=/apps/mysql/data/ndbsock/mysql.sock
symbolic-links=0
[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql_cluster]
ndb-connectstring=192.168.17.81
# SQL节点配置
[client]
socket=/apps/mysql/data/sqlsock/mysql.sock
[mysqld]
ndbcluster
datadir=/apps/mysql/data/sqldata
socket=/apps/mysql/data/sqlsock/mysql.sock
ndb-connectstring=192.168.17.81
[mysql_cluster]
ndb-connectstring=192.168.17.81