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

StackExchange Database Administrators Q#13093, answer score: 5