Croned MySQL Database Backup Script and Transfer to an Offsite Location

This bash script regularly (from a cron) backs up a production database to an offsite server into a date-stamped compressed (gzip) format.

It is intended to be run from the production server. You need to generate a public key (ssh-keygen) for the production server and add it to the offsite server’s authorized keys file (/home/username/.ssh/authorized_keys). Be sure to chmod 600 the public and private key after creating them.

Also make sure that you use the same username for the key generation, the connection string key location and the cron entry.


#
# http://codingsnippets.com/croned-mysql-database-backup-script
#
# This bash script regularly (from a cron) backs up a production
# database to an offsite server into a date-stamped compressed
# (gzip) format.
#
# It is intended to be run from the production server.
#
#
# Please report any comments, bugs or improvements at
# http://codingsnippets.com/croned-mysql-database-backup-script
# and please leave this message intact so others can
# do the same, improve the script, or download the
# most current version. Thanks.
#

LOCALFILE_PATH="/home/www/backups"
FILENAME="livedb-`date "+%Y-%m-%d"`.sql.gz"
SSH="$(which ssh)"
GZIP="$(which gzip)"
SCP="$(which scp)"
MYSQL_DUMP="$(which mysqldump)"

DB_HOSTNAME="localhost"
DB_USERNAME="USERNAME"
DB_PASSWORD="password"
DATABASE="database_name"

#Offsite location
SSH_PORT="22"
SSH_HOSTNAME="yourhostname.com"
SSH_USERNAME="yourusername"
SSH_KEY_LOCATION="~/.ssh/id_rsa"
BACKUP_LOCATION="/home/user/backups"

echo $FILENAME
$MYSQL_DUMP -u$DB_USERNAME -p$DB_PASSWORD -h$DB_HOSTNAME $DATABASE | $GZIP -9 > $LOCALFILE_PATH/$FILENAME
$SCP -i $SSH_KEY_LOCATION -P $SSH_PORT $LOCALFILE_PATH/$FILENAME $SSH_USERNAME@$SSH_HOSTNAME:$BACKUP_LOCATION/$FILENAME

Comments (1)

Drop All Tables in a MySQL Database

This BASH Script drops all tables in a MySQL Database without having to drop the database.

This is courtesy of http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/


#!/bin/bash
MUSER="$1"
MPASS="$2"
MDB="$3"

# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)

if [ $# -ne 3 ]
then
echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}"
echo "Drops all tables from a MySQL"
exit 1
fi

TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )

for t in $TABLES
do
echo "Deleting $t table from $MDB database..."
$MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"
done

Comments

Securing Subversion (SVN) Under HTTPS with Apache

This configuration is used to set up a svn repository under https for apache.

Contents of vhost file

<VirtualHost *:443>
    ServerAdmin admin@example.com
    ServerName svn.example.com

    SSLEngine on
    SSLCertificateFile /websites/_certs/example.com.crt
    SSLCertificateKeyFile /websites/_certs/example.com.key

    <Location />
        SSLRequireSSL
        DAV svn
        #SVNPath /repositories/repo_name
        SVNParentPath /repositories

        AuthzSVNAccessFile /repositories/svn-access-policy

        AuthType Basic
        AuthName "Restricted Area"
        AuthUserFile /repositories/.htpasswd
        Require valid-user
    </Location>
</VirtualHost>

<VirtualHost *:80>
    ServerName svn.example.com
    RedirectMatch (.*) https://svn.example.com$1
</VirtualHost>

Contents of /repositories/svn-access-policy

[groups]
administrators = user1, user2

[repo_name:/]
@administrators = rw

Comments (1)

Subversion (SVN) Backup BASH Script

This bash script performs an svnadmin hotcopy, tar and gunzips the resulting copy, then transfers the file to a remote server via scp with a datestamped filename.  Enjoy.


#
# http://codingsnippets.com/subversion-svn-backup-bash-script
#
# This script performs an svnadmin hotcopy, tar and
# gunzips the resulting copy, then transfers the file
# to a remote server via scp with a datestamped filename.
#
#
# Please report any comments, bugs or improvements at
# http://codingsnippets.com/subversion-svn-backup-bash-script
# and please leave this message intact so others can
# do the same, improve the script, or download the
# most current version. Thanks.
#

### Change these variables ###

LOCALFILE_PATH="/home/www/backups"
REPO_LOCATION="/repos/myrepo"
TARGET_SCP_LOCATION="username@your.remote.server.com:/home/username/backups/"
TARGET_SCP_PORT="22"
SCP_ID="~/.ssh/id_dsa"

### You don't need to change these ###

FILENAME="repo-`date "+%Y-%m-%d"`.tar.gz"
SCP="$(which scp)"
SVNADMIN="$(which svnadmin)"
TAR="$(which tar)"
TEMP_REPO_DIR="$LOCALFILE_PATH/temp_repo"
RM="$(which rm)"
MKDIR="$(which mkdir)"

### Commands ###

$MKDIR $TEMP_REPO_DIR
$SVNADMIN hotcopy $REPO_LOCATION $TEMP_REPO_DIR
$TAR -cvzf $LOCALFILE_PATH/$FILENAME $TEMP_REPO_DIR
$SCP -i $SCP_ID -P $TARGET_SCP_PORT $LOCALFILE_PATH/$FILENAME $TARGET_SCP_LOCATION/$FILENAME
$RM -rf $TEMP_REPO_DIR
$RM $LOCALFILE_PATH/$FILENAME

Comments