Oliver Nassar

Backing up my Ubuntu Web Server Config/DBs

November 10, 2012

I ran into an issue this morning when I started my computer up to do some development. I outlined my development flow in my post Web Development Flow: Ubuntu, Parallels, Coda and rsync, and while it's changed a little, it's more-or-less the same.

The problem was this: I tried to connect to my MySQL server, both through the CLI (command line) and through Sequel Pro (a great GUI for administrating your MySQL databases).

In doing so, I received the following error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I wasn't sure exactly what the cause of it was. I soon discovered that MySQL was having a problem starting up because there wasn't enough disk-space.

My Parallels VM had only 3 gigs allotted to it, which caused strange things to happen when there was no space left.

But this brought to light something else. Namely, backing up all my core files through a bash script of some kind, and running it either as a cron or before I go to bed each night.

The Plan

I've created a bash script (see below) which backs-up the following into a timestamp-based zip file:

  1. Ensure the zip program is installed
  2. Create a backup directory (timestamp-based)
  3. Backup the Apache configuration file (apache2.cnf)
  4. Backup the Apache sites-enabled and sites-available directories
  5. Backup the PHP configuration file (php.ini)
  6. Backup the VSFTP (Very Simple FTP) configuration file (vsftpd.conf)
  7. Backup the MySQL my.cnf configuration file
  8. Backup all MySQL databases in one _all.sql file
  9. Backup each MySQL database (excluding the mysql and information_schema databases) under it's associated name
  10. Zip up the directory
  11. Delete the directory

It's automatic, and now, just incase something strange happens and I can't connect to my db, I'll have my backups.

Additionally, I could use sendmail to email this out each night through a cron, but that's for another day.

Pre-req

When running the bash script, you will need to provide your MySQL username and password. To use the script:

  1. Create a file such as backup.sh
  2. Ensure it's executable by running sudo chmod +x backup.sh
  3. Run the following ./build.sh username password

The bash script, which you can copy-and-paste into your backup.sh file, is as follows:

#!/bin/bash -x
## Oliver Nassar Ubuntu Web Server Backup Script
## onassar@gmail.com
##
## Requires two arguments:
## 1) MySQL Username
## 2) MySQL Password
##
## Usage:
## sudo chmod +x build.sh
## ./build.sh {username} {password}


## 0.1 Username
##
##
if [ $# -ne 2 ]
then
    echo "Usage: sudo ./`basename $0` {username} {password}"
    exit 0
fi
USERNAME=$1
PASSWORD=$2


## 0.1 Zip Requirement
##
##
sudo apt-get install -y zip


## 1.0 Create Backup Directory
##
##
cd
directoryName="backup-`date +%b.%d.%y-%H:%M:%S`"
mkdir $directoryName


## 2.0 Apache BAckup
##
##
mkdir ~/$directoryName/apache
cp /etc/apache2/apache2.conf ~/$directoryName/apache
cp -R /etc/apache2/sites-enabled/ ~/$directoryName/apache/sites-enabled/
cp -R /etc/apache2/sites-available/ ~/$directoryName/apache/sites-available/


## 3.0 PHP
##
##
mkdir ~/$directoryName/php
cp /etc/php5/apache2/php.ini ~/$directoryName/php


## 4.0 VSFTP
##
##
mkdir ~/$directoryName/ftp
cp /etc/vsftpd.conf ~/$directoryName/ftp


## 5.0 MySQL Backup
##
##
mkdir ~/$directoryName/mysql
cp /etc/mysql/my.cnf ~/$directoryName/mysql

mkdir ~/$directoryName/mysql/databases
mysqldump --user $USERNAME --password=$PASSWORD --all-databases > ~/$directoryName/mysql/databases/_all.sql

mysql --user $USERNAME --password=$PASSWORD -e "show databases;" -s | awk '{ if (NR > 2 ) {print } }' |
while IFS= read -r database; do
    mysqldump --user $USERNAME --password=$PASSWORD "$database" > ~/$directoryName/mysql/databases/$database.sql
done


## 6.0 Zip
##
##
zip -r ~/$directoryName.zip ~/$directoryName/


## 7.0 Delete Directory
##
##
rm -rf ~/$directoryName/

Resources

http://www.linuxforums.org/forum/programming-scripting/45671-creating-folder-current-date-name.html
http://www.thegeekstuff.com/2010/05/bash-variables/
http://stackoverflow.com/questions/6749128/store-output-from-sed-into-a-variable
http://www.tonyspencer.com/2005/11/04/copy-an-entire-directory-in-linux/
http://www.liquidweb.com/kb/how-to-back-up-mysql-databases-from-the-command-line/
http://dev.mysql.com/doc/refman/5.0/en/batch-commands.html
http://unix.stackexchange.com/questions/36516/using-lines-of-output-as-an-array-in-bash
http://www.unix.com/shell-programming-scripting/34299-removing-first-line-output.html
http://unix.stackexchange.com/questions/36516/using-lines-of-output-as-an-array-in-bash