Backup MySQL to DigitalOcean

I have authored an article on how to create a dead simple backup approach for you project. My approach is for Laravel, but it could easily work for Wordpress as well or any other MySQL database for that instance as it only relies on MySQL and Linux commands.

If you are interested in the article it can be found here https://emil.moe/2019/10/backup-laravel-easily-on-remote-server/

For this post I will share my final script for your convenience.

The script is

# YOUR VARIABLES
key=xxxxxxxxxxxxxxxxxxxx
secret=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
endpoint=xxxxx.digitaloceanspaces.com
bucket=my-unique-name
dbuser=root
dbpassword=secret
dbname=database

# MIGHT NOT NEED EDIT
file=db-backup.sql
dbhost=127.0.0.1

# STATIC VARIABLES
file=$(date "+%Y.%m.%d-%H.%M.%S").${file}
resource="/${bucket}/${file}"
contentType="application/sql"
dateValue=`date -R`
stringToSign="PUT\n\n${contentType}\n${dateValue}\n${resource}"
signature=`/bin/echo -en ${stringToSign} | openssl sha1 -hmac ${secret} -binary | base64`

# DUMP SQL
mysqldump --host=${dbhost} --user=${dbuser} --password=${dbpassword} ${dbname} > ~/${file}

# UPLOAD
curl -X PUT -T ~/${file} \
  -H "Host: ${bucket}.${endpoint}" \
  -H "Date: ${dateValue}" \
  -H "Content-Type: ${contentType}" \
  -H "Authorization: AWS ${key}:${signature}" \
  https://${bucket}.${endpoint}/${file}

# REMOVE FILE AFTER UPLOAD
rm ~/${file}
`

And should be schedules with a cronjob:

* */4 * * * sh ~/backup.sh >/dev/null 2>&1