MySQL slave only views

Question

We are kind of struggling while creating the infrastructure for our BI sector.

I had read on this link that it is possible to create views on a read-only slave. Still, when I run the query I get

"The MySQL server is running with the --read-only option so it cannot execute this statement."

Is there any way to acomplish this?

Or if it's not, is it possible to be able to have 2 MySQL masters one way sync, without loosing our views? (It's kind of a fake slave).

Our problem is that we won't nor want write access to the master server in order to create our views, and just in case both options aren't possible, is any way to achive something similar?

• Rick James: Did that error message come from CREATE VIEW? Or from using the view? (Your question is ambiguous.)
• Juan: From the view creation.

Answer 1

It should be possible to make the slave read/write by disabling read_only. SET GLOBAL read_only=0, and changing it in the my.cnf configuration file.

Using GRANT you can create a user with SELECT, CREATE VIEW at

GRANT SELECT, CREATE VIEW ON *.* TO bob@localhost;

This should leave the user with enough access to do BI work on a slave without modifying data.