Friday, June 09, 2017

Auto Backup MYSQL DB table structure

Usually during coding, one would often forgot about the MySQL databases structure.
I would usually check in my code but not my DB design.

Below is a sh file that backup the MySQL table structure.
Not the best script. But it does the job.

#!/bin/bash

Host=localhost
BDir=/root/SQLStructureBackup/

Dump="/usr/bin/mysqldump -d -h $Host -u BACKUPUSER -pSOMEPASSWORDHERE "
MySQL=/usr/bin/mysql

Today=$(date +%Y-%b-%d)

# Get a list of all databases
Databases=$(echo "SHOW DATABASES" | $MySQL -u BACKUPUSER -pSOMEPASSWORDHERE)

for db in $Databases; do
        date=`date`
        file="$BDir/$Today-$db.sql.gz"
        echo "Backing up '$db' from '$Host' on '$date' to: "
        echo "   $file"
        $Dump $db | gzip > $file
done

No comments: