How to Setup Disk Quotas For MySQL Database

MySQL server has no support for preventing disk from getting filled up. You need to depend upon external configurations. Let us see how to use standard Linux disk quota to limit user database size.

Suppose you have a user ryan on the system as well as on MySQL. Now how do you limit space for ryan?
1) Create a separate directory for databases in ryan's home say /home/ryan/dbs with ownership mysql:ryan and permissions 3755:

# mkdir /home/ryan/dbs && chown mysql:ryan /home/ryan/dbs && chmod 3755 /home/ryan/dbs

2) Now move all ryan's databases from /var/lib/mysql to /home/ryan/dbs

# mkdir /root/backup
# cp -avr /var/lib/mysql/* root/backup
# mv /var/lib/mysql/database-name /home/ryan/dbs/
# chown -Rf mysql:ryan /home/ryan
# chmod -Rf 3755 /home/ryan
# ln -s /var/lib/mysql/database-name /home/ryan/dbs/database-name -v

Finally, restart mysqld. If mysqld starts with OK status, then its working else it isn't:

# /etc/init.d/mysqld restart

Always take the back up of your database before using these steps.