《Mysql應(yīng)用mysql ndb集群備份數(shù)據(jù)庫和還原數(shù)據(jù)庫的方法》要點：
本文介紹了Mysql應(yīng)用mysql ndb集群備份數(shù)據(jù)庫和還原數(shù)據(jù)庫的方法，希望對您有用。如果有疑問，可以聯(lián)系我們。

MYSQL必讀1、在管理節(jié)點上進行備份.
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節(jié)點的數(shù)據(jù).
DROP DATABASE TEST_CLUSTER;
、關(guān)閉MYSQLD服務(wù)器.
[root@localhost bin]# service mysqld stop
Shutting down MySQL... SUCCESS!
3、重新順序啟動所有節(jié)點.
[root@localhost mysql]# /usr/local/mysql/ndb_mgmd -f /etc/config.ini
[root@localhost data]# /usr/local/mysql/bin/ndbd --initial
我發(fā)現(xiàn)如果不帶這個 --initial選項的話,恢復(fù)會失敗.
[root@localhost bin]# service mysqld start
Starting MySQL SUCCESS!
4、在NDBD節(jié)點上進行恢復(fù).(每個節(jié)點都得執(zhí)行一次,因為數(shù)據(jù)分散在兩個節(jié)點上)
第一個節(jié)點：
[root@localhost BACKUP]# /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ù).就是表和庫的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
第二個節(jié)點：
[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、查看一下有沒有數(shù)據(jù),為了安全起見.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
rows in set (0.00 sec)
沒有恢復(fù)的數(shù)據(jù)庫?
MYSQL現(xiàn)在必須重新建立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 程序現(xiàn)在還只能進行完全備份.
[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節(jié)點上進行恢復(fù)的時候有一個要注意的問題.
因為NDBD節(jié)點以 --initial 方式啟動的時候不會自動刪除undo 和 data 文件(即保存到磁盤上的表數(shù)據(jù)),所以得手動在每個NDBD節(jié)點上進行RM操作：

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

然后開始備份.
在MASTER上備份的時候要加 -m 開關(guān).
在SLAVE上要加-d 而且不要-m開關(guān).

具體步驟如下：
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 開關(guān)的意思即：
-d, --no-restore-disk-objects
Dont restore disk objects (tablespace/logfilegroups etc)
既忽略表空間和分組空間

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/4826.html