Solved

Limit number of MySql connections from a single IP Address

Posted on 2009-03-31
3
389 Views
Last Modified: 2012-08-13
Is there a way to configure MySql Server to put a limit on how many connections can be made from a single ip address?

We have a situation where an application is failing and making continuous connections to the MySql.  Eventually takes up all available connections.  While the real answer is to fix the application, an alternative would be to simply set a cap on how many connections can come from any single ip address.
0
Comment
Question by:markschenkel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Expert Comment

by:oobayly
ID: 24032774
Does the application use a username not used by other clients? I'm guessing you've already thought about limiting connections on a user basis and discounted the idea.
Another option could be to schedule a script get all the connections, and kill any for the IP that are over a certain age.
Personly I reckon that this is beyond MySql's capability (and remit). ipfw or iptables would probably the route to take, especially given Linux's penchant for each tool having a specific task.
0
 
LVL 21

Accepted Solution

by:
K V earned 125 total points
ID: 24037854
It can be done by grant command as follows:
GRANT ALL ON customer.* TO 'USERNAME'@'IP ADDRESS'
      WITH MAX_QUERIES_PER_HOUR 20
      MAX_UPDATES_PER_HOUR 10
      MAX_CONNECTIONS_PER_HOUR 5
      MAX_USER_CONNECTIONS 2;
0
 
LVL 15

Expert Comment

by:oobayly
ID: 24037883
<Slaps forehead with palm>: Why didn't I think of that.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question