MySQL квоти - реалізація квот на бази даних MySQL

Здебільшого використовується два формати таблиць даних MySQL - це InnoDB і MyISAM. Таблиці MyISAM зберігаються в індивідуальних файлах, тому їх обмежити просто, але ось всі таблиці InnoDB всіх баз, за замовчуванням зберігаються в загальних файлах для всіх таблиць даного типу.

InnoDB і MyISAM

Щоб розділити таблиці InnoDB на індивідуальні для кожної таблиці файли, включаємо в конфіги mysql сервера my.cnf опцію innodb_file_per_table в секції [mysqld]:

[mysqld]
...
innodb_file_per_table

В такому випадку, таблиці InnoDB і MyISAM будуть розташовуватися в індивідуальних директоріях для кожної бази - MyISAM в .MYD і .MYI, InnoDB - в .ibd. Щоб розрізняти, яка база якому користувачеві належить, бази створюємо по імені групи користувача виду:

bduser_basename
bduser - локальна група користувача, який створює базу
basename - приставка, яку може змінювати користувач, щоб відрізнити різні бази одного і того ж локального користувача.

Бази у нас будуть зберігатися в /var/lib/mysql, таким чином по крону будемо міняти групу файлів баз, які належать одному користувачеві - для цього пишемо простий скрипт:

for bd in `find /var/lib/mysql -type d`
do
 chown -R mysql:`echo $bd |cut -d'/' -f5 |cut -d'_' -f1` $bd
done

Але тут виникає одна серйозна проблема - при перевищенні ліміту, сервер mysql не може писати в базу і створювати таблиці, тому в пам'яті росте безліч процесів INSERT, CREATE і UPDATE, які не можуть виконуватися. Щоб такого не було, необхідно користувачам, які перевищили квоту, заборонити виконувати ці операції, а якщо вони базу зменшать, відповідно назад дозволити. Таким чином, пишемо скрипт, який буде перевіряти квоти і забороняти / дозволяти операції INSERT, CREATE, UPDATE залежно від квоти:

export MYSQL_PWD=passWord # Тут у нас пароль адміністратора mysql
update='0'
for user in `cat /var/log/mysqlquota` # /var/lod/mysqlquota - список заблокованих
do
    baduser=`quota -q -g $user 2>/dev/null |head -n1 |cut -d' ' -f5`
    if [ "$baduser" != "$user" ]
    then
        for base in `echo "SELECT db FROM db WHERE db LIKE \"$user\_%\"" |mysql -uroot mysql -bs`
        do
            echo "UPDATE db SET Insert_priv='Y', Create_priv='Y', Update_priv='Y' WHERE Db='$base';" |mysql mysql -bs
            update='1'
        done
    fi.
done
echo -n >/var/log/mysqlquota
for home in `find /var/lib/mysql/ -type d |cut -d'/' -f5 |cut -d'_' -f1`
do
        if id -g $home >/dev/null 2>/dev/null # перевіряємо існування групи користувача в системі
        then
            for user in `quota -q -g $home 2>/dev/null |head -n1 |cut -d' ' -f5`
            do
              echo $user >>/var/log/mysqlquota
              for base in `echo "SELECT db FROM db WHERE db LIKE \"$user\_%\"" |mysql -uroot mysql -bs`
              do
                echo "UPDATE db SET Insert_priv='N', Create_priv='N', Update_priv='N' WHERE Db='$base';" |mysql -uroot mysql -bs
                update='1'
              done
            done
        fi
done
if [ "$update" = '1' ]
then
    echo "FLUSH PRIVILEGES;" |mysql -uroot mysql -bs
fi

Таким чином, можна поєднати встановлення прав і перевірку квот в один скрипт і запускати його за допомогою crontab, наприклад, раз в 5 хвилин.