Why MySQL Can’t Write Data After Restart?

1. Background

‍After applying system patches, a client restarted their server. Post-restart, the MySQL database entered a read-only state, preventing write operations. Initial checks confirmed no external programs were setting the read_only parameter. The root cause was traced to a misconfigured parameter in the mysqld-auto.cnf file, which persisted across restarts.

2. Investigation Process

2.1 Configuration File Check

‍The startup configuration file (my.cnf) was checked for read_only settings. No explicit configuration for read_only was found:

[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: -

[root@localhost ~]# cat /usr/local/mysql/etc/my.cnf | grep read_only
[root@localhost ~]#

‍

2.2 Checking Default Configuration Files

‍All potential MySQL configuration files were reviewed:

[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

[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

[root@localhost ~]# cat .my.cnf | grep read_only
[root@localhost ~]#

[root@localhost ~]# ll /home/mysql/.my.cnf
ls: cannot access /etc/my.cnf: No such file or directory

‍

2.3 Historical Operation Review

‍The MySQL history file (/root/.mysql_history) revealed a past command:

set PERSIST_ONLY read_only = 1;

‍

2.4 Discovery of mysqld-auto.cnf

‍The mysqld-auto.cnf file was identified as the source of the persistent read_only setting:

[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

[root@localhost ~]# cat /data/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "read_only" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1659045255269856 , "User" : "root" , "Host" : "localhost" } } } }

# Timestamp conversion to Beijing time: 1659045255269856 → 2022-07-29 05:54:15
[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: -

‍

3. Parameter Testing

3.1 Setting read_only with PERSIST_ONLY

[root@localhost etc]# cat my.cnf | grep read_only
read_only = 0
super_read_only = 0

# Parameter check:
mysql> select @@read_only, @@super_read_only;
+-------------+-------------------+
| @@read_only | @@super_read_only |
+-------------+-------------------+
|           0 |                 0 |
+-------------+-------------------+
1 row in set (0.00 sec)

# Set parameter:
mysql> set PERSIST_ONLY read_only = 1;
Query OK, 0 rows affected (0.00 sec)

‍

3.2 Restarting MySQL

‍After restarting, the read_only parameter remained ON:

[root@localhost ~]# systemctl restart mysqld_3301

# Check parameter:
mysql> select @@read_only, @@super_read_only;
+-------------+-------------------+
| @@read_only | @@super_read_only |
+-------------+-------------------+
|           1 |                 0 |
+-------------+-------------------+
1 row in set (0.00 sec)

‍

3.3 Configuration File Read Order

‍Using strace, it was confirmed that mysqld-auto.cnf was read during startup:

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>
15:56:35.154154 stat("/opt/mysql/data/3301/auto.cnf", {st_mode=S_IFREG|0640, st_size=56, ...}) = 0 <0.000008>
15:56:35.154228 stat("/opt/mysql/data/3301/auto.cnf", {st_mode=S_IFREG|0640, st_size=56, ...}) = 0 <0.000007>
15:56:35.172411 stat("/opt/mysql/data/3301/auto.cnf", {st_mode=S_IFREG|0640, st_size=56, ...}) = 0 <0.000007>
15:56:35.172441 stat("/opt/mysql/data/3301/auto.cnf", {st_mode=S_IFREG|0640, st_size=56, ...}) = 0 <0.000007>
15:56:35.174142 stat("/opt/mysql/data/3301/mysqld-auto.cnf", {st_mode=S_IFREG|0640, st_size=164, ...}) = 0 <0.000007>
15:56:35.174172 stat("/opt/mysql/data/3301/mysqld-auto.cnf", {st_mode=S_IFREG|0640, st_size=164, ...}) = 0 <0.000007>
15:56:35.357608 stat("/opt/mysql/data/3301/auto.cnf", {st_mode=S_IFREG|0640, st_size=56, ...}) = 0 <0.000011>
15:56:35.357643 stat("/opt/mysql/data/3301/auto.cnf", {st_mode=S_IFREG|0640, st_size=56, ...}) = 0 <0.000010>
15:56:35.360019 stat("/opt/mysql/data/3301/mysqld-auto.cnf", {st_mode=S_IFREG|0640, st_size=164, ...}) = 0 <0.000009>
15:56:35.360052 stat("/opt/mysql/data/3301/mysqld-auto.cnf", {st_mode=S_IFREG|0640, st_size=164, ...}) = 0 <0.000008>

‍

4. Conclusion

‍The persistent read_only setting in mysqld-auto.cnf caused the database to remain in read-only mode after restart. To resolve this, use RESET PERSIST to remove the setting:

mysql> RESET PERSIST read_only;

‍

5. Official Documentation Reference

‍MySQL’s PERSIST_ONLY syntax writes settings to mysqld-auto.cnf, which is read during subsequent restarts. This behavior ensures that dynamic changes persist across reboots. For more details, refer to the MySQL official documentation.