MySQL重啟之后無法寫入數(shù)據(jù)的問題排查及解決

更新時間：2024年05月29日 09:53:33 作者：愛可生開源社區(qū)

客戶在給系統(tǒng)打補丁之后需要重啟服務器,數(shù)據(jù)庫在重啟之后,read_only 的設置與標準配置文件中不一致,導致主庫在啟動之后無法按照預期寫入,所以本文給大家介紹了MySQL重啟之后無法寫入數(shù)據(jù)的問題排查及解決,需要的朋友可以參考下

背景

客戶在給系統(tǒng)打補丁之后需要重啟服務器，數(shù)據(jù)庫在重啟之后，read_only 的設置與標準配置文件中不一致，導致主庫在啟動之后無法按照預期寫入。

已知并沒有外部程序會對數(shù)據(jù)庫做只讀的設置，那么會是哪里出了問題？

排查過程

首先，檢查啟動進程配置文件的內(nèi)容，是否的確正確配置了 read_only ，有沒有在重啟前后對文件進行修改：

# 檢查配置文件的修改狀態(tài)
[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: -

# 查看配置文件內(nèi)對read_only的設置
[root@localhost ~]# cat /usr/local/mysql/etc/my.cnf |grep read_only
[root@localhost ~]# 
[root@localhost ~]#

已知數(shù)據(jù)庫版本信息為 8.0.25 ，近期沒有修改過配置文件，文件也沒有對 read_only 進行配置，默認配置為 OFF ：

其次，查看所有可能讀取的配置文件，并逐個排查：

# 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 為本實例啟動時的指定配置文件，配置見上文，此處略

[root@localhost ~]# ll ~/.my.cnf
-r-------- 1 root root 355 Aug 19  2021 /root/.my.cnf
[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

# 3. 通過檢查服務器上可能存在的配置文件，發(fā)現(xiàn) read_only 的設置在以上文件內(nèi)并不存在

以上查看配置文件的配置并沒有找到相關配置，那么還有什么辦法呢？嘗試看看對數(shù)據(jù)庫的歷史操作記錄，確認是否有用戶對數(shù)據(jù)庫做過 read_only 配置的操作：

# 通過 /root/.mysql_history ，看到這樣的歷史記錄：
set PERSIST_ONLY read_only = 1;

全局模糊搜索配置文件，發(fā)現(xiàn)了 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 文件內(nèi)容，以及文件的操作時間
[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 文件的狀態(tài)
[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: -

這套數(shù)據(jù)庫之前由別的團隊管理，根據(jù) mysql_history 的操作記錄、前后帶有時間記錄的業(yè)務查詢、以及 mysqld-auto.cnf 文件的生成時間，這些時間在我們接管之前，接管時僅檢查了當時的數(shù)據(jù)庫狀態(tài)、my.cnf 文件中的配置，非常懷疑是這個操作導致了啟動之后 read_only 被開起來，導致業(yè)務無法按照預期寫入，接下來我們對這個參數(shù)進行測試。

參數(shù)測試

在主庫設置參數(shù)。

# 配置文件檢查
[root@localhost etc]# cat my.cnf |grep read_only
read_only = 0
super_read_only = 0

# 參數(shù)檢查：
mysql> select @@read_only,@@super_read_only;
+-------------+-------------------+
| @@read_only | @@super_read_only |
+-------------+-------------------+
|           0 |                 0 |
+-------------+-------------------+
1 row in set (0.00 sec)


# 設置參數(shù)
mysql> set PERSIST_ONLY read_only = 1;
Query OK, 0 rows affected (0.00 sec)

查看重啟后的參數(shù)變化。

# 重啟數(shù)據(jù)庫
[root@localhost ~]# systemctl restart mysqld_3301

# 查看參數(shù)：
mysql> select @@read_only,@@super_read_only;
+-------------+-------------------+
| @@read_only | @@super_read_only |
+-------------+-------------------+
|           1 |                 0 |
+-------------+-------------------+
1 row in set (0.00 sec)

通過 strace 來查看數(shù)據(jù)庫在啟動時讀取了哪些配置文件，以下是相關配置片段：

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>