[PLUG] Mysql newbie question

[Paul Munday]

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