[aklug] Re: Remote backup of MySQL database

On 02/24/11 10:00, Shane R. Spencer wrote:
> 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.
>

You started to lose my comprehension about half way through the second
paragraph. All I see is "blah blah partitions blah blah hash ID blah
blah tiny files." :D

In any case, I don't think I have access to the MySQL db directory. This
is one of those hosted deals where I get a directory for web space and
create a db through CPanel. And they'll definitely cancel my service if
I try hacking into partitioning. :)

-- 
frigidcode.com
theologa.indicium.us
---------
To unsubscribe, send email to <aklug-request@aklug.org>
with 'unsubscribe' in the message body.