SQL Database Dump

# [SHOW CREATE TABLE|http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html] _table name_
# [SELECT * INTO OUTFILE|http://dev.mysql.com/doc/refman/5.0/en/select.html] _temporary file_
# Write the contents of the temporary file to the end of the dump file
# [UNLOCK TABLES|http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html]

h5.


h5. 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.

h5. 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.