mysql read only参数_MySQL中的read_only参数

晚上在做主从同步时，在slave上的my.cnf文件中的[mysqld]模块中添加了read_only参数。按说是不能对数据库内容进行更改了，但是却碰上了没有super权限的用户可以创建数据库的问题。

数据库版本为5.0.41，用户为baichi,给予的权限为select,insert,delete,create,update，read_only已为ON。

mysql> SELECT VERSION();

+-----------+

| VERSION() |

+-----------+

| 5.0.41 |

+-----------+

1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE "read_only";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| read_only | ON |

+---------------+-------+

1 row in set (0.00 sec)

mysql> SELECT USER();

+------------------+

| USER() |

+------------------+

| baichi@localhost |

+------------------+

1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR baichi@localhost;

+-----------------------------------------------------------------------------+

| Grants for baichi@localhost |

+-----------------------------------------------------------------------------+

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON *.* TO 'baichi'@'localhost' |

+-----------------------------------------------------------------------------+

1 row in set (0.00 sec)

现在使用baichi用户创建数据库baichi

mysql> CREATE DATABASE baichi;

Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| baichi |

| mysql |

| test |

+--------------------+

4 rows in set (0.00 sec)

在baichi数据库中创建baichi表

mysql> USE baichi;

Database changed

mysql> CREATE TABLE baichi(id int);

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

这时不能创建了。

现在使用root用户创建下baichi表并插入几个数字

mysql> use baichi;

Database changed

mysql> CREATE TABLE baichi(id int);

Query OK, 0 rows affected (0.00 sec)

mysql> insert into baichi values(1),(2),(3);

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM baichi;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.00 sec)

再回到baichi用户，试图insert,delete和update

mysql> USE baichi;

Database changed

mysql> INSERT INTO baichi VALUES(4);

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

mysql> DELETE FROM baichi WHERE id=1;

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

mysql> UPDATE baichi SET id=10 WHERE id=1;

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

mysql> SELECT * FROM baichi;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.00 sec)

结果是不能insert,delete和update。

man了下mysqld，查找到read_only的相关解释

・ read_only

When this variable is set to ON, the server allows no updates except from users that have the SUPER

privilege or (on a slave server) from updates performed by slave threads. On a slave server, this can be

useful to ensure that the slave accepts updates only from its master server and not from clients. As of

MySQL 5.0.16, this variable does not apply to TEMPORARY tables.

read_only exists only as a GLOBAL variable, so changes to its value require the SUPER privilege. Changes to

read_only on a master server are not replicated to slave servers. The value can be set on a slave server

independent of the setting on the master.

没有找到为什么可以创建数据库的解释。

查看MySQL参考手册，内容也差不多。

・         read_only

当变量对复制从服务器设置为ON时，从服务器不允许更新，除非通过从服务器的线程或用户拥有SUPER权限。可以确保从服务器不接受客户端的更新命令。

是版本问题？正好虚拟机中还有个系统装了其他版本，试试看

mysql> SELECT VERSION();

+-----------+

| VERSION() |

+-----------+

| 5.1.60 |

+-----------+

1 row in set (0.00 sec)

mysql> SELECT USER();

+------------------+

| USER() |

+------------------+

| baichi@localhost |

+------------------+

1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR baichi@localhost;

+---------------------------------------------------------------------------+

| Grants for baichi@localhost |

+---------------------------------------------------------------------------+

| GRANT SELECT, INSERT, DELETE, CREATE, DROP ON *.* TO 'baichi'@'localhost' |

+---------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> CREATE DATABASE baichi;

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

这回数据库创建不了了！

太晚了，明天继续尝试。