MySQL database shell script automatic backup, mysqlshell script

MySQL database shell script automatic backup, mysqlshell script

Automatic Backup of shell scripts in MySQL database

It is a good habit to back up databases frequently. Although the probability of database corruption or data loss is very low, it is useless to regret such a situation. Generally, the back-end of a website or application has a button to back up the database, but it needs to be executed manually. We need a secure, daily automatic backup method. The following shell script allows you to back up the MySQL database every day by setting Crontab.

#! /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 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 an SQL backup file every day. The file name is generated based on the date of the day. Over time, such files will generate a lot. It is necessary to delete some old backup files on a regular basis. The following command is used for this task. You can add it to the end of the script above.

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

I encountered a problem when using the above script. Crontab timed execution script export does not report an error, but the exported SQL file is empty, however, you have successfully backed up the script by logging on to the console. It was later found that the Crontab script was executed because the system environment information was missing and mysqldump could not be found. The correct method was to use the full path of mysqldump. The error message is not reported because mysqldump outputs the error message to stderr. After the command is followed by a message redirection command like "2> & 1", the error message is displayed:

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

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!