Shell scripts automatically back up MySQL to FTP and periodically clean up expired backups _linux shell

The previous company used an online search for MySQL automatic backup to the FTP shell script, has been used well. But the problem is with more and more backup files, have to regularly clean up FTP old backup files, very inconvenient.

In fact, the MySQL backup does not have to be kept for too long, if it can automatically clean up the old backup files. However, the internet looked for a while, although there are scripts in this area, but it may not be in line with my needs. So I can only manually change the previous shell script to achieve this.

First we have to determine the time to keep the backup, I think one months of backup is enough. Because the basic does not appear after 1 months to restore 1 months before the backup situation. (If you have a manual backup, after all, automatic backup will not replace the part of the manual backup). So our idea comes out: when you upload a backup with today's date as the filename, and delete the same date backup one months ago, you can ensure that the server retains the last one months of backup.

Then we need to know a command under Linux: Date. This is a command that can view the current server's time and date information. Of course here we need to know the parameters of this command, after all we need to get the date part.

Enter the following command, you can see "year-month-day" input, similar to "2011-09-09":

Copy Code code as follows:

Date +%y-%m-%d

So what if we're going to get a date 1 months ago? Use this command to get the following:

Copy Code code as follows:

Date-d Last-month +%y-%m-%d

What do you think? It's easy. The actual backup, because the previous script used the form of "m-d-y", which is the "month-day-year" form, so I made a slight adjustment. But the following shell script is based on "year-month-day" to produce the filename.

So, given the entire backup shell script, the friend needs to fill out a Chinese-language section and use it on its own Linux server. How to use: Copy to a directory, then give execution permissions, and then set the day to run at a specified time in cron. (For this part, please find the relevant instructions, do not repeat here)

MySQL Backup to ftp with auto clean up the shell script is as follows:

Copy Code code as follows:

#!/bin/bash

Myuser= "db_username" # database username, it is recommended to use the root account or a username with global privileges
Mypass= "Db_password" # Database Password
myhost= "localhost" # database server

# Linux bin paths, change the If it can ' t be autodetected via which command
Mysql= "$ (which MySQL)"
Mysqldump= "$ (which mysqldump)"
Chown= "$ (which CHOWN)"
Chmod= "$ (which CHMOD)"
Gzip= "$ (which gzip)"

# Path to database backup
Dest= "/home/backup"

# Main directory where backup would be stored
mbd= "$DEST/mysql"

# get hostname
Host= "$ (hostname)"

# get data in dd-mm-yyyy format
Now= "$ (date +%d-%m-%y)"
Old= "$ (date-d last-month +%d-%m-%y)"

# file to store current backup File
File= ""
# Store List of databases
Dbs= ""

# do don't BACKUP these databases
iggy= "Test"

RM-RF $MBD

[!-D $MBD] && mkdir-p $MBD | | :

# only Root can access it!
$CHOWN 0.0-r $DEST
$CHMOD 0600 $DEST

# Get all Database list
Dbs= "$ ($MYSQL-u $MyUSER-H $MyHOST-p$mypass-bse ' show databases ')"

Cat > Ftpcc.sh <<eof
CD $MBD
Ftp-i-n<<!
Open blog.renhao.org #在这里填写FTP服务器的域名或IP
User Ftp_user Ftp_pass #在这里填写FTP用户名和密码, note space-delimited
Binary
Mput *.gz
Eof

For DB in $DBS
Todo
Skipdb=-1
If ["$IGGY" "!="];
Then
For I in $IGGY
Todo
["$db" = "$i"] && skipdb=1 | | :
Done
Fi

If ["$skipdb" = "-1"]; Then
File= "$MBD/$db. $HOST. $NOW. Gz"
echo "Delete $db. $HOST. $OLD. gz" >> ftpcc.sh
# do all Inone job in pipe,
# Connect to MySQL using mysqldump for select MySQL database
# and pipe it out to GZ file in Backup dir:)
$MYSQLDUMP-U $MyUSER-H $MyHOST-p$mypass $db | $GZIP-9 > $FILE
Fi
Done

Cat >> ftpcc.sh <<eof
Close
Bye
!
Eof

SH ftpcc.sh
RM ftpcc.sh

After such a shell script is run, you can get a separate backup file for each MySQL library and compress it in GZ format to save space.