≡ Menu


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.

LVM is an implementation of a logical volume manager for the Linux kernel. The biggest advantage is that LVM provides the ability to make a snapshot of any logical volume.

In a production environment many users access the same file (file is open) or database. Suppose you start backup process when a file is open, you will not get correct or updated copy of file i.e. inconsistent backup (see accurate definition of inconsistent backup).

Read-only partition - to avoid inconsistent backup

You need to mount partition as read only, so that no one can make changes to file and make a backup:
# umount /home
# mount -o ro /home
# tar -cvf /dev/st0 /home
# umount /home
# mount -o rw /home

As you see, the draw back is service remains unavailable during backup time to all end users. If you are using database then shutdown database server and make a backup.

Logical Volume Manager snapshot to avoid inconsistent backup

This solution will only work if you have created the partition with LVM. A snapshot volume is a special type of volume that presents all the data that was in the volume at the time the snapshot was created. This means you can back up that volume without having to worry about data being changed while the backup is going on, and you don't have to take the database volume offline while the backup is taking place.

# lvcreate -L1000M -s -n dbbackup /dev/ops/databases


lvcreate -- WARNING: the snapshot must be disabled if it gets full
lvcreate -- INFO: using default snapshot chunk size of 64 KB for "/dev/ops/dbbackup"
lvcreate -- doing automatic backup of "ops"
lvcreate -- logical volume "/dev/ops/dbbackup" successfully created

Create a mount-point and mount the volume:
# mkdir /mnt/ops/dbbackup
# mount /dev/ops/dbbackup /mnt/ops/dbbackup


mount: block device /dev/ops/dbbackup is write-protected, mounting read-only

Do the backup
# tar -cf /dev/st0 /mnt/ops/dbbackup

Now remove it:
# umount /mnt/ops/dbbackup
# lvremove /dev/ops/databases

Please note that LVM snapshots cannot be used with non-LVM filesystems i.e. you need LVM partitions. You can also use third party commercial proprietary (see below for discussion) or GPL backup solutions/software.

MySQL Backup: Using LVM File System Snapshot

Login to your MySQL server:
# mysql -u root -p
At mysql prompt type the following command to closes all open tables and locks all tables for all databases with a read lock until you explicitly release the lock by executing UNLOCK TABLES. This is very convenient way to get backups if you have a file system such as Veritas or Linux LVM or FreeBD UFS that can take snapshots in time.
mysql> flush tables with read lock;
mysql> flush logs;
mysql> quit;

Now type the following command (assuming that your MySQL DB is on /dev/vg01/mysql):
# lvcreate --snapshot –-size=1000M --name=backup /dev/vg01/mysql
Again, login to mysql:
# mysql -u root -p
Type the following to release the lock:
mysql> unlock tables;
mysql> quit;

Now, move backup to tape or other server:
# mkdir -p /mnt/mysql
# mount -o ro /dev/vg01/backup /mnt/mysql
# cd /mnt/mysql
# tar czvf mysql.$(date +"%m-%d%-%Y).tar.gz mysql
# umount /mnt/tmp
# lvremove -f /dev/vg01/backup

If you are using a Veritas file system, you can make a backup like this (quoting from the official MySQL documentation):

Login to mysql and lock the tables:
mysql> quit;

Type the following at a shell prompt
# mount vxfs snapshot
mysql> quit;

Copy files from the snapshot and unmount the snapshot.

Further reading: