[MySQL] - back and restore

Single User Mode:connections to all other API nodes are closed gracefully and all running transactions are aborted. No new transactions are permitted to start.
ndb_mgm> ENTER SINGLE USER MODE {node ID}
ndb_mgm> EXIT SINGLE USER MODE

cluster backup:
1.backup主要分為三大部份
a)meatadata(BACKUP-backup_id.node_id.ctl): 所有database tables的names and definitions.
b)table records(BACKUP-backup_id-0.node_id.data): 實際上所有tables的資料.
c)Transaction log(BACKUP-backup_id.node_id.log): A sequential record telling how and when data was stored in the database.

Cluster backups are created by default in the BACKUP subdirectory of the DataDir on each data node.

2.做法:
a)啟動 ndb_mgm
b)mgm> START BACKUP [NOWAIT | WAIT STARTED | WAIT COMPLETED]
c)check %mysql_homedir%/data/BACKUP/

cluster restore:
1.針對每次 backup 產出的files(three file, .ctl;.data,.log),都需執行一次ndb_restore
2.在進行restore前,請使用single user mode.
3.ndb_restore syntax:
ndb_restore [-c connectstring] -n node_id [-s] [-m] -b backup_id -r [backup_path=]/path/to/backup/files [-e]
-n :is specify the node ID of the data node.
-m :確保metadata重新載入(re-create database tables),但這也要有一個空的database來執行這項工作(ndbd --initial)
-s(skip-table-check) 忽略 data 和 table schema 不合的錯誤.
-a :data backed up from a column of a given type can generally be restored to a column using a 「larger, similar」 type.
-b :used to specify the ID or sequence number of the backup(與backup時用的id相同).
-e :adds (or restores) epoch information to the cluster replication status table. This is useful for starting replication on a MySQL Cluster replication slave.
4.於config.ini裡保留一個空的[api] or [mysqld]的section,這個section主要用來使用single user mode時,給該cluster API node使用.
5.mysql 5.1.18後提供針對databases or tables的ndb_restore.syntax如下:
ndb_restore other_options db_name_1 [db_name_2[, db_name_3][, ...] | tbl_name_1[, tbl_name_2][, ...]]
6.configuration for cluster backup:
a)BackupDataBufferSize: The amount of memory used to buffer data before it is written to disk.
b)BackupLogBufferSize: The amount of memory used to buffer log records before these are written to disk.
c)BackupMemory: The total memory allocated in a database node for backups. This should be the sum of the memory allocated for the backup data buffer and the backup log buffer.
d)BackupWriteSize: The default size of blocks written to disk. This applies for both the backup data buffer and the backup log buffer.
e)BackupMaxWriteSize: The maximum size of blocks written to disk. This applies for both the backup data buffer and the backup log buffer.

resore時注意事項:
有一個 Bug #25918 ndb_restore fails when restoring a backup of a disk-data cluster
要解決此問題,可於每個 datanode下 %mysqldir%/data/ndb_xx_fs/所有files and dirs先備份到其它地方(mv),之後再以ndbd --initial重啟datanode後~再行執行ndb_restore就ok了.