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 ?
Best Answer
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:
Or, you could try skipping all errors that reference that table
Another way to do this would be to set
replicate-ignore-db=database2and restart MySQL on the slave, but there are some caveats to how that works that you should read about in the documentation