MySQL replicate-rewrite-db ALTER TABLE not rewritten

All we need is an easy explanation of the problem, so here it is.

I have a production database called “master” that I want to replicate on another server under another name (call it “slave”).

All worked fine until an ALTER TABLE statement was performed on the master server. Since then, the replication hung up and I get this error in the slave log:

[ERROR] Slave SQL: Error 'Table 'master.students' doesn't exist' on query. Default database: ''. Query: 'ALTER TABLE `master`.`students`

Well of course it does not exists since I told MySQL to rename “master” to “slave”! But it seems like MySQL decided not to rename the database in ALTER TABLE statements.

Here is an excerpt from my slave config.

server-id   = 2
relay-log   = /var/log/mysql/mysql-relay-bin.log
log_bin     = /var/log/mysql/mysql-bin.log
binlog_do_db   = slave
replicate-rewrite-db="master->slave"

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It turns out I was using MySQL Workbench and it does not output a “USE” statement in its DDL update scripts, also rewriting does not occur if you do not provide a use statement.
So adding USE master; at the beginning of the script fixed it. Thanks!

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply