各節點資訊如下：

管理節點：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)

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

相關文章