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

database connectionlinuxMySQLPHP

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?

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
Related Topic