[TriLUG] MySql replication

[Rick DeNatale]

On 7/6/06, Shawn Hood <shawnlhood at gmail.com> wrote:
> 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.

mysqlhotcopy perhaps?

Most of the tables in my databases are myisam, mostly because when I
got started on an old redhat system, I don't think that innodb was
supported.  I have been thinking of converting most of the tables for
my mediawiki database to innodb (one is a text search index and needs
to be in myisam).

> 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.

I'd love to get pointers to good ones, I tend to read a lot of how-tos
on things like this before I act.



-- 
Rick DeNatale

IPMS/USA Region 12 Coordinator
http://ipmsr12.denhaven2.com/

Visit the Project Mercury Wiki Site
http://www.mercuryspacecraft.com/