Details of shell script automatic backup of MySQL database

This article mainly introduces the relevant information about the shell script automatic backup of MySQL database. There is a function button to back up the database in the background of the website or application, but it needs to be executed manually. We need a safe method of automatic daily backup. Friends who need it can refer to

MySQL database shell script automatic backup

frequently It is a good habit to back up your database. Although the probability of database corruption or data loss is low, once it happens, there is no use regretting it. Generally, there is a function button to back up the database in the background of a website or application, but it needs to be executed manually. We need a secure, automated daily backup method. The following shell script allows you to back up your MySQL database every day by setting Crontab.

#!/bin/bash
# 数据库认证
 user=""
 password=""
 host=""
 db_name=""
# 其它
 backup_path="/path/to/your/home/_backup/mysql"
 date=$(date +"%d-%b-%Y")
# 设置导出文件的缺省权限
 umask 177
# Dump数据库到SQL文件
 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 name of the file is generated based on the date of the day. Over time, a lot of such files will be generated, and it is necessary to delete some old backup files regularly. The following line of command does this task. You can add it after the above script.

# 删除30天之前的就备份文件
 find $backup_path/* -mtime +30 -exec rm {} \;

I once encountered a problem when using the above script. Crontab regularly executed the script to export without an error, but the export was an empty SQL file. However, logging into the console and manually executing the script resulted in a successful backup. of. Later, it was discovered 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 because mysqldump outputs the error message to stderr. If you add "2>&1" at the end of the command, you can see the error message with such an information redirection command:

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

The above is the detailed content of Details of shell script automatic backup of MySQL database. For more information, please follow other related articles on the PHP Chinese website!