1

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.

Improve this question
1
  • 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, 2011 at 9:19

1 Answer 1

Reset to default
1

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)

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .