[aklug] Re: Remote backup of MySQL database

You lost me after blah blah hosted deals blah blah blah..

On 02/24/2011 08:01 PM, Christopher Howard wrote:
> 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. :)
>

---------
To unsubscribe, send email to <aklug-request@aklug.org>
with 'unsubscribe' in the message body.
Received on Fri Feb 25 09:29:12 2011