CentOS 6.5中如何搭建MySQL集群7.4

85次阅读

没有评论

共计 12614 个字符，预计需要花费 32 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 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

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 delete from mysql.user where (user,host) not in (select root , localhost
Query OK, 5 rows affected (0.15 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)

mysql select * from emp;
+——+
| id |
+——+
| 10 |
+——+
1 row in set (0.13 sec)

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

正文完