Mysql – Questions since MySQL Startup

MySQLPHPphpmyadminSecurityUbuntu

Have a weird question / (bug?) here. Using PHP My Admin I installed via apt-get on Ubuntu 13.10

Versions are as follows:

Server version: 5.5.35-0ubuntu0.13.10.2 – (Ubuntu)
Apache/2.4.6 (Ubuntu)
Database client version: libmysql – 5.5.35
PHP extension: mysqli Documentation
PHPMyAdmin Version information: 4.0.6deb1

Under the Stats page it is showing the following:

Questions since startup: 2,144
per hour: 405
per minute: 7

Here is the weird thing – about 5 minutes ago it said 5000+ questions since start-up.

I am the only user with access to the database besides the "WWW User" I have created for my PHP pages. My web server is not open to the public – port 80 is blocked on my router, and I have password protection set up in my Apache2.conf file. In order to access my LAMP server on the fly I use ConnectBot for Android to tunnel port 8080 to my computer's port 80. I am not running 405 queries per hour! I have only probably ran at most 20 on the database in the last hour including queries made by PHP scripts. The only way someone other than me could be accessing my LAMP server is if they were using a wifi sniffer and cloning their MAC address to bypass MAC filtering to get past my router – but my router shows nothing unusual connected.

Additionally – it is showing the following, which really has me concerned:

show binlogs    73  13.8    3.40
show tables 34  6.4     1.59
show variables  28  5.3     1.31
show master status  27  5.1     1.26
show status 27  5.1     1.26
show slave status   27  5.1     1.26

With the exception of show tables, I did not know these commands existed. I have not ran a show tables command in several days.

Is someone hacking my database? Or is PHPMyAdmin reporting inaccurate information?

Best Answer

You may have been unaware of this, but everything and its grandmother done in MySQL is a question.

In light of this, the real question is : To mysqld, what is a Question ?

According to the MySQL Documentation on Questions:

The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

Anything you issue for the sake of running queries or just checking a status of some kind is a Question. Even checking how many questions in your session is itself a Question. Here is proof: (I will connect to MySQL 5.6.14 for Windows and ask for Questions in my session):

C:\Windows\system32>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 3     |
+---------------+-------+
1 row in set (0.11 sec)

mysql> show status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 4     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 5     |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

This is normal to expect, so there is really nothing to worry about.

YOUR ORIGINAL QUESTIONS

Is someone hacking my database?

No, you are not being hacked.

Is PHPMyAdmin reporting inaccurate information?

PHPMyAdmin asks mysqld Questions all day long. The result is that it just runs up the global count (As seen from SHOW GLOBAL STATUS;). I wrote an answer to a post in ServerFault about 2.5 years ago entitled 1 billion mysql queries in 24 days? Can something be wrong?. I attributed the runaway stats to monitoring then, and I still strongly assert this now.

Related Solutions

MySQL Index Questions

You must refactor the query to perform WHERE, JOIN and GROUP BY clauses IN THAT EXACT ORDER !!!

Here is the refactored query :

select
    sq.search_id,
    count(1) search_id_count
from
    (select search_id from search where search_id > 47000) sq
    inner join
    (select search_id from search_hit where search_id > 47000) ssh
    using (search_id)
group by
    sq.search_id;

Running OPTIMIZE TABLE (reduces table fragmentation and recreate indexes) may not be necessary unless you do heavy INSERTs, UPDATEs, DELETEs. I'll say it is optional.

Give it a Try !!!

MySQL connection count much larger than query count

Here is something interesting to remember : the status variables Questions takes a count of all queries executed by mysqld, regardless of who issued each query.

Why do I say regardless of who issued each query ?

There are queries that are launched internally by mysqld. Here is a simple example:

mysql> show global status like 'Com_select'; show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 15    |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 56    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Com_select'; show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 15    |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 58    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 59    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 60    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

Please note that issuing a simple query like show global status like 'Questions'; is a query even if your are just requesting internal data from mysqld.

Multiply this by any number of DB Connections coming from MONyog, Munin, Nagios, MySQL Enterprise Monitor, or any other MySQL Monitoring tools and will always have a steady rise in the number of lightweight queries being executed.

I wrote about this before in ServerFault in a question "1 billion mysql queries in 24 hours? Can something be wrong?"

Queries can quitely come and go. The question left to answer is, why so many DB Connections? You need to make sure Apache is closing DB Connections at the same rate as mysqld. You may need to see run netstat and look for DB Connections to mysql that have the TIME_WAIT status.

There are bug reports on this going back to MySQL 4.1 although the root of the problem may actually be with PHP:

Best Answer

YOUR ORIGINAL QUESTIONS

Related Solutions

MySQL Index Questions

MySQL connection count much larger than query count

Related Question