mysql ndb集群备份数据库和还原数据库的方法

1、在管理节点上进行备份。 ndb_mgm> start backup Waiting for completed, this may take several minutes Node 3: Backup 3 started from node 1 Node 3: Backup 3 started from node 1 completed StartGCP: 1249 StopGCP: 1252 #Records: 2058 #LogRecords: 0 Data: 50752 bytes Log: 0 bytes ndb_mgm> shutdown Node 2: Cluster shutdown initiated Node 3: Cluster shutdown initiated Node 2: Node shutdown completed. Node 3: Node shutdown completed. 2 NDB Cluster node(s) have shutdown. Disconnecting to allow management server to shutdown. ndb_mgm> exit 2、删掉SQL节点的数据。 mysql> use test; mysql> drop tables ctest; Query OK, 0 rows affected (0.77 sec) 3、关闭MYSQLD服务器。 [root@Redhat52 data]#service mysqld stop Shutting down MySQL... SUCCESS! 4、重新顺序启动所有节点。 [root@test5 mysql-cluster]#ndb_mgmd -f /etc/config.ini [root@Redhat52 data]# /usr/local/mysql/bin/ndbd --initial [root@Redhat52 data]# service mysqld start Starting MySQL SUCCESS! 5、在NDBD节点上进行恢复。（每个节点都得执行一次，因为数据分散在两个节点上） 第一个节点： -r开关是记录集合。 -m是元数据。就是表和库的SCHEMA。 [root@Redhat51 data]# /usr/local/mysql/bin/ndb_restore -n2 -b3 -r -m --backup_p ath=/usr/local/mysql/data/BACKUP/BACKUP-3/ Nodeid = 3 Backup Id = 3 backup path = /usr/local/mysql/data/BACKUP/BACKUP-3/ Opening file '/usr/local/mysql/data/BACKUP/BACKUP-3/BACKUP-3.3.ctl' Backup version in files: ndb-6.3.11 ndb version: mysql-5.1.34 ndb-7.0.6 Stop GCP of Backup: 0 Connected to ndb!! Successfully restored table `test/def/ctest` Successfully restored table event REPL$test/ctest Opening file '/usr/local/mysql/data/BACKUP/BACKUP-3/BACKUP-3-0.3.Data' _____________________________________________________ Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 1 _____________________________________________________ Processing data in table: mysql/def/ndb_apply_status(6) fragment 1 _____________________________________________________ Processing data in table: test/def/ctest(7) fragment 1 _____________________________________________________ Processing data in table: mysql/def/NDB$BLOB_4_3(5) fragment 1 _____________________________________________________ Processing data in table: sys/def/SYSTAB_0(2) fragment 1 _____________________________________________________ Processing data in table: mysql/def/ndb_schema(4) fragment 1 Opening file '/usr/local/mysql/data/BACKUP/BACKUP-3/BACKUP-3.3.log' Restored 3 tuples and 0 log entries NDBT_ProgramExit: 0 - OKentries NDBT_ProgramExit: 0 - OK 第二个节点： [root@Redhat52 data]# /usr/local/mysql/bin/ndb_restore -n3 -b3 -r --backup_path=/usr/local/mysql/data/BACKUP/BACKUP-3/ Nodeid = 3 Backup Id = 3 backup path = /usr/local/mysql/data/BACKUP/BACKUP-3/ Opening file '/usr/local/mysql/data/BACKUP/BACKUP-3/BACKUP-3.3.ctl' Backup version in files: ndb-6.3.11 ndb version: mysql-5.1.34 ndb-7.0.6 Stop GCP of Backup: 0 Connected to ndb!! Opening file '/usr/local/mysql/data/BACKUP/BACKUP-3/BACKUP-3-0.3.Data' _____________________________________________________ Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 1 _____________________________________________________ Processing data in table: mysql/def/ndb_apply_status(6) fragment 1 _____________________________________________________ Processing data in table: test/def/ctest(7) fragment 1 _____________________________________________________ Processing data in table: mysql/def/NDB$BLOB_4_3(5) fragment 1 _____________________________________________________ Processing data in table: sys/def/SYSTAB_0(2) fragment 1 _____________________________________________________ Processing data in table: mysql/def/ndb_schema(4) fragment 1 Opening file '/usr/local/mysql/data/BACKUP/BACKUP-3/BACKUP-3.3.log' Restored 3 tuples and 0 log entries NDBT_ProgramExit: 0 - OK 6、查看一下有没有数据，为了安全起见。 没有恢复的数据库？ MYSQL现在必须重新建立SCHEMA。 mysql> create database test; Query OK, 1 row affected (0.33 sec) mysql> use test; Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | ctest | +----------------+ 1 row in set (0.06 sec) 7、在NDBD节点上进行恢复的时候有一个要注意的问题。 因为NDBD节点以 --initial 方式启动的时候不会自动删除undo 和 data 文件（即保存到磁盘上的表数据），所以得手动在每个NDBD节点上进行RM操作： [root@Redhat52 ndb_3_fs]# rm * -rf * 然后开始备份。 在MASTER上备份的时候要加 -m 开关。 在SLAVE上要加-d 而且不要-m开关。 具体步骤如下： MASTER : [root@Redhat51 data]# /usr/local/mysql/bin/ndb_restore -n2 -b3 -r -m --backup_p ath=/usr/local/mysql/data/BACKUP/BACKUP-3/ Nodeid = 3 Backup Id = 3 backup path = /usr/local/mysql/data/BACKUP/BACKUP-3/ Opening file '/usr/local/mysql/data/BACKUP/BACKUP-3/BACKUP-3.3.ctl' Backup version in files: ndb-6.3.11 ndb version: mysql-5.1.34 ndb-7.0.6 Stop GCP of Backup: 0 Connected to ndb!! Successfully restored table `test/def/ctest` Successfully restored table event REPL$test/ctest Opening file '/usr/local/mysql/data/BACKUP/BACKUP-3/BACKUP-3-0.3.Data' _____________________________________________________ Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 1 _____________________________________________________ Processing data in table: mysql/def/ndb_apply_status(6) fragment 1 _____________________________________________________ Processing data in table: test/def/ctest(7) fragment 1 _____________________________________________________ Processing data in table: mysql/def/NDB$BLOB_4_3(5) fragment 1 _____________________________________________________ Processing data in table: sys/def/SYSTAB_0(2) fragment 1 _____________________________________________________ Processing data in table: mysql/def/ndb_schema(4) fragment 1 Opening file '/usr/local/mysql/data/BACKUP/BACKUP-3/BACKUP-3.3.log' Restored 3 tuples and 0 log entries NDBT_ProgramExit: 0 - OKentries NDBT_ProgramExit: 0 - OK 其他的SLAVE上的操作： [root@Redhat52 data]# /usr/local/mysql/bin/ndb_restore -n3 -b3 -r --backup_path=/usr/local/mysql/data/BACKUP/BACKUP-3/ Nodeid = 3 Backup Id = 3 backup path = /usr/local/mysql/data/BACKUP/BACKUP-3/ Opening file '/usr/local/mysql/data/BACKUP/BACKUP-3/BACKUP-3.3.ctl' Backup version in files: ndb-6.3.11 ndb version: mysql-5.1.34 ndb-7.0.6 Stop GCP of Backup: 0 Connected to ndb!! Opening file '/usr/local/mysql/data/BACKUP/BACKUP-3/BACKUP-3-0.3.Data' _____________________________________________________ Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 1 _____________________________________________________ Processing data in table: mysql/def/ndb_apply_status(6) fragment 1 _____________________________________________________ Processing data in table: test/def/ctest(7) fragment 1 _____________________________________________________ Processing data in table: mysql/def/NDB$BLOB_4_3(5) fragment 1 _____________________________________________________ Processing data in table: sys/def/SYSTAB_0(2) fragment 1 _____________________________________________________ Processing data in table: mysql/def/ndb_schema(4) fragment 1 Opening file '/usr/local/mysql/data/BACKUP/BACKUP-3/BACKUP-3.3.log' Restored 3 tuples and 0 log entries NDBT_ProgramExit: 0 - OK -d 开关的意思即： -d, --no-restore-disk-objects Dont restore disk objects (tablespace/logfilegroups etc) 既忽略表空间和分组空间

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23721637/viewspace-1051467/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23721637/viewspace-1051467/