MySQL replication: "Houston, We've Got a Problem" I ran into a problem with our replication server. Essentially, we have 2 databases (database1 and database2). Master server has both. Slave has only database1. There is a

Replicate_Do_DB: database1


set in CHANGE MASTER TO configuration.

Now what happened is - we are using code igniter, and one of the programers created database2 and started inserting info into it. Code igniter sets a default database to database1. Now the result is for every query he produced - I get an error on SHOW SLAVE STATUS\G:

Error 'Table 'database2.tbl40' doesn't exist' on query. Default database: 'database1'. Query: 'INSERT INTO `database2`.`tbl40` (`date`, `day`) VALUES ('2011-04-26', '2011-04-26')'


So essentially, I he fixed the problem afterwards, but the replication doesn't work as there is around 1000 queries that will produce that error for replication server.

My question is - is there some way to clear queries like that from the binlog?
Or I need to write a script that will do a 

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;


for every query that produces and error ?
If you really don't care about that table, you can use pt-slave-restart on the slave and have it skip those problems.  I would be conservative about running it and make sure that you are only skipping queries for the table/database that you don't care about or at least for only a specific error.

You didn't post what the error code was in the output from SHOW SLAVE STATUS, but I suspect it is error 1146.

For example, this will skip all errors for 1146:

pt-slave-restart -u root -p pass --error-numbers 1146


Or, you could try skipping all errors that reference that table

pt-slave-restart -u root -p pass --error-text 'database2'


Another way to do this would be to set replicate-ignore-db=database2 and restart MySQL on the slave, but there are some caveats to how that works that you should read about in the documentation