Developing a MySQL Quota Daemon

Daniel Solin

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:

Continuous updates -- We wanted the quota checks to be performed frequently to provide current information about each database.
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 time.
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).

Finding a Solution

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 script:

<?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:

./quotad

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 in lst02.c.

I received the following email:

Date: Sun, 18 Nov 2001 19:41:24 +0100
From: mysql@mysql
Message-Id: <200111181841.TAA09554@mysql>
To: daniel@solin.org
Subject: Reports from MyQuotaD regarding mysql

Hi there!

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?

User/Database: mysql
Current Status: 1.29Mb
Allowed Size: 1.00Mb

All best,
MyQuotaD

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: daniel@solin.org.