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) 
既忽略表空间和分组空间

上一个：MySQL Memory 存储引擎浅析
下一个：忘记mysql数据库root用户密码重置方法[图文]