MYSQL集群的备份与恢复最终版本

MYSQL集群的备份与恢复最终版本中午刚刚弄明白了MYSQL集群的备份与恢复。写下来，以后就不用为这个问题浪费时间了。1、 在管理节点上进行备份。ndb_mgm>startbackupnowaitndb_mgm>Node3:Backup4startedfromnode1Node3:Backup4startedfromnode1completedStartGCP:43010StopGCP:43013#Records:2138#LogRecords:0Data:53068bytesLog:0bytesndb_mgm>shutdownNode3:ClustershutdowninitiatedNode4:ClustershutdowninitiatedNode4:Nodeshutdowncompleted.Node3:Nodeshutdowncompleted.2NDBClusternode(s)haveshutdown.Disconnectingtoallowmanagementservertoshutdown.ndb_mgm>exit2、 删掉SQL节点的数据。DROPDATABASETEST_CLUSTER;3、 关闭MYSQLD服务器。[root@localhostbin]#servicemysqldstopShuttingdownMySQL...SUCCESS!4、 重新顺序启动所有节点。[root@localhostmysql]#/usr/local/mysql/ndb_mgmd-f/etc/config.ini[root@localhostdata]#/usr/local/mysql/bin/ndbd--initial我发现如果不带这个--initial选项的话，恢复会失败。[root@localhostbin]#servicemysqldstartStartingMySQLSUCCESS!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是元数据。就是表和库的SCHEMAoNodeid=3BackupId=4backuppath=/usr/local/mysql/data/BACKUP/BACKUP-4/Ndbversioninbackupfiles:Version5.1.21Connectedtondb!!Successfullyrestoredtable'test_cluster/def/lk4_test'Successfullycreatedindex'PRIMARY'on'lk4_testProcessingdataintable:test_cluster/def/lk4_test54)fragment1Restored37tuplesand0logentriesNDBT_ProgramExit:0-OK第二个节点：-bac[root@localhostBACKUP-1]#/usr/local/mysql/bin/ndb_restore-n4-b4-rkup_path=/usr/local/mysql/data/BACKUP/BACKUP-4/-bacNodeid=4BackupId=4backuppath=/usr/local/mysql/data/BACKUP/BACKUP-4/Ndbversioninbackupfiles:Version5.1.21Connectedtondb!!Processingdataintable:sys/def/NDB$EVENTS_0(1)fragment1Processingdataintable:mysql/def/ndb_apply_status(4)fragment1Processingdataintable:mysql/def/NDB$BLOB_2_3(3)fragment1Processingdataintable:test/def/t11(5)fragment1Processingdataintable:sys/def/SYSTAB_0(0)fragment1Processingdataintable:mysql/def/ndb_schema(2)fragment1Restored2tuplesand0logentriesNDBT_ProgramExit:0-OK这里完成。5、查看一下有没有数据，为了安全起见。mysql>showdatabases;TOC\o"1-5"\h\z+ +|Database|+ +|information_schema||mysql ||test |+ +3rowsinset(0.00sec)没有恢复的数据库？MYSQL现在必须重新建立SCHEMAomysql>createdatabasetest_cluster;QueryOK,1rowaffected(0.33sec)mysql>usetest_cluster;Databasechangedmysql>showtables;TOC\o"1-5"\h\z+ +|Tables_in_test_cluster|+ +|lk4_test ||... |+ +27rowsinset(0.11sec)mysql>select*fromcs_comment;Emptyset(0.00sec)不过MYSQL的backup程序现在还只能进行完全备份。[root@localhostBACKUP]#du-h76K./BACKUP-296K./BACKUP-6180K./BACKUP-4172K./BACKUP-376K./BACKUP-160K./BACKUP-5668K .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-bl-r-m--backup_path=/usr/local/mysql/data/BACKUP/BACKUP-1/Nodeid=3BackupId=1backuppath=/usr/local/mysql/data/BACKUP/BACKUP-1/Ndbversioninbackupfiles:Version5.1.21Connectedtondb!!Creatinglogfilegroup:lg_1...doneCreatingtablespace:ts_1...doneCreatingdatafile"data_1.dat"...doneCreatingundofile"undo_1.dat"...doneSuccessfullyrestoredtable'test/def/t11'SuccessfullyrestoredtableeventREPL$test/t11Processingdataintable:sys/def/NDB$EVENTS_0(1)fragment0Processingdataintable:mysql/def/NDB$BLOB_2_3(3)fragment0Processingdataintable:sys/def/SYSTAB_0(0)fragment0Processingdataintable:mysql/def/ndb_schema(2)fragment0Processingdataintable:mysql/def/ndb_apply_status(4)fragment0Processingdataintable:test/def/t11(10)fragment0Restored26tuplesand0logentriesNDBT_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=6BackupId=1backuppath=/usr/local/mysql/data/BACKUP/BACKUP-1/Ndbversioninbackupfiles:Version5.1.21Connectedtondb!!Processingdataintable:sys/def/NDB$EVENTS_0(1)fragment3Processingdataintable:mysql/def/NDB$BLOB_2_3(3)fragment3Processingdataintable:sys/def/SYSTAB_0(0)fragment3Processingdataintable:mysql/def/ndb_schema(2)fragment3Processingdataintable:mysql/def/ndb_apply_stat