On Thu, Aug 23, 2001 at 03:19:17PM -0700, Bryan Coon wrote:
> Hi,
>
> We have a somewhat tricky problem that I need some help with. What
> we have is one machine (the master) that we use for database
> development, and two dedicated nodes (slaves) on a beowulf cluster
> that house the non-dev databases.
>
> What we need is to mirror the master to the slaves, but not
> necessarily moment to moment. Once a day or even once a week would
> be fine.
>
> I originally set up replication which worked just fine for a short
> time. But then I noticed that replication was crashing 3 or 4 times
> a day. Yuck!
That's clearly not good. Were the servers running the same version of
MySQL? What sort of "crash" did you see? What error messages
appeared in the error logs on the slaves?
> I think it had to do with the heavy development on the master- this
> machine is used to read in new tables from txt files, create loads
> of temporary tables and in general get beat to hell. It seemed that
> the slaves were choking on some particular line from the binary
> logs, and could not get going again.
Could be.
> Speaking of which, how to start replication at the next 'good' line
> number? I couldn't figure out how to find that line number.
Here
http://www.mysql.com/doc/R/e/Replication_SQL.html
you'll see SET SQL_SLAVE_SKIP_COUNTER. You can use that command to
skip a bad query.
> Anyways, I scoured the docs, but could not find a list of what
> happens for example if the master loads a new table from a text
> file. Does this command 'read ... from infile...' go into the
> binary logs?
It does. You can use the mysqlbinlog program to view what's actually
in the binary log, if you're curious.
> What happens to the slave when it tries to read in a text file that
> doesnt exist?
It generates and error, and replication [probably] fails.
> What kind of errors can the slave deal with, and which ones cause
> replication to fail?
Replication is fairly sensitive (and conservative). It stops on
most errors, from what I've seen.
> For a while, I just figured replication was a bad idea with a
> development db as the master.
It may be in your case. You aren't interesting in having the slaves
run all those queries themselves--you're just interested in having
them be in sync once in a while, right?
> But then when I tried to find alternatives, I hit another brick
> wall. A bit off topic, but how does one take a 'snapshot' of the
> master db and migrate that out to the slaves?
There are a couple ways.
You can use rsync to transfer the data.
You can use myrepl (a script I posted a while ago). I wrote it for
taking replication snapshots. But it assumes that the "master" is
actually running a binary log.
You can use tar and scp.
> Besides the obvious and (imho clumsy) tar, gzip and scp of about 4
> gigs of data, I thought of adding the entire db to cvs, but then
> there are inherent problems with that ( try adding a 1.2 gig table
> to a repository when you only have 256 megs of ram :P)
CVS would be a bad idea. It's not too good at handling binary files.
And you probably don't want to dump the data just to put it in CVS.
> Is replication the right tool for this job? If it is, what is the
> best model for our needs? If not, does anyone have any other
> suggestions?
Based on what I think you're saying, replication may not be best for
you. Perhaps a periodic rsync (or something similar) would work
better for you.
Hope that helps...
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.41-max: up 6 days, processed 88,300,780 queries (149/sec. avg)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
