Backup Each MySQL Table to its own File

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.

Leave a comment

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