Tuesday, February 22, 2011

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 ###

2 comments:

Jeremy Jones said...

The password for plesk is available using cat /etc/psa/.psa.shadow

ianchanning said...

For those that didn't know (me :)) the config directory is in /etc/automysqlbackup/automysqlbackup.conf