Menu

Backup Multiple MySQL Or MariaDB Databases Automatically
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 have three MySQL databases on our linux server named db-hackthesec-site, db-hackthesec-blog, db-hackthesec-app.

Log on to your MySQL or MariaDB database and run below query to create a database user db_user_backups to handle backups
GRANT LOCK TABLES, SELECT ON *.*, SHOW VIEW ON *.*, 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-hackthesec-site' 'db-hackthesec-blog' 'db-hackthesec-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
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
www.hackthesec.co.in

About Author:


I am a Linux Administrator and Security Expert with this site i can help lot's of people about linux knowladge and as per security expert i also intersted about hacking related news.TwitterFacebook

Next
Newer Post
Previous
Older Post

0 comments:

Post a comment

 
Top