首页 / Mysql / 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)
既忽略表空间和分组空间

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)
既忽略表空间和分组空间

本文来自网络,不代表我是攻城狮立场,转载请注明出处:https://www.54gcs.cn/sql/mysql/878.html

helwo作者

上一篇
下一篇

为您推荐

发表评论

联系我们

联系我们

在线咨询: QQ交谈

邮箱: web@5vo.cn

工作时间:周一至周五,9:00-17:30,节假日休息
关注微信
微信扫一扫关注我们

微信扫一扫关注我们

手机访问
手机扫一扫打开网站

手机扫一扫打开网站

返回顶部
跳至工具栏