Root Cause Analysis of Unexpected read_only Setting in MySQL After Server Restart

1 Background

After applying a patch, the server needed to be restarted; the MySQL instance started with a read_only setting that differed from the standard configuration file, preventing the primary server from writing as expected.

No external program was known to modify the read‑only setting, so the source of the problem was investigated.

2 Investigation Process

First, the startup configuration file was examined to confirm the read_only setting and to check whether the file had been altered before and after the restart:

# 检查配置文件的修改状态
[root@localhost ~]# stat /usr/local/mysql/etc/my.cnf
  File: '/usr/local/mysql/etc/my.cnf'
  Size: 6160       Blocks: 16          IO Block: 4096   regular file
Device: fd00h/64768d   Inode: 591296      Links: 1
Access: (0644/-rw-r--r--)  Uid: (   27/   mysql)   Gid: (   27/   mysql)
Access: 2023-12-18 03:47:45.375190686 +0800
Modify: 2022-08-01 23:25:34.861953062 +0800
Change: 2022-08-01 23:25:34.862953087 +0800
 Birth: -
# 查看配置文件内对read_only的设置
[root@localhost ~]# cat /usr/local/mysql/etc/my.cnf |grep read_only
[root@localhost ~]#

The database version is 8.0.25 and the default configuration for read_only is OFF , with no explicit setting found in the file.

Next, all possible configuration files were inspected according to MySQL's default option‑file loading order:

# 1. 查看 mysql 配置文件默认的加载顺序
[root@localhost ~]# mysql --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
# 2. 依次查看可能会存在的配置文件，及其配置的 read_only 值
[root@localhost ~]# ll /etc/my.cnf
ls: cannot access /etc/my.cnf: No such file or directory
[root@localhost ~]# ll /etc/mysql/my.cnf
ls: cannot access /etc/mysql/my.cnf: No such file or directory
# /usr/local/mysql/etc/my.cnf 为本实例启动时的指定配置文件，配置见上文，此处略
# 3. 通过检查服务器上可能存在的配置文件，发现 read_only 的设置在以上文件内并不存在

Since no read_only entry was found, the MySQL history file was examined, revealing a persisted command:

# 通过 /root/.mysql_history，看到这样的历史记录：
set PERSIST_ONLY read_only = 1;

A global search for configuration files uncovered mysqld-auto.cnf :

[root@localhost ~]# find / -name '*my*cnf'
/root/my.cnf
/root/.my.cnf
/usr/share/mysql/README.mysql-cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf
/usr/local/mysql/support-files/my.cnf
/usr/local/mysql/etc/my.cnf
/data/mysql/mysqld-auto.cnf
# 查看 mysqld-auto.cnf 文件内容，以及文件的操作时间
[root@localhost ~]# cat /data/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "read_only" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1659045255269856 , "User" : "root" , "Host" : "localhost" } } } }
# 时间戳转换为北京时间 【1659045255269856】 -->【2022-07-29 05:54:15】
# 查看 mysqld-auto.cnf 文件的状态
[root@localhost ~]# stat /data/mysql/mysqld-auto.cnf
  File: '/data/mysql/mysqld-auto.cnf'
  Size: 164        Blocks: 8          IO Block: 4096   regular file
Device: fd08h/64776d   Inode: 6291467      Links: 1
Access: (0640/-rw-r-----)  Uid: (   27/   mysql)   Gid: (   27/   mysql)
Access: 2023-12-19 11:48:42.511662204 +0800
Modify: 2022-07-29 05:54:15.269682214 +0800
Change: 2022-07-29 05:54:15.269682214 +0800
 Birth: -

The database had previously been managed by another team; the persisted read_only setting in mysqld-auto.cnf explained why the server became read‑only after restart.

Parameter Testing

On the primary server, the current variables were verified and then deliberately set to reproduce the issue:

# 配置文件检查
[root@localhost etc]# cat my.cnf |grep read_only
read_only = 0
super_read_only = 0
# 参数检查：
mysql> select @@read_only,@@super_read_only;
+-------------+-------------------+
| @@read_only | @@super_read_only |
+-------------+-------------------+
|           0 |                 0 |
+-------------+-------------------+
1 row in set (0.00 sec)
# 设置参数
mysql> set PERSIST_ONLY read_only = 1;
Query OK, 0 rows affected (0.00 sec)
# 重启数据库
[root@localhost ~]# systemctl restart mysqld_3301
# 查看参数：
mysql> select @@read_only,@@super_read_only;
+-------------+-------------------+
| @@read_only | @@super_read_only |
+-------------+-------------------+
|           1 |                 0 |
+-------------+-------------------+
1 row in set (0.00 sec)

Using strace during startup confirmed that MySQL reads mysqld-auto.cnf after the explicitly specified configuration file:

15:56:34.828260 stat("/opt/mysql/etc/3301/my.cnf", {st_mode=S_IFREG|0640, st_size=5042, ...}) = 0 <0.000008>
15:56:34.829061 stat("/opt/mysql/data/3301/mysqld-auto.cnf", {st_mode=S_IFREG|0640, st_size=164, ...}) = 0 <0.000006>
... (additional stat calls) ...

Test Conclusion

The investigation shows that during startup MySQL reads the persisted configuration file mysqld-auto.cnf , and the read_only entry in that file overrides the standard my.cnf setting, causing the unexpected read‑only state.

Official Documentation Supplement

Searching the MySQL documentation for PERSIST_ONLY reveals that this operation writes the setting to mysqld-auto.cnf . The persisted variable can be removed with RESET PERSIST .

While SET GLOBAL changes a variable only for the current runtime, the PERSIST variant stores the change in mysqld-auto.cnf so it survives subsequent restarts.

On Unix‑like systems, the option‑file loading order also includes mysqld-auto.cnf , as shown in the official option‑files documentation.

References

[1] sysvar_read_only: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only

[2] persisted-system-variables: https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html

[3] persisted-system-variables (same as above)

[4] option-files: https://dev.mysql.com/doc/refman/8.0/en/option-files.html