I have a new Master/Slave config running. Everything is happily replication. I have added a secondary user whose role will be to manage the databases and tables.
Currently the following privileges are set:
Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: N Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: N Repl_client_priv: N Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: N Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y account_locked: N Create_role_priv: N Drop_role_priv: N
Privileges have been flushed and the slave server was started read-only.
mysql> select @@read_only; +-------------+ | @@read_only | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec)
However I can still perform write operations on the slave with the user.
mysql> create database foo; Query OK, 1 row affected (0.01 sec) mysql> use foo; Database changed mysql> create table `foobar` ( `x` int ); Query OK, 0 rows affected (0.10 sec) mysql> insert into foobar values (1); Query OK, 1 row affected (0.06 sec) mysql> insert into foobar values (2); Query OK, 1 row affected (0.05 sec) mysql> select * from foobar; +------+ | x | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) mysql> drop database foo; Query OK, 1 row affected (0.06 sec)
It's my understanding that without the Repl and Super priv's I should not be able to do the above, or am I doing something else wrong ?
Essentially I want the scripts and daemons using this login to never break stuff if I flip the master and slave around.