Image via Wikipedia

I don't know very many people that haven't been devastated by the loss of data... Yet I am baffled that millions of professional IT workers still ignore backing up their data. Since computers are great at doing repetitive things like backups.. why not spend 20 minutes setting up your machine to backup your files for you. This guide will be specific to mysql to create a local copy of the backup. Then read my other guide about copying files securely to a remote backup server for the 2nd part. 

Hopefully you are using *nix. If so then you have some great utilities already available to you. Rsync, SSH, Tar, Bash and Cron can produce elegant solutions.

The Setup

Create Backup User

First thing to do is to create a Read Only mysql user to perform the backups. Optionally a second recovery user can also be created. It isn't advisable to perform backups as the root user as common sense and proper procedures dictate that a user should always have the minimum privilages necessary to accomplish the job.

Example: Run the following command at the mysql prompt when logged in as root to grant the minimal privilages needed. 

mysql> GRANT LOCK TABLES, SELECT, FILE, RELOAD, SUPER 
    ->     ON *.*
    ->     TO 'db-backup'@'localhost'
    ->     IDENTIFIED BY 'really-long.and Varied p@$$wrd';

 Install Your Backup Script

 There is a fantastic mysql backup script that is really easy to use and simply wraps the mysql backup utilities with nice features like daily, weekly, and monthly backups. Cycling and optionally emailing a summary of the backup. 

Download

Go here to download automysqlbackup

Install

Install it by copying it to a good location. I use /usr/local/sbin/ or /usr/local/bin depending on if I am an admin on the system, or just a user on a shared host.

make sure to set the permissions as executable.

# chmod +x automysqlbackup

Configure

Simply edit the shell script with your favorite editor. If you don't have one, permit me to recommend VIM :)

# vim /usr/local/sbin/automysqlbackup.sh

  
#=====================================================================
# Set the following variables to your system needs
# (Detailed instructions below variables)
#=====================================================================

# Username to access the MySQL server e.g. dbuser
USERNAME=db-backup 

# Username to access the MySQL server e.g. password
PASSWORD=that-secure password set earlier

# 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"  # OR NAME THEM WITH SPACES SEPARATING

# Backup directory location e.g /backups
BACKUPDIR="/usr/local/backup/mysql" 
# I LIKE THIS DIRECTORY, JUST MAKE SURE PERMISSIONS ARE SET PROPERLY

# 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="stdout"

# 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="youremail@domain.com" 

 Setup Cron to run nightly

create a new file in /etc/cron.daily/ and place these contents into it..

# vim /etc/cron.daily/mysql-backup

#!/bin/bash
/usr/local/bin/automysqlbackup 2>&1

Make sure it is executable.

# chmod +x /etc/cron.daily/mysql-backup

I usually test it simply run that cron file from the command line.

# /etc/cron.daily/mysql-backup