How To Setup Disk Quotas For MySQL Database

This is a user contributed article.

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 foo on the system as well as on MySQL. Now how do you limit space for foo?

Step #1: Create a separate directory for databases in foo’s home say /home/foo/dbs with ownership mysql:foo and permissions 3755:
# mkdir /home/foo/dbs && chown mysql:foo /home/foo/dbs && chmod 3755 /home/foo/dbs
Step #2: Now move all foo’s databases from /var/lib/mysql to /home/foo/dbs
# mkdir /root/backup
# cp -avr /var/lib/mysql/* root/backup
# mv /var/lib/mysql/database-name /home/foo/dbs/
# chown -Rf mysql:foo /home/foo
# chmod -Rf 3755 /home/foo
# ln -s /var/lib/mysql/database-name /home/foo/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

NOTE- I AM NOT RESPONSIBLE FOR ANY DAMAGES OCCURRED & TO TEST THIS TRY RESTARTING mysqld. Always backup database before trying something new.

Other tools

  • MySQL Quota-Tool : The MySQL Quota-Tool helps you to set a size limit on MySQL databases. It works by checking the size of each database and revoking the INSERT- and CREATE-priveleges for the databases, which exceed the given size limit. When the size of the database falls below the given limit, the INSERT- and CREATE-priveleges are granted again.
  • MySQL Quota Daemon : MySQL Quota Daemon is a lightweight tool for limiting database sizes.
  • Developing a MySQL Quota Daemon : One of the drawbacks of MySQL is the lack of a real quota system. This article will cover how our ISP was able to solve this problem and keep track of our customers’ databases. With this completely automated solution, many hours of administration work was avoided.
  • Feel free to add your tools and tricks in the comments.

About the author: This article is contributed by Nilesh.

🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 7 comments so far... add one
CategoryList of Unix and Linux commands
Disk space analyzersncdu pydf
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
7 comments… add one
  • Jonathan Jan 29, 2008 @ 3:14

    How will MySQL react when the quota kicks in? Will MySQL crash (I’ve googled it and a couple of people complained about MySQL crashing when the disk runs out of space)? What happens if MySQL is half-way through an insert and quotas kick in? Does it roll back the insert?

    • Baz Feb 2, 2015 @ 15:48

      You’ve hit the nail on the head. I have just spent 3 days messing about with a crashed MySQL database that was caused by quotas being exceed during upload. It would be far better to notify users and charge them once exceeded instead of preventing the quota being exceeded during upload.

  • Nilesh Jan 29, 2008 @ 12:53

    Johnathan, i have no solution for it. 😀

  • Raj Jan 29, 2008 @ 13:21


    A few years back I used to work for a large ISP and we used to jail both MySQL and apache (apache request proxy via 2-3 backends) using FreeBSD jail. Each user has 2GiB disk space for MySQL. MySQL will stop private instance when user reaches her disk quota. This way it was only affecting a single user.

    Today you can do the same thing using easily with XEN or other VPS software.


  • Mohammad Dec 19, 2008 @ 15:20

    I have one single database and server. now load is too enough to break the thread between them and let the db server down. i want to do clustering. How to do database clustering and web server clustering. Please suggest.

  • Özkan ÅžENOVA Jun 12, 2009 @ 14:38

    third link is gone :S

  • Bachsau Aug 29, 2012 @ 16:00

    This won’t work, as disk quota is calculated by inodes not directory. Additionaly, you don’t want to give others write and execute permissions to a users home, just to allow mysqld access it.

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum