☰

用于备份服务器上每个数据库但省略系统数据库的脚本

tag-icon • tag-icon bash shell-script scripting cron backup
用于备份服务器上每个数据库但省略系统数据库的脚本

我读过很多关于这个主题的文章，但我没有找到适合我需要的文章。所以，基本上我想为 MySQL 服务器上的每个数据库进行两次备份：一个在中午（中午 12 点），另一个在午夜（上午 12 点），但我想忽略系统数据库：mysql并且information_schema（据我所知还有其他吗请告诉我）。阅读了很多主题后，我得到了这个 bash 脚本：

#!/bin/sh
now="$(date +'%d_%m_%Y_%H_%M_%S')"
filename="db_backup_$now".gz
backupfolder="/home/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=userbackup --password=***** --default-character-set=utf8 database | gzip > "$fullpathbackupfile"
echo "mysqldump finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
find "$backupfolder" -name db_backup_* -mtime +7 -exec rm {} \;
echo "old files deleted" >> "$logfile"
echo "operation finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
echo "*****************" >> "$logfile"
exit 0

该脚本对数据库进行了备份database并保留最后 7 个.tar.gz文件。任何人都可以帮助我改进这个脚本，以便我可以备份除系统数据库之外的每个数据库，并为每个数据库保留最后 7 个副本吗？

答案1

我目前正在使用PostgreSQL，并且我所做的事情看起来非常接近您想要实现的目标，所以这是我的备份脚本：

#!/bin/bash
#
#------------------------------------------------------------------------------
# Editable parameters:
#
## Filesystem Location to place backups.
BACKUP_DIR="/path/to/backup/folder"
## The user used to connect to postgres instance
USER="postgres"
PWD="pwd_in_plaintext_is_not_a_"
## Just the date string that will be appended to the backup files name
BACKUP_DATE="$(date +'%d_%m_%Y_%H_%M_%S')"
## Numbers of days you want to keep copie of your databases
NUMBER_OF_DAYS=7
## Uncomment following line if you want to overwrite the whole folder each time
#rm -rf ${BACKUP_DIR}/backupFulldb-*
#
#------------------------------------------------------------------------------
# don't change anything below this line

# Vacumm al databases before begin to backup
vacuumdb --all -U ${USER}

DATABASES=`psql -U ${USER} -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'`
for i in ${DATABASES}; do
  if [ "$i" != "template0" ] && [ "$i" != "template1" ]; then
    echo Dumping $i to ${BACKUP_DIR}
    pg_dump -U ${USER} --column-inserts $i | gzip -c >  ${BACKUP_DIR}/backupFulldb-$i-${BACKUP_DATE}.out.gz
  fi
done
find ${BACKUP_DIR} -type f -prune -mtime +${NUMBER_OF_DAYS} -exec rm -f {} \;

您只需要一个列出 mysql 实例中所有数据库的查询，并将其替换为数组DATABASES。阅读这个帖子和这个，我想你可以，例如。执行如下操作：

while read line
do 
    DATABASES+=("$line")
done < <(mysql -u${USER} -p${PWD} INFORMATION_SCHEMA -e "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA")

当然，修复您想要排除的数据库名称：

if [ "$i" != "mysql" ] && [ "$i" != "information_schema" ]; then

相关内容