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:
~/db-backup.sh#!/bin/zsh # set variable with current date datestring=$(date +"%Y-%m-%d-%H-%M") threads=$(grep "^core id" /proc/cpuinfo | sort -u | wc -l) # calculate 75% percent of threads use_threads="$(($threads * .75))" # floor of result cpus=$(echo $use_threads|cut -f1 -d".") mkdir -p -m 0750 ~/backup/$datestring chown -R mysql:mysql ~/backup/$datestring # creates the data export mysqldump --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 threads cd ~/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/backup echo 'Done.'
Replace <database> with the actual databases’ name you want to export. Replace <user>, <host1>, <host2> and <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.
