多服务器MySQL cluster 配置

多服务器MySQL cluster 配置

前言

原来随笔才是原创啊……

之前尝试布置mysql的NDB集群，翻看了不少教程，但是有些问题其他人没有遇到，所以在解决了集群部署的问题后，将自己的笔记整理出来。

以下为配置说明：

  • 配置（四台服务器，最好之前没有mysql服务）：
    • 服务器192.168.5.70 管理节点、SQL节点
    • 服务器192.168.5.68 SQL节点
    • 服务器192.168.5.64 数据节点1
    • 服务器192.168.5.65 数据节点2

安装和启动

启动是有顺序的，先管理节点，后数据节点，最后是SQL节点。本文也是按照这个顺序安排的。

管理节点

  • 下载：我使用的是mysql-cluster-8.0.22-linux-glibc2.12-x86_64.tar.gz安装包，来源于官网。下载后上传到四台服务器上。因为使用的是内网，所以上传速度比较快。安装包大小在1G以上，可以闲暇时候下载。在官方的说明说部分节点（比如管理节点和SQL节点）不需要完整的包，只需要部分文件即可使用，但是为了方便，我都放的完整的包。另外我用的都是root用户，没有用root的小伙伴可能有的操作会需要使用sudo语句操作。这一点不确定，万一提示权限类的问题可以看看是不是这个原因。

  • 安装和授权：

# 当前的目录：/lanblue/mysql_cluster
# 添加用户和用户组
shell> groupadd mysql
shell> useradd -g mysql -s /bin/false mysql
# 安装并创建软链接
shell> tar -C /lanblue/mysql_cluster -xzvf mysql-cluster-8.0.22-linux-glibc2.12-x86_64.tar.gz
shell> ln -s /lanblue/mysql_cluster/mysql-cluster-8.0.22-linux-glibc2.12-x86_64 /lanblue/mysql_cluster/mysql

  • 配置管理节点:

    shell> mkdir /var/lib/mysql-cluster
shell> cd  /var/lib/mysql-cluster
# 启动语句（要先配好配置文件）
# 我使用的是全路径名，有时候不全路径会报错
shell> /lanblue/mysql_cluster/mysql/bin/ndb_mgmd --ndb-nodeid=1 --configdir=/var/lib/mysql-cluster --config-file=/var/lib/mysql-cluster/config.ini --initial

  • 配置文件 /var/lib/mysql-cluster/config.ini如下。会多配置一个mysql节点，用作轮询。主要还是因为不配置的话会报错……：

    [ndbd default]
NoOfReplicas= 1
DataMemory=512M

[ndb_mgmd]
HostName= 192.168.5.70
NodeId=1
DataDir= /var/lib/mysql-cluster

[ndbd]
HostName= 192.168.5.64
NodeId=11
DataDir= /lanblue/mysql_cluster/mysql/data

[ndbd]
HostName= 192.168.5.65
NodeId=12
DataDir= /lanblue/mysql_cluster/mysql/data

[mysqld]
NodeId=21
HostName=192.168.5.70
[mysqld]
NodeId=22
HostName=192.168.5.68
[mysqld]

  • 结果展示：

    shell> /lanblue/mysql_cluster/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	1 node(s)
id=11 (not connected, accepting connect from 192.168.5.64)
id=12 (not connected, accepting connect from 192.168.5.65)

[ndb_mgmd(MGM)]	1 node(s)
id=1	@192.168.5.70  (mysql-8.0.22 ndb-8.0.22)

[mysqld(API)]	3 node(s)
id=21 (not connected, accepting connect from 192.168.5.70)
id=22 (not connected, accepting connect from 192.168.5.68)
id=23 (not connected, accepting connect from any host)
数据节点64【65同理】

  • 创建目录并初始化：

    # 切换目录并初始化
shell> cd /lanblue/mysql_cluster/mysql

# 赋值和授权
shell> chown -R root .
shell> mkdir data
shell> chown -R mysql data
shell> chgrp -R mysql .

# 继续 
shell> cd bin
shell> chmod +x ndb*
shell> chmod +x ndb_mgm*
shell> chmod +x mysqld

  • 数据节点的/lanblue/mysql_cluster/my.cnf如下，其中basedir是我们按照数据节点的地方：

    [mysqld]
datadir=/lanblue/mysql_cluster/mysql/data
basedir=/lanblue/mysql_cluster/mysql
character_set_server=utf8

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

[mysql_cluster]
ndb-connectstring=192.168.5.70:1186

  • 初始化数据节点 【之后记得设置密码】：

    # 初始化数据库一定要指定新的basedir和datadir，否则会和之前程序冲突
# 我用的--initialize-insecure参数可以暂时不设置密码。
shell> /lanblue/mysql_cluster/mysql/bin/mysqld --defaults-file=/lanblue/mysql_cluster/my.cnf --user=mysql --initialize-insecure
2020-12-15T08:37:22.009481Z 0 [System] [MY-013169] [Server] /lanblue/mysql_cluster/mysql/bin/mysqld (mysqld 8.0.22-cluster) initializing of server in progress as process 447808
2020-12-15T08:37:22.011212Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2020-12-15T08:37:22.022325Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-15T08:37:27.205436Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-15T08:37:33.186945Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

#加入到service服务
shell> cp /lanblue/mysql_cluster/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
 
#加入到开机自启动列表
chkconfig --add mysqld

# 启动节点，指定配置文件
# 如果是第一次启动要加上参数 --initial
# 之后启动这样就行：/lanblue/mysql_cluster/mysql/bin/ndbd --defaults-file=/lanblue/mysql_cluster/my.cnf 
# 结果展示
shell> /lanblue/mysql_cluster/mysql/bin/ndbd --defaults-file=/lanblue/mysql_cluster/my.cnf --initial
2020-12-18 15:17:20 [ndbd] INFO     -- Angel connected to '192.168.5.70:1186'
2020-12-18 15:17:20 [ndbd] INFO     -- Angel allocated nodeid: 11
SQL节点

  • 创建目录并初始化，注意data1文件一定要干干净净：

    # 切换目录并初始化
shell> cd /lanblue/mysql_cluster/mysql

# 赋值和授权
shell> chown -R root .
shell> mkdir data1
shell> chown -R mysql data1
shell> chgrp -R mysql .

# 继续授权
shell> cd bin
shell> chmod +x ndb*
shell> chmod +x ndb_mgm*
shell> chmod +x mysqld

  • 配置文件/lanblue/mysql_cluster/mysql/my.cnf：

    [mysqld]
ndbcluster
datadir=/lanblue/mysql_cluster/mysql/data1
basedir=/lanblue/mysql_cluster/mysql
character_set_server=utf8
default-storage-engine=ndbcluster
port=3306
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
[mysql_cluster]
ndb-connectstring=192.168.5.70:1186

  • 配置启动和管理，记得SQL节点也是要初始化的啊QAQ：

    # 成功初始化。之前SQL节点一直无法连入的原因就是没有初始化而直接启动。
shell> /lanblue/mysql_cluster/mysql/bin/mysqld --defaults-file=/lanblue/mysql_cluster/mysql/my.cnf --user=mysql --initialize-insecure
2020-12-18T07:30:32.957110Z 0 [System] [MY-013169] [Server] /lanblue/mysql_cluster/mysql/bin/mysqld (mysqld 8.0.22-cluster) initializing of server in progress as process 329971
2020-12-18T07:30:32.958871Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2020-12-18T07:30:32.968475Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-18T07:30:36.979971Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-18T07:30:42.296339Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

# 启动不需要初始化
shell> /lanblue/mysql_cluster/mysql/bin/mysqld --defaults-file=/lanblue/mysql_cluster/mysql/my.cnf --user=mysql

# 加入启动项，估计这个是为了直接使用服务名启动。不确定，我使用全路径的话暂时没有发现他的作用。
shell> cp support-files/mysql.server /etc/rc.d/init.d/sql.server
shell>  chmod +x /etc/rc.d/init.d/sql.server
shell> chkconfig --add sql.server

  • 查看结果：

    ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]	1 node(s)
id=11	@192.168.5.64  (mysql-8.0.22 ndb-8.0.22, Nodegroup: 0, *)
id=12	@192.168.5.65  (mysql-8.0.22 ndb-8.0.22, Nodegroup: 0)

[ndb_mgmd(MGM)]	1 node(s)
id=1	@192.168.5.70  (mysql-8.0.22 ndb-8.0.22)

[mysqld(API)]	3 node(s)
id=21	@192.168.5.70  (mysql-8.0.22 ndb-8.0.22)
id=22	@192.168.5.68  (mysql-8.0.22 ndb-8.0.22)
id=23 (not connected, accepting connect from any host)
新建数据库并增加测试数据

  • 连接SQL节点：

    shell> /lanblue/mysql_cluster/mysql/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.22-cluster MySQL Cluster Community Server - GPL

Copyright (c) 2000, 2020, 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.

  • 创建用户（对每个SQL节点执行，因为SQL节点的用户特权是不共享的）：

    CREATE USER 'lanblue'@'%' IDENTIFIED BY 'lanblue123';

GRANT ALL ON *.* TO 'lanblue'@'%';

# 使用远程客户端连接可能会报错，可以使用接下来的语句们解决此问题。
# client does not support authentication protocol requested by server
ALTER USER 'lanblue'@'%' IDENTIFIED WITH mysql_native_password BY 'lanblue123';

# authentication plugin 'sha256_password' cannot be loaded
ALTER USER 'lanblue'@'%' IDENTIFIED BY 'lanblue123' PASSWORD EXPIRE NEVER;   #修改加密规则 
ALTER USER 'lanblue'@'%' IDENTIFIED WITH mysql_native_password BY 'lanblue123';   #更新一下用户的密码 
FLUSH PRIVILEGES;   #刷新权限 
ALTER USER 'lanblue'@'%' IDENTIFIED BY 'lanblue123';

  • 创建表并加入1000条数据（使用Java）

    # 建数据库并选择
mysql> create database mytest;
Query OK, 1 row affected (0.16 sec)
mysql> use mytest;

# 建表：
create table `student` (
`name` varchar(200) PRIMARY KEY,
`goal` int not null);

# 插入数据

  • 查看表的情况

    # 查看数据量
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

# 查看information_schema.partitions表 
select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
  from information_schema.partitions 
where 
 table_name='student';  
 
 +------+------+-------+------------+
| part | expr | descr | TABLE_ROWS |
+------+------+-------+------------+
| p0   | NULL | NULL  |       1000 |
+------+------+-------+------------+
1 row in set (0.00 sec)
posted @ 2020-12-22 23:05  蓝步芦  阅读(274)  评论(0)    收藏  举报