[TriLUG] MySql replication

[Chander Ganesan]

Rick DeNatale wrote:
> I've been meaning to set up a slave mysql server so that I can leave
> the main server running while taking backup snapshots from the slave.
>
> I just discovered the fact that MySql seems to make it pretty easy to
> have multiple instances of the daemon running on the same machine, so
> I'm starting to think more seriously about this.
>
> Has anyone done this?  I'm wondering what's involved in getting a new
> slave up to sync with a master with long-lived existing databases.  Do
> I copy the database directory hierarchy somewhere, configure the slave
> to go there and use different ports, then add binary logging to the
> master and hook up the slave to it?
>
There are a few ways to accomplish this.  First make sure that binary 
logging is enabled on the master - this is a requirement - before you 
can do anything else.

    - If you have a filesystem that supports snapshotting, take a 
snapshot of the file system/database.  Copy the snapshot to your new 
instance, configure a new mysql config file that changes the socket file 
location and the default port (3307 is probably a good idea, I think the 
default is 3306).  Issue a 'show master status' on your "new" slave 
server (which was started with your snapshotted backup).  Note the log 
name and position.  Use this when you configure it as a slave.

    - If you would rather not do that, use mysqldump with the 
'--master-data' option.  Pipe it into the mysql client that is connected 
to the slave.  Configure the miscellaneous settings on the slave (master 
password, host, etc.)  The rest (log position) will be done by the 
mysqldump application.

    - You can also use logshipping (so whenever a checkpoint occurs your 
binary log is replicated onto your slave).  This is outside of MySQL's 
normal replication, but is less resource intensive.  I'd recommend this 
if you just wanted a "nightly backup" type solution.  Let the system run 
all day long, then issue a checkpoint and ship the logs at night.  You 
then have a slave that has a "nightly" backup of the master.  You can 
also perform point-in-time recovery with the next days binary logs.

If you just want to keep from shutting down the server, you might 
consider snapshotting with something like EVMS, making a point-in-time 
backup, and then restarting the server.

I wouldn't recommend putting the slave on the same system as the master 
- though it is doable. 

As an alternative, we've got a MySQL Admin class in two weeks that 
covers all this stuff ;-)

http://www.otg-nc.com/training-courses/coursedetail.php?courseid=6

-- 
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com