I have some users that I want to grant read only access to my database so I setup a replica server, but I don't want to grant them access to the master. I'd assume that this is a common use case, but I've not been able to find out how to do this easily. The only thing I can think of to accomplish this is to restrict access by IP to the master server.

  • It's not common actually. What problem are you trying to solve? – akuzminsky Jul 28 '14 at 15:57
  • @akuzminsky, I've got my master server/db which is for production and is the backend to a website with many end users. There are some other power users that want direct read access to the database. I've set up the replica to handle this extra load to the database, and to separate it from prod. Now, these power users have access to both databases, and I can't figure out how to enforce that they only use the read replica. Thanks. – B Johnson Jul 28 '14 at 16:11
  • assuming the power users have read-only permissions is it OK they still can have access to the master? – akuzminsky Jul 28 '14 at 16:13
  • @akuzminsky, if all the power users decide "because I have a choice, I'll use the master instead of the replica, because I know that it runs on more powerful hardware", then nobody uses the replica, and the resources go unused. – B Johnson Jul 28 '14 at 16:27
  • 1
    Well, you can create a user on the slave host only. But this is a bad idea because it will lead to other problems. For example, what are you going to do with users in case you decide to promote the slave to master? – akuzminsky Jul 28 '14 at 16:35

Master and slave instances can have different users and access permissions -replicated queries are executed by the SUPER user-, just create and/or modify the separate users with SET SESSION sql_log_bin = 0; every time (so you do not create conflicts on replication). Avoid any other process writing directly to the mysql.user (and other mysql.*) tables directly and you will be fine.

You probably want to set your slave as read_only and not provide SUPER privileges on anyone else there (as it will become out of sync if someone writes there and not to the master).

  • We use replication by 'white lists', e. i. replicate only specified databases. So mysql database is excluded. – user60433 Jun 30 '16 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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