Mysql backups

I have found the most wonderful tool for backing up mysql databases. It's not new... it's just that I've only found it recently!

automysqlbackup does exactly what is says on the tin - it backs up your mysql database. There are some great todos, but I thought I'd post what I did on out Plesk servers so that I've got it noted down for the future!

• sudo -s
• mkdir /var/mysql
• mv automysqlbackup /usr/local/bin/
• ln -s /usr/local/bin/automysqlbackup /etc/cron.daily/automysqlbackup
• joe /etc/crontab (edit the time that the daily cron takes place and save)

When setting up automysqlbackup, these are the settings I used:

### START CFG ###

# Username to access the MySQL server e.g. dbuser

USERNAME=admin

# Password to access the MySQL server e.g. password

PASSWORD=YourAdminPasswordForPlesk

# Host name (or IP address) of MySQL server e.g localhost

DBHOST=localhost

# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"

DBNAMES="all"

# Backup directory location e.g /backups

BACKUPDIR="/var/mysql/"

# Mail setup

# What would you like to be mailed to you?

# - log : send only log file

# - files : send log file and sql files as attachments (see docs)

# - stdout : will simply output the log to the screen if run manually.

# - quiet : Only send logs if an error occurs to the MAILADDR.

MAILCONTENT="log"

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])

MAXATTSIZE="4000"

# Email Address to send mail to? (user@domain.com)

MAILADDR="support@domain.com"

# ============================================================

# === ADVANCED OPTIONS ( Read the doc's below for details )===

#=============================================================

# List of DBBNAMES for Monthly Backups.

MDBNAMES="${DBNAMES}"

# List of DBNAMES to EXLUCDE if DBNAMES are set to all (must be in " quotes)

DBEXCLUDE=""

# Include CREATE DATABASE in backup?

CREATE_DATABASE=no

# Separate backup directory and file for each DB? (yes or no)

SEPDIR=yes

# Which day do you want weekly backups? (1 to 7 where 1 is Monday)

DOWEEKLY=6

# Choose Compression type. (gzip or bzip2)

COMP=gzip

# Compress communications between backup server and MySQL server?

COMMCOMP=no

# Additionally keep a copy of the most recent backup in a seperate directory.

LATEST=yes

# The maximum size of the buffer for client/server communication. e.g. 16MB (maximum is 1GB)

MAX_ALLOWED_PACKET=

# For connections to localhost. Sometimes the Unix socket file must be specified.

SOCKET=

# Command to run before backups (uncomment to use)

#PREBACKUP="/etc/mysql-backup-pre"

# Command run after backups (uncomment to use)

#POSTBACKUP="/etc/mysql-backup-post"

### END CFG ###

Plesk, Backup and Crashplan

Finally, I think I've found a good solution for Plesk and backup. For ages, I've been trying to find ways of efficiently backing up server data (www, mail mysql) and have tried to the Plesk solution, TotalBackup from 4psa and other rsync methods. To date, they all create a massive amount of data.

TotalBackup has served us okay. It does manage a backup of the whole server and then performs daily deltas, but each week, we still have a massive backup split up into 1000Mb files moving from server to server to facilitate an offline backup solution. And, potentially, with a server going down, we're still 24 hours out of date. Added to that, the restore process (which needs to knit all the parts of the backup together, apply the deltas and then unzip it, before offering the chance to actually get at your files) and we're in a position that doesn't really allow us the best backup/restore cycle.

I've been using Crashplan at home for a while now and love the product (especially after seeing what Mozy did with their pricing strategy and Crashplan promise not to do the same).

So, why not use the same technology with the servers... that's the plan, and that's what I've been spending the last couple of days thinking about. The plan is to backup:

• /var/www/vhosts (where all the www data is)
• /var/qmail/mailnames (where all the mail is)
• /var/mysql/latest (where my latest mysql db backups are)

For more information about the mysql backup process, see the separate post.

Putty tunnels

In a previous post, I was talking about getting a headless instance of Crashplan setup on an Ubuntu server and then administrating it from a local machine (in that case, Ubuntu). However, I needed to do a similar thing with a windows machine.

Get putty: the best (maybe only) SSH client worth having on Windows.

On the support page at Crashplan, there is some discussion about how to use putty to create the remote connection via a tunnel, but either I'm daft, or it's not quite explicit enough. So, here's a little reminder about creating tunnels using putty.

Make sure that the Crashplan desktop client is not open (the tray icon can continue to run in the background).

Change the config

• edit C:\Program Files\CrashPlan\conf\uk.properties
• uncomment the servicePort line and make sure it reads servicePort=4200
• save the file

NB. Have just found Notepad++ which is a great little replacement for notepad.

Create the tunnel

• open putty
• in the first screen, enter the server details (as you would if just SSHing normally)
• now in the left hand column, under "Connection" and then "SSH" is a tab helpfully titled "Tunnels"
• in the source port box, type 4200
• in the destination box, type localhost:4243
• click Add (don't forget to do this bit!)
• finally, click Open
• log in with your normal user credentials

Check that the port is open

• Start -> Run
• cmd
• telnet localhost 4200 (should not get a message that talks about failed connection)

Open Crashlpan - and you should get a remote connection over an SSH tunnel.

Once you're finished, change back the config file and close the putty session which will close the tunnel.

Missing desktop notification link

When you visit mail.google.com, you should get a helpful hint at the top of the page that relates to the new desktop notifications (which are very cool).

"Click here to enable desktop notifications in Google".

However, while there are links to "Learn more" and "Hide", there is no actual link to setup notification. There *should be* a link to the settings page where you can actually set up notifications.

in reference to: Desktop notifications in Chrome - Gmail Help (view on Google Sidewiki)

Crashplan headless

I've previously mentioned my desire to get headless backups working with Crashplan. However, it wasn't quite right. So, I spent some more time this morning trying to get it working simply and well.

Before I start, here are the references to the work completed:

• Installing Crashplan remotely
• Using a headless server
• Helpful remote access script for Crashplan

Setup the remote server

Install the Java virtual machine - don't use openJRE

sudo apt-get install sun-java6-jre

Accept the licence

Goto the crashplan download site and find out the URL of the latest version

Get the crashplan archive, extract it and change directory

wget http://download.crashplan.com/installs/linux/install/CrashPlan/CrashPlan_3.0.2_Linux.tgz

tar -zxvf CrashPlan_3.0.2_Linux.tgz

cd CrashPlan-install

Install Crashplan as root (if you want access to all directories)

sudo -s

./install.sh

Complete the install process

• Read the EULA
• Hit q to exit the EULA and then agree to it
• Accept the default locations for the binaries, backups and scripts

Finish off the remote install process

• Remove the downloaded files
• Firewall: open up port 4243 on the remote machine

Setup the local machine

To administrate the remote machine, we will be port forwarding via ssh 4200 to 4243 and then using the desktop GUI.

Copy and paste the following script into crashplan/bin/CrashPlanRemote (which is usually in /usr/local/ on Ubuntu).

#!/bin/bash

# Do we have the appropriate input?

if [ -z "$1" ]; then

echo usage: $0 remote-server

exit

fi

# fill in your stuff here:

USER=jeremy

SERVER=$1

# dont change anything down here

ssh -fnNTL 4200:localhost:4243 ${USER}@${SERVER}

PID=$!

# get CrashPlan folder

SCRIPT=$(ls -l $0 | awk '{ print $NF }')

SCRIPTDIR=$(dirname $SCRIPT)

TARGETDIR="$SCRIPTDIR/.."

cd ${TARGETDIR}

#change CrashPlan config to use the tunnel

sed 's/#servicePort=4200/servicePort=4200/g' ${TARGETDIR}/conf/ui.properties > /tmp/CrashPlan-ui.properties

cp /tmp/CrashPlan-ui.properties ${TARGETDIR}/conf/ui.properties

rm /tmp/CrashPlan-ui.properties

#CrashPlan start script - begin

. ${TARGETDIR}/install.vars

. ${TARGETDIR}/bin/run.conf

${JAVACOMMON} ${GUI_JAVA_OPTS} -classpath "./lib/com.backup42.desktop.jar:./lang:./skin" com.backup42.desktop.CPDesktop > ${TARGETDIR}/log/ui_output.log 2 > ${TARGETDIR}/log/ui_error.log

#CrashPlan start script - end

#change CrashPlan config back to default

sed 's/servicePort=4200/#servicePort=4200/g' ${TARGETDIR}/conf/ui.properties > /tmp/CrashPlan-ui.properties

cp /tmp/CrashPlan-ui.properties ${TARGETDIR}/conf/ui.properties

rm /tmp/CrashPlan-ui.properties

#kill (every 4200) tunnel

CMD="ps -eo pid,args | grep 'ssh -fnNTL 4200:localhost:4243' | grep -v 'grep' | cut -c1-6"

PID=`eval $CMD`

kill -9 $PID

Now create a link from somewhere in your path, so that you can run it with the command CrashPlanRemote

chmod +x /usr/local/crashplan/bin/CrashPlanRemote

sudo ln -s /usr/local/crashplan/bin/CrashPlanRemote /usr/local/bin/CrashPlanRemote

And that should be it!

• CrashPlanRemove server.name.here

NB. it's best to have setup passwordless login to the remote server

• /etc/hosts: add a line with the IP address and full.domain.name
• ssh-copy-id -i ~/.ssh/id_rsa.pub full.domain.name

Crashplan remote management

A really helpful how to for managing a remote version of Crashplan on a headless server.

http://innerfusion.tumblr.com/post/616874889/setup-crashplan-to-manage-remote-clients-in-7-steps