Solved

Limit number of MySql connections from a single IP Address

Posted on 2009-03-31
3
377 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
  • 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:
theGhost_k8 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
simple sql statement 3 57
What does "flush tables;" do exactly? 7 72
I need to create a new user to access our RDS/MySQL database 1 39
html input clean up 3 47
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
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 …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now