Mysql高开用集群搭建

lukyjyt

环境
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

[iyunv@mysql11 ~]# tar -xf MySQL-Cluster-gpl-7.3.7-1.el6.x86_64.rpm-bundle.tar [iyunv@mysql11 ~]# scpMySQL-Cluster-gpl-7.3.7-1.el6.x86_64.rpm-bundle.tar root@192.168.20.12:/root [iyunv@mysql11 ~]# yum -y remove mysql* [iyunv@mysql11 ~]# mkdir /var/lib/mysql [iyunv@mysql11 ~]# mkdir /var/lib/mysql-cluster [iyunv@mysql11 ~]# mkdir /var/run/mysqld [iyunv@mysql11 ~]# rpm -ivhMySQL-Cluster-server-gpl-7.3.7-1.el6.x86_64.rpm [iyunv@mysql11 ~]# id mysql uid=498(mysql) gid=498(mysql)groups=498(mysql) [iyunv@mysql11 ~]# chown mysql:mysql -R/var/lib/mysql [iyunv@mysql11 ~]# chown mysql:mysql -R/var/lib/mysql-cluster [iyunv@mysql11 ~]# chown mysql:mysql -R/var/run/mysqld/

配置mysql12

1 2 3 4

[iyunv@mysql12 ~]# yum remove mysql -y [iyunv@mysql12 ~]# rpm -qa | grep mysql* mysql-libs-5.1.71-1.el6.x86_64 [iyunv@mysql12 ~]# rpm -e --nodepsmysql-libs-5.1.71-1.el6.x86_64

其实我觉得/var/lib/mysql  /var/lib/mysql-cluster/var/run/mysqld这三个路径会自动生成
来试试

1	[iyunv@mysql12 ~]# 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

[iyunv@mysql12 ~]# ls /var/lib/mysql ibdata1     ib_logfile1  ndbinfo             RPM_UPGRADE_HISTORY      test ib_logfile0 mysql       performance_schema RPM_UPGRADE_MARKER-LAST [iyunv@mysql12 ~]# ls/var/lib/mysql-cluster/ [iyunv@mysql12 ~]# mkdir/var/lib/mysql-cluster mkdir: cannot create directory`/var/lib/mysql-cluster': File exists [iyunv@mysql12 ~]# id mysql uid=498(mysql) gid=498(mysql)groups=498(mysql) [iyunv@mysql12 ~]# ls  /var/run/mysqld [iyunv@mysql12 ~]# mkdir /var/run/mysqld mkdir: cannot create directory`/var/run/mysqld': File exists [iyunv@mysql12 ~]# /var/run/mysqld -bash: /var/run/mysqld: is a directory [iyunv@mysql12 ~]# ll -d /var/run/mysqld drwxr-xr-x 2 root root 4096 Aug 27 23:29/var/run/mysqld [iyunv@mysql12 ~]# ll -d/var/lib/mysql-cluster drwxr-xr-x 2 root root 4096 Aug 27 23:29/var/lib/mysql-cluster [iyunv@mysql12 ~]# ll -d /var/lib/mysql drwxr-xr-x 6 mysql mysql 4096 Aug 27 23:53/var/lib/mysql [iyunv@mysql12 ~]#

改成mysql用户所有吧

1 2 3

[iyunv@mysql12 ~]# chown mysql:mysql -R/var/lib/mysql [iyunv@mysql12 ~]# chown mysql:mysql -R/var/lib/mysql-cluster [iyunv@mysql12 ~]# chown mysql:mysql -R/var/run/mysqld/

配置mysql11成为管理节点主要是两个命令：ndb_mgmd 和 ndb_mgm

1 2 3 4 5 6 7 8

[iyunv@mysql11 ~]# which ndb_mgm /usr/bin/ndb_mgm [iyunv@mysql11 ~]# rpm -qf /usr/bin/ndb_mgm MySQL-Cluster-server-gpl-7.3.7-1.el6.x86_64 [iyunv@mysql11 ~]# which ndb_mgmd /usr/sbin/ndb_mgmd [iyunv@mysql11 ~]# rpm -qf/usr/sbin/ndb_mgmd MySQL-Cluster-server-gpl-7.3.7-1.el6.x86_64

生成配置文件

1 2	[iyunv@mysql11 ~]# cd /var/lib/mysql-cluster [iyunv@mysql11 mysql-cluster]# vimconfig.ini

#配置文件的内容：
[ndbd default]
NoOfReplicas=2                 #数据写入数量。2 表示两份，这样当其中一个存储节点坏了，另一个还可以正常使用。
DataMemory=200M                 #配置数据存储可以使用的内存
IndexMemory=100M                #配置缓存索引可以使用的内存

[ndb_mgmd]
id=1
datadir=/var/lib/mysql-cluster                  # 指定存放管理结点的日志
HostName=192.168.20.11                         #管理结点的 IP 地址。本机 IP

###### data node options:                        #存储结点

[ndbd]
HostName=192.168.20.11
DataDir=/var/lib/mysql                               #mysql 数据存储路径
id=2

[ndbd]
HostName=192.168.20.12
DataDir=/var/lib/mysql
id=3

# SQL node options:                            #关于 SQL 结点
[mysqld]
HostName=192.168.20.11                         # 指定可以连接 ndbd 引擎的 sql 结点。
id=4

[mysqld]
HostName=192.168.20.12                         # 指定可以连接 ndbd 引擎的 sql 结点。
id=5

mysql11数据节点+SQL 节点配置文档：配置的内容要手动写呢！

1	[iyunv@mysql11 ~]# vim  /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql                                          #mysql数据存储路径[单独的SQL节点不需要这行]
ndbcluster                                                                #启动 ndb 引擎
ndb-connectstring=192.168.20.11                    # 管理节点 IP 地址

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysql_cluster]
ndb-connectstring=192.168.20.11                    # 管理节点 IP 地址
再装个客户端吧

1	[iyunv@mysql11 ~]# rpm -ivhMySQL-Cluster-client-gpl-7.3.7-1.el6.x86_64.rpm

mysql12数据节点+SQL 节点配置文档客户端装上

[iyunv@mysql12 ~]# rpm -ivhMySQL-Cluster-client-gpl-7.3.7-1.el6.x86_64.rpm
把mysql11上 的配置文件弄过来吧

1	[iyunv@mysql12 ~]# 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

[iyunv@mysql11 ~]# 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 [MgmtSrvr] INFO     -- The default config directory'/usr/mysql-cluster' does not exist. Trying to create it... 2016-08-28 00:28:34 [MgmtSrvr] INFO     -- Sucessfully created config directory 2016-08-28 00:28:34 [MgmtSrvr] WARNING  -- at line 7: [MGM] id is deprecated, useNodeId instead 2016-08-28 00:28:34 [MgmtSrvr] WARNING  -- at line 16: [DB] id is deprecated, useNodeId instead 2016-08-28 00:28:34 [MgmtSrvr] WARNING  -- at line 21: [DB] id is deprecated, use NodeIdinstead 2016-08-28 00:28:34 [MgmtSrvr] WARNING  -- at line 26: [API] id is deprecated, useNodeId instead 2016-08-28 00:28:34 [MgmtSrvr] WARNING  -- at line 30: [API] id is deprecated, useNodeId instead 2016-08-28 00:28:34 [MgmtSrvr] 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 [MgmtSrvr] WARNING  -- at line 7: [MGM] id is deprecated, useNodeId instead 2016-08-28 00:28:34 [MgmtSrvr] WARNING  -- at line 16: [DB] id is deprecated, useNodeId instead 2016-08-28 00:28:34 [MgmtSrvr] WARNING  -- at line 21: [DB] id is deprecated, use NodeIdinstead 2016-08-28 00:28:34 [MgmtSrvr] WARNING  -- at line 26: [API] id is deprecated, useNodeId instead 2016-08-28 00:28:34 [MgmtSrvr] WARNING  -- at line 30: [API] id is deprecated, useNodeId instead 2016-08-28 00:28:34 [MgmtSrvr] 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. [iyunv@mysql11 ~]#

#说明：仲裁员 （ID1） 和 DB（ID2）节点上同一主机192.168.20.11

在同一台主机作为数据库节点上运行仲裁人
造成完整的集群关机的情况下主机发生故障。虽然报了一些警告，但是，我们已经执行成功了。

查看端口号：

1 2 3 4 5

[iyunv@mysql11 ~]# 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       [iyunv@mysql11 ~]#

查看日志存储位置：

1 2 3 4 5 6 7

[iyunv@mysql11 ~]# 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 root  26 Aug 28 00:28 ndb_1_out.log -rw-r--r-- 1 root root   4 Aug 28 00:28 ndb_1.pid [iyunv@mysql11 ~]#

启动存储结点服务：mysql11

1 2 3 4

[iyunv@mysql11 ~]# ndbd --initial 2016-08-28 00:32:41 [ndbd] INFO     -- Angel connected to '192.168.20.11:1186' 2016-08-28 00:32:41 [ndbd] INFO     -- Angel allocated nodeid: 2 [iyunv@mysql11 ~]#

mysql12

1 2 3 4

[iyunv@mysql12 ~]# ndbd --initial 2016-08-28 00:33:09 [ndbd] INFO     -- Angel connected to '192.168.20.11:1186' 2016-08-28 00:33:09 [ndbd] INFO     -- Angel allocated nodeid: 3 [iyunv@mysql12 ~]#

启动 SQL 结点服务：mysql11

1 2 3 4

[iyunv@mysql11 ~]# mysqld_safe--defaults-file=/etc/my.cnf & [1] 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

[iyunv@mysql12 ~]# mysqld_safe--defaults-file=/etc/my.cnf & [1] 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

[iyunv@mysql11 ~]# ndb_mgm -- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at:192.168.20.11:1186 Cluster Configuration --------------------- [ndbd(NDB)]     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) [ndb_mgmd(MGM)]         1 node(s) id=1@192.168.20.11  (mysql-5.6.21 ndb-7.3.7) [mysqld(API)]   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	[iyunv@mysql11 ~]# 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

[iyunv@mysql11 ~]# 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	[iyunv@mysql11 ~]# 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	[iyunv@mysql12 ~]# cat /root/.mysql_secret # The random password set for the root userat Sat Aug 27 23:53:13 2016 (local time): MjcJWscCjVpvnfDX

默认密码登录以后改密码

1	[iyunv@mysql12 ~]#  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	[iyunv@mysql12 ~]# 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	[iyunv@mysql11 ~]# 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

[iyunv@mysql11 ~]# 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

[iyunv@mysql11 ~]# ps -axu | grep  ndbd Warning: bad syntax, perhaps a bogus '-'?See /usr/share/doc/procps-3.2.8/FAQ root      2382  0.0  0.1 103244  856 pts/1    S+   00:50  0:00 grep ndbd [iyunv@mysql11 ~]#

已经关掉了节点
手动关闭 SQL 结点服务mysql11

1 2 3 4 5 6 7 8 9 10 11 12

[iyunv@mysql11 ~]#  ps -axu | grep mysql Warning: bad syntax, perhaps a bogus '-'?See /usr/share/doc/procps-3.2.8/FAQ root      2166  0.0  0.0 106196  192 pts/0    S    00:33  0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf mysql     2291  1.4 77.8 1364648 504800pxs/0  Sl   00:33  0: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      2359  0.0 0.5 125800  2748 pts/2    S+  00:39   0:00 mysql -uroot -px xxxx root      2387  0.0  0.1 103244  860 pts/1    S+   00:52  0:00 grep mysql [iyunv@mysql11 ~]# kill -9 2166 [iyunv@mysql11 ~]# kill -9 2166 [iyunv@mysql11 ~]# ps -axu | grep mysql Warning: bad syntax, perhaps a bogus '-'?See /usr/share/doc/procps-3.2.8/FAQ root      2359  0.0  0.5 125800 2748 pts/2    S+   00:39  0:00 mysql -uroot -px xxxx root      2394  0.0  0.1 103244  860 pts/1    S+   00:54  0:00 grep mysql

mysql12

1 2 3 4 5 6 7 8 9 10 11

[iyunv@mysql12 ~]# ps -axu | grep mysql Warning: bad syntax, perhaps a bogus '-'?See /usr/share/doc/procps-3.2.8/FAQ root      1782  0.0  0.0 106196   20 pts/0    S    00:34  0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf mysql     1907  1.6 46.6 1364288 302192pxs/0  Sl   00:34  0: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      1978  1.0  0.1 103248  860 pts/1    S+   00:54  0:00 grep mysql [iyunv@mysql12 ~]# kill -9 1782 [iyunv@mysql12 ~]# kill -9 1907 [iyunv@mysql12 ~]# ps -axu | grep mysql Warning: bad syntax, perhaps a bogus '-'?See /usr/share/doc/procps-3.2.8/FAQ root      1981  0.0  0.1 103248  860 pts/1    S+   00:55  0:00 grep mysql [iyunv@mysql12 ~]#

因为 是笔记，所以全篇都是代码