MySQL sub-library backup

The company has always been full + binlog, but what to do if there is a problem with a single database? in the process of full recovery, first, it will delay time, second, it will affect other databases, and third, if the amount of data is large, it will also be a problem for the recovery of the database, so I have proposed that, in addition to full backup, we should also do sub-database backup, as long as it is more flexible and has problems, and directly restore the single database (convenient and fast).

Let's go directly to the script.

1. Back up the other libraries outside the MySQL.

#! / bin/bash#---# $Name: Store_backup.sh# $Version: v1.Qing $Author: Qiu Yuetao # $organization: www.yi*.cn# $Create Date: 2017-06-0315 $Description: MySQL store Backup # is used for MySQL sub-library backup And keep 30 days of data #-# source / etc/init.d/functionsDAY= `date +% F`Back _ DIR= "/ home/backup/mysqlbackup" DBUSER= "root" DBPASSWD= "your own password" BIN_PATH= "/ usr/bin" LOG= "/ home/backup/mysqlbackup/mysqlback. Log "# Close all tables and refresh log #" $BIN_PATH "/ mysql-u" $DBUSER "- p" $DBPASSWD "- e" flush logs "#" $BIN_PATH "/ mysql-u" $DBUSER "- p" $DBPASSWD "- e" flush tables with read lock "echo" mysqldump start `date +% F\:% M\:% S` > > "$LOG" # Each backup database to the target location # for i in `"$BIN_PATH" / mysql-u " $DBUSER "- p" $DBPASSWD "- e" show databases "| grep-vE" Database | information_schema "`do if [!-d" $BACK_DIR "/" $I "] Then mkdir-p "$BACK_DIR" / "$I" fi "$BIN_PATH" / mysqldump-u "$DBUSER"-p "$DBPASSWD"-- default-character-set=utf8-- opt-- lock-tables-f-- log-error= "$LOG"$I" > "$BACK_DIR" / "$I" / "$DAY" .SQL doneecho "mysqldump stop `date +% F\% H\:% M\:% S`" > "$LOG" echo " > "$LOG" # # Delete 30 days ago backup files # for RM in `"$BIN_PATH" / mysql-u "$DBUSER"-p "$DBPASSWD"-e "show databases" | grep-vE "Database | information_schema" `dorm-rf "$BACK_DIR" / "$RM" / $(date +% F-date='30 days ago') .sql done

The effect is as follows:

[root@files mysqlbackup] # tree-L 1 / home/backup/mysqlbackup/home/backup/mysqlbackup ├── luence ├── hpi ├── h ├── ├── mysql ├── mysqlback.log ├── ypp └── tao

Add the script to the crontab scheduled task

[root@files mysqlbackup] # crontab-l0 4 * / root/shell/mysqlbackup.sh