Automatic backup of shell script for MySQL database

  • 2021-07-10 21:01:51
  • OfStack

Automatic backup of shell script for MySQL database

It is a good habit to back up the database frequently. Although the probability of database damage or data loss is very low, it is useless to regret this once this happens. 1 general website or application background has the function button of backup database, but it needs to be executed manually. We need a safe and automatic backup method every day. The following shell script lets you back up the MySQL database every day by setting Crontab.


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

With the above script, we can export one sql backup file every day, and the name of the file is generated according to the date of the day. Over time, many of these files will be generated, so it is necessary to delete some old backup files regularly. The following command does this task, and you can add it to the script above.


#  Delete 30 Back up the file days ago 
 find $backup_path/* -mtime +30 -exec rm {} \;

I have encountered a problem when using the above script. Crontab regularly executes the script export without reporting an error, but the export is an empty SQL file, but logging in to the console and manually executing the script is a successful backup. Later, it was found that Crontab execution script lacked system environment information and could not find mysqldump. The correction method was to use mysqldump full path. The reason why there is no error message is that mysqldump outputs the error message to stderr. End the command with "2 > & 1 "So that a message redirect command can see the error message:


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

Thank you for reading, hope to help everyone, thank you for your support to this site!


Related articles: