奇怪的 MySQL“只读”错误

Strange MySQL "read-only" error

提问by Aleksandr Blekh

I'm experiencing a strange MySQL error, seemingly related to the database's read-onlyflag. A Web application that uses MySQL is running on Debian 7.9. It was running well for weeks, if not more, while, suddenly, attempts to access the application-powered website started producing the following error message on a blank webpage:

我遇到了一个奇怪的 MySQL 错误，似乎与数据库的read-only标志有关。使用 MySQL 的 Web 应用程序在 Debian 7.9 上运行。它运行良好数周，甚至更多，而突然，尝试访问由应用程序驱动的网站开始在空白网页上产生以下错误消息：

Error: 500 - SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement

错误：500 - SQLSTATE[HY000]：一般错误：1290 MySQL 服务器正在使用 --read-only 选项运行，因此无法执行此语句

The following are the steps that I performed as part of my investigation:

以下是我在调查中执行的步骤：

• found and read read relevant info on the Internet (some pointed to MySQL's read-onlyflag);
• based on the above, tried to find the read-onlyflag in MySQL config. file (my.cnf) - couldn't find it there, but read that the default value for the flag is OFF anyway;

• verified the filesystem to make sure there is plenty of disk space (df -h): Filesystem Size Used Avail Use% Mounted on udev 10M 0 10M 0% /dev tmpfs 3.2G 1.4M 3.2G 1% /run /dev/disk/by-uuid/xxxxxxxxxxxxxxxxx 113G 14G 94G 13% / tmpfs 5.0M 0 5.0M 0% /run/lock tmpfs 7.3G 72K 7.3G 1% /run/shm

• ran mysqlcheck --all-databases: all tables are OK;

• verified that there is plenty of RAM available on the server (free): total used free shared buffers cached Mem: 32898332 2090268 30808064 0 425436 970348 -/+ buffers/cache: 694484 32203848 Swap: 5105660 0 5105660

• finally, I have decided to take a "snapshot" of MySQL-related processes (ps ax | grep mysql) during the problem's existence and after a temporary fix (DB restart), hoping that it could give people additional context for ideas; here are the corresponding results:

  Problem:20307 ? S 0:00 /bin/sh /usr/bin/mysqld_safe 20635 ? Sl 0:37 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 20636 ? S 0:00 logger -t mysqld -p daemon.error 36427 pts/0 S+ 0:00 grep mysql

  No problem:36948 pts/0 S 0:00 /bin/sh /usr/bin/mysqld_safe 37275 pts/0 Sl 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 37276 pts/0 S 0:00 logger -t mysqld -p daemon.error 38313 pts/0 S+ 0:00 grep mysql

• 在互联网上找到并阅读阅读相关信息（有些指向 MySQL 的read-only标志）；
• 基于上述，尝试read-only在 MySQL 配置中找到标志。file ( my.cnf) - 在那里找不到它，但读到该标志的默认值无论如何都是 OFF ；

• 验证文件系统以确保有足够的磁盘空间 ( df -h)： Filesystem Size Used Avail Use% Mounted on udev 10M 0 10M 0% /dev tmpfs 3.2G 1.4M 3.2G 1% /run /dev/disk/by-uuid/xxxxxxxxxxxxxxxxx 113G 14G 94G 13% / tmpfs 5.0M 0 5.0M 0% /run/lock tmpfs 7.3G 72K 7.3G 1% /run/shm

• 跑mysqlcheck --all-databases：所有表都OK；

• 验证服务器上有足够的 RAM ( free)： total used free shared buffers cached Mem: 32898332 2090268 30808064 0 425436 970348 -/+ buffers/cache: 694484 32203848 Swap: 5105660 0 5105660

• 最后，我决定ps ax | grep mysql在问题存在期间和临时修复（数据库重启）后拍摄 MySQL 相关进程的“快照” ，希望它可以为人们提供更多的思路；以下是相应的结果：

  问题：20307 ? S 0:00 /bin/sh /usr/bin/mysqld_safe 20635 ? Sl 0:37 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 20636 ? S 0:00 logger -t mysqld -p daemon.error 36427 pts/0 S+ 0:00 grep mysql

  没问题：36948 pts/0 S 0:00 /bin/sh /usr/bin/mysqld_safe 37275 pts/0 Sl 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 37276 pts/0 S 0:00 logger -t mysqld -p daemon.error 38313 pts/0 S+ 0:00 grep mysql

UPDATE:

更新：

I just experienced the issue again and decided to check whether the global read-only flag is set to OFF or not, assuming the latter. My assumption has confirmed:

我刚刚再次遇到这个问题，并决定检查全局只读标志是否设置为 OFF ，假设后者。我的假设已经证实：

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

I guess, despite the default OFF value, since it is being overwritten by some process in the system, I will have to set the read-only flag to OFF explicitly and permanently via MySQL configuration file. Will report on results later in an answer.

我想，尽管默认值为 OFF，但由于它正在被系统中的某些进程覆盖，因此我必须通过 MySQL 配置文件明确且永久地将只读标志设置为 OFF。稍后将在答案中报告结果。

回答by Eborbob

As I see it there are two broad reasons for why your database is being set to read only:

在我看来，将数据库设置为只读的原因有两个：

1) MySQL is setting itself read only

1) MySQL 将自身设置为只读

I'm not sure what might cause MySQL to go read only, perhaps disk issues or corruption of database? In any case I'd expect something to appear in the logs, so check the MySQL (and system) logs.

我不确定什么可能导致 MySQL 变为只读，可能是磁盘问题或数据库损坏？在任何情况下，我都希望日志中出现某些内容，因此请检查 MySQL（和系统）日志。

2) A client is setting the database read only

2）客户端将数据库设置为只读

Clients connecting to MySQL can set the database read only using the command:

连接到 MySQL 的客户端可以使用以下命令将数据库设置为只读：

SET GLOBAL read_only = ON;

however to do this the user is required to have SUPERprivileges. This permission shouldn't be needed for websites, applications, etc that are using MySQL - keep it only for an admin account that you use for administering the database.

但是要做到这一点，用户需要有SUPER权限。使用 MySQL 的网站、应用程序等不需要此权限 - 仅为您用于管理数据库的管理员帐户保留它。

Lock down the permissions that each user has so they only have permission to do the things that they need on the databases / tables that are applicable. If you're using some out-of-the-box applications they should come with instructions detailing what permissions are required (e.g. SELECT, INSERT, DELETE, UPDATE).

锁定每个用户拥有的权限，以便他们只能在适用的数据库/表上执行所需的操作。如果您使用的是一些开箱即用的应用程序，它们应该附带详细说明所需权限的说明（例如SELECT, INSERT, DELETE, UPDATE）。

回答by sashoalm

If you're in AWS Aurora, you might be accessing the replica instance which is read-onlyso you need to use the DB Cluster endpoint instead.

如果您在 AWS Aurora 中，您可能正在访问只读副本实例，因此您需要改用数据库集群终端节点。

回答by Ajay Gadhavana

set global read_only = off; make read only mode off later it will work sure.

设置全局只读=关闭；稍后关闭只读模式它肯定会工作。

回答by Chris Hall

I just experienced the same error and fixed it by connecting to the hostname of the mysql server as opposed to the IP address. I'm not sure why this fixed it but it did. Just FYI

我刚刚遇到了同样的错误，并通过连接到 mysql 服务器的主机名而不是 IP 地址来修复它。我不知道为什么这修复了它，但确实如此。仅供参考

回答by Aleksandr Blekh

Based on my question's comments (special thanks to @Eborbob) and my update, I have figured that some process in the system resets the read-onlyflag to ON (1), which seem to trigger the issue and results in the website becoming inaccessible. In order to fix the problem as well as make this fix persistentacross software and server restarts, I decided to update MySQL configuration file my.cnfand restart the DB server.

根据我的问题评论（特别感谢@Eborbob）和我的更新，我发现系统中的某些进程将read-only标志重置为ON (1)，这似乎触发了问题并导致网站无法访问。为了解决这个问题并使这个修复在软件和服务器重启后持续存在，我决定更新 MySQL 配置文件my.cnf并重启数据库服务器。

After making the relevant update (in my case, addition) to the configuration file

在对配置文件进行相关更新（在我的情况下，添加）之后

read_only=0

let's verify that the flag is indeed set to OFF (0):

让我们验证该标志是否确实设置为OFF (0)：

# mysql
mysql> SELECT @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

Finally, let's restart MySQL server (for some reason, a dynamic reloadingof MySQL configuration (/etc/init.d/mysql reload) didn't work, so I had to restartthe database server explicitly:

最后，让我们重新启动 MySQL 服务器（由于某种原因，动态重新加载MySQL 配置 ( /etc/init.d/mysql reload) 不起作用，所以我不得不明确地重新启动数据库服务器：

service mysql stop
service mysql start

Voila! Now access to the website is restored. Will update my answer, if any changes will occur.

瞧！现在可以恢复对网站的访问。如果发生任何变化，将更新我的答案。

回答by NicoMinsk

As Eborbob say it's probably a client,

正如 Eborbob 所说，它可能是一个客户，

Did you check your backup tool ?

你检查你的备份工具了吗？

Do you use some SQL proxy like proxySQL or maxscale ? For exemple Mascale can enforce readonly by monitoring : https://jira.mariadb.org/browse/MXS-1859

您是否使用了一些 SQL 代理，如 proxySQL 或 maxscale ？例如，Mascale 可以通过监控强制只读：https://jira.mariadb.org/browse/MXS-1859

Replication Manager can also change READ ONLY flag

复制管理器还可以更改 READ ONLY 标志

回答by Akash Verma

The below error:- The MySQL server is running with the --read-only option so it cannot execute this statement

以下错误：- MySQL 服务器正在使用 --read-only 选项运行，因此无法执行此语句

It occurs when a user not having the write permission for the sql db tries to insert/update some data into the db.

当没有 sql db 写入权限的用户尝试将一些数据插入/更新到 db 时，就会发生这种情况。

It is quite a valid security error, as it is stating that you currently are having just --read-only rights and hence cannot execute a query that has anything to do with writing.

这是一个非常有效的安全错误，因为它表明您目前只有 --read-only 权限，因此无法执行与写入有关的查询。

To resolve this error:- Get the write access from the DBA.

要解决此错误：- 从 DBA 获取写访问权限。

e.g.

例如

GRANT ALL PRIVILEGES ON database.table TO 'user'@'localhost';

The above query will grant all privileges to the user with username 'user'.

上述查询将向用户名为“user”的用户授予所有权限。