User Tools

Site Tools


howtos:mysql_backup_script

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

howtos:mysql_backup_script [d/m/Y H:i] (current)
Line 1: Line 1:
 +<file>
 +#!/bin/sh
 +# System + MySQL backup script
 +# Copyright (c) 2008 Marchost
 +# This script is licensed under GNU GPL version 2.0 or above
 +# ---------------------------------------------------------------------
  
 +#########################
 +######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 +"%d%H")
 +
 +### 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 tables in individual files ###
 +  mkdir $BACKUP/$NOW/$db
 +
 +  for i in `echo "show tables" | $MYSQL -u $MUSER -h $MHOST -p$MPASS $db|grep -v Tables_in_`;
 +  do
 +    FILE=$BACKUP/$NOW/$db/$i.sql.gz
 +    echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u $MUSER -h $MHOST -p$MPASS $db $i | $GZIP -9 > $FILE
 +  done
 +done
 +
 +### Compress all tables in one nice file to upload ###
 +
 +ARCHIVE=$BACKUP/$NOW.tar.gz
 +ARCHIVED=$BACKUP/$NOW
 +
 +$TAR -cvf $ARCHIVE $ARCHIVED
 +
 +### Dump backup using FTP ###
 +cd $BACKUP
 +DUMPFILE=$NOW.tar.gz
 +$FTP -n $FTPS <<END_SCRIPT
 +quote USER $FTPU
 +quote PASS $FTPP
 +cd $FTPD
 +mput $DUMPFILE
 +quit
 +END_SCRIPT
 +
 +### Delete the backup dir and keep archive ###
 +
 +rm -rf $ARCHIVED
 +
 +</file>
howtos/mysql_backup_script.txt · Last modified: d/m/Y H:i (external edit)