Shulou(Shulou.com)05/31 Report--

How to achieve delayed backup in MySQL, I believe that many inexperienced people do not know what to do. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. Brief introduction to maatkit toolset

Maatkit is a toolset that includes several practical MySQL, and using them can make your MySQL server easier and more secure to use. Maatkit is open source software, you are free to improve the software and distribute the improved version to the public.

The maatkit tool currently contains 26 tools with different functions.

The main ones are:

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 and other tools.

As the name implies, mk-slave-delay is a deferred backup tool. Mk-slave-delay controls the MySQL slave side so that it lags behind its master.

2. Maatkit installation

Http://code.google.com/p/maatkit/

Installation steps:

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. Documentation help

Man / usr/bin/mk-slave-delay

/ usr/bin/mk-slave-delay-help

4. Use of mk-slave-delay

Example:

Mk-slave-delay-delay 1m-interval 15s-run-time 10m slavehost

Delay running for 10 minutes, slave lag master1 minutes, 15 seconds to check the next delay.

Detailed parameter description:

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 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. Delay principle

Mk-slave-delay periodically starts / stops slave sql_thread in order to achieve the purpose of lagging 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. Application deployment

Script my_slave_delay.sh centrally controls delayed backup of DB servers.

The server information is stored in the delay_host configuration file in host_ip port format.

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 core code:

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

}

# cancel delay

My_slave_start () {

Echo "my_slave_start"

Ps aux | grep "mk-slave-delay-h"-I | grep-v "grep" | awk'{print $2

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."

}

After reading the above, have you mastered how to achieve delayed backup in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.