MariaDB – Implementing Disk Quota for MariaDB

disk-spacemariadb

I want my Linux MariaDB database to have disk quota implemented.
How can I achieve this these days?

I have found some tools but they are for MySQL, don't know if they are supposed to work on MariaDB.

Best Answer

I ended up running this script in cron:

#!/usr/bin/php -q
<?php

/*
 * MySQL quota script
 * written by Sebastian Marsching
 * updated by Daniel Santos
 *
 */

/*
 *  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

$con = mysqli_connect($mysql_host, $mysql_user, $mysql_pass, $mysql_db);

if (mysqli_connect_errno()) {
    echo "Connection to MySQL-server failed!";
    exit;
}


// Check quota for each entry in quota table

$sql = "SELECT * FROM $mysql_table;";
$result = mysqli_query($con, $sql);

while ($row = mysqli_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'...\n";
    }

    $qsql = "SHOW TABLE STATUS FROM $quota_db;";
    $qresult = mysqli_query($con, $qsql);

    if ($debug) {
        echo "SQL-query is \"$qsql\"\n";
    }

    $quota_size = 0;

    while ($qrow = mysqli_fetch_array($qresult)) {
        if ($debug) {
            echo "Result of query:\n";
            var_dump($qrow);
        }
        $quota_size += $qrow['Data_length'] + $qrow['Index_length'];
    }

    if ($debug) {
        echo "Size is $quota_size bytes, limit is $quota_limit bytes\n";
    }

    if ($debug && $quota_exceeded) {
        echo "Quota is marked as exceeded.\n";
    }
    if ($debug && !$quota_exceeded) {
        echo "Quota is not marked as exceeded.\n";
    }

    if (($quota_size > $quota_limit) && !$quota_exceeded) {
        if ($debug) {
            echo "Locking database...\n";
        }
        // Save in quota table
        $usql = "UPDATE $mysql_table SET exceeded='Y' WHERE db='$quota_db';";
        mysqli_query($con, $usql);
        if ($debug) {
            echo "Querying: $usql\n";
        }
        // Dismiss CREATE and INSERT privilege for database
        mysqli_select_db($con, 'mysql');
        $usql = "UPDATE db SET Insert_priv='N', Create_priv='N' WHERE db='$quota_db';";
        mysqli_query($con, $usql);
        if ($debug) {
            echo "Querying: $usql\n";
        }
        mysqli_select_db($con, $mysql_db);
    }

    if (($quota_size <= $quota_limit) && $quota_exceeded) {
        if ($debug) {
            echo "Unlocking database...\n";
        }
        // Save in quota table
        $usql = "UPDATE $mysql_table SET exceeded='N' WHERE db='$quota_db';";
        mysqli_query($con, $usql);
        if ($debug) {
            echo "Querying: $usql\n";
        }
        // Grant CREATE and INSERT privilege for database
        mysqli_select_db($con, 'mysql');
        $usql = "UPDATE db SET Insert_priv='Y', Create_priv='Y' WHERE db='$quota_db';";
        mysqli_query($con, $usql);
        if ($debug) {
            echo "Querying: $usql\n";
        }
        mysqli_select_db($con, $mysql_db);
    }
}

mysqli_close($con);
?>

You first need to put your mariadb root password in the script.
You then need to create a database named quotadb and add this table to it:

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`));

In this table you will add the values of the database you want quota for and the value for the size of the quota (in bytes).

Execute this script on cron and you are good to go

Related Question