[aklug] Re: Remote backup of MySQL database

On 02/23/2011 09:03 PM, Christopher Howard wrote:
> It's been a few years since I worked much with MySQL, but is there a
> handy (open-source) tool these days for remote backup of a single MySQL
> database? IIRC, mysqldump would do the trick, but it seems on my
> distribution that you have to install the whole bloody MySQL server just
> to get that program. Googling brought up some scripts where you do the
> dump on the remote server, and then copy the file over to your machine,
> but that is a bit less elegant than I would prefer.
>

Set a write lock on all tables.. rsync the mysql db directory elsewhere.. check out the
--append and --append-verify options. You want to move those dbs off to a static state
somewhere else locally before pushing them out to a remote location so you can unlock them
quickly.

Alternatively use a snapshot capable filesystem or volume manager (or both!) and set a
write lock.. do a snapshot.. release the lock.. mount a snapshot.. do a backup.

One of the problems with backing up a database is that if you aren't using a standard SQL
format then binary differencing can be a problem when trying to transfer things off
quickly. Therefore one of the most straight forward approaches I can offer anybody in
this community for fast backups of MySQL databases is utilizing the partitioning methods
which restrict writes to a single smaller file instead of writing to the same large file
over and over again. This keeps me from having to dump everything out into text sql
format and spend hours loading in massive amounts of data as well as rebuilding indexes.

In my work I have dates per row for records for call data. I use the day of the record as
the hash ID for what file to write to.. and 20 "partitions" based upon that. If you back
up every day you really only need 2 partitions to start taking advantage of not backing up
yesterdays data :) I like tiny files however..

- Shane
---------
To unsubscribe, send email to <aklug-request@aklug.org>
with 'unsubscribe' in the message body.
Received on Thu Feb 24 10:00:49 2011