mysql replicate a view won't work

0
votes

I am trying to replicate a specific view on mysql slave while ignoring the base table. I have created a view that select * from a specific table on a specific DB. In the slave my.cnf I have restricted the replication to the following:

replicate-do-db=DBNAME
replicate-ignore-table=TABLENAME

When I start the replication on the slave, I get an sql error :

Last_SQL_Error: Error 'TABLENAME 'DBNAME.TABLENAME' doesn't exist' on query. Default database: 'DBNAME'. Query: 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `TABLENAMEVIEW` AS SELECT * FROM TABLENAME'

I am using mysql 5.5.1 and as per the following http://dev.mysql.com/doc/refman/5.5/en/replication-features-views.html a view can be replicated even if the table is ignored.

Any idea how I can solve this ?

Thanks,

1
mysqlviewdatabase-replicationperconamysql-5.5

1 Answers

2
votes

Without the base table, the VIEW won't work. You can ignore the base table in replication so that new data doesn't come in but that table must exist for the VIEW to function.

VIEWs in MySQL are nothing more than simple aliases for SELECT statements. They are not materialized, no data is stored within, and thus execute their underlying SELECT each time you SELECT from the VIEW.