I'm relatively new to database driven sites. In order to backup a database I use phpMyAdmin's Export function from the server and download the output to my computer. That works, but it requires me to do this on a regular basis. As I build more database driven sites, the process is going to become cumbersome and tedious. I wonder if there is a better method, possibly something automated and scheduled?

  • This site is for programming questions, not database management. But, yes, there's many ways to schedule a backup of MySQL. Google for 'mysqldump'. – Marc B Jul 8 '15 at 22:03
  • How much data? How valuable? What are your options for access? What security requirements? How frequent? What is your recovery plan? Are the backups used for other purposes? Not nearly enough information to advise. – symcbean Jul 8 '15 at 22:37

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 >$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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy