Background
Recently my homeassistant local DB (RPI-based), which is an SQLite DB, got corrupted during an upgrade, so this was an incentive to migrate to holding the data in the MySQL server I have running anyways as;
- This is more reliable
- Less writes to the SDcard
But it made me realise that my MySQL server backups weren’t exactly regular, and I have a number of things using the DB (kodi, now homeassistant, website, etc.) that I’d be quite annoyed to lose.
Running backups using mysqldump is a normal way of doing things, but has the problem of recording the password in some way, shape or form. Fortunately to do a backup, only read-type permissions are required.
Backup Account
We create an account on the server, BACKUP_USER, that can only connect from the localhost, that has read-only permissions for all databases and tables
GRANT LOCK TABLES, SELECT, SHOW VIEW ON *.* TO 'BACKUP_USER'@'localhost' IDENTIFIED BY 'secretReadonlyPassword';
FLUSH PRIVILEGES;
Script and Cron Job
Then we have a simple bash script, executed daily/weekly, to trigger the backups, and only keep X number around.
#!/bin/bash
# Set the backup date
BACKUP_DATE=`date +%Y-%m-%d`
BACKUP_DIR=/backup_location/sql_database
BACKUP_HISTORY=20
# Dump the database
mysqldump -u BACKUP_USER -p 'secretReadonlyPassword' --all-databases --add-drop-database | gzip -c > $BACKUP_DIR/dump_$BACKUP_DATE.sql.gz
# Remove all but the latest backups
cd $BACKUP_DIR
ls -t | tail -n +$BACKUP_HISTORY | xargs rm --
