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?
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:
$MYSQLDUMPis the full qualified location of the mysqldump executable
$DBis the name of the database
$BACKUPDIRis the directory, and
$DBFare 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.)