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
--
TriLUG mailing list : http://www.trilug.org/mailman/listinfo/trilug
TriLUG Organizational FAQ : http://trilug.org/faq/
TriLUG Member Services FAQ : http://members.trilug.org/services_faq/
