用于数据库备份的 Linux shell 脚本 [英] Linux shell script for database backup

问题描述

我尝试了许多用于数据库备份的脚本，但我无法成功.我想每小时备份一次我的数据库.
我将文件添加到/etc/cron.hourly/"文件夹，将其 chmod 更改为 755，但它没有运行.至少我写了我的伪代码.

I tried many scripts for database backup but I couldn't make it. I want to backup my database every hour.
I added files to "/etc/cron.hourly/" folder, changed its chmod to 755, but it didn't run. At least I write my pseudo code.

如果您能为此操作编写脚本并告诉我我还应该做什么，我会很高兴?将此脚本文件添加到 /etc/cron.hourly/ 文件夹后.

I would be happy if you can write a script for this operation and tell me what should I do more ? After adding this script file to /etc/cron.hourly/ folder.

• 获取当前日期并创建一个变量，date=date(d_m_y_H_M_S)
• 为文件名创建一个变量，filename="$date".gz
• 像这样获取我的数据库的转储 mysqldump --user=my_user --password=my_pass --default-character-set=utf8 my_database |gzip >"/var/www/vhosts/system/example.com/httpdocs/backups/$("filename")
• 删除文件夹 /var/www/vhosts/system/example.com/httpdocs/backups/ 中所有超过 8 天的文件
• 到文件"/var/www/vhosts/system/example.com/httpdocs/backup_log.txt"，这段文字将被写入:Backup is created at $("日期")
• 将文件所有者(chown)从 root 更改为my_user".因为我想从my_user"FTP 帐户打开备份和日志文件.
• 我不想在每个 cron 之后收到一封电子邮件.>/dev/null 2>&1 将被添加.

• Get current date and create a variable, date=date(d_m_y_H_M_S)
• Create a variable for the file name, filename="$date".gz
• Get the dump of my database like this mysqldump --user=my_user --password=my_pass --default-character-set=utf8 my_database | gzip > "/var/www/vhosts/system/example.com/httpdocs/backups/$("filename")
• Delete all files in the folder /var/www/vhosts/system/example.com/httpdocs/backups/ that are older than 8 days
• To the file "/var/www/vhosts/system/example.com/httpdocs/backup_log.txt", this text will be written: Backup is created at $("date")
• Change the file owners (chown) from root to "my_user". Because I want to open the backup and log files from the "my_user" FTP account.
• I don't want an email after each cron. >/dev/null 2>&1 will be added.

推荐答案

经过几个小时的工作，我创建了一个如下所示的解决方案.我为其他可以受益的人复制粘贴.

After hours and hours work, I created a solution like the below. I copy paste for other people that can benefit.

首先创建一个脚本文件并赋予该文件可执行权限.

First create a script file and give this file executable permission.

# cd /etc/cron.daily/
# touch /etc/cron.daily/dbbackup-daily.sh
# chmod 755 /etc/cron.daily/dbbackup-daily.sh
# vi /etc/cron.daily/dbbackup-daily.sh

然后使用 Shift+Ins 将以下几行复制到文件中

Then copy following lines into file with Shift+Ins

#!/bin/sh
now="$(date +'%d_%m_%Y_%H_%M_%S')"
filename="db_backup_$now".gz
backupfolder="/var/www/vhosts/example.com/httpdocs/backups"
fullpathbackupfile="$backupfolder/$filename"
logfile="$backupfolder/"backup_log_"$(date +'%Y_%m')".txt
echo "mysqldump started at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
mysqldump --user=mydbuser --password=mypass --default-character-set=utf8 mydatabase | gzip > "$fullpathbackupfile"
echo "mysqldump finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
chown myuser "$fullpathbackupfile"
chown myuser "$logfile"
echo "file permission changed" >> "$logfile"
find "$backupfolder" -name db_backup_* -mtime +8 -exec rm {} ;
echo "old files deleted" >> "$logfile"
echo "operation finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
echo "*****************" >> "$logfile"
exit 0

如果使用InnoDB，备份时间过长，可以添加"single-transaction" 防止锁定的参数.所以 mysqldump 行会是这样的:

If you use InnoDB and backup takes too much time, you can add "single-transaction" argument to prevent locking. So mysqldump line will be like this:

mysqldump --user=mydbuser --password=mypass --default-character-set=utf8
          --single-transaction mydatabase | gzip > "$fullpathbackupfile"

这篇关于用于数据库备份的 Linux shell 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！