MySQL automation Koriyuki uses mysqldump and Mysqlbinlog to implement a weekly full and daily differential backup of a database and add to the execution plan "hot standby"

Case:

There is a database on the line, need to be fully prepared once a week, every day to prepare for a daily [security or poor, do not add, do not stingy disk oh, and the recovery is fast]

1, weekly database Hellodb to do a full backup

Crontab Task Scheduler:

Ten  on 1 /bin/bash/work/dump-complete-hello. SH          ===> Monday 1:10 A.M. Perform a full script/work/dump-complete-hello every week. SH

Full script/work/dump-complete-hello.sh content is as follows:

#!/bin/bash# full file storage location Weekbackup=/complete/hello-'Date+%F '. sql# complete with mysqldump #--database followed by databases to be prepared #--master-The data record change MASTER to statement, 2 means to put this line comment #--flush-logs execute the flush logs command after locking the table, toggle binlog File #--single-transaction: A single thing, because the database hellodb inside the table is InnoDB storage engine, support things, can guarantee the data in a consistent state when the backup/usr/local/mysql/bin/mysqldump--database Hellodb--master-data=2--flush-logs--single-transaction >The statement behind the $weekbackup # is to create a statement that holds the full file location, which is required for an incremental backupCat> Weekbackup.SH<<eof#!/bin/basheofEcho "wb= $weekbackup">> Weekbackup.SH

2, daily database Hellodb to do differential backup:

Crontab Task Scheduler:

 -  * * * * */bin/bash/work/dump-incre. SH ==> performs a differential script/work/dump-incre 2:20 A.M. every day. SH

The contents of the/work/dump-incre.sh script are as follows:

#!/bin/bash# source a bit/work/weekbackup.SH, the script was generated by the most recent full-backup script, providing a fully-stocked file location./work/weekbackup.SH# Get binary files used by the current database Binlog= '/usr/local/mysql/bin/mysql-e'Show Master Status'|grep 'bin'|awk '{print $}'' # Get the full stop time from the most recent full file Time=grep 'completed'$WB |awk '{printf "%s%s\n", $5,$6}'# Differential backup of database Hellodb via Mysqlbinlog #--start-position indicates the starting position of an incremental backup, and its value is a fully-prepared termination position#/var/log/mysql/binarylog/$binlog The binary log file being used for the current database/usr/local/mysql/bin/mysqlbinlog--start-datetime="$time"/var/log/mysql/binarylog/$binlog >/increment/incre-'Date+%f%h%m%s '. sql

3. Recovery test:

Full Standby recovery:

[[email protected] data]# MySQL </complete/hello- -- on- -. sql [[email protected] data]# MySQL Welcome to the MariaDB Monitor. Commands End With;            or \g. Your MariaDB ConnectionIDIs7Server Version:5.5. $-mariadb-log MariaDB Server Copyright (c) -, the, Oracle, Monty program Ab and others. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement. MariaDB [(none)]>show databases; +--------------------+            |            Database | +--------------------+            |            Information_schema | |            Hellodb | |            MySQL | |            newdb | |            Performance_schema | |            tempdb | |            Test | +--------------------+7RowsinchSet (0.00sec) MariaDB [(none)]>Use Hellodb; Database changed MariaDB [Hellodb]>Show tables; +-------------------+            |            Tables_in_hellodb | +-------------------+            |            Classes | |            COC | |            Courses | |            Scores | |            Students | |            Teachers | |            TOC | +-------------------+7RowsinchSet (0.00Sec

Differential recovery:

[[email protected] data]# MySQL </increment/incre- -- on- -. sql [[email protected] data]# MYSQLL-Bash:mysqll:command not found [[e-mail protected] data]# MySQL Welcome to the MariaDB Monitor. Commands End With;            or \g. Your MariaDB ConnectionIDIs9Server Version:5.5. $-mariadb-log MariaDB Server Copyright (c) -, the, Oracle, Monty program Ab and others. Type'Help ;'Or'\h'  forHelp. Type'\c'ToClearThe current input statement. MariaDB [(none)]>Use Hellodb; Database changed MariaDB [Hellodb]>Show tables; +-------------------+            |            Tables_in_hellodb | +-------------------+            |            Classes | |            COC | |            Courses | |            Scores | |            Students | |            tb1 | |            Teachers | |            TOC | +-------------------+8RowsinchSet (0.00sec) MariaDB [Hellodb]>Select*From tb1; +------+            |            name | +------+            |            Wjs | +------+1RowinchSet (0.01Sec

From the above results know that full and differential backup can be restored, it can be put into normal use, you can go, haha

MySQL automation Koriyuki uses mysqldump and Mysqlbinlog to implement a weekly full and daily differential backup of a database and add to the execution plan "hot standby"