Best way to limit MySQL connection for one account

[Justec]

I have one account on my server that has a search filter setup. So basically MySQL search queries not from user input, but just clicking a filter link. Sometimes when bots are scanning the page they start going through those links and then causing 10-20+ slow search processes to start running and bog down the server.

Not sure if there is a way to limit this for just the one account? Or can I create a 5 min cron to just kill connections for this one account to mysql? Or do I need to just see about hacking the wordpress plugin to make the SQL better and more efficient?

[d2d4j]

Hi justec

Happy new year

I am sorry I am not by a computer but if you login to nodeworx, system services, MySQL, phpmyadmin- select the required database and I think it’s the second option/tab - resources or similar, you may have to schroll your browser but you should see at least 4 methods of restrictions for that 1 database

I will post some screenshots tommorow but I have posted this previously so perhaps a search

It does work as we have a few clients who are similar

I hope that helps a little

Many thanks

John

[Justec]

I see Routines, Events, Triggers tabs, but not exactly sure what I can do in here. I will wait for you screenshots. Thanks!!

[d2d4j]

Hi Justin

Many thanks and sorry, time differences between UK/USA

I was wrong sorry, you do not select any database but user accounts

Login to nodeworx, system services, mysql, phpmyadmin, and click user accounts from right hand screen, (make sure you know the user account(s) on database you want to restrict first), click the alphabet letter for the user account, locate the exact user and click edit next to the user name, scroll down until you see limits section and input as you need/require, then save. (see picture)

This then applies those restricted limits to that user and it does work, but may need tweaking after reviewing how the website runs on those limits

I was thinking though, as we very recently had 1 website which was causing a lot of issues, and pushing apache to limits, and mysql was slowing down due to queries building up etc… (not a word press site) and took me a little time to work out what was going wrong. (see picture) so if you see this type of graph and mysql have a lot of unanswered queries, then try this

login to siteworx account for the website identified as cause (see apache status for site which is making most calls usually), check to see if htaccess has been enabled (if it has not, it is not the same issue) and if so, edit global and goto php variables, and change inherited to on or off for modules you want to set, save and restart apache and mysql

check the site to make sure is working as expected

On the one we resolved, the site is a few years old and used magic quotes, which was set to inherited but the site was not displaying results as expected, so was building up, setting magic quotes to on in php variables resolved issue and all was well

Hope that helps a little

Many thanks

John

[Justec]

Thanks! I’m starting off by trying MAX USER_CONNECTIONS (Limits the number of simultaneous connections the user may have).

I think that should fit my needs, I don’t want to set limits by the hour, I just want to stop the site from bogging down the server when they are getting crawled by a bot queueing up 20,30, 40+ slow search queries that could take 30 seconds or more each.