[BNM] Master InnoDB and a slave MyISAM Was: Re: Transferring 60GB of files from one server to another

[Karim Ahmed]

I recommend replication to a physically separate slave server. You can then
lock the slave to do backups as frequently as you like without affecting the
master in any way at all.

I've set this up and it works a treat. Other advantages of this set up are
that you can  test new queries or run heavy queries on the slave. You also
have a live, mirrored database server you can instantly switch to in case of
disaster.

On Wed, Dec 3, 2008 at 12:45 PM, Jay Caines-Gooby <jay at gooby.org> wrote:

> On Wed, Dec 3, 2008 at 10:59 AM, Alessandro De Maria
> <alessandro.demaria at gmail.com> wrote:
> >> Hmmm mysqlhotcopy is only meant to be used for MyISAM and ARCHIVE
> >> tables. Won't do InnoDB, which everyone should be using.
> >
> > true, but what it is usually advised in situation like yours is to
> > have a Master InnoDB and a slave MyISAM, and then to backup from the
> > slave. This would allow you to have a shorted downtime and use
> > mysqlhotcopy.
>
> Hmm. Interesting. We're just about to reach a point where this becomes
> a critical issue for us (backup snapshot from slave).
>
> I'd settled on running a slave (but with all InnoDB as with the
> Master) and shutting the slave down, doing a file copy and keeping the
> mysql datafiles. This is all on AWS, so after shutting the slave down,
> I snapshot the mysql datafiles (which are held on a mounted EBS
> volume) as an S3 bucket.
>
> With your scheme (slave running MyISAM versions of all the tables) and
> running hotcopy, are there any downsides?  The endresult of hotcopy is
> still the backed-up datafiles isn't it. I suppose the benefit is that
> you don't have to shutdown the slave, because you can lock the tables?
>
> If I wanted to restore from a slave copy of the MyISAM versions of the
> table to a InnoDb master are there any issues?
>
>
> --
> Jay Caines-Gooby
> jay at gooby.org
> +44 (0)7956 182625
> skype: jaygooby
> gtalk: jaygooby at gmail.com
> AIM: jaygooby
> --
>
> BNM Subscribe/Unsubscribe:
> http://www.brightonnewmedia.org/options/bnmlist
>
> BNM powered by Wessex Networks:
> http://www.wessexnetworks.com
>