Backing up a MySQL server

Just wanted to paste a small script here, which dumps and gzips all databases hosted on a MySQL instance.
Make sure, that this script is not readable for everybody, as it contains credentials.

dumpdbs.sh



#!/bin/bash
#
# MySQL database dump
#
# - Takes MySQL Dumps of all available databases
# - Only keeps one backup in the dumpfolder
# -> uncomment the LOG variable and the pipes to the tee command for logging to a file
#
# 2011, Looke
#

# Setup
DBUSER=root
DBPASS=xxx
DBDUMPDIR=/dbdumps
DBDUMPDATE=$(date '+%d-%m-%Y')
# LOG=/var/log/dumpdbs

# Create/Empty DBDUMPDIR
if [ ! -d $DBDUMPDIR ]; then
	echo $(date +"%d/%m/%Y %T") INFO: $DBDUMPDIR not found, will create it... #| tee -a $LOG
	mkdir $DBDUMPDIR
else
	echo $(date +"%d/%m/%Y %T") INFO: Emptying $DBDUMPDIR... #| tee -a $LOG
	rm -f $DBDUMPDIR/*
fi

# Loop through all databases available and dump them (gzipped)
for DBNAME in $(echo "show databases;" | mysql --user=$DBUSER --password=$DBPASS -s)
do
	echo $(date +"%d/%m/%Y %T") INFO: Dumping $DBNAME as ${DBNAME}_${DBDUMPDATE}.sql.gz... # | tee -a $LOG
	mysqldump --user=$DBUSER --password=$DBPASS $DBNAME | gzip -c > $DBDUMPDIR/${DBNAME}_${DBDUMPDATE}.sql.gz
done

This script does a good job together with Bacula. Here the Job resource in Bacula


Job {
  Name = "Backup MySQL DBs"
  ...
  Client Run Before Job = "/opt/bacula/scripts/dumpdbs.sh"  
  ...
}

Application backup – Scripted pre- and postbackup actions

Many of today’s businesses rely heavily on their application servers. The times of simple fileshares and single-document based processes are over and with them the time of simple filecopy as a method of backing up is over.

In this article I want to describe a method to backup the two most common components of a modern application service: filesystem and database.
No matter how you solve your backup, the approach should always make sure, that the database integrity is given and that the filesystem is in sync with the database state.

The following two scripts are deployed as a Pre- and a Post backup script. The Pre- Backup script stops the application, dumps the database contents, creates a LVM snapshot and re-starts the application. Post- Backup removes the snapshot.

The fileset for the backup application would then look like this:
Database dumps: /dbdump
Filesystem snapshot: /volume-snapshot

appbackup-run-before.sh


#!/bin/bash
#
# Application Service Backup - Part 1 of 2
#
# Pre-Backup script
# - Stops Service
# - Takes a MySQL Dump
# - Creates a LVM Snapshot
# - Restarts Service
# - Mounts the LVM Snapshot
#
# 2010, Looke
#

# Which service to mess with
SERVICE="service"

# LVM Stuff
LVMVOLUME="/dev/lvm/volume"
LVMSNAPSHOT="volume-snapshot"
LVMSNAPSHOTSIZE="50G"

# MySQL Properties
DBDUMPDIR="/dbdump"
DBNAME="aaa"
DBHOST="zzz"
DBUSER="xxx"
DBPASSWORD="yyy"

echo "Shutting down Service..."
/etc/init.d/${SERVICE} stop

while ps ax | grep -v grep | grep ${SERVICE} > /dev/null;
do
  echo "...stopping..."
  sleep 5
done

echo "Creating MySQL Dump..."
if [ ! -d "${DBDUMPDIR}" ]; then
  mkdir -p ${DBDUMPDIR}
fi
mysqldump --host=${DBHOST} --user=${DBUSER} --password=${DBPASSWORD} ${DBNAME} > ${DBDUMPDIR}/${DBNAME}.sql

echo "Creating LVM Snapshot..."
modprobe dm-snapshot
lvm lvcreate --size ${LVMSNAPSHOTSIZE} --snapshot --name ${LVMSNAPSHOT} ${LVMVOLUME}
sleep 5

echo "Restarting Service..."
/etc/init.d/${SERVICE} start

while ! ps ax | grep -v grep | grep ${SERVICE} > /dev/null;
do
  echo "...starting..."
  sleep 5
done

echo "Mounting LVM Snapshot..."
if [ ! -d "/${LVMSNAPSHOT}" ]; then
  mkdir -p /${LVMSNAPSHOT}
fi
mount -o ro /dev/lvm/${LVMSNAPSHOT} /${LVMSNAPSHOT}

exit 0

appbackup-run-after.sh


#!/bin/bash
#
# Application Service Backup - Part 2 of 2
#
# Post-Backup script
# - Unmounts the LVM Snapshot
# - Destroys the LVM Snapshot
#
# 2010, Looke
#

# LVM Stuff
LVMSNAPSHOT="volume-snapshot"

echo "Unmounting LVM Snapshot..."
umount /${LVMSNAPSHOT}

echo "Destroying LVM Snapshot..."
lvm lvremove -f /dev/lvm/${LVMSNAPSHOT}

exit 0

One backdraw of this method is, that the service has to be stopped in order to get a consistent state of the data. If the service has to be online 24/7 you would have to consider clustering (anyways, you would have to come up with something to cover unplanned downtimes).

Here is a small excerpt to show you how to configure the Pre- and Post backup scripts with the open source backup software Bacula. I assume if you use some other backup software, you can click your way through the GUI yourself :)



Job {
  Name = "Appbackup"
  ...
  Client Run Before Job = "/opt/bacula/scripts/appbackup-run-before.sh"
  Client Run After Job = "/opt/bacula/scripts/appbackup-run-after.sh"
  ...
}

Useful links:
Bacula Documentation – Job Ressource
Ubuntuusers Wiki – LVM (german)

A small webchat using JavaScript, PHP and MySQL

During my studies, I had some lectures about Ajax driven web-services in the second or third semester and I thought, that I can build something like that on my own. So I started this little chat project and gave it a try.

Used technologies:
JavaScript (for Ajax action), PHP and MySQL, HTML and CSS
(Thats also where the name comes from – AMPChat – AjaxMysqlPhpChat)

The working mechanism is the following (simplified):
A JavaScript function polls a PHP script, telling it which chatmessages it already knows. The PHP script checks, whether there are new chatmessages stored in the database or not and sends its response – an XML with either no records (if no new messages are around) or one with the missing chatmessages. The JavaScript then writes the new messages in a DIV element of a website.

The chat is available under the following link:
http://looke.ch/amp
user: guest
password: 123123

The whole thing is open-source. You can find the code here: http://looke.ch/amp/source.php

Displaying custom markers with Google Maps

In this example, I try to explain you how to embed custom markers, containing data from an external datasource (in this case a MySQL database) in a Google Maps map, like the example below:

To test my code yourself, you need the following:

  • A Google Maps API key (get one here)
  • A Webserver capable of running PHP and MySQL (for development I recommend XAMPP)
  • Little knowledge of PHP and JavaScript

Lets call the project MapSomeStuff.
The whole thing consists of two files, a JavaScript file, wich contains the most of the calls to the Google Maps API and a PHP file, containing my MapSomeStuff class wich gets data from the database and prepares it to be put on the map.

Technical backgrounds

In a first step, we take the address of our marker- entry and pass it over to the Google Maps API to translate it to latitudinal and longitudinal (lat and lng) coordinates (wich is essential in the next step).

Now the data, wich is later displayed on the map as a marker is being read from the database and formated as XML- output (look out for the lat and lng attributes):

<markers>
<marker name="Basel SBB" street="Centralbahnplatz" nr="1" town="Basel"
 zip="4051" country="Switzerland" description="Railwaystation Basel SBB"
 lat="47.5482662" lng="7.5909349"/>
</markers>

In my example we still have the address in the XML dataset. This is only additional information for the user. In fact, Google Maps only needs the lat and lng attributes to place the marker on the map.

The XML gets sent to the Google Maps API, wich then places the markers on the correct coordinates.

The code

To avoid ruining the site’s look, I link the code. Just downlad the file, rename accordingly and put them all in the same directory:
mapsomestuffphp – containing the PHP class and HTML parts. Rename to mapsomestuff.php
mapsomestuffjs – containing the API calls. Rename to mapsomestuff.js
mapsomestuffsql – containing the database structure and an initial dataset