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 минут.