ERROR! MySQL server PID file could not be found!

yisou

服务器经常数据库出问题，要重启才能用。 军哥， 请帮忙看看。 谢了。





2020-06-12T04:41:06.812078Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2020-06-12T04:41:06.812365Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.30-log) starting as process 11612 ...
2020-06-12T04:41:06.958415Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-06-12T04:41:06.958547Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-06-12T04:41:06.958580Z 0 [Note] InnoDB: Uses event mutexes
2020-06-12T04:41:06.958601Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2020-06-12T04:41:06.958634Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-06-12T04:41:06.958655Z 0 [Note] InnoDB: Using Linux native AIO
2020-06-12T04:41:06.960259Z 0 [Note] InnoDB: Number of pools: 1
2020-06-12T04:41:06.960679Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-06-12T04:41:07.061234Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-06-12T04:41:07.095902Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-06-12T04:41:07.105365Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-06-12T04:41:07.312308Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-06-12T04:41:07.444258Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 330555939
2020-06-12T04:41:07.444359Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 330555948
2020-06-12T04:41:07.444399Z 0 [Note] InnoDB: Database was not shutdown normally!
2020-06-12T04:41:07.444419Z 0 [Note] InnoDB: Starting crash recovery.
2020-06-12T04:41:10.019456Z 0 [Note] InnoDB: Last MySQL binlog file position 0 3509180, file name mysql-bin.000041
2020-06-12T04:41:29.781365Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-06-12T04:41:29.781561Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-06-12T04:41:29.781877Z 0 [Note] InnoDB: Setting file '/usr/local/mysql/var/ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-06-12T04:41:29.976565Z 0 [Note] InnoDB: File '/usr/local/mysql/var/ibtmp1' size is now 12 MB.
2020-06-12T04:41:29.983700Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2020-06-12T04:41:29.983831Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2020-06-12T04:41:29.990197Z 0 [Note] InnoDB: Waiting for purge to start
2020-06-12T04:41:30.040713Z 0 [Note] InnoDB: 5.7.30 started; log sequence number 330555948
2020-06-12T04:41:30.040838Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 22935ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2020-06-12T04:41:30.045506Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql/var/ib_buffer_pool
2020-06-12T04:41:30.053015Z 0 [Note] Plugin 'FEDERATED' is disabled.
2020-06-12T04:41:30.095707Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200612 12:41:30
2020-06-12T04:41:30.143500Z 0 [Note] Recovering after a crash using mysql-bin
2020-06-12T04:41:30.631702Z 0 [Note] Starting crash recovery...
2020-06-12T04:41:30.631868Z 0 [Note] Crash recovery finished.
2020-06-12T04:41:30.816644Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2020-06-12T04:41:30.816753Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2020-06-12T04:41:30.844789Z 0 [Warning] CA certificate ca.pem is self signed.
2020-06-12T04:41:30.845243Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2020-06-12T04:41:30.852239Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2020-06-12T04:41:30.852479Z 0 [Note] IPv6 is available.
2020-06-12T04:41:30.852520Z 0 [Note]   - '::' resolves to '::';
2020-06-12T04:41:30.852741Z 0 [Note] Server socket created on IP: '::'.
2020-06-12T04:41:31.049229Z 0 [Note] Failed to start slave threads for channel ''
2020-06-12T04:41:31.186432Z 0 [Note] Event Scheduler: Loaded 0 events
2020-06-12T04:41:31.187739Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.30-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

licess

机器什么配置？
有可能发生out of memory也就是内存不够用了，进而mysql进程可能被kill，这个你可以看一下系统日志

yisou

带宽：         5Mbps
CPU：         Intel Xeon 5520/5620 4核        
操作系统： linux 32位 centos6.4
内存：         2GB        
数据库：         mysql
存储空间： 70GB  

yisou

2020-06-12T03:10:49.361562Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2020-06-12T03:10:49.436975Z 0 [Note] Binlog end
2020-06-12T03:10:49.505615Z 0 [Note] Shutting down plugin 'ngram'
2020-06-12T03:10:49.507502Z 0 [Note] Shutting down plugin 'partition'
2020-06-12T03:10:49.507539Z 0 [Note] Shutting down plugin 'ARCHIVE'
2020-06-12T03:10:49.507557Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2020-06-12T03:10:49.507582Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2020-06-12T03:10:49.509489Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2020-06-12T03:10:49.511692Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2020-06-12T03:10:49.511780Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2020-06-12T03:10:49.511795Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2020-06-12T03:10:49.511802Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2020-06-12T03:10:49.511808Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2020-06-12T03:10:49.511813Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2020-06-12T03:10:49.511819Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2020-06-12T03:10:49.511824Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2020-06-12T03:10:49.511830Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2020-06-12T03:10:49.511835Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2020-06-12T03:10:49.511841Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2020-06-12T03:10:49.511848Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2020-06-12T03:10:49.511859Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2020-06-12T03:10:49.511869Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2020-06-12T03:10:49.511880Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2020-06-12T03:10:49.511891Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2020-06-12T03:10:49.511903Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2020-06-12T03:10:49.511915Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2020-06-12T03:10:49.511926Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2020-06-12T03:10:49.511936Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2020-06-12T03:10:49.511942Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2020-06-12T03:10:49.511948Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2020-06-12T03:10:49.511995Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2020-06-12T03:10:49.512004Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2020-06-12T03:10:49.512010Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2020-06-12T03:10:49.512016Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2020-06-12T03:10:49.512022Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2020-06-12T03:10:49.512060Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2020-06-12T03:10:49.512070Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2020-06-12T03:10:49.512083Z 0 [Note] Shutting down plugin 'InnoDB'
2020-06-12T03:10:49.520348Z 0 [Note] InnoDB: FTS optimize thread exiting.
2020-06-12T03:10:49.522341Z 0 [Note] InnoDB: Starting shutdown...
2020-06-12T03:10:49.636877Z 0 [Note] InnoDB: Dumping buffer pool(s) to /usr/local/mysql/var/ib_buffer_pool
2020-06-12T03:10:49.658928Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 200612 11:10:49
2020-06-12T03:10:51.408610Z 0 [Note] InnoDB: Shutdown completed; log sequence number 325642498
2020-06-12T03:10:51.431077Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-06-12T03:10:51.432355Z 0 [Note] Shutting down plugin 'CSV'
2020-06-12T03:10:51.433488Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2020-06-12T03:10:51.433526Z 0 [Note] Shutting down plugin 'MyISAM'
2020-06-12T03:10:51.433581Z 0 [Note] Shutting down plugin 'MEMORY'
2020-06-12T03:10:51.433810Z 0 [Note] Shutting down plugin 'sha256_password'
2020-06-12T03:10:51.433828Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-06-12T03:10:51.434429Z 0 [Note] Shutting down plugin 'binlog'
2020-06-12T03:10:51.452056Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

2020-06-12T03:10:56.565745Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2020-06-12T03:10:56.565945Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.30-log) starting as process 9076 ...
2020-06-12T03:10:56.782458Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-06-12T03:10:56.782686Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-06-12T03:10:56.782712Z 0 [Note] InnoDB: Uses event mutexes
2020-06-12T03:10:56.782731Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2020-06-12T03:10:56.782771Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-06-12T03:10:56.782795Z 0 [Note] InnoDB: Using Linux native AIO
2020-06-12T03:10:56.787613Z 0 [Note] InnoDB: Number of pools: 1
2020-06-12T03:10:56.791448Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-06-12T03:10:56.810490Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-06-12T03:10:56.882870Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-06-12T03:10:56.898682Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-06-12T03:10:56.970645Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-06-12T03:10:57.624259Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-06-12T03:10:57.624613Z 0 [Note] InnoDB: Setting file '/usr/local/mysql/var/ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-06-12T03:10:57.759790Z 0 [Note] InnoDB: File '/usr/local/mysql/var/ibtmp1' size is now 12 MB.
2020-06-12T03:10:57.762667Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2020-06-12T03:10:57.762704Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2020-06-12T03:10:57.769509Z 0 [Note] InnoDB: Waiting for purge to start
2020-06-12T03:10:57.819909Z 0 [Note] InnoDB: 5.7.30 started; log sequence number 325642498
2020-06-12T03:10:57.821756Z 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mysql/var/ib_buffer_pool
2020-06-12T03:10:57.825404Z 0 [Note] Plugin 'FEDERATED' is disabled.
2020-06-12T03:10:57.949339Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200612 11:10:57
2020-06-12T03:10:58.675248Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2020-06-12T03:10:58.675342Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2020-06-12T03:10:58.693533Z 0 [Warning] CA certificate ca.pem is self signed.
2020-06-12T03:10:58.693856Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2020-06-12T03:10:58.696194Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2020-06-12T03:10:58.696371Z 0 [Note] IPv6 is available.
2020-06-12T03:10:58.696405Z 0 [Note]   - '::' resolves to '::';
2020-06-12T03:10:58.696494Z 0 [Note] Server socket created on IP: '::'.
2020-06-12T03:10:59.205898Z 0 [Note] Failed to start slave threads for channel ''
2020-06-12T03:10:59.361134Z 0 [Note] Event Scheduler: Loaded 0 events
2020-06-12T03:10:59.361961Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.30-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

yisou

2020-06-12T09:57:35.123857Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2020-06-12T09:57:35.124367Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.30-log) starting as process 17823 ...
2020-06-12T09:57:35.588888Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-06-12T09:57:35.589074Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-06-12T09:57:35.589089Z 0 [Note] InnoDB: Uses event mutexes
2020-06-12T09:57:35.589098Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2020-06-12T09:57:35.589125Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-06-12T09:57:35.589138Z 0 [Note] InnoDB: Using Linux native AIO
2020-06-12T09:57:35.591605Z 0 [Note] InnoDB: Number of pools: 1
2020-06-12T09:57:35.591995Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-06-12T09:57:35.600808Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-06-12T09:57:35.609018Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2020-06-12T09:57:35.609105Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2020-06-12T09:57:35.609156Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-06-12T09:57:35.656848Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-06-12T09:57:35.656989Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-06-12T09:57:35.657000Z 0 [ERROR] Failed to initialize builtin plugins.
2020-06-12T09:57:35.657006Z 0 [ERROR] Aborting

2020-06-12T09:57:35.658819Z 0 [Note] Binlog end
2020-06-12T09:57:35.659013Z 0 [Note] Shutting down plugin 'CSV'
2020-06-12T09:57:35.659047Z 0 [Note] Shutting down plugin 'MyISAM'
2020-06-12T09:57:35.660066Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

licess

按后半部分mysql日志看的话应该就是内存不够用了，不升级的话就只能先调整/etc/my.conf 中 innodb_buffer_pool_size 调小些，如64M 重启mysql再观察看看

yisou

谢谢licess，我已经改了， 看看这几天如何。

yisou

ERROR! MySQL server PID file could not be found!
服务器还是出问题呀。

licess

yisou 发表于 2020-6-15 19:59
ERROR! MySQL server PID file could not be found!
服务器还是出问题呀。

配置参数已经改的比较小了，再小可能就会影响innodb引擎的效率，如果不用innodb引擎可以关闭
如果还是之前的错误，那就是内存已经彻底不够用了，需要升级