MySQL 集群7.4的搭建流程(CentOS 6.5)

女爷i 2024-04-17 23:57 16阅读 0赞

各节点信息如下：  
  
管理节点：192.168.78.141  
数据节点1：192.168.78.137  
数据节点2:192.168.78.135  
SQL节点1：192.168.78.137  
SQL节点2：192.168.78.135  
  
**在管理节点、数据节点、SQL节点上分别执行编译安装**  
  
创建软件安装路径和日志、数据存放路径  
\[root@localhost /\]\# mkdir -p /cluster  
\[root@localhost /\]\# mkdir -p /cluster\_data/  
  
去官网下载MySQL Cluster  
http://dev.mysql.com/downloads/cluster/  
  
\[root@localhost install\]\# rpm -ivh MySQL-Cluster-gpl-7.4.11-1.el6.src.rpm   
warning: MySQL-Cluster-gpl-7.4.11-1.el6.src.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY  
   1:MySQL-Cluster-gpl      \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\# \[100%\]  
  
\[root@localhost log\]\# cd /root/rpmbuild/SOURCES/  
\[root@localhost SOURCES\]\# tar xvfz mysql-cluster-gpl-7.4.11.tar.gz  
\[root@localhost SOURCES\]\# ls  
mysql-5.5.48  mysql-5.5.48.tar.gz  mysql-cluster-gpl-7.4.11  mysql-cluster-gpl-7.4.11.tar.gz  
\[root@localhost SOURCES\]\# cd mysql-cluster-gpl-7.4.11  
  
\--cmake参数说明  
  
\-DWITH\_NDB\_JAVA=\{ON|OFF\}  
在创建MySQL集群的时候启用Java支持，包括ClusterJ。这个参数默认是开启状态。如果不希望在编译MySQL集群的时候使用Java支持，可以在编译的时候关闭这个参数-DWITH\_NDB\_JAVA=OFF。  
  
\-DWITH\_NDBCLUSTER\_STORAGE\_ENGINE=\{ON|OFF\}  
在mysqld中建立和连接NDB(NDBCLUSTER)存储引擎，这个参数默认是开启的。  
  
\[root@localhost SOURCES\]\# **cmake . -DCMAKE\_INSTALL\_PREFIX=/cluster \\**  
\-DDEFAULT\_CHARSET=utf8 \\  
\-DDEFAULT\_COLLATION=utf8\_general\_ci \\  
\-DWITH\_NDB\_JAVA=OFF \\  
\-DWITH\_FEDERATED\_STORAGE\_ENGINE=1 \\  
\-DWITH\_NDBCLUSTER\_STORAGE\_ENGINE=1 \\  
\-DCOMPILATION\_COMMENT='MySQL Cluster production environment' \\  
\-DWITH\_READLINE=ON \\  
\-DSYSCONFDIR=/cluster\_data \\  
\-DMYSQL\_UNIX\_ADDR=/cluster\_data/mysql.sock \\  
  
\[root@localhost mysql-cluster-gpl-7.4.11\]\# **make**  
.....  
\[100%\] Building CXX object libmysqld/CMakeFiles/sql\_embedded.dir/\_\_/sql/uniques.cc.o  
\[100%\] Building CXX object libmysqld/CMakeFiles/sql\_embedded.dir/\_\_/sql/unireg.cc.o  
Linking CXX static library libsql\_embedded.a  
\[100%\] Built target sql\_embedded  
\[100%\] Generating mysqlserver\_depends.c  
Scanning dependencies of target mysqlserver  
\[100%\] Building C object libmysqld/CMakeFiles/mysqlserver.dir/mysqlserver\_depends.c.o  
Linking C static library libmysqld.a  
/usr/bin/ar: creating /root/rpmbuild/SOURCES/mysql-cluster-gpl-7.4.11/libmysqld/libmysqld.a  
\[100%\] Built target mysqlserver  
Scanning dependencies of target mysql\_client\_test\_embedded  
\[100%\] Building C object libmysqld/examples/CMakeFiles/mysql\_client\_test\_embedded.dir/\_\_/\_\_/tests/mysql\_client\_test.c.o  
Linking CXX executable mysql\_client\_test\_embedded  
\[100%\] Built target mysql\_client\_test\_embedded  
Scanning dependencies of target mysql\_embedded  
\[100%\] Building CXX object libmysqld/examples/CMakeFiles/mysql\_embedded.dir/\_\_/\_\_/client/completion\_hash.cc.o  
\[100%\] Building CXX object libmysqld/examples/CMakeFiles/mysql\_embedded.dir/\_\_/\_\_/client/mysql.cc.o  
\[100%\] Building CXX object libmysqld/examples/CMakeFiles/mysql\_embedded.dir/\_\_/\_\_/client/readline.cc.o  
Linking CXX executable mysql\_embedded  
\[100%\] Built target mysql\_embedded  
Scanning dependencies of target mysqltest\_embedded  
\[100%\] Building CXX object libmysqld/examples/CMakeFiles/mysqltest\_embedded.dir/\_\_/\_\_/client/mysqltest.cc.o  
Linking CXX executable mysqltest\_embedded  
\[100%\] Built target mysqltest\_embedded  
Scanning dependencies of target my\_safe\_process  
\[100%\] Building CXX object mysql-test/lib/My/SafeProcess/CMakeFiles/my\_safe\_process.dir/safe\_process.cc.o  
Linking CXX executable my\_safe\_process  
\[100%\] Built target my\_safe\_process  
  
\[root@localhost mysql-cluster-gpl-7.4.11\]\# **make install**  
.....  
\-- Installing: /cluster/sql-bench/innotest2  
\-- Installing: /cluster/sql-bench/innotest2b  
\-- Installing: /cluster/sql-bench/innotest1b  
\-- Installing: /cluster/sql-bench/test-alter-table  
\-- Installing: /cluster/sql-bench/README  
\-- Installing: /cluster/sql-bench/innotest1  
\-- Installing: /cluster/sql-bench/bench-count-distinct  
\-- Installing: /cluster/sql-bench/innotest1a  
\-- Installing: /cluster/sql-bench/test-ATIS  
\-- Installing: /cluster/sql-bench/test-wisconsin  
\-- Installing: /cluster/sql-bench/run-all-tests  
\-- Installing: /cluster/sql-bench/test-create  
\-- Installing: /cluster/sql-bench/server-cfg  
\-- Installing: /cluster/sql-bench/test-connect  
\-- Installing: /cluster/sql-bench/test-big-tables  
\-- Installing: /cluster/sql-bench/test-transactions  
\-- Installing: /cluster/sql-bench/test-insert  
  
\--更改软件安装目录的权限为mysql  
\[root@localhost mysql-cluster-gpl-7.4.11\]\# chown -R mysql.mysql /cluster  
  
\--更改日志、数据存放目录的权限为mysql  
\[root@localhost /\]\# chown -R mysql.mysql /cluster\_data/  
  
**配置管理节点**  
管理节点需要配置一个config.ini文件，这个文件用于告诉MySQL集群需要维护的replica（冗余）数量、分配给每个数据节点的数据和索引的内存大小、数据节点的存放位置以及SQL节点的位置。  
  
配置管理节点的config.ini文件  
\[root@localhost mysql-cluster-gpl-7.4.11\]\# mkdir -p /cluster\_data/config/  
\[root@localhost mysql-cluster-gpl-7.4.11\]\# vim /cluster\_data/config/config.ini  
\[ndbd default\]  
\# Options affecting ndbd processes on all data nodes:  
NoOfReplicas=2    \# 指定冗余数量，建议该值不低于2，否则数据就无冗余保护  
DataMemory=80M    \# 为数据存储分配的内存大小，实际生产环境使用的内存应该很大  
IndexMemory=18M   \# 为索引存储分配的内存大小，实际生产环境使用的内存应该很大  
  
\[tcp default\]  
\# TCP/IP options:  
portnumber=2202   \# This the default; however, you can use any  
                  \# port that is free for all the hosts in the cluster  
                  \# Note: It is recommended that you do not specify the port  
                  \# number at all and simply allow the default value to be used  
                  \# instead  
  
\[ndb\_mgmd\]  
\# 管理节点选项:  
hostname=192.168.78.141           \# 管理节点的主机名或IP地址  
datadir=/cluster\_data/config \# 管理节点存放节点日志文件的路径  
  
\[ndbd\]  
\# 数据节点1选项：  
                                \# (每个数据节点需要配置一个\[ndbd\]部分)  
hostname=192.168.78.137           \# 主机名或IP地址  
datadir=/cluster\_data   \# 数据节点数据文件存放的路径  
  
\[ndbd\]  
\# 数据节点2选项：  
                                \# (每个数据节点需要配置一个\[ndbd\]部分)  
hostname=192.168.78.135           \# 主机名或IP地址  
datadir=/cluster\_data   \# 数据节点数据文件存放的路径  
  
\[mysqld\]  
\# SQL 节点1选项:  
hostname=192.168.78.137           \# 主机名或IP地址  
                                \# (additional mysqld connections can be  
                                \# specified for this node for various  
                                \# purposes such as running ndb\_restore)  
  
\[mysqld\]  
\# SQL 节点2选项:  
hostname=192.168.78.135           \# 主机名或IP地址  
  
\[root@localhost mysql-cluster-gpl-7.4.11\]\# chown -R mysql.mysql /cluster\_data/  
  
**配置数据节点**  
  
每个数据节点都要配置  
每个数据节点需要配置一个my.cnf配置文件，这个配置文件中提供连接到管理节点的连接串和管理节点所在的主机信息。  
\[root@localhost /\]\# vim /etc/my.cnf  
\[mysqld\]  
\# mysqld进程的选项:  
ndbcluster                      \# 开启NDB存储引擎  
  
\[mysql\_cluster\]  
\# MySQL集群节点选项:  
ndb-connectstring=192.168.78.141   \# 管理节点的所在主机  
  
\--初始化MySQL数据库的数据文件路径，并且创建系统表  
\[root@localhost cluster\_data\]\# cd /cluster  
\[root@localhost cluster\]\# ls  
bin  COPYING  data  docs  include  lib  man  mysql-test  README  scripts  share  sql-bench  support-files  
\[root@localhost cluster\]\# cd scripts/  
\[root@localhost scripts\]\# ls  
mysql\_install\_db  
\[root@localhost scripts\]\# ./mysql\_install\_db --user=mysql --basedir=/cluster  --datadir=/cluster\_data/  
FATAL ERROR: Could not find ./bin/my\_print\_defaults  
  
If you compiled from source, you need to run 'make install' to  
copy the software into the correct location ready for operation.  
  
If you are using a binary release, you must either be at the top  
level of the extracted archive, or pass the --basedir option  
pointing to that location.  
  
\[root@localhost scripts\]\# ./mysql\_install\_db --user=mysql --basedir=/cluster --datadir=/cluster\_data/  
  
**配置SQL节点**  
  
每个SQL节点都要配置  
每个SQL节点需要配置一个my.cnf配置文件，这个配置文件中提供连接到管理节点的连接串和数据节点所在的主机信息。  
\[root@localhost /\]\# vim /etc/my.cnf  
\[client\]  
socket=/cluster\_data/mysql.sock  
  
\[mysqld\]  
ndbcluster                      \# 开启NDB存储引擎  
basedir = /cluster  
datadir = /cluster\_data  
socket=/cluster\_data/mysql.sock  
log\_error = /cluster\_data/err.log  
  
\[mysql\_cluster\]  
\# MySQL集群节点选项:  
ndb-connectstring=192.168.78.141   \# 管理节点的所在主机  
  
**初始化启动MySQL集群**  
  
**启动管理节点**  
在管理节点所在的主机上，启动管理节点进程  
  
\[root@localhost mysql-cluster-gpl-7.4.11\]\# /cluster/bin/ndb\_mgmd -f /cluster\_data/config/config.ini  
MySQL Cluster Management Server mysql-5.6.29 ndb-7.4.11  
2016-05-15 01:26:16 \[MgmtSrvr\] INFO     -- The default config directory '/cluster/mysql-cluster' does not exist. Trying to create it...  
2016-05-15 01:26:16 \[MgmtSrvr\] INFO     -- Sucessfully created config directory  
  
使用ndb\_mgm客户端工具连接到集群，查看集群的状态  
  
\[root@localhost config\]\# /cluster/bin/ndb\_mgm  
\-- NDB Cluster -- Management Client --  
ndb\_mgm> help  
\---------------------------------------------------------------------------  
 NDB Cluster -- Management Client -- Help  
\---------------------------------------------------------------------------  
HELP                                   Print help text  
HELP COMMAND                           Print detailed help for COMMAND(e.g. SHOW)  
SHOW                                   Print information about cluster  
.....  
  
查看集群的状态，只有管理节点是启动状态，数据节点和SQL节点都没有启动  
  
ndb\_mgm> show  
Connected to Management Server at: localhost:1186  
Cluster Configuration  
\---------------------  
\[ndbd(NDB)\] 2 node(s)  
id=2 (not connected, accepting connect from 192.168.78.137)  
id=3 (not connected, accepting connect from 192.168.78.135)  
  
\[ndb\_mgmd(MGM)\] 1 node(s)  
id=1 @192.168.78.141  (mysql-5.6.29 ndb-7.4.11)  
  
\[mysqld(API)\] 2 node(s)  
id=4 (not connected, accepting connect from 192.168.78.137)  
id=5 (not connected, accepting connect from 192.168.78.135)  
  
\[root@localhost cluster\]\# cd /cluster\_data/  
\[root@localhost cluster\_data\]\# ls  
config  
\[root@localhost cluster\_data\]\# cd config/  
\[root@localhost config\]\# ls  
config.ini  ndb\_1\_cluster.log  ndb\_1\_out.log  ndb\_1.pid  
  
**启动数据节点**  
在每台数据节点所在的主机上，执行下面命令启动ndbd进程  
\[root@localhost mysql-cluster-gpl-7.4.11\]\# /cluster/bin/ndbd  
2016-05-15 01:34:45 \[ndbd\] INFO     -- Angel connected to '192.168.78.141:1186'  
2016-05-15 01:34:45 \[ndbd\] INFO     -- Angel allocated nodeid: 2  
  
\[root@localhost /\]\# cd /cluster\_data/  
\[root@localhost cluster\_data\]\# ls  
ndb\_2\_fs  ndb\_2\_out.log  ndb\_2.pid  
  
在管理节点上查看集群状态  
\[root@localhost config\]\# /cluster/bin/ndb\_mgm  
 \-- NDB Cluster -- Management Client --  
 ndb\_mgm> show  
 Cluster Configuration  
 \---------------------  
 \[ndbd(NDB)\] 2 node(s)  
 id=2 @192.168.78.137  (mysql-5.6.29 ndb-7.4.11, starting, Nodegroup: 0, \*)  
 id=3 @192.168.78.135  (mysql-5.6.29 ndb-7.4.11, starting, Nodegroup: 0)  
  
 \[ndb\_mgmd(MGM)\] 1 node(s)  
 id=1 @192.168.78.141  (mysql-5.6.29 ndb-7.4.11)  
  
 \[mysqld(API)\] 2 node(s)  
 id=4 (not connected, accepting connect from 192.168.78.137)  
 id=5 (not connected, accepting connect from 192.168.78.135)  
  
 ndb\_mgm> Node 2: Started (version 7.4.11)  
 Node 3: Started (version 7.4.11)  
 show  
 Cluster Configuration  
 \---------------------  
 \[ndbd(NDB)\] 2 node(s)  
 id=2 @192.168.78.137  (mysql-5.6.29 ndb-7.4.11, Nodegroup: 0, \*)  
 id=3 @192.168.78.135  (mysql-5.6.29 ndb-7.4.11, Nodegroup: 0)  
  
 \[ndb\_mgmd(MGM)\] 1 node(s)  
 id=1 @192.168.78.141  (mysql-5.6.29 ndb-7.4.11)  
  
 \[mysqld(API)\] 2 node(s)  
 id=4 (not connected, accepting connect from 192.168.78.137)  
 id=5 (not connected, accepting connect from 192.168.78.135)  
  
查看内存使用率  
  
ndb\_mgm> all report memory  
Node 11: Data usage is 57%(3478260 32K pages of total 6062080)  
Node 11: Index usage is 13%(795507 8K pages of total 5898272)  
Node 12: Data usage is 57%(3461303 32K pages of total 6062080)  
Node 12: Index usage is 13%(806025 8K pages of total 5898272)  
  
**启动SQL节点**  
\[root@localhost mysql-cluster-gpl-7.4.11\]\# /cluster/bin/mysqld\_safe --defaults-file=/etc/my.cnf &  
\[1\] 42623  
\[root@localhost mysql-cluster-gpl-7.4.11\]\# 160515 02:45:14 mysqld\_safe Logging to '/cluster\_data/err.log'.  
160515 02:45:14 mysqld\_safe Starting mysqld daemon with databases from /cluster\_data  
  
尝试连接到数据库，删除数据库中多余的root用户和匿名用户，只在本地保留一个root用户  
  
\[root@localhost mysqld\]\# /cluster/bin/mysql -uroot  
 Welcome to the MySQL monitor.  Commands end with ; or \\g.  
 Your MySQL connection id is 2  
 Server version: 5.6.29-ndb-7.4.11 MySQL Cluster production environment  
  
 Copyright (c) 2000, 2016, 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> select host, user,password from mysql.user;  
 \+-----------------------+------+----------+  
 | host                  | user | password |  
 \+-----------------------+------+----------+  
 | localhost             | root |          |  
 | localhost.localdomain | root |          |  
 | 127.0.0.1             | root |          |  
 | ::1                   | root |          |  
 | localhost             |      |          |  
 | localhost.localdomain |      |          |  
 \+-----------------------+------+----------+  
 6 rows in set (0.18 sec)  
  
mysql> delete from mysql.user where (user,host) not in (select 'root','localhost');  
 Query OK, 5 rows affected (0.15 sec)  
  
 mysql> select host, user,password from mysql.user;  
 \+-----------+------+----------+  
 | host      | user | password |  
 \+-----------+------+----------+  
 | localhost | root |          |  
 \+-----------+------+----------+  
 1 row in set (0.00 sec)  
  
mysql> update mysql.user set user='system',password=password('Mysql\#2015') where user='root';  
 Query OK, 1 row affected (0.08 sec)  
 Rows matched: 1  Changed: 1  Warnings: 0  
  
 mysql> flush privileges;  
 Query OK, 0 rows affected (0.02 sec)  
  
 mysql> select version();  
 \+-------------------+  
 | version()         |  
 \+-------------------+  
 | 5.6.29-ndb-7.4.11 |  
 \+-------------------+  
 1 row in set (0.08 sec)  
  
\--在管理节点上查看集群状态  
可以看到各个节点均已正常启动  
\[root@localhost config\]\# /cluster/bin/ndb\_mgm  
\-- NDB Cluster -- Management Client --  
ndb\_mgm> show  
Cluster Configuration  
\---------------------  
\[ndbd(NDB)\] 2 node(s)  
id=2 @192.168.78.137  (mysql-5.6.29 ndb-7.4.11, Nodegroup: 0)  
id=3 @192.168.78.135  (mysql-5.6.29 ndb-7.4.11, Nodegroup: 0, \*)  
  
\[ndb\_mgmd(MGM)\] 1 node(s)  
id=1 @192.168.78.141  (mysql-5.6.29 ndb-7.4.11)  
  
\[mysqld(API)\] 2 node(s)  
id=4 @192.168.78.137  (mysql-5.6.29 ndb-7.4.11)  
id=5 @192.168.78.135  (mysql-5.6.29 ndb-7.4.11)  
  
**--测试集群数据同步情况**  
\--SQL节点2，创建存储引擎为NDBCLUSTER的测试表  
mysql> use test  
Database changed  
mysql> create table emp(id int) engine=NDBCLUSTER;  
Query OK, 0 rows affected (2.68 sec)  
  
mysql> insert into emp values(10);  
Query OK, 1 row affected (0.07 sec)  
  
mysql> commit;  
Query OK, 0 rows affected (0.07 sec)  
  
\--SQL节点1，查看在节点2创建的表  
mysql> desc emp;  
 \+-------+---------+------+-----+---------+-------+  
 | Field | Type    | Null | Key | Default | Extra |  
 \+-------+---------+------+-----+---------+-------+  
 | id    | int(11) | YES  |     | NULL    |       |  
 \+-------+---------+------+-----+---------+-------+  
 1 row in set (0.13 sec)  
  
 mysql> select \* from emp;  
 \+------+  
 | id   |  
 \+------+  
 |   10 |  
 \+------+  
 1 row in set (0.13 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2100401/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2100401/