MySQL報錯:The server quit without updating PID file的解決思路與方法

今天在學習mysql 二進位制紀錄檔的時候需要在編輯my.cnf的檔案，產生的一系列報錯，個人的排錯思路和方法，僅供參考。

重啟damon、重啟mysql 發現報錯

Starting MySQL. ERROR! The server quit without updating PID file (/usr/local/mysql/data/mysqld.pid).

發現註釋這三行設定之後發現可以正常啟動，那麼問題就出現在這三行程式碼上

log-bin = mysql-bin  
#binlog_expire_logs_seconds = 600
#max_binlog_size = 200M

查詢紀錄檔發現報錯：You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation#您已經啟用了二進位制紀錄檔，但是沒有提供必需的伺服器id。請參考適當的伺服器啟動引數檔案

經過百度發現：MySQL 5.7.3以後版本必須設定server-id，否則無法啟用MySQL二進位制紀錄檔

嘗試啟動 在my.cnf中新增 server-id = 1   唯一的服務辨識號,數值位於 1 到 2^32-1之間。此值在master和slave上都需要設定。

如果 “master-host” 沒有被設定,則預設為1, 但是如果忽略此選項,MySQL不會作為master生效

新增引數，並嘗試啟動。 

發現又報錯：Can't connect to local MySQL server through socket '/tmp/mysql.sock'

mysql的連線方式有兩種:一種是TCP/IP、一種是socket連線

mysql.sock的作用是程式與mysqlserver處於同一臺機器，發起本地連線時可用。 

連線localhost通常通過一個Unix域通訊端檔案進行，一般是/tmp/mysql.sock。

如果通訊端檔案被刪除了，本地客戶就不能連線。/tmp 資料夾屬於臨時檔案，隨時可能被刪除。

每次mysql 啟動的時候，都會生成一個mysql.sock 的檔案，

socket = /var/lib/mysql/mysql.sock   建議將sock檔案移動至下var/lib/mysql/  

嘗試重新啟動 發現還是報錯 Starting MySQL. ERROR! The server quit without updating PID file

排錯：

 1. 檢查目錄許可權    

 2. selinux是否關閉  

 3. 檢查my.cnf引數 port,datadir,basedir,socket 是否設定正常

排錯發現是我設定sock目錄時候，並沒有給予許可權

chown -R mysql:mysql /var/lib/mysql/   #設定許可權，並檢視
ls -alh | grep mysql
drwxr-xr-x.  2 mysql   mysql      6 Sep 14 16:19 mysql

再次嘗試啟動mysql 發現還是報錯：Starting MySQL. ERROR! The server quit without updating PID file (/usr/local/mysql/data/mysqld.pid).

1. tail -f /usr/local/mysql/data/mysql.err  /查詢MySQL自帶的紀錄檔檔案mysql.err

 #發現沒有關鍵錯誤資訊

 2. tail /var/log/messages  #記錄了絕大多數的系統紀錄檔 

 2022-09-15T06:32:03.776090Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2022-09-15T06:32:03.776188Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.33-log) starting as process 57901 ...
2022-09-15T06:32:03.780168Z 0 [Note] InnoDB: PUNCH HOLE support available
2022-09-15T06:32:03.780229Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-09-15T06:32:03.780253Z 0 [Note] InnoDB: Uses event mutexes
2022-09-15T06:32:03.780278Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2022-09-15T06:32:03.780411Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-09-15T06:32:03.780419Z 0 [Note] InnoDB: Using Linux native AIO
2022-09-15T06:32:03.780605Z 0 [Note] InnoDB: Number of pools: 1
2022-09-15T06:32:03.780695Z 0 [Note] InnoDB: Using CPU crc32 instructions
2022-09-15T06:32:03.781783Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2022-09-15T06:32:03.787253Z 0 [Note] InnoDB: Completed initialization of buffer pool
2022-09-15T06:32:03.789167Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2022-09-15T06:32:03.800452Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2022-09-15T06:32:03.805695Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-09-15T06:32:03.805865Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-09-15T06:32:03.819123Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-09-15T06:32:03.820045Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2022-09-15T06:32:03.820088Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2022-09-15T06:32:03.821153Z 0 [Note] InnoDB: Waiting for purge to start
2022-09-15T06:32:03.872143Z 0 [Note] InnoDB: 5.7.33 started; log sequence number 2980979
2022-09-15T06:32:03.872679Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql/data/ib_buffer_pool
2022-09-15T06:32:03.872896Z 0 [Note] Plugin 'FEDERATED' is disabled.
2022-09-15T06:32:03.873741Z 0 [Note] InnoDB: Buffer pool(s) load completed at 220915 14:32:03
2022-09-15T06:32:03.875555Z 0 [ERROR] unknown variable 'binlog_expire_logs_seconds=600'
2022-09-15T06:32:03.875596Z 0 [ERROR] Aborting

2022-09-15T06:32:03.875610Z 0 [Note] Binlog end
2022-09-15T06:32:03.875666Z 0 [Note] Shutting down plugin 'ngram'
2022-09-15T06:32:03.875678Z 0 [Note] Shutting down plugin 'partition'
2022-09-15T06:32:03.875681Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2022-09-15T06:32:03.875684Z 0 [Note] Shutting down plugin 'ARCHIVE'
2022-09-15T06:32:03.875687Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2022-09-15T06:32:03.875727Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2022-09-15T06:32:03.875730Z 0 [Note] Shutting down plugin 'MyISAM'
2022-09-15T06:32:03.875738Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2022-09-15T06:32:03.875741Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2022-09-15T06:32:03.875742Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2022-09-15T06:32:03.875744Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2022-09-15T06:32:03.875745Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2022-09-15T06:32:03.875746Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2022-09-15T06:32:03.875748Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2022-09-15T06:32:03.875749Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2022-09-15T06:32:03.875751Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2022-09-15T06:32:03.875752Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2022-09-15T06:32:03.875754Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2022-09-15T06:32:03.875755Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2022-09-15T06:32:03.875757Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2022-09-15T06:32:03.875758Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2022-09-15T06:32:03.875760Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2022-09-15T06:32:03.875761Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2022-09-15T06:32:03.875763Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2022-09-15T06:32:03.875764Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2022-09-15T06:32:03.875766Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2022-09-15T06:32:03.875767Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2022-09-15T06:32:03.875769Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2022-09-15T06:32:03.875770Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2022-09-15T06:32:03.875772Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2022-09-15T06:32:03.875773Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2022-09-15T06:32:03.875775Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2022-09-15T06:32:03.875777Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2022-09-15T06:32:03.875778Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2022-09-15T06:32:03.875780Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2022-09-15T06:32:03.875781Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2022-09-15T06:32:03.875782Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2022-09-15T06:32:03.875784Z 0 [Note] Shutting down plugin 'InnoDB'
2022-09-15T06:32:03.875854Z 0 [Note] InnoDB: FTS optimize thread exiting.
2022-09-15T06:32:03.876196Z 0 [Note] InnoDB: Starting shutdown...
2022-09-15T06:32:03.976840Z 0 [Note] InnoDB: Dumping buffer pool(s) to /usr/local/mysql/data/ib_buffer_pool
2022-09-15T06:32:03.977146Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 220915 14:32:03
2022-09-15T06:32:05.488923Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2980998
2022-09-15T06:32:05.490187Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2022-09-15T06:32:05.490231Z 0 [Note] Shutting down plugin 'MEMORY'
2022-09-15T06:32:05.490243Z 0 [Note] Shutting down plugin 'CSV'
2022-09-15T06:32:05.490246Z 0 [Note] Shutting down plugin 'sha256_password'
2022-09-15T06:32:05.490248Z 0 [Note] Shutting down plugin 'mysql_native_password'
2022-09-15T06:32:05.490337Z 0 [Note] Shutting down plugin 'binlog'
2022-09-15T06:32:05.490478Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

 發現關鍵字 unknown variable 未知的變數  binlog_expire_logs_seconds

 我照著視訊裡面的單詞一個個敲，不是我敲錯。嘗試尋找度娘

在mysql 8.0版本中新增了binlog_expire_logs_seconds，該參數列示binlog的失效日期單位秒。

8.0之前的版本，binlog的失效紀錄檔用expire_logs_days來設定，單位是天。

參考連結：mysql 8 新特性之binlog_expire_logs_seconds

因為我是學習了8.0和5.7版本的Mysql都有在學習，win端使用的是8.0 linux安裝的是5.7

所以是我mysql版本的問題導致。更改引數 

[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql  
# 建立目錄
datadir = /usr/local/mysql/data   
# 資料庫目錄
pid-file = /usr/local/mysql/data/mysqld.pid 
# mysql程序
log-error = /usr/local/mysql/data/mysql.err   
# 錯誤紀錄檔
socket = /var/lib/mysql/mysql.sock
# 可以socket檔案方式登入mysql
log-bin = /usr/local/mysql/data/bin-log/mysql-bin.log
server-id = 1
#binlog_expire_logs_seconds = 600
max_binlog_size = 200M
#Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links = 0
#禁止符號連結以防各種安全風險
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
 
# [mysqld_safe]
 
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
explicit_defaults_for_timestamp=true

再再再再次嘗試啟動MySQL 

[root@master lib]# ps -aux | grep mysql
root      53523  0.0  0.0 108096   620 pts/1    S+   14:00   0:00 tail -f /usr/local/mysql/data/mysql.err
root      54867  0.0  0.0  11820  1612 pts/0    S    14:05   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/mysqld.pid
mysql     55106  1.1  2.2 1145048 176116 pts/0  Sl   14:05   0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/mysql.err --pid-file=/usr/local/mysql/data/mysqld.pid --socket=/var/lib/mysql/mysql.sock --port=3306
root      55162  0.0  0.0 112816   976 pts/0    S+   14:05   0:00 grep --color=auto mysql
[root@master lib]# netstat -anptu | grep :3306
tcp6       0      0 :::3306                 :::*                    LISTEN      55106/mysqld   

嘗試登入MySQL 發現又報錯：錯誤2002 (HY000):無法通過通訊端'/tmp/ MySQL連線到本地MySQL伺服器

[root@master lib]# mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock #設定軟連線

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| atguigudb          |
| #mysql50#bin-log   |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test_u             |
+--------------------+
8 rows in set (0.00 sec)

 登入成功！！！ 繼續繼續MySQL！！！衝

總結

到此這篇關於MySQL報錯:The server quit without updating PID file的解決思路與方法的文章就介紹到這了,更多相關MySQL報錯The server quit without updating PID file內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！