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 |