The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

replication tips

hi all

i have been trying to get replication working on a couple of servers we have
i got it up and running and have been doing some basic testing, but if a query runs on the master that has an error in it
the slave writes to the error log and quits
i read on the mysql.com site and it says you need to manually change it
we have a lot of tables on our site, and it wouldn't be very fun to go through and check each table to make sure they're all sync'ed
is it possible for the slave to keep going after errors? (ie. assuming the error just couldn't insert (so nothing was inserted, or updated or deleted, etc) so in essence there was no change)
cos i would assume on our live server there would be a fair number of bad queries (that's another post)

also, on the errors thing
the replication slave tries to replicate databases that it's not meant to
i've got all the replicate-ignore-db=dbname in the my.cnf of the slave for all the databases i don't want to replicate (which is all bar one in testing)
and also binlog-ignore-db=dbname for all databases i don't want on the master server
they are exactly the same
how does that work?

I replicate 100's of tables from one server to multiple servers around the country.

Bad queries that do not execute on the master do not get run on the slaves or stop the slaves. If a query executes on the master it must run on the slave and complete successfully or the slave is going to stop. If it were to keep going, which is won't, the tables would be out of sync.

You said "if a query runs on the master that has an error in it..." If the query had an error in it, it did really run on the master right? Only queries that successfully run on the master are replicated to the slaves.

I've had the same problem with queries that seem to slip past the ignor or include directives. The problem because you are not using the USE database command. Lets say db A is replicated and db B is not. When you connect to the database server and give it a specific database MySQL silently does a use command.

Connect to MySQL A (mysql silently does a use A; command)

insert into a1 ... This will get replicated
insert into B.b1 ... This will also get replicated.

What you need to do is this
insert into a1...
use B;
insert into b1... This will not get replicated.