Home / server / Questions / 240713
Accepted
Continuation
Asked: 2011-02-27 18:00:20 +0800 CST

How to tell if any MySQL connections has been dropped or timed out?

  • 772

A client is using PHP to connect to MySQL. The PHP scripts and the MySQL database are located on 2 different Linux servers. He complained that database connections were being dropped or timed out and asked me to take a look.

Is there any place in MySQL that can show me what and how many connections have been dropped or timed out? I looked into slow query log and didn't see anything.

Any suggestions on how to diagnose this dropped/timed out database connection problem?

Thanks

EDIT:

Slow query log is enabled in my.cnf:

log-slow-queries=/var/log/mysql-slow-queries.log

And when I do a 

mysql> show global status;

I got:

| Slow_queries                      | 11402347     |

So there are a lot of slow queries. But the file /var/log/mysql-slow-queries.log doesn't exist. Why is that?

linux mysql php database-connection

3 Answers

  1. Best Answer

    The slow query log file must exist and must be writable by the mysql user. On a standard mysql installation I would do:

    touch /var/log/mysql-slow-queries.log
chown mysql.mysql /var/log/mysql-slow-queries.log

    And then restart mysql.

    If you set the slow log file in a particular directory (i.e. /var/log/mysql/slow.log) with enough permissions, MySQL will create the log file by itself:

    mkdir /var/log/mysql
chown mysql.mysql /var/log/mysql
    • 2

  2. Have you enabled your slow query log? Is it empty or does it work?

    Normally the slow query log is off for performance reasons and you can enable it to debug if you need.

    You can try to use the new MySQL Workbench. With the MySQL Administrator inside the Workbench you can See all open connections in the Tool in a list. Its very helpful to see your connections and manage and monitor your server for a short time.

    Other way is to take the MySQL statement:

    show processlist;

    this shows you the running queries and you can see what's hanging.

    Perhaps i could help you. For the slow query log you get more information on the MySQL site or here

    • 1

  3. mysqladmin extended-status|head can also help here.

    • 0