Protecting the data.
WHAT THE METHODS ARE
Logical dumps, producing statements to recreate the data Physical copies of the data files Snapshots at the storage layer
WHAT LOGICAL DUMPS PROVIDE
Portability between versions and servers Selective restore of one table Readable output
WHAT THEY COST
Time, on large databases, in both directions.
WHAT PHYSICAL BACKUPS PROVIDE
Speed, on large databases.
WHAT THEY REQUIRE
A tool that understands the engine, or a consistent snapshot.
WHAT TO NEVER DO
Copy the data directory of a running database.
WHY
The files are mid-write, and the copy is usually unusable.
WHAT OPTION MAKES A DUMP CONSISTENT WITHOUT LOCKING
A single transaction, on a transactional engine.
WHAT TO INCLUDE
Routines, triggers and events, which are omitted by default in some tools.
WHY THAT CATCHES PEOPLE
A restore that appears complete is missing stored logic.
WHAT TO DO WITH THE OUTPUT
Compress it, which is dramatic on text dumps.
WHERE TO STORE IT
Not on the same machine.
WHAT SCHEDULE TO SET
Frequently enough that the loss between backups is acceptable.
WHAT TO ALSO KEEP
Several generations, over a longer period.
WHY
Corruption discovered late needs an older copy.
