One script for Mysql backup

DBAs are mainly responsible for database backup and recovery. It is important to back up the database effectively and restore the database in time when the database crashes, if you back up the database manually on Linux, it will cause a lot of trouble. I wrote a script to add the script to the regular task list and regularly perform data backup, this makes it easier to manage the database. Here I am a simple little script. Of course, I need to modify it in actual reference, such as adding users and passwords, you can make modifications as needed. The following is a brief introduction to this script, which has four parameters:/PATH/mysqlbak. sh-f // full backup. You can set how long it takes to execute the Backup Task based on database changes. Add the Backup Task once a month! /PATH/mysqlbak. sh-d // This is a differential backup from the last full backup to the current time, which reduces the backup time of the database. /PATH/mysqlbak. sh-I // This parameter can be used to implement Incremental backup, that is, changes to the database between the last backup and this backup. This time can be shorter, in this way, the loss can be reduced when the database crashes, once an hour. /PATH/mysqlbak. sh-h // This is the help information to add these to the scheduled execution plan, you can implement automatic script execution, without the Administrator manual backup. #! /Bin/bash
# FunctionHELPTXT {
Echo "/PATH/mysqlbak. sh-f: Youcanbackupalldatabases"
Echo "/PATH/mysqlbak. sh-I: Justbackuptheexterchangefromthelastbackup"
Echo "/PATH/mysqlbak. sh-d: Backupfromthelastfull-backuptothecurrenttime"
} DATE = 'date "+ % F-% H-% M-% S "'
Mysql-e "FLUSHTABLESWITHREADLOCK ;"
Mysql-e "SHOWMASTERSTATUS;">/dev/null
Startfile =/tmp/startposition
Exterfile =/tmp/exterpositionwhilegetopts "fdih" OPTS; do
Case $ OPTSin
F)
Mysqldump -- all-databases>/mybackup/db. $ DATE
STARTPOSITION = 'mysql-e "SHOWMASTERSTATUS;" | tail-1 | awk '{print $2}'> $ startfile'
ESTARTPOSITION = 'mysql-e "SHOWMASTERSTATUS;" | tail-1 | awk '{print $2}'> $ exterfile'
Cd/mybackup
Tar-cjf/root/bakmysql/db.$DATE.tar.gz 2db. $ DATE>/dev/null
;;
D)
Mysql-e "FLUSHTABLESWITHREADLOCK ;"
Mysql-e "SHOWMASTERSTATUS;">/dev/null
FILE = 'mysql-e "SHOWMASTERSTATUS;" | tail-1 | awk '{print $1 }''
STARTPOSITION = 'cat $ startfile'
ENDPOSITION = 'mysql-e "SHOWMASTERSTATUS;" | tail-1 | awk '{print $2 }''
Mysqlbinlog -- start-position $ STARTPOSITION -- stop-position $ ENDPOSITION/mydata/data/$ FILE>/mybackup/incre. $ DATE
Cd/mybackup
Tar-cjf/root/bakmysql/incre.$DATE.tar.gz 2incre. $ DATE>/dev/null
;;
I)
Mysql-e "FLUSHTABLESWITHREADLOCK ;"
Mysql-e "SHOWMASTERSTATUS;">/dev/null
FILE = 'mysql-e "SHOWMASTERSTATUS;" | tail-1 | awk '{print $1 }''
EENDPOSITION = 'mysql-e "SHOWMASTERSTATUS;" | tail-1 | awk '{print $2}'> $ exterfile'
ESTARTPOSITION = 'Tail-n2 $ exterfile | head-n1'
Exendposition = 'Tail-n1 $ exterfile'
Mysqlbinlog -- start-position $ ESTARTPOSITION -- stop-position $ Exendposition/mydata/data/$ FILE>/mybackup/exter. $ DATE
Cd/mybackup
Tar-cjf/root/bakmysql/exter.$DATE.tar.gz 2exter. $ DATE>/dev/null
;;
H)
HELPTXT
;;
Esac
Done
You can add a task plan in crontab-e to automatically back up the database !! I hope this script will make it easier for you to manage mysql databases. If you have any questions, we can discuss them together!