MYSQL CLUSTER NDB 数据库群集的备份与还原
版本：MySQL Cluster Management Server mysql-5.6.17 ndb-7.3.5
1、备份：
在管理服务器上面执行
[root@MSLINUX ~]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> start backup
#不指定备份ID，默认备份ID为1,指定备份号如：start backup 2
Connected to Management Server at: localhost:1186
Waiting for completed, this may take several minutes
Node 2: Backup 1 started from node 1
Node 2: Backup 1 started from node 1 completed
StartGCP: 54553 StopGCP: 54556
#Records: 2065 #LogRecords: 0
Data: 52528 bytes Log: 0 bytes
备份完成以后，将会在现有的两个数据节点上面产生备份文件：
[root@racnode1 BACKUP-1]# pwd
/var/mysql/data/BACKUP/BACKUP-1
[root@racnode1 BACKUP-1]# ls
BACKUP-1-0.2.Data BACKUP-1.2.ctl BACKUP-1.2.log
[root@racnode2 BACKUP-1]# pwd
/var/mysql/data/BACKUP/BACKUP-1
[root@racnode2 BACKUP-1]# ls
BACKUP-1-0.3.Data BACKUP-1.3.ctl BACKUP-1.3.log
文件名包含有备份ID,节点ID等信息
2、删除数据库
此时到任意SQL节点上面删除数据库
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test;
+------+-------+
| num | name |
+------+-------+
| 1 | ceshi |
+------+-------+
1 row in set (0.12 sec)
mysql> drop database test;
Query OK, 1 row affected (0.42 sec)
mysql> exit
3、还原:
还原以前关闭管理节点：
ndb_mgm> shutdown
Node 3: Cluster shutdown initiated
Node 2: Cluster shutdown initiated
Node 3: Node shutdown completed.
Node 2: Node shutdown completed.
3 NDB Cluster node(s) have shutdown.
关闭SQL节点，在全部SQL节点上面执行
[root@centos64 rsync]# service mysqld stop
Shutting down MySQL.....[确定]
[root@cos64 rsync]# service mysqld stop
Shutting down MySQL.......[确定]
重新加载管理节点：
[root@MSLINUX ~]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini --reload
MySQL Cluster Management Server mysql-5.6.17 ndb-7.3.5
数据节点：
[root@racnode1 BACKUP-1]# /usr/local/mysql/bin/ndbd –initial
2018-07-31 17:16:30 [ndbd] INFO -- Angel connected to '193.168.120.60:1186'
2018-07-31 17:16:30 [ndbd] INFO -- Angel allocated nodeid: 2
[root@racnode2 BACKUP-1]# /usr/local/mysql/bin/ndbd –initial
2018-07-31 17:13:54 [ndbd] INFO -- Angel connected to '193.168.120.60:1186'
2018-07-31 17:13:54 [ndbd] INFO -- Angel allocated nodeid: 3
全部SQL节点上面启动mysqld
[root@centos64 rsync]# service mysqld start
Starting MySQL..............................................................[确定]
[root@cos64 rsync]# service mysqld start
Starting MySQL......................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................[确定]
在全部数据节点上面执行还原，注意还原参数当中的数据节点ID和备份ID要正确
root@racnode1 BACKUP-1]# /usr/local/mysql/bin/ndb_restore -n2 -b1 -r --backup_path=/var/mysql/data/BACKUP/BACKUP-1
Nodeid = 2
Backup Id = 1
backup path = /var/mysql/data/BACKUP/BACKUP-1
Opening file '/var/mysql/data/BACKUP/BACKUP-1/BACKUP-1.2.ctl'
File size 22716 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.6.17 ndb-7.3.5
Stop GCP of Backup: 54555
Connected to ndb!!
Opening file '/var/mysql/data/BACKUP/BACKUP-1/BACKUP-1-0.2.Data'
File size 26944 bytes
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_7_3(8) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_sample(5) fragment 0
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(9) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_head(4) fragment 0
_____________________________________________________
Processing data in table: test/def/test(11) fragment 0
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(2) fragment 0
_____________________________________________________
Processing data in table: test1/def/test(10) fragment 0
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(7) fragment 0
Opening file '/var/mysql/data/BACKUP/BACKUP-1/BACKUP-1.2.log'
File size 52 bytes
Restored 0 tuples and 0 log entries
NDBT_ProgramExit: 0 - OK
[root@racnode2 BACKUP-1]# /usr/local/mysql/bin/ndb_restore -n3 -b1 -r --backup_path=/var/mysql/data/BACKUP/BACKUP-1
Nodeid = 3
Backup Id = 1
backup path = /var/mysql/data/BACKUP/BACKUP-1
Opening file '/var/mysql/data/BACKUP/BACKUP-1/BACKUP-1.3.ctl'
File size 22716 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.6.17 ndb-7.3.5
Stop GCP of Backup: 54555
Connected to ndb!!
Opening file '/var/mysql/data/BACKUP/BACKUP-1/BACKUP-1-0.3.Data'
File size 26184 bytes
_____________________________________________________
Processing data in table: mysql/def/NDB$BLOB_7_3(8) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_sample(5) fragment 1
_____________________________________________________
Processing data in table: sys/def/NDB$EVENTS_0(3) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(9) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_index_stat_head(4) fragment 1
_____________________________________________________
Processing data in table: test/def/test(11) fragment 1
_____________________________________________________
Processing data in table: sys/def/SYSTAB_0(2) fragment 1
_____________________________________________________
Processing data in table: test1/def/test(10) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_schema(7) fragment 1
Opening file '/var/mysql/data/BACKUP/BACKUP-1/BACKUP-1.3.log'
File size 52 bytes
Restored 2 tuples and 0 log entries
NDBT_ProgramExit: 0 - OK
此时再到SQL节点上面show database，数据库并没有还原回来，须得新建立数据库，这个时候数据库里面的表才能还原回来
如下：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ndbinfo |
| performance_schema |
| test1 |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.07 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ndbinfo |
| performance_schema |
| test |
| test1 |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.01 sec)
mysql> select * from test
-> ;
+------+-------+
| num | name |
+------+-------+
| 1 | ceshi |
+------+-------+
1 row in set (0.01 sec)
mysql>
