最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【未解决】Ubuntu中mysql有时候会突然挂掉

MySQL crifan 224浏览 0评论
某个Ubuntu系统,其中的mysql,偶尔会突然挂掉,导致小程序都不显示数据了:
且点击按钮也没反应。
去服务器中看看情况:
[email protected]:~/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.
[email protected]:~/xxx# service mysql restart
[email protected]:~/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
Why did MySQL suddenly stopped on my server? | DigitalOcean
Mysql server stops very frequently | DigitalOcean
【已解决】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
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的错误了:
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数据库又出问题了。
只不过现象和之前是类似的:
# 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
# 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有时候会突然挂掉

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
88 queries in 0.132 seconds, using 20.79MB memory