MySQL Dump Script For Database Backups

back to tech articles
| 30th November 2012
Mac OS X (10.6.8), CentOS, Linux

Do you perform regular database backups? Maybe you take copies of your live database and push that data somewhere else (like, your staging site)? And maybe you’ve accidentally overwritten your live database coz you typed something wrong?

Yeah, well I have. And after the second time, I decided to write a little bash script to prevent it happening again! Typically, you would run this script on your local machine.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
#!/bin/bash

# check for empty variables
echo "Choose a host to dump from: "
select host in local staging live
do
  echo "Using" $host
  break;
done

if [ -z "$1" ]
then
  echo "Database name: "
  read dbname
fi

# variables
date=$( date +%Y-%m-%d_%H%M )
backupPath="/Users/jason/Sites/db_backups"

# create dir if not found
[ ! -d "$backupPath" ] && mkdir -p $backupPath

# which host are we going to
[ $host == "local" ] && server="127.0.0.1" && dbuser="root" && dbpass="root"
[ $host == "staging" ] && server="192.168.0.1" && dbuser="jason" && dbpass="password"
[ $host == "live" ] && server="192.168.0.2" && dbuser="jason" && dbpass="password"

# dump
echo "starting..."
sudo mysqldump -h ${server} -u ${dbuser} -p${dbpass} -B ${dbname} --lock-tables=false > ${backupPath}/${host}_db_${date}.sql
echo "Success. Created" ${backupPath}/${host}_db_${date}.sql

That will grab the database from your host and create a backup on your local machine. It needs your own configuration variables (host names, IP addresses, etc), but the rough outline is there.

Now, what to do if we need to upload this to another server? If only someone wrote a handy little script…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
#!/bin/bash

# check for empty variables
echo "Choose a host to upload to: "
select host in local staging
do
echo "Using" $host
break;
done

if [ -z "$1" ]
then
echo "Database name: "
read dbname
fi

echo "Choose a file to upload: "
select db in $(ls /Users/jason/Sites/db_backups); do
echo "Uploading" $db
break;
done

# which host are we going to
[ $host == "local" ] && server="127.0.0.1" && dbuser="root" && dbpass="root"
[ $host == "staging" ] && server="192.168.0.1" && dbuser="jason" && dbpass="password"

# confirm message
echo "Upload" ${db} "to" ${server} "... Are you sure?"
select confirm in Yes No
do
echo $confirm
break;
done

# upload
if [ $confirm == "Yes" ]
then
echo "starting..."
sudo mysql -h ${server} -u ${dbuser} -p${dbpass} -B ${dbname} < /Users/jason/Sites/db_backups${db}
echo "Success! Database uploaded to" ${host}
fi

Create or install these scripts wherever you like. Then add a link to your PATH and you’re ready for some db-dumping mayhem!

Optional hint: Add Gzip for compression on download if you like by piping (|) the mysqldump into gzip before writing it to your local file.

Adding Gzip Compression

Change this line…

1
sudo mysqldump -h ${server} -u ${dbuser} -p${dbpass} -B ${dbname} --lock-tables=false  > ${backupPath}/${host}_db_${date}.sql

To this…

1
sudo mysqldump -h ${server} -u ${dbuser} -p${dbpass} -B ${dbname} --lock-tables=false | gzip > ${backupPath}/${host}_db_${date}.sql.gz

And you are all set for some backups!