用于数据库备份的 Linux shell 脚本

Linux shell script for database backup

提问by trante

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/”文件夹，将其 chmod 更改为 755，但它没有运行。至少我写了我的伪代码。

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.

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

• 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>&1will be added.

• 获取当前日期并创建一个变量， 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 $("date")
• 将文件所有者（chown）从 root 更改为“my_user”。因为我想从“my_user”FTP 帐户打开备份和日志文件。
• 我不想在每个 cron 之后收到一封电子邮件。>/dev/null 2>&1将被添加。

采纳答案by trante

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

Then copy following lines into file with Shift+Ins

然后使用 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

Edit:
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:

编辑：
如果您使用 InnoDB 并且备份花费太多时间，您可以添加“ single-transaction”参数以防止锁定。所以 mysqldump 行将是这样的：

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

回答by Peter Party Bus

Create a script similar to this:

创建一个类似这样的脚本：

#!/bin/sh -e

location=~/`date +%Y%m%d_%H%M%S`.db

mysqldump -u root --password=<your password> database_name > $location

gzip $location

Then you can edit the crontabof the user that the script is going to run as:

然后你可以编辑crontab脚本运行的用户名：

$> crontab -e

And append the entry

并附加条目

01 * * * * ~/script_path.sh

This will make it run on the first minute of every hour every day.

这将使其在每天每小时的第一分钟运行。

Then you just have to add in your rolls and other functionality and you are good to go.

然后你只需要添加你的卷和其他功能，你就可以开始了。

回答by Shal

#!/bin/bash

# Add your backup dir location, password, mysql location and mysqldump        location
DATE=$(date +%d-%m-%Y)
BACKUP_DIR="/var/www/back"
MYSQL_USER="root"
MYSQL_PASSWORD=""
MYSQL='/usr/bin/mysql'
MYSQLDUMP='/usr/bin/mysqldump'
DB='demo'

#to empty the backup directory and delete all previous backups
rm -r $BACKUP_DIR/*  

mysqldump -u root -p'' demo | gzip -9 > $BACKUP_DIR/demo$date_format.sql.$DATE.gz

#changing permissions of directory 
chmod -R 777 $BACKUP_DIR

回答by kartavya soni

#!/bin/sh
#Procedures = For DB Backup
#Scheduled at : Every Day 22:00

v_path=/etc/database_jobs/db_backup
logfile_path=/etc/database_jobs
v_file_name=DB_Production
v_cnt=0

MAILTO="[email protected]"
touch "$logfile_path/kaka_db_log.log"

#DB Backup
mysqldump -uusername -ppassword -h111.111.111.111 ddbname > $v_path/$v_file_name`date +%Y-%m-%d`.sql 
if [ "$?" -eq 0 ]
  then
   v_cnt=`expr $v_cnt + 1`
  mail -s "DB Backup has been done successfully" $MAILTO < $logfile_path/db_log.log
 else
   mail -s "Alert : kaka DB Backup has been failed" $MAILTO < $logfile_path/db_log.log
   exit
fi

回答by Vinod Amarathunga

I got the same issue. But I manage to write a script. Hope this would help.

我遇到了同样的问题。但我设法写了一个脚本。希望这会有所帮助。

#!/bin/bash
# Database credentials
user="username"
password="password"
host="localhost"
db_name="dbname"
# Other options
backup_path="/DB/DB_Backup"
date=$(date +"%d-%b-%Y")
# Set default file permissions
umask 177
# Dump database into SQL file
mysqldump --user=$user --password=$password --host=$host $db_name >$backup_path/$db_name-$date.sql

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


#DB backup log
echo -e "$(date +'%d-%b-%y  %r '):ALERT:Database has been Backuped"    >>/var/log/DB_Backup.log

回答by ggg

You might consider this Open Source tool, matiri, https://github.com/AAFC-MBB/matiriwhich is a concurrent mysql backup script with metadata in Sqlite3. Features:

您可能会考虑这个开源工具 matiri，https://github.com/AAFC-MBB/matiri，它是一个并发 mysql 备份脚本，在 Sqlite3 中带有元数据。特征：

• Multi-Server: Multiple MySQL servers are supported whether they are co-located on the same or separate physical servers.
• Parallel: Each database on the server to be backed up is done separately, in parallel (concurrency settable: default: 3)
• Compressed: Each database backup compressed
• Checksummed: SHA256 of each compressed backup file stored and the archive of all files
• Archived: All database backups tar'ed together into single file
• Recorded: Backup information stored in Sqlite3 database

• 多服务器：支持多个 MySQL 服务器，无论它们位于同一台还是单独的物理服务器上。
• Parallel：要备份的服务器上的每个数据库都单独进行，并行进行（可设置的并发性：默认值：3）
• 压缩：每个数据库备份都被压缩
• 校验和：存储的每个压缩备份文件的 SHA256 和所有文件的存档
• 存档：所有数据库备份都打包成一个文件
• 记录：Sqlite3数据库中存储的备份信息

Full disclosure: original matiri author.

完全披露：原始 matiri 作者。

回答by Baljeet Bhinder

Here is my mysql backup script for ubuntu in case it helps someone.

这是我用于 ubuntu 的 mysql 备份脚本，以防它对某人有所帮助。

#Mysql back up script

start_time="$(date -u +%s)"

now(){
date +%d-%B-%Y_%H-%M-%S
}

ip(){
/sbin/ifconfig eth0 2>/dev/null|awk '/inet addr:/ {print }'|sed 's/addr://'
}

filename="`now`".zip
backupfolder=/path/to/any/folder
fullpathbackupfile=$backupfolder/$filename
db_user=xxx
db_password=xxx
db_name=xxx

printf "\n\n"
printf "******************************\n"
printf "Started Automatic Mysql Backup\n"
printf "******************************\n"
printf "TIME: `now`\n"
printf "IP_ADDRESS: `ip` \n"
printf "DB_SERVER_NAME: DB-SERVER-1\n"

printf "%sBACKUP_FILE_PATH $fullpathbackupfile\n"

printf "Starting Mysql Dump \n"

mysqldump -u $db_user -p$db_password $db_name| pv | zip > $fullpathbackupfile

end_time="$(date -u +%s)"

elapsed=$(($end_time-$start_time))

printf "%sMysql Dump Completed In $elapsed seconds\n"

printf "******************************\n"

PS: Rememember to install pv and zip in your ubuntu

PS：记得在你的ubuntu中安装pv和zip

sudo apt install pv
sudo apt install zip

Here is how I set crontab by using crontab -ein ubuntu to run every 6 hours

这是我通过crontab -e在 ubuntu 中使用每 6 小时运行一次来设置 crontab 的方法

0 */6 * * * sh /path/to/shfile/backup-mysql.sh >> /path/to/logs/backup-mysql.log 2>&1

Cool thing is it will create a zip file which is easier to unzip from anywhere

很酷的事情是它会创建一个 zip 文件，更容易从任何地方解压缩

回答by Rosel

As a DBA, You must schedule the backup of MySQL Database in case of any issues so that you can recover your databases from the current backup.

作为 DBA，您必须安排 MySQL 数据库的备份以防出现任何问题，以便您可以从当前备份中恢复您的数据库。

Here, we are using mysqldump to take the backup of mysql databases and the same you can put into the script.

在这里，我们使用 mysqldump 来备份 mysql 数据库，您可以将其放入脚本中。

[orahow@oradbdb DB_Backup]$ cat .backup_script.sh

[orahow@oradbdb DB_Backup]$ cat .backup_script.sh

#!/bin/bash
# Database credentials
user="root"
password="1Loginxx"
db_name="orahowdb"
v_cnt=0
logfile_path=/DB_Backup
touch "$logfile_path/orahowdb_backup.log"
# Other options
backup_path="/DB_Backup"
date=$(date +"%d-%b-%Y-%H-%M-%p")
# Set default file permissions

Continue Reading .... MySQL Backup

继续阅读 .... MySQL 备份

回答by Vijay

I have prepared a Shell Script to create a Backup of MYSQL database. You can use it so that we have backup of our database(s).

我准备了一个 Shell 脚本来创建 MYSQL 数据库的备份。您可以使用它来备份我们的数据库。

    #!/bin/bash
    export PATH=/bin:/usr/bin:/usr/local/bin
    TODAY=`date +"%d%b%Y_%I:%M:%S%p"`

    ################################################################
    ################## Update below values  ########################
    DB_BACKUP_PATH='/backup/dbbackup'
    MYSQL_HOST='localhost'
    MYSQL_PORT='3306'
    MYSQL_USER='auriga'
    MYSQL_PASSWORD='auriga@123'
    DATABASE_NAME=( Project_O2 o2)
    BACKUP_RETAIN_DAYS=30   ## Number of days to keep local backup copy; Enable script code in end of th script

    #################################################################
    { mkdir -p ${DB_BACKUP_PATH}/${TODAY}
        echo "
                                ${TODAY}" >> ${DB_BACKUP_PATH}/Backup-Report.txt
    } || {
        echo "Can not make Directry"
        echo "Possibly Path is wrong"
    }
    { if ! mysql -u ${MYSQL_USER} -p${MYSQL_PASSWORD} -e 'exit'; then
        echo 'Failed! You may have Incorrect PASSWORD/USER ' >> ${DB_BACKUP_PATH}/Backup-Report.txt
        exit 1
    fi

        for DB in "${DATABASE_NAME[@]}"; do
            if ! mysql -u ${MYSQL_USER} -p${MYSQL_PASSWORD} -e "use "${DB}; then
                echo "Failed! Database ${DB} Not Found on ${TODAY}" >> ${DB_BACKUP_PATH}/Backup-Report.txt

            else
                # echo "Backup started for database - ${DB}"            
                # mysqldump -h localhost -P 3306 -u auriga -pauriga@123 Project_O2      # use gzip..

                mysqldump -h ${MYSQL_HOST} -P ${MYSQL_PORT} -u ${MYSQL_USER} -p${MYSQL_PASSWORD} \
                          --databases ${DB} | gzip > ${DB_BACKUP_PATH}/${TODAY}/${DB}-${TODAY}.sql.gz

                if [ $? -eq 0 ]; then
                    touch ${DB_BACKUP_PATH}/Backup-Report.txt
                    echo "successfully backed-up of ${DB} on ${TODAY}" >> ${DB_BACKUP_PATH}/Backup-Report.txt
                    # echo "Database backup successfully completed"

                else
                    touch ${DB_BACKUP_PATH}/Backup-Report.txt
                    echo "Failed to backup of ${DB} on ${TODAY}" >> ${DB_BACKUP_PATH}/Backup-Report.txt
                    # echo "Error found during backup"
                    exit 1
                fi
            fi
        done
    } || {
        echo "Failed during backup"
        echo "Failed to backup on ${TODAY}" >> ${DB_BACKUP_PATH}/Backup-Report.txt
        # ./myshellsc.sh 2> ${DB_BACKUP_PATH}/Backup-Report.txt
    }

    ##### Remove backups older than {BACKUP_RETAIN_DAYS} days  #####

    # DBDELDATE=`date +"%d%b%Y" --date="${BACKUP_RETAIN_DAYS} days ago"`

    # if [ ! -z ${DB_BACKUP_PATH} ]; then
    #       cd ${DB_BACKUP_PATH}
    #       if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then
    #             rm -rf ${DBDELDATE}
    #       fi
    # fi

    ### End of script ####

In the script we just need to give our Username, Password, Name of Database(or Databases if more than one)also Port number if Different.

在脚本中，我们只需要提供我们的用户名、密码、数据库名称（或数据库，如果有多个）以及端口号（如果不同）。

To Run the script use Command as:

要运行脚本，请使用命令作为：

sudo ./script.sc

I also Suggest that if You want to see the Result in a file Like: Failure Occurs or Successful in backing-up, then Use the Command as Below:

我还建议，如果您想在文件中查看结果，例如：备份失败或成功，请使用以下命令：

sudo ./myshellsc.sh 2>> Backup-Report.log

Thank You.

谢谢你。

回答by Yashan Sachinthaka

Now, copy the following content in a script file (like: /backup/mysql-backup.sh) and save on your Linux system.

现在，将以下内容复制到脚本文件中（例如：/backup/mysql-backup.sh）并保存在您的 Linux 系统上。

    #!/bin/bash

    export PATH=/bin:/usr/bin:/usr/local/bin
    TODAY=`date +"%d%b%Y"`

    DB_BACKUP_PATH='/backup/dbbackup'
    MYSQL_HOST='localhost'
    MYSQL_PORT='3306'
    MYSQL_USER='root'
    MYSQL_PASSWORD='mysecret'
    DATABASE_NAME='mydb'
    BACKUP_RETAIN_DAYS=30   

    mkdir -p ${DB_BACKUP_PATH}/${TODAY}
    echo "Backup started for database - ${DATABASE_NAME}"

    mysqldump -h ${MYSQL_HOST} \
   -P ${MYSQL_PORT} \
   -u ${MYSQL_USER} \
   -p${MYSQL_PASSWORD} \
   ${DATABASE_NAME} | gzip > ${DB_BACKUP_PATH}/${TODAY}/${DATABASE_NAME}-${TODAY}.sql.gz

if [ $? -eq 0 ]; then
  echo "Database backup successfully completed"
else
  echo "Error found during backup"
  exit 1
fi


##### Remove backups older than {BACKUP_RETAIN_DAYS} days  #####

DBDELDATE=`date +"%d%b%Y" --date="${BACKUP_RETAIN_DAYS} days ago"`

if [ ! -z ${DB_BACKUP_PATH} ]; then
      cd ${DB_BACKUP_PATH}
      if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then
            rm -rf ${DBDELDATE}
      fi
fi

After creating or downloading script make sure to set execute permission to run properly.

创建或下载脚本后，请确保设置执行权限以正常运行。

$ chmod +x /backup/mysql-backup.sh

Edit crontab on your system with crontab -e command. Add following settings to enable backup at 3 in the morning.

使用 crontab -e 命令在您的系统上编辑 crontab。添加以下设置以在凌晨 3 点启用备份。

0 3 * * * root /backup/mysql-backup.sh