注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Bioinformatics home

 
 
 

日志

 
 

mysql cluster双机集群  

2012-02-13 23:44:07|  分类: mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

(zt)一、介绍
这篇文档旨在介绍如何安装配置基于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

我用的是:三、安装并配置节点
以下步骤需要在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 -ADatabase 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 distributionType '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 entriesNDBT_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 entriesNDBT_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]


  评论这张
 
阅读(1424)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018