1

I've a mysql server (v5.6) running on ubuntu server 10 x64. It's always up and running with a medium traffic, but every while it crashes & restarts without lefting any log message behind, after restarting, it begins crash recovery which usually takes about 10 minutes & mysql error.log file resets & it looks like the following:

2017-07-27 10:07:37 23427 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2017-07-27 10:07:37 23427 [Note] Plugin 'FEDERATED' is disabled.
2017-07-27 10:07:37 23427 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-07-27 10:07:37 23427 [Note] InnoDB: The InnoDB memory heap is disabled
2017-07-27 10:07:37 23427 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-07-27 10:07:37 23427 [Note] InnoDB: Memory barrier is not used
2017-07-27 10:07:37 23427 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-07-27 10:07:37 23427 [Note] InnoDB: Using Linux native AIO
2017-07-27 10:07:37 23427 [Note] InnoDB: Not using CPU crc32 instructions
2017-07-27 10:07:37 23427 [Note] InnoDB: Initializing buffer pool, size = 5.0G

2017-07-27 10:07:38 23427 [Note] InnoDB: Completed initialization of buffer pool
2017-07-27 10:07:38 23427 [Note] InnoDB: Highest supported file format is Barracuda.
2017-07-27 10:07:38 23427 [Note] InnoDB: Log scan progressed past the checkpoint lsn 194934547118
2017-07-27 10:07:38 23427 [Note] InnoDB: Database was not shutdown normally!
2017-07-27 10:07:38 23427 [Note] InnoDB: Starting crash recovery.
2017-07-27 10:07:38 23427 [Note] InnoDB: Reading tablespace information from the .ibd files...

Is there anything that I can do to preserve log which shows crash source, so I would be able to fix the problem.

Seybsen
  • 125
  • 7
Ehsan Khodarahmi
  • 305
  • 1
  • 7
  • 18
  • Have you gone through the steps in the [MySQL manual](https://dev.mysql.com/doc/refman/5.7/en/starting-server-troubleshooting.html)? –  Jul 27 '17 at 06:14

3 Answers3

3

It is possible that from time to time MySQL server fall prey of Linux Out-of-Memory (OOM) killer process. You may find traces of such activity in /var/log/syslog or in an output of the dmesg command.

If system got short on RAM and killed MySQL, you could adjust settings of MySQL server process to avoid this. For example, use MySQLTuner-perl script. Performance Metrics in its output shows maximum possible memory usage by MySQL server process in percent of installed RAM.

RLazar
  • 101
  • 3
  • Here is what I found in syslog file: `kernel: [3541047.134888] type=1400 audit(1501133854.102:20): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/dev/tty" pid=14685 comm="mysqld" requested_mask="rw" denied_mask="rw" fsuid=107 ouid=0`. I guess its about opened file limit, but how can I determine the threshold? – Ehsan Khodarahmi Jul 28 '17 at 08:43
  • The quoted message is not about an open files limit. `/usr/sbin/mysqld` could not write to its TTY. Add line `/dev/tty rw,` to `/etc/apparmor.d/usr.sbin.mysqld` and MySQL will be able to output a real error message. – RLazar Jul 28 '17 at 11:37
  • As to checking open files limit in mysqld process, connect to it with `mysql` and run `show global variables like '%open_files_limit%';` – RLazar Jul 28 '17 at 11:40
  • & where exactly mysql will output the real error message? – Ehsan Khodarahmi Jul 28 '17 at 13:11
  • Messages still should appear in `/var/log/mysql/error.log`. – RLazar Jul 28 '17 at 14:13
0

To Answer your question, Is there anything that I can do to preserve log which shows crash source, so I would be able to fix the problem.
Yes, in your .cnf within [mysqld] section, add a new line for - expire_logs_days = 7 # from default 0 mm/dd/ccyy by xx You will have 7 days available to research before the oldest rolls off. Browse in your directory where you found error.log to see them.

Wilson Hauck
  • 472
  • 5
  • 11
0

I faced the same issue today on windows 10 machine. The server kept crashing at startup leaving nothing in logs of event viewer.

Just out of luck I discovered that Mysql community server (at-least version 8.x) crashed if the location we provided in setting in my.ini file is not live.

This could happen in case you updated this setting to load data files from a portable device that does not exist any more or simply your path to is incorrect

jdsurya
  • 101
  • 1
  • Here's the detailed bug report I filed on MySQL community for your reference https://bugs.mysql.com/bug.php?id=94826 – jdsurya Mar 29 '19 at 09:53