SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2003
    Melbourne, Australia
    0 Post(s)
    0 Thread(s)

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

    Last edited by dtra; May 16, 2005 at 00:45.

  2. #2
    SitePoint Zealot newspire's Avatar
    Join Date
    Mar 2005
    Houston, TX
    0 Post(s)
    0 Thread(s)
    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.

    Yes, I know this is silly but that's the way it works.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts