MySQL中怎么實(shí)現(xiàn)延時(shí)備份

155次閱讀

共計(jì) 4714 個(gè)字符，預(yù)計(jì)需要花費(fèi) 12 分鐘才能閱讀完成。

MySQL 中怎么實(shí)現(xiàn)延時(shí)備份，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。

1、 maatkit 工具集簡(jiǎn)介

maatkit 是一個(gè)包括多個(gè)實(shí)用 MySQL 的工具集，使用它們，可以讓你的 MySQL 服務(wù)器使用起來(lái)更加方便也更加安全。maatkit 是開(kāi)源的軟件, 你可以自由的改進(jìn)軟件并將自己作出的改進(jìn)版本向社會(huì)發(fā)行傳播。

maatkit 工具目前包含 26 個(gè)不同功能的工具。

主要有：

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 是延時(shí)備份工具。mk-slave-delay 控制 MySQL slave 端，使它滯后于其 master。

2、 maatkit 安裝
:http://code.google.com/p/maatkit/

安裝步驟：

tar -zxf maatkit- version .tar.gz

cd maatkit- version

perl Makefile.PL

make install

###

# make install

hellip;.

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 ndash;help

4、 mk-slave-delay 使用
示例：

mk-slave-delay ndash;delay 1m ndash;interval 15s ndash;run-time 10m slavehost

延時(shí)運(yùn)行 10 分鐘，slave 滯后 master1 分鐘,15 秒檢查下次延時(shí)情況。

詳細(xì)參數(shù)說(shuō)明：

Usage: mk-slave-delay [OPTION…] SLAVE-HOST [MASTER-HOST]

Options:

ndash;ask-pass Prompt for a password when connecting to MySQL

ndash;charset=s -A Default character set

ndash;config=A Read this comma-separated list of config files; if

specified, this must be the first option on the command

line

ndash;[no]continue Continue replication normally on exit (default yes)

ndash;daemonize Fork to the background and detach from the shell

ndash;defaults-file=s -F Only read options from the given file

ndash;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.

ndash;help Show help and exit

ndash;host=s -h Connect to host

ndash;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.

ndash;log=s Print all output to this file when daemonized

ndash;password=s -p Password to use when connecting

ndash;pid=s Create the given PID file when daemonized

ndash;port=i -P Port number to use for connection

ndash;quiet -q Don rsquo;t print informational messages about operation

ndash;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.

ndash;set-vars=s Set these MySQL variables (default wait_timeout=10000)

ndash;socket=s -S Socket file to use for connection

ndash;use-master Get binlog positions from master, not slave

ndash;user=s -u User for login if not current user

ndash;version Show version and exit

5、延時(shí)原理
mk-slave-delay 定時(shí)地 starts /stops slave 的 sql_thread, 以使其達(dá)到滯后 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、應(yīng)用布署
腳本 my_slave_delay.sh 集中控制延時(shí)備份 DB 服務(wù)器.

服務(wù)器信息以 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 lsquo;# rsquo; | while read host port

do

echo”/usr/bin/mk-slave-delay -h $host -P $port ndash;interval=$interval ndash;delay=$delay”

echo”/usr/bin/mk-slave-delay -h $host -P $port ndash;interval=$interval ndash;delay=$delay”$log_file

/usr/bin/mk-slave-delay -h $host -P $port ndash;interval=$interval ndash;delay=$delay

sleep 1

done

}

### 取消延時(shí)

my_slave_start(){

echo“my_slave_start”

ps aux |grep“mk-slave-delay -h”-i |grep -v“grep”| awk lsquo;{print $2,$14,$16} rsquo; | while read pid host port ;

do

echo“stop slave delay $host:$port

kill -9 $pid

echo“slave start sql_thread $host:$port”

echo“slave start sql_thread;”| mysql -h $host -P$port -N

done;

echo“my_slave_start done.”

}