mysql cluster reload_MySQL Cluster 备份与恢复

1、在管理节点上进行备份。

ndb_mgm> start backup

ndb_mgm> shutdown

ndb_mgm> exit

2、删掉SQL节点的数据。

DROP DATABASE TEST_CLUSTER;

3、关闭MYSQLD服务器。

[root@localhost bin]# service mysqld stop

Shutting down MySQL... SUCCESS!

4、重新顺序启动所有节点。

[root@localhost mysql]#ndb_mgmd -f /etc/config.ini --reload

[root@localhost data]#ndbd --initial

我发现如果不带这个 --initial选项的话，恢复会失败。

[root@localhost bin]# service mysqld start

//我用的7.0.8a版rpm 安装service mysqld start无法启动，用、、

//[root@localhost ~]# mysqld_safe &

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 |

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

3 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 |

| ... |

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

27 rows in set (0.11 sec)

mysql> select * from cs_comment;

Empty set (0.00 sec)

不过MYSQL的backup 程序现在还只能进行完全备份。

[root@localhost BACKUP]# du -h

76K ./BACKUP-2

96K ./BACKUP-6

180K ./BACKUP-4

172K ./BACKUP-3

76K ./BACKUP-1

60K ./BACKUP-5

668K .

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)

既忽略表空间和分组空间

异常：

[root@localhost ~]# ndb_restore -n2 -b2 -r -m --backup_path=/var/lib/mysql-cluster/BACKUP/BACKUP-2

Nodeid = 2

Backup Id = 2

backup path = /var/lib/mysql-cluster/BACKUP/BACKUP-2

Opening file '/var/lib/mysql-cluster/BACKUP/BACKUP-2/BACKUP-2.2.ctl'

Backup version in files: ndb-6.3.11 ndb version: mysql-5.1.37 ndb-7.0.8

Stop GCP of Backup: 0

Configuration error: Error : Could not alloc node id at 192.168.8.120 port 1186: Connection done from wrong host ip 192.168.8.121.

Failed to initialize consumers

NDBT_ProgramExit: 1 - Failed

解决：管理节点配置文件添加

[mysqld]

//空节点

分享到：

2010-01-05 15:45

浏览 3768

分类:数据库

评论