Implementation of MySQL delayed backup

In actual work, we often accidentally delete the database or table and regret it. Is there any regret to take? 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-<version> .tar.gz
 
Cd maatkit-<version>
 
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