Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to make my MySql slave read only, i've been looking and found the read_only option, but its says that users with super privalges can still write (if i understand the text correctly), here is the grants for my app:

GRANT RELOAD, PROCESS ON *.* TO 'my_app'@'%' IDENTIFIED BY PASSWORD '*'
GRANT ALL PRIVILEGES ON `my_app`.* TO 'my_app'@'%' 

will the app be able to write to the slave?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

Here are the grants you just mentioned

GRANT RELOAD, PROCESS ON *.* TO 'my_app'@'%' IDENTIFIED BY PASSWORD '*'
GRANT ALL PRIVILEGES ON `my_app`.* TO 'my_app'@'%' 

Based on this, you should not be able to write to the Slave because SUPER is a system level privilege (SUPER only appears in mysql.user as Super_priv) and not a DB level privilege (SUPER does not appear in mysql.db). The first GRANT lacks SUPER privilege. The second GRANT does not have a SUPER privilege context at all.

Having SUPER privilege has a lot of firepower because you can run the following

  • CHANGE MASTER TO
  • KILL
  • SET GLOBAL
  • PURGE BINARY LOGS

Not every user needs this. Giving the SUPER privilege to just anyone can actually hamper a DBA from logging into mysql if max_connections is reached.

share|improve this answer
add comment

Arbitrary or unintended updates to a slave may break replication or cause a slave to be inconsistent with respect to its master. Making a slave read_only can be useful to ensure that a slave accepts updates only from its master server and not from clients; it minimizes the possibility of unintended updates. Set read_only=1 in your my.cnf/my.ini file to ensure that a slave accepts updates only from its master server and restart your server.

Also I think you should avoid giving unnecessary SUPER privilege to other MySQL users for safety.

share|improve this answer
    
and will my app, who have ALL privileges be able to write to the slave? –  Ran Mar 6 '12 at 10:33
    
Yes .. it will write on SLAVE because it has SUPER privilege, You should try to avoid using SUPER privilege for MySQL user which is used in your app. Actually it's not recommended to allow write on SLAVE databases which can conflict with MASTER data and hence break in replication process. –  Mahesh Patil Mar 6 '12 at 13:02
    
No. All privileges is not super. Super is only granted explicitly or when you say with grant option. The permissions specified above will not be able to write to a read only db. –  Aaron Brown Mar 6 '12 at 13:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.