Articles

This content is in the process of being styled since being imported from a previous system, and may lack some formatting detail or imagery.

MySQL Backup Script

  • Hosting
  • Open Source
  • Uncategorized
  • Work Life

There are oh-so-many of these on the web, so I thought I'd add another. I got inspirations from two sources, and here's my version.

#!/bin/bash
# MYSQL Backup Script
#
# Creates:
# $BACKUP_PATH/
# '-- yyyy-mm-dd/
#   |-- database_name.sql       Full database backup
#   `-- database_name/
#     |-- table1.sql            Individual database table backup
#     |-- table2.sql            Individual database table backup
#     |-- table3.sql            Individual database table backup
#     `-- table4.sql            Individual database table backup

<p>###########################
# CONFIG
########
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
TAR="$(which tar)"
DATE=$(date +"%Y-%m-%d")
BACKUP_PATH="/var/backups/mysql"

</p><p>#Create database dump directory and go there
mkdir -p $BACKUP_PATH/$DATE
pushd $BACKUP_PATH >> /dev/null

</p><p># Loop through each database
for database in `echo "show databases" | $MYSQL | grep -v Database`;
do
        mkdir -p $DATE/$database

</p><p>
# Dump database
        $MYSQLDUMP --defaults-file=/root/.my.cnf --add-drop-table --allow-keywords -a -a -c $database

>$DATE/$database.sql

</p><p>
# Loop through each table
        for table in `echo "use $database; show tables" | $MYSQL $database | grep -v Tables_in_`;
        do

</p><p>
# Dumping table
                $MYSQLDUMP --defaults-file=/root/.my.cnf --add-drop-table --allow-keywords -q -a -c $database $table

>$DATE/$database/$table.sql

</p><p>        done

</p><p>done

</p><p># Create daily archive
$TAR jcf $DATE.tar.bz $DATE

</p><p># Ensure only root can access these files
$CHOWN 0.0 -R $DATE
$CHOWN 0.0 $DATE.tar.bz
$CHMOD go-rwx $DATE
$CHMOD go-rwx $DATE.tar.bz
$CHMOD go-rwx -R $DATE/*

</p><p># Back to start directory
popd >> /dev/null</p>

I was going to go to the extent of using which to find the location of grep and echo, but thought it unnecessary. Makes me wonder, should we use which to find which??

Comments

Hi Pete! Been in use since I wrote the post ;-)

Hey looks nice! have ya tested it yet?

Comments for this post are currently disabled.

Subscribe to my Newsletter

* indicates a required field

I don't send many updates. I don't like to spam. Let's face it - I've not posted many new articles for a while (although I do plan on changing that). If you subscribe to new articles, I'll send no more than two emails a week. As for workshop and conference information, that'll be as and when I have details. It's not likely to be more than an email a week.

Topics