service mysqld start 后, 服務過幾個小時左右就自動shutdown了, 基本沒有任何資料庫操作, 就是開啟了, 然后過幾個小時后就shutdown了, 網站就用不了了, 請問怎么回事呢? 阿里云上同樣的1G記憶體的ECS里并不會啊,也是mysql5.7.20 我平臺還沒上線還在開發中,1G記憶體夠的吧...是否BCC需要什么配置來確保mysql5.7.20穩定運行?....
整個服務器就裝了PHP7,mysql,vsftpd這幾個簡單的東西.
系統是:CentOS / 6.8 x86_64 (64bit) 1核1G
日志如下:
------------------------------------------------------------------------------------------------------
mysqld服務shutdown的日志:
2017-12-18T19:47:03.503012Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-12-18T19:47:03.543807Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2017-12-18T19:47:03.745187Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.20) starting as process 6777 ...
2017-12-18T19:47:04.595175Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-12-18T19:47:04.595234Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-12-18T19:47:04.595245Z 0 [Note] InnoDB: Uses event mutexes
2017-12-18T19:47:04.595253Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2017-12-18T19:47:04.595260Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-12-18T19:47:04.595267Z 0 [Note] InnoDB: Using Linux native AIO
2017-12-18T19:47:04.622159Z 0 [Note] InnoDB: Number of pools: 1
2017-12-18T19:47:04.743115Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-12-18T19:47:04.986152Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-12-18T19:47:05.029165Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2017-12-18T19:47:05.029221Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2017-12-18T19:47:05.029230Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-12-18T19:47:05.029239Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-12-18T19:47:05.029260Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-12-18T19:47:05.029267Z 0 [ERROR] Failed to initialize plugins.
2017-12-18T19:47:05.029271Z 0 [ERROR] Aborting
2017-12-18T19:47:05.055405Z 0 [Note] Binlog end
2017-12-18T19:47:05.124469Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
------------------------------------------------------------------------------------------------------
下面是我的my.conf: (基本就是默認my.conf,新增的我都注釋掉了沒用)
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
bind-address=0.0.0.0
disable-partition-engine-check=1
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#performance_schema = OFF
#降低mysql記憶體占用
#performance_schema_max_table_instances=200
#table_definition_cache=200
#table_open_cache=128
#找出執行慢的sql陳述句
#log-slow-queries=queries-slow.log
performance_schema_instrument ='memory%=counted'
------------------------------------------------------------------------------------------------------
補一份mysqld服務啟動的日志:(我剛啟動的)
2017-12-19T01:44:06.774497Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-12-19T01:44:06.774569Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2017-12-19T01:44:06.776537Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.20) starting as process 8175 ...
2017-12-19T01:44:06.799576Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-12-19T01:44:06.799640Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-12-19T01:44:06.799650Z 0 [Note] InnoDB: Uses event mutexes
2017-12-19T01:44:06.799655Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2017-12-19T01:44:06.799659Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-12-19T01:44:06.799664Z 0 [Note] InnoDB: Using Linux native AIO
2017-12-19T01:44:06.799973Z 0 [Note] InnoDB: Number of pools: 1
2017-12-19T01:44:06.800128Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-12-19T01:44:06.801539Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-12-19T01:44:06.811766Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-12-19T01:44:06.814412Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-12-19T01:44:06.846145Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-12-19T01:44:06.861855Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 62483265
2017-12-19T01:44:06.861889Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 62483274
2017-12-19T01:44:06.861896Z 0 [Note] InnoDB: Database was not shutdown normally!
2017-12-19T01:44:06.861902Z 0 [Note] InnoDB: Starting crash recovery.
2017-12-19T01:44:07.156501Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-12-19T01:44:07.156528Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-12-19T01:44:07.156575Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-12-19T01:44:07.414153Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-12-19T01:44:07.415460Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-12-19T01:44:07.415476Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-12-19T01:44:07.415765Z 0 [Note] InnoDB: Waiting for purge to start
2017-12-19T01:44:07.465984Z 0 [Note] InnoDB: 5.7.20 started; log sequence number 62483274
2017-12-19T01:44:07.466568Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-12-19T01:44:07.468024Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-12-19T01:44:07.470354Z 0 [Note] InnoDB: Buffer pool(s) load completed at 171219 9:44:07
2017-12-19T01:44:07.488290Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2017-12-19T01:44:07.493973Z 0 [Warning] CA certificate ca.pem is self signed.
2017-12-19T01:44:07.496122Z 0 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2017-12-19T01:44:07.496163Z 0 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2017-12-19T01:44:07.496191Z 0 [Note] Server socket created on IP: '0.0.0.0'.
2017-12-19T01:44:07.619414Z 0 [Note] Event Scheduler: Loaded 0 events
2017-12-19T01:44:07.619623Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.20' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
2017-12-19T01:44:08.098536Z 2 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)
求大神解決..莫名其妙,是否是linux的問題? CentOS / 6.8 x86_64 (64bit) 1核1G
uj5u.com熱心網友回復：
能解決我一定詳細描述解決流程... 完美結貼... 只有我遇到這種情況么? 一個純凈BCC, mysql會shutdown?補充一點 mysql運行程序中會有一些外來ip嘗試登錄不存在的賬號(自動試密碼的腳本吧,算是被輕微攻擊), 但是如果我把bind-address改成127.0.0.1的話,沒人攻擊mysqld服務也一樣會shutdown,沒有區別,而且我root賬號只有內網能訪問,試賬號的黑客腳本并不可能影響到資料庫吧? 而且量不大,服務shutdown的時間也不是被腳本嘗試賬號密碼的時間.
也黏貼一下被嘗試登錄資料庫的日志:
大概就是長這一個樣子: (是否需要防一下?配置一下服務器?是這些操作導致的shutdown嗎?)
2017-12-18T19:33:46.844097Z 1793 [Note] Access denied for user 'demo'@'14.215.176.13' (using password: YES)
2017-12-18T19:33:54.960561Z 1969 [Note] Access denied for user 'root'@'14.215.176.20' (using password: NO)
2017-12-18T19:33:54.960768Z 1970 [Note] Access denied for user 'root'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:54.968099Z 1971 [Note] Access denied for user 'root'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.031230Z 1973 [Warning] IP address '14.215.176.21' could not be resolved: Name or service not known
2017-12-18T19:33:55.031390Z 1972 [Warning] IP address '14.215.176.21' could not be resolved: Name or service not known
2017-12-18T19:33:55.035024Z 1972 [Note] Access denied for user 'root'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.035165Z 1973 [Note] Access denied for user 'root'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.042192Z 1975 [Note] Access denied for user 'root'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.084457Z 1977 [Note] Access denied for user 'root'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.084569Z 1978 [Note] Access denied for user 'root'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.091794Z 1979 [Note] Access denied for user 'test'@'14.215.176.21' (using password: NO)
2017-12-18T19:33:55.098697Z 1980 [Note] Access denied for user 'test'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.129198Z 1981 [Note] Access denied for user 'test'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.135625Z 1982 [Note] Access denied for user 'test'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.142358Z 1984 [Note] Access denied for user 'test'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.143155Z 1985 [Note] Access denied for user 'test'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.150254Z 1986 [Note] Access denied for user 'test'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.157125Z 1987 [Note] Access denied for user 'work'@'14.215.176.21' (using password: NO)
2017-12-18T19:33:55.164128Z 1988 [Note] Access denied for user 'work'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.177242Z 1990 [Note] Access denied for user 'work'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.177364Z 1991 [Note] Access denied for user 'test'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.184535Z 1993 [Note] Access denied for user 'work'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.184655Z 1992 [Note] Access denied for user 'work'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.191783Z 1994 [Note] Access denied for user 'work'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.191946Z 1995 [Note] Access denied for user 'work'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.198713Z 1996 [Note] Access denied for user 'work'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.198851Z 1997 [Note] Access denied for user 'pma'@'14.215.176.20' (using password: NO)
2017-12-18T19:33:55.205756Z 1998 [Note] Access denied for user 'pma'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.205902Z 1999 [Note] Access denied for user 'pma'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.212716Z 2000 [Note] Access denied for user 'pma'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.212934Z 2001 [Note] Access denied for user 'pma'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.219713Z 2002 [Note] Access denied for user 'pma'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.219844Z 2003 [Note] Access denied for user 'pma'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.228133Z 2005 [Note] Access denied for user 'admin'@'14.215.176.21' (using password: NO)
2017-12-18T19:33:55.228271Z 2004 [Note] Access denied for user 'pma'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.235132Z 2006 [Note] Access denied for user 'admin'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.235277Z 2007 [Note] Access denied for user 'admin'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.242325Z 2009 [Note] Access denied for user 'admin'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.242454Z 2008 [Note] Access denied for user 'admin'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.249236Z 2011 [Note] Access denied for user 'admin'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.249403Z 2012 [Note] Access denied for user 'admin'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.256147Z 2013 [Note] Access denied for user 'admin'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.256399Z 2014 [Note] Access denied for user 'guest'@'14.215.176.20' (using password: NO)
2017-12-18T19:33:55.263446Z 2016 [Note] Access denied for user 'guest'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.263563Z 2017 [Note] Access denied for user 'guest'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.270647Z 2018 [Note] Access denied for user 'guest'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.270879Z 2019 [Note] Access denied for user 'guest'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.278487Z 2020 [Note] Access denied for user 'guest'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.278738Z 2021 [Note] Access denied for user 'guest'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.285653Z 2022 [Note] Access denied for user 'guest'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.285817Z 2023 [Note] Access denied for user 'demo'@'14.215.176.21' (using password: NO)
2017-12-18T19:33:55.292649Z 2024 [Note] Access denied for user 'demo'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.292785Z 2025 [Note] Access denied for user 'demo'@'14.215.176.21' (using password: YES)
2017-12-18T19:33:55.299657Z 2026 [Note] Access denied for user 'demo'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.299849Z 2027 [Note] Access denied for user 'demo'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.306788Z 2028 [Note] Access denied for user 'demo'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.306929Z 2029 [Note] Access denied for user 'demo'@'14.215.176.20' (using password: YES)
2017-12-18T19:33:55.313644Z 2030 [Note] Access denied for user 'demo'@'14.215.176.20' (using password: YES)
2017-12-18T19:45:26.081151Z 11428 [Warning] IP address '202.74.232.142' could not be resolved: Name or service not known
2017-12-18T19:45:26.178979Z 11428 [Note] Access denied for user 'root'@'202.74.232.142' (using password: NO)
2017-12-18T19:45:26.334961Z 11430 [Note] Access denied for user 'root'@'202.74.232.142' (using password: YES)
2017-12-18T19:45:26.642606Z 11431 [Note] Access denied for user 'root'@'202.74.232.142' (using password: NO)
uj5u.com熱心網友回復：
2017-12-18T19:47:05.029221Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool------------這個看起來似乎是記憶體不足，試試打開這個選項限制一下
# innodb_buffer_pool_size = 128M
uj5u.com熱心網友回復：
innodb_buffer_pool_size 默認是128M可以設定這個引數到8M
在組態檔加
innodb_buffer_pool_size =8M
uj5u.com熱心網友回復：
樓上2位大神的innodb_buffer_pool_size 這個配置我沒開過...我有種預感要解決了....已開 看下還會不會先..uj5u.com熱心網友回復：
還是跪了 依舊shutdown了 還沒有log提示..uj5u.com熱心網友回復：
之前有 log, 現在沒，說明出新問題了轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/101025.html
標籤：MySQL
上一篇：mysql批量更新資料
下一篇：請教SQL執行效率問題