CentOS 6.5中如何搭建MySQL集群7.4

小編給大家分享一下CentOS 6.5中如何搭建MySQL集群7.4，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

各節(jié)點(diǎn)信息如下：

管理節(jié)點(diǎn)：192.168.78.141
數(shù)據(jù)節(jié)點(diǎn)1：192.168.78.137
數(shù)據(jù)節(jié)點(diǎn)2:192.168.78.135
SQL節(jié)點(diǎn)1：192.168.78.137
SQL節(jié)點(diǎn)2：192.168.78.135

在管理節(jié)點(diǎn)、數(shù)據(jù)節(jié)點(diǎn)、SQL節(jié)點(diǎn)上分別執(zhí)行編譯安裝

創(chuàng)建軟件安裝路徑和日志、數(shù)據(jù)存放路徑
[root@localhost /]# mkdir -p /cluster
[root@localhost /]# mkdir -p /cluster_data/

去官網(wǎng)下載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參數(shù)說(shuō)明

-DWITH_NDB_JAVA={ON|OFF}
在創(chuàng)建MySQL集群的時(shí)候啟用Java支持，包括ClusterJ。這個(gè)參數(shù)默認(rèn)是開(kāi)啟狀態(tài)。如果不希望在編譯MySQL集群的時(shí)候使用Java支持，可以在編譯的時(shí)候關(guān)閉這個(gè)參數(shù)-DWITH_NDB_JAVA=OFF。

-DWITH_NDBCLUSTER_STORAGE_ENGINE={ON|OFF}
在mysqld中建立和連接NDB(NDBCLUSTER)存儲(chǔ)引擎，這個(gè)參數(shù)默認(rèn)是開(kāi)啟的。

[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

--更改軟件安裝目錄的權(quán)限為mysql
[root@localhost mysql-cluster-gpl-7.4.11]# chown -R mysql.mysql /cluster

--更改日志、數(shù)據(jù)存放目錄的權(quán)限為mysql
[root@localhost /]# chown -R mysql.mysql /cluster_data/

配置管理節(jié)點(diǎn)
管理節(jié)點(diǎn)需要配置一個(gè)config.ini文件，這個(gè)文件用于告訴MySQL集群需要維護(hù)的replica（冗余）數(shù)量、分配給每個(gè)數(shù)據(jù)節(jié)點(diǎn)的數(shù)據(jù)和索引的內(nèi)存大小、數(shù)據(jù)節(jié)點(diǎn)的存放位置以及SQL節(jié)點(diǎn)的位置。

配置管理節(jié)點(diǎn)的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    # 指定冗余數(shù)量，建議該值不低于2，否則數(shù)據(jù)就無(wú)冗余保護(hù)
DataMemory=80M    # 為數(shù)據(jù)存儲(chǔ)分配的內(nèi)存大小，實(shí)際生產(chǎn)環(huán)境使用的內(nèi)存應(yīng)該很大
IndexMemory=18M   # 為索引存儲(chǔ)分配的內(nèi)存大小，實(shí)際生產(chǎn)環(huán)境使用的內(nèi)存應(yīng)該很大

[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]
# 管理節(jié)點(diǎn)選項(xiàng):
hostname=192.168.78.141           # 管理節(jié)點(diǎn)的主機(jī)名或IP地址
datadir=/cluster_data/config # 管理節(jié)點(diǎn)存放節(jié)點(diǎn)日志文件的路徑

[ndbd]
# 數(shù)據(jù)節(jié)點(diǎn)1選項(xiàng)：
                                # (每個(gè)數(shù)據(jù)節(jié)點(diǎn)需要配置一個(gè)[ndbd]部分)
hostname=192.168.78.137           # 主機(jī)名或IP地址
datadir=/cluster_data   # 數(shù)據(jù)節(jié)點(diǎn)數(shù)據(jù)文件存放的路徑

[ndbd]
# 數(shù)據(jù)節(jié)點(diǎn)2選項(xiàng)：
                                # (每個(gè)數(shù)據(jù)節(jié)點(diǎn)需要配置一個(gè)[ndbd]部分)
hostname=192.168.78.135           # 主機(jī)名或IP地址
datadir=/cluster_data   # 數(shù)據(jù)節(jié)點(diǎn)數(shù)據(jù)文件存放的路徑

[mysqld]
# SQL 節(jié)點(diǎn)1選項(xiàng):
hostname=192.168.78.137           # 主機(jī)名或IP地址
                                # (additional mysqld connections can be
                                # specified for this node for various
                                # purposes such as running ndb_restore)

[mysqld]
# SQL 節(jié)點(diǎn)2選項(xiàng):
hostname=192.168.78.135           # 主機(jī)名或IP地址

[root@localhost mysql-cluster-gpl-7.4.11]# chown -R mysql.mysql /cluster_data/

配置數(shù)據(jù)節(jié)點(diǎn)

每個(gè)數(shù)據(jù)節(jié)點(diǎn)都要配置
每個(gè)數(shù)據(jù)節(jié)點(diǎn)需要配置一個(gè)my.cnf配置文件，這個(gè)配置文件中提供連接到管理節(jié)點(diǎn)的連接串和管理節(jié)點(diǎn)所在的主機(jī)信息。
[root@localhost /]# vim /etc/my.cnf
[mysqld]
# mysqld進(jìn)程的選項(xiàng):
ndbcluster                      # 開(kāi)啟NDB存儲(chǔ)引擎

[mysql_cluster]
# MySQL集群節(jié)點(diǎn)選項(xiàng):
ndb-connectstring=192.168.78.141   # 管理節(jié)點(diǎn)的所在主機(jī)

--初始化MySQL數(shù)據(jù)庫(kù)的數(shù)據(jù)文件路徑，并且創(chuàng)建系統(tǒng)表
[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節(jié)點(diǎn)

每個(gè)SQL節(jié)點(diǎn)都要配置
每個(gè)SQL節(jié)點(diǎn)需要配置一個(gè)my.cnf配置文件，這個(gè)配置文件中提供連接到管理節(jié)點(diǎn)的連接串和數(shù)據(jù)節(jié)點(diǎn)所在的主機(jī)信息。
[root@localhost /]# vim /etc/my.cnf
[client]
socket=/cluster_data/mysql.sock

[mysqld]
ndbcluster                      # 開(kāi)啟NDB存儲(chǔ)引擎
basedir = /cluster
datadir = /cluster_data
socket=/cluster_data/mysql.sock
log_error = /cluster_data/err.log

[mysql_cluster]
# MySQL集群節(jié)點(diǎn)選項(xiàng):
ndb-connectstring=192.168.78.141   # 管理節(jié)點(diǎn)的所在主機(jī)

初始化啟動(dòng)MySQL集群

啟動(dòng)管理節(jié)點(diǎn)
在管理節(jié)點(diǎn)所在的主機(jī)上，啟動(dòng)管理節(jié)點(diǎn)進(jìn)程

[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客戶(hù)端工具連接到集群，查看集群的狀態(tài)

[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
.....

查看集群的狀態(tài)，只有管理節(jié)點(diǎn)是啟動(dòng)狀態(tài)，數(shù)據(jù)節(jié)點(diǎn)和SQL節(jié)點(diǎn)都沒(méi)有啟動(dòng)

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

啟動(dòng)數(shù)據(jù)節(jié)點(diǎn)
在每臺(tái)數(shù)據(jù)節(jié)點(diǎn)所在的主機(jī)上，執(zhí)行下面命令啟動(dòng)ndbd進(jìn)程
[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

在管理節(jié)點(diǎn)上查看集群狀態(tài)
[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)

查看內(nèi)存使用率

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)

啟動(dòng)SQL節(jié)點(diǎn)
[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

嘗試連接到數(shù)據(jù)庫(kù)，刪除數(shù)據(jù)庫(kù)中多余的root用戶(hù)和匿名用戶(hù)，只在本地保留一個(gè)root用戶(hù)

[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)

--在管理節(jié)點(diǎn)上查看集群狀態(tài)
可以看到各個(gè)節(jié)點(diǎn)均已正常啟動(dòng)
[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)

--測(cè)試集群數(shù)據(jù)同步情況
--SQL節(jié)點(diǎn)2，創(chuàng)建存儲(chǔ)引擎為NDBCLUSTER的測(cè)試表
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節(jié)點(diǎn)1，查看在節(jié)點(diǎn)2創(chuàng)建的表
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)

看完了這篇文章，相信你對(duì)“CentOS 6.5中如何搭建MySQL集群7.4”有了一定的了解，如果想了解更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！