MySQL backup 2.0
Här kommer en hyggligt upphottad version av mitt tidigare MySQL script där jag nu implementerat en typ av timestamp backup/restore förfarande vilket tillåter dig att välja och importera olika SQL dumpar direkt via mitt script.
För mer info är det bara att köra ./mysqlbackup.sh -h :-)
mysqlbackup.sh [ originalformat]
usage()
{
cat << EOF
usage: $0 options
Author: Tommy Frössman aka. Styrbjorn
Web Site: http://styrbjorn.kladhest.se
Changelog
2011-06-06 First version of the script with the following functions.
* Taking automatic backup of all databases on the server
* Taking backup of a specific database only with the -d flag
* List all available databases in the MySQL Server with the lsdb mode in the -m flag.
* Restore database a specific dump with the -r flag along with a timestamp of the
backup you want with the -t flag.
* List all available backups for a specific database in the backup directory using
-m restore -t lstimestamp along with a database supplied for the -r flag.
* Optional -e flag for selecting a specific character encoding when doing a restore of a database.
This script is built for automatic backup of MySQL databases.
OPTIONS:
-h Show this message
-m Specificy what should be done (-m backup/restore/lsdb) (required)
-d Backup this database only (-d mydatabase) (optional)
-r Restore database (-r mydatabase) (required when using -m restore mode)
-t Show or choose timestamp to restore database from (-t lstimestamp/timestamp) (required when using -m restore mode)
-e Database encoding when restoring (eg. -t utf8) (optional)
EOF
}
MODE=
BACKUP_DB=
RESTORE_DB=
TAKEN_AT=
ENCODING=
ARG_FOUND=
BWLIMIT=250
TIMESTAMP=$(date +"%Y%m%d")
# PATH TO SAVE BACKUPS
BACKUPDIR="/foo/bar/MySQL_backup_backupdir"
# PATH TO SAVE BACKUP-LOGS
BACKUPLOGDIR="/foo/bar/MySQL_backup_logdir"
# HOSTNAME OF THE SERVER TO BACKUP
HOSTNAME="myfoohostbar"
# MYSQL MY.CNF FILE PATH
MYSQL_CNF="/foo/bar.my.cnf"
# AVAILBALE DATABASES
LSDB=`/usr/bin/mysql --defaults-file=${MYSQL_CNF} -Bse "show databases;" | awk '{print $1}' | grep -iv ^Database$|grep -v mysql|grep -v information_schema`
# email subject
SUBJECT="${TODAY} Mysql Dump Failed"
# Email To ?
EMAIL="foo@bar.com"
# Email text/message
EMAILMESSAGE="/tmp/emailmessage.txt"
echo "${TODAY} Mysql Dump Failed ${BACKUPLOGDIR}/${HOSTNAME}.log"> $EMAILMESSAGE
if [[ -z $LSDB ]]
then
echo "Connection to the MySQL Server failed, aborting script!"
echo "${TIMESTAMP} Connection to the MySQL Server failed, aborting script!"> $EMAILMESSAGE
echo "${TIMESTAMP} Connection to the MySQL Server failed, aborting script!" >> ${BACKUPLOGDIR}/${HOSTNAME}.log
/usr/bin/mail -s "${SUBJECT}" "${EMAIL}" < ${EMAILMESSAGE}
exit 0
fi
# Checking which arguments that is passed to the backupscript.
while getopts ":h:m:d:r:t:e:" OPTION
do
ARG_FOUND=1
case $OPTION in
h)
usage
exit 1
;;
m)
MODE=$OPTARG
;;
d)
BACKUP_DB=$OPTARG
;;
r)
RESTORE_DB=$OPTARG
;;
t)
TAKEN_AT=$OPTARG
;;
e)
ENCODING=$OPTARG
;;
?)
usage
exit 1
;;
esac
done
if [[ -z $ARG_FOUND ]]
then
usage
fi
if [[ $MODE == 'backup' ]]
then
# The -m flag has been supplied with backup mode.
if [[ -n $BACKUP_DB ]]
then
# Checking if the specified database supplied with the -d flag exists in the MySQL Server.
for db in $LSDB; do
if [ "$db" = "$BACKUP_DB" ]
then
DB_EXISTS=1
fi
done
if [[ $DB_EXISTS == 1 ]]
then
# The specified database supplied with the -d flag was found in the MySQL Server, continuing with backup.
# Specific database choosen so only that database will be backed up.
echo "Only taking backup of database ${BACKUP_DB}"
echo ${BACKUPDIR}/${TIMESTAMP}/${BACKUP_DB}.sql
mkdir -p ${BACKUPDIR}/${TIMESTAMP}/
mysqldump --defaults-file=${MYSQL_CNF} ${BACKUP_DB} > ${BACKUPDIR}/${TIMESTAMP}/${BACKUP_DB}.sql
if [ "$?" = 0 ];
then
echo "${TIMESTAMP} Mysql Dump for database ${BACKUP_DB} was Successful" >> ${BACKUPLOGDIR}/${HOSTNAME}.log
else
echo "${TIMESTAMP} Mysql Dump for database ${BACKUP_DB}"> $EMAILMESSAGE
echo "${TIMESTAMP} Mysql Dump for database ${BACKUP_DB} Failed" >> ${BACKUPLOGDIR}/${HOSTNAME}.log
/usr/bin/mail -s "${SUBJECT}" "${EMAIL}" < ${EMAILMESSAGE}
fi
else
# The specified database supplied with the -d flag was not found in the MySQL Server, no backup will be made.
echo "Backup of database failed ${BACKUP_DB} !"
echo "The specified database could not be found on the server."
echo "${TIMESTAMP} Mysql Dump for database ${BACKUP_DB} Failed - The specified database could not be found on the server."> $EMAILMESSAGE
echo "${TIMESTAMP} Mysql Dump for database ${BACKUP_DB} Failed - The specified database could not be found on the server." >> ${BACKUPLOGDIR}/${HOSTNAME}.log
/usr/bin/mail -s "${SUBJECT}" "${EMAIL}" < ${EMAILMESSAGE}
fi
else
# No database has been specified in the -d argument so everything will be backed up exept the internal MySQL databases.
echo "Taking backup of all databases on the server"
for db in $LSDB; do
echo ${BACKUPDIR}/${TIMESTAMP}/${db}.sql
mkdir -p ${BACKUPDIR}/${TIMESTAMP}/
mysqldump --defaults-file=${MYSQL_CNF} ${db} > ${BACKUPDIR}/${TIMESTAMP}/${db}.sql
if [ "$?" = 0 ];
then
echo "${TIMESTAMP} Mysql Dump for database ${db} was Successful" >> ${BACKUPLOGDIR}/${HOSTNAME}.log
else
echo "${TIMESTAMP} Mysql Dump for database ${db} Failed"> $EMAILMESSAGE
echo "${TIMESTAMP} Mysql Dump for database ${db} Failed" >> ${BACKUPLOGDIR}/${HOSTNAME}.log
/usr/bin/mail -s "${SUBJECT}" "${EMAIL}" < ${EMAILMESSAGE}
fi
done
fi
fi
if [[ $MODE == 'restore' ]]
then
# The -m flag has been supplied with restore mode.
if [[ -z $RESTORE_DB ]]
then
# No database was choosen with the -r argument when restore mode was enabled, no restore will be made.
echo "database to backup has not been specified"
else
if [[ -z $TAKEN_AT ]]
then
# A timestamp has not been supplied with the -t argument when trying to restore the database, no restore will be made.
echo "Timestamp must be specified"
else
# Checking if the specified database supplied with the -r flag exists in the MySQL Server.
DB_EXISTS=0
for db in $LSDB; do
if [ "$db" = "$RESTORE_DB" ]
then
DB_EXISTS=1
fi
done
if [[ $DB_EXISTS == 1 ]]
then
# The database specified in the -d flag has been found in MySQL, continuing with database restore.
if [[ $TAKEN_AT == "lstimestamp" ]]
then
TIMESTAMP_CHECK=0
# Listing all available backup timestamps for the database specified with the -r flag using the lstimesstamp mode.
for directory in ${BACKUPDIR}/*; do
if [[ -f "${directory}/${RESTORE_DB}.sql" ]]
then
TIMESTAMP_CHECK=1
echo "${directory: -8}"
fi
done
if [[ ${TIMESTAMP_CHECK} == 0 ]]
then
# The database was found in MySQL but no timestamps could be found in the backup directory, database has probably never been backed up.
echo "Could not find any timestamps for database ${RESTORE_DB}!"
echo "This Database has probably never been backed up before."
fi
else
if [[ ! -f "${BACKUPDIR}/${TAKEN_AT}/${RESTORE_DB}.sql" ]]
then
# The database was found in the MySQL Server but the timestamp supplied with the -t was invalid, no restore will be made.
echo "The backup of database ${RESTORE_DB} with timestamp ${TAKEN_AT} could not be found!"
else
if [[ -n $ENCODING ]]
then
# We have supplied the -e argument with a specific encoding so we will restore the database with --default-character-set options.
# Dropping the database that is going to be restored.
echo "* Dropping database"
mysqladmin --defaults-file=${MYSQL_CNF} --force drop ${RESTORE_DB};
# Creating a new empty database to restore the backup to.
echo "* Creating empty database for restore"
mysqladmin --defaults-file=${MYSQL_CNF} --force create ${RESTORE_DB};
# Restoring the database backup to the new database.
echo "Restoring database ${RESTORE_DB} with timestamp ${TAKEN_AT} using ${ENCODING} encoding"
mysql --defaults-file=${MYSQL_CNF} --default-character-set=$ENCODING ${RESTORE_DB} < ${BACKUPDIR}/${TAKEN_AT}/${RESTORE_DB}.sql
else
# No encoding has been supplied with the -e flag we are using the default MySQL encoding.
# Dropping the database that is going to be restored.
echo "* Dropping database"
mysqladmin --defaults-file=${MYSQL_CNF} --force drop ${RESTORE_DB};
# Creating a new empty database to restore the backup to.
echo "* Creating empty database for restore"
mysqladmin --defaults-file=${MYSQL_CNF} --force create ${RESTORE_DB};
# Restoring the database backup to the new database.
echo "Restoring database ${RESTORE_DB} with timestamp ${TAKEN_AT} using standard encoding"
mysql --defaults-file=${MYSQL_CNF} ${RESTORE_DB} < ${BACKUPDIR}/${TAKEN_AT}/${RESTORE_DB}.sql
fi
fi
fi
else
# If the restore process fails this generic error message will be displayed.
echo "The database $RESTORE_DB cannot be restored on this database server!"
echo "There can be several reasons why the restore process failed"
echo "* You are trying to restore an internal service database for MySQL server."
echo "* You are trying to restore a database that does not exist on your system, if you want to migrate"
echo " the database to this server please create an empty database first then try to restore it again."
echo " To create the database in MySQL just type \"mysqladmin -u root -p create $RESTORE_DB\""
fi
fi
fi
fi
if [[ $MODE == 'lsdb' ]]
# The -m flag has been supplied with lsdb mode.
then
echo "Available MySQL databases on host ${HOSTNAME}"
echo "****************************************************"
# Listing all available databases on the MySQL server.
echo $LSDB|tr ' ' '\n'
fi
exit 0
För att scriptet skall fungera krävs en .my.cnf fil som specificerar databasanvändare och dess lösenord som har behörighet att dumpa data ur databasen.
Viktigt här är att sätta rätt behörighet på filen så endast din användare som kör backupscriptet har tillåtelse att läsa den.
.my.cnf
user = dbadmin
password = dbpassword
host = localhost
Efter detta är fixat är det bara att slänga in scriptet i ett enkelt cronjob och sen hoppas att allt sköter sig själv och du slipper få ett e-mail gällande en fallerande backup.
Crontab









