Home > Database > Mysql Tutorial > MySQL延时备份之实现_MySQL

MySQL延时备份之实现_MySQL

WBOY
Release: 2016-06-01 13:43:35
Original
909 people have browsed it

bitsCN.com 在实际工作中,经常有一不小心误删除数据库或表而后悔莫及的事件发生,这有没有后悔药可吃呢?今天介绍的延时备份就可以做到。
 
延时备份让slave滞后于master一段时间,当你误操作时只要立即停止slave的同步,即可轻松地从延时备份库中找回你误删的数据。
 
下面介绍使用maatkit工具集中mk-slave-delay来实现延时备份。
 
1、     maatkit工具集简介
官方:http://www.maatkit.org/doc/
 
maatkit是一个包括多个实用MySQL的工具集,使用它们,可以让你的MySQL服务器使用起来更加方便也更加安全。maatkit是开源的软件,你可以自由的改进软件并将自己作出的改进版本向社会发行传播。
 
maatkit工具目前包含26个不同功能的工具。
 
主要有:
 
mk-archiver,mk-audit,mk-find,mk-kill,mk-heartbeat,mk-query-digest,mk-parallel-dump,mk-parallel-restore,mk-show-grants ,mk-slave-delay,mk-slave-move,mk-slave-restart等工具。
 
顾名思义mk-slave-delay是延时备份工具。mk-slave-delay控制MySQL slave端,使它滞后于其master。
 
2、     maatkit安装
下载:http://code.google.com/p/maatkit/
 
安装步骤:
 
tar -zxf maatkit-.tar.gz
 
cd maatkit-
 
perl Makefile.PL
 
make install
 
###
 
# make install
 
….
 
Installing /usr/bin/mk-index-usage
 
Installing /usr/bin/mk-slave-delay
 
Installing /usr/bin/mk-archiver
 
Installing /usr/bin/mk-checksum-filter
 
Installing /usr/bin/mk-slave-prefetch
 
Installing /usr/bin/mk-log-player
 
Installing /usr/bin/mk-query-profiler
 
Installing /usr/bin/mk-slave-move
 
Installing /usr/bin/mk-query-digest
 
Installing /usr/bin/mk-show-grants
 
Installing /usr/bin/mk-parallel-restore
 
Installing /usr/bin/mk-profile-compact
 
Installing /usr/bin/mk-find
 
Installing /usr/bin/mk-table-checksum
 
Installing /usr/bin/mk-fifo-split
 
Installing /usr/bin/mk-error-log
 
Installing /usr/bin/mk-visual-explain
 
Installing /usr/bin/mk-variable-advisor
 
Installing /usr/bin/mk-kill
 
Installing /usr/bin/mk-heartbeat
 
Installing /usr/bin/mk-purge-logs
 
Installing /usr/bin/mk-query-advisor
 
Installing /usr/bin/mk-parallel-dump
 
Installing /usr/bin/mk-merge-mqd-results
 
Installing /usr/bin/mk-duplicate-key-checker
 
Installing /usr/bin/mk-upgrade
 
Installing /usr/bin/mk-deadlock-logger
 
Installing /usr/bin/mk-slave-restart
 
Installing /usr/bin/mk-slave-find
 
Installing /usr/bin/mk-loadavg
 
Installing /usr/bin/mk-table-sync
 
3、     文档帮助
man /usr/bin/mk-slave-delay
 
/usr/bin/mk-slave-delay –help
 
4、     mk-slave-delay使用
示例:
 
mk-slave-delay –delay 1m –interval 15s –run-time 10m slavehost
 
延时运行10分钟,slave滞后master1分钟,15秒检查下次延时情况。
 
详细参数说明:
 
Usage: mk-slave-delay [OPTION...] SLAVE-HOST [MASTER-HOST]
 
Options:
 
–ask-pass            Prompt for a password when connecting to MySQL
 
–charset=s       -A  Default character set
 
–config=A            Read this comma-separated list of config files; if
 
specified, this must be the first option on the command
 
line
 
–[no]continue        Continue replication normally on exit (default yes)
 
–daemonize           Fork to the background and detach from the shell
 
–defaults-file=s -F  Only read mysql options from the given file
 
–delay=m             How far the slave should lag its master (default 1h).
 
Optional suffix s=seconds, m=minutes, h=hours, d=days;
 
if no suffix, s is used.
 
–help                Show help and exit
 
–host=s          -h  Connect to host
 
–interval=m          How frequently mk-slave-delay should check whether the
 
slave needs to be started or stopped (default 1m).
 
Optional suffix s=seconds, m=minutes, h=hours, d=days;
 
if no suffix, s is used.
 
–log=s               Print all output to this file when daemonized
 
–password=s      -p  Password to use when connecting
 
–pid=s               Create the given PID file when daemonized
 
–port=i          -P  Port number to use for connection
 
–quiet           -q  Don’t print informational messages about operation
 
–run-time=m          How long mk-slave-delay should run before exiting.
 
Optional suffix s=seconds, m=minutes, h=hours, d=days;
 
if no suffix, s is used.
 
–set-vars=s          Set these MySQL variables (default wait_timeout=10000)
 
–socket=s        -S  Socket file to use for connection
 
–use-master          Get binlog positions from master, not slave
 
–user=s          -u  User for login if not current user
 
–version             Show version and exit
 
5、     延时原理
mk-slave-delay 定时地starts /stops slave的sql_thread,以使其达到滞后master的目的。
 
2011-02-15T14:46:19 START SLAVE until master 2011-02-15T06:46:17 apollo226-bin.000290/779116659
 
2011-02-15T14:46:19 START SLAVE until master 2011-02-15T06:46:18 apollo226-bin.000290/781852897
 
6、     应用布署
脚本my_slave_delay.sh集中控制延时备份DB服务器.
 
服务器信息以host_ip port 的格式存在delay_host配置文件中。
 
head delay_host.txt
 
10.xx.xx.xxx 3601
 
usage1: sh my_slave_delay.sh
 
usage2: sh my_slave_delay.sh [delay_host] [delay] [interval]
 
my_slave_delay核心代码:
 
delay_host=$1
 
delay_host=${delay_host:-”delay_host.txt”}
 
delay=$2
 
delay=${delay:-”8h”}
 
interval=$3
 
interval=${interval:-”2m”}
 
log_file=”my_slave_delay.log”
 
my_slave_delay(){
 
cat $delay_host |grep -v ‘#’ | while read host port
 
do
 
echo ” /usr/bin/mk-slave-delay  -h $host -P $port –interval=$interval  –delay=$delay &”
 
echo ” /usr/bin/mk-slave-delay  -h $host -P $port –interval=$interval  –delay=$delay &”>>$log_file
 
/usr/bin/mk-slave-delay  -h $host -P $port –interval=$interval  –delay=$delay &
 
sleep 1
 
done
 
}
 
### 取消延时
 
my_slave_start(){
 
echo “my_slave_start”
 
ps aux |grep “mk-slave-delay -h” -i |grep -v “grep” |  awk ‘{print $2,$14,$16}’ | while read pid host port ;
 
do
 
echo “stop slave delay ”;
 
kill  -9 $pid
 
echo “slave start sql_thread ”
 
echo “slave start sql_thread;” | mysql -h $host -P$port -N
 
done;
 
echo “my_slave_start done.”
 
}
 
7、     参考
http://www.maatkit.org/doc/mk-slave-delay.html
  bitsCN.com

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template