Shell Scripting | How to automate the MySQL Database Server Individual Database(s) Backup(s)

Hi guys today I'm going to show you the script that I use to take MySQL Database Server Databases Backups individually. First thing you need to do login to Linux and edit a file with anyname like mysqlbackup.sh.
My favorite editor is VIM or vi in linux, once file's edited put the following code in it and make changes according to your needs:

#!/bin/bash

USER="put username here that can take the backup of all or required databases and have enough privileges"
PASSWORD="write your password for the above specified user"
OUTPUTDIR="path to output directory where backups will be placed"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
MYSQLHOST="localhost or ip address of your database server"
MYSQLPORT="3306"
DATE=$(date +"%Y-%m-%d-%H-%I-%S")
EXCLUDE="put the database name here if you want to exclude any from backup"

# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD --port=$MYSQLPORT -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

# dump each database in turn
for db in $databases; do
    echo $db
    $MYSQLDUMP --force --skip-lock-tables --opt --port=$MYSQLPORT --user=$USER --password=$PASSWORD --databases $db | gzip > "$OUTPUTDIR/planet-$db-$DATE.gz"
done

Save the script and you can test it by running it from shell with command like ./mysqlbackup.sh but before you run it you have to make it executable with command chmod +x mysqlbackup.sh then you can run it like ./mysqlbackup.sh

Once script runs successfully and you get your desired results you can put it to cronjobs to run it daily or weekly or monthly as per your requirement.

To put it to cronjob run command:

#crontab -e

then make an entry for your shell script like this:

* * * * * /bin/sh /path/to/mysqlbackup.sh

First star is minutes, second star is hours, and then next ones are days, week days and month etc... That's it. I'll be back with more tutorials. See you later...