MySQL set read-only from library to invalid user for Super permissions

Because in the test MySQL master and slave read and write separation, with the root user set from the library to read-only, but the restart is effective after the discovery, the root user can still do update insert, when root with all privilege permissions, Including the super (Administration) permission test to reclaim the super privilege of revoke root and then try again, found that root at this time can not write operations so in the ordinary user, or distinguish between read and write separate users, pay attention to reclaim super permissions, otherwise, read-only invalid. In addition, in order to prevent ordinary users from inserting from the library, when assigning permissions to users to reclaim super privileges below is a reference to an article on the internet to do an example:

Configuration:

[[email protected] data]# grep read-only/etc/my.cnf
Read-only

Test process:

Main Library Licensing All

Mysql> Grant All on * * to ' imbyrd ' @ ' localhost ' identified by ' admin ';

Test from library:

[[email protected] data]#/usr/local/mysql/bin/mysql-uimbyrd-p ' admin '
mysql> use hitest;
mysql> INSERT INTO Test (id,name) VALUES (' fo ');
Query OK, 1 row affected (0.14 sec)

Main Library Authorization Select,insert,update,delete

Mysql> REVOKE All on * * from ' imbyrd ' @ ' localhost ';
Mysql> Grant Select,insert,update,delete on * * to ' imbyrd ' @ ' localhost ' identified by ' admin ';
Mysql> Show grants for [email protected] ' localhost ';
+-------------------------------------------------------------------------------------------------------------- --------------------------+
|                                                                                                            Grants for [email protected] |
+-------------------------------------------------------------------------------------------------------------- --------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE on *. imbyrd ' @ ' localhost ' identified by PASSWORD ' *4acfe3202a5ff5cf467898fc58aab 1d615029441 ' |
+-------------------------------------------------------------------------------------------------------------- --------------------------+
1 row in Set (0.00 sec)

Test from library:

mysql> use hitest;
mysql> INSERT INTO Test (Id,name) values (+, ' dddd ');
ERROR 1290 (HY000): The MySQL server is running with the--read-only option so it cannot execute this statement

Main Library configuration:

Mysql> Grant All on * * to ' imbyrd ' @ ' localhost ' identified by ' admin ';
Mysql> Show grants for [email protected] ' localhost ' \g
1. Row ***************************
Grants for [e-mail protected]: GRANT all privileges on * * to ' imbyrd ' @ ' localhost ' identified by PASSWORD ' *4acfe3202a5ff5c f467898fc58aab1d615029441 '
1 row in Set (0.00 sec)
Mysql> REVOKE SUPER on * * from ' imbyrd ' @ ' localhost ';
Mysql> Show grants for [email protected] ' localhost ' \g
1. Row ***************************
Grants for [email protected]: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, Referen CES, INDEX, ALTER, SHOW DATABASES, CREATE temporary TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, C reate view, SHOW view, create ROUTINE, ALTER ROUTINE, create USER, EVENT, TRIGGER, create tablespace on * * to ' imbyrd ' @ ' L Ocalhost ' identified by PASSWORD ' *4acfe3202a5ff5cf467898fc58aab1d615029441 '
1 row in Set (0.00 sec)

Test from library:

[[email protected] data]#/usr/local/mysql/bin/mysql-uimbyrd-p ' admin '
mysql> use hitest;
mysql> INSERT INTO Test (Id,name) values (, ' fddf ');
ERROR 1290 (HY000): The MySQL server is running with the--read-only option so it cannot execute this statement

conclusion : When the user right does not have super Privilege (all permissions are included Super), the read-only from the library takes effect!

MySQL set read-only from library to invalid user for Super permissions