cPanelResources

Tutorial MySQL Backups With mysqldump

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

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

Code:
/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 backup each database into its own compressed archive, the following can be used:

Code:
/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."
Author
cPanelResources
Views
423
First release
Last update