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

这篇文章主要为大家详细介绍了MySQL ndb集群备份数据库和还原数据库的方法，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

1、在管理节点上进行备份。ndb_mgm> start backup nowaitndb_mgm> Node 3: Backup 4 started from node 1Node 3: Backup 4 started from node 1 completedStartGCP: 43010 StopGCP: 43013#Records: 2138 #LogRecords: 0Data: 53068 bytes Log: 0 bytesndb_mgm> shutdownNode 3: Cluster shutdown initiatedNode 4: Cluster shutdown initiatedNode 4: Node shutdown completed.Node 3: Node shutdown completed.NDB Cluster node(s) have shutdown.Disconnecting to allow management server to shutdown.ndb_mgm> exit2、删掉SQL节点的数据。DROP DATABASE TEST_CLUSTER;、关闭MYSQLD服务器。[root@localhost bin]# service mysqld stopShutting 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 startStarting 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 = 3Backup Id = 4backup path = /usr/local/mysql/data/BACKUP/BACKUP-4/Ndb version in backup files: Version 5.1.21Connected 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 entriesNDBT_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 = 4Backup Id = 4backup path = /usr/local/mysql/data/BACKUP/BACKUP-4/Ndb version in backup files: Version 5.1.21Connected 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 1Restored 2 tuples and 0 log entriesNDBT_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 changedmysql> 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 -hK ./BACKUP-2K ./BACKUP-6K ./BACKUP-4K ./BACKUP-3K ./BACKUP-1K ./BACKUP-5K .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 = 3Backup Id = 1backup path = /usr/local/mysql/data/BACKUP/BACKUP-1/Ndb version in backup files: Version 5.1.21Connected to ndb!!Creating logfile group: lg_1...doneCreating tablespace: ts_1...doneCreating datafile "data_1.dat"...doneCreating undofile "undo_1.dat"...doneSuccessfully 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 0Restored 26 tuples and 0 log entriesNDBT_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 = 6Backup Id = 1backup path = /usr/local/mysql/data/BACKUP/BACKUP-1/Ndb version in backup files: Version 5.1.21Connected 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 3Restored 20 tuples and 0 log entriesNDBT_ProgramExit: 0 - OK-d 开关的意思即：-d, --no-restore-disk-objectsDont restore disk objects (tablespace/logfilegroups etc)既忽略表空间和分组空间

注：关于MySQL ndb集群备份数据库和还原数据库的方法的内容就先介绍到这里，更多相关文章的可以留意