I have replication setup that has been working great. We have a master (prod) and a slave (reporting) that have been running for several weeks. We are changing the slave database through replication and that has been running well. That is until an ALTER TABLE statement was replicated.
As you can see in the error statement, it looks like the --replicate-rewrite-db option was not applied and that caused the failure. But why would this work for months and fail with just the ALTER TABLE statement. Also what do I need to do with the replication options so this will work properly?
Slave SQL: Error 'Table 'prod.user' doesn't exist' on query.
Default database: 'reporting'. Query: 'ALTER TABLE `prod`.`user`
ADD COLUMN `new_col` TINYINT(1) NOT NULL DEFAULT '0'', Error_code: 1146
replication settings from my.cnf:
replicate-wild-ignore-table = mysql.%
replicate-rewrite-db = prod->reporting
replicate-do-db = reporting
replicate-ignore-table = reporting.user
