[PLUG] Mysql newbie question

[Robert Citek]

If you are sure about the zero users between midnight and 6AM, then
you have a lot of options.  From easy to more challenging:

1) stop mysql and rsync /var/lib/mysql
2) run a mysql dump (e.g. apt-cache search automysqlbackup)
3) create slave replica and run backups from the slave
4) stop mysql, create an LVM snaphot, start mysql, rsync from snapshot

The key in each case, though, is that you need to verify that you can
restore.  It won't matter what method you choose or how good you
believe your backup to be, if you can't restore.

We do this kind of stuff regularly for all kinds of clients.  If you
want to off-load some of the heavy lifting, shoot me an e-mail
off-line.

Regards,
- Robert


On Sat, Jun 22, 2013 at 9:22 PM, Paul Munday <paulm at freegeek.org> wrote:
> On Sat, 2013-06-22 at 17:49 -0700, Keith Lofstrom wrote:
>> On Fri, Jun 21, 2013 at 03:39:18PM -0700, Russell Senior wrote:
>> > replication and it'll be great!"  The message was, paraphrasing, "it's
>> > not magic, and it won't magically solve all your problems", and "make
>> > good backups".
>>
>> Sophisticated questions and answers.   Perhaps someone can point
>> me at a not-too-complicated tutorial which quickly summarizes
>> what is simpler and harder to do with mysql backup/mirroring.
>>
>> I'm using an app that uses mysql, and I know too little about
>> mysql for safety.  Especially backup and mirroring.  But wuth
>> way too much to do, I don't have time to become an expert.
>>
>> I'm guessing mysql is getting 20 reads and 5 writes per minute
>> during office hours, with zero user load between midnight and 6AM.
>>
>> At minimum, I want to do the right thing to image the database to
>> backups at 3 AM.  I rsync them to two different backup servers, but
>> I don't know that the databases will recover to a viable state.
>> Is there something I should be doing to freeze the database?
>
> mysqldump dumps out the database contents as sql statements that can
> be loaded into another database (not just mysql), it does the whole data
> base at once
> though you can use diff to make it incremental, you can also enable
> binary logs to make
> incremental backups(though I would still do regular full dumps)
>
>
> https://dev.mysql.com/doc/refman/5.1/en/backup-methods.html
>
>> At maximum, I would like to do realtime imaging to a failover
>> server on a different network far far away.  About 100kbps between
>> a couple of lightly loaded machines with solid state drives.
>> In the rare event that I lose the primary server, I expect to
>> lose some recent activity, and spend an hour or two recovering.
>>
>> What's reasonable to expect from a maintainable-by-dummies backup
>
> mysqldump would get you reliable backups with little effort and it would
> be quick
> to get up and running
>
> In fact you should do at least one asap
>
> mysqldump -u root -ptmppassword database_name > database.sql (single
> database)
>
> mysqldump -u root -ptmppassword --all-databases > all-database.sql
>
>> and/or failover process for mysql?
> given the load a master /slave setup would be fine for your purposes and
> it is
> possible to do it remotely /asynchronously.
>
> Here's some pointers
> https://dev.mysql.com/doc/refman/5.0/en/replication.html
> (I'm a little rusty on the details since its a while since I've had to
> set it up
> but it wasn't too hard)
>
> Paul
> --
>                Technical Support Specialist, Free Geek
>               Free Geek Tech Support: support at freegeek.org
>         (503) 232-9350 option 6 Tuesday-Saturday: 12-1,1:30-5:45PM
>
> _______________________________________________
> PLUG mailing list
> PLUG at lists.pdxlinux.org
> http://lists.pdxlinux.org/mailman/listinfo/plug