3

Do the users initiating inserts on the master server also have to be on the slave with at least as many permissions for the replicated queries to be correctly executed? My understanding is that you only have to have a replication user with enough permissions but I still have a doubt.

Moreover, let's say that the slave server has the same users as the master server, in addition to a replication-dedicated user, but with less permissions, non writes for instance. Would there be any kind of conflicts resulting in the replicated queries not being executed?

The idea is to have a read-only slave with only SELECT perm users to access to data, not necessarily the same as the master's users, plus a replication-dedicated user on the slave with REPLICATION SLAVE perm. Would all replicated queries then supposed to be executed normally? That's my actual wondering.

  • at the very least avoid having Users with SUPER privs and enable super-read-only on the slaves (for 5.6 only) this way you prevent DMLs on the slaves which is usually the cause for inconsistencies. – jerichorivera Mar 11 '16 at 12:20
  • yes, it will still replicate – jerichorivera Mar 11 '16 at 19:41
2
  • The "user" that the Slave connects to the Master with -- It needs only REPLICATION SLAVE, no other privileges. Replication's goal is to keep the Slave identical to the Master. This goal overrides any privilege issues.

  • The Slave should have read_only = ON as an extra protection.

  • "Users" (either human or application), other than the system admin, should not have SUPER privilege. This keeps you out of trouble, and helps prevent hacking.

  • Another hacking prevention is to GRANT users access only to dbname.*, not *.*. The latter would include the system tables where all the Grants are stored.

  • Users need write permissions when writing to the Master. Don't worry about whether the users have write perm to the Slave; the Slave user bypasses that kind of check. (This is probably the answer you were looking for.)

  • Users need only read permission when reading from the Slave.

  • TEMPORARY tables follow different rules. (It is something you might want to use on a read-only Slave.) I hesitate to spell them out here, because I am not sure of the rules.

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.