Automatic backup of MySQL database using shell script

Automatic backup of MySQL database using shell script

It is a good habit to back up the database frequently. Although the probability of database damage or data loss is very low, once such a thing happens, it is useless to regret. Generally, there is a function button for backing up the database in the background of a website or application, but it needs to be executed manually. We need a secure way to automatically back up every day. The following shell script is how you can set up Crontab to back up the MySQL database every day.

#!/bin/bash
# Database authentication user=""
 password=""
 host=""
 db_name=""
# Other backup_path="/path/to/your/home/_backup/mysql"
 date=$(date +"%d-%b-%Y")
# Set the default permissions for exported files to umask 177
# Dump database to SQL file mysqldump --user=$user --password=$password --host=$host $db_name > $backup_path/$db_name-$date.sql

Through the above script, we can export a SQL backup file every day, and the file name is generated according to the date of the day. Over time, a lot of such files will be generated. It is necessary to delete some old backup files regularly. The following command line is for this task. You can add it after the above script.

# Delete the backup files older than 30 days find $backup_path/* -mtime +30 -exec rm {} \;

I once encountered a problem when using the above script. There was no error when Crontab executed the script export regularly, but the exported SQL file was empty. However, when I logged into the console and executed the script manually, the backup was successful. Later I found that the Crontab execution script lacked system environment information and could not find mysqldump. The correction method was to use the full path of mysqldump. The reason why there is no error message is that mysqldump outputs the error message to stderr. Add "2>&1" to the end of the command so that you can see the error message:

mysqldump -ujoe -ppassword > /tmp/somefile 2>&1

Thank you for reading, I hope it can help you, thank you for your support of this site!