CentOS 6.5中如何搭建MySQL集群7.4

小编给大家分享一下CentOS 6.5中如何搭建MySQL集群7.4，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

各节点信息如下：

管理节点：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)

看完了这篇文章，相信你对“CentOS 6.5中如何搭建MySQL集群7.4”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！