UseCentOS can help IT managers to get rid of the boring learning methods, quick grasp Linux technology knowledge, so as to improve the level of technology, to lay a good foundation for their growth. Our tenet: all for one, one for all
For database backup and recovery is the main responsibility of DBA, and how to effectively backup database, and when the database is the collapse of the timely recovery will become very important, if the Linux of pure manual backup of the database, will bring a lot of trouble, I have to write a script, the script added to regularly perform the tasks list, regular data backup, it becomes easier to manage the database, here is a simple small script, of course in the actual reference but also changes, such as adding the user and password, you can modify the doing according to their own needs.
The following simple introduce the script, the script has four parameters:
/PATH/mysqlbak.sh -f / / full backup, the database can according to their own changes set how long the implementation time, add a month to perform a!
/PATH/mysqlbak.sh -d / / this is from the last full backup to a difference between the current time of the backup, thus reducing the database backup time.
/PATH/mysqlbak.sh -i / / this parameter can achieve incremental backup, which changes between a database backup and the backup, this time can be a little shorter, so the loss can be reduced when the database can collapse, once an hour.
/PATH/mysqlbak.sh -h / / this is to help information
These are added to the plan regularly, can realize the automatic execution of the script, without manual backup administrator.
# /bin/bash!
#
#
Function {HELPTXT
Echo "/PATH/mysqlbak.sh -f: You can backup all databases"
Echo "/PATH/mysqlbak.sh -i: Just backup the exter change from the last backup"
Echo "/PATH/mysqlbak.sh -d: Backup from the last full-backup to the current time"
}
Echo "/PATH/mysqlbak.sh -f: You can backup all databases"
Echo "/PATH/mysqlbak.sh -i: Just backup the exter change from the last backup"
Echo "/PATH/mysqlbak.sh -d: Backup from the last full-backup to the current time"
}
DATE=`date "+%F-%H-%M-%S".
MySQL -e "FLUSH TABLES WITH READ LOCK;"
/dev/null">MySQL -e "SHOW MASTER STATUS >/dev/null;"
Startfile=/tmp/startposition
Exterfile=/tmp/exterposition
MySQL -e "FLUSH TABLES WITH READ LOCK;"
/dev/null">MySQL -e "SHOW MASTER STATUS >/dev/null;"
Startfile=/tmp/startposition
Exterfile=/tmp/exterposition
While getopts "fdih" OPTS; do
Case $OPTS in
F)
/mybackup/db.$DATE">Mysqldump --all-databases >/mybackup/db.$DATE
$startfile`">STARTPOSITION=`mysql -e "SHOW MASTER STATUS; tail -1 awk'{print" | | $2}'>$startfile`
$exterfile`">ESTARTPOSITION=`mysql -e "SHOW MASTER STATUS; tail -1 awk'{print" | | $2}'>$exterfile`
CD /mybackup
/dev/null">Tar -cjf /root/bakmysql/db.$DATE.tar.gz2 db.$DATE >/dev/null
;;
D)
MySQL -e "FLUSH TABLES WITH READ LOCK;"
/dev/null">MySQL -e "SHOW MASTER STATUS >/dev/null;"
FILE=`mysql -e "SHOW MASTER STATUS; tail -1 awk'{print" | | $1}'`
STARTPOSITION=`cat $startfile`
ENDPOSITION=`mysql -e "SHOW MASTER STATUS; tail -1 awk'{print" | | $2}'`
/mybackup/incre.$DATE">Mysqlbinlog --start-position $STARTPOSITION --stop-position $ENDPOSITION /mydata/data/$FILE >/mybackup/incre.$DATE
CD /mybackup
/dev/null">Tar -cjf /root/bakmysql/incre.$DATE.tar.gz2 incre.$DATE >/dev/null
;;
I)
MySQL -e "FLUSH TABLES WITH READ LOCK;"
/dev/null">MySQL -e "SHOW MASTER STATUS >/dev/null;"
FILE=`mysql -e "SHOW MASTER STATUS; tail -1 awk'{print" | | $1}'`
>$exterfile`">EENDPOSITION=`mysql -e "SHOW MASTER STATUS tail -1;" | | awk'{print $2}'>>$exterfile`
ESTARTPOSITION=`tail -n 2 $exterfile head -n 1` |
Exendposition=`tail -n 1 $exterfile`
/mybackup/exter.$DATE">Mysqlbinlog --start-position $ESTARTPOSITION --stop-position $Exendposition /mydata/data/$FILE >/mybackup/exter.$DATE
CD /mybackup
/dev/null">Tar -cjf /root/bakmysql/exter.$DATE.tar.gz2 exter.$DATE >/dev/null
;;
H)
HELPTXT
;;
ESAC
Done
Case $OPTS in
F)
/mybackup/db.$DATE">Mysqldump --all-databases >/mybackup/db.$DATE
$startfile`">STARTPOSITION=`mysql -e "SHOW MASTER STATUS; tail -1 awk'{print" | | $2}'>$startfile`
$exterfile`">ESTARTPOSITION=`mysql -e "SHOW MASTER STATUS; tail -1 awk'{print" | | $2}'>$exterfile`
CD /mybackup
/dev/null">Tar -cjf /root/bakmysql/db.$DATE.tar.gz2 db.$DATE >/dev/null
;;
D)
MySQL -e "FLUSH TABLES WITH READ LOCK;"
/dev/null">MySQL -e "SHOW MASTER STATUS >/dev/null;"
FILE=`mysql -e "SHOW MASTER STATUS; tail -1 awk'{print" | | $1}'`
STARTPOSITION=`cat $startfile`
ENDPOSITION=`mysql -e "SHOW MASTER STATUS; tail -1 awk'{print" | | $2}'`
/mybackup/incre.$DATE">Mysqlbinlog --start-position $STARTPOSITION --stop-position $ENDPOSITION /mydata/data/$FILE >/mybackup/incre.$DATE
CD /mybackup
/dev/null">Tar -cjf /root/bakmysql/incre.$DATE.tar.gz2 incre.$DATE >/dev/null
;;
I)
MySQL -e "FLUSH TABLES WITH READ LOCK;"
/dev/null">MySQL -e "SHOW MASTER STATUS >/dev/null;"
FILE=`mysql -e "SHOW MASTER STATUS; tail -1 awk'{print" | | $1}'`
>$exterfile`">EENDPOSITION=`mysql -e "SHOW MASTER STATUS tail -1;" | | awk'{print $2}'>>$exterfile`
ESTARTPOSITION=`tail -n 2 $exterfile head -n 1` |
Exendposition=`tail -n 1 $exterfile`
/mybackup/exter.$DATE">Mysqlbinlog --start-position $ESTARTPOSITION --stop-position $Exendposition /mydata/data/$FILE >/mybackup/exter.$DATE
CD /mybackup
/dev/null">Tar -cjf /root/bakmysql/exter.$DATE.tar.gz2 exter.$DATE >/dev/null
;;
H)
HELPTXT
;;
ESAC
Done
Add the task plan in crontab -e, can realize the automatic backup of the database!!
I hope this little script can make you more convenient management of MySQL database, if there is a problem, can discuss!