1、准备服务器
计划建立有5个节点的MySQL CLuster体系,需要用到5台服务器,但是我们做实验时没有这么多机器,可以只用3台,我就是一台57,一台55,一台56搭建了有5个节点的MySQL CLuster体系,将一个SQL节点一个数据节点一个SQL节点放在了,两台服务器上(172.16.57.55,172.16.57.56),将另一个SQL节点和一个数据节点放在了另外一台服务器上(172.16.57.55,172.16.57.56)。
节点配置说明
节点 对应的IP和端口
管理节点(1个) 172.16.57.57
SQL节点 (2个) 172.16.57.55
172.16.57.56
数据节点 (2个) 172.16.57.55
172.16.57.56
注意:目前这种设计存在的问题是管理节点是单点的,57挂掉后整个集群就会瘫痪,先不管这个问题,首先把这个简单的MySQL集群先搭建起来。
2、准备软件包
现在的mysql提供了一个专门作集群的安装包,这样就不用一个个的下载所需要的工具了。我在网上找到了最新的而且下载比较快的资源,第一步先是下载,有200M左右。
[root@oracle_test tmp]# cat /etc/issue
CentOS release 6.7 (Final)
Kernel \r on an \m
下载合适操作系统的安装包
[root@oracle_test tmp]# cd /var/tmp/
[root@oracle_test tmp]# ls
MySQL-Cluster-gpl-7.4.11-1.el7.x86_64.rpm-bundle.tar yum-mongoadmin-mir5Ej yum-oracle-1ll3sp yum-redisadmin-hUvT7d
二、安装
#所有节点:
#mysql的集群安装可以有三种方式,一是直接下载二进制使用,二是使用rpm安装,三是源码编译。
#这里使用rpm安装,每种安装方式略有不同。
#rpm包安装注意安装后的命令目录
#清除原理机器上的mysql:
[root@oracle_test tmp]# rpm -qa | grep -i mysql
mysql-community-common-5.7.11-1.el6.x86_64
mysql-community-libs-5.7.11-1.el6.x86_64
mysql-community-server-5.7.11-1.el6.x86_64
mysql-community-client-5.7.11-1.el6.x86_64
[root@oracle_test tmp]# rpm -e --nodeps mysql-community-common-5.7.11-1.el6.x86_64
[root@oracle_test tmp]# rpm -e --nodeps mysql-community-libs-5.7.11-1.el6.x86_64
[root@oracle_test tmp]# rpm -e --nodeps mysql-community-server-5.7.11-1.el6.x86_64
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
[root@oracle_test tmp]# rpm -e --nodeps mysql-community-client-5.7.11-1.el6.x86_64
管理节点:172.16.57.57
[root@T4 tmp]# pwd
/var/tmp
[root@T4 tmp]# rpm -ivh MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-server-gp########################################### [100%]
[root@T4 tmp]#rpm -Uvh MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm
[root@T4 tmp]# rpm -ivh MySQL-Cluster-shared-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-shared-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-shared-gp########################################### [100%]
#创建管理节点日志目录
[root@T4 tmp]# mkdir /var/lib/mysql-cluster
[root@T4 tmp]# chmod -R 1777 /var/lib/mysql-cluster
管理节点中有一个非常重要的参数叫NoOfReplicas,这个参数 指定了每一份数据被冗余存储在不同节点上面的份数,该参数一般至少应该被设置成2
[root@T4 tmp]# vi /var/lib/mysql-cluster/config.ini
[NDBD DEFAULT]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2 # Number of replicas
DataMemory=500M # How much memory to allocate for data storage
IndexMemory=500M # How much memory to allocate for index storage
# For DataMemory and IndexMemory, we have used the
# default values. Since the "world" database takes up
# only about 500KB, this should be more than enough for
# this example Cluster setup.
#[TCP DEFAULT]
#portnumber=3306
[NDB_MGMD]
NodeId=1 # Management process option
HostName=172.16.57.57 # Hostname or IP address of MGM node
DataDir=/var/lib/mysql-cluster # Directory for MGM node log files
[NDBD]
NodeId=2 # Options for data node "A" (one [ndbd] section per data node)
HostName=172.16.57.55 # Hostname or IP address
DataDir=/var/mysql/data # Directory for this data node's data files
[NDBD]
NodeId=3 # Options for data node "B" (one [ndbd] section per data node)
HostName=172.16.57.56 # Hostname or IP address
DataDir=/var/mysql/data # Directory for this data node's data files
[MYSQLD]
NodeId=4 # SQL node options:
HostName=172.16.57.55 # Hostname or IP address
# (additional mysqld connections can be
# specified for this node for various
# purposes such as running ndb_restore)
[MYSQLD]
NodeId=5 # SQL node options:
HostName=172.16.57.56 # Hostname or IP address
# (additional mysqld connections can be
# specified for this node for various
# purposes such as running ndb_restore)
注意要多加一个
[MYSQLD]备份才不会出问题
如果以前有安装删除文件ndb_1_config.bin.1
[root@T4 tmp]# cd /usr/mysql-cluster/
[root@T4 mysql-cluster]# ls
ndb_1_config.bin.1
[root@T4 mysql-cluster]# rm *
#启动管理节点
[root@T4 tmp]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.6.29 ndb-7.4.11
2016-06-06 10:37:25 [MgmtSrvr] INFO -- The default config directory '/usr/mysql-cluster' does not exist. Trying to create it...
2016-06-06 10:37:25 [MgmtSrvr] INFO -- Sucessfully created config directory
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 22: [DB] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 27: [DB] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 32: [API] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 39: [API] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 47: [API] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 49: [API] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 22: [DB] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 27: [DB] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 32: [API] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 39: [API] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 47: [API] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 49: [API] id is deprecated, use NodeId instead
[root@T4 tmp]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from 172.16.57.55)
id=3 (not connected, accepting connect from 172.16.57.56)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @172.16.57.57 (mysql-5.6.29 ndb-7.4.11)
[mysqld(API)] 4 node(s)
id=4 (not connected, accepting connect from 172.16.57.55)
id=5 (not connected, accepting connect from 172.16.57.56)
id=6 (not connected, accepting connect from any host)
id=7 (not connected, accepting connect from any host)
#存储节点:172.16.57.55,172.16.57.56
#安装MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm包
55:
[root@T2 tmp]#rpm -Uvh MySQL-Cluster-client-gpl-7.4.11-1.el6.x86_64.rpm
[root@T2 tmp]# rpm -ivh MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-server-gp########################################### [100%]
[root@T2 tmp]# rpm -ivh MySQL-Cluster-shared-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-shared-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-shared-gp########################################### [100%]
56:
[root@T3 tmp]# rpm -ivh MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-server-gp########################################### [100%]
其他和上述一样
#创建存储目录
两个节点均执行
[root@T2 tmp]# mkdir -p /var/mysql/data
[root@T2 tmp]# chmod -R 1777 /var/mysql/data
[root@T4 lib]# chown -R mysql:mysql /var/lib/mysql-cluster
#创建数据字典
两个节点均执行
[root@T2 tmp]# cd /usr/bin/
[root@T2 bin]# ll |grep mysql
-rwxr-xr-x 1 root root 11255 4月 5 20:08 mysqlbug
-rwxr-xr-x 1 root root 4334 4月 5 20:08 mysql_convert_table_format
-rwxr-xr-x 1 root root 26356 4月 5 20:08 mysqld_multi
-rwxr-xr-x 1 root root 24723 4月 5 20:08 mysqld_safe
-rwxr-xr-x 1 root root 7424 4月 5 20:08 mysqldumpslow
-rwxr-xr-x 1 root root 1366 4月 5 20:08 mysql_fix_extensions
-rwxr-xr-x 1 root root 34938 4月 5 20:08 mysqlhotcopy
-rwxr-xr-x 1 root root 34612 4月 5 20:08 mysql_install_db
-rwxr-xr-x 1 root root 4949586 4月 5 20:15 mysql_plugin
-rwxr-xr-x 1 root root 10106 4月 5 20:08 mysql_secure_installation
-rwxr-xr-x 1 root root 17584 4月 5 20:08 mysql_setpermission
-rwxr-xr-x 1 root root 9583306 4月 5 20:15 mysqltest
-rwxr-xr-x 1 root root 4492005 4月 5 20:11 mysql_tzinfo_to_sql
-rwxr-xr-x 1 root root 5148693 4月 5 20:15 mysql_upgrade
-rwxr-xr-x 1 root root 3976 4月 5 20:08 mysql_zap
SQL节点数据库系统文件初始化
[root@T2 bin]# mysql_install_db --user=mysql --datadir=/var/mysql/data
Installing MySQL system tables...2016-06-06 10:57:39 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-06-06 10:57:39 0 [Note] /usr/sbin/mysqld (mysqld 5.6.29-ndb-7.4.11-cluster-gpl) starting as process 27729 ...
2016-06-06 10:57:39 27729 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-06-06 10:57:39 27729 [Note] InnoDB: The InnoDB memory heap is disabled
2016-06-06 10:57:39 27729 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-06-06 10:57:39 27729 [Note] InnoDB: Memory barrier is not used
2016-06-06 10:57:39 27729 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-06-06 10:57:39 27729 [Note] InnoDB: Using Linux native AIO
2016-06-06 10:57:39 27729 [Note] InnoDB: Not using CPU crc32 instructions
2016-06-06 10:57:39 27729 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-06-06 10:57:39 27729 [Note] InnoDB: Completed initialization of buffer pool
2016-06-06 10:57:39 27729 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2016-06-06 10:57:39 27729 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2016-06-06 10:57:39 27729 [Note] InnoDB: Database physically writes the file full: wait...
2016-06-06 10:57:39 27729 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2016-06-06 10:57:40 27729 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2016-06-06 10:57:40 27729 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2016-06-06 10:57:40 27729 [Warning] InnoDB: New log files created, LSN=45781
2016-06-06 10:57:40 27729 [Note] InnoDB: Doublewrite buffer not found: creating new
2016-06-06 10:57:40 27729 [Note] InnoDB: Doublewrite buffer created
2016-06-06 10:57:40 27729 [Note] InnoDB: 128 rollback segment(s) are active.
2016-06-06 10:57:40 27729 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-06-06 10:57:40 27729 [Note] InnoDB: Foreign key constraint system tables created
2016-06-06 10:57:40 27729 [Note] InnoDB: Creating tablespace and datafile system tables.
2016-06-06 10:57:40 27729 [Note] InnoDB: Tablespace and datafile system tables created.
2016-06-06 10:57:40 27729 [Note] InnoDB: Waiting for purge to start
2016-06-06 10:57:40 27729 [Note] InnoDB: 5.6.29 started; log sequence number 0
2016-06-06 10:57:40 27729 [Note] Binlog end
2016-06-06 10:57:40 27729 [Note] InnoDB: FTS optimize thread exiting.
2016-06-06 10:57:40 27729 [Note] InnoDB: Starting shutdown...
2016-06-06 10:57:42 27729 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK
Filling help tables...2016-06-06 10:57:42 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-06-06 10:57:42 0 [Note] /usr/sbin/mysqld (mysqld 5.6.29-ndb-7.4.11-cluster-gpl) starting as process 27751 ...
2016-06-06 10:57:42 27751 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-06-06 10:57:42 27751 [Note] InnoDB: The InnoDB memory heap is disabled
2016-06-06 10:57:42 27751 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-06-06 10:57:42 27751 [Note] InnoDB: Memory barrier is not used
2016-06-06 10:57:42 27751 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-06-06 10:57:42 27751 [Note] InnoDB: Using Linux native AIO
2016-06-06 10:57:42 27751 [Note] InnoDB: Not using CPU crc32 instructions
2016-06-06 10:57:42 27751 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-06-06 10:57:42 27751 [Note] InnoDB: Completed initialization of buffer pool
2016-06-06 10:57:42 27751 [Note] InnoDB: Highest supported file format is Barracuda.
2016-06-06 10:57:42 27751 [Note] InnoDB: 128 rollback segment(s) are active.
2016-06-06 10:57:42 27751 [Note] InnoDB: Waiting for purge to start
2016-06-06 10:57:42 27751 [Note] InnoDB: 5.6.29 started; log sequence number 1625977
2016-06-06 10:57:42 27751 [Note] Binlog end
2016-06-06 10:57:42 27751 [Note] InnoDB: FTS optimize thread exiting.
2016-06-06 10:57:42 27751 [Note] InnoDB: Starting shutdown...
2016-06-06 10:57:44 27751 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h T2 password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
New default config file was created as /usr/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings
#创建my.cnf配置文件
两个节点均执行
[root@T2 bin]# vi /etc/my.cnf
[CLIENT]
socket=/var/mysql/mysql.sock
[MYSQLD]
user=mysql
datadir=/var/mysql/data
ndbcluster
ndb-connectstring=172.16.57.57
default_storage_engine=ndbcluster
[MYSQL_CLUSTER]
ndb-connectstring=172.16.57.57
[MYSQLD_SAFE]
log-error=/var/mysql/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
[root@T2 mysql]# mkdir log
注意这里的ip写前面我们配置的管理节点
启动ndbd服务
ndbd服务第一次启动时需要加上--initial选项,以后不需要
55:
[root@T2 mysql]# ndbd --initial
2016-06-06 11:12:02 [ndbd] INFO -- Angel connected to '172.16.57.57:1186'
2016-06-06 11:12:02 [ndbd] INFO -- Angel allocated nodeid: 2
56:
[root@T3 bin]# ndbd --initial
2016-06-06 11:12:12 [ndbd] INFO -- Angel connected to '172.16.57.57:1186'
2016-06-06 11:12:12 [ndbd] INFO -- Angel allocated nodeid: 3
可以看到管理节点显示如下信息
ndb_mgm> Node 2: Started (version 7.4.11)
Node 3: Started (version 7.4.11)
正常启动方式:
[root@T2 bin]# ndbd
2016-06-06 11:47:48 [ndbd] INFO -- Angel connected to '172.16.57.57:1186'
sql节点:
#安装server包和client包
两个节点均执行
[root@T2 mysql]# cd /var/tmp/
[root@T2 tmp]# rpm -ivh MySQL-Cluster-client-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-client-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-client-gp########################################### [100%]
[root@T2 tmp]# rpm -ivh MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-server-gp########################################### [100%]
[root@T2 tmp]# rpm -ivh MySQL-Cluster-shared-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-shared-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-shared-gp########################################### [100%]
[root@T2 tmp]# mkdir -p /var/run/mysqld/
[root@T2 tmp]# chmod -R 777 /var/run/mysqld/
权限都要修改
[root@T2 tmp]# chown -R mysql:mysql /var/mysql/
[root@T3 data]# chown mysql.mysql /var/mysql/ -R
#安装数据库
此时sql节点和数据节点在同一机器,由于在同一个节点,已经安装
启动SQL节点
55:
[root@T2 tmp]# service mysql start
Starting MySQL.. SUCCESS!
56:
[root@T3 mysql]# service mysql start
Starting MySQL.. SUCCESS!
注意顺序。
1. 启动管理节点:ndb_mgmd -f /var/lib/mysql-cluster/config.ini
2. 启动存储节点:ndbd --initial
初次启动时需要加--initial参数,以后只需要执行ndbd即可;
3. 启动SQL节点:service mysql start
注意:重新启动不准使用ndbd --initial,因为这样会删除所有表和数据
#--initial操作会清空所有表和数据
一些管理方法
管理节点关闭
[root@localhost /]# /usr/local/mysql/bin/ndb_mgm -e shutdown
#成功后终端打印出以下信息
Connected to Management Server at: 192.168.15.236:1186
3 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.
数据节点关闭
[root@localhost /]# /etc/rc.d/init.d/mysqld stop
或者
[root@localhost mysql]# /etc/init.d/mysql stop
Shutting down MySQL.. SUCCESS!
/usr/local/mysql/bin/mysqladmin -uroot shutdown
SQL节点关闭
最直接的方式:
[root@localhost mysql]# /usr/local/mysql/bin/mysqladmin -uroot shutdown
[root@localhost /]# /etc/rc.d/init.d/mysqld stop
或者
[root@localhost mysql]# /etc/init.d/mysql stop
Shutting down MySQL.. SUCCESS!
关闭集群步骤
先关闭管理节点,然后关闭SQL节点和数据节点。
[root@T4 lib]# ndb_mgm -e shutdown
Connected to Management Server at: localhost:1186
3 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.
[root@T2 tmp]# service mysql stop
Shutting down MySQL..... SUCCESS!
[root@T3 data]# service mysql stop
Shutting down MySQL.... SUCCESS!
集群监控
在管理节点监控集群状态,这里就是57节点
[root@T4 tmp]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @172.16.57.55 (mysql-5.6.29 ndb-7.4.11, Nodegroup: 0, *)
id=3 @172.16.57.56 (mysql-5.6.29 ndb-7.4.11, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @172.16.57.57 (mysql-5.6.29 ndb-7.4.11)
[mysqld(API)] 2 node(s)
id=4 @172.16.57.55 (mysql-5.6.29 ndb-7.4.11)
id=5 @172.16.57.56 (mysql-5.6.29 ndb-7.4.11)
其他的命令
[root@T4 tmp]# ndb_mgm -e "all status"
Connected to Management Server at: localhost:1186
Node 2: started (mysql-5.6.29 ndb-7.4.11)
Node 3: started (mysql-5.6.29 ndb-7.4.11)
[root@T4 tmp]# ndb_mgm -e "all report memory"
Connected to Management Server at: localhost:1186
Node 2: Data usage is 0%(23 32K pages of total 16000)
Node 2: Index usage is 0%(20 8K pages of total 64032)
Node 3: Data usage is 0%(23 32K pages of total 16000)
Node 3: Index usage is 0%(20 8K pages of total 64032)
测试集群
测试集群在sql节点进行
使用如下命令改root密码
[root@T3 data]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] Y #是否删除匿名用户,生产环境建议删除
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] Y #是否禁止root远程登录,根据自己的需求选择Y/n并回车,建议禁止
... Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] Y #是否删除test数据库,Y直接回车
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] Y #是否重新加载权限表,Y直接回车
... Success!
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
Cleaning up...
密码这里设置为fafa123
[root@T3 data]# ln -s /var/mysql/mysql.sock /var/lib/mysql/mysql.sock
[root@T2 tmp]# ln -s /var/mysql/mysql.sock /var/lib/mysql/mysql.sock
数据库重启以后,以上软连接会丢失,需要重新生成
把my.cnf里的/var/mysql/mysql.sock都改成了/var/lib/mysql/mysql.sock,保存后重启Mysql,这样也可以
[root@T3 data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.29-ndb-7.4.11-cluster-gpl MySQL Cluster Community Server (GPL)
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>
创建用户密码
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'fafa123';
Query OK, 0 rows affected (0.02 sec)
查看用户列表
mysql> select user, host, password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *8C53D2FE0782BF01ACB952CF9C84B79701B310E5 |
| root | 127.0.0.1 | *8C53D2FE0782BF01ACB952CF9C84B79701B310E5 |
| root | ::1 | *8C53D2FE0782BF01ACB952CF9C84B79701B310E5 |
| root | % | *8C53D2FE0782BF01ACB952CF9C84B79701B310E5 |
+------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
给新的root用户,一个远端可以读写的权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*8C53D2FE0782BF01ACB952CF9C84B79701B310E5' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
注意这里的密码是红色字体的密码
查看在55上查看56
[root@T2 tmp]# mysql -h 172.16.57.56 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.29-ndb-7.4.11-cluster-gpl MySQL Cluster Community Server (GPL)
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>
成功
注意:重新启动不准使用ndbd --initial,因为这样会删除所有表和数据
#--initial操作会清空所有表和数据
节点 对应的IP和端口
管理节点(1个) 172.16.57.57
SQL节点 (2个) 172.16.57.55
172.16.57.56
数据节点 (2个) 172.16.57.55
172.16.57.56
注意:目前这种设计存在的问题是管理节点是单点的,57挂掉后整个集群就会瘫痪,先不管这个问题,首先把这个简单的MySQL集群先搭建起来。
2、准备软件包
现在的mysql提供了一个专门作集群的安装包,这样就不用一个个的下载所需要的工具了。我在网上找到了最新的而且下载比较快的资源,第一步先是下载,有200M左右。
[root@oracle_test tmp]# cat /etc/issue
CentOS release 6.7 (Final)
Kernel \r on an \m
下载合适操作系统的安装包
[root@oracle_test tmp]# cd /var/tmp/
[root@oracle_test tmp]# ls
MySQL-Cluster-gpl-7.4.11-1.el7.x86_64.rpm-bundle.tar yum-mongoadmin-mir5Ej yum-oracle-1ll3sp yum-redisadmin-hUvT7d
二、安装
#所有节点:
#mysql的集群安装可以有三种方式,一是直接下载二进制使用,二是使用rpm安装,三是源码编译。
#这里使用rpm安装,每种安装方式略有不同。
#rpm包安装注意安装后的命令目录
#清除原理机器上的mysql:
[root@oracle_test tmp]# rpm -qa | grep -i mysql
mysql-community-common-5.7.11-1.el6.x86_64
mysql-community-libs-5.7.11-1.el6.x86_64
mysql-community-server-5.7.11-1.el6.x86_64
mysql-community-client-5.7.11-1.el6.x86_64
[root@oracle_test tmp]# rpm -e --nodeps mysql-community-common-5.7.11-1.el6.x86_64
[root@oracle_test tmp]# rpm -e --nodeps mysql-community-libs-5.7.11-1.el6.x86_64
[root@oracle_test tmp]# rpm -e --nodeps mysql-community-server-5.7.11-1.el6.x86_64
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
[root@oracle_test tmp]# rpm -e --nodeps mysql-community-client-5.7.11-1.el6.x86_64
管理节点:172.16.57.57
[root@T4 tmp]# pwd
/var/tmp
[root@T4 tmp]# rpm -ivh MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-server-gp########################################### [100%]
[root@T4 tmp]#rpm -Uvh MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm
[root@T4 tmp]# rpm -ivh MySQL-Cluster-shared-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-shared-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-shared-gp########################################### [100%]
#创建管理节点日志目录
[root@T4 tmp]# mkdir /var/lib/mysql-cluster
[root@T4 tmp]# chmod -R 1777 /var/lib/mysql-cluster
管理节点中有一个非常重要的参数叫NoOfReplicas,这个参数 指定了每一份数据被冗余存储在不同节点上面的份数,该参数一般至少应该被设置成2
[root@T4 tmp]# vi /var/lib/mysql-cluster/config.ini
[NDBD DEFAULT]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2 # Number of replicas
DataMemory=500M # How much memory to allocate for data storage
IndexMemory=500M # How much memory to allocate for index storage
# For DataMemory and IndexMemory, we have used the
# default values. Since the "world" database takes up
# only about 500KB, this should be more than enough for
# this example Cluster setup.
#[TCP DEFAULT]
#portnumber=3306
[NDB_MGMD]
NodeId=1 # Management process option
HostName=172.16.57.57 # Hostname or IP address of MGM node
DataDir=/var/lib/mysql-cluster # Directory for MGM node log files
[NDBD]
NodeId=2 # Options for data node "A" (one [ndbd] section per data node)
HostName=172.16.57.55 # Hostname or IP address
DataDir=/var/mysql/data # Directory for this data node's data files
[NDBD]
NodeId=3 # Options for data node "B" (one [ndbd] section per data node)
HostName=172.16.57.56 # Hostname or IP address
DataDir=/var/mysql/data # Directory for this data node's data files
[MYSQLD]
NodeId=4 # SQL node options:
HostName=172.16.57.55 # Hostname or IP address
# (additional mysqld connections can be
# specified for this node for various
# purposes such as running ndb_restore)
[MYSQLD]
NodeId=5 # SQL node options:
HostName=172.16.57.56 # Hostname or IP address
# (additional mysqld connections can be
# specified for this node for various
# purposes such as running ndb_restore)
注意要多加一个
[MYSQLD]备份才不会出问题
如果以前有安装删除文件ndb_1_config.bin.1
[root@T4 tmp]# cd /usr/mysql-cluster/
[root@T4 mysql-cluster]# ls
ndb_1_config.bin.1
[root@T4 mysql-cluster]# rm *
#启动管理节点
[root@T4 tmp]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.6.29 ndb-7.4.11
2016-06-06 10:37:25 [MgmtSrvr] INFO -- The default config directory '/usr/mysql-cluster' does not exist. Trying to create it...
2016-06-06 10:37:25 [MgmtSrvr] INFO -- Sucessfully created config directory
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 22: [DB] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 27: [DB] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 32: [API] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 39: [API] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 47: [API] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 49: [API] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 22: [DB] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 27: [DB] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 32: [API] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 39: [API] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 47: [API] id is deprecated, use NodeId instead
2016-06-06 10:37:25 [MgmtSrvr] WARNING -- at line 49: [API] id is deprecated, use NodeId instead
[root@T4 tmp]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from 172.16.57.55)
id=3 (not connected, accepting connect from 172.16.57.56)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @172.16.57.57 (mysql-5.6.29 ndb-7.4.11)
[mysqld(API)] 4 node(s)
id=4 (not connected, accepting connect from 172.16.57.55)
id=5 (not connected, accepting connect from 172.16.57.56)
id=6 (not connected, accepting connect from any host)
id=7 (not connected, accepting connect from any host)
#存储节点:172.16.57.55,172.16.57.56
#安装MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm包
55:
[root@T2 tmp]#rpm -Uvh MySQL-Cluster-client-gpl-7.4.11-1.el6.x86_64.rpm
[root@T2 tmp]# rpm -ivh MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-server-gp########################################### [100%]
[root@T2 tmp]# rpm -ivh MySQL-Cluster-shared-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-shared-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-shared-gp########################################### [100%]
56:
[root@T3 tmp]# rpm -ivh MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-server-gp########################################### [100%]
其他和上述一样
#创建存储目录
两个节点均执行
[root@T2 tmp]# mkdir -p /var/mysql/data
[root@T2 tmp]# chmod -R 1777 /var/mysql/data
[root@T4 lib]# chown -R mysql:mysql /var/lib/mysql-cluster
#创建数据字典
两个节点均执行
[root@T2 tmp]# cd /usr/bin/
[root@T2 bin]# ll |grep mysql
-rwxr-xr-x 1 root root 11255 4月 5 20:08 mysqlbug
-rwxr-xr-x 1 root root 4334 4月 5 20:08 mysql_convert_table_format
-rwxr-xr-x 1 root root 26356 4月 5 20:08 mysqld_multi
-rwxr-xr-x 1 root root 24723 4月 5 20:08 mysqld_safe
-rwxr-xr-x 1 root root 7424 4月 5 20:08 mysqldumpslow
-rwxr-xr-x 1 root root 1366 4月 5 20:08 mysql_fix_extensions
-rwxr-xr-x 1 root root 34938 4月 5 20:08 mysqlhotcopy
-rwxr-xr-x 1 root root 34612 4月 5 20:08 mysql_install_db
-rwxr-xr-x 1 root root 4949586 4月 5 20:15 mysql_plugin
-rwxr-xr-x 1 root root 10106 4月 5 20:08 mysql_secure_installation
-rwxr-xr-x 1 root root 17584 4月 5 20:08 mysql_setpermission
-rwxr-xr-x 1 root root 9583306 4月 5 20:15 mysqltest
-rwxr-xr-x 1 root root 4492005 4月 5 20:11 mysql_tzinfo_to_sql
-rwxr-xr-x 1 root root 5148693 4月 5 20:15 mysql_upgrade
-rwxr-xr-x 1 root root 3976 4月 5 20:08 mysql_zap
SQL节点数据库系统文件初始化
[root@T2 bin]# mysql_install_db --user=mysql --datadir=/var/mysql/data
Installing MySQL system tables...2016-06-06 10:57:39 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-06-06 10:57:39 0 [Note] /usr/sbin/mysqld (mysqld 5.6.29-ndb-7.4.11-cluster-gpl) starting as process 27729 ...
2016-06-06 10:57:39 27729 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-06-06 10:57:39 27729 [Note] InnoDB: The InnoDB memory heap is disabled
2016-06-06 10:57:39 27729 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-06-06 10:57:39 27729 [Note] InnoDB: Memory barrier is not used
2016-06-06 10:57:39 27729 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-06-06 10:57:39 27729 [Note] InnoDB: Using Linux native AIO
2016-06-06 10:57:39 27729 [Note] InnoDB: Not using CPU crc32 instructions
2016-06-06 10:57:39 27729 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-06-06 10:57:39 27729 [Note] InnoDB: Completed initialization of buffer pool
2016-06-06 10:57:39 27729 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2016-06-06 10:57:39 27729 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2016-06-06 10:57:39 27729 [Note] InnoDB: Database physically writes the file full: wait...
2016-06-06 10:57:39 27729 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2016-06-06 10:57:40 27729 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2016-06-06 10:57:40 27729 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2016-06-06 10:57:40 27729 [Warning] InnoDB: New log files created, LSN=45781
2016-06-06 10:57:40 27729 [Note] InnoDB: Doublewrite buffer not found: creating new
2016-06-06 10:57:40 27729 [Note] InnoDB: Doublewrite buffer created
2016-06-06 10:57:40 27729 [Note] InnoDB: 128 rollback segment(s) are active.
2016-06-06 10:57:40 27729 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-06-06 10:57:40 27729 [Note] InnoDB: Foreign key constraint system tables created
2016-06-06 10:57:40 27729 [Note] InnoDB: Creating tablespace and datafile system tables.
2016-06-06 10:57:40 27729 [Note] InnoDB: Tablespace and datafile system tables created.
2016-06-06 10:57:40 27729 [Note] InnoDB: Waiting for purge to start
2016-06-06 10:57:40 27729 [Note] InnoDB: 5.6.29 started; log sequence number 0
2016-06-06 10:57:40 27729 [Note] Binlog end
2016-06-06 10:57:40 27729 [Note] InnoDB: FTS optimize thread exiting.
2016-06-06 10:57:40 27729 [Note] InnoDB: Starting shutdown...
2016-06-06 10:57:42 27729 [Note] InnoDB: Shutdown completed; log sequence number 1625977
OK
Filling help tables...2016-06-06 10:57:42 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-06-06 10:57:42 0 [Note] /usr/sbin/mysqld (mysqld 5.6.29-ndb-7.4.11-cluster-gpl) starting as process 27751 ...
2016-06-06 10:57:42 27751 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-06-06 10:57:42 27751 [Note] InnoDB: The InnoDB memory heap is disabled
2016-06-06 10:57:42 27751 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-06-06 10:57:42 27751 [Note] InnoDB: Memory barrier is not used
2016-06-06 10:57:42 27751 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-06-06 10:57:42 27751 [Note] InnoDB: Using Linux native AIO
2016-06-06 10:57:42 27751 [Note] InnoDB: Not using CPU crc32 instructions
2016-06-06 10:57:42 27751 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-06-06 10:57:42 27751 [Note] InnoDB: Completed initialization of buffer pool
2016-06-06 10:57:42 27751 [Note] InnoDB: Highest supported file format is Barracuda.
2016-06-06 10:57:42 27751 [Note] InnoDB: 128 rollback segment(s) are active.
2016-06-06 10:57:42 27751 [Note] InnoDB: Waiting for purge to start
2016-06-06 10:57:42 27751 [Note] InnoDB: 5.6.29 started; log sequence number 1625977
2016-06-06 10:57:42 27751 [Note] Binlog end
2016-06-06 10:57:42 27751 [Note] InnoDB: FTS optimize thread exiting.
2016-06-06 10:57:42 27751 [Note] InnoDB: Starting shutdown...
2016-06-06 10:57:44 27751 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h T2 password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
New default config file was created as /usr/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings
#创建my.cnf配置文件
两个节点均执行
[root@T2 bin]# vi /etc/my.cnf
[CLIENT]
socket=/var/mysql/mysql.sock
[MYSQLD]
user=mysql
datadir=/var/mysql/data
ndbcluster
ndb-connectstring=172.16.57.57
default_storage_engine=ndbcluster
[MYSQL_CLUSTER]
ndb-connectstring=172.16.57.57
[MYSQLD_SAFE]
log-error=/var/mysql/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
[root@T2 mysql]# mkdir log
注意这里的ip写前面我们配置的管理节点
启动ndbd服务
ndbd服务第一次启动时需要加上--initial选项,以后不需要
55:
[root@T2 mysql]# ndbd --initial
2016-06-06 11:12:02 [ndbd] INFO -- Angel connected to '172.16.57.57:1186'
2016-06-06 11:12:02 [ndbd] INFO -- Angel allocated nodeid: 2
56:
[root@T3 bin]# ndbd --initial
2016-06-06 11:12:12 [ndbd] INFO -- Angel connected to '172.16.57.57:1186'
2016-06-06 11:12:12 [ndbd] INFO -- Angel allocated nodeid: 3
可以看到管理节点显示如下信息
ndb_mgm> Node 2: Started (version 7.4.11)
Node 3: Started (version 7.4.11)
正常启动方式:
[root@T2 bin]# ndbd
2016-06-06 11:47:48 [ndbd] INFO -- Angel connected to '172.16.57.57:1186'
sql节点:
#安装server包和client包
两个节点均执行
[root@T2 mysql]# cd /var/tmp/
[root@T2 tmp]# rpm -ivh MySQL-Cluster-client-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-client-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-client-gp########################################### [100%]
[root@T2 tmp]# rpm -ivh MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-server-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-server-gp########################################### [100%]
[root@T2 tmp]# rpm -ivh MySQL-Cluster-shared-gpl-7.4.11-1.el6.x86_64.rpm
warning: MySQL-Cluster-shared-gpl-7.4.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-Cluster-shared-gp########################################### [100%]
[root@T2 tmp]# mkdir -p /var/run/mysqld/
[root@T2 tmp]# chmod -R 777 /var/run/mysqld/
权限都要修改
[root@T2 tmp]# chown -R mysql:mysql /var/mysql/
[root@T3 data]# chown mysql.mysql /var/mysql/ -R
#安装数据库
此时sql节点和数据节点在同一机器,由于在同一个节点,已经安装
启动SQL节点
55:
[root@T2 tmp]# service mysql start
Starting MySQL.. SUCCESS!
56:
[root@T3 mysql]# service mysql start
Starting MySQL.. SUCCESS!
注意顺序。
1. 启动管理节点:ndb_mgmd -f /var/lib/mysql-cluster/config.ini
2. 启动存储节点:ndbd --initial
初次启动时需要加--initial参数,以后只需要执行ndbd即可;
3. 启动SQL节点:service mysql start
注意:重新启动不准使用ndbd --initial,因为这样会删除所有表和数据
#--initial操作会清空所有表和数据
一些管理方法
管理节点关闭
[root@localhost /]# /usr/local/mysql/bin/ndb_mgm -e shutdown
#成功后终端打印出以下信息
Connected to Management Server at: 192.168.15.236:1186
3 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.
数据节点关闭
[root@localhost /]# /etc/rc.d/init.d/mysqld stop
或者
[root@localhost mysql]# /etc/init.d/mysql stop
Shutting down MySQL.. SUCCESS!
/usr/local/mysql/bin/mysqladmin -uroot shutdown
SQL节点关闭
最直接的方式:
[root@localhost mysql]# /usr/local/mysql/bin/mysqladmin -uroot shutdown
[root@localhost /]# /etc/rc.d/init.d/mysqld stop
或者
[root@localhost mysql]# /etc/init.d/mysql stop
Shutting down MySQL.. SUCCESS!
关闭集群步骤
先关闭管理节点,然后关闭SQL节点和数据节点。
[root@T4 lib]# ndb_mgm -e shutdown
Connected to Management Server at: localhost:1186
3 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.
[root@T2 tmp]# service mysql stop
Shutting down MySQL..... SUCCESS!
[root@T3 data]# service mysql stop
Shutting down MySQL.... SUCCESS!
集群监控
在管理节点监控集群状态,这里就是57节点
[root@T4 tmp]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @172.16.57.55 (mysql-5.6.29 ndb-7.4.11, Nodegroup: 0, *)
id=3 @172.16.57.56 (mysql-5.6.29 ndb-7.4.11, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @172.16.57.57 (mysql-5.6.29 ndb-7.4.11)
[mysqld(API)] 2 node(s)
id=4 @172.16.57.55 (mysql-5.6.29 ndb-7.4.11)
id=5 @172.16.57.56 (mysql-5.6.29 ndb-7.4.11)
其他的命令
[root@T4 tmp]# ndb_mgm -e "all status"
Connected to Management Server at: localhost:1186
Node 2: started (mysql-5.6.29 ndb-7.4.11)
Node 3: started (mysql-5.6.29 ndb-7.4.11)
[root@T4 tmp]# ndb_mgm -e "all report memory"
Connected to Management Server at: localhost:1186
Node 2: Data usage is 0%(23 32K pages of total 16000)
Node 2: Index usage is 0%(20 8K pages of total 64032)
Node 3: Data usage is 0%(23 32K pages of total 16000)
Node 3: Index usage is 0%(20 8K pages of total 64032)
测试集群
测试集群在sql节点进行
使用如下命令改root密码
[root@T3 data]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] Y #是否删除匿名用户,生产环境建议删除
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] Y #是否禁止root远程登录,根据自己的需求选择Y/n并回车,建议禁止
... Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] Y #是否删除test数据库,Y直接回车
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] Y #是否重新加载权限表,Y直接回车
... Success!
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
Cleaning up...
密码这里设置为fafa123
[root@T3 data]# ln -s /var/mysql/mysql.sock /var/lib/mysql/mysql.sock
[root@T2 tmp]# ln -s /var/mysql/mysql.sock /var/lib/mysql/mysql.sock
数据库重启以后,以上软连接会丢失,需要重新生成
把my.cnf里的/var/mysql/mysql.sock都改成了/var/lib/mysql/mysql.sock,保存后重启Mysql,这样也可以
[root@T3 data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.29-ndb-7.4.11-cluster-gpl MySQL Cluster Community Server (GPL)
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>
创建用户密码
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'fafa123';
Query OK, 0 rows affected (0.02 sec)
查看用户列表
mysql> select user, host, password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *8C53D2FE0782BF01ACB952CF9C84B79701B310E5 |
| root | 127.0.0.1 | *8C53D2FE0782BF01ACB952CF9C84B79701B310E5 |
| root | ::1 | *8C53D2FE0782BF01ACB952CF9C84B79701B310E5 |
| root | % | *8C53D2FE0782BF01ACB952CF9C84B79701B310E5 |
+------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
给新的root用户,一个远端可以读写的权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*8C53D2FE0782BF01ACB952CF9C84B79701B310E5' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
注意这里的密码是红色字体的密码
查看在55上查看56
[root@T2 tmp]# mysql -h 172.16.57.56 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.29-ndb-7.4.11-cluster-gpl MySQL Cluster Community Server (GPL)
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>
成功
注意:重新启动不准使用ndbd --initial,因为这样会删除所有表和数据
#--initial操作会清空所有表和数据