How would one check MySQL?

[Keiro]

So I had a customer come to me asking me to check his domain.

I see the error establishing connection message. SSH in and I see this:

@server

[root@daedalus ~]# service mysqld statusmysqld dead but subsys locked
[root@daedalus ~]# service mysqld stop
Stopping mysqld: [ OK ]
[root@daedalus ~]# service mysqld start
Starting mysqld: [ OK ]

Now, the log itself says:

@mysqld.log

150906 03:46:48 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
150906 12:14:05 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150906 12:14:05 InnoDB: Initializing buffer pool, size = 8.0M
150906 12:14:05 InnoDB: Completed initialization of buffer pool
InnoDB: Log scan progressed past the checkpoint lsn 2 3610098884
150906 12:14:05 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer…
InnoDB: Doing recovery: scanned up to log sequence number 2 3610099732
150906 12:14:05 InnoDB: Starting an apply batch of log records to the database…
InnoDB: Progress in percents: 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 43 44 45 46 47 48 49 50 51 52 53 54$
InnoDB: Apply batch completed
150906 12:14:06 InnoDB: Started; log sequence number 2 3610099732
150906 12:14:06 [Note] Event Scheduler: Loaded 0 events
150906 12:14:06 [Note] /usr/libexec/mysqld: ready for connections.
Version: ‘5.1.73’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 Source distribution
150906 12:25:02 [Note] /usr/libexec/mysqld: Normal shutdown

150906 12:25:02 [Note] Event Scheduler: Purging the queue. 0 events
150906 12:25:02 InnoDB: Starting shutdown…
150906 12:25:02 InnoDB: Shutdown completed; log sequence number 2 3610664100
150906 12:25:02 [Note] /usr/libexec/mysqld: Shutdown complete

150906 12:25:02 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
150906 12:25:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150906 12:25:03 InnoDB: Initializing buffer pool, size = 8.0M
150906 12:25:03 InnoDB: Completed initialization of buffer pool
150906 12:25:03 InnoDB: Started; log sequence number 2 3610664100
150906 12:25:03 [Note] Event Scheduler: Loaded 0 events
150906 12:25:03 [Note] /usr/libexec/mysqld: ready for connections.
Version: ‘5.1.73’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 Source distribution

Obviously, something is very wrong here… and it may be because I just barely woke up, but I’m not seeing anything immediately obvious besides this bit which doesn’t tell me anything useful:

InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer…
InnoDB: Doing recovery: scanned up to log sequence number 2 3610099732
150906 12:14:05 InnoDB: Starting an apply batch of log records to the database…
InnoDB: Progress in percents: 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 43 44 45 46 47 48 49 50 51 52 53 54$
InnoDB: Apply batch completed

What exactly caused the SQL server to die?

Edit: Also, already optimized and repaired tables, but I’m still working on investigating exactly what caused the server to die since there doesn’t seem to be anything obviously wrong with the server.

[d2d4j]

Hi kerio

I would check your graphs to see how many connections and queries were present at or just before restart.

Also, as you say, there’s no logged information to help or show, therefore, if it happens again, you would need to set your logging to debug level until it happens again, to have a clearer insight to the cause.

I would also check the update logs to see if there were any updates at or around the failure time.

Lastly, not knowing your setup, but I would also check to make sure the raid battery is working normally, and the consistency of the drives.

I hope that helps

Many thanks

John

[Keiro]

[QUOTE=d2d4j;27772]Hi kerio

I would check your graphs to see how many connections and queries were present at or just before restart.

Also, as you say, there’s no logged information to help or show, therefore, if it happens again, you would need to set your logging to debug level until it happens again, to have a clearer insight to the cause.

I would also check the update logs to see if there were any updates at or around the failure time.

Lastly, not knowing your setup, but I would also check to make sure the raid battery is working normally, and the consistency of the drives.

I hope that helps

Many thanks

John[/QUOTE]

John, thank you for the clear answers.

As to the logging option… we should have the server set up to automatically log all error messages. To not have this done from initial install of InterWorx is, in my mind… a very dangerous thing to have, as you may not notice issues until it’s too late.

Also, details of /etc/my.cnf now:

@/etc/my.cnf

[mysqld]old-passwords=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

Enable logging.

#log-bin
log
log-error
log-slow-queries
#log-bin=/var/log/mysql-bin.log
log=/var/log/mysql.log
log-error=/var/log/mysql-errors.log
log-slow-queries=/var/log/mysql-slowquery.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqld_safe]
log-bin=/var/log/mysql-bin.log
log=/var/log/mysql.log
log-error=/var/log/mysql-errors.log
log-slow-queries=/var/log/mysql-slowquery.log
pid-file=/var/run/mysqld/mysqld.pid

Enabling log-bin logging causes MySQL to not start. I’ve commented it out for now. But this should do quite nicely. At least, right up until the server has issues again. By that point we should know what’s going on.

Also, this server is running on a VPS, so it’s pretty much fairly well protected, at least hardware-wise. For the most part, anyway.

[d2d4j]

Hi kerio

Many thanks, and I understand what your saying over logging, but please understand there are different types of logging and more importantly, there’s a space consideration as well as resource usage.

I would turn on debug logging from nodeworx, server, settings, logging set to debug.

This should give extra details.

Once your happy, my advice is to revert it back, to save space and resource.

It could just be one of those events that is extremely rare and may not happen again, but it’s cause I would think is not interworx, it would be distro, MySQL or even an errant code from website I think.

I hope that helps, and sorry if I’m wrong but please post if you find anything or it happens again

Many thanks

John

[Keiro]

[QUOTE=d2d4j;27774]Hi kerio

Many thanks, and I understand what your saying over logging, but please understand there are different types of logging and more importantly, there’s a space consideration as well as resource usage.

I would turn on debug logging from nodeworx, server, settings, logging set to debug.

This should give extra details.

Once your happy, my advice is to revert it back, to save space and resource.

It could just be one of those events that is extremely rare and may not happen again, but it’s cause I would think is not interworx, it would be distro, MySQL or even an errant code from website I think.

I hope that helps, and sorry if I’m wrong but please post if you find anything or it happens again

Many thanks

John[/QUOTE]

Oh, I understand all too well. I just think that if nothing else, at least error logging should be enabled. This way you can monitor for errors. No need to monitor everything else unless that’s needed. Although to be honest, disk space is fairly cheap these days.

Right now I have mine logging everything at least until I find out why it’s crashed. Aside from that, yeah, I will be posting back here what the cause was and what I’ve done to fix it.