mysql ndb 备份_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