mysql slave read only


Make sure you are working on slave server:

SHOW SLAVE STATUS \G ;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

SELECT @@global.read_only, @@global.super_read_only;
+--------------------+--------------------------+
| @@global.read_only | @@global.super_read_only |
+--------------------+--------------------------+
| 0 | 0 |
+--------------------+--------------------------+
1 row in set (0.00 sec)

SET GLOBAL super_read_only = 1;

SELECT @@global.read_only, @@global.super_read_only;
+--------------------+--------------------------+
| @@global.read_only | @@global.super_read_only |
+--------------------+--------------------------+
| 1 | 1 |
+--------------------+--------------------------+
1 row in set (0.00 sec)

Save config settings:
super_read_only = On

mysql> INSERT INTO authors (id,name,email) VALUES(1,"Test","[email protected]");
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

If ERROR 1193 (HY000): Unknown system variable 'super_read_only'
SET GLOBAL read_only = 1;
UPDATE mysql.user SET super_priv='N' WHERE user<>'root';
FLUSH PRIVILEGES;

select user,host,plugin,Super_priv from mysql.user;
+------------------+-----------+-------------+------------+
| user | host | plugin | Super_priv |
+------------------+-----------+-------------+------------+
| root | localhost | unix_socket | Y |
| replica_user | % | | N |

Views All Time
Views All Time
55
Views Today
Views Today
1

Leave a Reply

Your email address will not be published. Required fields are marked *