A single InnoDB table has been dropped by accident. You are unable to use an additional intermediate MySQL instance to restore the table. Which two backup methods can be used to restore the single…

The correct answer is A. a backup created with mysqldump --all-databases D. a file system-level snapshot. A (mysqldump) works because it produces a logical backup (SQL statements). You can extract just the dropped table's CREATE TABLE and INSERT statements from the dump file using standard text tools, then replay them against the live running instance - no downtime or intermediate…

Backup and Recovery

Question

A single InnoDB table has been dropped by accident. You are unable to use an additional intermediate MySQL instance to restore the table. Which two backup methods can be used to restore the single table without stopping the MySQL instance?

Options

Aa backup created with mysqldump --all-databases
Ba backup created using FLUSH TABLES ... FOR EXPORT
Can up-to-date replication slave
Da file system-level snapshot
Ea file system copy created while MySQL was shut down.

How the community answered

(37 responses)

A
84% (31)
B
5% (2)
C
8% (3)
E
3% (1)

Explanation

A (mysqldump) works because it produces a logical backup (SQL statements). You can extract just the dropped table's CREATE TABLE and INSERT statements from the dump file using standard text tools, then replay them against the live running instance - no downtime or intermediate server required.

D (filesystem snapshot) works because you can mount the snapshot, locate the table's .ibd file, then use InnoDB's online transportable tablespace feature (ALTER TABLE ... DISCARD TABLESPACE / IMPORT TABLESPACE) to restore the single table into the running instance without stopping MySQL.

B (FLUSH TABLES FOR EXPORT) is wrong because restoring from a transportable tablespace backup in this context effectively requires an intermediate MySQL instance to properly stage and validate the import - the exact constraint the question rules out.

C (replication slave) is wrong because a slave is an intermediate MySQL instance, which is explicitly excluded by the question's constraints.

E (cold filesystem copy) is wrong because files copied during a MySQL shutdown lack the .cfg metadata file generated by FLUSH TABLES FOR EXPORT; without it, you cannot perform a reliable online IMPORT TABLESPACE, meaning you'd have to stop the running instance to do a raw file replacement.

Memory tip: Think "L-S" - Logical (mysqldump) and Snapshot (filesystem) are the two methods that let you restore a single table surgically into a live server. Physical cold copies and tablespace exports need either a stopped server or a helper instance.

Topics

#backup methods#table restoration#InnoDB recovery#online backups

Community Discussion

No community discussion yet for this question.

Full 1Z0-888 Practice