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 briefly introduces how DBAs can effectively back up databases, which is the primary responsibility of DBAs for database backup and recovery, it is also very important to restore the database in time when it crashes. If you back up the database manually on Linux, it will cause a lot of trouble, I wrote a script, added the script to the scheduled task list, and regularly performed data backup, which makes it easier to manage the database. Here I am a simple little script, of course, you need to modify it in the actual reference. For example, you can add users and passwords 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
# 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"
} DATE = 'date "+ % F-% H-% M-% S "'
Mysql-e "flush tables with read lock ;"
Mysql-e "show master status;">/dev/null
Startfile =/tmp/startposition
Exterfile =/tmp/exterpositionwhile getopts "fdih" OPTS; do
Case $ OPTS in
F)
Mysqldump -- all-databases>/mybackup/db. $ DATE
STARTPOSITION = 'mysql-e "show master status;" | tail-1 | awk '{print $2}'> $ startfile'
ESTARTPOSITION = 'mysql-e "show master status;" | tail-1 | awk '{print $2}'> $ exterfile'
Cd/mybackup
Tar-cjf/root/bakmysql/db.$DATE.tar.gz 2 db. $ DATE>/dev/null
;;
D)
Mysql-e "flush tables with read lock ;"
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 }''
Mysqlbinlog -- start-position $ STARTPOSITION -- stop-position $ ENDPOSITION/mydata/data/$ FILE>/mybackup/incre. $ DATE
Cd/mybackup
Tar-cjf/root/bakmysql/incre.$DATE.tar.gz 2 increcre. $ DATE>/dev/null
;;
I)
Mysql-e "flush tables with read lock ;"
Mysql-e "show master status;">/dev/null
FILE = 'mysql-e "show master status;" | tail-1 | awk '{print $1 }''
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'
Mysqlbinlog -- start-position $ ESTARTPOSITION -- stop-position $ Exendposition/mydata/data/$ FILE>/mybackup/exter. $ DATE
Cd/mybackup
Tar-cjf/root/bakmysql/exter.$DATE.tar.gz 2 exter. $ 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!