Log number of hits !!!

MySQL & MariaDB Other MySQL® Questions
1

Hello all,

My percona DB server serves data to the web server the whole day.

I need to log how many hits do i get from the sever in 24 hours and also weekly.

Is there any script that i can install & enable for it to record these details ?

Please assist !

Thank you

2

If I understand your query you wanted to know how many connection being made to mysql server from apache or your application each day.
Just use show global status and trap the specific variables you want, in your case show global status like ‘Connections’.
you can also get how many select, insert or delete operations happened on database in certain period of time.

For reporting you can use munin monitoring to generate graphs for any status variables.

3

Hello Yogesh,

Thank you for the heads up, but when i try to run “[COLOR=#252C2F]show global status like ‘Connections’” i get the something like this :-

mysql> show global status like 'Connections';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Connections | 82314805 |
+---------------+----------+

I guess that is the total number of connections it has got from the web server., How can i get the number of connections per day, without using any other third party tool like “munin” ?

I would like to clarify, I have a separate DB and separate web server !!

Thank you !

4

If you issue this command once in a day and subtract previous day’s number from today’s number you will get the total hits for a day. setup this command in cron.
show global status like ‘Connections’;

you can write something tike this :

#!/bin/bash
conn=mysql -e "show global status like 'Conn%'" | grep Conn | awk '{print $2'}
lconn=awk 'END{print $1}' /tmp/totconn
diff=$[conn - lconn]
printf “%s\t%s\n” “$conn” “$diff” >> /tmp/totconn

file output would be:

2137811 27
2137812 1
2137814 2

first column is the total connections and second column is the difference. Execute it once in a day. Just try it out!!!

5

Thank you for the script Yogesh,

Will try it out and let you know how it goes,

In the mean time, i have some emergency issue at hand and i have opened two different post for the same.

Thank you