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

ndbt_programexit: 0 - ok

这里完成。

5、查看一下有没有数据，为了安全起见。

mysql> show databases;

+--------------------+

| database |

+--------------------+

| information_schema |

| mysql |

| test |

+--------------------+

rows in set (0.00 sec)

没有恢复的数据库？

mysql现在必须重新建立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 程序现在还只能进行完全备份。

[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节点上进行恢复的时候有一个要注意的问题。

因为ndbd节点以 --initial 方式启动的时候不会自动删除undo 和 data 文件(即保存到磁盘上的表数据)，所以得手动在每个ndbd节点上进行rm操作：

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

然后开始备份。

在master上备份的时候要加 -m 开关。

在slave上要加-d 而且不要-m开关。

具体步骤如下：

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 开关的意思即：

-d, --no-restore-disk-objects

dont restore disk objects (tablespace/logfilegroups etc)

既忽略表空间和分组空间

希望与广大网友互动？？

点此进行留言吧！