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

1、在管理节点上进行备份。
ndb_mgm>startbackupnowait
ndb_mgm>Node3:Backup4startedfromnode1
Node3:Backup4startedfromnode1completed
StartGCP:43010StopGCP:43013
#Records:2138#LogRecords:0
Data:53068bytesLog:0bytes

ndb_mgm>shutdown
Node3:Clustershutdowninitiated
Node4:Clustershutdowninitiated
Node4:Nodeshutdowncompleted.
Node3:Nodeshutdowncompleted.
NDBClusternode(s)haveshutdown.
Disconnectingtoallowmanagementservertoshutdown.
ndb_mgm>exit
2、删掉SQL节点的数据。
DROPDATABASETEST_CLUSTER;
、关闭MYSQLD服务器。
[root@localhostbin]#servicemysqldstop
ShuttingdownMySQL...SUCCESS!
3、重新顺序启动所有节点。
[root@localhostmysql]#/usr/local/mysql/ndb_mgmd-f/etc/config.ini
[root@localhostdata]#/usr/local/mysql/bin/ndbd--initial
我发现如果不带这个--initial选项的话，恢复会失败。
[root@localhostbin]#servicemysqldstart
StartingMySQLSUCCESS!
4、在NDBD节点上进行恢复。（每个节点都得执行一次，因为数据分散在两个节点上）
第一个节点：
[root@localhostBACKUP]#/usr/local/mysql/bin/ndb_restore-n3-b4-r-m--backup_path=/usr/local/mysql/data/BACKUP/BACKUP-4/
-r开关是记录集合。
-m是元数据。就是表和库的SCHEMA。
Nodeid=3
BackupId=4
backuppath=/usr/local/mysql/data/BACKUP/BACKUP-4/
Ndbversioninbackupfiles:Version5.1.21
Connectedtondb!!
Successfullyrestoredtable`test_cluster/def/lk4_test`
...
Successfullycreatedindex`PRIMARY`on`lk4_test`
...
_____________________________________________________
Processingdataintable:test_cluster/def/lk4_test54)fragment1
_____________________________________________________
...
Restored37tuplesand0logentries

NDBT_ProgramExit:0-OK
第二个节点：
[root@localhostBACKUP-1]#/usr/local/mysql/bin/ndb_restore-n4-b4-r--backup_path=/usr/local/mysql/data/BACKUP/BACKUP-4/
Nodeid=4
BackupId=4
backuppath=/usr/local/mysql/data/BACKUP/BACKUP-4/
Ndbversioninbackupfiles:Version5.1.21
Connectedtondb!!
_____________________________________________________
Processingdataintable:sys/def/NDB$EVENTS_0(1)fragment1
_____________________________________________________
Processingdataintable:mysql/def/ndb_apply_status(4)fragment1
_____________________________________________________
Processingdataintable:mysql/def/NDB$BLOB_2_3(3)fragment1
_____________________________________________________
Processingdataintable:test/def/t11(5)fragment1
_____________________________________________________
Processingdataintable:sys/def/SYSTAB_0(0)fragment1
_____________________________________________________
Processingdataintable:mysql/def/ndb_schema(2)fragment1
Restored2tuplesand0logentries

NDBT_ProgramExit:0-OK

这里完成。
5、查看一下有没有数据，为了安全起见。
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|test|
+--------------------+
rowsinset(0.00sec)
没有恢复的数据库？
MYSQL现在必须重新建立SCHEMA。

mysql>createdatabasetest_cluster;
QueryOK,1rowaffected(0.33sec)

mysql>usetest_cluster;
Databasechanged
mysql>showtables;
+------------------------------+
|Tables_in_test_cluster|
+------------------------------+
|lk4_test|
|...|
+------------------------------+
rowsinset(0.11sec)

mysql>select*fromcs_comment;
Emptyset(0.00sec)

不过MYSQL的backup程序现在还只能进行完全备份。
[root@localhostBACKUP]#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@node239ndb_6_fs]#rm-rf*.dat

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

具体步骤如下：
MASTER:

[root@localhostndb_3_fs]#/usr/local/mysql/bin/ndb_restore-n3-b1-r-m--backup_path=/usr/local/mysql/data/BACKUP/BACKUP-1/
Nodeid=3
BackupId=1
backuppath=/usr/local/mysql/data/BACKUP/BACKUP-1/
Ndbversioninbackupfiles:Version5.1.21
Connectedtondb!!
Creatinglogfilegroup:lg_1...done
Creatingtablespace:ts_1...done
Creatingdatafile"data_1.dat"...done
Creatingundofile"undo_1.dat"...done
Successfullyrestoredtable`test/def/t11`
SuccessfullyrestoredtableeventREPL$test/t11
_____________________________________________________
Processingdataintable:sys/def/NDB$EVENTS_0(1)fragment0
_____________________________________________________
Processingdataintable:mysql/def/NDB$BLOB_2_3(3)fragment0
_____________________________________________________
Processingdataintable:sys/def/SYSTAB_0(0)fragment0
_____________________________________________________
Processingdataintable:mysql/def/ndb_schema(2)fragment0
_____________________________________________________
Processingdataintable:mysql/def/ndb_apply_status(4)fragment0
_____________________________________________________
Processingdataintable:test/def/t11(10)fragment0
Restored26tuplesand0logentries

NDBT_ProgramExit:0-OK

其他的SLAVE上的操作：

[root@node239ndb_6_fs]#/usr/local/mysql/bin/ndb_restore-n6-b1-r-d--backup_path=/usr/local/mysql/data/BACKUP/BACKUP-1/
Nodeid=6
BackupId=1
backuppath=/usr/local/mysql/data/BACKUP/BACKUP-1/
Ndbversioninbackupfiles:Version5.1.21
Connectedtondb!!
_____________________________________________________
Processingdataintable:sys/def/NDB$EVENTS_0(1)fragment3
_____________________________________________________
Processingdataintable:mysql/def/NDB$BLOB_2_3(3)fragment3
_____________________________________________________
Processingdataintable:sys/def/SYSTAB_0(0)fragment3
_____________________________________________________
Processingdataintable:mysql/def/ndb_schema(2)fragment3
_____________________________________________________
Processingdataintable:mysql/def/ndb_apply_status(4)fragment3
_____________________________________________________
Processingdataintable:test/def/t11(10)fragment3
Restored20tuplesand0logentries

NDBT_ProgramExit:0-OK


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