MySQL延时备份之实现_MySQL
程序员文章站
2024-01-20 13:36:34
...
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
延时备份让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-
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