Mysql集群搭建-实操

--2022-01-30 18:35:52-- http://mirrors.sohu.com/mysql/MySQL-Cluster-7.5/mysql-cluster-gpl-7.5.20-el7-x86_64.tar.gz
Resolving mirrors.sohu.com (mirrors.sohu.com)... 123.125.123.141
Connecting to mirrors.sohu.com (mirrors.sohu.com)|123.125.123.141|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1020481224 (973M) [application/octet-stream]
Saving to: ‘mysql-cluster-gpl-7.5.20-el7-x86_64.tar.gz’

100%[================================================>] 1,020,481,224 132KB/s in 2h 9m

2022-01-30 20:45:42 (128 KB/s) - ‘mysql-cluster-gpl-7.5.20-el7-x86_64.tar.gz’ saved [1020481224/1020481224]

 [root@fp-web-114 local]# cat /etc/redhat-release 
CentOS Linux release 7.2.1511 (Core)
[root@fp-web-114 local]# rpm -qa | grep mysql  

#####验证一下用户是否存在

  [root@fp-web-114 local]# id mysql
   id: mysql: no such user
   #####添加组
   [root@fp-web-114 local]# > groupadd mysql
  #####添加用户
 [root@fp-web-114 local]# > useradd -g mysql -s /bin/false mysql  

   ######解释：  -s /bin/false mysql 禁止用mysql用户登录
 4、防火墙的状态

[root@fp-web-116 bin]# firewall-cmd --state
not running

 ###### 查看mysql是否创建成功

[root@fp-web-114 local]# >  id mysql    

uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)

 注意： 安装管理节点不需要 mysqld二进制文件。只需要NDB群集管理服务器（ndb_mgmd）; 您最有可能也想安装管理客户端（ndb_mgm）
 

[root@fp-web-114 local]# tar -zxvf   mysql-cluster-gpl-7.5.20-el7-x86_64.tar.gz
...........

[root@fp-web-114 local]# ll
total 996568
drwxr-xr-x. 2 root root 36 Feb 12 2019 bin
drwxr-xr-x. 2 root root 6 Aug 12 2015 etc
drwxr-xr-x. 2 root root 6 Aug 12 2015 games
drwxr-xr-x. 2 root root 6 Aug 12 2015 include
drwxr-xr-x 5 root root 4096 Feb 12 2019 k8s
drwxr-xr-x. 2 root root 6 Aug 12 2015 lib
drwxr-xr-x. 2 root root 6 Aug 12 2015 lib64
drwxr-xr-x. 2 root root 6 Aug 12 2015 libexec
drwxr-xr-x 10 root root 155 Jan 30 21:04 mysql-cluster-gpl-7.5.20-el7-x86_64    ###
-rw-r--r-- 1 root root 1020481224 Sep 21 2020 mysql-cluster-gpl-7.5.20-el7-x86_64.tar.gz
drwxr-xr-x. 2 root root 6 Aug 12 2015 sbin
drwxr-xr-x. 5 root root 46 Jun 7 2017 share
drwxr-xr-x. 2 root root 6 Aug 12 2015 src

[root@fp-web-114 local]# cd mysql-cluster-gpl-7.5.20-el7-x86_64/
[root@fp-web-114 mysql-cluster-gpl-7.5.20-el7-x86_64]# ll
total 396
drwxr-xr-x 2 root root 4096 Jan 30 21:04 bin
drwxr-xr-x 2 root root 52 Jan 30 21:03 docs
drwxr-xr-x 4 root root 4096 Jan 30 21:03 include
drwxr-xr-x 5 root root 4096 Jan 30 21:03 lib
-rw-r--r-- 1 7161 31415 373771 Sep 21 2020 LICENSE
drwxr-xr-x 4 root root 28 Jan 30 21:04 man
drwxr-xr-x 10 root root 4096 Jan 30 21:04 mysql-test
-rw-r--r-- 1 7161 31415 587 Sep 21 2020 README
-rw-r--r-- 1 7161 31415 587 Sep 21 2020 README-test
drwxr-xr-x 31 root root 4096 Jan 30 21:04 share
drwxr-xr-x 2 root root 86 Jan 30 21:04 support-files

[root@fp-web-114 mysql-cluster-gpl-7.5.20-el7-x86_64]# cp bin/ndb_mgm* /usr/local/bin
[root@fp-web-114 mysql-cluster-gpl-7.5.20-el7-x86_64]# ll /usr/local/bin
total 294512
-rwxr-xr-x 1 root root 269569768 Aug 30 2019 hyperkube
lrwxrwxrwx 1 root root 24 Feb 12 2019 kubectl -> /usr/local/bin/hyperkube
-rwxr-xr-x 1 root root 10495120 Jan 30 21:08 ndb_mgm
-rwxr-xr-x 1 root root 21510512 Jan 30 21:08 ndb_mgmd

[root@fp-web-114 bin]# chmod +x ndb_mgm*

[root@fp-web-114 bin]# ll --time-style=full
total 294512
-rwxr-xr-x 1 root root 269569768 2019-08-30 14:07:12.222643291 +0800 hyperkube
lrwxrwxrwx 1 root root 24 2019-02-12 14:32:22.004817282 +0800 kubectl -> /usr/local/bin/hyperkube
-rwxr-xr-x 1 root root 10495120 2022-01-30 21:08:07.784260579 +0800 ndb_mgm
-rwxr-xr-x 1 root root 21510512 2022-01-30 21:08:07.806260940 +0800 ndb_mgmd

管理节点负责整个Cluster 集群中各个节点的管理工作，包括集群的配置，启动关闭
各节点，以及实施数据的备份恢复等。管理节点会获取整个Cluster 环境中各节点的状态和
错误信息，并且将各Cluster 集群中各个节点的信息反馈给整个集群中其他的所有节点。由
于管理节点上保存在整个Cluster 环境的配置，同时担任了集群中各节点的基本沟通工作，

所以他必须是最先被启动的节点

/sbin/iptables -I INPUT -p tcp --dport 1186 -j ACCEPT     -- 开放端口

[root@fp-web-114 mysql-cluster]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.7.32 ndb-7.5.20
2022-01-30 21:22:24 [MgmtSrvr] INFO -- The default config directory '/usr/local/mysql/mysql-cluster' does not exist. Trying to create it...
Failed to create directory '/usr/local/mysql/mysql-cluster', error: 2
2022-01-30 21:22:24 [MgmtSrvr] ERROR -- Could not create directory '/usr/local/mysql/mysql-cluster'. Either create it manually or specify a different directory with --configdir=<path>

[root@fp-web-114 local]# mv mysql-cluster-gpl-7.5.20-el7-x86_64 mysql

[root@fp-web-114 mysql]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini

   MySQL Cluster Management Server mysql-5.7.32 ndb-7.5.20
2022-01-30 21:25:06 [MgmtSrvr] INFO -- The default config directory '/usr/local/mysql/mysql-cluster' does not exist. Trying to create it...
2022-01-30 21:25:06 [MgmtSrvr] INFO -- Sucessfully created config directory

[root@fp-web-114 mysql]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini

MySQL Cluster Management Server mysql-5.7.32 ndb-7.5.20
Couldn't start as daemon, error: 'Failed to lock pidfile '/var/lib/mysql-cluster/ndb_1.pid', already locked by pid=60230, errno: 11'

[root@fp-web-114 mysql]# ll /var/lib/mysql-cluster/

total 16
-rw-r--r-- 1 root root 1803 Jan 30 21:18 config.ini
-rw-r--r-- 1 root root 990 Jan 30 21:25 ndb_1_cluster.log
-rw-r--r-- 1 root root 26 Jan 30 21:25 ndb_1_out.log
-rw-r--r-- 1 root root 5 Jan 30 21:25 ndb_1.pid

[root@fp-web-114 mysql]# cd /var/lib/mysql-cluster/
[root@fp-web-114 mysql-cluster]# ll
total 16
-rw-r--r-- 1 root root 1803 Jan 30 21:18 config.ini
-rw-r--r-- 1 root root 990 Jan 30 21:25 ndb_1_cluster.log
-rw-r--r-- 1 root root 26 Jan 30 21:25 ndb_1_out.log
-rw-r--r-- 1 root root 5 Jan 30 21:25 ndb_1.pid

[root@fp-web-114 mysql-cluster]# rm -rf ndb_1
ndb_1_cluster.log ndb_1_out.log ndb_1.pid 
[root@fp-web-114 mysql-cluster]# rm -rf ndb_1.pid 

[root@fp-web-114 mysql-cluster]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini

MySQL Cluster Management Server mysql-5.7.32 ndb-7.5.20

[root@fp-web-114 mysql-cluster]# ps -ef | grep mysql
mysql 3476 3458 0 2021 ? 00:00:00 /pause
mysql 4348 4332 0 2021 ? 00:00:00 sh -c start-zookeeper --servers=3 --data_dir=/var/lib/zookeeper/data --data_log_dir=/var/lib/zookeeper/data/log --conf_dir=/opt/zookeeper/conf --client_port=2181 --election_port=3888 --server_port=2888 --tick_time=2000 --init_limit=10 --sync_limit=5 --heap=2G --max_client_cnxns=60 --snap_retain_count=3 --purge_interval=12 --max_session_timeout=40000 --min_session_timeout=4000 --log_level=INFO
mysql 4387 4348 0 2021 ? 02:11:14 /usr/lib/jvm/java-8-openjdk-amd64/bin/java -Dzookeeper.log.dir=/var/log/zookeeper -Dzookeeper.root.logger=INFO,CONSOLE -cp /usr/bin/../build/classes:/usr/bin/../build/lib/*.jar:/usr/bin/../share/zookeeper/zookeeper-3.4.10.jar:/usr/bin/../share/zookeeper/slf4j-log4j12-1.6.1.jar:/usr/bin/../share/zookeeper/slf4j-api-1.6.1.jar:/usr/bin/../share/zookeeper/netty-3.10.5.Final.jar:/usr/bin/../share/zookeeper/log4j-1.2.16.jar:/usr/bin/../share/zookeeper/jline-0.9.94.jar:/usr/bin/../src/java/lib/*.jar:/usr/bin/../etc/zookeeper: -Xmx2G -Xms2G -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.local.only=false org.apache.zookeeper.server.quorum.QuorumPeerMain /usr/bin/../etc/zookeeper/zoo.cfg
root 60230 1 0 21:25 ? 00:00:00 ndb_mgmd -f /var/lib/mysql-cluster/config.ini

[root@fp-web-114 mysql-cluster]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 1 node(s)
id=2 (not connected, accepting connect from 10.129.79.50)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.129.79.46 (mysql-5.7.32 ndb-7.5.20)

[mysqld(API)] 1 node(s)
id=3 (not connected, accepting connect from 10.129.79.48)

ndb_mgm>

SQL 层的SQL 服务器节点（后面简称为SQL 节点），也就是我们常说的Mysql Server：
主要负责实现一个数据库在存储层之上的所有事情，比如连接管理，query 优化和响
应，cache 管理等等，只有存储层的工作交给了NDB 数据节点去处理了。也就是说，在纯粹
的Mysql Cluster 环境中的SQL 节点，可以被认为是一个不需要提供任何存储引擎的Mysql
服务器，因为他的存储引擎有Cluster 环境中的NDB 节点来担任。

[root@fp-web-116 local]# id mysql
 id: mysql: no such user
#####添加组
[root@fp-web-116 local]# > groupadd mysql
#####添加用户
[root@fp-web-116 local]# > useradd -g mysql -s /bin/false mysql  

 ######解释：  -s /bin/false mysql 禁止用mysql用户登录

 ###### 查看mysq用户l是否创建成功

[root@fp-web-116 local]# >  id mysql  

  uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)

[root@fp-web-116 local]#  cd /usr/local/

   [root@fp-web-116  local] #  tar -zxvf  mysql-cluster-gpl-7.5.20-el7-x86_64.tar.gz 

   ........

[root@fp-web-116 local]#  mv mysql-cluster-gpl-7.6.9-linux-glibc2.12-x86_64 mysql

[root@fp-web-116 local]#  cd /usr/local/mysql

[root@fp-web-116 mysql]#  mkdir data

[root@fp-web-116 mysql]#  chown mysql:mysql data

[root@fp-web-116 mysql]#   chmod -R 755 data

[root@fp-web-116 mysql]# mkdir log
[root@fp-web-116 mysql]#  cd log
[root@fp-web-116 log]# touch mysqld.log

[root@fp-web-116 log]# chown mysql:mysql mysqld.log
[root@fp-web-116 log]# chmod 775 mysqld.log

[root@fp-web-116 log]# ll
total 0
-rwxrwxr-x 1 mysql mysql 0 Jan 30 21:55 mysqld.log

 [root@fp-web-116 mysql]# cd data
  [root@fp-web-116 data]# ll
  total 0

  现在开始初始化数据库

 [root@fp-web-116 mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --lc_messages_dir=/usr/local/mysql/share

2022-01-30T14:03:34.972423Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-01-30T14:03:35.970480Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-01-30T14:03:36.106340Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-01-30T14:03:36.172384Z 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: 6ad0b90b-81d5-11ec-aea7-0605ae0020e3.
2022-01-30T14:03:36.173756Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-01-30T14:03:36.918937Z 0 [Warning] CA certificate ca.pem is self signed.
2022-01-30T14:03:36.987892Z 1 [Note] A temporary password is generated for root@localhost: Ry7zAZKID()%

[root@fp-web-116 data]# ll  /us/local/mysql/data
total 110668
-rw-r----- 1 mysql mysql 56 Jan 30 22:03 auto.cnf
-rw------- 1 mysql mysql 1680 Jan 30 22:03 ca-key.pem
-rw-r--r-- 1 mysql mysql 1140 Jan 30 22:03 ca.pem
-rw-r--r-- 1 mysql mysql 1140 Jan 30 22:03 client-cert.pem
-rw------- 1 mysql mysql 1680 Jan 30 22:03 client-key.pem
-rw-r----- 1 mysql mysql 440 Jan 30 22:03 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jan 30 22:03 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jan 30 22:03 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jan 30 22:03 ib_logfile1
drwxr-x--- 2 mysql mysql 4096 Jan 30 22:03 mysql
drwxr-x--- 2 mysql mysql 4096 Jan 30 22:03 ndbinfo
drwxr-x--- 2 mysql mysql 8192 Jan 30 22:03 performance_schema
-rw------- 1 mysql mysql 1680 Jan 30 22:03 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Jan 30 22:03 public_key.pem
-rw-r--r-- 1 mysql mysql 1140 Jan 30 22:03 server-cert.pem
-rw------- 1 mysql mysql 1676 Jan 30 22:03 server-key.pem
drwxr-x--- 2 mysql mysql 8192 Jan 30 22:03 sys

设置MySQL服务器和数据目录的必要权限

[root@fp-web-116 data]#  cd /usr/local/mysql 

[root@fp-web-116 mysql]#  chown -R root . 

drwxr-xr-x 2 root root 4096 Jan 30 21:49 bin
drwxr-xr-x 6 root mysql 4096 Jan 30 22:03 data
drwxr-xr-x 2 root root 52 Jan 30 21:48 docs
drwxr-xr-x 4 root root 4096 Jan 30 21:48 include
drwxr-xr-x 5 root root 4096 Jan 30 21:48 lib
-rw-r--r-- 1 root 31415 373771 Sep 21 2020 LICENSE
drwxr-xr-x 2 root root 23 Jan 30 21:55 log
drwxr-xr-x 4 root root 28 Jan 30 21:49 man
-rw-r--r-- 1 root root 0 Jan 30 21:59 mysql.pid
drwxr-xr-x 10 root root 4096 Jan 30 21:49 mysql-test
-rw-r--r-- 1 root 31415 587 Sep 21 2020 README
-rw-r--r-- 1 root 31415 587 Sep 21 2020 README-test
drwxr-xr-x 31 root root 4096 Jan 30 21:49 share
drwxr-xr-x 2 root root 86 Jan 30 21:49 support-files

[root@fp-web-116 mysql]#  chown -R mysql data 

drwxr-xr-x 2 root root 4096 Jan 30 21:49 bin
drwxr-xr-x 6 mysql mysql 4096 Jan 30 22:03 data
drwxr-xr-x 2 root root 52 Jan 30 21:48 docs
drwxr-xr-x 4 root root 4096 Jan 30 21:48 include
drwxr-xr-x 5 root root 4096 Jan 30 21:48 lib
-rw-r--r-- 1 root 31415 373771 Sep 21 2020 LICENSE
drwxr-xr-x 2 root root 23 Jan 30 21:55 log
drwxr-xr-x 4 root root 28 Jan 30 21:49 man
-rw-r--r-- 1 root root 0 Jan 30 21:59 mysql.pid
drwxr-xr-x 10 root root 4096 Jan 30 21:49 mysql-test
-rw-r--r-- 1 root 31415 587 Sep 21 2020 README
-rw-r--r-- 1 root 31415 587 Sep 21 2020 README-test
drwxr-xr-x 31 root root 4096 Jan 30 21:49 share
drwxr-xr-x 2 root root 86 Jan 30 21:49 support-files

[root@fp-web-116 mysql]# chgrp -R mysql .

drwxr-xr-x 2 root mysql 4096 Jan 30 21:49 bin
drwxr-xr-x 6 mysql mysql 4096 Jan 30 22:03 data
drwxr-xr-x 2 root mysql 52 Jan 30 21:48 docs
drwxr-xr-x 4 root mysql 4096 Jan 30 21:48 include
drwxr-xr-x 5 root mysql 4096 Jan 30 21:48 lib
-rw-r--r-- 1 root mysql 373771 Sep 21 2020 LICENSE
drwxr-xr-x 2 root mysql 23 Jan 30 21:55 log
drwxr-xr-x 4 root mysql 28 Jan 30 21:49 man
-rw-r--r-- 1 root mysql 0 Jan 30 21:59 mysql.pid
drwxr-xr-x 10 root mysql 4096 Jan 30 21:49 mysql-test
-rw-r--r-- 1 root mysql 587 Sep 21 2020 README
-rw-r--r-- 1 root mysql 587 Sep 21 2020 README-test
drwxr-xr-x 31 root mysql 4096 Jan 30 21:49 share
drwxr-xr-x 2 root mysql 86 Jan 30 21:49 support-files

[root@fp-web-116 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/ 

[root@fp-web-116 mysql]#  chmod +x /etc/rc.d/init.d/mysql.server 

[root@fp-web-116 mysql]#  chkconfig --add mysql.server

注意：启动脚本目录可能因操作系统和版本而异 - 例如，在某些Linux发行版中，它是 /etc/init.d

[root@fp-web-116 data]# /etc/rc.d/init.d/mysql.server stop
Shutting down MySQL........ SUCCESS! 
[root@fp-web-116 data]# /etc/rc.d/init.d/mysql.server start
Starting MySQL................................... SUCCESS!

[root@fp-web-116 mysql]# ps -efww | grep mysql 
root 14167 1 0 22:11 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/fp-web-116.pid
mysql 14331 14167 0 22:11 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/log/mysqld.log --pid-file=/usr/local/mysql/data/fp-web-116.pid --socket=/tmp/mysql.sock

[root@fp-web-116 bin]# echo "export PATH=$PATH:/usr/local/mysql/bin/" >> /etc/profile   ###加入环境变量

[root@fp-web-116 bin]# source /etc/profile    ##重载环境变量

[root@fp-web-116 bin]# cd /usr/local/mysql/bin

[root@fp-web-116 bin]# ./mysql -h localhost -u root -pRy7zAZKID()%
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.32-ndb-7.5.20-cluster-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.

mysql> 

虽然启动了，发现启动成功了，但是日志有警告” [Warning] NDB : Tables not available after 15 seconds. Considerincreasing --ndb-wait-setup value “，并且在集群管理节点show时，读取不到节点信息。

问题原因：必须要把config.ini配置的所有数据节点启动后，才能启动SQL节点。

[root@fp-web-122 local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --lc_messages_dir=/usr/local/mysql/share
2022-01-31T13:35:13.536522Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-01-31T13:35:14.367762Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-01-31T13:35:14.474663Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-01-31T13:35:14.534944Z 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: 9ef934c6-829a-11ec-b315-0606880020e9.
2022-01-31T13:35:14.535799Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-01-31T13:35:15.630383Z 0 [Warning] CA certificate ca.pem is self signed.
2022-01-31T13:35:15.701579Z 1 [Note] A temporary password is generated for root@localhost: Ewhu*Re_v6/.