Implementation of MySQL delayed backup _ MySQL

In actual work, the implementation of MySQL delayed backup bitsCN.com often happens when you accidentally delete the database or table and regret it. is there any regret? The delayed backup described today can be achieved.

Delayed backup causes slave to lag behind the master for a period of time. when a misoperation occurs, you only need to immediately stop slave synchronization, and you can easily retrieve the accidentally deleted data from the delayed backup database.

The following describes how to use mk-slave-delay in the maatkit tool to implement delayed backup.

1. Introduction to maatkit
Http://www.maatkit.org/doc/

Maatkit is a tool set that includes multiple practical MySQL tools. using them can make your MySQL server more convenient and secure. Maatkit is an open-source software. you can freely improve the software and publish your own improved version to the public.

The maatkit tool currently contains 26 tools with different functions.

Mainly include:

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.

Mk-slave-delay is a delayed backup tool. Mk-slave-delay controls the MySQL slave end so that it lags behind its master.

2. install maatkit
Download: 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. document help
Man/usr/bin/mk-slave-delay

/Usr/bin/mk-slave-delay-help

4. mk-slave-delay
Example:

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

The latency is 10 minutes, and the slave lags behind master1 minute, and the next latency is checked in 15 seconds.

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 mysql options from the given file

-Delay = m How far the slave shoshould lag its master (default 1 h ).

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 shocould check whether

Slave needs to be started or stopped (default 1 m ).

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 shoshould 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 regularly uses SQL _thread of starts/stops slave to delay the 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
The script my_slave_delay.sh centrally controls the delayed backup DB server.

The server information is stored in the delay_host configuration file in the 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: -includelay_host.txt "}

Delay = $2

Delay =$ {delay:-"8h "}

Interval = $3

Interval =$ {interval:-"2 M "}

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 latency

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

}

7. Reference
Http://www.maatkit.org/doc/mk-slave-delay.html
BitsCN.com