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.

This (of course) doesn't work for users who have global priveleges, because the quota is database and not user based, but in most environments privileges are given in the "db"-table which is modified by the MySQL Quota Tool.

Here is the source code:

#!/usr/bin/php -q <?PHP /* * MySQL quota script * written by Sebastian Marsching * */ /* This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ /* * Create table for quota data with the following statement: * * CREATE TABLE `Quota` (`Db` CHAR(64) NOT NULL, * `Limit` BIGINT NOT NULL, * `Exceeded` ENUM('Y','N') DEFAULT 'N' NOT NULL, * PRIMARY KEY (`Db`), UNIQUE (`Db`)); * * The field 'db' stores the information for which database * you want to limit the size. * The field 'limit' is the size limit in bytes. * The field 'exceeded' is only used internally and must be * initialized with 'N'. */ /* * Settings */ $mysql_host = 'localhost'; $mysql_user = 'root'; // Do NOT change, root-access is required $mysql_pass = ''; $mysql_db = 'quotadb'; // Not the DB to check, but the db with the quota table $mysql_table = 'quota'; /* * Do NOT change anything below */ $debug = 0; // Connect to MySQL Server if (!mysql_connect($mysql_host, $mysql_user, $mysql_pass)) { echo "Connection to MySQL-server failed!"; exit; } // Select database if (!mysql_select_db($mysql_db)) { echo "Selection of database $mysql_db failed!"; exit; } // Check quota for each entry in quota table $sql = "SELECT * FROM $mysql_table;"; $result = mysql_query($sql); while ($row = mysql_fetch_array($result)) { $quota_db = $row['db']; $quota_limit = $row['limit']; $quota_exceeded = ($row['exceeded']=='Y') ? 1 : 0; if ($debug) echo "Checking quota for '$quota_db'... "; $qsql = "SHOW TABLE STATUS FROM $quota_db;"; $qresult = mysql_query($qsql); if ($debug) echo "SQL-query is "$qsql" "; $quota_size = 0; while ($qrow = mysql_fetch_array($qresult)) { if ($debug) { echo "Result of query: "; var_dump($qrow); } $quota_size += $qrow['Data_length'] + $qrow['Index_length']; } if ($debug) echo "Size is $quota_size bytes, limit is $quota_limit bytes "; if ($debug && $quota_exceeded) echo "Quota is marked as exceeded. "; if ($debug && !$quota_exceeded) echo "Quota is not marked as exceeded. "; if (($quota_size > $quota_limit) && !$quota_exceeded) { if ($debug) echo "Locking database... "; // Save in quota table $usql = "UPDATE $mysql_table SET exceeded='Y' WHERE db='$quota_db';"; mysql_query($usql); if ($debug) echo "Querying: $usql "; // Dismiss CREATE and INSERT privilege for database mysql_select_db('mysql'); $usql = "UPDATE db SET Insert_priv='N', Create_priv='N' WHERE Db='$quota_db';"; mysql_query($usql); if ($debug) echo "Querying: $usql "; mysql_select_db($mysql_db); } if (($quota_size <= $quota_limit) && $quota_exceeded) { if ($debug) echo "Unlocking database... "; // Save in quota table $usql = "UPDATE $mysql_table SET exceeded='N' WHERE db='$quota_db';"; mysql_query($usql); if ($debug) echo "Querying: $usql "; // Grant CREATE and INSERT privilege for database mysql_select_db('mysql'); $usql = "UPDATE db SET Insert_priv='Y', Create_priv='Y' WHERE Db='$quota_db';"; mysql_query($usql); if ($debug) echo "Querying: $usql "; mysql_select_db($mysql_db); } } ?>

You can also download the source code.
The General Public License can be found here.

Visit other projects on projects.marsching.org!

Imprint | Privacy Policy