MySQL backups with mysqldump

The basic method to backup databases is to use the mysqldump utility.

Usage of this utility is as follows:

mysqldump DB_NAME > DB_NAME.sql

This would dump the database DB_NAME to the file DB_NAME.sql with no compression.

Additional Examples

Note: The examples below store the backup in /home/mysqlbackup/. However, this should be adjusted as needed to use the partition with the most disk space available.

To back up all databases into one large compressed archive via SSH (or WHM's Terminal feature) as the root user:

/usr/bin/mkdir -v /home/mysqlbackup/ ; /usr/bin/echo "Generating full MySQL backup..." ; /usr/bin/mysqldump --events --routines --triggers --all-databases | /usr/bin/gzip -9 > /home/mysqlbackup/"$(date +%F_%T)"_mysql_backup.sql.gz ; /usr/bin/echo "Complete."

To individually back up each database into its own compressed archive:

/usr/bin/mkdir -v /home/mysqlbackup/ ; for DB in $(mysql -Be "show databases" | /usr/bin/grep -v 'row\|information_schema\|Database') ; do echo "Generating MySQL backup of $DB" ; /usr/bin/mysqldump --skip-lock-tables --events --routines --triggers ${DB} | /usr/bin/gzip -9 > /home/mysqlbackup/"$(date +%F_%T)"_${DB}.sql.gz ; done; /usr/bin/echo "Complete."