mysql延时备份_MySQL延时备份之实现_MySQL

.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

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网

TAG标签：延时