Complete and Incremental backup of mysql database using shell

Complete and Incremental backup of shell mysql database documentation Introduction This document uses mysqldump to back up the database. mysqldump uses an SQL-level backup mechanism to export data tables into SQL script files, it is relatively suitable for upgrading between different MySQL versions, which is also the most common backup method. mysqldump is slower than direct copy. This document describes the automatic backup of the Mysql database, including full backup and Incremental backup. Full backup is performed once a week, and Incremental backup is performed every day. After the backup is successful, it is automatically uploaded to the FTP server. Mysql needs to enable binary log. The backup policy layout puts the script under the/usr/bin directory (1). the binlog method is more flexible and easy to enable, and supports Incremental backup. Mysqld must be restarted when binlog is enabled. First, close mysqld, open/etc/my. cnf, and add the following lines: [mysqld] log-bin and then start mysqld. The HOSTNAME-bin.000001 and HOSTNAME-bin.index will be generated during running, the previous file is mysqld record all the update operations on the data, the subsequent file is the index of all binlog, cannot be easily deleted. For more information about binlog, see the manual. (2) set the crontab task and execute the backup script shell> vi/etc/crontab every day to add the following: 0 00 *** root/usr/bin/backap_mysql.sh Script :#! /Bin/bash # set-x # This script is mainly used to back up databases on the mysql server. And automatically uploads data to the server through FTP. An email will be sent after the backup. Echo-e "this script is mainly used to back up the database on the mysql server and automatically upload it to the server through FTP. "Host = www.chlinux.net pass = chenqibin name = root DATE = 'date +" % Y % m % d "'wan_dir ="/WAN_DIR "ZENG_BACK ="/backup "DATA_DIR = "/ usr/local/mysql/data "MYSQL_BIN ="/usr/local/mysql/bin "error_log =" $ WAN_DIR/backup_error _ $ DATE. log "backup_log =" $ ZENG_DIR/backup _ $ DATE. log "gzdumpfile =" Courier "db ="/var/log/backup_mongodate.txt "cd $ DATA_DIR ls-l $ DATA_DIR | grep" ^ d "| awk-F" "'{print $9} '> $ db function Wan () {# Check whether the full backup directory exists. If not, create it. If [-d $ WAN_DIR] then echo "the full backup directory exists"> $ backup_log else echo "the full backup directory does not exist. Start to create ....... "/bin/mkdir $ WAN_DIR fi eMailFile =" $ WAN_DIR/mail. log "email = kelly@r2games.net echo"> $ eMailFile echo "-----------------------"> $ eMailFile echo "'date +" % y-% m-% d % H: % M: % S "'" >>$ eMailFile echo "-----------------------" >>$ eMailFile cd $ WAN_DIR for dbname in $ (cat $ db) do mysqldump -- flush-logs-u $ name- P $ pass -- skip-lock-tables -- quick $ dbname> $ dbname. SQL if [$? = 0] then find $ ZENG_BACK-name "*. log "-mtime + 32-exec rm {}\;>/dev/null 2> & 1 cd $ WAN_DIR tar-zcvf $ dbname. $ gzdumpfile $ dbname. SQL echo "Backup MySQL succeed" >>$ eMailFile mail-s "MySQL Backup" $ email <$ eMailFile else echo "Backup MySQL fail" >>$ eMailFile mail-s "MySQL Backup fail "$ email <$ eMailFile fi done # Delete the local Incremental backup file after full backup, retain only the Incremental backup file find $ ZENG_BACK-name "*. SQL .tar.gz" -- mtime + 7-exec rm-rf {}\>> $ backup_log # upload the backed up file to the FTP server cd $ WAN_DIR for db_back in $ (cat $ db) do ftp-nv $ Host <EOF user wolf "wolf #123" put $ db_back. $ gzdumpfile quit EOF done} function zeng () {/bin/mkdir/zeng_dir eMailFile = "$ ZENG_DIR/mail. log "email = kelly@r2games.net echo"> $ eMailFile echo "-----------------------"> $ eMailFile echo "'date +" % y-% m-% d % H: % M: % S "'" >>$ eMailFile echo "---------- --------------- ">>$ EMailFile TIME = $ (date"-d 10 day ago "+ % Y-% m-% d % H: % M: % S) startTime = $ (date "-d 1 day ago" + "% Y-% m-% d % H: % M: % S ") start = "-- start-datetime" # Delete the binary file mysql-u $ name-p $ pass-e "purge master logs before $ {TIME}" & echo "delete 10 days before log" | tee-a $ eMailFile filename = 'cat $ DATA_DIR/chlinux-bin.index | awk-F "/" '{print $2} ''cd/zeng_dir I in $ filename do echo "$ S TartTime start backup binlog ">>$ eMailFile for db_name in $ (cat $ db) do mysqlbinlog-u $ name-pchenqibin-d $ db_name $ Start = "$ StartTime" $ DATA_DIR/$ I >>$ db_name. $ DATE. SQL if [$? = 0] then cd/zeng_dir tar-zcvf $ db_name. $ gzdumpfile $ db_name. $ DATE. SQL echo "Backup MySQL succeed" >>$ eMailFile mail-s "MySQL Backup" $ email <$ eMailFile else echo "Backup MySQL fail" >>$ eMailFile mail-s "MySQL Backup fail "$ email <$ eMailFile fi done find $ ZENG_BACK-name "*. log "-name + 32-exec rm {}\;> /dev/null 2> & 1 cd/zeng_dir # Delete the complete backup file find $ WAN_DIR-name "* .tar.gz" -- mtime + 7-exec rm-rf {}\; # upload the backed up file to the FTP server for db_back in $ (cat $ db) do ftp-nv $ Host <EOF user wolf "wolf #123" put $ db_back. $ gzdumpfile quit EOF done} backfile = 'LS-l/wan_dir | wc-l' if [$ backfile! = 0] then echo "Full backup already exists. Now Incremental Backup" sleep 10 zeng else echo "has not been fully backed up yet, now the complete backup "sleep 30 wan fi main variables are described: host # ftp ip pass # FTP password name # FTP and mysql username DATE # Time WAN_DIR # Full backup directory ZENG_BACK # Incremental Backup Directory DATA_DIR # mysql data directory error_log # error log gzdumpfile # compressed suffix db # mysql Database Name