MySQL sometime it's down (not stop and not restart)

[Blueman]

Hello,

During 12 hrs apart my MySQL (5.1.41) service down with no service stoped and also it's not restart with directadmin. I changed my my.cnf with /usr/share/mysql/my-large.cnf but the problem didn't solve the problem 6 hrs later I've got the same problem.

My questions is

1. How can I detect who (user) use many sql load ?

2. How can I protect MySQL service from down or if it down it must stop (so directadmin can restart it) ?

3. How to open mysqlerrorlog.err (1.68 GB) ?

If you have any suggestions please tell me.

Thank you
Blueman

 

[tillo]

Automatically kill and restart a stalled MySQL distribution is a bad idea: there is a reason MySQL is stalling, and you have to find and remove the cause first.

You can't see which privilege uses MySQL the most without a binary log and some dedicated application, but you may see why MySQL stalls by reading the last few lines of the mysqlerrorlog.err logfile with "tail -n 500 mysqlerrorlog.err". Since the error logfile should be much smaller than that, you have a good chance to find the problem within it and remove the cause.

 

[Blueman]

Hello,

Log isn't make sense for me. below is log which save during MySQL service crash.

100121 9:00:58 InnoDB: Started; log sequence number 0 164067455
100121 9:01:00 [Note] Event Scheduler: Loaded 0 events
100121 9:01:00 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
100121 09:01:20 mysqld_safe Number of processes running now: 0
100121 09:01:20 mysqld_safe mysqld restarted
100121 9:01:20 [Note] Plugin 'FEDERATED' is disabled.
100121 9:01:21 InnoDB: Started; log sequence number 0 164067455
100121 9:01:21 [Note] Recovering after a crash using mysql-bin
100121 9:01:21 [Note] Starting crash recovery...
100121 9:01:21 [Note] Crash recovery finished.
100121 9:01:21 [Note] Event Scheduler: Loaded 0 events
100121 9:01:21 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
100121 10:37:11 [Note] /usr/sbin/mysqld: Normal shutdown

100121 10:37:11 [Note] Event Scheduler: Purging the queue. 0 events
100121 10:37:13 InnoDB: Starting shutdown...
100121 10:37:15 InnoDB: Shutdown completed; log sequence number 0 164072619
100121 10:37:15 [Note] /usr/sbin/mysqld: Shutdown complete

100121 10:37:15 mysqld_safe mysqld from pid file /var/lib/mysql/ns1.mywebsite.com.pid ended
100121 10:37:16 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
100121 10:37:16 [Note] Plugin 'FEDERATED' is disabled.
100121 10:37:16 InnoDB: Started; log sequence number 0 164072619
100121 10:37:16 [Note] Event Scheduler: Loaded 0 events
100121 10:37:16 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
100121 12:42:54 [Note] /usr/sbin/mysqld: Normal shutdown

100121 12:42:54 [Note] Event Scheduler: Purging the queue. 0 events
100121 12:42:57 InnoDB: Starting shutdown...
100121 12:42:58 InnoDB: Shutdown completed; log sequence number 0 164079684
100121 12:42:58 [Note] /usr/sbin/mysqld: Shutdown complete

100121 12:42:58 mysqld_safe mysqld from pid file /var/lib/mysql/ns1.mywebsite.com.pid ended
100121 12:42:59 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
100121 12:42:59 [Note] Plugin 'FEDERATED' is disabled.
100121 12:42:59 InnoDB: Started; log sequence number 0 164079684
100121 12:43:00 [Note] Event Scheduler: Loaded 0 events
100121 12:43:00 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

Is there any keywords or patten to show me cause of the problem ?

Thank you
Blueman