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.
Thanks.
Best Answer
@Rossi, you don't have to update your "CONNECTION_ADMIN" privilege to make your slave read only for SUPER users like you.
you can set super_read_only (available from MySQL 5.6.21) and your server will not allow any updates even for the users that have SUPER privilege.
super_read_only Documentation