0

I have a MySQL instance acting as a replication slave and something recently caused it to crash. I suspected InnoDB page corruption and wanted to switch from one-shared-tablespace (for InnoDB) to file-per-table, so I went through this process mostly to good effect. (I had a server crash part of the way through the restore, but I re-ran from the middle and was able to get back up and running).

I restarted the replication slave and started catching back up with the master. Until sometime around the middle of the night when MySQL same down again.

No error messages are in any logs that I can find, other than in /var/log/daemon.log:

Nov 17 04:12:32 myserver mysqld_safe: Number of processes running now: 0
Nov 17 04:12:32 myserver mysqld_safe: mysqld restarted
Nov 17 04:12:32 myserver mysqld: 2017-11-17 04:12:32 0 [Warning] Using unique option prefix key_buffer instead of 
key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
Nov 17 04:12:32 myserver mysqld: 2017-11-17 04:12:32 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecat
ed. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
Nov 17 04:12:32 myserver mysqld: 2017-11-17 04:12:32 0 [Note] /usr/sbin/mysqld (mysqld 5.6.38-1~dotdeb+7.1-log) st
arting as process 8811 ...
Nov 17 04:12:33 myserver mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended

Attempts to start the server result in the same messages in my log file.

Setting innodb_force_recovery=1 in my my.cnf file allows me to start the server, albeit in read-only mode. But I can't seem to determine which table or tables are causing the crash.

I have run innochecksum against every on-disk *.ibd file I can find, as well as the primary ibdata1 file, and no errors are reported.

How can I get more information about whatever is causing my server to crash/panic/quit/exit immediately after starting? I've tried running mysqld directly with the -v switch, but I get nothing more than I got in my daemon.log file.

How can I turn-up the log-level on the mysqld process to get some actual information about what's going on? I have some multi-million-record tables, here, and simply wiping things out and restoring from a backup takes a really long time.

UPDATE 2017-11-20

I'm including my logging configuration for reference:

#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#slow_query_log = 1
#slow_query_log_file = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
# NOTE: Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf
log_error=
syslog (this line comes from /etc/mysql/conf.d/mysqld_sage_syslog.cnf)

None of the files in /var/log/ seem to have anything useful in them. When running mysqld from the command-line (where logs are sent to stdout), the process exits without any indication as to why.

  • mysqld.log or mysqld.err ? -- including when you had the crash during recovery. – Rick James Nov 18 '17 at 1:14
  • @RickJames I think everything is going to syslog. This is a Debian-based system, but I can turn on the various other log files I think. All the MySQL-specific log files under /var/log are all zero-byte files. – Christopher Schultz Nov 19 '17 at 15:23
  • Which log-related option(s) should I set in order to get useful logs outside of syslog? – Christopher Schultz Nov 19 '17 at 15:46
  • MySQL normally write to its own logs. – Rick James Nov 19 '17 at 19:12
  • @RickJames Not with Debian packages... everything goes to syslog. I'll update the question with the current logging configuration. – Christopher Schultz Nov 20 '17 at 14:26
0

If --log-error is given without naming a file, mysqld writes the error log to a file named host_name.err in the data directory. I'm thinking that this is turning off the logging before syslog gets involved:

log_error=

What version? 5.7.5 has log_syslog. It seems to be replacing syslog.

See this bug about failure of syslog https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=745071

  • I'm not affected by this bug; MySQL is logging to syslog... just nothing that indicates why it's failing. – Christopher Schultz Nov 21 '17 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.