Mysql高开用集群搭建
环境rhel6.5x64
192.168.20.11 mysql11
192.168.29.12 mysql12
拓扑
环境
关闭iptables
关闭selinux
yum -y install ncurses-devel gcc gcc-c++make autoconf automake zlib zlib-devel openssl openssl-devel pcre-devel
开始安装配置mysql11
1
2
3
4
5
6
7
8
9
10
11
12
# tar -xf MySQL-Cluster-gpl-7.3.7-1.el6.x86_64.rpm-bundle.tar
# scpMySQL-Cluster-gpl-7.3.7-1.el6.x86_64.rpm-bundle.tar root@192.168.20.12:/root
# yum -y remove mysql*
# mkdir /var/lib/mysql
# mkdir /var/lib/mysql-cluster
# mkdir /var/run/mysqld
# rpm -ivhMySQL-Cluster-server-gpl-7.3.7-1.el6.x86_64.rpm
# id mysql
uid=498(mysql) gid=498(mysql)groups=498(mysql)
# chown mysql:mysql -R/var/lib/mysql
# chown mysql:mysql -R/var/lib/mysql-cluster
# chown mysql:mysql -R/var/run/mysqld/
配置mysql12
1
2
3
4
# yum remove mysql -y
# rpm -qa | grep mysql*
mysql-libs-5.1.71-1.el6.x86_64
# rpm -e --nodepsmysql-libs-5.1.71-1.el6.x86_64
其实我觉得/var/lib/mysql/var/lib/mysql-cluster/var/run/mysqld这三个路径会自动生成
来试试
1
# rpm -ivhMySQL-Cluster-server-gpl-7.3.7-1.el6.x86_64.rpm
看看，这里有了
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# ls /var/lib/mysql
ibdata1 ib_logfile1ndbinfo RPM_UPGRADE_HISTORY test
ib_logfile0 mysql performance_schema RPM_UPGRADE_MARKER-LAST
# ls/var/lib/mysql-cluster/
# mkdir/var/lib/mysql-cluster
mkdir: cannot create directory`/var/lib/mysql-cluster': File exists
# id mysql
uid=498(mysql) gid=498(mysql)groups=498(mysql)
# ls/var/run/mysqld
# mkdir /var/run/mysqld
mkdir: cannot create directory`/var/run/mysqld': File exists
# /var/run/mysqld
-bash: /var/run/mysqld: is a directory
# ll -d /var/run/mysqld
drwxr-xr-x 2 root root 4096 Aug 27 23:29/var/run/mysqld
# ll -d/var/lib/mysql-cluster
drwxr-xr-x 2 root root 4096 Aug 27 23:29/var/lib/mysql-cluster
# ll -d /var/lib/mysql
drwxr-xr-x 6 mysql mysql 4096 Aug 27 23:53/var/lib/mysql
#
改成mysql用户所有吧
1
2
3
# chown mysql:mysql -R/var/lib/mysql
# chown mysql:mysql -R/var/lib/mysql-cluster
# chown mysql:mysql -R/var/run/mysqld/
配置mysql11成为管理节点主要是两个命令：ndb_mgmd 和 ndb_mgm
1
2
3
4
5
6
7
8
# which ndb_mgm
/usr/bin/ndb_mgm
# rpm -qf /usr/bin/ndb_mgm
MySQL-Cluster-server-gpl-7.3.7-1.el6.x86_64
# which ndb_mgmd
/usr/sbin/ndb_mgmd
# rpm -qf/usr/sbin/ndb_mgmd
MySQL-Cluster-server-gpl-7.3.7-1.el6.x86_64
生成配置文件
1
2
# cd /var/lib/mysql-cluster
# vimconfig.ini
#配置文件的内容：
NoOfReplicas=2 #数据写入数量。2 表示两份，这样当其中一个存储节点坏了，另一个还可以正常使用。
DataMemory=200M #配置数据存储可以使用的内存
IndexMemory=100M #配置缓存索引可以使用的内存
id=1
datadir=/var/lib/mysql-cluster # 指定存放管理结点的日志
HostName=192.168.20.11 #管理结点的 IP 地址。本机 IP
###### data node options: #存储结点
HostName=192.168.20.11
DataDir=/var/lib/mysql #mysql 数据存储路径
id=2
HostName=192.168.20.12
DataDir=/var/lib/mysql
id=3
# SQL node options: #关于 SQL 结点
HostName=192.168.20.11 # 指定可以连接 ndbd 引擎的 sql 结点。
id=4
HostName=192.168.20.12 # 指定可以连接 ndbd 引擎的 sql 结点。
id=5
mysql11数据节点+SQL 节点配置文档：配置的内容要手动写呢！
1
# vim/etc/my.cnf
datadir=/var/lib/mysql #mysql数据存储路径[单独的SQL节点不需要这行
ndbcluster #启动 ndb 引擎
ndb-connectstring=192.168.20.11 # 管理节点 IP 地址
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
ndb-connectstring=192.168.20.11 # 管理节点 IP 地址
再装个客户端吧
1
# rpm -ivhMySQL-Cluster-client-gpl-7.3.7-1.el6.x86_64.rpm
mysql12数据节点+SQL 节点配置文档客户端装上
# rpm -ivhMySQL-Cluster-client-gpl-7.3.7-1.el6.x86_64.rpm
把mysql11上 的配置文件弄过来吧
1
# scp root@192.168.20.11:/etc/my.cnf/etc/my.cnf
SQL Cluster 初次启动命令以及用户密码更改调整：（请严格按照次序启动）启动 mysql cluster先启动顺序：管理结点服务需要手动启动 ndbd 数据结点服务-》sql 结点服务
关闭顺序：关闭管理结点服务关闭管理结点服务后，nbdb 数据结点服务会自动关闭-》手动把 sql结点服务关了。
执行初次启动前请先确认将两台机器的防火墙关闭（service iptables stop 或者设定防火墙端口可通，两个端口即：通讯端口 1186、数据端口 3306 )
启动管理结点命令：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# ndb_mgmd -f/var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.6.21ndb-7.3.7
2016-08-28 00:28:34 INFO -- The default config directory'/usr/mysql-cluster' does not exist. Trying to create it...
2016-08-28 00:28:34 INFO -- Sucessfully created config directory
2016-08-28 00:28:34 WARNING-- at line 7: id is deprecated, useNodeId instead
2016-08-28 00:28:34 WARNING-- at line 16: id is deprecated, useNodeId instead
2016-08-28 00:28:34 WARNING-- at line 21: id is deprecated, use NodeIdinstead
2016-08-28 00:28:34 WARNING-- at line 26: id is deprecated, useNodeId instead
2016-08-28 00:28:34 WARNING-- at line 30: id is deprecated, useNodeId instead
2016-08-28 00:28:34 WARNING-- at line 30: Cluster configuration warning:
arbitrator with id 1 and db node with id 2 on same host 192.168.20.11
Running arbitrator on the same host as a database node may
cause complete cluster shutdown in case of host failure.
2016-08-28 00:28:34 WARNING-- at line 7: id is deprecated, useNodeId instead
2016-08-28 00:28:34 WARNING-- at line 16: id is deprecated, useNodeId instead
2016-08-28 00:28:34 WARNING-- at line 21: id is deprecated, use NodeIdinstead
2016-08-28 00:28:34 WARNING-- at line 26: id is deprecated, useNodeId instead
2016-08-28 00:28:34 WARNING-- at line 30: id is deprecated, useNodeId instead
2016-08-28 00:28:34 WARNING-- at line 30: Cluster configuration warning:
arbitrator with id 1 and db node with id 2 on same host 192.168.20.11
Running arbitrator on the same host as a database node may
cause complete cluster shutdown in case of host failure.
#
#说明：仲裁员 （ID1） 和 DB（ID2）节点上同一主机192.168.20.11在同一台主机作为数据库节点上运行仲裁人
造成完整的集群关机的情况下主机发生故障。虽然报了一些警告，但是，我们已经执行成功了。
查看端口号：
1
2
3
4
5
# netstat -anptu | grep 1186
tcp 0 0 0.0.0.0:1186 0.0.0.0:* LISTEN 2084/ndb_mgmd
tcp 0 0 127.0.0.1:1186 127.0.0.1:59216 ESTABLISHED 2084/ndb_mgmd
tcp 0 0 127.0.0.1:59216 127.0.0.1:1186 ESTABLISHED 2084/ndb_mgmd
#
查看日志存储位置：
1
2
3
4
5
6
7
# ll/var/lib/mysql-cluster/
total 16
-rw-r--r-- 1 root root 401 Aug 28 00:11config.ini
-rw-r--r-- 1 root root 699 Aug 28 00:28ndb_1_cluster.log
-rw-r--r-- 1 root root26 Aug 28 00:28 ndb_1_out.log
-rw-r--r-- 1 root root 4 Aug 28 00:28 ndb_1.pid
#
启动存储结点服务：mysql11
1
2
3
4
# ndbd --initial
2016-08-28 00:32:41 INFO -- Angel connected to '192.168.20.11:1186'
2016-08-28 00:32:41 INFO -- Angel allocated nodeid: 2
#
mysql12
1
2
3
4
# ndbd --initial
2016-08-28 00:33:09 INFO -- Angel connected to '192.168.20.11:1186'
2016-08-28 00:33:09 INFO -- Angel allocated nodeid: 3
#
启动 SQL 结点服务：mysql11
1
2
3
4
# mysqld_safe--defaults-file=/etc/my.cnf &
2166
160828 00:33:49mysqld_safe Logging to '/var/log/mysqld.log'.
160828 00:33:50 mysqld_safe Starting mysqlddaemon with databases from /var/lib/mysql
mysql12
1
2
3
4
# mysqld_safe--defaults-file=/etc/my.cnf &
1782
160828 00:34:56mysqld_safe Logging to '/var/log/mysqld.log'.
160828 00:34:56 mysqld_safe Starting mysqlddaemon with databases from /var/lib/mysql
测试：
查看 mysql 集群状态：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at:192.168.20.11:1186
Cluster Configuration
---------------------
2node(s)
id=2@192.168.20.11(mysql-5.6.21 ndb-7.3.7, Nodegroup: 0, *)
id=3@192.168.20.12(mysql-5.6.21 ndb-7.3.7, Nodegroup: 0)
1 node(s)
id=1@192.168.20.11(mysql-5.6.21 ndb-7.3.7)
2 node(s)
id=4@192.168.20.11(mysql-5.6.21 ndb-7.3.7)
id=5@192.168.20.12(mysql-5.6.21 ndb-7.3.7)
ndb_mgm>
测试集群，数据同步：mysql11先查看下默认生成的密码
1
2
# cat /root/.mysql_secret
# The random password set for the root userat Sat Aug 27 23:31:54 2016 (local time): oPjyozXIA05TSXb4
第一次登录
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# mysql -u root -poPjyozXIA05TSXb4
Warning: Using a password on the commandline interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 2
Server version:5.6.21-ndb-7.3.7-cluster-gpl
Copyright (c) 2000, 2014, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
mysql>
mysql> show databases; #第一使用数据库时，必须修改密码
ERROR 1820 (HY000): You must SET PASSWORDbefore executing this statement
mysql>
mysql> set password=password('111111');
Query OK, 0 rows affected (0.12 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ndb_2_fs |
| ndbinfo |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.10 sec)
mysql>
mysql> exit
Bye
来使用刚才修改的密码登录看
1
# mysql -uroot -p111111
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || ndb_2_fs || ndbinfo || performance_schema || test |+--------------------+6 rows in set (0.01 sec)
mysql12先查看默认密码
1
2
# cat /root/.mysql_secret
# The random password set for the root userat Sat Aug 27 23:53:13 2016 (local time): MjcJWscCjVpvnfDX
默认密码登录以后改密码
1
#mysql -uroot -pMjcJWscCjVpvnfDX
Warning: Using a password on the commandline interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21-ndb-7.3.7-cluster-gpl
Copyright (c) 2000, 2014, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
mysql> set password=password('111111');
Query OK, 0 rows affected (0.17 sec)
mysql> exit
Bye
使用修改的密码登录
1
# mysql -uroot -p111111
Warning: Using a password on the commandline interface can be insecure.
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 3
Server version:5.6.21-ndb-7.3.7-cluster-gpl MySQL Cluster Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ndb_3_fs |
| ndbinfo |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.07 sec)
mysql>
插入数据：mysql11
1
# mysql -uroot -p111111
mysql> create database abiao;
Query OK, 1 row affected (0.38 sec)
mysql12
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abiao |
| mysql |
| ndb_3_fs |
| ndbinfo |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
关闭 mysql 集群顺序：关闭管理节点服务-》关闭管理节点时，数据结点服务自动关闭–》 需要手动关闭 SQL 结点服务
关闭管理节点
1
2
3
4
5
6
7
8
9
10
11
# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> shutdown
Node 2: Cluster shutdown initiated
Node 3: Cluster shutdown initiated
Node 2: Node shutdown completed.
3 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server toshutdown.
Node 3: Node shutdown completed.
ndb_mgm> exit
看看进程，
1
2
3
4
# ps -axu | grepndbd
Warning: bad syntax, perhaps a bogus '-'?See /usr/share/doc/procps-3.2.8/FAQ
root 23820.00.1 103244856 pts/1 S+ 00:500:00 grep ndbd
#
已经关掉了节点
手动关闭 SQL 结点服务mysql11
1
2
3
4
5
6
7
8
9
10
11
12
#ps -axu | grep mysql
Warning: bad syntax, perhaps a bogus '-'?See /usr/share/doc/procps-3.2.8/FAQ
root 21660.00.0 106196192 pts/0 S 00:330:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf
mysql 22911.4 77.8 1364648 504800pxs/0Sl 00:330:15 /usr/sbin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr--datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql--log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid
root 23590.0 0.5 1258002748 pts/2 S+00:39 0:00 mysql -uroot -px xxxx
root 23870.00.1 103244860 pts/1 S+ 00:520:00 grep mysql
# kill -9 2166
# kill -9 2166
# ps -axu | grep mysql
Warning: bad syntax, perhaps a bogus '-'?See /usr/share/doc/procps-3.2.8/FAQ
root 23590.00.5 125800 2748 pts/2 S+ 00:390:00 mysql -uroot -px xxxx
root 23940.00.1 103244860 pts/1 S+ 00:540:00 grep mysql
mysql12
1
2
3
4
5
6
7
8
9
10
11
# ps -axu | grep mysql
Warning: bad syntax, perhaps a bogus '-'?See /usr/share/doc/procps-3.2.8/FAQ
root 17820.00.0 106196 20 pts/0 S 00:340:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf
mysql 19071.6 46.6 1364288 302192pxs/0Sl 00:340:20 /usr/sbin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr--datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql--log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid
root 19781.00.1 103248860 pts/1 S+ 00:540:00 grep mysql
# kill -9 1782
# kill -9 1907
# ps -axu | grep mysql
Warning: bad syntax, perhaps a bogus '-'?See /usr/share/doc/procps-3.2.8/FAQ
root 19810.00.1 103248860 pts/1 S+ 00:550:00 grep mysql
#
因为 是笔记，所以全篇都是代码
页:
[1]