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.

  • 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

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)

  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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