Backup a MySQL database -options
For a relatively small database, the mysqldump utility can be an effective way to backup the database.

We use a shell script, execution scheduled thru cron

We run separate mysqldump for each database. We omit the "create database" and "drop table" statements. We also include options to get a "consistent" backup of the InnoDB tables. There's some housekeeping work the script does (such as getting the list of databases to backup, etc.) We run the output from mysqldump straight into a compression routine.

For each database, this is what gets run:

  $MYSQLDUMP -h localhost -u root -pTOPSECRET \
  --insert-ignore --single-transaction --quick --tz-utc \
  --routines --no-create-db --skip-add-drop-table $DB \
  | gzip -c &gt;$BACKUPDIR/backup_$BDT.$DBF.sql.gz


Obviously, some variables are set when this gets executed:


$MYSQLDUMP is the full qualified location of the mysqldump executable
$DB is the name of the database
$BACKUPDIR is the directory, and $BDT and $DBF are parts of the file name


The generated .sql file is suitable for running into an empty database; we test restore by moving the file to a different machine, and do restore a clone of the database (into a new database name.)