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.

  • 1
    Just to double check, please run SHOW GRANTS; – Rick James Jun 29 at 16:59
up vote 0 down vote accepted

I ran SHOW GRANTS; as Rick suggested and I had a "CONNECTION_ADMIN" grant.

Removing that seems to have fixed it:

create database foo;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

@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

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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