mysql cluster双机集群

一、介绍
这篇文档旨在介绍如何安装配置基于2台服务器的MySQL集群。并且实现任意一台服务器出现问题或宕机时MySql集群依然能够继续运行。

安装环境及软件包：
2台PC
mysql-6.0.0-alpha.tar.gz
Centos5
SerA:192.168.1.50
SerB:192.168.1.8


二、在SerA和SerB上安装MySQL
以下步骤需要在SerA和SerB上各做一次
# mv mysql-6.0.0-alpha.tar.gz /tmp/package
# cd /tmp/package
# groupadd mysql
# useradd -g mysql mysql
# tar -zxvf mysql-6.0.0-alpha.tar.gz
# rm -f mysql-6.0.0-alpha.tar.gz
# mv mysql-6.0.0-alpha mysql
# cd mysql
# ./configure --prefix=/usr/local --with-extra-charsets=complex --with-plugin-ndbcluster --with-plugin-partition --with-plugin-innobase --with-unix-socket-path=/usr/local/var/mysql.sock
# make && make install
#ln -s /usr/local/libexec/ndbd /usr/bin
#ln -s /usr/local/libexec/ndb_mgmd /usr/bin
#ln -s /usr/local/libexec/ndb_cpcd /usr/bin
#ln -s /usr/local/libexec/mysqld /usr/bin
#ln -s /usr/local/libexec/mysqlmanager /usr/bin
#mysql_install_db --user=mysql

我用的是:mysql-5.0.85-linux-i686.tar.gz



三、安装并配置节点
以下步骤需要在SerA和SerB上各做一次
1.配置管理节点配置文件：
# mkdir /var/lib/mysql-cluster
# cd /var/lib/mysql-cluster
# vi config.ini
在config.ini中添加如下内容：
[ndbd default]
NoOfReplicas= 2
MaxNoOfConcurrentOperations= 10000
# Amount of memory required=(SizeofDatabase * NumberOfReplicas * 1.1 ) / NumberOfDataNodes
DataMemory= 128M
IndexMemory= 24M
TimeBetweenWatchDogCheck= 30000
DataDir=/var/lib/mysql-cluster
MaxNoOfOrderedIndexes= 512
StartPartialTimeout=100
StartPartitionedTimeout=100
ArbitrationTimeout=5000
TransactionDeadlockDetectionTimeout=5000
HeartbeatIntervalDbDb=5000
StopOnError=0

[ndb_mgmd default]
DataDir=/var/lib/mysql-cluster
[ndb_mgmd]
Id=1
HostName= 192.168.1.50
[ndb_mgmd]
Id=2
HostName= 192.168.1.8
[ndbd]
Id= 3
HostName= 192.168.1.50
[ndbd]
Id= 4
HostName= 192.168.1.8
[mysqld]
ArbitrationRank=2 (非常重要,全靠有它,才可以形成仲裁竞争,从而当另一个机子当了时,此机还可以有知道partion完整的节点)
[mysqld]
ArbitrationRank=2
[mysqld] (多出的这项是留给恢复时使用的.)
[mysqld] (多出的这项是留给恢复时使用的.)
[tcp default]
PortNumber= 63132

[separator]

2.配置通用my.cnf文件，mysqld及ndbd,ndb_mgmd均使用此文件.
# vi /etc/my.cnf
在my.cnf中添加如下内容：
[mysqld]
datadir=/usr/local/var
socket=/usr/local/var/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
default-storage-engine=ndbcluster
ndbcluster
ndb-connectstring=192.168.1.50,192.168.1.8

[ndbd]
connect-string=192.168.1.50,192.168.1.8

[ndb_mgm]
connect-string=192.168.1.50,192.168.1.8

[ndb_mgmd]
config-file=/var/lib/mysql-cluster/config.ini

[mysql_cluster]
ndb-connectstring=192.168.1.50,192.168.1.8

[mysql.server]
user=mysql
basedir=/usr/local/

[mysqld_safe]
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
[mysql]
#socket=/usr/local/var/mysql.sock
[mysqladmin]
#socket=/usr/local/var/mysql.sock
[ndb_restore default]


保存退出后.



四.启动管理节点SerA为：
[root@SerA ~]# ndb_mgmd --ndb_nodeid=1
Cluster configuration warning:
arbitrator with id 1 and db node with id 3 on same host 192.168.1.50
arbitrator with id 2 and db node with id 4 on same host 192.168.1.8
arbitrator with id 5 has no hostname specified
arbitrator with id 6 has no hostname specified
Running arbitrator on the same host as a database node may
cause complete cluster shutdown in case of host failure.
注:在启动时有一个警告提示
说节点1和3，2和4的arbitrator一样，可能引起整个集群失败。（可以不用放在心上）

启动管理节点SerB为：
[root@SerB ~]# ndb_mgmd --ndb_nodeid=2



五.初始化集群
在SerA中
[root@SerA ~]# ndbd --ndb_nodeid=3 --initial
在SerB中
[root@SerB ~]# ndbd --ndb_nodeid=4 --initial
注：只有在第一次启动ndbd时或者对config.ini进行改动后才需要使用--initial参数！(在下面为了进行恢复实验时还再次使用到)


检查工作状态
在任意一台机子上启动管理终端：
[root@SerA ~]# ndb_mgm -e show
Connected to Management Server at: 192.168.1.50:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=3    @192.168.1.50 (Version: 6.0.0, Nodegroup: 0, Master)
id=4    @192.168.1.8 (Version: 6.0.0, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1    @192.168.1.50 (Version: 6.0.0)
id=2    @192.168.1.8 (Version: 6.0.0)

[mysqld(API)]   3 node(s)
id=5 (not connected, accepting connect from any host)
id=6 (not connected, accepting connect from any host)
id=7 (not connected, accepting connect from any host)

如果上面没有问题，现在开始加入mysqld(API)

六.加入mysqld(API)
注意，这篇文档对于MySQL并没有设置root密码，推荐你自己设置SerA和SerB的MySQL root密码。
在SerA 中：
[root@SerA ~]# mysqld_safe --ndb_nodeid=5 --user=mysql &
在SerB 中：
[root@SerB ~]# mysqld_safe --ndb_nodeid=6 --user=mysql &

再次检查工作状态,看mysql节点是否加入成功
[root@SerA ~]# ndb_mgm -e show
Connected to Management Server at: 192.168.1.50:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=3    @192.168.1.50 (Version: 6.0.0, Nodegroup: 0, Master)
id=4    @192.168.1.8 (Version: 6.0.0, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1    @192.168.1.50 (Version: 6.0.0)
id=2    @192.168.1.8 (Version: 6.0.0)

[mysqld(API)]   3 node(s)
id=5    @192.168.1.50 (Version: 6.0.0)
id=6    @192.168.1.8 (Version: 6.0.0)
id=7 (not connected, accepting connect from any host)


七.测试：
在SerA 中
[root@SerA ~]# mysql -uroot
>create databases backup;
>use backup;
>create table dog (name varchar(10));
>create table pig (name varchar(10));

退出终端, 使用下面的命令往上面两个表内批量插入数据.
[root@SerA ~]# mysql -uroot<sql.txt
[root@SerB ~]# mysql -uroot<sqltest.txt

这里要等上几分钟, 应为sql.txt 里有20W行记录, 而且sqltest.txt也用10W行.

数据插入完毕再回到终端中检查是否有新增的数据库和表以及数据.
[root@SerB ~]# mysql -uroot
>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backup             |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use backup
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_backup |
+------------------+
| dog              |
| pig              |
+------------------+
2 rows in set (0.00 sec)


mysql> select * from pig;
...............
...............

| 144163 |
| 173821 |
| 188584 |
| 45860 |
+--------+
200000 rows in set (1.66 sec)


mysql> select * from pig;
...............
...............

| 27580 |
| 83268 |
| 47744 |
| 97018 |
+--------+
100000 rows in set (0.83 sec)


可以看到mysql能正常工作.


ndb下数据备份和恢复：
备份很简单：
在任意的一台机子上,只需通过ndb_mgm，运行start backup
[root@SerB zman]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> start backup
Connected to Management Server at: 192.168.1.50:1186
Waiting for completed, this may take several minutes
Node 3: Backup 1 started from node 1
Node 3: Backup 1 started from node 1 completed
StartGCP: 515 StopGCP: 518
#Records: 302059 #LogRecords: 0
Data: 8427304 bytes Log: 0 bytes

这个备份很快，备份的结果是在每个数据节点上都生成一个备份. 用ll命令分别在两台机子上查看是否生成备份文件.

[root@SerA mysql-cluster]# ll /var/lib/mysql-cluster/BACKUP/BACKUP-1/
total 4116
-rw-r--r-- 1 root root 4194172 Aug 22 02:16 BACKUP-1-0.3.Data
-rw-r--r-- 1 root root    8580 Aug 22 02:16 BACKUP-1.3.ctl
-rw-r--r-- 1 root root      44 Aug 22 02:16 BACKUP-1.3.log

[root@SerB conf]# ll /var/lib/mysql-cluster/BACKUP/BACKUP-1/
总计 4156
-rw-r--r-- 1 root root 4233956 08-22 14:15 BACKUP-1-0.4.Data
-rw-r--r-- 1 root root    8580 08-22 14:15 BACKUP-1.4.ctl
-rw-r--r-- 1 root root      44 08-22 14:15 BACKUP-1.4.log


恢复
恢复要通过以下几个步骤完成:
1. 测试删除数据表，至少1个节点重新建个空的；
2. 停止sql节点的运行，或者在配置文件中增加1个空的sql节点标志(config.ini文件里多出的一个[mysqld]项就是留个这里用 的)，否则会出现 No free node id found for mysqld(API) 错误；
3. 首先在任意一个节点上恢复表结构，然后在每个数据节点上恢复数据
ndb_restore -n 4 -b 1 -m -r /var/lib/mysql-cluster/BACKUP/BACKUP-1/
没有-m 这个恢复过程，会出现Unable to find table错误
-n 和 -b 的数值，对应备份文件 BACKUP-n.b.ctl，这里是 BACKUP-1.4.ctl
ndb_restore -n 3 -b 1 -r /var/lib/mysql-cluster/BACKUP/BACKUP-1/



1) 先关闭集群.
[root@SerA ~]# mysqladmin shutdown
[root@SerB ~]# mysqladmin shutdown

[root@SerA ~]# ndb_mgm -e shutdown
Connected to Management Server at: 192.168.1.50:1186
2 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.

2)重复上面的四五六 (主要是想通过 ndbd --ndb_nodeid=3 --initial 和 ndbd --ndb_nodeid=4 --initial 將数据库初始化)

[root@SerA ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.0-alpha Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use backup
Database changed
mysql> show tables; (可以看到数据库是空的)
Empty set (0.00 sec)


3)进行恢复
在SerB上执行:
[root@SerB ~]# ndb_restore -n 4 -b 1 -m -r /var/lib/mysql-cluster/BACKUP/BACKUP-1
Nodeid = 4
Backup Id = 1
backup path = /var/lib/mysql-cluster/BACKUP/BACKUP-1
Ndb version in backup files: Version 6.0.0
Connected to ndb!!
Successfully restored table backup/def/pig
Successfully restored table event REPL$backup/pig
Successfully restored table backup/def/dog
Successfully restored table event REPL$backup/dog
_____________________________________________________
Processing data in table: backup/def/pig(6) fragment 1
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 1
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_2_3(3) fragment 1
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(0) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(2) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(4) fragment 1
_____________________________________________________
Processing data in table: backup/def/dog(5) fragment 1
Restored 150727 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK




在SerA上执行:
[root@SerA ~]# ndb_restore -n 3 -b 1 -r /var/lib/mysql-cluster/BACKUP/BACKUP-1
Nodeid = 3
Backup Id = 1
backup path = /var/lib/mysql-cluster/BACKUP/BACKUP-1
Ndb version in backup files: Version 6.0.0
Connected to ndb!!
_____________________________________________________
Processing data in table: backup/def/pig(6) fragment 0
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(1) fragment 0
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_2_3(3) fragment 0
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(0) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(2) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(4) fragment 0
_____________________________________________________
Processing data in table: backup/def/dog(5) fragment 0
Restored 149273 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK




回到终端里查看恢复情况
[root@SerA ~]# mysql -uroot
mysql> use backup
mysql> show tables;
+------------------+
| Tables_in_backup |
+------------------+
| dog              |
| pig              |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from pig;
...............
...............

| 144163 |
| 173821 |
| 188584 |
| 45860 |
+--------+
200000 rows in set (1.66 sec)


mysql> select * from pig;
...............
...............

| 27580 |
| 83268 |
| 47744 |
| 97018 |
+--------+
100000 rows in set (0.83 sec)

OK, 数据库恢复成功.
cluster目前，只能对所有数据进行备份和恢复，不能选择数据库，也不能进行差量备份，不知如果对应上G的数据会怎样，目前想到的是打开log-bin手工进行差量数据恢复

转载: http://hi.baidu.com/doublelook/blog/item/e7ed781e3714246af624e4b0.html

在配置中还是遇到不少问题:

最终成功版本:

下载:http://mysql.cs.pu.edu.tw/Downloads/MySQL-5.0/mysql-5.0.85-linux-i686.tar.gz

# mv mysql-5.0.85-linux-i686.tar.gz /usr/local/

# cd /usr/local/

# groupadd mysql

# useradd -g mysql mysql

# tar -zxvf mysql-5.0.85-linux-i686.tar.gz

# rm -f mysql-5.0.85-linux-i686.tar.gz

# mv mysql-5.0.85-linux-i686 mysql

# cd mysql

# scripts/mysql_install_db --user=mysql

# chown -R root   .

# chown -R mysql data

# chgrp -R mysql .

# cp support-files/mysql.server /etc/rc.d/init.d/mysqld

# chmod +x /etc/rc.d/init.d/mysqld

# chkconfig --add mysqld

my.cnf:

[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
default-storage-engine=ndbcluster
ndbcluster
ndb-connectstring=192.168.1.229,192.168.1.191
[ndbd]
connect-string=192.168.1.229,192.168.1.191
[ndb_mgm]
connect-string=192.168.1.229,192.168.1.191
[ndb_mgmd]
config-file=/var/lib/mysql-cluster/config.ini
[mysql_cluster]
ndb-connectstring= 192.168.1.229,192.168.1.191
[mysql_server]
user=mysql
basedir=/usr/local
[mysqld_safe]
log-error=/var/log/mysqld.log
[mysql]
[mysqladmin]

config.ini:

[ndbd default]
NoOfReplicas= 2
MaxNoOfConcurrentOperations= 10000
DataMemory= 128M
IndexMemory= 24M
TimeBetweenWatchDogCheck= 30000
DataDir= /var/lib/mysql-cluster
MaxNoOfOrderedIndexes= 512
StartPartialTimeout=100
StartPartitionedTimeout=100
ArbitrationTimeout=5000
TransactionDeadlockDetectionTimeout=5000
HeartbeatIntervalDbDb=5000
StopOnError=0

[ndb_mgmd default]
DataDir= /var/lib/mysql-cluster
[ndb_mgmd]
Id=1
HostName= 192.168.1.229
[ndb_mgmd]
Id=2
HostName= 192.168.1.191
[ndbd]
Id=3
HostName= 192.168.1.229
[ndbd]
Id=4
HostName= 192.168.1.191
[mysqld]
ArbitrationRank=2
[mysqld]
ArbitrationRank=2
[mysqld]
[mysqld]