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.
Identifying the Problem
In the UNIX world, MySQL and PHP have become very popular for
Web database development. The ISP that I work for has offered both
MySQL database access as well as PHP support for its customers for
quite a while. However, we've lacked an automated system that
tracks our customers' databases so that they don't grow
to an unreasonable size. The customers are offered a set of pre-defined
MySQL packages, each package defining exactly how much space the
customer is allowed to use on the MySQL server. However, the only
way to be sure customers don't exceed their quota is to manually
log into the server, check each database size, and compare the current
size to the size that the customer has paid for. This obviously
isn't the best solution.
We needed software that could track and automate this process
for us. Our software criteria were:
Finding a Solution
- Continuous updates -- We wanted the quota checks to be
performed frequently to provide current information about each
- Low footprint -- Because the quota software would be executed
on the same server on which MySQL runs (which is already heavy),
it was important for the quota software to require minimal CPU
- Good communication -- The quota software should be able
to contact the administrator via email when a problem occurs,
and also when any problem is resolved.
- Logging functionality -- A good logging functionality was
required for reference.
- Dumping database-specific information -- We let our customers
use phpMyAdmin to administer their databases, and we wanted to
also provide a status page to phpMyAdmin (http://phpwizard.net/projects/phpMyAdmin).
After considering these criteria, we worked toward a solution.
We chose C as the programming language for speed and lowest footprint.
We couldn't consider a cron job because we needed updates on
an almost per-second basis. We instead created a UNIX daemon that
resides in memory so that the host isn't forced to execute
the program each time an update is needed. The communication, logging,
and dumping functionalities would not be difficult, even in C, because
they only require some basic file operations and an execution of
Sendmail with the correct arguments. We considered further developing
the mailing functions of the new program but weren't sure we
could do that without devoting an unreasonable amount of work to
it. Additionally, we wanted to receive the mail that was to be sent,
and Sendmail provided a stable option. Everything that the daemon
would do was to be logged into a logfile. For the interaction with
phpMyAdmin, the daemon should write a simple HTML page for each
database, informing the customer of the current quota status. After
making these decisions, we were ready to begin coding.
Writing a UNIX Daemon
A few things must be done either manually or by the daemon()
function (do a man daemon for details) in order to make a
program a real UNIX daemon. I prefer to do these manually. By doing
it manually, you can learn more about UNIX processes. Listing 1
(http://www.sysadminmag.com/code/) shows the main()
function used in our MySQL quota daemon. See the sidebar for an
explanation of Listing 1.
Implementing the Quota Checking
Each of our customer's MySQL databases is represented by
a sub-directory of $MYSQL_BASE_DIR/data (or $MYSQL_BASE_DIR/var
or something else, depending on your setup). The name of this sub-directory
is always the same as the database name. The files in $MYSQL_BASE_DIR/data/$DB_NAME
represent the tables you have in your database, so you get the actual
database size by calculating the size of the $MYSQL_BASE_DIR/data/$DB_NAME
directory. If you're on a system running a MySQL database server,
and you know there is a database called "mydb" on the
server, you can determine the size of this database by running du
$MYSQL_BASE_DIR/data/mydb (changing $MYSQL_BASE_DIR to
your real MySQL base installation directory). In my company's
case, the database name is the same as the user who owns the database,
so mydb would be owned by the user "mydb" (which
is useful when implementing the database status Web page a little
later). Listing 2 (http://www.sysadminmag.com/code/) shows
the complete do_quota_file() function. See the sidebar for
an explanation of Listing 2.
Dumping Database-Specific Information
As mentioned, we also wanted the quota daemon to dump information
about each database to a file that could easily be implemented in
phpMyAdmin. This information should be presented on a Web page anyway,
so we put this information in HTML format. This functionality is
handled by the function int gen_www_info( char *db_name, double
size, double quota ), which is called in Listing 2 on line 212.
It takes three arguments -- the name of the database about which
to dump information, the current size, and quota for the database.
Given this information, gen_ww_info() generates the database
information, saves it to /var/lib/apache/htdocs/quota/$DB_NAME,
and returns 0 if everything goes well. Listing 3 shows our implementation
of this function (http://www.sysadminmag.com/code/).
The function is straightforward and simply takes the database
name passed to it and saves some information in HTML format to the
file /var/lib/apache/htdocs/quota/$DB_NAME. (Of course, you
need to change this path to suit your system.)
The customer can log on to phpMyAdmin using a MySQL username and
password. Because the username is the same as the database name,
the name of the database is present in phpMyAdmin, stored in the
$PHP_AUTH_NAME variable. The database status information
is stored in a file with the same name as its corresponding database
in /var/lib/apache/htdocs/quota, so the correct information
file is used by implementing the following line in a suitable PHP
<?php include("quota/$PHP_AUTH_USER"); ?>
This will cause all information stored in quota/$PHP_AUTH_USER
to be included in the HTML document.
Time for Testing
When you're ready to test this setup, you first must compile
the daemon. Before the test will work, however, remove the code
on line 114 of Listing 2 that tells the program to ignore the MySQL
database ((!strcmp( dir_element-d_name, "mysql"))). Then
tell the C compiler to build the daemon. On my Linux system, I use
the GNU C compiler:
gcc -Wall lst01.c -o quotad
Before starting the daemon, confirm that it has enough permissions
to perform all its tasks. You can run it as root the first time to
see whether it works as expected and then run it as a normal user.
If any problem occurs now, it has something to do with the permissions.
Start the daemon:
The daemon should now go directly into the background, directing you
to the command line. To see whether the deamon is running, you can
run a tail -f /usr/local/mysql/logs/quota_log. If the daemon
is running, this file should get updates every two seconds. To test
whether the quota checking works, create a quota file for the MySQL
database (/usr/local/mysql/quota), and add a single 1 to it.
This will cause the quota daemon to react each time the /usr/local/mysql/data/mysql
directory gets larger than 1 Mb. If you don't have many MySQL
users and privileges defined, your MySQL database is probably not
larger than 1 Mb. To fake an exceeded account, copy a file larger
than 1 Mb to /usr/local/mysql/data/mysql. If everything works
as expected, you should quickly receive an email to the address defined
I received the following email:
Date: Sun, 18 Nov 2001 19:41:24 +0100
Subject: Reports from MyQuotaD regarding mysql
I am the MySQL-quota-daemon, and I just realized that the the user mysql has
exceeded his/her MySQL-quota. The database is 129.80Mb, while the maximum
allowed size for this account is 100.00Mb. Maybe we should contact the customer?
Current Status: 1.29Mb
Allowed Size: 1.00Mb
The email report arrived just as we wanted it to and, with our automated
system for adding new databases to this quota-checking facility, we'll
soon be able to just wait for the emails to arrive.
Daniel Solin is a UNIX and Linux freak from cold Sweden. He
is working with UNIX on one of Europe's largest ISPs in the
daytime, and writing about UNIX at night. He can be contacted at: