Backing up MySQL

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.

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.

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

Conclusion

Remember that a backup is only as good as it’s restore. What we have done here should be considered
the bare minimum and will result in some data loss if it’s needed.. How much data loss?
Up to one days worth of changes as that’s how frequently we are backing up things.
If we wanted to shrink that window then run the script more often, but recognize that cost for this
is the data retained will grow in size.

Fri Dec 12, 2008

700 Words

Read in about 3 Min

About the Author:

Steve Francia has been responsible for two of the largest commercial open
source projects as the current Chief Operator of the Docker Project and the
former Chief Developer Advocate of MongoDB. Steve has also run some of the top
community-based open source projects: spf13-vim, Hugo, Cobra & Viper

He loves open source and is thrilled to be able to work on it full-time and
then some. In writing, Steve tweets as @spf13,
codes on GitHub,
blogs at spf13.com,
connects on LinkedIn,
ocassionally posts on Google+
and has written a few books for O'Reilly.

In person, he enjoys giving talks and workshops and spending time with the
developer community around the world. When not coding, he is usually having fun
outdoors with his wife and four children.