mysql ndb还原单表_mysql ndb集群与恢复

一、实验环境

管理节点：192.168.50.219

数据节点：192.168.50.217，192.168.50.218

sql节点：192.168.50.216

装ndb前，先删除原先系统自带mysql;

各个节点的安装顺序“管理节点—数据节点—SQL节点”；

1、安装管理节点

[root@server1 tarbag]# groupadd mysql

[root@server1 tarbag]# useradd -g mysql mysql[root@server1 tarbag]# tar -zxvf mysql-cluster-gpl-7.0.9-linux-i686-glibc23.tar.gz -C ../software/

[root@server1 tarbag]# cd ../software/mysql-cluster-gpl-7.0.9-linux-i686-glibc23/bin/

[root@server1 bin]# cp -rp ndb* /usr/local/bin/

[root@server1 bin]# mkdir -p /usr/local/mysql-cluster

[root@server1 bin]# cd /usr/local/mysql-cluster/

[root@server1 mysql-cluster]# cp /usr/local/src/software/mysql-cluster-gpl-7.0.9-linux-i686-glibc23/support-files/config.medium.ini ./config.ini

[root@server1 mysql-cluster]# vim config.ini

[NDBD DEFAULT]

NoOfReplicas=2

DataDir=/mydata

DataMemory=200M

IndexMemory=50M

MaxNoOfConcurrentTransactions=500

MaxNoOfConcurrentOperations=1000

MaxNoOfOrderedIndexes=256

MaxNoOfUniqueHashIndexes=128

MaxNoOfAttributes=1000

MaxNoOfTables=100

TimeBetweenWatchDogCheck=15000

NoOfFragmentLogFiles=200

[NDB_MGMD DEFAULT]

PortNumber=1186

DataDir=/mydata

ArbitrationRank=1

[ndb_mgmd]

id=1

HostName=192.168.50.219[ndbd]

Id=2

HostName=192.168.50.217

[ndbd]

Id=3

HostName=192.168.50.218

[mysqld]

Id=4

HostName=192.168.50.216

[mysqld]    //预留1个空的mysqld，数量与sql节点数量一致，数据库恢复时使用

[tcp default]

PortNumber=63132

[root@server1 mysql-cluster]# cp /usr/local/src/software/mysql-cluster-gpl-7.0.9-linux-i686-glibc23/support-files/my-huge.cnf  /etc/my.cnf

[root@server1 ~]# vim /etc/my.cnf

[mysqld]

ndbcluster

ndb-connectstring = 192.168.50.219

[mysql_cluster]

ndb-connectstring = nodeid=1,192.168.50.219

[root@server1 ~]#/usr/local/bin/ndb_mgmd -f /usr/local/mysql-cluster/config.ini  --configdir=/usr/local/mysql-cluster[root@server1 /]# netstat -ntpl |grep ndb

tcp        0      0 0.0.0.0:1186                0.0.0.0:*                   LISTEN   2561/ndb_mgmd  [root@server1 mysql-cluster]# vim /mydata/ndb_1_cluster.log2010-04-30 13:39:58 [MgmtSrvr] INFO     -- Got initial configuration from '/usr/local/mysql-cluster/config.ini', will try to set it when all ndb_mgmd(s)

started

2010-04-30 13:39:58 [MgmtSrvr] INFO     -- Mgmt server state: nodeid 1 reserved for ip 192.168.50.219, m_reserved_nodes 00000000000000000000000000000000

00000000000000000000000000000002.

2010-04-30 13:39:58 [MgmtSrvr] INFO     -- Node 1: Node 1 Connected

2010-04-30 13:39:58 [MgmtSrvr] INFO     -- Id: 1, Command port: *:1186

2010-04-30 13:39:58 [MgmtSrvr] INFO     -- Starting initial configuration change

2010-04-30 13:39:58 [MgmtSrvr] INFO     -- Configuration 1 commited

2010-04-30 13:39:58 [MgmtSrvr] INFO     -- Config change completed! New generation: 1

管理节点安装完毕

2、数据节点安装

[root@server2 ~]# groupadd mysql

[root@server2 ~]# useradd -g mysql mysql

wget http://download.softagency.net/MySQL/Downloads/MySQL-Cluster-7.0/mysql-cluster-gpl-7.0.9-linux-i686-glibc23.tar.gz

[root@server2 tarbag]# tar -zxvf mysql-cluster-gpl-7.0.9-linux-i686-glibc23.tar.gz -C ../software

[root@server2 tarbag]# mv ../software/mysql-cluster-gpl-7.0.9-linux-i686-glibc23/  /usr/local/mysql

[root@server2 tarbag]# cp /usr/local/mysql/support-files/my-huge.cnf /etc/my.cnf

[root@server2 ~]# vim /etc/my.cnf

[mysqld]

datadir=/mydata

ndbcluster

ndb-connectstring = 192.168.50.219

[mysqlhotcopy]

interactive-timeout

[mysql_cluster]

ndb-connectstring= nodeid=2,192.168.50.219

[root@server2 ~]# mkdir /mydata

[root@server2 ~]# chown -R mysql.mysql /mydata/

[root@server2 ~]# vim  .bash_profile

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

export PATH

[root@server2 ~]# source .bash_profile

[root@server2 ~]# /usr/local/mysql/bin/ndbd --initial

2010-05-01 13:36:37 [ndbd] INFO     -- Configuration fetched from '192.168.50.219:1186', generation: 1

[root@server2 ~]# netstat -ntpl |grep ndb

tcp        0      0 192.168.50.215:63132        0.0.0.0:*                   LISTEN      12177/ndbd

设置开机启动

[root@server2 ~]# vi /etc/rc.local

/usr/local/mysql/bin/ndbd

数据结点安装完毕

3、sql节点安装

[root@server4 ~]# groupadd mysql

[root@server4 ~]# useradd -g mysql mysql

[root@server4 ~]# cd /usr/local/src/tarbag/

wgethttp://download.softagency.net/MySQL/Downloads/MySQL-Cluster-7.0/mysql-cluster-gpl-7.0.9-linux-i686-glibc23.tar.gz[root@server4 tarbag]# tar -zxvf mysql-cluster-gpl-7.0.9-linux-i686-glibc23.tar.gz -C /usr/local/

[root@server4 tarbag]# cd /usr/local/

[root@server4 local]# mv mysql-cluster-gpl-7.0.9-linux-i686-glibc23/ mysql

[root@server4 local]# cp mysql/support-files/my-huge.cnf /etc/my.cnf

[root@server4 local]# vim  /etc/my.cnf |uniq

datadir = /mydata

ndbcluster

ndb-connectstring = 192.168.50.219

[mysql_cluster]

ndb-connectstring= nodeid=4,192.168.50.219

[root@server4 ~]# cd /usr/local/mysql/

[root@server4 mysql]# ./scripts/mysql_install_db --user=mysql

[root@server4 mysql]# cp support-files/mysql.server /etc/init.d/mysqld

[root@server4 mysql]# chmod +x /etc/init.d/mysqld

[root@server4 mysql]# chkconfig mysqld on

[root@server4 mysql]# service mysqld start

Starting MySQL.. SUCCESS!

[root@server4 mydata]# mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.1.39-ndb-7.0.9-cluster-gpl-log MySQL Cluster Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

sql节点安装完毕二、MySQL cluster备份恢复

1、打开管理节点的ndb_mgm控制台，输入start backup backupID(backupid用任意数字，比如日期20100106，注意，不能和以前的备份ID相同)。在所有存储节点的$datadir/BACKUP下会生成相应备份目录BACKUP-backupid

2、在ndb_mgm中输入shutdown来关闭所有的存储节点和所有的管理节点

3、在所有的sql节点中删除数据库，留下系统自带的

4、按顺序启动mysql集群

4.1启动管理节点

/usr/local/bin/ndb_mgmd -f /usr/local/mysql-cluster/config.ini --configdir=/usr/local/mysql-cluster --initial  //必须加上initial参数，否则在存储节点恢复是时出现以下错误

Nodeid = 3

Backup Id = 20100510

backup path = /mydata/BACKUP/BACKUP-20100510/

Opening file '/mydata/BACKUP/BACKUP-20100510/BACKUP-20100510.3.ctl'

Backup version in files: ndb-6.3.11 ndb version: mysql-5.1.39 ndb-7.0.9

Stop GCP of Backup: 136000128

Configuration error: Error : Could not alloc node id at 192.168.50.219 port 1186: Id 3 configured as ndbd(NDB), connect attempted as mysqld(API).

Failed to initialize consumers

NDBT_ProgramExit: 1 - Failed

4.2启动存储节点

/usr/local/mysql/bin/ndbd --initial //必须加上initial参数

4.3 启动sql节点

/usr/local/mysql/support-files/mysql.server  restart

5、在存储节点Master(192.168.50.217)上执行ndb_restore还原命令

/usr/local/mysql/bin/ndb_restore -n2 -b20100510 -r -m --backup_path=/mydata/BACKUP/BACKUP-20100510/ --ndb-connectstring=nodeid=5,192.168.50.219

命令解释：

-n 当前存储节点的nodeid

-b 要还原的backupid

-r 数据开关，也就是还原数据了

-m 还原表结构

--ndb-connectstring=nodeid=5,192.168.50.219  //5为config.ini预留的mysql节点

6、在第二个节点(192.168.50.218)上执行还原命令

/usr/local/mysql/bin/ndb_restore -n3 -b20100510 -r -d --backup_path=/mydata/BACKUP/BACKUP-20100510/ --ndb-connectstring=nodeid=5,192.168.50.219

在Master备份的时候要加 -m 开关。

在Slave上要加-d 而且不要-m开关。

7、在sql节点上创建需要还原的数据库，mysql必须重新建立SCHEMA

create database 库名;