Automated MySQL Database Dumps Script

Deprecated -- This will back up ALL MySQL databases. I'll have a new one out soon.

===

Here's a script I've put together to run nightly database dumps. There's a lot of back up software out there that can back up files on a daily basis but most cannot back up live databases without any issues, corruption for example.

This will dump all databases for the user specified although it is sometimes best to use root.

I start with creating a directory to store the backups; we'll use /sqldumps in the root directory:

# mkdir /sqldumps

Inside the /sqldumps folder, we'll have the script:
nightly-sql-dump.sh

#!/bin/bash
# MySQL Dump All Databases / Data

# Backup Storage Location (no trailing slash)
LOC=/sqldumps

# MySQL User
USER=root

# MySQL $USER Pass
PASS=root

# Date (output like: YYYY-MM-DD)
DDATE=`date +%Y-%m-%d`

# Run DUMP
mysqldump -u$USER -p$PASS --all-databases > $LOC/$DDATE-alldb.sql

Feel free to go ahead and test the script to ensure that it runs correctly:

# sh nightly-sql-dump.sh
# ls -l
-rw-r--r-- 1 root root 355324732 Mar 13 21:27 2013-03-13-alldb.sql
-rw-r--r-- 1 root root       305 Mar 13 21:27 nightly-sql-dump.sh

Now you can go ahead and add it to the root users crontab. This example will run the script every day at 1:00AM SERVER TIME:

# crontab -l
*	1	*	*	*	sh /sqldumps/nightly-sql-dump.sh

It may be a good idea to append to the script to copy the file created to multiple locations whether it is seperate drives or locations via FTP or otherwise.

You may also like...

1 Response

  1. David says:

    Will be coming out with an updated version of this script at some point in the future. Keep subscribed

Leave a Reply

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