2

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 '18 at 16:59
0

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
0

@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

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.