A linux mysql full backup, Incremental backup and recovery script implementation program. If you need it, you can refer to it and make it a regular backup.
A linux mysql full backup, Incremental backup and recovery script implementation program. If you need it, you can refer to it and make it a regular backup.
| The Code is as follows: |
|
#! /Bin/bash
# Full & increment backup and recover
# Note: ensure that the/data/bak directory exists in advance and that at least one full backup has been performed during Incremental backup; otherwise, the position file cannot be found.
Port = '000000'
Back_src_dir = "/data/mysql/$ {port}/logs/binlog"
Back_dir = '/data/Bak'
DATE = 'date + % Y % m % d'
User = 'root'
Pass = 'cy2009'
Bak_db = 'test1'
Mysql_bin = '/usr/local/mysql-5.1.48/bin'
Socket = "/data/mysql/$ {port}/mysql. sock"
Full_bak ()
{
Cd $ {back_dir}
DumpFile = Full_back $ DATE. SQL
$ {Mysql_bin}/mysqldump -- lock-all-tables -- flush-logs -- master-data = 2-u $ {user}-p $ {pass }$ {bak_db}> $ {DumpFile}
$ {Mysql_bin}/mysql-u $ {user}-p $ {pass} -- socket =$ {socket}-e "unlock tables" # Store the current binlog and position information in the position File
Cat $ {DumpFile} | grep 'master _ LOG_FILE '| awk-F "'" '{print $2}' >$ {back_dir}/position
Cat $ {DumpFile} | grep 'master _ LOG_FILE '| awk-F "="' {print $3} '| awk-F "; "'{print $1}' >$ {back_dir}/position
}
Incre_bak ()
{
# Lock the table and refresh the log
$ {Mysql_bin}/mysql-u $ {user}-p $ {pass} -- socket =$ {socket}-e "flush tables with read lock"
$ {Mysql_bin}/mysqladmin-u $ {user}-p $ {pass} -- socket =$ {socket} flush-logs
# Obtain the binlog and position of the last backup.
Cd $ {back_dir}
Start_binlog = 'sed-n' 1p' position'
Start_pos = 'sed-n' 2p 'position' # Obtain the current binlog and position
Mysql-u $ {user}-p $ {pass} -- socket =$ {socket}-e "show master statusG" | awk '{print $2}' | sed-n '2, 3 p '> now_position
Stop_binlog = 'sed-n' 1p' now_position'
Stop_pos = 'sed-n' 2p 'now_position'
# If it is in the same binlog
If ["$ {start_binlog}" = "$ {stop_binlog}"]; then
$ {Mysql_bin}/mysqlbinlog -- start-position =$ {start_pos} -- stop-position =$ {stop_pos }$ {back_src_dir}/$ {start_binlog}> Incr_back $ DATE. SQL # Cross-binlog backup
Else
Startline = 'awk "/$ {start_binlog}/{print NR}" $ {back_src_dir}/mysql-bin.index'
Stopline = 'wc-l $ {back_src_dir}/mysql-bin.index | awk '{print $1 }''
For I in 'seq $ {startline }$ {stopline }'
Do
Binlog = 'sed-n "$ I" p $ {back_src_dir}/mysql-bin.index | sed's/. * // g''
Case "$ {binlog}" in
"$ {Start_binlog }")
$ {Mysql_bin}/mysqlbinlog -- start-position =$ {start_pos }$ {back_src_dir}/$ {binlog}> Incr_back $ DATE. SQL
;;
"$ {Stop_binlog }")
$ {Mysql_bin}/mysqlbinlog -- stop-position =$ {stop_pos }$ {back_src_dir}/$ {binlog}> Incr_back $ DATE. SQL
;;
*)
$ {Mysql_bin}/mysqlbinlog $ {back_src_dir}/$ {binlog}> Incr_back $ DATE. SQL
;;
Esac
Done
Fi
# Unlock the table and save the current binlog and position information to the position file.
$ {Mysql_bin}/mysql-u $ {user}-p $ {pass} -- socket =$ {socket}-e "unlock tables"
Cp now_position position
}
Full_recov ()
{
Cd $ {back_dir}
Recov_file1 = 'ls | grep 'full _ back''
$ {Mysql_bin}/mysql-u $ {user}-p $ {pass} -- socket =$ {socket}-e "use $ {bak_db }; source $ {back_dir}/$ {recov_file1 };"
} Incre_recov ()
{
Cd $ {back_dir}
Recov_file2 = 'ls | grep 'incr _ back''
$ {Mysql_bin}/mysql-u $ {user}-p $ {pass} -- socket =$ {socket}-e "use $ {bak_db }; source $ {back_dir}/$ {recov_file2 };"
}
While true
Do
Echo-e "tt *********************************** ***"
Echo
Echo-e "tttWelcome to backup program! "
Echo
Echo-e "ttt (1) Full Backup For MySQL"
Echo-e "ttt (2) Increment Backup For MySQL"
Echo-e "ttt (3) Recover From The Full Backup File"
Echo-e "ttt (4) Recover From The Increment Backup File"
Echo-e "ttt (5) Exit The Program! "
Echo
Echo-e "tt *********************************** ***"
Read-p "Enter your choice:" choice
Case $ choice in
)
Echo "now! Let's backup the data by full method ......."
Full_bak
Echo "succeed! "
Sleep 2
;;
)
Echo "now! Let's backup the data by increment method ......"
Incre_bak
Echo "succeed"
Sleep 2
;;
)
Echo "now! Let's recover from the full back file"
Full_recov
Echo "successful"
Sleep 2
;;
)
Echo "now! Let's recover from the increment backup file"
Incre_recov
Echo "successful"
Sleep 2
;;
)
Break
;;
*)
Echo "Wrong Option! Try again! "
Sleep 2
Continue
;;
Esac
Done |
