Script Bash to Backup Database MySQL Linux

How to write and schedule a Bash Script to Backup MySQL Linux Database and to secure server databases.

What is a Bash script

A Bash script is a text file that contains a set of commands for Unix-based operating systems, such as Linux, written in the Bash scripting language.

Bash scripts are used to automate multiple actions, such as executing system commands, manipulating files, creating backups, managing processes, etc…

Bash scripts can be run directly from the terminal or can be scheduled to run at a specific time using tools such as the Linux crontab.

Create a Bash Script to Backup MySQL Linux Database

As a first step, it will be necessary to create a folder where to put the backups of all databases

cd /var/ww/vhosts/
mkdir backups

Create a .sh file, for example MySqlBackup.sh, in the folder previously created

cd backups
touch MySqlBackup.sh

Edit the file by entering the following bash statements:

#!/bin/bash
 
USER="DB_USERNAME"
PASSWORD="DB_PASSWORD"
OUTPUT="SERVER_FOLDER"
 
rm "$OUTPUT/*gz" > /dev/null 2>&1
 
databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
 
for db in $databases; do
    if [[ "$db" != "information_schema" ]] ; then
        echo "Dumping database: $db"
        mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > $OUTPUT/$db.`date +%Y%m%d`.sql
        gzip $OUTPUT/$db.`date +%Y%m%d`.sql
    fi
done

Where you need to change the following parameters with your own data:

• DB_USERNAME: MySQL user name that must have read permissions on all databases
• DB_PASSWORD: MySQL user password
• SERVER_FOLDER: the folder where to put the backups

Assign appropriate permissions to the .sh file

chmod 700 MySqlBackup.sh

Run the newly created bash script Run the and verify that it works

./MySqlBackup.sh

Creating a cron for the Backup Bash Script

Assign a time forautomatic script execution with crontab

crontab -e
00 02 * * * /SERVER_FOLDER/MySqlBackup.sh