Оглавление
Несколько скриптов архивации баз в 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}
Загрузка...