1

Similar questions have been asked before, such as this one, but none of the answers I've seen so far really tell me what I'm after.

I have a need to set up a Linux box for internal development and testing purposes which will require multiple user accounts. Each account must be restricted in the amount of space taken up by their MySQL database(s), as the testing will attempt to break things and I wish to confine any damage done.

I'm assuming I will need to set up one instance of MySQL per account, with the database files within that account's file space, and limit this with quotas. Is that correct or is there another way that I've overlooked?

Community
  • 1
John Gardeniers
  • 27,458
  • 12
  • 55
  • 109
  • Is it just space that you're concerned about or is it important that the users are all separated? – vsltd Apr 12 '11 at 09:44
  • @vsltd, both. We need separation but we can't afford to have a runaway process harm other accounts or the system by using up all available space. The testing will be very brutal. – John Gardeniers Apr 12 '11 at 10:30

2 Answers2

0

You are correct - there is no in-built quota support within MySQL.

Be aware that when using disk quotas to achieve your goal, when the user runs out of space, MySQL can buffer statements that are unable to complete. This can cause server stability issues. Data integrity issues have also been reported by people hitting a disk limit (although this probably doesn't affect all table engines).

Steve Mayne
  • 1,001
  • 6
  • 5
  • I don't really care about the data integrity for a user if that account runs out of space. That's just one of the things I need to try and prevent in code. I also don't care if that instance of MySQL gets killed, as long as other accounts are more or less unaffected (within practical limits of course). – John Gardeniers Apr 12 '11 at 10:34
  • Disk quotas are the way to go then. – Steve Mayne Apr 12 '11 at 11:55
0

Another option is to run a cronjob or daemon that monitors database sizes and then revokes insert and update privileges when they reach a specified size. One such tool is MySQL Quota Daemon

http://lrem.net/software/mysql-quota-daemon.xhtml

sreimer
  • 2,218
  • 15
  • 17
  • I'll look into that possibility, although during testing I expect some instances to have inserts thrown at them as fast as they can be processed, so cron may be too slow. Nevertheless, it might be worthwhile for the final production version. – John Gardeniers Apr 12 '11 at 21:34
  • With a cron running every minute or even 15, you could shut them down pretty quickly without it getting too far over your cap. It depends on you close you want/need to keep them to the limits. – sreimer Apr 12 '11 at 21:46