[TriLUG] MySql replication

Shawn Hood shawnlhood at gmail.com
Thu Jul 6 15:41:07 EDT 2006

It's been a while for me, but it's pretty easy, and works well.  I
think there are several tools out there that take snapshots of hot
MyISAM data without any problems.  I think they do this by utilizing
the binary log that MySQL creates if the option is enabled.  There is
some overhead associated with enabled the binlog.

You can freeze the database using "flush tables with read lock"  and
copy the hierarchy over as long as the data file format is the same
among the versions of MySQL on the master and slave.  There is some
info out there about this.  Another option is to run mysqldump
(--all-databases?) > dumpfile.sql, and load that file using the mysql
utility on the slave.  There is also a command that will sync the
master and slave, starting from scratch.  This takes quite some time
on a large database.  I've had the best experience copying over the
file hierarchy and keeping the versions of MySQL similar.

You will need to modify my.cnf, get the data moved, and then issue a
START SLAVE *AFTER* you issue some other commands telling the slave
what relay log to use, the offset, and authentication info, etc.

There is plenty of good information available on the web on this as
well.  I consumed a lot of it when I was a db admin.

On 7/6/06, Rick DeNatale <rick.denatale at gmail.com> 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?
> --
> Rick DeNatale
> IPMS/USA Region 12 Coordinator
> http://ipmsr12.denhaven2.com/
> Visit the Project Mercury Wiki Site
> http://www.mercuryspacecraft.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/

Shawn Hood
(910) 670-1819 Mobile

More information about the TriLUG mailing list