SQL Database Dump
How Does MySQL Dump Work?
A database dump contains the table structure and data in SQL text (ASCII) format. This can be done with any software that can issue SQL queries to the MySQL database to read the data and table structure. Its not uncommon for database administrators to take a periodic database dump (e.g. weekly) even when they might be using a more advanced backup method for more frequent backup. Because of the readable SQL text nature of the database dump they provide peace of mind to the administrator.
How Does MySQL Dump Work?
For each database and table the backup program (e.g. mysqldump) executes:
- LOCK TABLE table name
- SHOW CREATE TABLE table name
- SELECT * INTO OUTFILE temporary file
- Write the contents of the temporary file to the end of the dump file
- UNLOCK TABLES
Advantages
- Corrupted databases can be corrected by looking at the contents of the dump.
- There is good assurance that the raw database files (e.g. MyISAM or InnoDB) are not corrupt as all the data is read and it is read using standard SQL queries. For example if a MyISAM file happen to be corrupt and a different backup method was used instead like mysqlhotcopy there could be corruption in the raw MyISAM file. This corruption could go undetected until a restore was attempted or the data backup was attempted to be used. When a SQL Dump is taken it is known that the data must be in a good state or an error would be generated during the dump process.
Disadvantages
- Requires a Full backup be done every time. There is no way to do an incremental backup with a SQL Dump. This means a backup can be very time consuming especially on larger databases.
- When using MyISAM table type a dump holds a global read lock on all tables blocking writes from other connections for the duration of the full backup. Locking can be optional. If locking is not performed there is no consistency in the backup.
- Restoring only desired databases or tables requiring editing the SQL Dump file before restoring from it. Editing raw database dumps is very time consuming and error prone.
Labels:
None