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
Creating a cron for the Backup Bash Script
Assign a time forautomatic script execution with crontab
crontab -e 00 02 * * * /SERVER_FOLDER/MySqlBackup.sh