Learn Backup Multiple MySQL Or MariaDB Databases Automatically
Table of Contents
Introduction
In this write-up, we’ll walk through how to backup multiple MySQL or MariaDB databases that sit on the same machine using a custom bash script and setting up a cron job.
Database Backup(s)
We’ll assume we have three MySQL databases on our IT Web Services instance named db-itweb.services-site, db-itweb.services-blog, db-itweb.services-app (Don’t worry about creating these databases, you’ll be able to substitute their names for yours hwere they are used in the script on Step 2 below).
-
Log on to your MySQL or MariaDB database and run below query to create a database user
db_user_backups
to handle backupsGRANT LOCK TABLES, SELECT, SHOW VIEW, REPLICATION CLIENT ON *.* TO 'db_user_backups'@'%' IDENTIFIED BY '{COMPLEX-PASSWORD}';
Also run below to make sure MySQL is configured to properly restore stored procedures
SET GLOBAL log_bin_trust_function_creators = 1;
-
Setup the necessarily directory structure and files needed
# create backup directory with environment and log file sudo mkdir /backups && cd /backups sudo touch .env db-backup.sh db-backup.log sudo chmod -R 775 /backups sudo chmod -R g+s /backups sudo chmod +x db-backup.sh # add mysql backup user credentials into environment file echo "export MYSQL_USER=db_user_backups" > /backups/.env echo "export MYSQL_PASS={COMPLEX-PASSWORD}" >> /backups/.env
-
Open db-backup.sh
nano /backups/db-backup.sh
and paste the code below inside it, then save the file (Ctrl+X -> Y -> hit Enter).DB_NAMES=( 'db-itweb.services-site' 'db-itweb.services-blog' 'db-itweb.services-app' ) #replace with your own database name(s) BKUP_NAMES=() BKUP_DIR="/backups" # get total number of directories total_dbs=${#DB_NAMES[@]} # create backup file names for (( i=0; i<${total_dbs}; i++ )); do BKUP_NAMES[$i]="`date +%Y%m%d%H%M`-backup-$${DB_NAMES[$i]}.sql.gz" done # get backup users credentials source $BKUP_DIR/.env # create backups for (( i=0; i<${total_dbs}; i++ )); do # NOTE: --routines flag makes sure stored procedures are also backed up mysqldump --routines -u ${MYSQL_USER} -p${MYSQL_PASS} | gzip > ${BKUP_DIR}/${BKUP_NAMES[$i]} done
The code above is looping through an array with the name(s) of the database(s) you want to backup and doing so.
Cronjob Setup
Setup a cronjob to run every midnight that runs the backup script and saves the result/output to backup log.
-
Open crontab
crontab -e
-
Add below entry to crontab
0 0 * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log
Note: While testing you can set cronjob to run every 1 minute instead like below
* * * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log
-OR- every 5 minutes (replace 5 with the number of minutes you want)
*/5 * * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log
What’s Next
https://www.itweb.services/tutorials/linux-guides/setup-file-mirroring-using-rsync-in-debian-ubuntu
Need help?
Do you need help setting up this on your own service?
Please contact us and we’ll provide you the best possible quote!