mysql ndb集群备份数据库和还原数据库的方法

1、在管理节点上进行备份。
ndb_mgm> start backup nowait
ndb_mgm> node 3: backup 4 started from node 1
node 3: backup 4 started from node 1 completed
startgcp: 43010 stopgcp: 43013
#records: 2138 #logrecords: 0
data: 53068 bytes log: 0 bytes

ndb_mgm> shutdown
node 3: cluster shutdown initiated
node 4: cluster shutdown initiated
node 4: node shutdown completed.
node 3: node shutdown completed.
ndb cluster node(s) have shutdown.
disconnecting to allow management server to shutdown.
ndb_mgm> exit
2、删掉sql节点的数据。
drop database test_cluster;
、关闭mysqld服务器。
[root@localhost bin]# service mysqld stop
shutting down mysql... success!
3、重新顺序启动所有节点。
[root@localhost mysql]# /usr/local/mysql/ndb_mgmd -f /etc/config.ini
[root@localhost data]# /usr/local/mysql/bin/ndbd --initial
我发现如果不带这个 --initial选项的话，恢复会失败。
[root@localhost bin]# service mysqld start
starting mysql success!
4、在ndbd节点上进行恢复。（每个节点都得执行一次，因为数据分散在两个节点上）
第一个节点：
[root@localhost backup]# /usr/local/mysql/bin/ndb_restore -n3 -b4 -r -m --backup_path=/usr/local/mysql/data/backup/backup-4/
-r开关是记录集合。
-m是元数据。就是表和库的schema。
nodeid = 3
backup id = 4
backup path = /usr/local/mysql/data/backup/backup-4/
ndb version in backup files: version 5.1.21
connected to ndb!!
successfully restored table `test_cluster/def/lk4_test`
...
successfully created index `primary` on `lk4_test`
...
_____________________________________________________
processing data in table: test_cluster/def/lk4_test54) fragment 1
_____________________________________________________
...
restored 37 tuples and 0 log entries

ndbt_programexit: 0 - ok
第二个节点：
[root@localhost backup-1]# /usr/local/mysql/bin/ndb_restore -n4 -b4 -r --backup_path=/usr/local/mysql/data/backup/backup-4/
nodeid = 4
backup id = 4
backup path = /usr/local/mysql/data/backup/backup-4/
ndb version in backup files: version 5.1.21
connected to ndb!!
_____________________________________________________
processing data in table: sys/def/ndb$events_0(1) fragment 1
_____________________________________________________
processing data in table: mysql/def/ndb_apply_status(4) fragment 1
_____________________________________________________
processing data in table: mysql/def/ndb$blob_2_3(3) fragment 1
_____________________________________________________
processing data in table: test/def/t11(5) fragment 1
_____________________________________________________
processing data in table: sys/def/systab_0(0) fragment 1
_____________________________________________________
processing data in table: mysql/def/ndb_schema(2) fragment 1
restored 2 tuples and 0 log entries

ndbt_programexit: 0 - ok

这里完成。
5、查看一下有没有数据，为了安全起见。
mysql> show databases;
+--------------------+
| database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
rows in set (0.00 sec)
没有恢复的数据库？
mysql现在必须重新建立schema。

mysql> create database test_cluster;
query ok, 1 row affected (0.33 sec)

mysql> use test_cluster;
database changed
mysql> show tables;
+------------------------------+
| tables_in_test_cluster |
+------------------------------+
| lk4_test |
| ... |
+------------------------------+
rows in set (0.11 sec)

mysql> select * from cs_comment;
empty set (0.00 sec)

不过mysql的backup 程序现在还只能进行完全备份。
[root@localhost backup]# du -h
k ./backup-2
k ./backup-6
k ./backup-4
k ./backup-3
k ./backup-1
k ./backup-5
k .
6、在ndbd节点上进行恢复的时候有一个要注意的问题。
因为ndbd节点以 --initial 方式启动的时候不会自动删除undo 和 data 文件（即保存到磁盘上的表数据），所以得手动在每个ndbd节点上进行rm操作：

[root@node239 ndb_6_fs]# rm -rf *.dat

然后开始备份。
在master上备份的时候要加 -m 开关。
在slave上要加-d 而且不要-m开关。

具体步骤如下：
master :

[root@localhost ndb_3_fs]# /usr/local/mysql/bin/ndb_restore -n3 -b1 -r -m --backup_path=/usr/local/mysql/data/backup/backup-1/
nodeid = 3
backup id = 1
backup path = /usr/local/mysql/data/backup/backup-1/
ndb version in backup files: version 5.1.21
connected to ndb!!
creating logfile group: lg_1...done
creating tablespace: ts_1...done
creating datafile "data_1.dat"...done
creating undofile "undo_1.dat"...done
successfully restored table `test/def/t11`
successfully restored table event repl$test/t11
_____________________________________________________
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: test/def/t11(10) fragment 0
restored 26 tuples and 0 log entries

ndbt_programexit: 0 - ok

其他的slave上的操作：

[root@node239 ndb_6_fs]# /usr/local/mysql/bin/ndb_restore -n6 -b1 -r -d --backup_path=/usr/local/mysql/data/backup/backup-1/
nodeid = 6
backup id = 1
backup path = /usr/local/mysql/data/backup/backup-1/
ndb version in backup files: version 5.1.21
connected to ndb!!
_____________________________________________________
processing data in table: sys/def/ndb$events_0(1) fragment 3
_____________________________________________________
processing data in table: mysql/def/ndb$blob_2_3(3) fragment 3
_____________________________________________________
processing data in table: sys/def/systab_0(0) fragment 3
_____________________________________________________
processing data in table: mysql/def/ndb_schema(2) fragment 3
_____________________________________________________
processing data in table: mysql/def/ndb_apply_status(4) fragment 3
_____________________________________________________
processing data in table: test/def/t11(10) fragment 3
restored 20 tuples and 0 log entries

ndbt_programexit: 0 - ok


-d 开关的意思即：
-d, --no-restore-disk-objects
dont restore disk objects (tablespace/logfilegroups etc)
既忽略表空间和分组空间