Регистрация доменных имен

Ниже приведен пример скрипта который и занимается этой черной работой. Сразу же предупреждаю, что скрипт бакапит только те базы, до которых он имеет права доступа, и если вы внесли “чужие” базы, то они не будут сохранены. 

/*   начало скрипта  sql-backup.sh  */
#!/bin/sh

FN=`/bin/date +%Y-%m-%d`
BK_DIR=’/home/user_name/backup_dir/’
PARAM=’–quote-names –add-drop-table -udb_login -pdb_pass’

cd ${BK_DIR}
/opt/mysql/bin/mysqldump ${PARAM} vadim_db1 > vadim_db1.sql
/opt/mysql/bin/mysqldump ${PARAM} vadim_db2 > vadim_db2.sql

tar -cvf ${BK_DIR}/sql-${FN}.tar *.sql
gzip ${BK_DIR}/sql-${FN}.tar
chmod 600 ${BK_DIR}sql-${FN}.tar.gz

rm *.sql
/*   конец скрипта */

Настройка скрипта делается очень просто:

BK_DIR=’/home/user_name/backup_dir/’ - тут вы должны указать, в какую директорию складывать бакап. Эта директория уже должна быть созданна.

PARAM=’–quote-names –add-drop-table -udb_login -pdb_pass’ - замените db_login и db_pass соответственно на ваши логин и пароль к базе данных (этот аккаунт должен иметь права select к тем базам, которые вы собираетесь бакапить.)

/opt/mysql/bin/mysqldump ${PARAM} vadim_db1 > vadim_db1.sql - где vadim_db1 это имя базы каторую бакапим.  Если у вас MySQL проинсталлирован не в /opt/mysql , то замените /opt/mysql/bin/mysqldump на ваш путь к mysqldump.

Сохраните файл и выставите на него chmod 700 , тем самым защитив его содержимое от посторонних глаз.

Все, работа сделана, теперь можно запускать ./sql-backup.sh
Если вы все настроили правильно, то в /home/user_name/backup_dir/ должен появится файл с содержимым ваших баз. Иначе смотрите конфигурацию скрипта заново.

Разрабатывая корпоративный сайт, в определенный момент времени начинаешь понимать, что сочетания Apache+Perl+Php недостаточно для полноценного веб-сервера. Особенно это становится заметно, когда речь заходит о создании и поддержке большого хранилища данных. Вот здесь нам на помощь и приходит база данных MySQL. О том, что это такое, для чего оно нужно и как его установить, поговорим в данной статье.

Для начала давайте уясним, зачем вообще нужны базы данных.

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

При использовании базы данных перед вами открывается куча ранее недоступных (или очень труднодоступных) возможностей. Например, вы сможете показывать пользователю только те данные, которые он сам захотел видеть, либо предоставлять какие-либо оперативные данные (курсы валют, сводку погоды, цены на какие либо товары/услуги и т.д.). Для всего этого вам и нужно постоянное структурированное хранилище данных. Есть множество различных видов баз данных, но в нашем случае мы будим рассматривать MySQL. Почему именно MySQL? Потому что она является небольшим, компактным и простым в использовании сервером баз данных, идеальным для приложений малого и среднего размера. Стоит отметить и тот факт, что MySQL доступна на ряде платформ Unix, Windows NT/2000, 95/98. Кроме того, отметим, что большинству интернет-приложений, которые можно скачать на различных бесплатных скриптовых серверах, необходима именно MySQL. Например, мне так и не удалось найти полнофункциональный форум, который бы не использовал базу данных.

Убедившись необходимости использования MySQL, давайте поговорим о том, где ее взять и как настроить для работы на локальном компьютере. (Дистрибутив сервера и клиента MySQL можно найти в разделе Download)

Скачивайте версию под Windows(win_32), так как в этой статье будет рассматриваться именно windows-версия. Ориентировочный размер файла 10 Мб. После того как вы скачали zip файл, займемся его установкой. Не пугайтесь, если какие-либо настройки в вашем файле будут отличаться от описанных здесь. Все описанное здесь было проверено на версии Ver 10.11 Distrib 3.23.23-beta for win95/98(i32).

Процесс установки довольно прост как для пользователей, ранее работавших с подобными программами, так и для тех, кто устанавливает и конфигурирует сервер впервые. Для последних следует сделать небольшие пояснения. Только что скачанный zip архив необходимо разархивировать в отдельную папку. Затем следует запустить файл setup.exe. Появится стандартное окно инсталляции. Здесь вы должны внимательно читать все сообщения программы инсталляции и аккуратно жать кнопку “далее”, до тех пор, пока не придется выбирать директорию, куда следует установить сам MySQL сервер и где будут находиться базы. Здесь следует оговориться, что если вы измените папку, которая стояла по умолчанию, то вам придется впоследствии настраивать отдельные переменные в файле конфигурации сервера, что не является препятствием для “профи”, но серьезная проблема для начинающих пользователей. В отношении домашнего сервера, нет смысла изменять путь папки. Разве только для удобства можно поставить поближе к Apache. На реальном сервера пути установки выглядят примерно так: /usr/htdocs — основная папка с документами, /usr/php — здесь живет php, /usr/bin/perl — интерпретатор perl, и, наконец, /usr/web/databases/mysql. Все эти пути условные, и в каждом конкретном случае могут отличаться. У меня на компьютере структура папок организована несколько иначе: основные документы /web/htdocs/ (C:webhtdocs), php — /web/php/ (C:webphp), perl — /web/bin/perl(C:webinperl), mysql — /web/mysql/ (С:webmysql). Но для простоты и удобства настройки все же поставим MySQL в папку по умолчанию, т.е. c:mysql. Это позволит нам в дальнейшем сразу начать работу, без каких-либо дополнительных настроек. Больше никаких существенных изменений в процессе установки нам делать не нужно, просто жмем везде кнопку “далее” и ждем, пока программа установки сделает свое “черное” дело:). После установки мы сможем наблюдать примерно следующую иерархию папок:

C:mysql — корневая директория.

|_bench — контрольные замеры и тест “crash-me”.

|_bin — клиентские программы и сценарии.

|_data — именно здесь будут находиться сами базы данных.

|_docs — различная информация: копирайты, лицензии, краткий мануал и т.д.

|_examples — несколько примеров использования базы (см. мануал).

|_include — файлы заголовков.

|_lib — различные библиотеки.

|_scripts — несколько Perl-скриптов. Подробнее о них в readme’шке.

|_share — файлы сообщений об ошибках.

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

Обычно в приложениях, использующих MySQL, присутствуют три стандартные переменные, которые отвечают за доступ к самой базе данных:

Сделать копию всех статических HTML- и прочих документов просто. Так же несложно периодически “откладывать в сторонку” и копии скриптов. Гораздо более сложной представляется
задача создания копии (далее backup) такой динамичной структуры, как база данных MySQL. Основные трудности, которые возникают перед администратором размещенного на хостинге сайта, обычно бывают такие:

1. Отсутствие физического доступа к файлам базы данных. Как правило, провайдеры хостинга предоставляют возможность работы с базой данных только через скрипты или специальный mysql-клиент,
но не дают прав на доступ непосредственно к файлам, в которых содержатся данные из MySQL-базы.

2. Отсутствие у администратора знаний о том, как вообще надо делать backup. Обычно такая задача возникает только, когда “клюнул жареный петух”. То есть,
в случае аварии, вторжения хакеров или в других внештатных ситуациях. Веб-мастеры просто не готовы к немедленному backup и начинают судорожно изучать документацию по MySQL, а время идет…

3. В случае, если веб-мастер не владеет в достаточной мере навыками работы со специализированными утилитами из пакета MySQL, могут возникать трудности, связанные с ограничениями,
налагаемыми хостинг-провайдером на пользовательские аккаунты. Например, если база очень большая и ее размер превышает лимит на доступную пользователю память (RAM),
backup сделать будет сложно. Нужно пользоваться тонкими настройками утилит резервного копирования, что иногда тоже вызывает трудности на практике.

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

Как сделать копию базы MySQL mysqldump,
позволяющая быстро и просто производить операции по созданию резервных копий баз MySQL. Также mysqldump дает возможность делать очень тонкие настройки для
управления процессом создания резервных копий баз данных или отдельных таблиц. Можно сказать, что mysqldump – это основной инструмент, которым Вам придется пользоваться в том случае, если Вы будете делать backup MySQL.

Существует программа

Сразу возьмем простую задачу, которую будем решать с помощью mysqldump, и разберемся, что к чему. Есть хостинг, есть база данных DBNAME, которую выделил Вам хостинг-провайдер.
Есть хост HOST, на котором размещен сервер MySQL, логин LOGIN к нему, порт PORT, на котором работает сервер, а также пароль PASS. Имея все эти данные, можно сделать dump (дамп, копию) базы DBNAME так (выполняем в unix shell):

> mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME > dump.txt

После выполнения данной команды в файле dump.txt у нас будет копия MySQL-базы DBNAME. Это произойдет только в том случае, конечно, если все параметры Вы зададите верно,
в соответствии с настройками своего хостинга. Сразу нужно сказать, что программа mysqldump производит вывод результатов прямо Вам на STDIN, то есть, на экран.
Нужно перенаправлять вывод в какой-либо файл. Например, как в данном случае – ” > dump.txt “. Если этого не сделать, а база большая, Вы получите на экран все те мегабайты информации, которые в ней содержатся.

Немного расскажем о том, что же делает mysqldump. Эта программа создает сценарий восстановления Ваших данных. То есть, вывод mysqldump – это не какие-то абстрактные и нечитаемые двоичные данные,
а осмысленный текст сценария. Например, если в Вашей базе была таблица test, в которой было поле test2 с типом данных integer и одна-единственная запись “1111″, то mysqldump создаст примерно такой сценарий:

# MySQL dump 8.14 # # Host: HOST Database: DBNAME #-------------------------------------------------------- # Server version 3.23.39-log #
# Table structure for table 'test' # CREATE TABLE test ( test2 int(11) default NULL ) TYPE=MyISAM; # # Dumping data for table 'test2' # INSERT INTO test2 VALUES ('1111');

Таким образом, mysqldump “опишет” все Ваши таблицы и создаст INSERT-команды для восстановления данных в таблицах. Итак, мы перенаправляем вывод mysqldump в текстовый файл,
который потом будем использовать для восстановления. Рассмотрим и этот процесс – воссоздание базы из резервной копии.

Для восстановления будем пользоваться стандартной программой mysql, которая входит в комплект поставки MySQL наряду с mysqldump. Допустим, у нас имеется backup в файле dump.txt.
Нам нужно восстановить его в рабочую базу. Например, мы случайно удалили нашу базу данных, а теперь пытаемся исправить эту незадачу. Делаем так :

> mysql -uLOGIN -PPORT -hHOST -pPASS DBNAME < dump.txt

То есть, заставляем mysql-клиент соединиться с сервером и выполнить сценарий, который у нас имеется. После выполнения этой команды в Вашей базе появятся таблицы и данные из резервной копии.
Учитывайте то, что данные будут просто восстанавливаться по сценарию из dump.txt. То есть, если таблицы, которые упоминаются в дампе базы, уже существуют и имеют другую структуру,
тут явно возникнет ошибка. Просто посмотрите на сценарий и на рабочую базу и представьте, что Вы вручную выполняете команды из сценария. Если уверены, что все будет хорошо – смело восстанавливайте.

Рассмотрим более тонкие настройки mysqldump:

• –databases позволяет сделать так, что mysqldump включит в сценарий восстановления команды CREATE DATABASE /*!33333 IF NOT EXISTS*/ DBNAME и USE DBNAME.
  Это позволит создавать рабочие базы “с нуля”. То есть, без использования –databases подразумевается, что пользователь восстанавливает одну базу данных и явно указывает,
  куда нужно помещать восстанавливаемые данные. Если же backup создается с целью сделать полностью рабочую копию данных, например, на другом MySQL-сервере, то нужно использовать этот ключ;
• –all-databases позволяет сделать копии всех баз данных, которые существуют на данном MySQL-сервере. Если же нужно сделать копии только некоторых баз,
  нужно просто указать их через пробел при вызове mysqldump из командной строки (см. выше);
• Ключ –help. Программа mysqldump имеет множество версий. Посмотреть, какие возможности поддерживаются конкретно Вашей версией, можно с помощью этого ключа;
• –add-drop-table – ключ, который заставит mysqldump добавлять в итоговый сценарий команду drop table перед созданием таблиц. Это позволит избежать некоторых ошибок
  при восстановлении базы из резервной копии. Конечно, нужно учитывать то, что таблицы, находящиеся в рабочей копии (если таблицы с таким же именем существуют в backup),
  перед восстановлением из резервной копии будут удалены из основной базы и пересозданы из backup;
• –no-data. С помощью этого ключа можно быстро сделать копию структуры таблицы/баз без самих данных. Например, Вы создали сложную таблицу и хотели бы сохранить
  на будущее ее структуру, а сами данные, которые находятся в этой таблице, Вам в резервной копии не нужны;
• –result-file=… – этот ключ можно использовать для перенаправления вывода в файл. Можно использовать обычное unix-перенаправление командой “>“, а можно – вот этот ключ. Кому что нравится;

Кроме перечисленных ключей mysqldump имеет и еще некоторое количество очень полезных возможностей, которые Вы можете применять по обстоятельствам. Полная документация
по mysqldump доступна на странице http://www.mysql.com/doc/m/y/mysqldump.html.

Еще один очень полезный совет по использованию mysqldump в хостинговой среде. Как правило, при использовании хостинга на пользователя налагаются некоторые ограничения.
Например, нельзя занять больше некоторого количества физической памяти (RAM, ОЗУ). mysqldump по умолчанию помещает все полученные от MySQL-сервера данные в память,
а потом записывает все это на диск. Соответственно, если провайдер дает Вам занять, например, 30Мб памяти, а база, копию которой Вы делаете с помощью mysqldump, занимает 50Мб,
конечно, тут возникнет ошибка – mysqldump не сможет отработать корректно и завершится аварийно, о чем Вам сообщит. Чтобы “заставить” mysqldump писать данные сразу на диск,
а не хранить их, пусть даже и временно, в памяти, используйте ключ –quick. Это решит проблему.

Автоматизация резервного копирования

Теперь подумаем, как бы нам автоматизировать процесс создания резервных копий базы данных. Итак, существует программа – cron. Она позволяет запускать процессы в указанное
пользователем время или с определенной периодичностью. Сразу оговоримся – cron в общем случае существует только под Unix, так что, если Вы используете для хостинга ОС Windows,
проконсультируйтесь со своим хостинг-провайдером о том, как лучше запускать процессы в нужное время. Да и вообще, пожалуй, этот пункт будет интересен только unix-пользователям.

В unix shell запускаем crontab -e и создаем такое правило запуска процесса создания копий базы:

0 0 * * * mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date “+%Y-%m-%d”`.gz

Эта команда, запускаясь из cron в полночь (00:00) каждых суток, делает дамп Вашей базы DBNAME и архивирует его архиватором gzip в файл-архив с именем, соответствующим текущей дате.
Например, если мы делаем dump 3 января 2002 года, имя файла с архивом будет 2002-01-03.gz. Для того, чтобы получить файлы, по именам которых можно удобно узнать дату их создания,
мы используем команду date, которая является стандартной для всех unix-систем. Эта команда позволяет задавать произвольный формат вывода даты, что мы и использовали – date “+%Y-%m-%d”.
Мы поместили эту команду в обратные одинарные кавычки (backticks), что в unix shell заставляет вставить в команду (утрируя) результат выполнения другой команды.

Сохраняем правило для cron и ждем результатов. Итак, каждый день мы будем иметь на диске заархивированную копию нашей базы данных. Можно быстро найти нужный архив по его названию и восстановить то,
что испортилось, например. Кстати, если Вы хотите автоматизировать удаление старых архивов, попробуйте воспользоваться cron и командой find, которая обычно есть в unix.
Запуская периодически find ~/каталог-с-архивами -name “*.gz” -mtime +7, Вы будете удалять архивы, которые “старше” семи дней. Прочитайте документацию по find – она доступна по команде man find в unix shell.

Если у Вас есть машина, постоянно подключенная к интернет, можно так же по cron копировать созданный Вами backup на нее. Конечно, провайдерская хостинг-машина – это очень надежная штука. Однако, как говорится,
“береженого Бог бережет”. Старая как мир истина в определенных условиях может и Вам помочь. Используйте для копирования на другую машину команды ftp и scp. Добавьте их запуск в cron.
Если Ваша машина поддерживает соединение по протоколу ssh, используйте secure copy клиент для копирования файлов – scp. Читайте документацию по этой команде в man-странице man scp.
Примерный запуск: scp 2002-01-03.gz login@your.host.ru: – закачиваем файл 2002-01-03.gz на машину your.host.ru авторизовавшись там под логином login.

Дополнительные возможности

Скрипт mysqlhotcopy, написанный на языке Perl, поможет Вам упростить создание копий баз данных и отдельных таблиц. Использование этого скрипта позволяет производить резервное копирование еще более гибко.
Описание mysqlhotcopy читайте тут.

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

Статья будет состоять из пунктов, назовем их рецептами. Автор попытался отсортировать их, для более удобного чтения. Некоторые рецепты, универсальны и подходят для всех СУБД. Вы можете читать их в любом порядке, пропуская то, что уже хорошо знаете.

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

1). Во всех версиях 3.хх не поддерживается ссылочная целостность и вложенные запросы(как это обойти-рецепт 6). По первичному ключу MySQL автоматически создает индекс.

Для других атрибутов можно создать дополнительные индексы. Индекс создается вместе с таблицей -
CREATE TABLE [имя_таблицы] (… ,INDEX|KEY [имя_индекса] (аттрибут1,…))
причем служебные поля KEY и INDEX – синонимы. Начиная с версии 3.22 индекс можно создать и отдельно -
CREATE INDEX [имя_индекса] ON [имя_таблицы] (аттрибут1,…).

Обычно дополнительные индексы, создаются для ускорения процесса выборки из базы данных. При его создании объем таблицы увеличивается, поэтому если скоростные параметры вас устраивают, то индекс создавать не нужно. Если скорость неприемлима, индекс добавляется для атрибутов указанных в запросе после конструкции WHERE.
(Пример:
SELECT uid,data,comments FROM test_table WHERE uid = 90 AND data = ‘test’;
CREATE INDEX ind1 ON test_table (data))

2). Если из выборки необходимо извлечь лишь несколько строк, то в конструкции SELECT указывается LIMIT x,y.
x-смещение,
y-максимальное число строк
Параметр x-не обязателен.
При этом на выполнеие сокращенного запроса уходит меньше времени.
(Пример:
SELECT * FROM table WHERE d > 900 LIMIT 100)

3). Иногда требуется сохранить результат выборки в файле. Это делается следующим образом -
SELECT … INTO OUTFILE ‘/path/to/dir/filename’.
Файл не должен существовать.

Обратная операция загрузки из файла в таблицу -
LOAD DATA INFILE ‘/path/to/dir/filename’ INTO TABLE table. Строки файла должны иметь формат вида (поле1\tполе2\t.., где \t – символ табуляции). Более сложный формат команды в данной статье рассматриваться не будет.

Последний вариант может применяться и в случае повышения скорости загрузки данных в таблицу. Предположим, ваша программа последовательно выполняет вставку большого количества строк в таблицу. Эта операция может занять несколько минут, в зависимости от объема данных и количества индексов. С помощью же LOAD DATA автору удалось повысить скорость вставки в 7 раз!

4). При частых операциях вставки, удаления и обновления данных таблицы(с типами – VARCHAR,BLOB,TEXT), возникает дефрагментация. Для того, чтобы ее уменьшить существует операция OPTIMIZE TABLE [имя_таблицы].

5). Удобной возможностью MySQL является авто увеличение значения поля. Создание такого поля – CREATE TABLE .. pole int(7) AUTO_INCREMENT.. . Причем это поле должно быть объявлено, как первичный ключ и следовательно в таблице оно должно быть единственным.

При вставке в таблицу имя такого поля можно не указывать. Операция auto increment в MySQL упрощена. Например в Oracle для этого необходимо отдельно создавать, так называемую последовательнось(SEQUENCE). И при вставке ее явно указывать.

Иногда бывает нужно при добавлении данных в таблицы знать значения поля с AUTO_INCREMENT для вставленных строк, а лишние запросы слишком замедляют работу. Для этого существует функция LAST_INSERT_ID().
CREATE TABLE table1 (id AUTO_INCREMENT, comm varchar(20),PRIMARY KEY (id));
INSERT INTO table1 (comm) VALUES(’test’);
INSERT INTO table2 (id_table1) VALUES (LAST_INSERT_ID());
Или, если вы пишите на perl, то можно использовать значения объекта.
$dbh->{’mysql_insertid’};

6). В версиях 3.xx не поддерживаются вложенные запросы. Но вместо них можно использовать временные таблицы. Пример создания такой таблицы:
CREATE TEMPORARY TABLE table SELECT … .
А потом делать выборку из нее с дополнительным условием. При написании интерфейса к MySQL на perl, при создании временных таблиц вышеописанным способом, предпочтительнее использовать метод ‘do’ вместо пары ‘prepare-execute’, так как эта функция работает быстрее.

7). Некоторые способы хранения резервных копий таблиц или данных.

a) Резервное копирование данных можно производить с помощью cgi-скриптов, написанных на shell. Администратор с определенной периодичностью запускает данный скрипт, данные сохраняются в файле. Если файл уже существет, то при запуске скрипта информация в нем обновляется. Скрипт можно запускать cron’ом, тогда команды echo нужно убрать.
Файл (db) представляет из себя интструкции INSERT … . Ключ -t указывает не копировать информацию о таблицах – CREATE TABLE … . Другой скрипт запускает SQL-команды из файла.

#!/bin/sh DB=test USER=user PASSWORD=pass HOST=host.ru export DB USER PASSWORD HOST cd /usr/local/mysql/bin ./mysql -D $DB -h$HOST -u$USER -p$PASSWORD <db echo "Content-type: text/html" echo "" echo "OK"

Для повышения надежности этот файл можно скачивать по ftp. Одним из удобств данного метода является возможность портирования данных в любую СУБД.

б) Начиная с версии 3.23.25 появилась возможность резервного копирования таблиц (без индексов).
BACKUP TABLE [имя] TO ‘/dir’;
И восстановления
RESTORE TABLE [имя] FROM ‘/dir’;

в) Еще одой возможностью является, приведенный выше способ записи результата запроса в файл.
SELECT … INTO OUTFILE ‘/dir/file’;
И последующего восстановления из файла.
LOAD DATA … .

8). Приятно удивила возможность полнотекстового идексирования полей VARCHAR и TEXT(начиная с версии 3.23.23). В запросе указываетя подстрока, и в результате выводятся те значения полей где она(подстрока) найдена. Например вы храните в БД статьи или анонсы статей и хотите предоставить поиск по статьям пользователю. Можно конечно установить поисковик(пример Yandex.Site), но для этого необходимо заводить отдельную БД, да и если требования к поисковой системе минимальны, данная установка просто излишество.

MySQL предоставляет возможность полнотекстового поиска. Проиллюстрирую на примере:
CREATE TABLE data (did int(7) AUTO_INCREMENT, a text, FULLTEXT (a),PRIMARY KEY (did));
INSERT INTO data (a) VALUES (’test message’), (’test mysql fulltext’), (’test test test’);
SELECT * FROM data WHERE MATCH (a) AGAINST (’mysql’);
В результате выведется строка = ‘test mysql fulltext’. А по запросу
SELECT data,MATCH (a) AGAINST (’mysql’) as m FROM data;
поле m будет содержать числовое значение. Как их использовать оставляю на ваше усмотрение.

Таким образом организован простейший поиск.

9). Некоторые советы по оптимизации:

• После заполнения данными таблицы, выполните команду ‘isamchk -a’ или ‘myisamchk -a’, в зависимости от типа таблицы. Эта команда соберет статистику по таблицам, которую сможет использовать оптимизатор.
• Удалите ненужные скобки в запросе.
• Всегда, где это возможно используйте WHERE вместо HAVING
• Свойство атрибута NOT NULL экономит 1бит и ускоряет запросы.
• Испольэование типа CHAR вместо VARCHAR, позволит ускорить выборку по этим полям на 10-100%. Это целесообразно делать если количество символов поля фиксировано, приэтом оно принимает различные значения. В случае переменной длины, придется расплачиваться дисковым пространством. Вообще поля фиксированной длины обрабатываются быстрее. Выбор того или иного типа зависит от решаемой задачи и требований к БД.
• Если выборки из нескольких таблиц слишком замедленна, а дополнительые индексы не хочется создавать, разбейте один большой запрос на несколько. Пример из жизни: выборка из 3 таблиц, со “сложным” условиям длилась 4мин.!!!. Причем все таблицы имели первичный ключ. Разбив один запрос на 2 удалось ускорить выборку на 3мин. 54сек. Но задача оптимизации индивидуальна и зависит от структуры БД и данный метод возможно подходит не для всех БД. Так что для собственной базы необходимо проводить исследования.

10). Одним из способов организации данных является определение в одной таблице и первичных и внешних ключей. Я сталкивался с такой таблицей, правда в Oracle. Это бывет удобным в случае когда, “подчинненная” строка имеет такую же структуру, скажем иерархия отделов и должностей в организации. Для извлечения всей иерархии заданного объекта, необходимо строить рекурсию, к сожалению в MySQL нет конструкции – connect by prior.

Для достижения необходимой производительности может помочь избыточность. Есть некоторое правило – в мастер таблицу(таблицу с первичным ключем) заносится последнее значение объекта. Например для таблицы продавцов, это может быть последняя осуществленная сделка.

Вот некоторые соображения, которые могут пригодится при проектировании БД в MySQL. На сегодняший день она получила наибольшее распространение в internet, в первую очередь благодаря своей простоте и минимальным требованиям к системным ресурсам. Конечно она не может конкурировать с “монстрами” СУБД, такими как, например Oracle и для крупных проектов ее будет не достаточно.

Но на MySQL возложены совершенно иные задачи, с которыми, на мой взгляд, она прекрасно справляется.

Основные термины

атрибут(поле) – определенная атомарная часть информации об объекте.

первичный ключ – атрибут или совокупность атрибутов однозначно идентифицирующих объект.

кортеж(строка) – вся информация об одном объекте в таблице. Совокупность значений всех атрибутов объектов.

#!/bin/sh DB=test USER=user PASSWORD=pass HOST=host.ru export DB USER PASSWORD HOST echo "Content-type: text/plain" echo "" cd /usr/local/mysql/bin ./mysqldump -h$HOST -u$USER -p$PASSWORD -t $DB >db echo "OK"

ДЕРЕВО КАТАЛОГОВ NESTED SETS (ВЛОЖЕННЫЕ МНОЖЕСТВА) И УПРАВЛЕНИЕ ИМ

     О проблемах хранения деревьев в SQL базах данных вопрос можно не поднимать, просто сказать, что они есть.

     Прежде всего посмотрим как выглядят деревья Nested Sets, как они организованы и в чем удобство их использования.

     На схеме представлено дерево, описанное по всем правилам метода “Вложенных множеств”. Квадратами обозначены узлы дерева, синие цифры в верхнем правом и верхнем левом углах узла – уровень и уникальный идентификатор соответственно, а красные цифры в нижних углах – это левый и правый ключ. Именно в этих двух цифрах – левом и правом ключе заложена вся информация о дереве. И если информацию о ключах занести в базу данных, то работа с деревом намного упрощается.
     Обратите внимание на то, в каком порядке проставлены эти ключи. Если мысленно пройтись по порядку от 1 до 32, то вы обойдете все узлы дерева слева направо. Фактически это путь обхода всех узлов дерева слева направо.

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

Создадим таблицу, где мы будем хранить наше дерево:

CREATE my_tree (
id INT(10) NOT NULL AUTO_INCREMENT,
name VARCHAR(150) NOT NULL,
left_key INT(10) NOT NULL DEFAULT 0,
right_key INT(10) NOT NULL DEFAULT 0,
level INT(10) NOT NULL DEFAULT 0,
PRIMARY KEY id,
INDEX left_key (left_key, right_key, level)
)

Теперь определим, какие данные мы можем из неё (таблицы) выбрать:

1. Собственно само дерево:

SELECT id, name, level FROM my_tree ORDER BY left_key

В итоге, после небольшой обработки (в которой level играет роль множителя отступа), получим следующий список:

• Узел 1
• • Узел 2
• • • Узел 5
• • • • Узел 10
• • • • Узел 11
• • Узел 3
• • • Узел 6
• • • Узел 7
• • • • Узел 12
• • • • Узел 13
• • • • Узел 14
• • • Узел 8
• • Узел 4
• • • Узел 9
• • • • Узел 15
• • • • Узел 16

2. Выбор подчиненных узлов (за отправной узел возьмем “Узел 7″ его ключи $left_key, $right_key и уровень $level)

SELECT id, name, level FROM my_tree WHERE left_key >= $left_key AND right_key <= $right_key ORDER BY left_key

В итоге получаем:

• • • Узел 7 • • • • Узел 12 • • • • Узел 13 • • • • Узел 14

3. Выбор родительской “ветки”:

SELECT id, name, level FROM my_tree WHERE left_key <= $left_key AND right_key >= $right_key ORDER BY left_key

В итоге получаем:

• Узел 1 • • Узел 3 • • • Узел 7

4. Выбор ветки в которой участвует наш узел:

SELECT id, name, level FROM my_tree WHERE right_key > $left_key AND left_key < $right_key ORDER BY left_key

В итоге получаем:

• Узел 1 • • Узел 3 • • • Узел 7 • • • • Узел 12 • • • • Узел 13 • • • • Узел 14

В общем использование в условии запроса ключи узла можно выбрать любые данные связанные с этим узлом.

Единственным затруднением может возникнуть выборка родительского узла, чтобы его получить можно сделать запрос:

SELECT id, name, level FROM my_tree WHERE left_key <= $left_key AND right_key >= $right_key AND level = $level + 1 ORDER BY left_key

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

УПРАВЛЕНИЕ ДЕРЕВОМ КАТАЛОГОВ

     Прежде чем начинать управлять деревом, создадим проверку целостности ключей, что бы линий раз не наступать на «грабли». Для этого определим основные правила:

1. Левый ключ ВСЕГДА меньше правого;
2. Наименьший левый ключ ВСЕГДА равен 1;
3. Наибольший правый ключ ВСЕГДА равен двойному числу узлов;
4. Разница между правым и левым ключом ВСЕГДА нечетное число;
5. Если уровень узла нечетное число то тогда левый ключ ВСЕГДА нечетное число, то же самое и для четных чисел;
6. Ключи ВСЕГДА уникальны, вне зависимости от того правый он или левый;

Отсюда, создаем проверочные запросы :

1. SELECT id FROM my_tree WHERE left_key >= right_key

Если все правильно то результата работы запроса не будет, иначе, получаем список идентификаторов неправильных строк;

2 (3). SELECT COUNT(id), MIN(left_key), MAX(right_key) FROM my_tree

Получаем количество записей (узлов), минимальный левый ключ и максимальный правый ключ, проверяем значения.

4. SELECT id, MOD((right_key – left_key) / 2) AS ostatok FROM my_tree WHERE ostatok = 0

Если все правильно то результата работы запроса не будет, иначе, получаем список идентификаторов неправильных строк;

5. SELECT id, MOD((left_key – level + 2) / 2) AS ostatok FROM my_tree WHERE ostatok = 1

Если все правильно то результата работы запроса не будет, иначе, получаем список идентификаторов неправильных строк;

6. SELECT t1.id, COUNT(t1.id) AS rep, MAX(t3.right_key) AS max_right FROM my_tree AS t1, my_tree AS t2, my_tree AS t3 WHERE t1.left_key <> t2.left_key AND t1.left_key <> t2.right_key AND t1.right_key <> t2.left_key AND t1.right_key <> t2.right_key GROUP BY t1.id HAVING max_right <> SQRT(4 * rep + 1) + 1

Здесь, я думаю, потребуется некоторое пояснение запроса. Выборка по сути осуществляется из одной таблицы, но в разделе FROM эта таблица “виртуально” продублирована 3 раза: из первой мы выбираем все записи по порядку и начинаем сравнивать с записями второй таблицы (раздел WHERE) в результате мы получаем все записи неповторяющихся значений. Для того, что бы определить сколько раз запись не повторялась в таблице, производим группировку (раздел GROUP BY) и получаем число “не повторов” (COUNT(t1.id)). По условию, если все ключи уникальны, то число не повторов будет меньше на одну единицу чем общее количество записей. Для того, чтобы определить количество записей в таблице, берем максимальный правый ключ (MAX(t3.right_key)), так как его значение – двойное число записей, но так как в условии отбора для записи с максимальным правым ключом – максимальный правый ключ будет другим, вводится третья таблица, при этом число “неповторов” увеличивается умножением его на количество записей. SQRT(4*rep +1) – решение уравнения x^2 + x = rep. Если все правильно то результата работы запроса не будет, иначе, получаем список идентификаторов неправильных строк;

Примечание: Хотя данное тестирование не дает 100% гарантии, но определит большее количество ошибок.

СОЗДАНИЕ УЗЛА:

Создание узла – самое простое действие над деревом. Для того, что бы его осуществить нам потребуется уровень и правый ключ родительского узла (узел в который добавляется новый), либо максимальный правый ключ, если у нового узла не будет родительского.

Пусть $right_ key – правый ключ родительского узла, или максимальный правый ключ плюс единица (если родительского узла нет, то узел с максимальным правым ключом не будет обновляться, соответственно, чтобы не было повторов, берем число на единицу большее). $level – уровень родительского узла, либо 0, если родительского нет.

1. Обновляем ключи существующего дерева, узлы стоящие за родительским узлом:

UPDATE my_tree SET left_key = left_key + 2, right_ key = right_ key + 2 WHERE left_key > $right_ key

Но мы обновили только те узлы в которых изменяются оба ключа, при этом родительскую ветку (не узел, а все родительские узлы) мы не трогали, так как в них изменяется только правый ключ. Следует иметь в виду, что если у нас не будет родительского узла, то есть новый узел будет корневым, то данное обновление проводить нельзя.

2. Обновляем родительскую ветку:

UPDATE my_tree SET right_key = right_key + 2 WHERE right_key >= $right_key AND left_key < $right_key

3. Теперь добавляем новый узел :

INSERT INTO my_tree SET left_key = $right_key, right_key = $right_key + 1, level = $level + 1 [дополнительные параметры ]

4. Проверяем.

Теперь можно объединить первые два запроса в один, что бы не делать лишних действий.

UPDATE my_tree SET right_key = right_key + 2, left_key = IF(left_key > $right_key, left_key + 2, left_key) WHERE right_key >= $right_key

УДАЛЕНИЕ УЗЛА

Удаление узла не намного сложнее, но требуется учесть, что у удаляемого узла могут быть подчиненные узлы. Для осуществления этого действия нам потребуется левый и правый ключ удаляемого узла.

Пусть $left_key – левый ключ удаляемого узла, а $right_key – правый**

** Получить эти данные не сложно одним простейшим запросом.

1. Удаляем узел (ветку):

DELETE FROM my_tree WHERE left_key >= $left_key AND right_ key <= $right_key

2. Обновляем ключи оставшихся веток:

Как и в случае с добавлением обновление происходит двумя командами: обновление ключей родительской ветки и обновление ключей узлов, стоящих за родительской веткой. Следует правда учесть, что обновление будет производиться в другом порядке, так как ключи у нас уменьшаются.

2.1. Обновление родительской ветки :

UPDATE my_tree SET right_key = right_key – ($right_key – $left_key + 1)*** WHERE right_key > $right_key AND left_key < $left_key

*** Так как мы не знаем точное количество подчиненных узлов, мы вычисляем длину диапазона (смещения) ключей удаляемой ветки (узла).

2.2. Обновление последующих узлов :

UPDATE my_tree SET left_key = left_key – ($right_key – $left_key + 1), right_key = right_key – ($right_key – $left_key + 1) WHERE left_key > $right_key

3. Проверяем.

Теперь можно объединить последние два запроса в один, что бы не делать лишних действий.

UPDATE my_tree SET left_key = IF(left_key > $left_key, left_key – ($right_key – $left_key + 1), left_key), right_key = right_key – ($right_key – $left_key + 1) WHERE right_key > $right_key

ПЕРЕМЕЩЕНИЕ УЗЛА

Перемещение узла – самое сложное действие в управлении деревом. На схеме показаны области, на которые можно разделить наше дерево. Из её можно увидеть, что узел может перемещаться только в две разные области: вышестоящих и нижестоящих узлов. Вообще, чем примечательно использование Nested Set, что с помощью двух ключей ветки возможен выбор узлов любой области.

1. Вверх по дереву (в область вышестоящих узлов), включает в себя:

• Перенос ветки (узла) в подчинение нижестоящему по дереву узлу;
• Перенос ветки (узла) вверх без изменения родительского узла (изменение порядка узлов);

2. Вниз по дереву (в область нижестоящих узлов), включает в себя.

• Перенос ветки в «корень» дерева (учитывая, что переносимая ветка будет последней по порядку);
• Перенос ветки (узла) вниз без изменения родительского узла (изменение порядка узлов);
• Поднятие узла (ветки) на уровень выше;
• Перемещение ветки вниз по дереву:

Для начала выберем ключи следующих узлов:

1. Ключи и уровень перемещаемого узла;

SELECT level, left_key, right_key FROM my_tree WHERE id = $id

Получаем $level, $left_key, $right_key

2. Уровень нового родительского узла (если узел перемещается в “корень” то сразу можно подставить значение 0):

SELECT level FROM my_tree WHERE id = $id_up

Получаем $level_up

3. Правый ключ узла за который мы вставляем узел (ветку):

Данный параметр, а не ключи нового родительского узла, выбираем по одной простой причине: Для обычного перемещения этого ключа нам будет достаточно, а при изменении порядка узлов и переноса в “корень” дерева данный параметр нам просто необходим.

Данная переменная берется в зависимости от действия:

• При простом перемещении в другой узел;

SELECT (right_key – 1) AS right_key FROM my_tree WHERE id = [id нового родительского узла]

• При изменении порядка, когда родительский узел не меняется – правый ключ узла за которым будет стоять перемещаемый;

SELECT left_key, right_key FROM my_tree WHERE id = [id соседнего узла с который будет(!) выше (левее)]****

**** Следует обратить внимание, что при поднятии узла вверх по порядку, узел выбирается не соседний, а следующий, за неимением оного (перемещаемый узел будет первым) берется левый ключ родительского узла

• При переносе узла в корень дерева – максимальный правый ключ ветки;

SELECT MAX(right_key) FROM my_tree

• При поднятии узла на уровень выше – правый ключ старого родительского узла

SELECT right_key FROM my_tree WHERE level = $level

Получаем $right_key_near и $left_key_near (для варианта изменения порядка)

4. Определяем смещения:

• $level_up – $level + 1 = $skew_level – смещение уровня изменяемого узла;
• $right_key – $left_key + 1 = $skew_tree – смещение ключей дерева;

Выбираем все узлы перемещаемой ветки:

SELECT id FROM my_tree WHERE left_key >= $left_key AND right_key <= $right_key

Получаем $id_edit – список id номеров перемещаемой ветки.

Так же требуется определить: в какую область перемещается узел, для этого сравниваем $right_key и $right_key_near, если $right_key_near больше, то узел перемещается в облась вышестоящих узлов, иначе – нижестоящих (почему существует разделение описано ниже).

Где у нас изменяются ключи по дереву во время переноса узла показано на схеме:

Как видно из схемы правые ключи меняются только у левой родительской ветки, левые ключи меняются у правой родительской ветки а оба ключа меняются в узлах находящихся между родительской старой и родительской новой веткой, области изменений, не меняются в зависимости от того, в какую (вышестоящую или нижестоящую) область перемещается узел. Отличием является, то что при перемещении в вышестоящую область ключи увеличиваются, а при переходе в нижестоящую – уменьшаются.

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

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

При перемещении вверх по дереву выделяем следующие области:

• Для левого ключа:
  • левый ключ узла меньше $left_key
  • левый ключ узла больше $right_key_near

• Для правого ключа:
  • правый ключ узла меньше $left_key
  • правый ключ узла больше $right_key_near

Хотел бы обратить внимание на то, что в условии с $right_key_near и $left_key дерево разделяется на разные области так как эти переменные сравниваются с разными ключами.

Определяем смещение ключей редактируемого узла $right_key_near – $left_key + 1 = $skew_edit;

Так как при в условиях не участвуют ключи кроме изменяемых, то порядок действий не имеет значения.

1. UPDATE my_tree SET right_key = right_key + $skew_tree WHERE right_key < $left_key AND right_key > $right_key_near

2. UPDATE my_tree SET left_key = left_key + $skew_tree WHERE left_key < $left_key AND left_key > $right_key_near

Теперь можно переместить ветку:

UPDATE my_tree SET left_key = left_key + $skew_edit, right_key = right_key + $skew_edit, level = level + $skew_level WHERE id IN ($id_edit)

После оптимизации этих запросов получаем всего один:

UPDATE my_table
SET right_key = IF(left_key >= $left_key, right_key + $skew_edit, IF(right_key < $left_key, right_key + $skew_tree, right_key)),
level = IF(left_key >= $left_key, level + $skew_level, level),
left_key = IF(left_key >= $left_key, left_key + $skew_edit, IF(left_key > $right_key_near, left_key + $skew_tree, left_key))
WHERE right_key > $right_key_near AND left_key < $right_key

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

Замечу, что при использовании этой команды, выбирать узлы перемещаемой ветки не нужно.

При перемещении вниз по дереву выделяем следующие области:

• Для левого ключа:
  • левый ключ узла больше $right_key
  • левый ключ узла меньше $right_key_near

• Для правого ключа:
  • правый ключ узла больше $right_key
  • правый ключ узла меньше или равно $right_key_near

Опять же порядок не имеет значения, поэтому просто делаем команды на обновление. Правда хочу обратить внимание на тот факт, что в условии: “левый ключ узла меньше $right_key_near” узел в котором находится $right_key_near тоже попадает в диапазон изменения, следует иметь ввиду, что при сравнении не однотипных ключей (правый <-> левый) текущий узел попадает или не попадает в диапазон без использования равенства в условии.

Определяем смещение ключей редактируемого узла $right_key_near – $left_key + 1 – $skew_tree = $skew_edit.

1. UPDATE my_tree SET right_key = right_key - $skew_tree WHERE right_key > $right_key AND right_key <= $right_key_near

2. UPDATE my_tree SET left_key = left_key - $skew_tree WHERE left_key < $left_key AND left_key > $right_key_near

Теперь можно переместить ветку:

UPDATE my_tree SET left_key = left_key + $skew_edit, right_key = right_key + $skew_edit, level = level + $skew_level WHERE id IN ($id_edit)

После оптимизации этих запросов получаем всего один:

UPDATE my_table
SET left_key = IF(right_key <= $right_key, left_key + $skew_edit, IF(left_key > $right_key, left_key - $skew_tree, left_key)),
level = IF(right_key <= $right_key, level + $skew_level, level),
right_key = IF(right_key <= $right_key, right_key + $skew_edit, IF(right_key <= $right_key_near, right_key - $skew_tree, right_key))
WHERE right_key > $left_key AND left_key <= $right_key_near

Замечания те же, что и при перемещении ветки вверх по дереву.

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

Сергей Томулевич (Phoinix)

Спасибо Максу (Maxim Matyukhin) за помощь в написании данной статьи

• Инсталляция и первый запуск сервера MySQL.
• Таблицы привилегий.
• Специальные пользователи MySQL.
• Создание баз данных и таблиц.
• Разрешение проблем.
• Запросы к базе данных и команда Select.
• Основные утилиты MySQL.

Инсталляция и первый запуск MySQL.

• Установка
• Запуск

Установка.

Установка MySQL проходит довольно просто.Для начала нужно скачать и распаковать исходники (как это делать я думаю писать не стоит).
Найти их можно на сайте MySQL в разделе Downloads-на любой вкус и для любой ОС,так как MySQL работает практически на всех платформах.

Если вы работаете на Unix-платформе(в частности я буду приводить примеры для Linux,которые немногим отличаются для других Unix-платформ),нужно выполнить следующие шаги:

 shell> gunzip mysql-VERSION-OS.tar.gz | tar xvf - shell> ln -s mysql-VERSION-OS mysql shell> cd mysql shell> scripts/mysql_install_db shell> bin/safe_mysqld & 

Распаковывать исходники нужно в каталог /usr/local,так как в настроечных скриптах указывается именно эта директория и создать симлинк для каталога mysql-это делается,
потому что в тех же скриптах указывается этот каталог.Нужно подредактировать скрипты mysql_install_db и safe_mysqld применительно к вашей системе,для того,чтобы они начали работать,
в частности исправить путь для указания местоположения mysqld и баз данных. Для запуска демона mysqld нужно перейти в каталог /usr/local/mysql/bin и запустить скрипт safe_mysql в фоновом режиме.
Если вы не получили никаких сообщений,это уже хорошо-значит,все прошло успешно.
Если же появилось сообщение об ошибке,значит что-то не в порядке со скриптами.Можно настроить запуск сервера mysql во время загрузки компьютера.
Для этого нужно поместить запуск safe_mysql в один из файлов инициализации системы(они разные в зависимости от версии Linux). Для Linux Slakware в файл /etc/rc.d/rc.local нужно добавить:

/usr/local/mysql/bin/safe_mysqld &

Что касается Windows,поклонником которого я являюсь,то здесь установка проходит практически без проблем.Распаковываете zip-архив,запускаете exe-файл,а дальше все работает само.
Устанавливается обычно в каталог c:mysql. В Windows NT MySQL устанавливается в виде службы и можно указать способ запуска-ручной или автоматический (Start-Settings-Control Panel-Services-MySQL-Startup).

Запуск MySQL.

Управление сервером обычно осуществляется из командной строки.Насчет Linux я уже писал,а в Windows 95/98 откройте сеанс DOS и выполните следующие команды:

 cd mysqlbin mysqlbin>mysqld --standalone

Эта команда запустит демон mysql в фоновом режиме.В Windows 95/98 не предусмотрен запуск mysqld в виде службы.Отсутствие сообщений при этом -хороший знак,значит все в порядке.

При запуске mysqld можно указывать следующие опции:

Теперь можно попытаться войти в сервер.Для этого используется команда mysql.Изначально существует единственный пользователь,которому предоставляется право входа-root,
которая не имеет пароля.Первое,что нужно сделать-войти под именем root и установить для него пароль. Команда mysql может использовать следующие опции:

Примечание.Команды mysqld и mysql имеют еще некоторые опции,но в данный момент они особого интереса не представляют.

 mysqlbin>mysql -u root mysql Welcome to the MySQL monitor.Commands end with ; or g. Your MySQL connection id is 1 to server version: 3.23.19 Type 'help' for help. mysql>

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

 mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root'; mysql>quit mysqlbin>mysqladmin -u root reload

После установки пароля для root нужно перезагрузить сервер командой mysqladmin reload,чтобы изменения вступили в силу.После этого можно попробовать войти снова:

 mysqlbinmysql -u root -p mysql Enter password:******* 

Если вы получили приглашение mysql monitor,значит все правильно работает.Можно начинать настраивать таблицы привилегий,вводить новых пользователей,создавать базы данных и таблицы,
то есть делать все то,что называется администрированием.Обо всем этом подробнее далее.

Система привилегий и безопасность в MySQL.

• User
• Db
• Host
• Пользовательские привилегии

База данных mysql и таблицы привилегий.

Итак,вы успешно вошли в базу данных mysql,которая используется для администрирования сервера.Что же здесь находится?А находятся здесь 5 таблиц,которые ничем не отличаются от других таблий баз данных,
за исключением того,что эти таблицы используются для предоставления доступа к базам данных и таблицам в них пользователям.Рассмотрим каждую из них.

Введите следующую команду,которая покажет таблицы в базе данных mysql:

 mysql>show tables;

Кратко рассмотрим функции каждой из таблиц:

• User
  Определяет,разрешено ли пользователю,пытающемуся подключиться к серверу делать это.Содержит имя пользователя,пароль а также привилегии.Если ввести команду show columns from user;,
  то получим следующее:

  Field	Type	Null	Key	Default	Extra
  Host	char(60)		PRI
  User	char(16)		PRI
  Password	char(8)
  Select_priv	char(1)			N
  Insert_priv	char(1)			N
  Update_priv	char(1)			N
  Delete_priv	char(1)			N
  Create_priv	char(1)			N
  Drop_priv	char(1)			N
  Reload_priv	char(1)			N
  Shutdown_priv	char(1)			N
  Process_priv	char(1)			N
  File_priv	char(1)			N

  Изначально эта таблица содержит пользователя root с паролем,который вы установили и именем хоста ‘%’.По умолчанию root может входить с любого хоста,
  имеет все привилегии и доступ ко всем базам данных.Также в таблице содержится запись для пользователя ‘%’,которую нужно сразу же удалить,так как она предоставляет доступ любому пользователю.

  delete from user where user='%';

  Для добавления нового пользователя выполните следующие действия:

  insert into user (host,user,password) values ('%.domain.com','john',password('df456'); insert into user (host,user,password) values ('localhost,'mary',password('kitchen'); select host,user,password from user;           Host User Password % root 456g879k34df9 %.domain.com john 657t234d980hg6 localhost mary 234d76gh88rt9

  Пояснения:

  1.Команда insert вставляет данные в таблицу,не забывайте завершать команды ‘;’.
  2.При вводе пароля используйте функцию password(),иначе пароль работать не будет!
  3.Все пароли шифруются mysql,поэтому в поле Password вы видите абракадаьры.Это делается в целях безопасности.
  4.Не есть хорошей практикой назначать привилегии пользователям в таблице user,так как в этом случае они являются глобальными и распространяются на
  все базы данных.Предоставляйте привилегии каждому пользователю к конкретной базе данных в таблице db,которая будет рассмотрена далее.
  5.При задании имени хоста для входа через сеть рекомендуется явно указывать полное имя хоста,а не ‘%’.В приведенном выше примере юзеру john разрешается
  вход на сервер со всех машин домена domain.com.Можно также указывать IP-адреса машин и маски подсетей для большей безопасности.

• Db
  Определяет к каким базам данных каким пользователям и с каких хостов разрешен доступ.В этой таблице можно предоставлять каждому пользователю доступ к
  базам данных и назначать привилегии. Eсли выполнить команду show columns from db; получим следующее:

  Field	Type	Null		Default	Extra
  Host	char(60)		PRI
  Db	char(32)		PRI
  User	char(16)		PRI
  Select_priv	char(1)			N
  Insert_priv	char(1)			N
  Update_priv	char(1)			N
  Delete_priv	char(1)			N
  Create_priv	char(1)			N
  Drop_priv	char(1)			N

  По умолчанию,все привилегии установлены в ‘N’.Например,предоставим юзеру john доступ к базе данных library и дадим ему привилегии select,insert и update
  (описание основных команд mysql будет дано в отдельном разделе,сейчас моя цель-показать,как работают таблицы привилегий).

  insert into db (host,user,db,select_priv,insert_priv,update_priv) values ('%.domain.com','john','library','Y','Y','Y');

  Привилегии,устанавливаемые в таблице db,распространяются только на базу данных library.Если же установить эти привилегии в таблице user,
  то они будут распространяться и на другие базы данных,даже если доступ к ним и не установлен явно.

• Host
  Таблица host используется для расширения диапазона доступа в таблице db.К примеру,если доступ к какой-либо базе данных должен быть предоставлен более чем одному хосту,
  тогда следует оставить пустой колонку host в таблице db,и внести в таблицу host необходимые имена хостов. Выполним команду show columns from host;

  Field	Type	Null	Key	Default	Extra
  Host	char(60)		PRI
  Db	char(32)		PRI
  Select_priv	char(1)			N
  Insert_priv	char(1)			N
  Update_priv	char(1)			N
  Delete_priv	char(1)			N
  Create_priv	char(1)			N
  Drop_priv	char(1)			N

Как видно из таблицы,здесь также можно задавать привилегии для доступа к базе данных.Они обычно редко используются без необходимости.Все привилегии доступа нужно задавать в таблице db для каждого пользователя,
а в таблице host только перечислить имена хостов.Сервер читает все таблицы,проверяет имя пользователя,пароль,имя хоста,имя базы данных,привилегии.Если в таблице db привилегии select,
insert установлены в ‘Y’,а в таблице host в ‘N’,то в итоге юзер все равно получит ‘Y’.Чтобы не вносить путаницы,лучше назначать привилегии в таблице db.

Эти 3 таблицы являются основными.В новых версиях MySQL,начиная с 3.22 добавлены еще 2 таблицы- tables_priv и columns_priv,которые позволяют задать права доступа к определенной таблице в базе данных и даже
к определенной колонке.Они работают подобно таблице db,только ссылаются на таблицы и колонки. Также,начиная с версии 3.22 можно использовать команду GRANT для предоставления доступа к базам данных,
таблицам и колонкам таблиц,что избавляет от необходимости вручную модифицировать таблицы db,tables_priv и columns_priv.Команда GRANT будет подробно рассмотрена в следующих разделах.

Привилегии,предоставляемые MySQL.

Select-используется для извлечения информации из таблиц.Select можно также использовать не имея разрешения на доступ к какой-либо БД,например,как калькулятор.

Сервер выдаст результат.Пример использования Select:

 mysql>select fname,lname,address from customers where city='New York'; 

Insert-используется для вставки информации в таблицу.Например:

 mysql>insert into data2 (first_col,third_col,sixth_col) values ('Bob','Smith','bob@fdrt.net');

Update-позволяет обновлять информацию в таблицах.Например:

 mysql>update numbers set field3='129867' where field5='567483'; 

Delete-используется для удаления информации из таблиц.

 mysql>delete from indexes where u_id='2201';

Alter-используется для модификации таблиц:переименования таблицы,добавления колонок,изменения колонок,удаления колонок.Примеры:

 mysql>alter table wind rename newwind; mysql>alter table newwind add master_con varchar(20); mysql>alter table newwind change retail wholesale varchar(60); mysql>alter table newwind drop field021; 

Create-позволяет создавать базы данных и таблицы в них.Примеры:

 mysql>mysqladmin -u john -ptest create mydb //Создает пустую БД. mysql>use mydb;
 Database changed. create table monitoring ( id int(5) not null auto_increment, first_name varchar(15) not null, last_name varchar(20) not null, address varchar(80) not null, primary key (id), key(last_name)); 

Drop-ипользуется для удаления таблиц и баз данных.

Grant-позволяет пользователю предоставлять другим пользователям привилегии,которыми он сам обладает.Два пользователя с различными привилегиями и привилегий GRANT могут комбинировать свои разрешения.

Index-позволяет пользователю создавать и удалять индексы.

File-пользователь,обладающий этим правом,может выполнять команды LOAD DATA INFILE и SELECT…INTO OUTFILE и может читать и записывать любой файл на сервере MySQL.

Последние 3 привилегии используются для администрирования сервера из команды mysqladmin.

Специальные пользователи MySQL.

• montyСуперпользователь.Может входить на сервер как с локального хоста,так и с любого хоста в сети.Имеет доступ ко всем базам данных и все привилегии,
  но если root может в первый раз войти без пароля,monty должен использовать пароль и должен быть добавлен в таблицы привилегий вручную.

  mysql>mysql -u root -p mysql Enter password:****** mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('password'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); mysql> INSERT INTO user VALUES('%','monty',PASSWORD('password'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); или mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'password' WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'password' WITH GRANT OPTION;

  Следует заметить,что следует внести данные как для localhost,так и для ‘%’,то есть любого хоста в сети.

• adminПользователь,который может входить на сервер с локального хоста без пароля и которому назначаются административные привилегии Reload и Process.
  Пользователь admin может использовать команды mysqladmin reload, mysqladmin refresh и mysqladmin flush-*,а также mysqladmin processlist.

  mysql>mysql -u root -p mysql Enter password:****** mysql> INSERT INTO user SET Host='localhost',User='admin', Reload_priv='Y', Process_priv='Y'; или mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;

  Доступа к базам данных admin не имеет.Привилегии для доступа к определенным БД должны быть назначены индивидуально-или в таблице db,или командой GRANT.

• dummyПользователь,который может входить на сервер без пароля ,но только с локального хоста.Все глобальные привилегии устанавливаются в ‘N’ кроме “USAGE”,
  что позволяет dummy создавать пользователей без привилегий.Права доступа к базам данных должны быть заданы root’ом.

mysql>mysql -u root -p mysql Enter password:****** mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','dummy',''); или mysql> GRANT USAGE ON *.* TO dummy@localhost;

Создание и модификация баз данных и таблиц в MySQL.

• Создание баз данных
• Типы данных в MySQL
• Создание и модификация таблиц
  • Команда LOAD DATA INFILE
  • Команды модификации таблиц

Создание баз данных.

Создание базы данных в MySQL производится с помощью утилиты mysqladmin.Изначально существует только БД mysql для администратора и БД test
,в которую может войти любой пользователь и которая по умолчанию пуста.Приведенный ниже пример иллюстрирует создание базы данных.

 mysqlbin>mysqladmin -u root -p create data1 Enter password:****** Database "data1" created. mysqlbin>

По умолчанию,root имеет доступ ко всем базам данных и таблицам.Перейти в созданную базу данных можно,используя команду mysql.

 mysqlbin>mysql -u root -p data1 Enter password:****** Welcome to MySQL monitor.

Или,находясь в другой базе данных,например в mysql ввести команду:

 mysql>use data1 Database changed.

Теперь можно создавать таблицы и вводить информацию.

Типы данных в MySQL.

Прежде чем создавать таблицы,необходимо ознакомиться с тем,какие типы данных поддерживает MySQL.Все они представлены в нижеследующей таблице:

Создание и модификация таблиц.

Cоздадим таблицу customers в БД data1:

 mysql>use data1 Database changed. mysql>create table customers ( emp_id int(4) not null auto_increment, emp_name varchar(10) not null, emp_lname varchar(15) not null, address varchar(60) not null, phone int(10), primary key(emp_id)); 

Мы создали пустую таблицу.Вводить данные в нее можно несколькими способами:
а)вручную,используя команду insert into;
б)загрузить данные из текстового файла,что является более предпочтительным,особенно если нужно ввести несколько тысяч записей.Синтаксис этой команды будет описан позже.
в)использовать утилиту mysqlimport также для загрузки данных из текстового файла.

Пример ввода данных вручную:

 mysql>insert into customers (emp_id,emp_name,emp_lname,address,phone) -->values ('1001','John','Walker','New York','1236458794'); или mysql>
insert into customers values ('1001','John','Walker','New York','1236458794'); //работает и то,и то. 

Что касается auto_increment в столбце emp_id,это означает,что числовое значение этого столбца будет автоматически увеличиваться на
единицу с каждой новой записью.То есть,если мы ввели значение 1001,то следующее будет 1002 и т.д.Значение в такой столбец вводится один раз для задания точки отсчета,а дальше сервер будет сам подставлять нужные значения.

Синтаксис команды LOAD DATA INFILE.

DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY 't'] [OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '' ]] [LINES TERMINATED BY 'n'] [IGNORE number LINES] [(col_name,...)]

Предположим,существует некоторый текстовый файл 123.txt,содержащий 2000 записей,которые нужно внести в таблицу.Нужно создать таблицу,имеющую такую же структуру
и такое же число полей,как и файл(а также подходящие типы данных).Предположим,что поля в файле разделены запятыми.Кроме того,файл должен находиться в нужной базе данных. Вводим следующую команду:

LOAD DATA INFILE ‘123.txt’ into table customers fields terminated by ‘,’;

Это все.Данные из файла помещаются в таблицу.

Для модификации таблиц и данных в них используются команды update и alter table. Рассмотрим их действие на примере таблицы customers:

Пример действия команды alter table:

 mysql>alter table customers add last_accessed date; mysql>insert into customers (last_accessed) values ('20000512') where -->emp_id='1003';
mysql>alter table customers rename customers buyers; mysql>alter table buyers change phone telephone int(11); 

После выполнения этих команд таблица примет следующий вид:

Можете проверить это сами

Изменение данных в таблицах производится с помощью команды update.Для примера возьмем ту же таблицу buyers.

 mysql>update buyers set address='Seattle' where emp_lname='Smith'; 

Для модификации таблиц используются также команды Drop и Delete.

Delete-удаляет строку из таблицы.Например,если ввести команду
delete from buyers where emp_id=’1002′

будет удалена строка для Bill Smith.

Drop-если используется в Alter table удаляет колонку из таблицы.

Команда Drop также используется для удаления таблиц и баз данных.Пользоваться ею следует осторожно,так как любое удаление необратимо,восстановить данные можно только из резервной копии.

Общие проблемы и их разрешение.

После этого при попытке войти в базу данных он получает сообщение Access denied.Такой пароль не будет прочитан.Правильно нужно ввести:

update user set password=password('mamamia') where user='root';

Это очень важный шаг и необходимо быть внимательным.Единственный выход при этом в Windows-деинсталлировать сервер и установить по новой,благо еще ничего не настроено.

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

В таблицах содержится информация для юзера jerry.Как можно видеть,jerry может входить по сети с любого компьютера домена domain.com,введя свое имя и пароль.Сервер читает таблицу User,
определяет соответствие имени и пароля,затем таблицу Db,в которой указано,к каким базам данных разрешен доступ.Если все правильно,jerry получает доступ к базе данных data1.В этой БД jerry
может просматривать информацию в таблицах,помещать туда новые данные,изменять содержимое строк и удалять строки.Если же он захочет создать новую таблицу,он получит сообщение Access denied,
поскольку у него нет разрешения создавать таблицы.

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

select * from data2.authors;

он получит доступ к этой таблице,хотя в таблице Db не назначен доступ к базе data2.Поэтому,без особой необходимости не нужно предоставлять пользователям привилегии в таблице User,
а делать это в таблице Db для каждой конкретной базы данных.

Предположим,что jerry временно работает в другом отделе,который находится в другом домене,и когда он пытается получить доступ к базе данных,он получает Access denied,поскольку компьютерам
этого домена не разрешен доступ к базе данных.В данном случае нужно поступить следующим образом:очистить поле Host в таблице Db,а в таблицу Host занести следующие данные:

update db set host='' where user='jerry';
insert into host (host,db) values ('%.domain.com','data1');
insert into host (host,db) values ('monster.domain2.com','data1');

Привилегии в таблице Host указывать не нужно.Главное,чтобы данные в таблицах привилегий нигде не дублировались,чтобы не возникало противоречий.Данные для пользователя-имя и пароль,
должны назначаться в таблице User,привилегии-в таблице Db,хосты,с которых разрешен доступ,в таблице Host.Если все внимательно вводить,проблем возникать не должно.

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

GRANT select,insert update,delete on data1.telephone to jerry@%.domain.com identified by 'password';

С помощью этой команды можно указывать привилегии на доступ даже к определенным столбцам таблицы:

GRANT select(id,name,address,phone),update(address,phone) on data2.customers to jerry@%.domain.com identified by 'password';

Применение этой команды автоматически помещает данные в таблицы Db,Tables_priv и Column_priv,что избавляет от необходимости их ручной модификации.

Если нужно отнять у пользователя какую-либо привилегию,используйте команду REVOKE.

REVOKE update(address,phone) on data2.customers from jerry@%.domain.com;

Запросы к базе данных и команда Select.

SELECT [STRAIGHT_JOIN] [DISTINCT | ALL] select_expression,... [FROM tables... [WHERE where_definition] [GROUP BY column,...] [ORDER BY column [ASC | DESC], ...]
HAVING full_where_definition [LIMIT [offset,] rows] [PROCEDURE procedure_name]] [INTO OUTFILE 'file_name'... ]

Как видно из вышеприведенного,вместе с командой Select используются ключевые слова,использование которых очень влияет на ответ сервера.Рассмотрим каждое из них.

• DISTINCT..Пропускает строки,в которых все выбранные поля идентичны,то есть устраняет дублирование данных.
• WHERE.Предложение команды Select,которое позволяет устанавливать предикаты,условие которых может быть верным или неверным для любой строки таблицы.
  Извлекаются только те строки,для которых такое утверждение верно.Например:SELECT u_id,lname from publishers WHERE city ='New York'; Выводит колонки u_id и lname из таблицы publishers для которых значение в столбце city-New York.Это дает возможность сделать запрос более конкретным.
• Реляционные операторы.Реляционный оператор – математический символ который указывает на определенный тип сравнения между двумя значениями. Реляционные операторы которыми располагает MySQL :

  = Равнo > Больше < Меньше >= Больше или равно <= Меньше или равно < > Не равно

  Эти операторы имеют стандартные значения для числовых значений.

  Предположим что вы хотите увидеть всех заказчиков с оценкой(rating) выше 200. Так как 200 – это скалярное значение, как и значение в столбце оценки, для их сравнения вы можете использовать реляционный оператор.

  SELECT * FROM Customers WHERE rating > 200;

• Булевы операторы.Основные Булевы операторы также распознаются в MySQL. Выражения Буля – являются или верными или неверными, подобно предикатам.
  Булевы операторы связывают одно или более верных/неверных значений и производят единственное верное или неверное значение. Стандартными операторами Буля распознаваемыми в SQL являются:AND,OR и NOT.Предположим вы хотите видеть всех заказчиков в Далласе,которые имеют рейтинг выше 200:SELECT * FROM Customers WHERE city = 'Dallas' AND rating > 200; При использовании оператора AND,должны быть выполнены оба условия,то есть должны быть выбраны все заказчики из Далласа,рейтинг которых больше 200.

  При использовании оператора OR,должно выполниться одно из условий.Например:

  SELECT * FROM Customers WHERE city = 'Dallas ' OR rating > 200;

  В данном случае будут выбраны все заказчики из Далласа и все имеющие рейтинг больше 200,даже если они и не из Далласа.

  NOT может использоваться для инвертирования значений Буля.Пример запроса с NOT:

  SELECT * FROM Customers WHERE city = 'Dallas' OR NOT rating > 200;

  При таком запросе будут в

Написана на C и C++. Базовая платформа: Solaris 2.7-2.8, SuSE Linux 7.1 (ядро 2.4, ReiserFS), но работает также в AIX, BSDI, DEC Unix, FreeBSD, HP-UX, Linux 2.0, Mac OS X, NetBSD, OpenBSD, OS/2, SGI Irix, SunOS, SCO OpenServer, SCO UnixWare, Tru64, Win9x, NT, Win2000.

Многопотоковая. Первоначально мимикрировала под mSQL. API для C, C++, Java, Eiffel, Perl, PHP, Python, Tcl. ODBC. Парольная защита (пароли шифруются перед пересылке, это, однако, не увеличивает безопасность).

Таблицы в виде B-tree со сжатием индекса. До 32 индексов на таблицу. До 16 колонок на индекс. Длина индекса до 500 байт. Таблицы в памяти. Записи переменной длины. Есть примеры использования MySQL с 60000 таблиц и 5 миллиардами строк. Отсутствует memory leak (проверено Purify). Поддержка koi8-r и cp1251 (сортировка, сравнение и т.д.). Клиенты могут соединяться по TCP/IP (можно использовать только, если никто не подслушивает) или Unix socket. Можно встраивать в свои программы.

Стабильность подсистем: ISAM – стабильная, MyISAM – gamma, C API – стабильная (буфер до 16МБ), mysql(admin,show,dump,import) – стабильные, Basic SQL – стабильная, оптимизатор – стабильная, блокировка (одновременный доступ нескольких процессов, не клиентов) – gamma (проблемы в Linux, рекомендуется –skip-locking), нити в Linux – рекомендуется –skip-locking и использовать не более 1000 одновременных соединений, DBD – стабильная, MyODBC – gamma, репликация – бета/gamma, BDB – бета (транзакции), автоматическое восстановление MyISAM – бета, слияние таблиц – бета/gamma, INNODB – альфа (транзакции с блокировкой на уровне строк), полнотекстовый поиск – бета.

Расширения к ANSI SQL92:

• типы полей MEDIUMINT, SET, ENUM и различные модификации BLOB и TEXT
• атрибуты полей: AUTO_INCREMENT, BINARY, NULL, UNSIGNED и ZEROFILL
• по умолчанию строки сравниваются независимо от регистра
• ключевые слова TEMPORARY и IF NOT EXISTS при создании/удалении таблиц
• ключ DELAYED при создании/замене строк
• ключ LOW_PRIORITY при манипуляции со строками
• SHOW
• строки можно заключать не только в апострофы, но и в кавычки
• SET OPTION
• синонимы операторов OR (||) и AND (&&) и MOD (%)
• LAST_INSERT_ID()
• REGEXP
• IT_COUNT(), CASE, ELT(), FROM_DAYS(), FORMAT(), IF(), PASSWORD(), ENCRYPT(), md5(), ENCODE(), DECODE(), PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(), or WEEKDAY()
• REPLACE вместо DELETE + INSERT
• присвоение значений переменным в выражениях
• комментарии в стиле C и sh
• множество других мелких улучшений и несовместимостей, которые не позволят Вам “соскочить” с MySQL на другую СУБД

Отсутствующие возможности ANSI SQL92:

• sub-select (в руководстве приводятся примеры как обойтись без него)
• хранимые процедуры и тригеры (тригеры не планируются совсем)
• FOREIGN KEY
• views

Установка и настройка (Linux)
Опции ./configure
mysql.server (запуск MySQL при загрузке копьютера)
safe_mysqld (надстройка над mysqld)
mysqld (сервер БД)
Разбор параметров и конфигурационных файлов
Типы таблиц (методы доступа)
Права доступа и привилегии
Базовый синтаксис
Типы данных (в колонках)
Операторы SQL
Функции для SELECT и WHERE
Отличия версий

• иметь gcc лучше, чем 2.8.1 (egcc 1.0.2), рекомендуется 2.95.2
• создать директорию для сборки, распаковать в нее mysql-3.23.37.tar.gz (взять на http://www.mysql.ru/download)
• для версии ранее 3.23.34 распаковать туда db-3.2.3h.tar.gz (это специальная версия bdb для MySQL)
• создать группу mysql
• создать пользователя mysql (в группе mysql) (зачем ему bash?)
• ./configure –prefix=/usr/local/mysql –localstatedir=/usr/local/mysql/data –with-unix-socket-path=путь –with-mysqld-user=mysql –disable-large-files –with-libwrap –without-debug –with-charset=cp1251 –with-extra-charsets=all –with-berkeley-db
• make (70MB/91MB)
• если upgrade, то остановить mysql, сохранить базы данных и my.cfg (не забыть потом удалить!)
• make install (как root)(16 MB, из них 5МБ – тест)
  • /usr/local/mysql/include/mysql
  • /usr/local/mysql/info
  • /usr/local/mysql/man
  • /usr/local/mysql/lib/mysql (libdbug, libheap, libmerge, libmyisam, libmyisammrg, libmysqlclient, libmystrings, libmysys, libnisam); эту директорию указывать для libtool, либо занести в /etc/ld.so.conf
  • /usr/local/mysql/bin (comp_err, isamchk, isamlog, my_print_defaults, myisamchk, myisamlog, myisampack, mysql, mysqladmin, mysqlbinlog, mysqlshow, mysqldump, mysqlimport, mysqltest, pack_isam, perror, replace, resolve_stack_dump, resolveip, скрипты )
  • /usr/local/mysql/share/mysql (сообщения об ошибках – koi8-r, таблицы кодировок – cp1251, koi8-r; mi_test_all; mi_test_all.res)
  • /usr/local/mysql/libexec (mysqld)
  • /usr/local/mysql/sql-bench
  • /usr/local/mysql/mysql-test
  • /usr/local/mysql/data (базы данных, конфигурация, журналы, pid-файл)
• при первой установке: scripts/mysql_install_db (как root – создание таблиц с правами доступа, дает все права пользователю root без пароля и позволяет делать все с базами test и test_*, кроме раздачи привилегий)
• chown -R root:mysql /usr/local/mysql (как root)
• chown -R mysql /usr/local/mysql/data (и отдельную директорию для mysql.sockets с правами чтения для всех; mysql не нужны права на запись для my.cnf)
• support-files/mysql.server в /etc/rc.d/init.d для автоматического запуска и дать ему права на исполнение и сделать линк K00mysql из rc0.d и rc6.d на него, S99mysql из rc2.d, rc3.d и rc5.d на него
• скопировать my-medium.cnf в /usr/local/mysql/data/my.cnf и слегка отредактировать [mysqld]
  • socket=имя-файла под Unix-socket (и в раздел [client] тоже)
  • skip-locking (не блокировать доступ к данным от ДРУГИХ процессов)
  • log-bin #журнал изменений для репликации
  • log-slow-queries
  • log-update #журнал изменений
  • #secure – говорит, что нет такой опции
  • skip-networking #если не нужен доступ по TCP/IP (а еще лучше использовать ssh + port forward)
  • safe-show-database
  • skip-show-database
  • server-id=1 # что это?
  • куча set-variable из исходного файла
  • set-variable = max_connections=500
• тестовый запуск: /usr/local/mysql/bin/safe_mysqld –user=mysql (как root)(или сразу /etc/rc.d/rc3.d/S99mysql start)
• /usr/local/mysql/bin/mysqladmin -u root -p password ‘пароль’ (при запросе пароля нажать Enter)
• /usr/local/mysql/bin/mysqladmin -u root -h localhost.localdomain -p password ‘пароль’ (надо ли?)
• установка интерфейса с Perl
  • взять модули Data-Dumper, DBI и Msql-Mysql-modules
  • каждый распаковать в отдельную директорию (Msql-Mysql-modules последним)
  • зайти в нее
  • perl Makefile.PL (опционально хочет RPC::PlServer, RPC::PlClient, Storable, Net::Daemon).
  • make
  • make test (mysqld должен работать)
  • make install (как root)
• тестирование
  • зайти в sql-bench
  • ./run-all-tests –user=test (нужны права для записи в директорию output, час времени и 200 МБ на диске)(connect/disconnect временами грохает mysqld!)
  • можно удалить sql-bench и mysql-test
• настроить права доступа (как минимум, убрать анонимный доступ)

• –prefix=куда-устанавливать (множество мелочных опций по установке)
• –enable-maintainer-mode [no]
• –enable-shared (делать разделяемые библиотеки)[yes]
• –enable-static (10% быстрее)[yes]
• –with-mit-threads (для linux 2.2 не надо)
• –with-pthread (для linx 2.2 не надо)
• –with-named-thread-libs=где
• –with-named-curses-libs=где
• –with-named-z-libs=где
• –enable-thread-safe-client (если клиентская программа использует потоки)
• –enable-assembler
• –with-raid
• –with-unix-socket-path=куда-класть-unix-socket
• –with-tcp-port=порт [3306]
• –with-mysqld-user=имя-пользователя-для-mysqld
• –disable-large-files
• –with-libwrap
• –without-debug (15% быстрее)
• –without-server
• –without-docs
• –without-bench
• –without-readline (использовать системный readline вместо встроенного)
• –with-charset=кодировка-по-умолчанию (cp1251, koi8_ru, latin1, …)
• –with-extra-charsets=список-дополнительных-кодировок (включая – none, complex, all)
• –with-berkeley-db
• –with-innodb
• –with-gemini (Gemini DB)

Скрипт предназначен для /etc/rc.d/init.d (и линки в 0,2,3,5,6 не забыть). Соответственно первый параметр start или stop (посылается сигнал процессу, вместо выполнения “mysqladmin shutdown”). Далее обработка опций из конфигурационного файла (группы [mysqld] и [mysql_server]) и командной строки (какая командная строка в скрипте из rc.d?!). Ищет и запускает safe_mysqld. Если нужны какие-либо специфические опции safe_mysqld, то их можно указать здесь. Секция [mysql.server] в конфигурационном файле /etc/my.cnf может содержать установку переменных:

• user
• datadir
• basedir
• bindir
• pid-file

Скрипт запускает mysqld и перезапускает его ежели тот помрет. Проверяет, что не был запущен ранее. Грохает зависшие процессы. Запускать под root. Пытается определить где что лежит (понимает только стандартные конфигурации, иначе надо запускать из BASEDIR). Собирает опции из секций mysqld, server и safe_mysqld конфигурацинных файлов и командной строки (м.б. модифицированных опциями –no-defaults, –defaults-file=имя и –defaults-extra-file=имя). Передает их (”улучшив” некоторые из них) mysqld. Свои опции:

• –err-log=имя
• –open-files=для-установки-ulimit-на-открытые-файлы
• –open-files-limit=
• –core-file-size=для-установки-ulimit-на-coresize
• –timezone=установить-TZ

Есть закоментированные проверки всех таблиц. Пользователь по умолчанию – mysql. Пытается запустить mysqld под “nice nohup”.

Вместо ключей запуска можно использовать конфигурационные файлы.

• –ansi (бОльшая совместимость с ANSI SQL)
• –basedir=путь
• –bdb-lock-detect=[DEFAULT | OLDEST | RANDOM | YOUNGEST | число-секунд]
• –bdb-logdir=директория
• –bdb-no-sync
• –bdb-no-recover
• –bdb-shared-data (запускать Berkeley DB в режиме разделения с другими процессами)
• –bdb-tmpdir=директория
• –bind-address=IP-адрес (для хостов с несколькими адресами)
• –chroot=путь (умеет?!)
• –core-file
• –datadir=путь
• –default-character-set=кодировка (сменил кодировку – перестрой индексы)
• –default-table-type=метод-хранения [MyISAM]
• –defaults-extra-file=дополнительно-к-my.cnf
• –defaults-file=имя-файла-параметров (вместо my.cnf)
• –enable-locking (внешняя блокировка – если нужна блокировка нескольких серверов или myisamchk к одной базе данных; работает неустойчиво и медленно)
• –flush (сбрасывать буфера на диск после каждой SQL-команды
• –help (выдает список команд; настройки, выбранные при сборке с учетом текущих параметров и значения переменных)
• –init-file=имя-файла (при запуске читать команды из файла)
• –language (язык сообщений об ошибках)
• –log[=имя-файла] (записывать все соединения и команды в журнал)
• –log-bin[=имя-файла] (записывать журнал в новом двоичном формате для репликации)
• –log-bin-index=имя-файла (хранит имена последних бинарных журналов)
• –log-slow-queries[=имя-файла] (записывать все слишком медленные – занимающие более long_query_time секунд – команды в журнал)
• –log-update[=имя-файла] (записывать изменения в журнал)
• –log-long-format (дополнительную информацию в журнал изменений)
• –low-priority-updates (INSERT/DELETE/UPDATE будут иметь меньший приоритет, чем SELECT)
• –memlock (не свопировать mysqld)
• –myisam-recover[=опция...] (проверка корректности myisam таблиц и способ восстановления):
  • DEFAULT
  • BACKUP (делать резервную копию при исправлениях – .BAK)
  • FORCE (исправлять даже если это приведет к потере многих данных)
  • QUICK
• –new (использовать новые, т.е. недоотлаженные возможности)
• –no-defaults
• –port=TCP-порт
• -O имя=значение (установить переменную, список по –help)
• –safe-mode (уменьшить уровень оптимизации)
• –safe-show-database (не показывать имена БД пользователям, которые не имеют к ним никаких прав)(или нет такой опции? в списке ее нет, но не ругается)
• –secure (двойная проверка IP-адресов – есть ли такая опция? если ее указать, то не стартует. Или она теперь по умолчанию? Если нет, то именами хостов вообще нельзя пользоваться!)
• –skip-bdb
• –skip-delay-key-write (отключить буферизацию записи ключей)
• –skip-grant-tables (отключить проверку прав доступа)
• –skip-locking (не использовать внешнюю блокировку; safe_mysqld использует этот ключ; ключ применяется, если не нужна блокировка нескольких серверов или myisamchk к одной базе данных; все равно внешняя блокировка работает неустойчиво и медленно; команда LOCK работает даже с этим ключом)
• –skip-name-resolve (в таблицах прав доступа д.б. только IP)
• –skip-networking (не слушать TCP/IP, только через unix socket)
• –skip-new (не использовать новые, т.е. не доотлаженные возможности)
• –skip-show-database (не позволять команду SHOW DATABASE, если пользователь не имеет привилегии работы с процессами. В другом месте говорится, что вообще ничего не показывать – но это неправда)
• –skip-thread-priority (ускоряет ответ)
• –socket=путь
• –tmpdir=путь (вместо /tmp)
• –user=имя-пользователя (обязательно, если запускается из под root)

Опции берутся в следующем порядке (последующие затирают предыдущие):

• переменные окружения
• /etc/my.cnf
• /usr/local/mysql/data/my.cnf (нельзя поменять с помощью –datadir)
• из файла, указанного ключом –defaults-extra-file
• ~/.my.cnf
• опции в командной строке

Действуют на mysql, mysqld, mysqladmin, mysqlimport, mysqldump, myisamchk, myisampack, mysql.server. Файл делится на секции. Секция начинается со строки, в которой указано имя секции в квадратных скобках. Имя секции соответствует имени программы на которую она действует. Любая длинная опция программы (начинающаяся с двух минусов, запустите программу с ключом –help) может быть использована здесь (минусы надо отбросить). Секция с именем client позволяет задать опции (например, пароль , общие для всех клиентских программ. Переменные устанавливаются строкой:
set-variable = имя=значение.

MySQL позволяет использовать для хранения данных различные типы таблиц (методы доступа). Меняется командой ALTER TABLE, описание хранится в файле .frm.

• MyISAM (по умолчанию, см. –default-table-type). Индекс – .MYI, данные – .MYD. Восстановление – myisamchk. Бывают таблицы с записями фиксированной длины, переменной длины (BLOB, VARCHAR, TEXT) и сжатые (myisampack – только чтение). Машинонезависимый формат хранения (хотя формат плавающей точки должен совпадать на этих архитектурах).
• MERGE – виртуальное слияние таблиц одинакового формата (SELECT, UPDATE и DELETE). Список в файле .MRG.
• ISAM. Предлагается больше не использовать. Индекс – .ISI, данные – .ISD. Индексирование – B-tree. Восстановление – isamchk. Зависит не только от архитектуры, но и от ОС.
• HEAP – таблицы в памяти с хешированием. Записи только фиксированной длины.
• BDB (с поддержкой транзакций). Надежнее (автоматическое восстановление). Транзакции (BEGIN/COMMIT/ROLLBACK). Дольше, больше места на диске и в памяти.
• InnoDB – в состоянии alfa

Аутентификация пользователя производится по имени (до 16 символов), паролю (м.б. пустым) и хосту или его IP. Большинство клиентских программ по умолчанию используют mysql-имя, совпадающее с unix-именем, но это можно изменить с помощью ключа –user=. Пароль можно задать:

• прямо в командной строке после ключа -p (без пробела, очень опасно)
• указав ключ -p без пароля (программа запросит пароль с клавиатуры, наиболее безопасно)
• в файле .my.cnf (права к этому файлу должны быть только у собственника), секция [client], поля host, user и password
• с помощью переменной окружения MYSQL_PWD (очень опасно) и MYSQL_HOST

Вся информация о правах хранится в БД с именем mysql. Никто не должен иметь к ней доступа на чтение (см. про пароли).

Используются таблицы:

• user (используется чтобы понять – пускать/не пускать; данные здесь права действуют на все БД; административные привилегии и операции с файлами определяются только здесь)
  • Host: CHAR(60),PRI, нечувствителен к регистру. Может содержать имя хоста, IP адрес или localhost. Можно использовать шаблоны с символами ‘%’ (любое количество любых символов) и ‘_’ (любой символ). Пустое поле означает, что производится логическое “И” привилегий в данной строке и привилегий в соответствующей строке таблицы host. При использовании IP-адреса можно задавать сетевую маску (в виде – /255.255.255.0 или /24).
  • User: CHAR(16),PRI. Пустое поле соответствует любому имени, в данном случае пользователь рассматривается как анонимный и предоставленное им имя заменяется на пустое для дальнейших проверок прав доступа.
  • Password: CHAR(16), зашифрован, но не так как в Unix, знание даже зашифрованного пароля позволяет выдать себя за данного пользователя – пароль шифруется на стороне клиента! Может быть пустым – пользователь также должен предъявить пустой пароль.
  • привилегии: enum(’N',’Y'), по умолчанию – ‘N’
    • select – выборка из строк таблицы
    • insert – вставка строки в таблицу
    • update – изменение строки таблицы
    • delete – удаление строки таблицы
    • create – создавать БД/таблицу
    • drop – удалять БД/таблицу
    • reload – позволяет выполнять административные команды (reload, refresh, flush-*)
    • shutdown – позволяет остановить mysqld
    • process – позволяет выполнить processlist (можно смотреть текст команд, выполняемых другими пользователями (в т.ч. SET PASSWORD)), kill
    • file – читать файлы (LOAD DATA INFILE) и писать файлы (SELECT … INTO OUTFILE), с точки зрения Unix используются права, с которыми запущен mysqld (в частности, он может прочитать /etc/passwd, любую БД и т.п., записать файл в /tmp и т.п., но не может переписать уже существующий файл)
    • grant – передавать свои привилегии другим, два пользователя с различными привилегиями могут объединить их
    • references – не реализовано
    • index – создавать/удалять индексы таблицы
    • alter – изменять формат таблицы, в том числе переименовать ее, что позволяет обмануть систему прав доступа
• host
  • Host: CHAR(60),PRI. ‘%’ или пустое поле означает – любой хост.
  • DB: CHAR(64),PRI. ‘%’ или пустое поле означает – любая БД.
  • привилегии: enum(’N',’Y'), по умолчанию – ‘N’
    • select
    • insert
    • update
    • delete
    • create
    • drop
    • grant
    • references
    • index
    • alter
• db
  • Host: CHAR(60),PRI. Строка ‘%’ означает – при доступе с любого хоста. Пустое поле означает необходимость посмотреть в таблицу host.
  • DB: CHAR(64),PRI. ‘%’ или пустое поле означает – любая БД.
  • User: CHAR(16),PRI. Пустое поле – анонимный пользователь.
  • привилегии: enum(’N',’Y'), по умолчанию – ‘N’
    • select
    • insert
    • update
    • delete
    • create
    • drop
    • grant
    • references
    • index
    • alter
• tables_priv
  • Host: CHAR(60),PRI. ‘%’ или пустое поле означает – любой хост.
  • DB: CHAR(60),PRI. Не м.б. пустым или содержать шаблоны.
  • User: CHAR(16),PRI. Пустое поле – анонимный пользователь.
  • Table_name: CHAR(60)(или 64?),PRI. Не м.б. пустым или содержать шаблоны.
  • Grantor: CHAR(77)
  • Timestamp: timestamp(14)
  • Table_priv: set(’Select’,'Insert’,'Update’,'Delete’,'Create’,'Drop’,'Grant’,'References’,'Index’,'Alter’)
  • Column_priv: set(’Select’,'Insert’,'Update’,'References’)
• culumns_priv
  • Host: CHAR(60),PRI. ‘%’ или пустое поле означает – любой хост.
  • DB: CHAR(60),PRI. Не м.б. пустым или содержать шаблоны.
  • User: CHAR(16),PRI. Пустое поле – анонимный пользователь.
  • Table_name: CHAR(64)(или 60?),PRI. Не м.б. пустым или содержать шаблоны.
  • Column_name: CHAR(64)(или 60?),PRI. Не м.б. пустым или содержать шаблоны.
  • Timestamp: timestamp(14)
  • Column_priv: set(’Select’,'Insert’,'Update’,'References’)

Проверка права на подсоединение к серверу: mysql-клиент предъявляет имя пользователя, сервер определяет имя (или IP) хоста клиента (или localhost для обращения через unix-socket). По данной паре (адрес/имя) ищется строка в таблице user. Предварительно таблица сортируется по полям (Host/User) так, что наиболее специфичные строки оказываются первыми, наименее специфичные – последними. Если строка не найдена, то соединение отвергается. Если – найдена, то сверяется пароль.

Проверка прав при исполнении каждого запроса: таблица db сортируется по полям Host, Db и User, таблица host по полям Host и Db, таблицы tables_priv и columns_priv по полям Host, Db и User от наиболее специфичного к наименее. Для административных запросов и доступа к файлам проверяется только таблица user. Для прочих запросов в начале проверяется таблица user – а нет ли у данного пользователя прав доступа на “глобальном” уровне. Если есть – операция разрешается. Если нет, то проверяются права доступа к конкретной БД с конкретного хоста (по пересечению таблиц Db и Host с учетом шаблонов и пустых полей). Если их достаточно, то доступ дается. Если недостаточно, то к объединению “глобальных” прав и прав БД/хост добавляются права, извлеченные из таблиц tables_priv и columns_priv. Если и этого не хватает, то увы…

Права доступа читаются mysqld (и не читаются при “ручном” изменении БД mysql):

• при запуске
• при выполнении команд GRANT, REVOKE и SET PASSWORD
• при выполнении команды FLUSH PRIVILEGES
• при выполнении mysqladmin flush-privileges/reload

“Глобальные” привилегии и изменения пароля вступают в силу только при следующем соединении. Изменения в доступе к БД – при следующей команде use. Изменения в доступе к таблицам и колонкам – при следующем запросе.

Все команды, введенные в клиенте mysql (а в других?) попадают в .mysql_history (того unix-пользователя, который запускал mysql). А также в различные журналы сервера и в выдачу команды SHOW PROCESSLIST.

Изменение пароля: set password for имя=password(’новый пароль’).

GRANT тип-привилегии [(список-столбцов)] [, тип-привилегии [(список-столбцов)] …] ON { имя-таблицы | * | *.* | имя-БД.* } TO имя-пользователя [ IDENTIFIED BY 'пароль' ] [, имя-пользователя [ IDENTIFIED BY 'пароль ] … ] WITH GRANT OPTION

REVOKE тип-привилегии [(список-столбцов)] [, тип-привилегии [(список-столбцов)] …] ON { имя-таблицы | * | *.* | имя-БД.* } FROM имя-пользователя [, имя-пользователя ... ]

Типы привилегий:

• ALL PRIVILEGES (aka ALL)
• ALTER
• CREATE
• DELETE
• DROP
• FILE
• GRANT OPTION (только для REVOKE)
• INDEX
• INSERT
• PROCESS
• REFERENCES (не реализовано)
• RELOAD
• SELECT
• SHUTDOWN
• UPDATE
• USAGE (никаких – просто место застолбить)

Для столбцов можно задавать только INSERT, SELECT и UPDATE. Для таблиц – INSERT, SELECT, UPDATE, CREATE, DROP, DELETE, GRANT, INDEX, ALTER. Использование “*.*” означает задание глобальных привилегий. Использование “*” означает задание привилегий для текущей БД (если текущей БД нет, то глобальные).

SHOW GRANTS FOR имя@хост;

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

Для упрощения жизни можно использовать утилиты mysqladmin и mysqlaccess, xmysqladmin, mysql_webadmin из директории Contrib.

Посмотрев на это безобразие, ORACLE может перестать волноваться Зря они различают пользователей с одним именем, пришедших с разных хостов (наверное, когда-то mysql-имя совпадало с unix-именем и этой информации м.б. доверять . Не говоря об отсутствии шифровки при передаче данных по сети и возможность подсовывать перехваченный зашифрованный пароль

Не храните пароли и тому подобную информацию в БД в открытом виде.

Константы

• строка (в апострофах или кавычках, обратная косая черта как escape-символ)
• целое число (64 бита)
• вещественное число
• шестнадцатеричное число: в численном контексте как целое (64-бит), в строковом контексте пара 16-ричных цифр преобрацуется в символ
• NULL (N при экспорте/импорте)

Имена

• БД (имя файла до 64 символов, кроме ‘/’ и точки, в некоторых случаях до 60 символов)
• таблица (имя файла до 64 символов, кроме ‘/’ и ‘.’)
• колонка (до 64 любых символов). Можно ссылаться:
  • col_name
  • tbl_name.col_name
  • db_name.tbl_name.col_name
• алиас (до 255 любых символов)

Если в имени есть спецсимволы или оно совпадает с зарегистрированным словом, то его надо заключать в апострофы. Нельзя использовать символы 0×00 или 0xFF (привет маленькому “я” в cp1251!), апостроф и кавычку. Имена БД и таблиц чувствительны к регистру в Unix и нечувствительны в MS Windows. Имена колонок нечувствительны везде. Алиасы на таблицы чувствительны везде, алиасы на колонки нечувствительны везде.

Переменные

Имя переменной начинается с ‘@’, может содержать буквы, цифры, ‘_’, ‘$’, ‘.’. Первоначальное значение – NULL. Может содержать целое (64 бита), вещественное или строку. Можно использовать в тех местах, где разрешается использование выражений (не числовых констант как во фразе LIMIT!). Действует на текущую нить (thread). Установка:

• SET @имя=выражение
• @имя:=выражение внутри выражения

Комментарии

• как в C (начиная с ‘/*’ до ‘*/’); если после ‘/*’ стоит восклицательный знак и номер версии, то содержимое комментария исполняется, если номер версии равен или больше указанного
• как в sh (начиная с ‘#’ до конца строки)
• как в SQL (начиная с ‘– ‘ до конца строки, не забудьте про пробел!)

M – ширина поля при отображении (максимально – 255). D – число знаков в дробной части (не более M-2 и 30).

• Числовые (если число занимает меньше M позиций, то оно дополняется слева пробелами или нулями для ZEROFILL. Если число занимает более M колонок, то выводятся все цифры. Если используется ZEROFILL для целых, то автоматически добавляется UNSIGNED). Все вычисления проводятся с точностью 63 бита или переводом в DOUBLE (кроме нескольких функций).
  • TINYINT[(M)] [UNSIGNED[ [ZEROFILL] (1 байт)
  • SMALLINT[(M)] [UNSIGNED[ [ZEROFILL] (2 байта)
  • MEDIUMINT[(M)] [UNSIGNED[ [ZEROFILL] (3 байта)
  • INT[(M)] [UNSIGNED[ [ZEROFILL] (4 байта)
  • INTEGER[(M)] [UNSIGNED] [ZEROFILL] (4 байта)
  • BIGINT[(M)] [UNSIGNED] [ZEROFILL] (8 байт)
  • FLOAT[(M,D)] [ZEROFILL] (4 байта)
  • DOUBLE [PRECISION][(M,D)] [ZEROFILL]
  • REAL[(M,D)] [ZEROFILL] (синоним DOUBLE)
  • DECIMAL[(M[,D])] [ZEROFILL] (неупакованное десятичное вещественное число)
  • DEC – синоним DECIMAL
  • NUMERIC[(M[,D])] [ZEROFILL] (синоним DECIMAL)
• Дата и время (проверка значений не очень тщательная, например, дата 1999-11-31 будет воспринята нормально; допусимы номер дня, равный нулю, и номера месяца И дня равные нулю; неверное значение преобразуется в “нулевое” – 0000-00-00 и т.д.; заносить можно как строки с разделителями полей – любыми -, так и без разделителей или числа)
  • DATE (дата от ‘1000-01-01′ до ‘9999-12-31′; 3 байта)
  • DATETIME (время от ‘1000-01-01 00:00:00′ до ‘9999-12-31 23:59:59′; 8 байт)
  • TIMESTAMP[(M)] (время от 19700101000000 до 2037 года; 4 байта; если при INSERT или UPDATE (должны быть реальные изменения) значение опущено (только первая колонка этого типа) или равно NULL или NOW(), то заносится текущее время; формат выдачи зависит от M (хранится всегда все; задавать надо тоже все):
    • 14 (по умолчанию) – YYYYMMDDHHMMSS
    • 12 – YYMMDDHHMMSS
    • 8 – YYYYMMDD
    • 6 – YYMMDD
  • TIME (время от -838:59:59 до 838:59:59; выводится как HH:MM:SS; 3 байта; можно вводить как ‘D HH:MM:SS.fraction’, хотя доли пока не хранятся)
  • YEAR[(2|_4_)] (от 1901 до 2155 или от 1970 до 2069; 1 байт)
• Строки (в MySQL CHAR всегда NATIONAL; надо учитывать, что максимальная длина строки таблицы и пакета протокола клиент/сервер – 16 МБ – и надо увеличить размер каких-то буферов как сервера, так и клиента; VARCHAR, BLOB и TEXT – строки переменной длины; BINARY – навязчивый атрибут, т.е. если операнд выражения – BINARY, то все выражение – BINARY; TEXT – это BLOB, который сортируется и сравнивается в нечувствительной к регистру манере; TEXT можно рассматривать как VARCHAR нужного размера, а BLOB можно рассматривать как VARCHAR BINARY нужного размера, но хвостовые пробелы не удаляются и не м.б. значения по умолчанию; для CHAR и VARCHAR можно индексировать префикс колонки; для BLOB и TEXT обязательно индексировать префикс фиксированной длины; для VARCHAR и TEXT можно создавать индекс специального вида FULLTEXT для индексирования всей колонки; ORDER BY и GROUP BY работают только с объектами фиксированной длины или первыми max_sort_length байтами)
  • [NATIONAL] CHAR(M) [BINARY] (фиксированной длины от 1 до 255; дополняется справа пробелами; при извлечении правые пробелы удаляются; сортируются и сравниваются нечувствительно к регистру относительно набора символов по умолчанию, если не указано BINARY; CHAR(0) занимает 1 бит и может принимать два значения – NULL и “”)
  • NCHAR – синоним NATIONAL CHAR
  • CHARACTER – синоним CHAR
  • [NATIONAL] VARCHAR(M) [BINARY] (строка переменной длины от 1 до 255; правые пробелы удаляются при сохранении; сортируются и сравниваются нечувствительно к регистру относительно набора символов по умолчанию, если не указано BINARY)
  • CHARACTER VARYING – синоним VARCHAR
  • TINYBLOB (BLOB макс. длины 255)
  • TINYTEXT (TEXT макс. длины 255)
  • BLOB (BLOB макс. длины 65535)
  • TEXT (TEXT макс. длины 65535)
  • MEDIUMBLOB (BLOB макс. длины 2^24-1)
  • MEDIUMTEXT (TEXT макс. длины 2^24-1)
  • LONGBLOB (BLOB макс. длины 2^32-1, в действительности строка таблицы ограничена 16МБ)
  • LONGTEXT (TEXT макс. длины 2^32-1)
• ENUM(’строка1′,’строка2′,…) (может принимать значение из списка, NULL или “” – как признак ошибки при вставке; не более 65535 различных значений; 1 или 2 байта; если описан как NULL, то по умолчанию вставляется NULL; если описан как NOT NULL, то по умолчанию вставляется первая строка из списка; каждая строка имеет индекс, начиная с 1; индекс “” равен 0; индекс NULL равен NULL; в числовом контексте извлекается индекс, а не строка (как при вставке, так и при извлечении); сортировка производится по индексу; регистр букв не влияет при вставке, но извлекается в том виде, в каком колонка описывалась)
• SET(’строка1′,’строка2′,…) (может одновременно принимать от 0 до 64 значений из списка; элемент списка не может содержать запятую; 1, 2, 3, 4 или 8 байт;если значение SET извлекается/устанавливается в числовом контексте, то в числе каждому установленому биту соответствует элемент множества (первый элемент в описании множества соответствует младшему биту); сортируются как числа)

• операции над базами данных
  • CREATE DATABASE [IF NOT EXISTS] db_name
  • DROP DATABASE [IF EXISTS] db_name
• операции над таблицами
  • CREATE [TEMPORARY] TABLE [IF NOT EXISTS] имя-таблицы [(описание-колонки,...)] [опции] [[IGNORE | REPLACE] SELECT …]
    • описание-колонки
      • имя-колонки тип [NOT NULL | NULL] [DEFAULT значение-по-умолчанию] [AUTO_INCREMENT] [PRIMARY KEY] [описание-ссылки]
        NULL: по умолчанию
        AUTO_INCREMENT: для колонки типа целое число, при вставке NULL или 0 в такую колонку ищется максимальное значение колонки и увеличивается на единицу; отсчет начинается с 1; при удалении строки с наибольшим значением бывает всякое ; такая колонка м.б. только одна и она д.б. проиндексирована; не надо записывать в такую колонку отрицательных чисел; получить наибольшее число можно с помощью оператора: SELECT * FROM имя-таблицы WHERE имя-колонки IS NULL
        DEFAULT: назначается системой (NULL, 0, пустая строка и т.д.), если Вы не установили явно
        SELECT позволяет создать новую таблицу на основе уже существующей
      • PRIMARY KEY (имя-колонки[(длина)],…)
        уникальный ключ; все ключевые колонки д.б. NOT NULL; только один на таблицу (присваивается имя PRIMARY)
      • {KEY | INDEX} [имя-индекса] (имя-колонки[(длина)],…)
      • UNIQUE [INDEX] [имя-индекса] (имя-колонки[(длина)],…)
      • FULLTEXT [INDEX] [имя-индекса] (имя-колонки[(длина)],…)
      • [CONSTRAINT символ] FOREIGN KEY имя-индекса (имя-колонки[(длина)],…) [описание-ссылки]
        не реализовано
      • CHECK (выражение)
        не реализовано
    • описание-ссылки ::= REFERENCES имя-таблицы [(имя-колонки[(длина)],…)] [MATCH FULL | MATCH PARTIAL] [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
      не реализовано
    • опции
      • TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MYISAM } (см, типы таблиц)
      • AUTO_INCREMENT = начальное-значение
      • AVG_ROW_LENGTH = число
      • CHECKSUM = {0 | 1}
      • COMMENT = “строка-до-60-символов”
      • MAX_ROWS = число
      • MIN_ROWS = число
      • PACK_KEYS = {0 | 1}
        чтение происходит быстрее, а изменение – медленнее
      • PASSWORD = “пароль”
        шифруется описание таблицы (в бесплатной версии не работает)
      • DELAY_KEY_WRITE = {0 | 1}
      • ROW_FORMAT= { default | dynamic | fixed | compressed }
        не реализовано
      • RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=число-расслоений RAID_CHUNKSIZE=числоKB
        требуется ключ –with-raid при сборке; пока реализован только тип STRIPED (1 и RAID0 – это синонимы для STRIPED); позволяет создавать таблицы с данными более 2GB (или распараллелить ввод/вывод, если разнести директории на разные диски)
      • UNION = (имя-таблицы,[имя-таблицы...])
        только для таблиц типа MERGE
  • ALTER [IGNORE] TABLE имя-таблицы как-менять [,как-менять ...]
    • ADD [COLUMN] описание-колонки [FIRST | AFTER имя-колонки ]
      без FIRST или AFTER колонка вставляется в конец
    • ADD [COLUMN] (описание-колонки, описание-колонки, …)
    • ADD INDEX [имя-индекса] (имя-колонки[(длина)],…)
    • ADD PRIMARY KEY (имя-колонки[(длина)],…)
    • ADD UNIQUE [имя-индекса] (имя-колонки[(длина)],…)
    • ADD FULLTEXT [имя-индекса] (имя-колонки[(длина)],…)
    • ALTER [COLUMN] имя-колонки {SET DEFAULT константа | DROP DEFAULT}
    • CHANGE [COLUMN] имя-существующей-колонки описание-колонки
    • MODIFY [COLUMN] описание-колонки
    • DROP [COLUMN] имя-колонки
    • DROP PRIMARY KEY
    • DROP INDEX имя-индекса
    • RENAME [TO] новое-имя-таблицы
    • ORDER BY имя-колонки
    • опции таблицы

    делается временная копия таблицы, изменяется, затем старая таблица удаляется (соответственно, нужны привилегии: select, insert, delete, update, create и drop)
    IGNORE: если в преобразованной таблице образуются одинаковые значения UNIQUE индексов, то берется только одна строка (без IGNORE операция откатывается)
    нельзя сделать размер колонки короче, чем используется в каком-либо индексе

  • RENAME TABLE tbl_name TO new_table_name[, tbl_name2 TO new_table_name2,...]
    оператор выполняется как единое целое, что позволяет подменить таблицу пустой:

    CREATE TABLE new_table (...); RENAME TABLE old_table TO backup_table, new_table TO old_table;

  • DROP TABLE [IF EXISTS] имя-таблицы [, имя-таблицы,...] [RESTRICT | CASCADE]
    RESTRICT и CASCADE не реализованы
• обслуживание таблиц
  • OPTIMIZE TABLE имя-таблицы [, имя-таблицы,...]
    полезно после больших удалений или изменений таблиц с переменной длиной строки: сортировка индексов и дефрагментация
  • CHECK TABLE имя-таблицы [, имя-таблицы,...] {QUICK | FAST | MEDIUM | EXTEND | CHANGED}
  • BACKUP TABLE имя-таблицы [, имя-таблицы,...] TO ‘имя-директории’
  • RESTORE TABLE имя-таблицы [, имя-таблицы,...] FROM ‘имя-директории’
  • ANALYZE TABLE имя-таблицы [, имя-таблицы,...]
    пересчет распределения ключей
  • REPAIR TABLE имя-таблицы [, имя-таблицы,...] [QUICK] [EXTENDED]
• операции над строками
  • DELETE [LOW_PRIORITY] FROM имя-таблицы [WHERE выражение] [LIMIT макс-число-строк]
    место не освобождается – используйте OPTIMIZE
  • TRUNCATE TABLE имя-таблицы
    удаляет таблицу и тут же создает заново
  • SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL]
    выражение [AS имя-алиаса],…
    [INTO {OUTFILE | DUMPFILE} 'имя-файла' опции-экспорта
    [FROM из-каких-таблиц
    [WHERE выражение]
    [GROUP BY {номер-колонки | имя-колонки | формула} [ASC | DESC], …]
    [HAVING выражение]
    [ORDER BY {номер-колонки | имя-колонки | формула} [ASC | DESC], …]
    [LIMIT [смещение-от-0,] макс-число-строк]
    [PROCEDURE имя-процедуры]
    [FOR UPDATE | LOCK IN SHARE MODE]]
    FROM – простейший случай синтаксиса – имя-таблицы, если таблиц несколько, то смотри синтаксис JOIN (в простейшем случае имена перечисляются через запятую); также можно задавать алиас таблицы с помощью слова AS
    HAVING применяется последним (в частности, после GROUP BY) непосредственно перед посылкой результата клиенту (т.е. без оптимизации!)
    DUMPFILE: в файл записывается одна строка безо всяких разделителей полей и строк и escape (позволяет, например, сбросить картинку из BLOB)
    OUTFILE и DUMPFILE: необходимо иметь привилегии file, если файл с указанным именем существовал, то он не затирается; права к файлу устанавливаются на чтение для всех
    FOR UPDATE: если тип таблицы позволяет блокировку страницы/строки (InnoDB, GEMINI), то задействованные строки блокируются на запись
  • синтаксис JOIN (только не надо вопросов ко мне, задавайте их этим фантазерам
    • таблица::= имя-таблицы [[AS] алиас] [USE INDEX (список-ключей)] [IGNORE INDEX (список-ключей)]
    • таблица, таблица
      это синоним INNER JOIN
    • таблица [CROSS] JOIN таблица
    • таблица INNER JOIN таблица {ON условное-выражение | USING (список-колонок)}
    • таблица STRAIGHT_JOIN таблица
    • таблица LEFT [OUTER] JOIN [ON условное-выражение | USING (список-колонок)]
    • таблица NATURAL [LEFT [OUTER]] JOIN таблица
    • таблица LEFT OUTER JOIN таблица ON условное-выражение
    • таблица RIGHT [OUTER] JOIN [ON условное-выражение | USING (список-колонок)]
    • таблица NATURAL [RIGHT [OUTER]] JOIN таблица
  • INSERT
  • LOAD FILE

Между именем функции и скобкой не должно быть пробела. Операндами могут быть константы, имена колонок и переменные.

• группировка функций с помощью круглых скобок
• арифметические операции: +, -, *, /
• битовые функции: |, &, <<, >>, ~, BIT_COUNT()
• логические функции: !, ||, && (возвращают 1, 0, или NULL)
• сравнение:
  • =, <>, !=, <=, <, >=, >
  • <=> (сравнение, позволяющее сравнивать NULL)
  • expr IS NULL, expr IS NOT NULL
  • expr BETWEEN min AND max
  • expr IN (значение1,…)
  • expr NOT IN (значение1,…)
  • ISNULL()
  • COALESCE(список) – возвращает первый ненулевой элемент списка
  • INTERVAL(N,N1,N2,N3,…) – возвращает номер интервала, т.е. 1, если N < N1; 2 если N < N2 и т.д.
• сравнение строк (если выражение чувствительно к регистру, то и сравнение будет чувствительным к регистру)
  • expr LIKE шаблон [ESCAPE 'escape-символ'] (соответствие простому регулярному выражению SQL; “%” – любое количество любых символов; “_” – ровно один символ; escape-символ по умолчанию – “”; чтобы найти ‘ ‘ надо задать в шаблоне ‘ ‘; чтобы найти ” – ‘\’)
  • expr NOT LIKE шаблон [ESCA