Q:

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 ?

A:

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

A:

I think the bigger problem here is your default database context was database1.  Thats's why  your slave tried to execute the update on database2 since it was specified in database2.table format.
Basically it's not safe to user db.table syntax with wildcards or you find yourself in the situation you did.  If you're wanting to use the wildcard do or ignores it's generally safer to always specify your default db using "use" and execute the query in that context.

