30.01.2012

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

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

Последние новости

Хостинг Хутор теперь в Telegram: чат с поддержкой, бот, канал
04.05.2020
Хостинг Хутор теперь в Telegram: чат с поддержкой, бот, канал
Уважаемые пользователи! Теперь мы в Telegram! Чат с поддержкой, бот для отправки уведомлений личного кабинета и канал.
График работы и обработки платежей на майские праздники
29.04.2020
График работы и обработки платежей на майские праздники
Уважаемые пользователи! Ознакомьтесь с графиком обработки банковских переводов на время майских праздников.
Google Chrome скоро начнет блокировать небезопасные HTTP-загрузки
03.03.2020
Google Chrome скоро начнет блокировать небезопасные HTTP-загрузки
Сначала Chrome будет предупреждать, но в следующих релизах уже блокировать небезопасные загрузки по HTTP.
С 13 января действует только новый стандарт банковских счетов IBAN!
13.01.2020
С 13 января действует только новый стандарт банковских счетов IBAN!
C сегодняшнего дня код банка (МФО) и расчетный счет в Украине заменит номер банковского счета по стандарту IBAN.

Последнее в блоге

Коронавирус Coronavirus (COVID-19) и кибермошенники в сети Интернет
02.03.2020
Коронавирус Coronavirus (COVID-19) и кибермошенники в сети Интернет
Паника вокруг коронавируса COVID-19 используется кибермошенниками в сети Интернет - фишинг, продажа масок, вакцин и тестов.
Не отправляется почта - проверяем не блокирует ли провайдер 25 порт
11.01.2020
Не отправляется почта - проверяем не блокирует ли провайдер 25 порт
Проверяем блокирует ли провайдер 25 порт с помощью командной строки в ОС Windows. Как отправлять почту если 25 порт заблокирован.
Как установить Clam AntiVirus (ClamAV) на VPS или сервер с ОС CentOS
11.11.2019
Как установить Clam AntiVirus (ClamAV) на VPS или сервер с ОС CentOS
Устанавливаем Clam AntiVirus (ClamAV) на VPS / VDS или выделенный сервер с ОС CentOS и настраиваем ежедневное сканирование сервера.
ISPmanager больше не поддерживает резервное копирование в Яндекс.Диск
20.10.2019
ISPmanager больше не поддерживает резервное копирование в Яндекс.Диск
В течении недели Яндекс.Диск исчезнет из списка хранилища для резервного копирования в панеле ISPmanager и других продуктах ISPsystem.

Популярные домены