Данная статья не является полным руководством по администрированию mysql-сервера, здесь скорее представлены первичные, самые необходимые данные для новичков. А посему рассматриваться будет сервер для ОС windows, хотя большинство команд справедливы и для *nix-систем. В работе используется в основном консоль, т.к. команды, вводимые в консоли, можно легко перенести, допустим, в web-интерфейс (например, в php для этого существуют специальные функции).

Для чего нужна база данных? Реляционные базы данных представляют собой не что иное, как просто несколько таблиц с записями. СУБД предназначены для управления этими таблицами, т.е. создания новых таблиц, удаления ненужных, вставки в таблицы новых записей, изменения записей и, конечно же, обработки запросов, а также некоторых иных, менее часто используемых функций. Собственно, ведь и базы данных необходимы для того, чтобы оперативно выдавать информацию, причем в определенном порядке. СУБД существует великое множество (хотя бы всем известная microsoft access). Но на принципах работы мы останавливаться не будем. Пожалуй, наиболее распространенной СУБД в Интернете является mysql, которая из-за своей быстроты и удобства в использовании получила широкое распространение. Да, и большое значение имеет то, что она совершенно бесплатная, т.е. распространяется по gnu gpl (в общем это значит, что вы можете скачать исходные коды и откомпилировать их у себя или вообще сделать СУБД «под себя», если, конечно, являетесь достаточно грамотным программистом. Правда, если это так, данная статья не для вас:-)).

Итак, в теперешней «лабораторной работе» понадобится: компьютер, собственно сам сервер СУБД mysql, ярлык консоли под рукой (собственно, он всегда должен быть под рукой: хоть удобство работы под cmd и значительно уступает работе в консольке в linux, но очень много вещей можно сделать из консоли быстрее, чем лезть через 3-5 менюшек за нужной опцией). Много вещей будет делаться именно через консоль. Конечно, существует множество «юзеро-ориентированных» программ, предназначенных для управления сервером, но рассматривать какую-либо одну не имеет смысла. Если вы будете знать основные команды, использовать mysql в web-приложениях станет не просто легко, а очень просто!
Установка mysql. Устанавливать и использовать мы будем mysql-сервер 3.23.38. Зачем вообще нужен sql? На первый взгляд, для создания более-менее приличного сайта не требуется особых наворотов (каковыми некоторые считают php и sql), но даже просто лог счетчика после пары-другой тысяч «оборотов» вам смотреть интересно не будет. Ежели в лог счетчика писать только время-дату, ip и имя компьютера посетителя, то лог в несколько тысяч строчек не только вытягивать каждый раз с сервера надоест, но и оперативно посмотреть, кто был, допустим, в ночь с четверга на пятницу на вашем, безусловно, замечательном сайте, будет очень трудно. А форум писать только, допустим, на php не просто довольно сложно, но и «неприятно».

Итак, инсталляция. Как таковая инсталляция не требует особо больших танцев с бубном. Жмем энное количество раз на кнопочку next — и наслаждаемся. Пару слов о том, как хранятся данные в mysql. По умолчанию каждая база данных хранится в отдельной папочке, название которой совпадает с названием этой базы данных, которые, в свою очередь, хранятся (опять же, по умолчанию) в папке data директории, в которой установлен сервер. Изменить дефолтовое местоположение данных можно следующим образом. Например, вы хотите создать базу данных, которая будет храниться, скажем, в e:/data/database_name, где database_name — имя базы данных. Тогда вы создаете файл database_name.sym в директории data и записываете в него всего одну строчку, которая содержит путь к местоположению вашей базы данных: «e:/data/database_name», и сохраняете. Если заглянуть в папки, в которых хранятся данные, то вы увидите там 3 файла. Данные хранятся в «оптимизированном» виде, т.е. не в виде простого текста — так просто их просмотреть не удастся. Итак, три файла: с расширениями .frm — в нем хранится информация о таблицах, содержащихся в БД, .myd — собственно данные и .myi — индексные данные. Теперь займемся безопасностью. Данные о пользователях mysql хранит в специальной базе данных, которая называется mysql (оригинально, ничего не скажешь:-)). В ней 5 таблиц, в которых хранятся логины и пароли в зашифрованном виде, а также атрибуты доступа к таблице. Эти таблицы называются:

В таблицах columns_priv, tables_priv хранятся привилегии пользователей для доступа, соответственно, к колонкам и таблицам, в db — привилегии для доступа к базам данных, в host — информация о компьютерах, с которых подключаются юзвери к mysql, и, наконец, в таблице user хранится информация о пользователях.
Более подробные сведения можно получить из мануала, который есть в установочном пакете (после установки он находится в mysql/docs). Это почти «двухметровый» документ, в котором написано практически все, что нужно знать на первых порах, и не только на первых:-). Нас интересует, каким образом сменить юзверя с логином root (те, кто знаком с *nux, знают, что это такое, а для остальных поясняю: суперпользователь с неограниченными правами), а также сделать юзверя, из-под имени которого мы будем работать с сервером. В идеале такой пользователь должен быть только один. Остальные пользователи должны быть с ограниченными правами (главный принцип администрирования — что не разрешено, то запрещено). Дело в том, что по умолчанию суперпользователь не имеет пароля!! И если вы, например, занесете в свою базу данных какие-то сверхсекретные сведения, посмотреть, удалить, изменить их сможет каждый:-). Итак, сервер мы установили, начинаем администрирование. Находим директорию, в которую мы установили наш сервер (по умолчанию это c:mysql). Находим директорию c:mysqlbin и видим множество «экзешников». Для начала нам потребуется mysql.exe. Запускаем. Да, опции запуска из консоли можно посмотреть, написав что-то вроде c:mysqlbinmysql.exe -h, после чего будет выведена справка. Но пока они нам не понадобятся. Все sql-команды вводятся в командной строке и должны завершаться «;» или «g». Для того, чтобы использовать базу данных, необходимо сначала ее выбрать. Для этого существует команда use. Нам необходима БД «mysql»: «mysql> use mysql;». Для того, чтобы посмотреть, какие базы данных имеются на сервере, существует команда «mysql> show databases;». Просмотр имеющихся таблиц так же прост, как и просмотр имеющихся БД: «mysql> show tables from databasename;», где databasename — имя базы данных. Посмотрим, кто у нас числится в юзверях нашего сервера. Пишем «mysql> select user,host,password from user;», после чего получим список юзверей с названиями компьютеров, с которых им можно подключаться к серверу. Прежде, чем вы начнете экспериментировать с правами доступа к базам данных, советую вам скопировать папку mysql (с файлами соответствующей базы данных) куда-нибудь в отдаленное место для того, чтобы, если что-то не получится, можно было бы «откатиться».

Команда select — она из самых частоиспользуемых команд. С ее помощью мы можем показать, какие данные нас интересуют, и попросить вывести ее на экран. Синтаксис ее таков: «select интересующие_столбцы_в_таблице from название_таблицы <опции>;» Вместо «интересующие_столбцы_в_таблице» можно поставить «*», тогда нам будут выданы значения всех колонок, которые присутствуют в таблице. Чаще всего вместо «<параметры>» пишут еще одно ключевое слово — where. Синтаксис таков: «where название_колонки_таблицы=’значение'».
Так, теперь пишем: «mysql>upda-te user set password=password (‘newpasword’) where user=’root';», где newpassword — новый пароль для суперпользователя. К слову, таким образом можно изменить пароль для любого юзверя. Немного поясню, что значат эти команды. Функция password(‘string’) шифрует строку по определенному алгоритму, а команда update изменяет значение записи where (где) в таблице user. К слову, все пароли в mysql хранятся в зашифрованном виде. Теперь нам нужно запретить суперпользователю подключаться с удаленных компьютеров. Для этого удаляем строчки со знаком «%». Пишем «de-lete from user where host=’%';». Команда delete имеет похожий синтаксис с командой select: «select from название_таблицы where название_колонки_таблицы=’значение';». Я надеюсь, понятно, что команды необходимо набирать без кавычек. Да, последней командой мы также удалили и «пустого» юзверя. Теперь у нас только два пользователя с неограниченными правами: root и mentalzavr, пароль которого, к слову, вы должны были задать при первой загрузке winmysqladmin (теперь он должен висеть в трее этаким светофором, зеленый свет на котором означает нормальную работу сервера, желтый — переходное состояние, а красный — останов сервера). Для того, чтобы наши изменения вступили в силу, необходимо либо перезапустить сервер, либо дать команду «flush privileges;». Дело в том, что большинство серверов самого различного назначения читают свой конфиг, где хранится большинство их настроек, только один раз при загрузке. Так и сервер mysql читает базу данных с параметрами доступа к базам данных при загрузке либо принудительно после специальной команды. Перезапустить сервер можно из панели управления, если вы работаете в windows nt-серии, к которой относятся windows xp, 2000 и собственно сама nt:-), либо из той же самой пресловутой командной строки. Сначала посмотрим, как называется наша служба. Для этого воспользуемся так называемой расширенной консолью wmic (wmi command-line). Если вы до этого ею не пользовались, система вам ее установит (xp). Это очень мощный инструмент. Но не тема данной статьи:-). Итак, пишем «wmic:rootcli>service list». Видим название службы «mysql». Выходим из wmic: «wmic:rootcli> exit». Теперь останавливаем службу «c:>net stop mysql» и стартуем снова: «c:>net stop mysql». Вы спрашиваете, зачем я все это рассказываю? Одна из причин — таким же образом можно останавливать и запускать любые другие службы. А вторая… Ну, представьте, что у вас стоит сервер mysql где-нибудь в локальной сети, а ваш компьютер находится в другом здании, и вам необходимо срочно переконфигурировать его. Бежать к серверу и вживую его настраивать? Ну, вы как хотите, а мне лично лень. Коннектимся к серверу и удаленно админим:-). Кстати, программка mysql.exe, которой мы пользовались для подключения к серверу (а именно это мы и делали), может работать и с удаленным сервером. Это легко увидеть, запустив mysql.exe следующим образом: «c:>mysql -h ip_adresss», где вместо «ip_address» — либо имя компьютера, к которому мы подключаемся, либо его ip-адрес:-). Ну вот, сервер мы перезапустили (или просто написали «flush privileges;»). Теперь, ежели все правильно, вы не сможете подключиться к своему серверу, просто запустив mysql.exe. Необходимо ввести пароль. Делается это таким образом: «c:>mysql -u root -p». Вас попросят ввести пароль на root, которые мы с вами задавали выше. Если все нормально, появится приглашение «mysql>», как и ранее. Теперь попробуем создать еще юзверей. Делается это так. Создадим троих юзверей с разными правами:

" mysql> grant all privileges on *.* to ferst@localhost identified by 'some_pass' with grant option; mysql> grant all privileges on *.* to ferst@"%" identified by 'some_pass' with grant option; mysql> grant reload,process on *.* to admin@localhost; mysql> grant usage on *.* to doom@localhost; "

Кстати, если вы дадите команду «mysql> status», сервер вам выдаст некоторое количество полезной информации. Итак, мы создали трех юзверей. Первый — ferst — получает все права и может подключаться к серверу как с локального компьютера, так и удаленно, пароль для доступа ему назначен sam_pass. Второй юзверь — admin — получает права на reload и refresh, но может подключаться только с локального компьютера (что означает значок «%»), пароль на вход не назначен. Третий юзверь с именем doom может подключаться с локального компьютера, и он создан, так сказать, «бесправным», т.е. все глобальные привилегии ему не даны. То же самое можно было бы сделать по-другому. Например, вот так:

"
mysql> insert into user values ('localhost','ferst',password('some_pass'),
'y','y','y','y','y','y','y', 'y','y','y','y','y','y','y');
mysql> insert into user values('%','ferst',password('some_pass'),
'y','y','y','y','y','y','y', 'y','y','y','y','y','y','y');
mysql> insert into user set host='localhost',user='admin',
reload_priv='y', process_priv= 'y';
mysql> insert into user(host, user,password)
values('localhost','doom','');
mysql> flush privileges;
"

Наверное, необходимо пояснить, что это значит. Команда insert вставляет строчку в таблицу, название которой следует после слова into, в скобках даны названия столбцов, имеющихся в этих таблицах. После values следуют значения, которые заносятся в соответствующие колонки. Значения необходимо заключать в кавычки. Последняя команда обновляет права доступа. Буква ‘y’ означает включение соответствующей привилегии, ‘n’ — соответственно выключение. Таблица user имеет следующие столбцы (даны в том же порядке, что и в реальности):
«| host | user | password | select_priv | insert_priv | update_priv | delete_priv | create_priv | drop_priv | reload_priv | shutdown_priv | process_priv | file_priv | grant_priv | references_priv | index_priv | alter_priv |»

Я думаю, что в пояснениях данная таблица не нуждается. Названия колонок говорят сами за себя (если вы, конечно, знаете английский, как я — на уровне 5-го класса хотя бы:-)). Естественно, что, ежели в предыдущих командах буквы ‘y’ заменить на ‘n’, соответствующий пользователь соответствующего права и не получит:-). Теперь разберемся, каким образом можно изменять права юзверей при подключении к базе данных. Для этого существует команда grant.

" mysql> grant select,insert,update, delete,create,drop on bankaccount.* to custom@localhost identified by 'stupid'; mysql> grant select,insert,update, delete,create,drop on expenses.* to custom@whitehouse.gov identified by 'stupid'; mysql> grant select,insert,update, delete,create,drop on customer.* to custom@'%' identified by 'stupid'; "

Поясню, что делают эти команды. После команды grant следует список привилегий, которые необходимо предоставить юзверю custom. После слова on идет название базы данных, после точки можно указать конкретную таблицу в этой базе данных, а звездочка означает «любая». А после слова to идет имя_юзверя@имя_ компьютера, с которого этот юзверь сможет подключаться. Знак процентов означает «любой_компьютер». После слов identified by следует указать в кавычках пароль, который будет требоваться у юзверя при входе. В данном случае это ‘stupid’. Конечно, подобного результата можно добиться и непосредственной модификацией таблицы user. Например, вот так:

»
mysql> insert into user (host,user, password)
values(‘localhost’,’custom’, password(‘stupid’));
»
Я думаю, понятно, что делает данная команда. Она вставляет в таблицу user запись со значениями в столбцах, которые записаны после слова values. Соответствующей модификацией этой команды можно достичь тех же результатов, что и использованием команд, изложенных выше. И не забудьте после того, как внесете изменения, дать команду flush privileges;. Теперь, после того, как вы успешно создали юзеров, переходим к более интересной части — собственно созданию базы данных. Для создания базы данных необходимо из той же директории, что и программу mysql.exe, запустить программу mysqladmin.exe. Если вы запустите эту программу просто без опций, она выдаст вам список параметров запуска. Собственно, и все использование этой программы сводится к запуску ее с соответствующими опциями. Итак, нас прежде всего интересует создание и удаление баз данных, а также подключение через логин и пароль к удаленному серверу (лень — воистину двигатель прогресса!). Подключаемся к удаленному серверу hostname с использованием логина root пароля, который у нас после спросят.

«c:mysqlbin>mysqladmin -h hostname -u root -p options». Вместо options необходимо написать собственно то, что мы хотим сделать. Создание базы данных с именем database_name «c:mysqlbin>mysqladmin -h mentalzavr -u mentalzavr -p create database_name». Удаление базы данных database_name после подтверждения и ввода пароля: «c:mysqlbin>mysqladmin -h mentalzavr -u mentalzavr -p drop database_name». Существуют также и другие параметры запуска, на которых мы особо останавливаться не будем, хотя они не менее важны. Сведения о них вы получите, как я вам уже сказал, запустив mysqladmin без опций. Итак, мы создали базу данных. Теперь необходимо создать таблицы, в которых, собственно, и будут храниться данные. Любая таблица должна относиться к какой-либо базе данных. Поэтому запускаем mysql.exe и выбираем только что созданную базу данных «mysql> use database_name» Для создания таблицы существует команда create. Синтаксис таков: «mysql>create table table_name(name int(3),name char(15));», где table_name — имя таблицы, после которой в скобках через запятую указаны последовательно имя столбца и его тип. Конечно, столбцов может быть и больше, чем два. Цифры в столбцах показывают размер значения в столбце. Ну, и для того, чтобы удалить таблицу, соответственно, необходимо написать: «mysql>drop table table_name» Таблиц в базе данных может быть несколько. Теперь вкратце остановимся на типах данных в таблице. Их довольно много, и поэтому я думаю на первых порах (а данная статья предназначена для предоставления только НАЧАЛЬНЫХ сведений об администрировании СУБД mysql, и ни в коем разе не является полным руководством) ограничиться нижеизложенным. Пожалуй, наиболее часто встречающимся полем в таблицах является порядковый номер. Это довольно удобно. Так, в mysql существует возможность создать таблицу со столбцом, значение которого будет изменяться на единицу с каждой новой записью. Теперь о том, как это делается. Как обычно, пишете в консоли:
»
mysql>use test;
mysql> create table table_name (no-mer int auto_increment,text char (10),key (nomer));
»
Данные команды выбирают в качестве текущей базу данных test и создают таблицу с именем table_name с двумя колонками. Первая колонка имеет атрибут extra «auto_increment». Это значит, что при добавлении записи в эту таблицу значение (число) в этом столбце будет автоматически увеличиваться на единицу. Посмотреть свойства колонок таблицы можно следующим образом (дав соответствующую команду): «mysql> desc table_name;».
Так, теперь коснемся остальных типов данных. Числовые. Их присутствует большое количество, основными являются (на мой взгляд) int и float. Те, кто знаком хотя бы немного с языком программирования С++, поймут сразу же, что первый из них — это целочисленный тип данных. float — тип данных, используемый для хранения чисел с плавающей точкой. Типы данных даты и времени. К ним относятся data, time, year, а также некоторые другие. Формат хранения данных в типе data — «yyyy-mm-dd», в типе time — «hh:mm:ss», в типе year — «yyyy». И практически основным типом данных является char. К текстовым типам также относятся text (65535 символов максимально) и некоторые другие.

И в заключение я хотел бы дать несколько полезных команд, которые помогут вам использовать консоль более эффективно. Все советы действительны для cmd.exe — командного процессора windows. Для начала вам необходимо включить использование мыши в консоли. Для этого щелкните правой кнопкой мыши на заголовке открытого окна консоли и выберите Свойства. И на первой закладке поставьте галочки напротив пунктов Выделение мышью и Быстрая вставка, а также напротив Отбрасывать повторения. Теперь вы можете в консоли использовать все прелести работы с буфером обмена. Выделение мышью происходит как обычно, копирование в буфер обмена — при нажатии на правую кнопку. Вставка производится в место нахождения курсора также правой кнопкой. Пара слов об автоматизации процесса первичного занесения данных в таблицы. Если вы создаете базу данных с нуля, то никаких проблем практически не возникает (ну, если не считать большого количества организационной работы и непосредственно кодинга, в процесс которого входит создание хотя бы web-интерфейса:-). Конечно, пару-другую строчек можно занести и в консоли. Ну, а ежели у вас имеется какой-то текстовый файл с записями, данные из которых нужно перевести в БД mysql? Тут воевать только буфером обмена с бесконечным повторением действий «копирование-вставка» может только ну очень большой… трудоголик. Ну, а мы как умные люди можем поступить проще (правда, все в этом мире относительно). Дело в том, что можно в качестве аргумента в команде «mysql> . file_name» передать имя файла «file_name», и этот файл будет выполнен. Что он собой представляет? Это просто последовательный список sql-команд. Теперь нам остается только сгенерировать такой файл, что в каждом отдельно взятом случае делается по-разному — тут нужно смотреть по обстановке. Конечно, можно пойти еще дальше и написать программу для автоматического занесения данных в БД. Но для простых задач можно просто даже текстовый файл с написанными в нем командами скопировать в буфер и вставить в приглашение mysql. «Вводы» будут восприниматься как знак завершения команды. Только не забывайте ставить в конце каждой строчки «;», ведь в mysql.exe команды можно вводить и в многострочном режиме, поэтому могут случаться накладки. Правда, есть и альтернативный способ. Если у вас есть текстовый файл с записями в виде таблицы, и они в нем разделены при помощи запятых и знаков окончания строки либо каких-то иных символов (тогда следует внести изменения в параметры следующей команды, заменив запятую и «n» на соответствующие знаки), можно использовать команду «mysql> load data infile «data.txt» into table my_table fields terminated by ‘,’ lines terminated by ‘n';». Результатом данной команды должна явиться таблица. В файле поля должны быть разделены запятыми, а каждая запись — начинаться с новой строки. Следует помнить еще и о такой вещи, как кодировка. Если все данные вносить через консоль, то они будут храниться в dos-кодировке, и при выводе таких данных — например, если в html-странице используют php — вы увидите «абракадабру». Чтобы этого не происходило, заносите данные только в одной кодировке или перекодируйте их с помощью специальных функций (для php это функция $text_after=convert_cyr_string ($text,»w»,»d»), где два последних параметра указывают соответственно на то, что исходная кодировка — windows, а конечная после перекодировки — dos). И напоследок. Гораздо приятней работать с консолью, так всеми нелюбимой, ежели сделать что-то вроде этого: «c:>title linux». На несведущих людей действует довольно своеобразно:-).

Источник:  internet-technologies.ru