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)