MYSQL集群的備份與恢復最終版本

MYSQL集群的備份與恢復最終版本中午剛剛弄明白了MYSQL集群的備份與恢復。寫下來，以后就不用為這個問題浪費時間了。1、 在管理節(jié)點上進行備份。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節(jié)點的數(shù)據(jù)。DROPDATABASETEST_CLUSTER;3、 關(guān)閉MYSQLD服務(wù)器。[root@localhostbin]#servicemysqldstopShuttingdownMySQL...SUCCESS!4、 重新順序啟動所有節(jié)點。[root@localhostmysql]#/usr/local/mysql/ndb_mgmd-f/etc/config.ini[root@localhostdata]#/usr/local/mysql/bin/ndbd--initial我發(fā)現(xiàn)如果不帶這個--initial選項的話，恢復會失敗。[root@localhostbin]#servicemysqldstartStartingMySQLSUCCESS!4、在NDBD節(jié)點上進行恢復。(每個節(jié)點都得執(zhí)行一次，因為數(shù)據(jù)分散在兩個節(jié)點上第一個節(jié)點：[root@localhostBACKUP]#/usr/local/mysql/bin/ndb_restore-n3-b4-r-m--backup_path=/usr/local/mysql/data/BACKUP/BACKUP-4/-r開關(guān)是記錄集合。-m是元數(shù)據(jù)。就是表和庫的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第二個節(jié)點：-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、查看一下有沒有數(shù)據(jù)，為了安全起見。mysql>showdatabases;TOC\o"1-5"\h\z+ +|Database|+ +|information_schema||mysql ||test |+ +3rowsinset(0.00sec)沒有恢復的數(shù)據(jù)庫？MYSQL現(xiàn)在必須重新建立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程序現(xiàn)在還只能進行完全備份。[root@localhostBACKUP]#du-h76K./BACKUP-296K./BACKUP-6180K./BACKUP-4172K./BACKUP-376K./BACKUP-160K./BACKUP-5668K .6、在NDBD節(jié)點上進行恢復的時候有一個要注意的問題。因為NDBD節(jié)點以--initial方式啟動的時候不會自動刪除undo和data文件(即保存到磁盤上的表數(shù)據(jù))，所以得手動在每個NDBD節(jié)點上進行RM操作：[root@node239ndb_6_fs]#rm-rf*.dat然后開始備份。在MASTER上備份的時候要加-m開關(guān)。在SLAVE上要加-d而且不要-m開關(guān)。具體步驟如下：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