某个Ubuntu系统,其中的mysql,偶尔会突然挂掉,导致小程序都不显示数据了:

且点击按钮也没反应。
去服务器中看看情况:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | root@VM - 158 - 80 - ubuntu:~ / xxx # service mysql status ● mysql.service - MySQL Community Server Loaded: loaded ( / lib / systemd / system / mysql.service; bad; vendor preset: enabled) Active: inactive (dead) since Fri 2019 - 06 - 07 13 : 39 : 13 CST; 7h ago Main PID: 1271 (code = exited, status = 0 / SUCCESS) Jun 07 13 : 39 : 13 VM - 158 - 80 - ubuntu mysqld[ 1271 ]: 2019 - 06 - 07T05 : 39 : 13.429642Z 0 [Note] InnoDB: Removed temporary tablespace data file : "ibtmp1" Jun 07 13 : 39 : 13 VM - 158 - 80 - ubuntu mysqld[ 1271 ]: 2019 - 06 - 07T05 : 39 : 13.429654Z 0 [Note] Shutting down plugin 'MRG_MYISAM' Jun 07 13 : 39 : 13 VM - 158 - 80 - ubuntu mysqld[ 1271 ]: 2019 - 06 - 07T05 : 39 : 13.429659Z 0 [Note] Shutting down plugin 'MyISAM' Jun 07 13 : 39 : 13 VM - 158 - 80 - ubuntu mysqld[ 1271 ]: 2019 - 06 - 07T05 : 39 : 13.429666Z 0 [Note] Shutting down plugin 'CSV' Jun 07 13 : 39 : 13 VM - 158 - 80 - ubuntu mysqld[ 1271 ]: 2019 - 06 - 07T05 : 39 : 13.429671Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA' Jun 07 13 : 39 : 13 VM - 158 - 80 - ubuntu mysqld[ 1271 ]: 2019 - 06 - 07T05 : 39 : 13.429705Z 0 [Note] Shutting down plugin 'sha256_password' Jun 07 13 : 39 : 13 VM - 158 - 80 - ubuntu mysqld[ 1271 ]: 2019 - 06 - 07T05 : 39 : 13.429708Z 0 [Note] Shutting down plugin 'mysql_native_password' Jun 07 13 : 39 : 13 VM - 158 - 80 - ubuntu mysqld[ 1271 ]: 2019 - 06 - 07T05 : 39 : 13.429848Z 0 [Note] Shutting down plugin 'binlog' Jun 07 13 : 39 : 13 VM - 158 - 80 - ubuntu mysqld[ 1271 ]: 2019 - 06 - 07T05 : 39 : 13.430514Z 0 [Note] / usr / sbin / mysqld: Shutdown complete Jun 07 13 : 39 : 13 VM - 158 - 80 - ubuntu systemd[ 1 ]: Stopped MySQL Community Server. root@VM - 158 - 80 - ubuntu:~ / xxx # service mysql restart root@VM - 158 - 80 - ubuntu:~ / xxx # service mysql status ● mysql.service - MySQL Community Server Loaded: loaded ( / lib / systemd / system / mysql.service; bad; vendor preset: enabled) Active: active (running) since Fri 2019 - 06 - 07 21 : 23 : 54 CST; 2s ago Process: 408 ExecStartPost = / usr / share / mysql / mysql - systemd - start post (code = exited, status = 0 / SUCCESS) Process: 396 ExecStartPre = / usr / share / mysql / mysql - systemd - start pre (code = exited, status = 0 / SUCCESS) Main PID: 407 (mysqld) Tasks: 28 Memory: 183.6M CPU: 285ms CGroup: / system. slice / mysql.service └─ 407 / usr / sbin / mysqld Jun 07 21 : 23 : 54 VM - 158 - 80 - ubuntu mysqld[ 407 ]: 2019 - 06 - 07T13 : 23 : 54.047140Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not Jun 07 21 : 23 : 54 VM - 158 - 80 - ubuntu mysqld[ 407 ]: 2019 - 06 - 07T13 : 23 : 54.047695Z 0 [Note] Server hostname (bind - address): '127.0.0.1' ; port: 3306 Jun 07 21 : 23 : 54 VM - 158 - 80 - ubuntu mysqld[ 407 ]: 2019 - 06 - 07T13 : 23 : 54.047932Z 0 [Note] - '127.0.0.1' resolves to '127.0.0.1' ; Jun 07 21 : 23 : 54 VM - 158 - 80 - ubuntu mysqld[ 407 ]: 2019 - 06 - 07T13 : 23 : 54.048229Z 0 [Note] Server socket created on IP: '127.0.0.1' . Jun 07 21 : 23 : 54 VM - 158 - 80 - ubuntu mysqld[ 407 ]: 2019 - 06 - 07T13 : 23 : 54.561969Z 0 [Note] Event Scheduler: Loaded 0 events Jun 07 21 : 23 : 54 VM - 158 - 80 - ubuntu mysqld[ 407 ]: 2019 - 06 - 07T13 : 23 : 54.562600Z 0 [Note] / usr / sbin / mysqld: ready for connections. Jun 07 21 : 23 : 54 VM - 158 - 80 - ubuntu mysqld[ 407 ]: Version: '5.7.23-0ubuntu0.16.04.1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu) Jun 07 21 : 23 : 54 VM - 158 - 80 - ubuntu mysqld[ 407 ]: 2019 - 06 - 07T13 : 23 : 54.582214Z 2 [Note] Access denied for user 'root' @ 'localhost' (using password: NO) Jun 07 21 : 23 : 54 VM - 158 - 80 - ubuntu systemd[ 1 ]: Started MySQL Community Server. Jun 07 21 : 23 : 55 VM - 158 - 80 - ubuntu mysqld[ 407 ]: 2019 - 06 - 07T13 : 23 : 55.011997Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190607 21 : 23 : 55 |

重启后,小程序数据正常了:

mysql突然挂的原因目前没找到
去找找mysql的log:
【已解决】Ubuntu中mysql的log日志文件在哪
而对于此处的bin二进制log文件
好像这个mariadb-bin.000392
最后修改时间
13:39
和mysql挂掉的时间:
2019-06-07 13:39:13 CST; 7h ago
是一致的。
然后继续研究,mysql为何会突然,偶尔挂掉:
ubuntu mysql sometime stop
【已解决】Ubuntu中mysql然后开启error的log及配置中提示:Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf
期间:
【已解决】Ubuntu查看系统版本号
然后再去:
【已解决】Ubuntu中mysql警告:Warning Changed limits max_open_files 1024 requested 5000
但是又出现其他错误:
/var/log/mysql/mysql_error.log
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 2019 - 06 - 07T14 : 29 : 01.455551Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead. 2019 - 06 - 07T14 : 29 : 01.455618Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use - - explicit_defaults_for_timestamp server option (see documentation for more details). 2019 - 06 - 07T14 : 29 : 01.457628Z 0 [Warning] Insecure configuration for - - secure - file - priv: Location is accessible to all OS users. Consider choosing a different directory. 2019 - 06 - 07T14 : 29 : 01.457665Z 0 [Note] / usr / sbin / mysqld (mysqld 5.7 . 23 - 0ubuntu0 . 16.04 . 1 - log) starting as process 7522 ... 2019 - 06 - 07T14 : 29 : 01.483674Z 0 [Note] InnoDB: PUNCH HOLE support available 2019 - 06 - 07T14 : 29 : 01.483716Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2019 - 06 - 07T14 : 29 : 01.483721Z 0 [Note] InnoDB: Uses event mutexes 2019 - 06 - 07T14 : 29 : 01.483724Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2019 - 06 - 07T14 : 29 : 01.483727Z 0 [Note] InnoDB: Compressed tables use zlib 1.2 . 8 2019 - 06 - 07T14 : 29 : 01.483752Z 0 [Note] InnoDB: Using Linux native AIO 2019 - 06 - 07T14 : 29 : 01.483961Z 0 [Note] InnoDB: Number of pools: 1 2019 - 06 - 07T14 : 29 : 01.484054Z 0 [Note] InnoDB: Using CPU crc32 instructions 2019 - 06 - 07T14 : 29 : 01.496562Z 0 [Note] InnoDB: Initializing buffer pool, total size = 256M , instances = 1 , chunk size = 128M 2019 - 06 - 07T14 : 29 : 01.513839Z 0 [Note] InnoDB: Completed initialization of buffer pool 2019 - 06 - 07T14 : 29 : 01.518149Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2019 - 06 - 07T14 : 29 : 01.528144Z 0 [ERROR] InnoDB: Unable to lock . / ibdata1 error: 11 2019 - 06 - 07T14 : 29 : 01.528183Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. 2019 - 06 - 07T14 : 29 : 01.528251Z 0 [Note] InnoDB: Retrying to lock the first data file 2019 - 06 - 07T14 : 29 : 02.295188Z 3 [Note] Access denied for user 'root' @ 'localhost' (using password: NO) 2019 - 06 - 07T14 : 29 : 02.528502Z 0 [ERROR] InnoDB: Unable to lock . / ibdata1 error: 11 2019 - 06 - 07T14 : 29 : 02.528536Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. ... 2019 - 06 - 07T14 : 29 : 39.535340Z 0 [ERROR] InnoDB: Unable to lock . / ibdata1 error: 11 2019 - 06 - 07T14 : 29 : 39.535387Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. |
第二天,mysql又挂了,所以重启,重启后的log中,没了昨天的innodb的错误了:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | 2019 - 06 - 08T03 : 01 : 23.744220Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead. 2019 - 06 - 08T03 : 01 : 23.744285Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use - - explicit_defaults_for_timestamp server option (see documentation for more details). 2019 - 06 - 08T03 : 01 : 23.745910Z 0 [Warning] Insecure configuration for - - secure - file - priv: Location is accessible to all OS users. Consider choosing a different directory. 2019 - 06 - 08T03 : 01 : 23.745950Z 0 [Note] / usr / sbin / mysqld (mysqld 5.7 . 23 - 0ubuntu0 . 16.04 . 1 - log) starting as process 18166 ... 2019 - 06 - 08T03 : 01 : 23.759860Z 0 [Note] InnoDB: PUNCH HOLE support available 2019 - 06 - 08T03 : 01 : 23.759891Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2019 - 06 - 08T03 : 01 : 23.759895Z 0 [Note] InnoDB: Uses event mutexes 2019 - 06 - 08T03 : 01 : 23.759898Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2019 - 06 - 08T03 : 01 : 23.759901Z 0 [Note] InnoDB: Compressed tables use zlib 1.2 . 8 2019 - 06 - 08T03 : 01 : 23.759904Z 0 [Note] InnoDB: Using Linux native AIO 2019 - 06 - 08T03 : 01 : 23.760175Z 0 [Note] InnoDB: Number of pools: 1 2019 - 06 - 08T03 : 01 : 23.760299Z 0 [Note] InnoDB: Using CPU crc32 instructions 2019 - 06 - 08T03 : 01 : 23.764425Z 0 [Note] InnoDB: Initializing buffer pool, total size = 256M , instances = 1 , chunk size = 128M 2019 - 06 - 08T03 : 01 : 23.782056Z 0 [Note] InnoDB: Completed initialization of buffer pool 2019 - 06 - 08T03 : 01 : 23.785292Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2019 - 06 - 08T03 : 01 : 23.994320Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 2019 - 06 - 08T03 : 01 : 25.135946Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2019 - 06 - 08T03 : 01 : 25.136022Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2019 - 06 - 08T03 : 01 : 25.545058Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2019 - 06 - 08T03 : 01 : 25.545963Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2019 - 06 - 08T03 : 01 : 25.545982Z 0 [Note] InnoDB: 32 non - redo rollback segment(s) are active. 2019 - 06 - 08T03 : 01 : 25.584492Z 0 [Note] InnoDB: 5.7 . 23 started; log sequence number 29032734 2019 - 06 - 08T03 : 01 : 25.584740Z 0 [Note] InnoDB: Loading buffer pool(s) from / var / lib / mysql / ib_buffer_pool 2019 - 06 - 08T03 : 01 : 25.584784Z 0 [Note] Plugin 'FEDERATED' is disabled. 2019 - 06 - 08T03 : 01 : 26.039874Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key 2019 - 06 - 08T03 : 01 : 26.039922Z 0 [Note] Server hostname (bind - address): '127.0.0.1' ; port: 3306 2019 - 06 - 08T03 : 01 : 26.039938Z 0 [Note] - '127.0.0.1' resolves to '127.0.0.1' ; 2019 - 06 - 08T03 : 01 : 26.040028Z 0 [Note] Server socket created on IP: '127.0.0.1' . 2019 - 06 - 08T03 : 01 : 26.089750Z 0 [Note] Event Scheduler: Loaded 0 events 2019 - 06 - 08T03 : 01 : 26.089966Z 0 [Note] / usr / sbin / mysqld: ready for connections. Version: '5.7.23-0ubuntu0.16.04.1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu) 2019 - 06 - 08T03 : 01 : 26.601092Z 2 [Note] Access denied for user 'root' @ 'localhost' (using password: NO) 2019 - 06 - 08T03 : 01 : 27.235923Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190608 11 : 01 : 27 |
所以至此,mysql突然挂掉,或者没有挂掉,但是小程序中没数据的问题,还是没解决。
抽空再去研究原因。
【后记20190618】
此刻,小程序又不能用了
看来是内部mysql数据库又出问题了。
只不过现象和之前是类似的:
1 2 3 4 5 6 7 8 9 10 11 12 13 | # service mysql status ● mariadb.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) Drop-In: /etc/systemd/system/mariadb.service.d └─migrated-from-my.cnf-settings.conf Active: active (running) since Tue 2019-06-18 21:36:03 CST; 2min 9s ago Main PID: 356 (mysqld) CGroup: /system.slice/mariadb.service └─356 /usr/sbin/mysqld Jun 18 21:36:02 VM-158-80-ubuntu systemd[1]: Starting MySQL Community Server... Jun 18 21:36:03 VM-158-80-ubuntu systemd[1]: Started MySQL Community Server. |
虽然还running呢,但是实际上有问题,导致小程序出错无法使用了。
看看django程序的log
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | # tail -n 40 access.log response = self .process_exception_by_middleware(e, request) File "/usr/local/lib/python3.5/dist-packages/django/core/handlers/base.py" , line 124 , in _get_response response = wrapped_callback(request, * callback_args, * * callback_kwargs) File "./libs/auth.py" , line 17 , in wrapper request.wx_user = WxUser.objects. filter ( id = token).first() File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py" , line 602 , in first for obj in ( self if self .ordered else self .order_by( 'pk' ))[: 1 ]: File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py" , line 268 , in __iter__ self ._fetch_all() File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py" , line 1183 , in _fetch_all self ._result_cache = list ( self ._iterable_class( self )) File "/usr/local/lib/python3.5/dist-packages/django/db/models/query.py" , line 54 , in __iter__ results = compiler.execute_sql(chunked_fetch = self .chunked_fetch, chunk_size = self .chunk_size) File "/usr/local/lib/python3.5/dist-packages/django/db/models/sql/compiler.py" , line 1059 , in execute_sql cursor = self .connection.cursor() File "/usr/local/lib/python3.5/dist-packages/django/db/backends/base/base.py" , line 255 , in cursor return self ._cursor() File "/usr/local/lib/python3.5/dist-packages/django/db/backends/base/base.py" , line 232 , in _cursor self .ensure_connection() File "/usr/local/lib/python3.5/dist-packages/django/db/backends/base/base.py" , line 216 , in ensure_connection self .connect() File "/usr/local/lib/python3.5/dist-packages/django/db/utils.py" , line 89 , in __exit__ raise dj_exc_value.with_traceback(traceback) from exc_value File "/usr/local/lib/python3.5/dist-packages/django/db/backends/base/base.py" , line 216 , in ensure_connection self .connect() File "/usr/local/lib/python3.5/dist-packages/django/db/backends/base/base.py" , line 194 , in connect self .connection = self .get_new_connection(conn_params) File "/usr/local/lib/python3.5/dist-packages/django/db/backends/mysql/base.py" , line 227 , in get_new_connection return Database.connect( * * conn_params) File "/usr/local/lib/python3.5/dist-packages/pymysql/__init__.py" , line 94 , in Connect return Connection( * args, * * kwargs) File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py" , line 327 , in __init__ self .connect() File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py" , line 629 , in connect raise exc django.db.utils.OperationalError: ( 2003 , "Can't connect to MySQL server on 'localhost' ([Errno 99] Cannot assign requested address)" ) [pid: 18359 |app: 0 |req: 22 / 279 ] 114.219 . 21.127 () { 40 vars in 934 bytes} [Tue Jun 18 19 : 01 : 21 2019 ] GET / api / event / list ?token = % 252FAVboaM5lxh % 252BDodQiYS15w % 253D % 253D = > generated 196272 bytes in 128 msecs (HTTP / 1.1 500 ) 4 headers in 130 bytes ( 2 switches on core 0 ) Not Found: / [pid: 18357 |app: 0 |req: 16 / 280 ] 198.108 . 67.16 () { 34 vars in 393 bytes} [Tue Jun 18 20 : 05 : 13 2019 ] GET / = > generated 2034 bytes in 3 msecs (HTTP / 1.1 404 ) 3 headers in 102 bytes ( 2 switches on core 0 ) [pid: 18359 |app: 0 |req: 23 / 281 ] 119.29 . 151.95 () { 36 vars in 584 bytes} [Tue Jun 18 21 : 37 : 37 2019 ] GET / admin / login / ? next = / admin / = > generated 1821 bytes in 23 msecs (HTTP / 1.1 200 ) 7 headers in 395 bytes ( 2 switches on core 0 ) |
果然是mysql挂了。
转载请注明:在路上 » 【未解决】Ubuntu中mysql有时候会突然挂掉