MySQL limiting user disk space by tablespace

Question

I am trying to figure out how to set a limit of disk space for users in MySQL. What I want is, for example, administrator assign 1G disk space for user A, if A used up the 1G disk space, MySQL would not allow A insert anything. I googled this problem, and someone mentioned that it can be solved by using tablespace, but did not mention how. Any help is appreciated.

Answer 1

As far as I know there is no native MySQL disk quota support (and limiting disk usage for a database with hard quota sounds like a recipe for disaster).

You can report on disk usage though with the MySQL disk usage utility https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldiskusage.html

Answer 2

You can put mysql/datadir on each user home example
/home/user1 or DATADIR="/var/www/${VHOST}/mysql/datadir" then put a limit on the disk space for each user

Also like that each user will have his sock and pid file I use to do it like this:

mysqld --skip-ndbcluster --user=mysql --skip-grant-tables --basedir=/usr --datadir=${DATADIR} --skip-innodb --skip-bdb --skip-networking --max_allowed_packet=8M --net_buffer_length=16K --socket=${sockfile} --pid-file=${pidfile} &>/dev/null