mysql ndbcluster集群安装以及遇到问题

你的名字 2022-08-18 12:06 262阅读 0赞

mysql ndbcluster 安装  
  
1.数据库规划：  
操作系统 centos 6.3  
ndbcluster 版本：MySQL-Cluster-gpl-7.4.8-1.el6.x86\_64  
2个管理节点、2个sql节点、2个数据节点（管理节点和sql节点放在同一台服务器上）  
如下：  
管理节点1 192.168.1.17     
管理节点2 192.168.1.18  
  
sql节点1 192.168.1.17  
sql节点2 192.168.1.18  
ndbd 节点1 192.168.1.19    
ndbd 节点2 192.168.1.20    
  
2.操作系统设置：  
设置服务主机名称  
关闭selinux和防火墙  
设置内核参数和最大进程数  
  
下载mysql集群  
MySQL-Cluster-gpl-7.4.8-1.el6.x86\_64.rpm-bundle.tar  
上传到服务器的相应目录下如 /home/mysqlinstall  
  
3.安装管理节点和sql节点：  
To check if your system has any RPM version of any MySQL package currently installed, run:  
shell> rpm -qa | grep -i mysql  
检查  
\[root@redis1 ~\]\#  rpm -qa | grep -i mysql  
mysql-libs-5.1.61-4.el6.x86\_64  
如果有删除  
shell> rpm -e mysql-libs-5.1.61-4.el6.x86\_64  
删除报错：  
\[root@redis1 ~\]\# rpm -e mysql-libs-5.1.61-4.el6.x86\_64  
error: Failed dependencies:  
        libmysqlclient.so.16()(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6\_1.x86\_64  
        libmysqlclient.so.16(libmysqlclient\_16)(64bit) is needed by (installed) postfix-2:2.6.6-2.2.el6\_1.x86\_64  
        mysql-libs is needed by (installed) postfix-2:2.6.6-2.2.el6\_1.x86\_64  
\[root@redis1 ~\]\#   
解决：  
\[root@redis1 ~\]\# rpm -e --nodeps mysql-libs-5.1.61-4.el6.x86\_64  
\[root@redis1 ~\]\#   
  
\[root@ndbcluster1 mysqlinstall\]\# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86\_64.rpm   
\[root@ndbcluster1 mysqlinstall\]\# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86\_64.rpm   
  
4.安装数据节点：  
执行上面步骤  
\[root@ndb mysqlinstall\]\# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86\_64.rpm   
\[root@ndb mysqlinstall\]\# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86\_64.rpm   
  
5.配置集群  
管理节点建立目录  
mkdir /var/lib/mysql-cluster  
mkdir -p /data/mysql/ndbdata  
  
管理节点配置文件config.ini：  
\# TCP PARAMETERS  
\[tcp default\]  
SendBufferMemory=20M  
ReceiveBufferMemory=20M  
\# Increasing the sizes of these 2 buffers beyond the default values  
\# helps prevent bottlenecks due to slow disk I/O.  
\# MANAGEMENT NODE PARAMETERS  
\[ndb\_mgmd default\]  
DataDir=/data/mysql-cluster  
\# It is possible to use a different data directory for each management  
\# server, but for ease of administration it is preferable to be  
\# consistent.  
\[ndb\_mgmd\]  
NodeId=1  
HostName=192.168.1.17  
\# NodeId=management-server-A-nodeid  
\[ndb\_mgmd\]  
NodeId=2  
HostName=192.168.1.18  
\[ndbd default\]  
NoOfReplicas=2  
\# Using 2 replicas is recommended to guarantee availability of data;  
\# using only 1 replica does not provide any redundancy, which means  
\# that the failure of a single data node causes the entire cluster to  
\# shut down. We do not recommend using more than 2 replicas, since 2 is  
\# sufficient to provide high availability, and we do not currently test  
\# with greater values for this parameter.  
LockPagesInMainMemory=1  
\# On Linux and Solaris systems, setting this parameter locks data node  
\# processes into memory. Doing so prevents them from swapping to disk,  
\# which can severely degrade cluster performance.  
DataMemory=2000M  
IndexMemory=800M  
\# The values provided for DataMemory and IndexMemory assume 4 GB RAM  
\# per data node. However, for best results, you should first calculate  
\# the memory that would be used based on the data you actually plan to  
\# store (you may find the ndb\_size.pl utility helpful in estimating  
\# this), then allow an extra 20% over the calculated values. Naturally,  
\# you should ensure that each data node host has at least as much  
\# physical memory as the sum of these two values.  
\# ODirect=1  
\# Enabling this parameter causes NDBCLUSTER to try using O\_DIRECT  
\# writes for local checkpoints and redo logs; this can reduce load on  
\# CPUs. We recommend doing so when using MySQL Cluster on systems running  
\# Linux kernel 2.6 or later.  
NoOfFragmentLogFiles=600  
MaxNoOfConcurrentOperations=100000  
MaxNoOfLocalOperations=110000  
SchedulerSpinTimer=400  
SchedulerExecutionTimer=100  
RealTimeScheduler=1  
\# Setting these parameters allows you to take advantage of real-time scheduling  
\# of NDB threads to achieve increased throughput when using ndbd. They  
\# are not needed when using ndbmtd; in particular, you should not set  
\# RealTimeScheduler for ndbmtd data nodes.  
TimeBetweenGlobalCheckpoints=1000  
TimeBetweenEpochs=200  
\#DiskCheckpointSpeed=20M  
\#DiskCheckpointSpeedInRestart=100M  
MaxNoOfConcurrentScans=500  
RedoBuffer=32M  
\# CompressedLCP=1  
\# CompressedBackup=1  
\# Enabling CompressedLCP and CompressedBackup causes, respectively, local  
\#checkpoint files and backup files to be compressed, which can result in a space  
\#savings of up to 50% over noncompressed LCPs and backups.  
MaxNoOfLocalScans=1000  
MaxNoOfTables=4096  
MaxNoOfOrderedIndexes=2048  
MaxNoOfExecutionThreads=4  
TransactionDeadLockDetectionTimeOut=30000  
HeartbeatIntervalDbDb=5000  
HeartbeatIntervalDbApi=5000  
MaxNoOfConcurrentTransactions=4096  
MaxNoOfConcurrentIndexOperations=30000  
MaxNoOfFiredTriggers=10000  
MaxNoOfAttributes=600000  
MaxNoOfUniqueHashIndexes=512  
\[ndbd\]  
Nodeid=3  
HostName=192.168.1.19  
DataDir=/data/mysql/ndbdata  
\# NodeId=data-node-A-nodeid  
\#LockExecuteThreadToCPU=1  
\#LockMaintThreadsToCPU=0  
\# On systems with multiple CPUs, these parameters can be used to lock NDBCLUSTER  
\# threads to specific CPUs  
\[ndbd\]  
Nodeid=4  
HostName=192.168.1.20  
DataDir=/data/mysql/ndbdata  
\# NodeId=data-node-B-nodeid  
\#LockExecuteThreadToCPU=1  
\#LockMaintThreadsToCPU=0  
\# You must have an \[ndbd\] section for every data node in the cluster;  
\# each of these sections must include a HostName. Each section may  
\# optionally include a NodeId for convenience, but in most cases, it is  
\# sufficient to allow the cluster to allocate node IDs dynamically. If  
\# you do specify the node ID for a data node, it must be in the range 1  
\# to 48 inclusive and must be unique among all IDs specified for  
\# cluster nodes.  
\# SQL NODE / API NODE PARAMETERS  
\[mysqld\]  
Nodeid=5  
HostName=192.168.1.17  
\[mysqld\]  
Nodeid=6  
HostName=192.168.1.18  
\[mysqld\]  
Nodeid=7  
\[mysqld\]  
Nodeid=8  
  
sql节点和数据节点配置文件/etc/my.cnf  
\[mysqld\]  
\# Options for mysqld process:  
ndbcluster                      \#run NDB storage engine  
\#skip-grant-tables  
character\_set\_server=utf8  
lower\_case\_table\_names=1  
max\_connections=1000  
ndb-connectstring=192.168.1.17,192.168.1.18  \# location of management server  
default-storage-engine=ndbcluster  
datadir=/data/mysql  
\# provide connection string for management server host (default port: 1186)  
\[ndbd\]  
connect-string=192.168.1.17,192.168.1.18  \# location of management server  
\#provide connection string for management server host (default port: 1186)  
\[ndb\_mgm\]  
connect-string=192.168.1.17,192.168.1.18  \# location of management server  
\#provide location of cluster configuration file  
\[ndb\_mgmd\]  
config-file=/var/lib/mysql-cluster/config.ini  
  
5.启动关闭集群：  
启动管理节点  
ndb\_mgmd -f /var/lib/mysql-cluster/config.ini --initial (第一次启动加--initial修改了配置文件后的启动)  
  
启动数据节点  
ndbmtd --initial  
  
启动sql节点  
service mysql start  
  
查看集群状态  
shell> ndb\_mgm -e "SHOW"  
  
关闭管理节点和数据节点  
ndb\_mgm -e shutdown  
关闭sql节点  
service mysql stop  
  
安装遇到的问题：  
1.权限问题启动mysql报错  
\[root@sqlmgm2 mysql\]\# service mysql start  
Starting MySQL..The server quit without updating PID file (\[FAILED\]sql/sqlmgm2.pid).  
\[root@sqlmgm2 mysql\]\#   
  
\[root@sqlmgm2 mysql\]\# ll  
total 110644  
\-rw-rw---- 1 mysql mysql       56 Dec 14 17:13 auto.cnf  
\-rw-rw---- 1 mysql mysql 12582912 Dec 14 17:39 ibdata1  
\-rw-rw---- 1 mysql mysql 50331648 Dec 14 17:40 ib\_logfile0  
\-rw-rw---- 1 mysql mysql 50331648 Dec 14 17:13 ib\_logfile1  
drwx------ 2 mysql mysql     4096 Dec 14 17:39 mysql  
drwx------ 2 root  root      4096 Dec 14 17:39 ndbinfo  
drwx------ 2 root  root      4096 Dec 14 17:39 performance\_schema  
\-rw-r----- 1 mysql mysql    30068 Dec 14 17:40 sqlmgm2.err  
drwx------ 2 root  root      4096 Dec 14 17:33 test  
\[root@sqlmgm2 mysql\]\# chown mysql:mysql -R mysql  
\[root@sqlmgm2 mysql\]\# service mysql start  
Starting MySQL..                                           \[  OK  \]  
\[root@sqlmgm2 mysql\]\#   
  
2.安装rpm包时，报包依赖关系如下：  
\[root@ndbcluster2 mysqlinstall\]\# rpm -ivh MySQL-Cluster-server-gpl-7.4.8-1.el6.x86\_64.rpm   
warning: MySQL-Cluster-server-gpl-7.4.8-1.el6.x86\_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY  
error: Failed dependencies:  
        libnuma.so.1()(64bit) is needed by MySQL-Cluster-server-gpl-7.4.8-1.el6.x86\_64  
        libnuma.so.1(libnuma\_1.1)(64bit) is needed by MySQL-Cluster-server-gpl-7.4.8-1.el6.x86\_64  
        libnuma.so.1(libnuma\_1.2)(64bit) is needed by MySQL-Cluster-server-gpl-7.4.8-1.el6.x86\_64         
解决方法是把安装的依赖的包  
\[root@ndbcluster2 mysqlinstall\]\# yum install numactl  
Loaded plugins: fastestmirror, refresh-packagekit, security  
Loading mirror speeds from cached hostfile  
 \* base: mirrors.163.com  
 \* extras: mirrors.opencas.cn  
 \* updates: mirrors.opencas.cn  
Setting up Install Process  
Resolving Dependencies  
\--> Running transaction check  
\---> Package numactl.x86\_64 0:2.0.9-2.el6 will be installed  
\--> Finished Dependency Resolution  
Dependencies Resolved  
==============================================================================================================================  
 Package                      Arch                        Version                             Repository                 Size  
==============================================================================================================================  
Installing:  
 numactl                      x86\_64                      2.0.9-2.el6                         base                       74 k  
Transaction Summary  
==============================================================================================================================  
Install       1 Package(s)  
Total download size: 74 k  
Installed size: 171 k  
Is this ok \[y/N\]: y  
Downloading Packages:  
numactl-2.0.9-2.el6.x86\_64.rpm                                                                         |  74 kB     00:00       
warning: rpmts\_HdrFromFdno: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY  
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6  
Importing GPG key 0xC105B9DE:  
 Userid : CentOS-6 Key (CentOS 6 Official Signing Key) <centos-6-key@centos.org>  
 Package: centos-release-6-3.el6.centos.9.x86\_64 (@anaconda-CentOS-201207061011.x86\_64/6.3)  
 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6  
Is this ok \[y/N\]: y  
Running rpm\_check\_debug  
Running Transaction Test  
Transaction Test Succeeded  
Running Transaction  
  Installing : numactl-2.0.9-2.el6.x86\_64                                                                                 1/1   
  Verifying  : numactl-2.0.9-2.el6.x86\_64                                                                                 1/1   
Installed:  
  numactl.x86\_64 0:2.0.9-2.el6                                                                                                  
Complete!  
  
3.启动数据库节点无法和管理节点相连  
原因由于防火墙和selinux没有关闭  
解决关闭防火墙和selinux  
一、关闭防火墙  
1、重启后永久性生效：  
开启：chkconfig iptables on  
关闭：chkconfig iptables off  
2、即时生效，重启后失效：  
开启：service iptables start  
关闭：service iptables stop  
二、关闭SELinux  
vim /etc/selinux/config  \# 改为 SELINUX=disabled  
\# 保存退出，重启服务器  
init 6  
禁用SeLinux  
\#永久禁用，需要重启生效。  
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config    
\# 临时禁用，不需要重启  
setenforce 0    
  
4.root用户不能登录  
\[root@ndbcluster2 ~\]\# mysql -uroot -p  
Enter password:   
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)  
\[root@ndbcluster2 ~\]\# mysql -uroot -p  
Enter password:   
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)  
  
设置--skip-grant-tables跳过授权表认证  
service mysqld stop  
在配置文件中添加--skip-grant-tables（/etc/my.cnf）  
service mysqld start  
另外开个SSH连接  
\[root@localhost ~\]\# mysql  
mysql>use mysql  
mysql>update user set password=password("123456") where user="root";  
mysql>flush privileges;  
mysql>exit  
去掉--skip-grant-tables重启mysql,root用户可以用设置的密码登录  
  
设置root用户远程登录  
GRANT ALL PRIVILEGES ON \*.\* TO root@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;   
执行报如下错误，ERROR 1820 (HY000): You must SET PASSWORD before executing this statement  
\[root@ndbcluster1 ~\]\# mysql -uroot -p  
Enter password:   
Welcome to the MySQL monitor.  Commands end with ; or \\g.  
Your MySQL connection id is 2  
Server version: 5.6.27-ndb-7.4.8-cluster-gpl  
Copyright (c) 2000, 2015, 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>   
mysql>   
mysql> GRANT ALL PRIVILEGES ON \*.\* TO root@"%" IDENTIFIED BY 'root' WITH GRANT OPTION;  
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement  
mysql> GRANT ALL PRIVILEGES ON \*.\* TO root@"%" IDENTIFIED BY 'root001' WITH GRANT OPTION;  
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement  
  
执行SET PASSWORD修改成功  
mysql> SET PASSWORD = PASSWORD('root001');  
Query OK, 0 rows affected (0.02 sec)  
mysql>  GRANT ALL PRIVILEGES ON \*.\* TO root@"%" IDENTIFIED BY 'root' WITH GRANT OPTION;  
Query OK, 0 rows affected (0.02 sec)  
mysql>   
FLUSH PRIVILEGES;  
  
执行完成  
远程连接测试，mysql集群节点，发现一个节点可以连接，一个节点不能连接  
这个问题纠结很久  
原因上面的操作，没有在另外一个节点上执行导致的，mysql集群的用户认证是分开管理的，要两个节点都要执行  
索引以后创建用户要两个节点都执行  
  
5.配置两个管理节点时show报错Could not get configuration  
\[root@ndbcluster2 mysql-cluster\]\# ndb\_mgm  
\-- NDB Cluster -- Management Client --  
ndb\_mgm> show  
Connected to Management Server at: 192.168.1.17:1186  
ERROR Message: The cluster configuration is not yet confirmed by all defined management servers. This management server is still waiting for node 6 to connect.  
Could not get configuration  
\*  4012: Failed to get configuration  
\*        The cluster configuration is not yet confirmed by all defined management servers. This management server is still waiting for node 6 to connect.  
ndb\_mgm> show  
Cluster Configuration  
\---------------------  
\[ndbd(NDB)\]     2 node(s)  
id=2 (not connected, accepting connect from 192.168.1.18)  
id=3 (not connected, accepting connect from 192.168.1.17)  
\[ndb\_mgmd(MGM)\] 2 node(s)  
id=1    @192.168.1.17  (mysql-5.6.27 ndb-7.4.8)  
id=6    @192.168.1.18  (mysql-5.6.27 ndb-7.4.8)  
\[mysqld(API)\]   2 node(s)  
id=4 (not connected, accepting connect from 192.168.1.17)  
id=5 (not connected, accepting connect from 192.168.1.18)  
两个管理节点同时都启动起来，才能看到状态  
ndb\_mgm> show  
Cluster Configuration  
\---------------------  
\[ndbd(NDB)\]     2 node(s)  
id=2    @192.168.1.18  (mysql-5.6.27 ndb-7.4.8, Nodegroup: 0, \*)  
id=3    @192.168.1.17  (mysql-5.6.27 ndb-7.4.8, Nodegroup: 0)  
\[ndb\_mgmd(MGM)\] 2 node(s)  
id=1    @192.168.1.17  (mysql-5.6.27 ndb-7.4.8)  
id=6    @192.168.1.18  (mysql-5.6.27 ndb-7.4.8)  
\[mysqld(API)\]   2 node(s)  
id=4    @192.168.1.17  (mysql-5.6.27 ndb-7.4.8)  
id=5    @192.168.1.18  (mysql-5.6.27 ndb-7.4.8)