MySQL 5.7.10 automatic Backup, automatic cleanup of old backup sets (RPM)

1,mysqldump Backup Scripts

Backup script for, there are several parameters to note:

(1)--master-data=2: This parameter can be used to record the copy point information of the current backup when it is built from the library.

(2)--extended-insert=false: This is when a SQL statement is formed, an INSERT statement is recorded

(3)--single-transaction: To ensure the consistency of the backup, in fact it works is to set the isolation level of this session is: repeatable READ, to ensure that the session (dump), will not see the other session has committed data.

The backup script is: backup_full.sh

Date= ' Date ' +%y%m%d%h%m%s "' Back_path=/data/mysql/backup/data Mysqldump=/usr/local/mysql/bin/mysqldump CD ${back_path} ${mysqldump}-uroot--password= "[email protected]"-r-e-h localhost--socket=/usr/local/mysql/mysql.sock--skip-opt-- Single-transaction--flush-logs--master-data=2--add-drop-table--create-option--quic K--extended-insert=false--set-charset--disable-keys--databases user_db |gzip > user_db.${date}.sql.gz ${mysqldump}-uroot--password= "[email protected]"-r-e-h localhost--socket=/usr/local/mysql/mysql.sock--skip-opt- -single-transaction--flush-logs--master-data=2--add-drop-table--create-option--quic K--extended-insert=false--set-charset--disable-keys--databases plocc_system |gzip > plocc_system.${date}.sql.gz

2. Clean up scripts for redundant backups

Clear the old backup script as: clear_old_backup.sh, the approximate idea is:

(1) A year ago, save the backup collection of numbers 1th and 16th for each month, and other deletions (# 1 for one years ago, save 1/16 on every month, else clear).

(2) Six months ago, save the backup collections of numbers 1th and 11th and 21st, and delete the others (# 2 for 6 months ago, save 1/11/21 in a month)

(3) One months ago, save the 6 o'clock Backup collection every day, the other deleted (# 3 for a month ago,save *063001.sq.gz on a days)

(4) Three days ago, save the backup collection for 6 o'clock and 18 o'clock, and delete the other (# 4 for 3 day ago, save 063001.sql.gz and 183001.sql.gz)

The Script clear_old_backup.sh is:

d3= ' Date ' +%y-%m-%d%h:%m:%s ' d2= ' Date ' +%y-%m-%d ' Logfile=/data/mysql/backup/scripts/clear_old_backup.log Old_backupfile=/data/mysql/backup/scripts/old_backupfile_$d2.csv Cd/data/mysql/backup/data echo "" >> $logfile echo "-$d 3 begin ...--" >> $logfile # 1 for one years ago, save 1/16 on every month, else clear, Find. -mtime +360-name "*.sql.gz" |grep-v "01063001.sql.gz" >> $old _backupfile; # 2 for 6 months ago, save 1/11/21 in a month Find. -mtime +180-name "*.sql.gz" |grep-v "1063001.sql.gz" >> $old _backupfile; # 3 for a month Ago,save *063001.sq.gz in a day Find. -mtime +30-name "*.sql.gz" |grep-v "063001.sql.gz" >> $old _backupfile; # 4 for 3 days ago, save 063001.sql.gz and 183001.sql.gz Find. -mtime +3-name "*.sql.gz" |grep-v "063001.sql.gz" |grep-v "183001.sql.gz" >> $old _backupfile; # 5 Begin Clear Find/mnt/resource-mtime +5-name "*.sql.gz"-exec rm-rf {} \; #6 Save the clear sql.gz to a temp directory For i in ' cat $old _backupfile '; Do echo "--$i--is cleared." MV $i/mnt/resource/ Done d4= ' Date ' +%y-%m-%d%h:%m:%s ' echo "-$d 4 End ...--" >> $logfile echo "" >> $logfile

PS: Originally here is ready to use Find.-mtime +180-name "*.sql.gz" |grep-v "1063001.sql.gz"-exec rm-rf {} \; The direct RM was deleted after the retrieval, but since find uses G Rep error does not recognize {}, so the compromise adopted a scheme to save the retrieved file in a temporary file old_backupfile_$d2.csv inside, and then traverse the Old_backupfile_$d2.csv file to MV or RM operation, etc. The find grep exec error is as follows:

[[email protected] ~]# find. -mtime +180-name "*.sql.gz" |grep-v "1063001.sql.gz"-exec rm-rf {} \; grep: {}: No such file or directory [Email protected] ~]#

3,crontab Scheduled Tasks

Use Crontab to set up scheduled tasks, perform daily backups and cleanup, and automate the DB server.

[Email protected]_master_2 ~]# crontab-l 1 * * * sh/data/mysql/backup/scripts/clear_old_backup.sh */6 * * * sh/data/mysql/backup/scripts/backup_full.sh [[Email protected]_master_2 ~]#

4, subsequent extension issues

Simple backup is done, simple old backup collection cleanup is also done, but the follow-up can do more things, more perfect some:

(1) Email notification, backup success or failure, email notification

(2) Copy a copy of the backup collection to the file server

(3) Automatically check the validity of database backup

MySQL 5.7.10 automatic Backup, automatic cleanup of old backup sets (RPM)