Backup Each MySQL Table to its own File

Primary tabs

Submitted by geoff on Mon, 2012-01-09 10:02

This script will backup each table in the given database into its own file.

#/bin/bash

db="database"
pw="password"
un="username"

if [ ! -d "tables" ]
    then
    mkdir "tables"
fi

tables=$(mysql -N -u$un -p$pw $db <<< "show tables" | grep -Ev "^log" | xargs);
for table in $tables
do
echo $table
filename="tables/$table.sql"
mysqldump -u$un -p$pw $db $table > $filename
done

echo "done" 

An extension of this is to then copy these files to S3 to provide an offsite backup.

#/bin/bash

db="xxx"
pw="xxx"
un="xxx"
dt=`date "+%Y/%m/%d"`
s3bucket="xxx"
filepath="/mnt/tables"

export AWS_ACCESS_KEY_ID=xxx
export AWS_SECRET_ACCESS_KEY=xxx

if [ ! -d $filepath ]
then
    mkdir -p $filepath
fi

tables=$(mysql -N -u$un -p$pw $db <<< "show tables" | grep -Ev "^log" | xargs);
for table in $tables
do
    echo $table
    filename="$table.sql"
    mysqldump -u$un -p$pw $db $table > $filepath/$filename

    gzip --force $filepath/$filename

    s3cmd put -v "$s3bucket:$dt/$filename.gz" "$filepath/$filename.gz"
done

echo "done"

This required the Amazon credentials are stored in plain view. Meaning a malicious hacker could delete your database and also delete your backups!!! Something to be considered.

Tags:

Your IP Info

IP Address Locator

Speed Test

Test how fast your Internet connection really is.

DropBox

Use this link to get DropBox http://db.tt/iQHWaaF