Hey, dolphins! Ready to test your NDB backup and restore skills?
Q1: You have a large database which takes 3 hours to back up. Insert/update/delete traffic will run during the backup. How do you run a backup so that none of the inserts/updates/deletes which are executed after the start of the backup are reflected in the backup files?
Q2: Your backup fileset contains 10 tables, t0 to t9. You wish to restore them to a database which already contains a table named t0. How do you restore all the data minus the contents of t0? Without modifying the database, of course.
Q3: You have the same schema as above, but you do want to restore t0 from the backup to the database. The table definitions are identical except for the datatypes of one column – the backup table has a VARCHAR and the database table has a TEXT. What do you do? Again, without modifying the database.
Q4: You want to start a backup, but you also want to handle read/write traffic while the backup is running. How do you limit the backup parallelism so as to leave the data nodes with ample capacity to handle queries?
Q5. How do you set up your restore to run as fast as possible, if you have the hardware resources to handle a higher load?
Q6: You follow excellent security practices, so you’ve decided to encrypt your backup. How do you set the backup encryption password?
Q7: You have a backup with a table t0 that contains 100 tuples, with ‘id’ column values from 1 to 100. Your database also has an identical table t0, but with ‘id’ values from 1 to 1000. How do you append the backup table t0’s tuples to the database table t0 without overwriting the first 1000 rows?
Q8: You have taken a backup from a 4-node cluster. You want to restore this backup to an 8-node cluster. What extra steps do you need to take to restore this backup so that the tuples in the backup are evenly distributed among the 8 nodes?
Scroll down for answers
. . .
. . .
. . .
. . .
. . .
A1: start backup snapshotstart
|
1
2
3
4
5
6
7
8
9
|
mysql$ ndb_mgm -e 'start backup snapshotstart' Connected to Management Server at: localhost:1186 Waiting for completed, this may take several minutes Node 2: Backup 2 started from node 1 Node 2: Backup 2 started from node 1 completed StartGCP: 3586 StopGCP: 3589 #Records: 3719 #LogRecords: 0 Data: 125640 bytes Log: 0 bytes mysql$ |
Every backup has a startGCP and a stopGCP. The startGCP establishes a point-in-time where the backup has just started, but not yet written any tuples to file. The stopGCP establishes a point-in-time where the backup has finished writing all the data and is ready to report completion. A SNAPSHOTSTART backup will capture the data in the cluster at the startGCP. The default option is SNAPSHOTEND, which captures the data at the stopGCP.
A2: ndb_restore –exclude-tables
|
1
2
3
4
5
6
|
mysql$ ndb_restore --nodeid=2 --backupid=1 --restore-data --exclude- tables=test.t0 --backup-path=./data2/BACKUP/BACKUP-1 Nodeid = 2 Backup Id = 1 backup path = ./data2/BACKUP/BACKUP-1 Excluding tables: test.t0 |
ndb_restore has options to exclude individual tables with –exclude-tables, and also to exclude databases using –exclude-databases. If you prefer to specify tables/databases to include instead, the –include-tables and –include-databases options can be used instead.
A3: ndb_restore –promote-attributes
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql$ ndb_restore --nodeid=2 --backupid=1 --restore-data --promote- attributes --backup-path=./data2/BACKUP/BACKUP-1 Nodeid = 2 Backup Id = 1 ... Column test/def/t0.val val has different type in DB; promotion or lossy type conversion (demotion, signed/unsigned) may be required. Column test/def/t0.val precision is different in the DB Column test/def/t0.val scale is different in the DB Column test/def/t0.val length is different in the DB Column test/def/t0.val ArrayType is different in the DB Column test/def/t0.val Blob version is different in the DB Difference(s) cannot be ignored. Column requires conversion to restore. Data for column test/def/t0.val will be converted from Backup type into DB type. |
ndb_restore can perform type conversions while restoring. These type conversions may be promotions from a smaller data type in the backup to a larger data type in the restored DB, or conversions which can result in data loss, i.e. from a larger data type in the backup to a smaller data type in the restored DB. Promotions can be enabled by adding the –promote-attributes flag to ndb_restore, while data-loss conversions can be enabled by adding –lossy-conversions.
A4: EnableMultithreadedBackup=0 in config.ini
|
1
2
3
4
5
6
7
8
|
mysql$ cat config.ini [ndbd default] NoOfReplicas = 2 DataMemory = 50M MaxNoOfAttributes = 30000 MaxNoOfTables = 1000 EnableMultithreadedBackup = 0 ... |
Backup is normally run in parallel across nodes. Within a node, the backup is multi-threaded (versions 8.0.16 and up). The multi-threading can be switched off in the configuration by setting the configuration parameter EnableMultithreadedBackup=0. Don’t forget to do a rolling restart after setting the parameter, so that all the nodes pick it up!
A5: ndb_restore –parallelism=<higher_value>
|
1
2
|
mysql$ ndb_restore --nodeid=2 --backupid=1 --restore-data --parallelism=1024 --backup-path=./data2/BACKUP/BACKUP-1 |
On 8.0.16 and up, ndb_restore runs a multithreaded restore to restore data.
The –parallelism parameter controls the load placed upon each ndb_restore thread. By default, this is throttled to consume the same resources as a single-threaded restore. You can increase the parallelism by specifying a higher value than the default value 128.
A6: ndb_mgm -e ‘START BACKUP ENCRYPT PASSWORD=”<password>”’
|
1
2
3
4
5
6
7
8
9
|
mysql$ ndb_mgm -e 'start backup encrypt password="my_super_secret_password"' 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: 22 StopGCP: 25 #Records: 8 #LogRecords: 0 Data: 2444 bytes Log: 0 bytes mysql$ |
Backup encryption is supported on 8.0.22 and up. The password can be included in the START BACKUP command, provided the ENCRYPT keyword is used.
A7: ndb_restore –remap-column=test.t0.id:offset:1000
|
1
2
|
mysql$ ndb_restore --nodeid=2 --backupid=1 --restore-data --remap- column=test.t0.id:offset:1000 --backup-path=./data2/BACKUP/BACKUP-1 |
The –remap-column option can be used to specify an offset value. In the above example, the backup tuples with values 1 to 100 will be offset by 1000 so that they are restored as values 1001 to 1100.
A8: None!
The standard restore procedure can be used to restore data from a different-sized cluster. No extra steps needed.
So how many questions did you get right?
6-8: Congratulations, you aced the challenge – your backup/restore knowledge is impressive!
3-5: Well done – check out this deep dive to improve your already solid skills!
http://messagepassing.blogspot.com/2020/11/mysql-cluster-backup-and-restore.html
0-2: That was a valiant effort, don’t give up just yet – try this beginner-friendly introduction to backup and restore!
https://clustertesting.blogspot.com/2021/01/mysql-ndb-cluster-backup-restore-in.html