Mysqldump usage

07.Jul.2010

Use mysqldump option to backup your database quickly. Pay attention how mysqldump has been solved in this snippet. It is a tree step backup since you don’t want to lose functions, and triggers, and save only data, but you still want to use this mysqldump as a backup if you need it.

Therefore, you need to take care of ordering data in your backup so you can import it with no problems.

mysqldump --opt --routines --no-data --skip-triggers  -uuser -ppass dbname > backup-file.sql
mysqldump --opt --no-create-info --skip-triggers  -uuser -ppass dbname >> backup-file.sql
mysqldump --opt --no-create-info --no-data --triggers  -uuser -ppass dbname >> backup-file.sql

Explanation of mysqldump snippet:

By order of commands (in order to have easy import):

  1. Dump structure and procedures
  2. Dump data (triggers have already been applied on this data)
  3. Dump triggers