最近服务器MYSQL压力有点大,为了应付后续公司发展,准备测试一下mysql cluster听说最近发行的版本在原基础上性能提升不少,呵呵,先下载一个试下
下载地址:
http://downloads.mysql.com/archives/mysql-cluster-gpl-7.1/mysql-cluster-gpl-7.1.18-linux-i686-glibc23.tar.gz
参考文档:
http://dev.mysql.com/doc/index-cluster.html
环境如下:
192.168.200.22 (管理节点)
192.168.200.11 (数据节点以及mysql点,没有多的机器,此机器担任两角色)
192.168.200.10 (mysql节点)
管理节点安装:
tar -zxvf mysql-cluster-gpl-7.1.18-linux-i686-glibc23.tar.gz
cp mysql-cluster-gpl-7.1.18-linux-i686-glibc23 /soft/mysql-mgm
#cd /soft/mysql-mgm
#mkdir -p mysql-cluster/mysqld_data
#vi my.cnf 确保/etc/ 下无my.cnf文件,因为他会优先读取
[mysqld]
ndbcluster
datadir=/home/user1/my_cluster/mysqld_data
basedir=/home/user1/mysqlc
port=5000
#vi config.ini
[ndbd default]
NoOfReplicas= 1
[ndb_mgmd]
NodeId=1
HostName=192.168.200.22
datadir=/soft/mysql-mgm/mysql-cluster/mysqld_data
[ndbd]
NodeId=2
HostName=192.168.200.11
[mysqld]
NodeId=3
HostName=192.168.200.10
[mysqld]
NodeId=50
HostName=192.168.200.11
然后就可以启动管理节点了:
#bin/ndb_mgmd -f conf/config.ini --initial
如果无问题下次启动直接执行
#bin/ndb_mgmd -f conf/config.ini
数据节点NDB安装
#scp -r /soft/mysql-mgm 192.168.200.10:/soft/ 直接复制过来即可
#vi my.cnf
[mysqld]
port = 3331
socket = /tmp/mysqlndb-a.sock
ndbcluster
[mysql_cluster]
ndb-connectstring=192.168.200.22
初始化一下数据库
#scripts/mysql_install_db --user=mysql
启动
#bin/ndbd --initial
如果无错误下次启动直接执行
#bin/ndbd
SQL节点安装
#scp -r /soft/mysql-mgm 192.168.200.10:/soft/ 直接复制过来即可
#vi my.cnf
[mysqld]
port = 3331
socket = /tmp/mysqlndb.sock
ndbcluster
[mysql_cluster]
ndb-connectstring=192.168.200.22
初始化一下数据库
#scripts/mysql_install_db --user=mysql
#./mysql.server start
这样就OK了
赶紧测试一下
在管理节点上面看一下
#bin/ndb_mgm -e show
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 1 node(s)
id=2 @192.168.200.11 (mysql-5.1.56 ndb-7.1.18, Nodegroup: 0, Master)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.200.22 (mysql-5.1.56 ndb-7.1.18)
[mysqld(API)] 2 node(s)
id=3 @192.168.200.10 (mysql-5.1.56 ndb-7.1.18)
id=50 @192.168.200.11 (mysql-5.1.56 ndb-7.1.18)
正常的
再去192.168.200.10 创建一个数据看一下192.168.200.11是否有了,如果有了就证明OK了
华丽的分割线
==============================================================================================================
这两天看了一下 mysql-cluster 从 5.0.X 的时候做过测试,后来就在也没有看过,后来google时,说有些改善,所以就研究了一下。今天只把配置过程写下来,改天把LVS 和 测试结果发来,不过也拿不出什么测试结果,因为现在手头上没有实体服务器,虚拟机器做并发测试没有什么意义呵呵。
hostname IP地址 应用服务
DB1 192.168.6.162 MGM
DB2 192.168.6.160 NDBD,MYSQLD
DB3 192.168.6.188 NDBD2, MYSQLD
DB4 192.168.6.191 MYSQLD
一.DB1 管理节点 MGM
./configure --prefix=/usr/local/mysql/ --with-charset=utf8 --with-collation=utf8_general_ci --with-client-ldflags=-all-static -with-mysqld-ldflags=-all-static --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-readline --with-ssl --with-embedded-server --enable-local-infile --with-unix-socket-path=/tmp/mysql.sock --sysconfdir=/usr/local/mysql/etc --without-debug --with-mysqld-user=mysql --with-plugin=innobase --with-plugin-ndbcluster --with-plugin-partition
make
make install
cd /usr/local/mysql
mkdir mysql-cluster
cd mysql-cluster
vim config.ini
[ndbd default]
NoOfReplicas= 2
[MYSQLD DEFAULT]
[ndb_mgmd default]
DataDir= /usr/local/mysql/mysql-cluster
[ndb_mgmd]
HostName= 192.168.6.162
[ndbd]
HostName= 192.168.6.160
DataDir= /usr/local/mysql/mysql-cluster
[ndbd]
HostName= 192.168.6.188
DataDir= /usr/local/mysql/mysql-cluster
[mysqld]
HostName= 192.168.6.160
[mysqld]
HostName= 192.168.6.188
[mysqld]
HostName= 192.168.6.191
/usr/local/mysql/libexec/ndb_mgmd -f /usr/local/mysql/mysql-cluster/config.ini
/usr/local/mysql/bin/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 192.168.6.160)
id=3 (not connected, accepting connect from 192.168.6.188)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.6.162 (Version: 5.1.45)
[mysqld(API)] 3 node(s)
id=4 (not connected, accepting connect from 192.168.6.160)
id=5 (not connected, accepting connect from 192.168.6.188)
id=6 (not connected, accepting connect from 192.168.6.191)
可以看到ndb_mgmd 是 连上的,nbd 和 mysqld 还没有连上~
二.在两个ndbd 节点 DB1,DB2 安装mysql
./configure --prefix=/usr/local/mysql/ --with-charset=utf8 --with-collation=utf8_general_ci --with-client-ldflags=-all-static -with-mysqld-ldflags=-all-static --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-readline --with-ssl --with-embedded-server --enable-local-infile --with-unix-socket-path=/tmp/mysql.sock --sysconfdir=/usr/local/mysql/etc --without-debug --with-mysqld-user=mysql --with-plugin=innobase --with-plugin-ndbcluster --with-plugin-partition
groupadd mysql
useradd -g mysql mysql
cd /usr/local/mysql
chown root.mysql . -R
/usr/local/mysql/bin/mysql_install_db --user=mysql
vim /usr/local/mysql/etc/my.cnf
[mysqld]
basedir = /usr/local/mysql/
datadir = /usr/local/mysql/var
user = mysql
port = 3306
socket = /tmp/mysql.sock
ndbcluster
ndb-connectstring=192.168.6.162
[mysql_cluster]
ndb-connectstring=192.168.6.162
/usr/local/mysql/libexec/ndbd --initial ### 第一次启动需要 --initial 以后在启动不需要加
随后启动两台ndbd 节点上的 mysql 服务
/usr/local/mysql/bin/mysqld_safe --user=mysql &
然后在 MGM 服务器上查看
/usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.6.160 (Version: 5.1.45, Nodegroup: 0)
id=3 @192.168.6.188 (Version: 5.1.45, Nodegroup: 0, Master)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.6.162 (Version: 5.1.45)
[mysqld(API)] 3 node(s)
id=4 @192.168.6.160 (Version: 5.1.45)
id=5 @192.168.6.188 (Version: 5.1.45)
id=6 (not connected, accepting connect from 192.168.6.191)
从上面可以看出 两个 ndbd 节点 和 ndbd 上的两个mysql 服务已经连接上了。
三.DB4安装mysql节点
./configure --prefix=/usr/local/mysql/ --with-charset=utf8 --with-collation=utf8_general_ci --with-client-ldflags=-all-static -with-mysqld-ldflags=-all-static --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-readline --with-ssl --with-embedded-server --enable-local-infile --with-unix-socket-path=/tmp/mysql.sock --sysconfdir=/usr/local/mysql/etc --without-debug --with-mysqld-user=mysql --with-plugin=innobase --with-plugin-ndbcluster --with-plugin-partition
groupadd mysql
useradd -g mysql mysql
cd /usr/local/mysql
chown root.mysql . -R
/usr/local/mysql/bin/mysql_install_db --user=mysql
vim /usr/local/mysql/etc/my.cnf
[mysqld]
basedir = /usr/local/mysql/
datadir = /usr/local/mysql/var
user = mysql
port = 3306
socket = /tmp/mysql.sock
ndbcluster
ndb-connectstring=192.168.6.162
[mysql_cluster]
ndb-connectstring=192.168.6.162
启动 mysql 服务
/usr/local/mysql/bin/mysqld_safe --user=mysql &
回到 MGM 上查看是否正常
DB1 $> /usr/local/mysql/bin/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 @192.168.6.160 (Version: 5.1.45, Nodegroup: 0)
id=3 @192.168.6.188 (Version: 5.1.45, Nodegroup: 0, Master)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.6.162 (Version: 5.1.45)
[mysqld(API)] 3 node(s)
id=4 @192.168.6.160 (Version: 5.1.45)
id=5 @192.168.6.188 (Version: 5.1.45)
id=6 @192.168.6.191 (Version: 5.1.45)
好了,一切正常了,现在回到 mysql 节点上 创建和删除数据看是否同步。
那么就登录 DB2 的 mysql
DB2 $> /usr/local/mysql/bin/mysql -uroot -p
Mysql >show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
Mysql > create database eric;
Query OK, 1 row affected (0.39 sec)
Mysql >show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| eric |
| mysql |
+--------------------+
3 rows in set (0.01 sec)
好了,在看 DB3 DB4 上是否同步
Db3 $> /usr/local/mysql/bin/mysql -uroot -p
Mysql $> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| eric |
| mysql |
+--------------------+
DB4 $> /usr/local/mysql/bin/mysql -uroot -p
Mysql $> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| eric |
| mysql |
+--------------------+
都看到了 eric 数据库·结束
下载地址:
http://downloads.mysql.com/archives/mysql-cluster-gpl-7.1/mysql-cluster-gpl-7.1.18-linux-i686-glibc23.tar.gz
参考文档:
http://dev.mysql.com/doc/index-cluster.html
环境如下:
192.168.200.22 (管理节点)
192.168.200.11 (数据节点以及mysql点,没有多的机器,此机器担任两角色)
192.168.200.10 (mysql节点)
管理节点安装:
tar -zxvf mysql-cluster-gpl-7.1.18-linux-i686-glibc23.tar.gz
cp mysql-cluster-gpl-7.1.18-linux-i686-glibc23 /soft/mysql-mgm
#cd /soft/mysql-mgm
#mkdir -p mysql-cluster/mysqld_data
#vi my.cnf 确保/etc/ 下无my.cnf文件,因为他会优先读取
[mysqld]
ndbcluster
datadir=/home/user1/my_cluster/mysqld_data
basedir=/home/user1/mysqlc
port=5000
#vi config.ini
[ndbd default]
NoOfReplicas= 1
[ndb_mgmd]
NodeId=1
HostName=192.168.200.22
datadir=/soft/mysql-mgm/mysql-cluster/mysqld_data
[ndbd]
NodeId=2
HostName=192.168.200.11
[mysqld]
NodeId=3
HostName=192.168.200.10
[mysqld]
NodeId=50
HostName=192.168.200.11
然后就可以启动管理节点了:
#bin/ndb_mgmd -f conf/config.ini --initial
如果无问题下次启动直接执行
#bin/ndb_mgmd -f conf/config.ini
数据节点NDB安装
#scp -r /soft/mysql-mgm 192.168.200.10:/soft/ 直接复制过来即可
#vi my.cnf
[mysqld]
port = 3331
socket = /tmp/mysqlndb-a.sock
ndbcluster
[mysql_cluster]
ndb-connectstring=192.168.200.22
初始化一下数据库
#scripts/mysql_install_db --user=mysql
启动
#bin/ndbd --initial
如果无错误下次启动直接执行
#bin/ndbd
SQL节点安装
#scp -r /soft/mysql-mgm 192.168.200.10:/soft/ 直接复制过来即可
#vi my.cnf
[mysqld]
port = 3331
socket = /tmp/mysqlndb.sock
ndbcluster
[mysql_cluster]
ndb-connectstring=192.168.200.22
初始化一下数据库
#scripts/mysql_install_db --user=mysql
#./mysql.server start
这样就OK了
赶紧测试一下
在管理节点上面看一下
#bin/ndb_mgm -e show
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 1 node(s)
id=2 @192.168.200.11 (mysql-5.1.56 ndb-7.1.18, Nodegroup: 0, Master)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.200.22 (mysql-5.1.56 ndb-7.1.18)
[mysqld(API)] 2 node(s)
id=3 @192.168.200.10 (mysql-5.1.56 ndb-7.1.18)
id=50 @192.168.200.11 (mysql-5.1.56 ndb-7.1.18)
正常的
再去192.168.200.10 创建一个数据看一下192.168.200.11是否有了,如果有了就证明OK了
华丽的分割线
==============================================================================================================
这两天看了一下 mysql-cluster 从 5.0.X 的时候做过测试,后来就在也没有看过,后来google时,说有些改善,所以就研究了一下。今天只把配置过程写下来,改天把LVS 和 测试结果发来,不过也拿不出什么测试结果,因为现在手头上没有实体服务器,虚拟机器做并发测试没有什么意义呵呵。
hostname IP地址 应用服务
DB1 192.168.6.162 MGM
DB2 192.168.6.160 NDBD,MYSQLD
DB3 192.168.6.188 NDBD2, MYSQLD
DB4 192.168.6.191 MYSQLD
一.DB1 管理节点 MGM
./configure --prefix=/usr/local/mysql/ --with-charset=utf8 --with-collation=utf8_general_ci --with-client-ldflags=-all-static -with-mysqld-ldflags=-all-static --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-readline --with-ssl --with-embedded-server --enable-local-infile --with-unix-socket-path=/tmp/mysql.sock --sysconfdir=/usr/local/mysql/etc --without-debug --with-mysqld-user=mysql --with-plugin=innobase --with-plugin-ndbcluster --with-plugin-partition
make
make install
cd /usr/local/mysql
mkdir mysql-cluster
cd mysql-cluster
vim config.ini
[ndbd default]
NoOfReplicas= 2
[MYSQLD DEFAULT]
[ndb_mgmd default]
DataDir= /usr/local/mysql/mysql-cluster
[ndb_mgmd]
HostName= 192.168.6.162
[ndbd]
HostName= 192.168.6.160
DataDir= /usr/local/mysql/mysql-cluster
[ndbd]
HostName= 192.168.6.188
DataDir= /usr/local/mysql/mysql-cluster
[mysqld]
HostName= 192.168.6.160
[mysqld]
HostName= 192.168.6.188
[mysqld]
HostName= 192.168.6.191
/usr/local/mysql/libexec/ndb_mgmd -f /usr/local/mysql/mysql-cluster/config.ini
/usr/local/mysql/bin/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 192.168.6.160)
id=3 (not connected, accepting connect from 192.168.6.188)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.6.162 (Version: 5.1.45)
[mysqld(API)] 3 node(s)
id=4 (not connected, accepting connect from 192.168.6.160)
id=5 (not connected, accepting connect from 192.168.6.188)
id=6 (not connected, accepting connect from 192.168.6.191)
可以看到ndb_mgmd 是 连上的,nbd 和 mysqld 还没有连上~
二.在两个ndbd 节点 DB1,DB2 安装mysql
./configure --prefix=/usr/local/mysql/ --with-charset=utf8 --with-collation=utf8_general_ci --with-client-ldflags=-all-static -with-mysqld-ldflags=-all-static --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-readline --with-ssl --with-embedded-server --enable-local-infile --with-unix-socket-path=/tmp/mysql.sock --sysconfdir=/usr/local/mysql/etc --without-debug --with-mysqld-user=mysql --with-plugin=innobase --with-plugin-ndbcluster --with-plugin-partition
groupadd mysql
useradd -g mysql mysql
cd /usr/local/mysql
chown root.mysql . -R
/usr/local/mysql/bin/mysql_install_db --user=mysql
vim /usr/local/mysql/etc/my.cnf
[mysqld]
basedir = /usr/local/mysql/
datadir = /usr/local/mysql/var
user = mysql
port = 3306
socket = /tmp/mysql.sock
ndbcluster
ndb-connectstring=192.168.6.162
[mysql_cluster]
ndb-connectstring=192.168.6.162
/usr/local/mysql/libexec/ndbd --initial ### 第一次启动需要 --initial 以后在启动不需要加
随后启动两台ndbd 节点上的 mysql 服务
/usr/local/mysql/bin/mysqld_safe --user=mysql &
然后在 MGM 服务器上查看
/usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.6.160 (Version: 5.1.45, Nodegroup: 0)
id=3 @192.168.6.188 (Version: 5.1.45, Nodegroup: 0, Master)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.6.162 (Version: 5.1.45)
[mysqld(API)] 3 node(s)
id=4 @192.168.6.160 (Version: 5.1.45)
id=5 @192.168.6.188 (Version: 5.1.45)
id=6 (not connected, accepting connect from 192.168.6.191)
从上面可以看出 两个 ndbd 节点 和 ndbd 上的两个mysql 服务已经连接上了。
三.DB4安装mysql节点
./configure --prefix=/usr/local/mysql/ --with-charset=utf8 --with-collation=utf8_general_ci --with-client-ldflags=-all-static -with-mysqld-ldflags=-all-static --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-readline --with-ssl --with-embedded-server --enable-local-infile --with-unix-socket-path=/tmp/mysql.sock --sysconfdir=/usr/local/mysql/etc --without-debug --with-mysqld-user=mysql --with-plugin=innobase --with-plugin-ndbcluster --with-plugin-partition
groupadd mysql
useradd -g mysql mysql
cd /usr/local/mysql
chown root.mysql . -R
/usr/local/mysql/bin/mysql_install_db --user=mysql
vim /usr/local/mysql/etc/my.cnf
[mysqld]
basedir = /usr/local/mysql/
datadir = /usr/local/mysql/var
user = mysql
port = 3306
socket = /tmp/mysql.sock
ndbcluster
ndb-connectstring=192.168.6.162
[mysql_cluster]
ndb-connectstring=192.168.6.162
启动 mysql 服务
/usr/local/mysql/bin/mysqld_safe --user=mysql &
回到 MGM 上查看是否正常
DB1 $> /usr/local/mysql/bin/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 @192.168.6.160 (Version: 5.1.45, Nodegroup: 0)
id=3 @192.168.6.188 (Version: 5.1.45, Nodegroup: 0, Master)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.6.162 (Version: 5.1.45)
[mysqld(API)] 3 node(s)
id=4 @192.168.6.160 (Version: 5.1.45)
id=5 @192.168.6.188 (Version: 5.1.45)
id=6 @192.168.6.191 (Version: 5.1.45)
好了,一切正常了,现在回到 mysql 节点上 创建和删除数据看是否同步。
那么就登录 DB2 的 mysql
DB2 $> /usr/local/mysql/bin/mysql -uroot -p
Mysql >show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
Mysql > create database eric;
Query OK, 1 row affected (0.39 sec)
Mysql >show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| eric |
| mysql |
+--------------------+
3 rows in set (0.01 sec)
好了,在看 DB3 DB4 上是否同步
Db3 $> /usr/local/mysql/bin/mysql -uroot -p
Mysql $> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| eric |
| mysql |
+--------------------+
DB4 $> /usr/local/mysql/bin/mysql -uroot -p
Mysql $> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| eric |
| mysql |
+--------------------+
都看到了 eric 数据库·结束