Mysql backup, Incremental backup and restoration programs in linux

Source: Internet
Author: User
Tags mysql 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.

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.