Реализация архивации MySQL

Несколько скриптов архивации баз в MySQL

Первый

cd /home/backup/ && mysql -uappsroot -uUSER -e ‘show databases’ | while read dbname; do mysqldump -uUSER -pPASS –complete-insert “$dbname”|gzip > “$dbname”.sql.gz; done

Второй

#!/bin/bash
DIR=”/path/to/backup”
mkdir -p $DIR
LOG=”/path/to/log.log”
touch $LOG
TIMENAME=date +%d.%m.%Y-%H.%M
db=mysql -u USERNAME -h localhost -pPASSWORD -Bse 'show databases'
for n in $db; do
TIMEDUMP=date '+%T %x'
echo “backup has been done at $TIMEDUMP : $TIMENAME on db: $n” >> $LOG
mysqldump -u USERNAME -h localhost -pPASSWORD $n | gzip -c > “$DIR/mysql-$n-$TIMENAME-db.dump.gz”

Третий

#!/bin/bash

MyUSER=”dbuser”
MyPASS=”dbpass”
MyHOST=”localhost”

MYSQL=”$(which mysql)”
MYSQLDUMP=”$(which mysqldump)”

#MYSQL=”/usr/local/bin/mysql”
#MYSQLDUMP=”/usr/local/bin/mysqldump”

CHOWN=”$(which chown)”
CHMOD=”$(which chmod)”
GZIP=”$(which gzip)”

DEST=”./db_backup”

MBD=”$DEST”
HOST=”$(hostname)”
NOW=”$(date +”%Y%m%d”)”

FILE=””
DBS=””

# DO NOT BACKUP these databases, delemiter SPACE
IGN=”information_schema”

# Get all database list first
DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’)”

for db in $DBS
do

skipdb=-1
if [ “$IGN” != “” ]; then
for i in $IGN
do
[ “$db” == “$i” ] && skipdb=1 || :
done
fi

if [ “$skipdb” == “-1″ ] ; then
MBD=”$DEST/$db”
[ ! -d $MBD ] && mkdir -p $MBD || :
FILE=”$MBD/$NOW.sql.gz”
$MYSQLDUMP –opt -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
FNUM=”$(find $MBD/* | wc -l)”
if [ $FNUM -ge 0 ] ; then
find $MBD/* -type f -mtime 20 -exec rm -rf {} \;
fi
fi

done

Скрипт от iredmail

#!/usr/bin/env bash

# Author: Zhang Huangbin (zhb@iredmail.org)
# Date: 16/09/2007
# Purpose: Backup specified mysql databases with command ‘mysqldump’.
# License: This shell script is part of iRedMail project, released under
# GPL v2.

###########################
# REQUIREMENTS
###########################
#
# * Required commands:
# + mysqldump
# + du
# + bzip2 # If bzip2 is not available, change ‘CMD_COMPRESS’
# # to use ‘gzip’.
#

###########################
# USAGE
###########################
#
# * It stores all backup copies in directory ‘/var/vmail/backup’ by default,
# You can change it in variable $BACKUP_ROOTDIR below.
#
# * Set correct values for below variables:
#
# BACKUP_ROOTDIR
# MYSQL_USER
# MYSQL_PASSWD
# DATABASES
# DB_CHARACTER_SET
#
# * Add crontab job for root user (or whatever user you want):
#
# # crontab -e -u root
# 1 4 * * * bash /path/to/backup_mysql.sh
#
# * Make sure ‘crond’ service is running, and will start automatically when
# system startup:
#
# # —- On RHEL/CentOS —-
# # chkconfig –level 345 crond on
# # /etc/init.d/crond status
#
# # —- On Debian/Ubuntu —-
# # update-rc.d cron defaults
# # /etc/init.d/cron status
#

#########################################################
# Modify below variables to fit your need —-
#########################################################
# Keep backup for how many days. Default is 90 days.
KEEP_DAYS=’90’

# Where to store backup copies.
export BACKUP_ROOTDIR=”/var/vmail/backup”

# MySQL user and password.
export MYSQL_USER=”root”
export MYSQL_PASSWD=”DtbbYWndnFuR4vBdXKusH9WL8w6s5M”

# Databases we should backup.
# Multiple databases MUST be seperated by SPACE.
export DATABASES=”vmail iredadmin mysql amavisd iredapd roundcubemail iredadmin”

# Database character set for ALL databases.
# Note: Currently, it doesn’t support to specify character set for each databases.
export DB_CHARACTER_SET=”utf8″

#########################################################
# You do *NOT* need to modify below lines.
#########################################################
export PATH=’/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/local/sbin’

# Commands.
export CMD_DATE=’/bin/date’
export CMD_DU=’du -sh’
export CMD_COMPRESS=’bzip2 -9′
export COMPRESS_SUFFIX=’bz2′
export CMD_MYSQLDUMP=’mysqldump’
export CMD_MYSQL=’mysql’

# Date.
export YEAR=”$(${CMD_DATE} +%Y)”
export MONTH=”$(${CMD_DATE} +%m)”
export DAY=”$(${CMD_DATE} +%d)”
export TIME=”$(${CMD_DATE} +%H:%M:%S)”
export TIMESTAMP=”${YEAR}-${MONTH}-${DAY}-${TIME}”

# Pre-defined backup status
export BACKUP_SUCCESS=’YES’

# Define, check, create directories.
export BACKUP_DIR=”${BACKUP_ROOTDIR}/mysql/${YEAR}/${MONTH}/${DAY}”

# Find the old backup which should be removed.
export REMOVE_OLD_BACKUP=’NO’
if which python &>/dev/null; then
export REMOVE_OLD_BACKUP=’YES’
py_cmd=”import time; import datetime; t=time.localtime(); print datetime.date(t.tm_year, t.tm_mon, t.tm_mday) – datetime.timedelta(days=${KEEP_DAYS})”
shift_date=$(python -c “${py_cmd}”)
shift_year=”$(echo ${shift_date} | awk -F’-‘ ‘{print $1}’)”
shift_month=”$(echo ${shift_date} | awk -F’-‘ ‘{print $2}’)”
shift_day=”$(echo ${shift_date} | awk -F’-‘ ‘{print $3}’)”
export REMOVED_BACKUP_DIR=”${BACKUP_ROOTDIR}/mysql/${shift_year}/${shift_month}/${shift_day}”
fi

# Log file
export LOGFILE=”${BACKUP_DIR}/${TIMESTAMP}.log”

# Check required variables.
if [ X”${MYSQL_USER}” == X”” -o X”${MYSQL_PASSWD}” == X”” -o X”${DATABASES}” == X”” ]; then
echo “[ERROR] You don’t have correct MySQL related configurations in file: ${0}” 1>&2
echo -e “\t- MYSQL_USER\n\t- DATABASES” 1>&2
echo “Please configure them first.” 1>&2

exit 255
fi

# Verify MySQL connection.
${CMD_MYSQL} -u”${MYSQL_USER}” -p”${MYSQL_PASSWD}” -e “show databases” &>/dev/null
if [ X”$?” != X”0″ ]; then
echo “[ERROR] MySQL username or password is incorrect in file ${0}.” 1>&2
echo “Please fix them first.” 1>&2

exit 255
fi

# Check and create directories.
[ ! -d ${BACKUP_DIR} ] && mkdir -p ${BACKUP_DIR} 2>/dev/null

# Initialize log file.
echo “* Starting backup: ${TIMESTAMP}.” >${LOGFILE}
echo “* Backup directory: ${BACKUP_DIR}.” >>${LOGFILE}

# Backup.
echo “* Backing up databases: ${DATABASES}.” >> ${LOGFILE}
for db in ${DATABASES}; do
#backup_db ${db} >>${LOGFILE}

#if [ X”$?” == X”0″ ]; then
# echo ” – ${db} [DONE]” >> ${LOGFILE}
#else
# [ X”${BACKUP_SUCCESS}” == X’YES’ ] && export BACKUP_SUCCESS=’NO’
#fi
output_sql=”${BACKUP_DIR}/${db}-${TIMESTAMP}.sql”

# Check database existence
${CMD_MYSQL} -u”${MYSQL_USER}” -p”${MYSQL_PASSWD}” -e “use ${db}” &>/dev/null

if [ X”$?” == X’0′ ]; then
# Dump
${CMD_MYSQLDUMP} \
-u”${MYSQL_USER}” \
-p”${MYSQL_PASSWD}” \
–events –ignore-table=mysql.event \
–default-character-set=${DB_CHARACTER_SET} \
${db} > ${output_sql}

if [ X”$?” == X’0′ ]; then
# Get original SQL file size
original_size=”$(${CMD_DU} ${output_sql} | awk ‘{print $1}’)”

# Compress
${CMD_COMPRESS} ${output_sql} >>${LOGFILE}

if [ X”$?” == X’0′ ]; then
rm -f ${output_sql} >> ${LOGFILE}
fi

# Get compressed file size
compressed_file_name=”${output_sql}.${COMPRESS_SUFFIX}”
compressed_size=”$(${CMD_DU} ${compressed_file_name} | awk ‘{print $1}’)”

sql_log_msg=”INSERT INTO log (event, loglevel, msg, admin, ip, timestamp) VALUES (‘backup’, ‘info’, ‘Database backup: ${db}. Original file size: ${original_size}, compressed: ${compressed_size}, backup file: ${compressed_file_name}’, ‘cron_backup_sql’, ‘127.0.0.1’, NOW());”
else
# backup failed
export BACKUP_SUCCESS=’NO’
sql_log_msg=”INSERT INTO log (event, loglevel, msg, admin, ip, timestamp) VALUES (‘backup’, ‘info’, ‘Database backup failed: ${db}, check log file ${LOGFILE} for more details.’, ‘cron_backup_sql’, ‘127.0.0.1’, NOW());”
fi

# Log to SQL table iredadmin.log, so that global domain admins can
# check backup status (System -> Admin Log)
${CMD_MYSQL} -u”${MYSQL_USER}” -p”${MYSQL_PASSWD}” iredadmin -e “${sql_log_msg}”
fi
done

# Append file size of backup files.
echo -e “* File size:\n—-” >>${LOGFILE}
cd ${BACKUP_DIR} && \
${CMD_DU} *${TIMESTAMP}*sql* >>${LOGFILE}
echo “—-” >>${LOGFILE}

echo “* Backup completed (Success? ${BACKUP_SUCCESS}).” >>${LOGFILE}

if [ X”${BACKUP_SUCCESS}” == X’YES’ ]; then
echo “==> Backup completed successfully.”
else
echo -e “==> Backup completed with !!!ERRORS!!!.\n” 1>&2
fi

if [ X”${REMOVE_OLD_BACKUP}” == X’YES’ -a -d ${REMOVED_BACKUP_DIR} ]; then
echo -e “* Delete old backup: ${REMOVED_BACKUP_DIR}.” >> ${LOGFILE}
echo -e “* Suppose to delete: ${REMOVED_BACKUP_DIR}” >> ${LOGFILE}
rm -rf ${REMOVED_BACKUP_DIR} >> ${LOGFILE} 2>&1

sql_log_msg=”INSERT INTO log (event, loglevel, msg, admin, ip, timestamp) VALUES (‘backup’, ‘info’, ‘Remove old backup: ${REMOVED_BACKUP_DIR}.’, ‘cron_backup_sql’, ‘127.0.0.1’, NOW());”
${CMD_MYSQL} -u”${MYSQL_USER}” -p”${MYSQL_PASSWD}” iredadmin -e “${sql_log_msg}”
fi

echo “==> Detailed log (${LOGFILE}):”
echo “=========================”
cat ${LOGFILE}

 

Просмотров: 31

Звёзд: 1Звёзд: 2Звёзд: 3Звёзд: 4Звёзд: 5 (Пока оценок нет)
Загрузка...

Оставить комментарий

Пожалуйста, авторизуйтесь чтобы добавить комментарий.
avatar
  Подписаться  
Уведомление о