We require zsh (to run the Bash script) and pigz to compress the .sql file, so install them with
apt install zsh pigz.
Now we create the new bash script:
# set variable with current datedatestring=$(date +"%Y-%m-%d-%H-%M")threads=$(grep "^core id" /proc/cpuinfo | sort -u | wc -l)# calculate 75% percent of threadsuse_threads="$(($threads * .75))"# floor of resultcpus=$(echo $use_threads|cut -f1 -d".")mkdir -p -m 0750 ~/backup/$datestringchown -R mysql:mysql ~/backup/$datestring# creates the data exportmysqldump --host='localhost' --extended-insert --quick --no-create-info <database> > ~/backup/$datestring/$datestring.sql# compresses the original .sql file using a bit less than 75% of available CPU threadscd ~/backup/$datestring && /usr/bin/pigz -4 -p$cpus ./$datestring.sql# Remove backups and all sql file older than 3 days/usr/bin/find ~/backup -mindepth 1 -mtime +3 -delete# Make three copies of your backup (3-2-1 backup strategy)/usr/bin/scp ~/backup/$datestring/$datestring.sql.gz <user>@<host1>:/path/to/backup/usr/bin/scp ~/backup/$datestring/$datestring.sql.gz <user>@<host2>:/path/to/backup/usr/bin/scp ~/backup/$datestring/$datestring.sql.gz <user>@<host3>:/path/to/backupecho 'Done.'
<database> with the actual databases’ name you want to export. Replace
<host3> with real login credentials of some of your servers.
A backup is only secure if you have several copies of it. The idea here is to distribute and duplicate the backup file.
Now let’s add a cronjob, so the backup runs every night at 3 am:
0 3 * * * /usr/bin/zsh ~/db-backup.sh
Note, that we run this with zsh not with sh or bash, because mathematical functions aren’t available in those by default (depending on your distro).
Also, you should download the backup to your local computer, in order to being able to access it, in case you don’t have three servers to distribute the file to, or to allow offline-access to the backup. I don’t want to go into discussing the 3-2-1 backup strategy here, but let me add, that it is debatable if it’s a good or sufficient approach.