[ec2] Best practice for setting up MySQL on ubuntu?

[LocaleMinder.com]

We've got an almost identical setup to the tutorial and we recently endured 
several minutes of downtime whilst trying to grow an EBS volume because the 
volume didn't detach from the instance cleanly. We had to hack our scripts 
quickly to alter the device the new volume would be attached to.

But our plan to prevent any downtime in the future is to bring LVM into the 
mix. But our volume group will only ever contain the one EBS volume so we can 
still use EBS snapshots. Basically we'll just use LVM to migrate from the 
smaller volume to the larger volume. The steps will be:
1. Create a new EBS volume of the required size.
2. Expand the volume group to encompass the new and old volume.
3. Use pvmove to move logical extents to the new volume.
4. Shrink the volume group to remove the old small volume.
5. Grow the logical volume and the filesystem.

Viola. No downtime. New EBS volume.

I would like a copy of the preview copy of the new snapshot program though 
please!

David.

On Saturday 22 August 2009 08:01:28 Barry Jaspan wrote:
> Eric,
>
> > It is still possible to grow an EBS file system without LVM if
> > you're willing to have a short amount of downtime, probably in the
> > seconds range if done right. (If folks are interested in how to do
> > this, let me know).
>
> Sure, I would like to know how to do that.  We're using an active/
> passive dual-master MySQL setup with application-level failover, so we
> can handle taking down our db master one at a time to grow the EBS
> volume.  I was assuming we'd have to mount a new EBS volume, shut down
> mysql, move /var/lib/mysql, and restart the server.
>
> But that will certainly take more than a few seconds, so I'm
> interested in your technique.
>
> Thanks,
>
> Barry
>
> > The other reason folks use LVM with EBS is to enable suspending the
> > file system during an EBS snapshot if they are using ext3.  With EBS
> > I generally recommend using XFS instead of ext3 as it provides an
> > integrated file system freeze and works better on RAIDs of EBS
> > volumes if you get to need that for peformance.  The tutorial
> > demonstrates with XFS.
> >
> > The sample snapshot driver script linked to from the above tutorial
> > works for simple setups, but I have rewritten and enhanced the
> > software dramatically over the last year based on many conditions
> > and interactions we found in real world production use.
> >
> > I will be publishing the updated snapshot program as open source,
> > and in the meantime am providing private preview copies to anybody
> > who would like to test it in their own environment.
> >
> > --
> > Eric Hammond
> >
> >
> > -----Original Message-----
> > From: Lucas Gates <lucasbgates at yahoo.com>
> > Date: Friday, Aug 21, 2009 11:05 am
> > Subject: [ec2] Best practice for setting up MySQL on ubuntu?
> > To: ec2 at lists.ubuntu.com
> >
> > Hi,
> >
> >> Some time ago I read on this list that someone was working on a
> >> tutorial or script that would make backups of mysql databases via
> >> EBS. I believe the solution involved LVM volumes and snapshots.
> >> Does anyone know if that is available to the public yet?
> >>
> >> Thanks!
> >>
> >> Regards,
> >> Lucas Gates