Sometimes, MySQL will make your server pain if you have user abusing it. Mick Genie will teach you to identify which MySQL database creating the problem to your server.
Actually MySQL can log all of the those query that taking longer time than certain seconds.
1. Login to the server as root.
2. Open your my.cnf with your editor.
3. In the [mysqld] section, add the line as below.
log-slow-queries = /var/log/mysql-slow.log long_query_time = 3
this meant that the log will logged to /var/log/mysql-slow.log and the query time is 3 seconds.
4. Save the my.cnf by typing “:wq!” and press enter.
5. Now, change the ownership of the mysql-slow.log file to the mysql owner to allow it to write the log.
chown mysql.root /var/log/mysql-slow.log
6. Restart the MySQL services.
service mysql restart
7. Tail the log to check which database make your server pain.
tail -f /var/log/mysql-slow.log
8. Once identified the problem, modify the /etc/my.cnf again become line as below.
#log-slow-queries = /var/log/mysql-slow.log #long_query_time = 3
9. Restart the MySQL services again and done.