2

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.

0

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

  • Instead of implementing this as a cron job, you could use a MariaDB event. And another thing that may or may not be useful in relation to this is the MariaDB 10.3 DISKS plugin. – dbdemon Apr 26 '18 at 15:42
  • Can you post an howto answer using events and disk plugin? – alexandre1985 Apr 27 '18 at 13:39
  • @dbdemon the DISKS plugin seems not to offer quota feature. It's only for monitoring inside DB the Linux devices. Because most DBA don't have access to system level monitoring. – Ivanov May 1 '18 at 6:39
  • @FydorGlebov Yes, I only mentioned it as a source of related, potentially useful info if additional features were to be added to the program. E.g. to sanity-check quotas so they don't exceed available disk space. – dbdemon May 1 '18 at 7:37
  • Re: the UPDATE db ... statement: It would be better to use GRANT and REVOKE statements. It's not advisable to write directly to this table, although I'm sure it works fine in a lot of cases. Galera Cluster is one use-case where it probably doesn't work, unless you turn on the experimental MyISAM support. – dbdemon May 1 '18 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.