Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I maintain a MySQL server. There is one user whose database size has very quickly grown enormously. Since he is using InnoDB, the /data/mysql/ibdata1 file has grown out of porpotions. I could mount a small partition and put his database there but ibdata1 file seems to be shared over all databases.

What I am concerned is how to limit a particular user from using more database space than is defined for him.

share|improve this question
    
If you wish to continue decent getting answers you should start accepting some of those already provided for your other questions. –  John Gardeniers Mar 29 '11 at 9:19

1 Answer 1

up vote 1 down vote accepted

There is no out of the box solution. But you can do the following:

  1. Get the data_length and index_length for the tables the user owns with show table status (This is the amount of space the data files use in the file system)
  2. Sum up these numbers for all databases
  3. If the limit is reached, remove for example INSERT privileges for that user, so he can not insert new data anymore.

But keep in mind that removing data from an InnoDB tables will not free up space in the file system. So if your user reached the limit, he can not do anything against it. (Except dumping all InnoDB tables, dropping them and reimport them)

share|improve this answer
    
Thanks! We wrote a PHP script to do that and put it on crontab. –  Henno Mar 30 '11 at 15:29
    
@Henno Maybe you should warn your users about that before removing the INSERT privileges. –  fab Mar 30 '11 at 15:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.