MySQL set read-only mode

Common phenomena:

The MySQL master-slave server is often maintained in operations, but from the library we are only used for reading.

General access is only authorized to read account, but sometimes maintenance work may not be a person doing, you can not guarantee that all follow this standard operation.

A colleague may authorize the slave library MySQL account to be all or select,update,insert,delete. There is also a situation is the master and slave to do all the data synchronization, in the main library authorized account is also synchronized to the top of the library, of course, the master account will certainly have Select,update,insert,delete permissions.

Problems that exist:

Then the problem comes, when the operator or the developer program incorrectly connected to MySQL slave as the master, and so on, then sad to all the data modification to operate the slave.

To avoid this problem, we need to set MySQL's slave to read-only mode.

Workaround:

The demo is as follows:

MySQL>set global read_only=10 rows affected (0.00 Sec

Authorized normal MySQL test account

Mysql> Grant Select,Insert,Update,Delete  onS18.*  to 'Test'@'127.0.0.1'identified by '123456'; Query OK,0Rows affected,1Warning (0.00Sec

Use the test account login to delete and other operations, will prompt--read-only error

Mysql> Delete  fromStudentwhereSid= -; ERROR1290(HY000): the MySQL server isRunning withThe--read-only option so IT cannotExecuteThis statementmysql> InsertClassValues(5, third grade 10 classes); ERROR1290(HY000): the MySQL server isRunning withThe--read-only option so IT cannotExecuteThis statement

Note: Set global Read_only=1 does not take effect on accounts that have super privileges, so try to avoid adding super privileges when you authorize your account.

MySQL set read-only mode