HTTP basic авторизация из таблицы MySQL

Некая closed-source биллинговая система хранит логины и пароли для онлайн-плательщиков в таблице менеджеров, причем пароли зашифрованы с помощью MySQL-функции PASSWORD(). Если бы там был plaintext или банальный crypt, ничего бы не стоило периодически создавать из этой таблицы файл htpasswd - и не мучиться. А так придется водружать на апач mod_auth_mysql. Настройка его достаточно тривиальна. В httpd.conf (или в любой из conf-файлов, вкладываемых в него) добавляем такие строки:

LoadModule mysql_auth_module modules/mod_auth_mysql.so
<Directory /var/www/protected/dir/>

    AuthName "MySQL authenticated zone"
    AuthType Basic
    AuthMYSQLEnable on
    AuthMySQLHost my.db.ho.st
    AuthMySQLUser apache
    AuthMySQLPassword PaSsW0Rd
    AuthMySQLDB mybilldb
    AuthMySQLUserTable paysys
    AuthMySQLPwEncryption scrambled
    require valid-user

<Directory>

Наиболее интересна тут опция AuthMySQLPwEncryption - она задает метод шифрования паролей в базе. Значение scrambled как раз означает результат работы MySQL-функции PASSWORD() (причем поддерживаются как старый 16-символьный формат, так и новый 41-символьный). Еще возможные значения: none, crypt, md5, aes и sha1.
И последнее. mod_auth_mysql не поддерживает проверку логинов/паролей произвольным запросом к БД (как это разрешается, например, во freeradius) - нужно обязательно иметь таблицу с именем, указанным в AuthMySQLUserTable, содержащую поля user_name (AuthMySQLNameField по умолчанию) и user_password (AuthMySQLPasswordField по умолчанию). Можно указать еще условие WHERE в опции AuthMySQLUserCondition, но лично мне более гибким кажется путь создания в базе представления:

$ mysql -u root mybilldb -p


mysql> create view paysys as select login as user_name, pass as user_password from managers where payments > 0 and archive = 0 and template = 0;
mysql> grant select on mybilldb.paysys to 'apache'@'my.apache.ho.st' identified by 'PaSsW0Rd';

Полную справку по опциям mod_auth_mysql можно прочитать тут: http://modauthmysql.sourceforge.net/CONFIGURE

Вызов mysql_use_result в Python-MySQL

Точно так же, как и Perl DBI, Python-MySQL по умолчанию использует API функцию mysql_store_result, что на больших объемах выборки (тестировалось с выборкой возвращающей 17 млн. записей) приводит к использованию невероятного количества памяти - и результатов работы скрипта можно не ждать - ибо система будет увлеченно занята копированием страниц из свопа - и в своп. Метод борьбы с этой бедой - тот же, что и в DBI - нужно использовать функцию mysql_use_result (плата за экономию ресурсов проста и жестока - пока result не закрыт, никакой другой запрос на этом подключении выполнить нельзя).

На практике делается это так. При использовании модуля MySQLdb вместо обычного вызова метода cursor() без аргументов пишем:

cu = db.cursor(MySQLdb.cursors.SSCursor)

Скажем несколько слов о поведении данного типа курсоров. Как легко догадаться, привычный вызов fetchall() делает курсор на серверной стороне абсолютно безсмысленным - так что нужно использовать fetchone(). Вот тут-то нас и поджидает небольшая засада - очевидный цикл:

for i in range(0, cu.rowcount):

как раз и не работает, ибо для SSCursor rowcount не определен. Для любителей чисто питонского цикла

for rw in cu:

сразу скажу, что данная конструкция, как ни странно, работает правильно. Так же можно организовать цикл способом аналогичным тому, который предлагается ниже для модуля _mysql.

Так вот, переходим к этому модулю. Выигрыш в производительности он дает выдающийся - на моей выборке быстрее раз в 5 (даже быстрее, чем аналогичный скрипт на Perl), так что ради таких выгод вполне можно потерпеть несколько зубодробительный синтаксис (тот, кто знаком с MySQL C API ничего страшного в этом синтаксисе не увидит).

Работаем оно так:

db.query("select * from sometable")

rs = db.use_result()

Все это еще не беда. Беда начинается дальше. rs.num_rows() по причинам, изложенным выше, не работает, а потому с циклом придется слегка повозиться. Python - это вам не C и не Perl - оператор присваивания внутри условия цикла не поддерживается, и потому нужно организовывать достаточно уродливый цикл (последний раз что-то подобное приходилось делать в языке 1С Бухгалтерии):

rw = rs.fetch_row()

while rw:

    # do something

    rw = rs.fetch_row()

На этом сюрпризы не заканчиваются. rw в этом примере - вовсе не tuple, состоящий из полей запроса, а tuple, состоящий tuples, состоящих из полей (к счастью, по умолчанию, там этот tuple строго один - для возврата нескольких строк за раз fetch_row нужно вызывать с аргументом maxrows - значение 0 означает "все"). Так что присваивание переменным значений полей будет выглядеть так:

fld1, fld2, fld3 = rw[0]

Еще нужно помнить, что если MySQLdb приводит типы данных MySQL к аналогичным типам Python, то в _mysql все данные - тупые строки (если я правильно помню, аналогичные функции C API ведут себя аналогично).

Вставка нулевых значений в auto_increment поля в MySQL

По умолчанию, если в поле с опцией auto_increment вставляется значение 0 или null, MySQL заменяет его следующим значением счетчика. Если критично важно вставить именно 0, то нам поможет директива:

sql-mode='NO_AUTO_VALUE_ON_ZERO'

в /etc/my.cnf

или строка:

SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

в начале файла дампа.

Перенос дампа большой базы с MySQL на PostgreSQL

Перенести большую базу (размер дампа около 10 Гб) с MySQL на PostgreSQL просто удалением несовместимостей в дампе, созданном mysqldump, и накатыванием его на psql не получается - процесс заливки дампа занимает несколько суток. Далее приводится пошаговое руководство по сокращению этого времени до пары часов.

1. создаем дамп без данных (mysqldump -d --skip-opt --compatible=postgresql);
2. накатывам этот дамп на psql;
3. дампим полученнную базу - и делим полученный файл дампа на две части - в первой - только операторы CREATE TABLE, а ALTER TABLE ... ADD CONSTRAINT и CREATE INDEX - во второй. В файле дампа они расположены строго последовательно. Смысл этой операции в том, чтобы заливать данные в базу без индексов, а только потом создать индексы - так получится в разы быстрее;
4. создаем базу заново - и накатываем на нее схему без индексов;
5. для дампа данных придется писать скрипт. Смысл его такой: выбираем все поля table_name в таблице information_schema.tables, где table_schema равна имени нашей переносящейся базы - и для каждой из этих таблиц запускам select * into outfile;
6. заливаем эти файлы в psql с помощью оператора copy from;
7. накатываем вторую часть схемы - индексы - и наша база перенесена.

Тюнинг DBD::mysql

Ниже описаны два параметра соединения с сервером БД MySQL с помощью модуля DBD::mysql, которые могут использоваться для повышения производительности.

1. mysql_server_prepare - операторы сохраняются на стороне сервера.

Активируется с помощью добавления выражения mysql_server_prepare=1 к DSN, напр.:


$dbh = DBI->connect('DBI:mysql:db1:localhost;mysql_server_prepare=1', 'user', 'pass');


По идее такой подход должен повышать производительность массовых запросов. Проверяем на заведомо слабой машине (сервер и клиент на одном и том же компьютере). Миллион insert выполняется быстрее на 11%, миллион update - на 19%, миллион delete - на 11%. Стоило оно того? Не знаю. Но вроде противопоказаний не обнаружено.


2. mysql_use_result - результат запроса не сохраняется на клиенте перед использованием (умолчательное поведение называется mysql_store_result). Может быть применено:
   1. на уровне database handler: добавлением mysql_use_result=1 к DSN или оператором

      $dbh->{mysql_use_result}=1;

   2. или на уровне statement handler - в операторе:

      $dbh->prepare('select * from bigtable', {mysql_use_result => 1});

      или (после prepare и до execute) оператором:

      $sth->{mysql_use_result} = 1;

   Пользу от такого поведения можно почуствовать, если оператор возвращает очень много записей - порядка нескольких миллионов. При умолчательном mysql_store_result perl (по крайней мере, v 5.8.8 на FreeBSD 7.0) пожирает всю доступную память - и умирает, а при mysql_use_result - ничего, живет - и даже обходится не очень большим количеством памяти. К недостаткам этого метода работы с результатом man-страница DBD::mysql (3) относит

   tends to block other processes,

   но не очень понятно, что имеется в виду - по крайней мере, у автора по ходу длинного запроса другие процессы продолжали успешно читать из базы и писать в нее. Возможно, эффект бы наблюдался при запросе на запись - но в моей практике запросы на запись с таким размером возвращаемого результата не встречались.

Работа с привилегиями пользователей в MySQL

Памятка по операциям выдачи-отъема привилегий пользователей в MySQL (тестировалось на версии 5.1.24). Предполагается, что все последующие действия совершаются от имени учетной записи root сервера MySQL.
Пусть имеется база данных myDb0 с таблицами myTbl1 и myTbl2. Создадим пользователя newusr с правами на чтение из первой таблицы и чтение-вставку во вторую.

mysql> grant select on mydb0.mytbl1 to 'newusr'@'localhost' identified by 'qwerty123';
mysql> grant select, insert on mydb0.mytbl2 to 'newusr'@'localhost';

Заметим в скобках: (

1. предложение identified by 'qwerty123' нужно только при первом grant для данного пользователя;
2. для выполнения второго grant (т.е. выдача привилегий уже существующему пользователю) права root необязательны - достаточно иметь привилегию grant на те объекты, к которым мы выделяем права).

Просмотреть существующие привилегии можно командой:

mysql> show grants for 'newusr'@'localhost';

Удалить права можно командой вида:

mysql> revoke insert on mydb0.mytbl2 from 'newusr'@'localhost';

Но если прав выдавалось много, то проще воспользоваться таблицами в системной базе данных mysql. Та есть, например, таблица - tables_priv (сруктура такая: Host char(60), Db char(64), User char(16), Table_name char(64), Grantor char(77), Timestamp timestamp, Table_priv set('Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter', 'Create View', 'Show view', 'Trigger'), Column_priv set('Select', 'Insert', 'Update', 'References'), PRIMARY KEY (Host, Db, User, Table_name)), а, значит можно применить, такую, например, массовую операцию:

mysql> delete from mysql.tables_priv where user='newusr' and db='mydb0';

Если пользователю выдавалась привилегия на все таблицы базы (т.е. mydb0.*), то удалять ее надо из таблицы db(структура такая: Host char(60), Db char(64), User char(16), Select_priv enum('N','Y'), Insert_priv enum('N','Y'), Update_priv enum('N','Y'), Delete_priv enum('N','Y'), Create_priv enum('N','Y'), Drop_priv enum('N','Y'), Grant_priv enum('N','Y'), References_priv enum('N','Y'), Index_priv enum('N','Y'), Alter_priv enum('N','Y'), Create_tmp_table_priv` enum('N','Y'), Lock_tables_priv` enum('N','Y'), Create_view_priv enum('N','Y'), Show_view_priv enum('N','Y'), Create_routine_priv enum('N','Y'), Alter_routine_priv enum('N','Y'), Execute_priv enum('N','Y'), Event_priv enum('N','Y'), Trigger_priv enum('N','Y'), PRIMARY KEY (Host, Db, User)):

mysql> delete from mysql.db where user='newusr' and db='mydb0';

Все операции над этими таблицами не приводит к непосредсвенному изменению привилегий (т.е. вывод show privs не изменится), чтобы записать изменения не забудем выполнить:

mysql> flush privileges;

И еще два замечания в скобках: (

1. при удалении таблиц и баз данных строчки в таблицах db и tables_priv никуда не исчезают - т.е. при создании нового объекта со старым именем к нему применятся прежде существовавшие права. Хорошо это или плохо, сразу не скажешь, но забывать об этом не рекомендуется;
2. привилегия grant usage on *.*, отображаемая командой show grants, является привилегией по умолчанию при создании пользователя и может быть удалена только удалением самого пользователя командой drop user 'newusr'@'localhost' или delete from mysql.user where user='sel').
   

INNODB, INSERT ... SELECT и большие таблицы

В MySQL 5 (у меня версия 5.1.24-rc-log) при использовании движка INNODB существует проблема: если делаем INSERT INTO mytable SELECT ... FROM outhertable WHERE ... , и таблица, из которой SELECT - достаточно большая (миллионы записей), то после нескольких десятков секунд страдания появляется сообщение об ошибке: The total number of locks exceeds the lock table size
Есть два способа, как с этим бороться:

1. по большому счету это не способ, а костыль, но если на машине сильная нехватка ОЗУ, то можно воспользоваться. Разделяем INSERT ... SELECT на два оператора - сперва делаем SELECT ... INTO OUTFILE 'tmpfilename', а потом LOAD DATA INFILE 'tmpfilename' INTO TABLE mytable Потом этот файл неплохо удалить - только следует учесть, что владелец у него - mysql, зато права доступа - еще интереснее - 0666 ;)
   
2. вариант более нормальный - установить в /etc/my.cnf параметр innodb_buffer_pool_size=???M Документация утверждает, что значение может быть до 80% ОЗУ - то есть - кому сколько не жалко. У меня проблема не наблюдалась уже при 64 Мб. Значение по умолчанию (на платформе FreeBSD 7.0) - всего лишь 8 Мб. Просмотреть текущее значение этой переменной можно командой show variables like 'innodb_buf%';
   

Обсуждение этой ошибки на официальном сайте MySQL можно посмотреть тут (много английских букв)

Транзакции в DBD::mysql

Движок INNODB в MySQL поддерживает транзакции, но по умолчанию DBI работает в режиме AutoCommit. Ниже описывается, как удалось заставить DBD::mysql работать с транзакциями.

Обнаружено два работоспособных пути:

• в DBI->connect включать атрибут {AutoCommit => 0} (в man DBI написано, что его значение будет сброшено на умолчательную единицу первым же COMMIT, но на практике такое поведение в данной конфигурации не наблюдалось). Транзакция начинается автоматически после DBI->connect - явная посылка BEGIN не требуется - так же успешно сами по себе начинаются все последующие транзакции - очевидно, первой командой транзакции считается следующая после $dbh->commit или $dbh->rollback; Вызов команды $dbh->begin_work при выключенным AutoCommit приводит к ошибке (если мы установили атрибут RaiseError => 1, рекомендуемый документацией, то эта ошибка станет фатальной).
• если необходимо явно указывать начало транзакции, то в DBI->connect оставляем AutoCommit по умолчанию равным 1, транзакцию же будем начинать коммандой $dbh->begin_work. После $dbh->commit значение $dbh->{AutoCommit} сбрасывается в 1 - в полном соответствии с документацией - т.е. мы продолжаем работать в режиме без транзакций.

Наличие таких двух вариантов поведения, вероятно, не является особенностью собственно DBI - в главе START TRANSACTION, COMMIT, and ROLLBACK Syntax документации по MySQL описаны такие же возможности - либо можно установить параметр сессии SET AUTOCOMMIT=0, и тогда транзакции будут неявно начинаться и записываться/сбрасыться по COMMIT/ROLBACK - либо можно при умолчательном AUTOCOMMIT=1 явно начинать транзакцию коммандой START TRANSACTION или BEGIN.

Немного о логах в MySQL

Несколько практически важных команд из файла /etc/my.cnf, относящиеся к ведению журнала (расположены в секции [mysqld]):
log=filename
пишет в файл все запросы, поступающие на сервер. Может быть крайне полезна при отладке. На рабочем сервере этот прекрасный способ создания гиганских файлов лучше отключить.
Эти две команды включают ведение бинарного журнала (используется при реплицицировании):
bin-log=filename
server-id=1
(в последней команде важен именно параметр 1 - он задает master сервер, значения от 2 и выше - slave).

Если ничего реплицировать не планируется. бинарный лог лучше выключить - больно уж он большой. Если реплика все-таки необходима, неплохо ограничить размер бинарного лога - и устроить ротацию - например, вот так:

max_binlog_size=8192000
expire_logs_days=1
несмотря на свое название, последняя команда действут только на бинарный лог. Если нужно ротировать остальные логи, на то есть команда:
$ mysqladmin flush-logs

она закрывает/открывает текстовые логи - т.е. действует так, как на нормальные программы kill -HUP (хотя в документации на MySQL мне этого читать не приходилось, но на моей системе - Ver 5.1.24-rc-log for portbld-freebsd7.0 on i386 - привычная kill -HUP прекрасно справляется с этой операцией) - а бинарный лог начинает писать в новый файл.

Еще есть журнал медленных запросов. Кроме самого SQL выражения и времени выполнения, в него пишется, сколько строк возвращено и сколько было просмотрено. Возможно, это пригодиться при оптимизации самых тяжелых запросов. Первый параметр - куда писать, второй - после скольки секунд выполнения запрос считается медленным:

log-slow-queries=filename
long_query_time=5