Mysqlndb cluster backup database and restore database methods Mysql Cluster
BitsCN.com
At noon, I understood the backup and recovery of the MYSQL Cluster.
Write it down. you don't have to waste time on this issue.
1. back up data 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.
2 NDB Cluster node (s) have shutdown.
Disconnecting to allow management server to shutdown.
Ndb_mgm> exit
2. delete SQL node data.
Drop database TEST_CLUSTER;
3. disable the MYSQLD server.
[Root @ localhost bin] # service mysqld stop
Shutting down MySQL... SUCCESS!
4. 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 this -- initial option is not included, the restoration will fail.
[Root @ localhost bin] # service mysqld start
Starting MySQL SUCCESS!
4. perform restoration on the NDBD node. (Each node has to be executed once because the data is distributed across two nodes)
First node:
[Root @ localhost BACKUP] #/usr/local/mysql/bin/ndb_restore-n3-b4-r-m -- backup_path =/usr/local/mysql/data/BACKUP -4/
The-r switch is a set of records.
-M is metadata. It is the SCHEMA of the table and database.
Nodeid = 3
Backup Id = 4
Backup path =/usr/local/mysql/data/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 -4/
Nodeid = 4
Backup Id = 4
Backup path =/usr/local/mysql/data/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
This is done.
5. check whether there is any data. for the sake of security.
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Test |
+ -------------------- +
3 rows in set (0.00 sec)
Database not recovered?
MYSQL must now create a SCHEMA again.
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)
However, the MYSQL backup program can only perform full backup.
[Root @ localhost BACKUP] # du-h
76 K./BACKUP-2
96 K./BACKUP-6
180 K./BACKUP-4
172 K./BACKUP-3
76 K./BACKUP-1
60 K./BACKUP-5
668 K.
6. pay attention to this issue when restoring the NDBD node.
Because the undo and data files (that is, the table data saved to the disk) are not automatically deleted when the NDBD node is started in the -- initial mode, you must manually perform the RM operation on each NDBD node:
[Root @ node239 ndb_6_fs] # rm-rf *. dat
Then start the backup.
-M should be added during backup on the MASTER.
Add-d to SLAVE and do not enable-m.
The procedure is 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 -1/
Nodeid = 3
Backup Id = 1
Backup path =/usr/local/mysql/data/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 -1/
Nodeid = 6
Backup Id = 1
Backup path =/usr/local/mysql/data/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)
Ignore tablespace and group space bitsCN.com
