[MySQL管理] 分享一個腳本

[MySQL管理] 分享一個腳本

分享一個腳本在瀏覽國外網站時看到的。

#! /bin/bash

# Ameir Abdeldayem
# http://www.ameir.net
# You are free to modify and distribute this code,
# so long as you keep my name and URL in it.

# your MySQL server's name
SERVER=`hostname`

# directory to backup to
BACKDIR=~/backups/mysql

# date format that is appended to filename
DATE=`date +'%m-%d-%Y-%H%M'`

#----------------------MySQL Settings--------------------#

# your MySQL server's location (IP address is best)
HOST=localhost

# MySQL username
USER=root

# MySQL password
PASS=password

# List all of the MySQL databases that you want to backup in here,
# each separated by a space 使用空格隔開
DBS="db1 db2"

# set to 'y' if you want to backup all your databases. this will override
# the database selection above.
DUMPALL=y


#----------------------Mail Settings--------------------#

# set to 'y' if you'd like to be emailed the backup (requires mutt)
MAIL=y

# email addresses to send backups to, separated by a space 使用空格隔開
EMAILS="address1@provider.com address2@provider.com address3@provider.com address4@provider.com"

SUBJECT="MySQL backup on $SERVER ($DATE)"

#----------------------FTP Settings--------------------#

# set "FTP=y" if you want to enable FTP backups
FTP=y

# FTP server settings; group each remote server using arrays
# you can have unlimited remote FTP servers
FTPHOST="server1"
FTPUSER="user1"
FTPPASS="password1"

FTPHOST="server2"
FTPUSER="user2"
FTPPASS="password2"

# directory to backup to; if it doesn't exist, file will be uploaded to
# first logged-in directory; the array indices correspond to the FTP info above
FTPDIR="backups"
FTPDIR="backups"

#-------------------Deletion Settings-------------------#

# delete old files?
DELETE=y

# how many days of backups do you want to keep?
DAYS=3

#----------------------End of Settings------------------#

# check of the backup directory exists
# if not, create it
if  [ ! -d $BACKDIR ]; then

echo -n "Creating $BACKDIR..."

mkdir -p $BACKDIR

echo "done!"
fi

if  [ $DUMPALL = "y" ]; then

echo -n "Creating list of all your databases..."

DBS=`mysql -h $HOST --user=$USER --password=$PASS -Bse "show databases;"`

echo "done!"
fi

echo "Backing up MySQL databases..."
for database in $DBS
do

echo -n "Backing up database $database..."

mysqldump -h $HOST --user=$USER --password=$PASS $database > \

$BACKDIR/$SERVER-$database-$DATE-mysqlbackup.sql

gzip -f -9 $BACKDIR/$SERVER-$database-$DATE-mysqlbackup.sql

echo "done!"
done

# if you have the mail program 'mutt' installed on
# your server, this script will have mutt attach the backup
# and send it to the email addresses in $EMAILS

if  [ $MAIL = "y" ]; then

BODY="Your backup is ready! Find more useful scripts and info at http://www.ameir.net"

ATTACH=`for file in $BACKDIR/*$DATE-mysqlbackup.sql.gz; do echo -n "-a ${file} ";  done`


echo "$BODY" | mutt -s "$SUBJECT" $EMAILS $ATTACH

if [[ $? -ne 0 ]]; then

echo -e "ERROR:  Your backup could not be emailed to you! \n";

else

echo -e "Your backup has been emailed to you! \n"

fi
fi

if  [ $FTP = "y" ]; then

echo "Initiating FTP connection..."

if  [ $DELETE = "y" ]; then

OLDDBS=`cd $BACKDIR; find . -name "*-mysqlbackup.sql.gz" -mtime +$DAYS`

REMOVE=`for file in $OLDDBS; do echo -n -e "delete ${file}\n"; done`

fi


cd $BACKDIR

ATTACH=`for file in *$DATE-mysqlbackup.sql.gz; do echo -n -e "put ${file}\n"; done`

for KEY in "${!FTPHOST[@]}"
do

echo -e "\nConnecting to ${FTPHOST[$KEY]} with user ${FTPUSER[$KEY]}..."

ftp -nv <<EOF

open ${FTPHOST[$KEY]}

user ${FTPUSER[$KEY]} ${FTPPASS[$KEY]}

tick

cd ${FTPDIR[$KEY]}

$REMOVE

$ATTACH

quit
EOF
done


echo -e  "FTP transfer complete! \n"
fi

if  [ $DELETE = "y" ]; then

cd $BACKDIR; for file in $OLDDBS; do rm ${file}; done

if  [ $DAYS = "1" ]; then

echo "Yesterday's backup has been deleted."

else

echo "The backups from $DAYS days ago and earlier have been deleted."

fi
fi

echo "Your backup is complete!"

Tags: