Inspired from this tutorial by Nick Janetakis: Automatic MySQL / PostgreSQL Backups with a Shell Script and Cron Job
Code inspired from @nickjj/cron-sendy-backup
These scripts may also work on other webhost providers not just DreamHost. Just change the folder names and they way cron is setup.
This branch processes WordPress the wp-config.php file.
For the .env version, checkout the env branch.
For the CodeIgniter 3 version, checkout the ci3 branch.
Download and unzip the code in your local folder.
Modify the following variables in the mysql-backup and mysql-restore files.
dh_user='techxing' # enter your DreamHost SSH username here
dh_web_directory='techxing.net' # enter the your DreamHost web directory here
With this setting, the script will /home/techxing/techxing.net/wp-config.php file.
And will create the backups in /home/techxing/mysql-backup-dreamhost/backups/<WordPress Database>/ folder.
(Optional file left here as reference) Modify the <dreamhost user> in the cron-mysql-backup file.
(Optional file left here as reference) Modify the <dreamhost user> in the cron-mysql-backup-rotate file.
You can increase or decrease the retention period of the files by modifying the +90 (more than 90 days) variable.
Upload the mysql-backup-dreamhost folder to your DreamHost Shared server in the /home/<dreamhost user>/ folder via FTP, SSH, or SCP etc. creating the following directory structure (example):
/home/techxing/mysql-backup-dreamhost
Make sure that mysql-backup-dreamhost is not publicly accessible
You may need to add execute permissions to the files:
$ chmod +x mysql-backup
$ chmod +x mysql-restore
This requires that the DreamHost user must have SSH/Secure shell access enabled: Editing an existing user to become a SHELL user
To test the variables that are extracted from the file ucomment this line before the mysqldump command and execute the file in the command line to display the values: (example) ./mysql-backup or ./mysql-restore
printf "VARS: \n\${db_conf} = '${db_conf}' \n\${db_name} = '${db_name}' \n\${db_user} = '${db_user}' \n\${db_pass} = '${db_pass}' \n\${db_host} = '${db_host}' \nDESTINATION: '${backup_destination}/${db_name}_$(date +%Y%m%d-%H%M%S).sql.gz' \n"; exit;
Values between the single quotes ' are the actual values
Script currently doesn't work as-is with database user with a blank password (eg. localhost).
A work around for this is to set the db_pass variable manually to '' (blank, empty). Example:
db_pass='' #"$(grep -o "define('DB_PASSWORD', '[^']*[^');]'" "${db_conf}" | cut -d "," -f 2 | sed "s/'//g;s/ //g")"
Pro Tip: Test your commands first in the command line before entering them in this cron job and without the delete option.
Go to your Cron Jobs panel.
Add a new Cron Job.
Select the DreamHost user from the Setup section.
Type in a meaningful title for the job ex. Daily DB Backup of techxing.net.
(Optional) Enter an email to notify.
Enter the full path of the mysql-backup in the command to run, (example)
/home/techxing/mysql-backup-dreamhost/mysql-backup
Set to run Daily
Submit the form.
Go to your Cron Jobs panel.
Add a new Cron Job.
Select the DreamHost user from the Setup section.
Type in a meaningful title for the job (example) Cleanup DB Backup more than 90 days of techxing.net.
(Optional) Enter an email to notify.
Enter the full path of the mysql-backup in the command to run, (example)
find /home/techxing/mysql-backup-dreamhost/backups -type f -mtime +90 -delete > /dev/null 2>&1
Set to run Daily
Submit the form.