mysql cluster双机_mysql cluster双机集群

最新推荐文章于 2022-11-24 22:48:06 发布

weixin_39518002

最新推荐文章于 2022-11-24 22:48:06 发布

阅读量64

点赞数

文章标签： mysql cluster双机

本文链接：https://blog.csdn.net/weixin_39518002/article/details/113310336

版权

[原文:http://hi.baidu.com/fuwenpan/blog/item/0c5d2d3ff9e67de655e7232a.html]

一、介绍

这篇文档旨在介绍如何安装配置基于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] (多出的这项是留给恢复时使用的.)

[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

[root@SerB ~]# mysql -uroot

这里要等上几分钟, 应为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手工进行差量数据恢复

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

最终成功版本:

# 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]

weixin_39518002

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql cluster双机_mysql cluster双机集群

[原文:http://hi.baidu.com/fuwenpan/blog/item/0c5d2d3ff9e67de655e7232a.html]一、介绍这篇文档旨在介绍如何安装配置基于2台服务器的MySQL集群。并且实现任意一台服务器出现问题或宕机时MySql集群依然能够继续运行。安装环境及软件包：2台PCmysql-6.0.0-alpha.tar.gzCentos5SerA:192.168.1...
复制链接

扫一扫