[Blog recommendations] use shell scripts to automate Database Backup

[Blog recommendations] use shell scripts to automate Database Backup

This blog post is from the Bkjia blogger Lemon. If you have any questions, go to the blog page for an interactive discussion! Blog: http://maomaostyle.blog.51cto.com/2220531/1654994

Because personal website construction cannot guarantee high availability, and data may be easily lost, the need to back up the database is necessary. So I found some information on the Internet, based on my actual situation, you can use a script to back up the database at every day and regularly Delete the database backup files seven days ago every Sunday. Below, I will post my backup database script for your learning and reference.

First, this is the script for backing up the database.

 
 

  
  
Cat/usr/local/script/BackupDatabase
  
  
#! /Bin/bash
  
  
# Shell Command For Backup MySQL Database Everyday Automatically By Crontab
  
  
# Time 2015-5-20
  
  
# Name huxianglin
  
  
USER = root
  
  
PASSWORD = xxxxxxxx
  
  
DATABASE1 = zblog
  
  
DATABASE2 = zabbix
  
  
BACKUP_DIR =/data/backup/database/# path of the backup database file
  
  
LOGFILE =/data/backup/database/data_backup.log # back up the log file of the database script
  
  
DATE = 'date + % Y % m % d-% H % M-d-3minute '# Get the current system time-3 minutes
  
  
DUMPFILE1 = $ DATE-zblog. SQL # Name of the database to be backed up
  
  
DUMPFILE2 = $ DATE-zabbix. SQL
  
  
Archive1=dumpfile1-tar.gz # Name of the backup database after compression
  
  
Archive2w.dumpfile2-tar.gz
  
  

  
  
If [! -D $ BACKUP_DIR]; # determines whether the backup path exists. If not, this path is created.
  
  
Then
  
  
Mkdir-p "$ BACKUP_DIR"
  
  
Fi
  
  

  
  
Echo-e "\ n" >>$ LOGFILE
  
  
Echo "------------------------------------"> $ LOGFILE
  
  
Echo "backup date: $ DATE" >>$ LOGFILE
  
  
Echo "------------------------------------"> $ LOGFILE
  
  

  
  
Cd $ BACKUP_DIR # Jump to the backup path
  
  
/Usr/local/mysql/bin/mysqldump-u $ USER-p $ PASSWORD $ DATABASE1> $ DUMPFILE1 # Use mysqldump to back up the database
  
  
If [[$? = 0]; then
  
  
Tar czvf $ ARCHIVE1 $ DUMPFILE1 >>$ LOGFILE 2> & 1 # determine whether the backup is successful. If the backup is successful, compress the backup database; otherwise, write the error log to the log file.
  
  
Echo "$ ARCHIVE1 backup successful! ">>> $ LOGFILE
  
  
Rm-f $ DUMPFILE1
  
  
Else
  
  
Echo "$ ARCHIVE1 Backup Fail !" >>$ LOGFILE
  
  
Fi
  
  
/Usr/local/mysql/bin/mysqldump-u $ USER-p $ PASSWORD $ DATABASE2> $ DUMPFILE2
  
  
If [[$? = 0]; then
  
  
Tar czvf $ ARCHIVE2 $ DUMPFILE2 >>$ LOGFILE 2> & 1
  
  
Echo "$ ARCHIVE2 backup successful! ">>> $ LOGFILE
  
  
Rm-f $ DUMPFILE2
  
  
Else
  
  
Echo "$ ARCHIVE2 Backup Fail !" >>$ LOGFILE
  
  
Fi
 
 

Then, write the script to delete the backup file.

 
 

  
  
[Root @ localhost database] # cat/usr/local/script/CleanDatabase
  
  
#! /Bin/bash
  
  
# Time 2015-05-21
  
  
# Name huxianglin
  
  

  
  
BACKUPDIR = "/data/backup/database/" # define the backup file path
  
  
KEEPTIME = 7 # define the number of days before the file to be deleted
  
  
DELFILE = 'Find $ BACKUPDIR-type f-mtime + $ KEEPTIME-exec ls {}\; '# find the file whose number of days is greater than 7 days
  
  
For delfile in $ {DELFILE} # Delete objects that meet the condition that the number of days is greater than seven days
  
  
Do
  
  
Rm-f $ delfile
  
  
Done
 
 

Finally, you need to write the time for automatic script execution in crontab.

 
 

  
  
Cat/etc/crontab
  
  
SHELL =/bin/bash
  
  
PATH =/sbin:/bin:/usr/sbin:/usr/bin
  
  
MAILTO = root
  
  
# For details see man 4 crontabs
  
  
# Example of job definition:
  
  
#. ---------------- Minute (0-59)
  
  
# |. ----------- Hour (0-23)
  
  
# |. ---------- Day of month (1-31)
  
  
# |. ------- Month (1-12) OR jan, feb, mar, apr...
  
  
# |. ---- Day of week (0-6) (Sunday = 0 or 7) OR sun, mon, tue, wed, thu, fri, sat
  
  
# |
  
  
# ***** User-name command to be executed
  
  
01 00 *** root/usr/local/script/BackupDatabase # define to execute the backup database script at 00:01 every day
  
  
02 00 ** 0 root/usr/local/script/CleanDatabase # define to delete database backup files at 00:02 every Sunday
 
 

The above is my script for backing up the database and regularly deleting the database. I hope you can give your comments and suggestions.