I’m giving myself some side projects to keep my skills sharp. Well, I’m going to apply for a supervisor position soon and I’m worried about losing my technical skills so I’m giving myself fun tech projects at home. One I’ve been thinking about lately is backing up my blog. I wanted a cronjob script to run on my home machine that downloads the content and a database dump weekly on to my home machine.
There were a couple of gotchas I want to document to help me remember them.
The first was my dump command gave me an error I never seen:
mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces
Newer versions of MySQL require the PROCESS privilege just to dump tablespace metadata, which your WordPress DB user (correctly) doesn’t have. The good news: I don’t need tablespace info at all for a WordPress backup. There’s no need to grant that privilege if I just don’t need it.
Solution: Add the --no-tablespaces flag to the mysqldump line in the script:
ssh "$VPS" "mysqldump --single-transaction --no-tablespaces wordpress | gzip" \
> "$DEST/$STAMP/db-$STAMP.sql.gz"The next gotcha mistake I made is because I never really understood ssh-agents. And I really need to look into how that works. It’s still a mystery to me. But from what I understand now, my Ubuntu desktop is using it and crontab does not have access to it. So from crontabs perspective, I had no ssh key.
The solution was to create a dedicated passphrase-less key just for this job, and authorize it on the VPS:
ssh-keygen -t ed25519 -f ~/.ssh/blog-backup -N ""
ssh-copy-id -i ~/.ssh/blog-backup.pub username@ip-addressThen my script needed to tell the remote commands to use the key:
ssh -i "$HOME/.ssh/blog-backup"