mysql ndb cluster database backup and restore methods

  • 2020-05-12 06:22:34
  • OfStack

1. Backup on the management node.
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. Delete the data of the SQL node.
DROP DATABASE TEST_CLUSTER;
, shut down the MYSQLD server.
[root@localhost bin]# service mysqld stop
Shutting down MySQL... SUCCESS!
3. Restart all nodes in sequence.
[root@localhost mysql]# /usr/local/mysql/ndb_mgmd -f /etc/config.ini
[root@localhost data]# /usr/local/mysql/bin/ndbd --initial
I found that if I didn't have this --initial option, the recovery would fail.
[root@localhost bin]# service mysqld start
Starting MySQL SUCCESS!
4. Restore on NDBD node. (each node has to be executed once because the data is spread over two nodes)
The first node:
[root@localhost BACKUP]# /usr/local/mysql/bin/ndb_restore -n3 -b4 -r -m --backup_path=/usr/local/mysql/data/BACKUP/BACKUP-4/
-r switch is a set of records.
-m is metadata. SCHEMA for tables and libraries.
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
The second node:
[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

We're done here.
5. Check 1 to see if there is any data, just to be on the safe side.
mysql > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
rows in set (0.00 sec)
No recovered database?
MYSQL must now re-establish 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)

However, MYSQL's backup program is now fully backed up.
[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. There is one problem to pay attention to when restoring on the NDBD node.
Since the NDBD node does not automatically delete the undo and data files (that is, the table data saved to disk) when it is booted in the --initial mode, RM operations have to be performed manually on each NDBD node:

[root@node239 ndb_6_fs]# rm -rf *.dat

Then start the backup.
Backup on MASTER with the -m switch.
Add -d to SLAVE and do not use the -m switch.

The specific steps are as follows:
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

Other operations on 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 switch means:
-d, --no-restore-disk-objects
Dont restore disk objects (tablespace/logfilegroups etc)
Both table Spaces and grouping Spaces are ignored


Related articles: