Mysql Database backup script.

I have found this one mysql backup script, that just suite my need to backup all database in their respective file. It will be easy for recovery, if I need the sql for that particular database. This is script have been modified from mysql database backup script in HowToForge. This script will also FTP to other location, but I have remove this functionality, as I dont need to move it to another FTP server.

#!/bin/sh
# System + MySQL backup script
# Copyright (c) 2008 Marchost
# This script is licensed under GNU GPL version 2.0 or above
# Modified by Farhan Faisal @ farhanfaisal.com
# ---------------------------------------------------------------------

#########################
######TO BE MODIFIED#####

### System Setup ###
BACKUP=YOUR_LOCAL_BACKUP_DIR

### MySQL Setup ###
MUSER=”MYSQL_USER”
MPASS=”MYSQL_USER_PASSWORD”
MHOST=”localhost”

### FTP server Setup ###
FTPD=”YOUR_FTP_BACKUP_DIR”
FTPU=”YOUR_FTP_USER”
FTPP=”YOUR_FTP_USER_PASSWORD”
FTPS=”YOUR_FTP_SERVER_ADDRESS”

######DO NOT MAKE MODIFICATION BELOW#####
#########################################

### Binaries ###
TAR=”$(which tar)”
GZIP=”$(which gzip)”
FTP=”$(which ftp)”
MYSQL=”$(which mysql)”
MYSQLDUMP=”$(which mysqldump)”

### Today + hour in 24h format ###
NOW=”$(date +”%Y-%m-%d”)”

### Create hourly dir ###

mkdir $BACKUP/$NOW

### Get all databases name ###
DBS=”$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse ‘show databases’)”
for db in $DBS
do

### Create dir for each databases, backup databases in this folder. ###
FILE=$BACKUP/$NOW/$db.sql.gz
$MYSQLDUMP -a -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done

### Compress all tables in one nice file ###

ARCHIVE=$BACKUP/$NOW.tar.gz
ARCHIVED=$BACKUP/$NOW

$TAR -zcf $ARCHIVE $ARCHIVED

rm -rf $ARCHIVED

http://www.howtoforge.com/shell-script-to-back-up-all-mysql-databases-each-table-in-an-individual-file-and-upload-to-remote-ftp

Leave a Reply

Your email address will not be published. Required fields are marked *