MySQL User Management with a readonly instance

I have a MySQL master instance and a readonly slave instance. I want to separately maintain users and privileges across both instance. The mysql schema on the master is not replicated to 开发者_JAVA百科the slave.

The problem is users on the slave instance are not allowed to change their passwords because of the readonly status. It is not appropriate to give these users SUPER privileges, even temporarily, just to change their password. Is there a way to make MySQL readonly, but on a schema-by-schema basis, instead of instance-wide? I only want data from my problem space to be readonly. Not my solution space (db users, db permissions, etc.).

read_only only works if the user doesn't have SUPER privileges.

You could try this:

1. Create a stored procedure to change passwords
2. Use SQL SECURITY DEFINER in your procedure.
3. Set the definer to a user with SUPER privileges
4. The procedure can be called by the unprivileged user to change passwords.

You should make sure that the procedure checks if someone is allowed to change the password in question.

Other possibility would be to replicate only special databases (and not the mysql database), being able to define other users and rights on the slave. This way, you could instead of using readlony=1 forbid all users the rights to modify the dataset.