Back up and restore MySQL databases

I use MySQL on a daily basis. Most web developers connect to databases using GUI's such as phpmyadmin or MySQL query browser. This article will briefly explain how to use the command line to backup your databases, making it easy to automate your tasks using bash scripts.

Introduction

Backing up a mysql database

 
mysqldump -u [uname] -p[pass] [dbname] > [backupfile.sql]
 

Restoring your MySQL database

 
mysql - u [uname] -p[pass] [dbname] < [backfile.sql]
 

The above commands require a bit more explanation the square brackets represent a variable that you would enter yourself (don't put in the brackets).

Note there is no space between the -p option and the password. Usually when working on the command line we enter the password separately for security purposes.

MySQL Bash Backup script

The Script

View on GitHub

 
#!/bin/bash
# database.bash
# Automates working with a database
# v2.0
# Last Updated Nov 26 2011
# Documentation: http://www.nickyeoman.com/blog/mysql/31-mysql-backup-restore
 
##
#  Set/Get Variables
##
tstamp=$(date +%s)  # Date, The "+%s" option to 'date' is GNU-specific.
 
#Dump or Update?
if [ -z "$1" ]; then
  echo -n "What do you want to do?([D]ump or [U]pdate)"
  read parm
else
  parm=$1
fi
 
# Database name
if [ -z "$2" ]; then
  echo -n "What database are you using?"
  read dbname
else
  dbname=$2
fi
 
#Database user
if [ -z "$3" ]; then
  echo -n "What database user are you using?"
  read dbuser
else
  dbuser=$3
fi
 
#database password
if [ -z "$4" ]; then
  echo -n "What database password are you using?"
  read dbpass
else
  dbpass=$4
fi
 
#database host (in case not local)
if [ -z "$5" ]; then
  echo -n "What is the database host? (usually localhost)"
  read dbhost
else
  dbhost=$5
fi
 
##
#  Got everything we need, let's begin
##
 
#Regardless of what we are doing, create a backup of the existing database.
# This is a life saver when you run an update instead of a dump or want to restore to a previous dump
# if your using git, just add sql/backup/ to .gitignore then your dumps will remain local
 
#check to see if dir exists
if test ! -d "../sql/backup"; then
  mkdir ../sql/backup #if not make the directory
fi
 
#dump the database incase we want to revert
mysqldump -h $dbhost -u $dbuser -p$dbpass $dbname > ../sql/backup/$tstamp.$dbname.sql
 
 
#Great, what are we actually doing?
 
#dump or update the db
if [ "$parm" = d ]; then 
  mysqldump -h $dbhost -u $dbuser -p$dbpass $dbname > ../sql/$dbname.sql  
elif [ "$parm" = u ]; then #update the db
  mysql -h $dbhost -u $dbuser -p$dbpass $dbname < ../sql/$dbname.sql
else
  echo "d or u options only! (lower case)" 
fi
 
#All Done
 

Documentation

File System setup

The script assumes you have your file system setup as so.

• example.com
  • scripts
  • sql

The database.bash should be placed and run from the scripts folder, all our sql dumps will be stored to the sql folder.

File names

The name of the scripts directory and the database.bash file do not matter, just the sql directory is hard codded.

The name of the dump will always be the databasename.sql

Use the parameters

Instead of following the wizard each time you need to dump or update the database, you can create a bash script in the scripts directory to dump the database like so.

./database.bash d databaseName databaseUser databasePassword databaseHost

A script to update all you would have to do is change the u to a d:

./database.bash u databaseName databaseUser databasePassword databaseHost

If you name these files dump.bash and update.bash, all you would have to do is run

bash dump.bash

To dump the database. This is helpful when you add the script to a git hook, as then you have a snapshot of your database that matches the file system snapshot.