Доброго времени суток, коллеги 🙂
Да, именно коллеги, потому как простому смертному MySQL командная строка (или MySQL Shell, как её ещё любят называть разработчики), равно как и работа с MySQL в консоли, вряд ли пригодится.
Для того, чтобы данная тема была интересна, нужно быть, как минимум, или начинающим системным администратором, которые не пользуются phpMyAdmin и другими интерфейсами принципиально.
Лично я сам предпочитаю использовать phpMyAdmin как основную для работы с MySQL, т.к. по природе своей являюсь визуалом. Однако, иногда на практике встречаются ситуации, когда знания MySQL командной строки и умения работы с MySQL в консоли сервера просто необходимы.
У меня на данный момент их было три:
Но, повторюсь, работа с MySQL в консоли и командной строке вполне подходит и для повседневного использования. Все известные визуальные программы для работы с БД всё равно работают на основе консольных команд MySQL. Поэтому, кому как нравится 🙂
Поскольку я буду демонстрировать работу с командной строкой MySQL в консоли сервера, то для начала неплохо бы было её запустить.
Действие простое, многим знакомое, для которого достаточно знания «горячих клавиш».
Запуск консоли в Windows :
Запуск консоли в Linux дистрибутивах (терминала) : в Ubuntu, с которой я работал, достаточно системной комбинации клавиш Ctrl+Alt+T . Про остальные ОС на базе Linux ничего сказать не могу.
Запуска консоли в MacOS : сам я таким не занимался, т.к. Мак-ом ещё не обзавёлся и вряд ли обзаведусь за ненадобностью, но, насколько мне удалось узнать, «горячих клавиш» для вызова терминала в этой ОС нет. Так что если вы являетесь пользователем «яблочной» продукции, то запустите консоль через интерфейс ОС, благо, что мануалов в сети много.
Немного стыдно даже такую информацию публиковать на случай, если на данную статью случайно набредёт какой-то профессиональный системный администратор с бородатым опытом, т.к. он подумает: «Автор считает своих читателей программистами, но при этом учит запускать консоль… Псих какой-то:-)».
Да, вполне логично 🙂 Но я просто учитываю ситуации, что наряду с профессиональными разработчиками сюда могут попасть и новички. Поэтому я и стараюсь делать информацию полной и доступной для всех категорий пользователей, о чём уже говорил.
Итак, первым делом, нам нужно получить доступ к MySQL командной строке в консоли. Для этого открываем консоль сервера и, если у вас MySQL установлен глобально в виде сервиса, то для «проверки связи» прописываем следующее:
Mysql -V
Консольная команда mysql позволяет нам запустить одноименную утилиту, являющуюся командной строкой MySQL.
Это позволит нам узнать версию MySQL, установленного на компьютере и убедиться, что он вообще установлен как служба. Если это так, то в ответ в консоли вы увидите примерно следующий текст: .
Да, я не «трушный кодер», потому что сижу под Виндой 🙂 Но, не суть. На Unix системах процедура будет такой же.
Если же вдруг MySQL у вас не установлен глобально на компьютере или у вас есть необходимость работы с несколькими версиями MySQL, при которой есть только каталоги с файлами и библиотеками MySQL, то запуск MySQL через консоль будет выглядеть следующим образом.
В консоли переходим в каталог, где находится исполняемый файл MySQL (на Windows, по крайней мере, это mysql.exe) следующей командой:
Cd C:\OpenServer\modules\database\MySQL-5.7-x64\bin
Путь к дистрибутиву у вас, естественно, может отличаться. В качестве примера я решил запустить одну из версий MySQL, входящих в комплект OpenServer.
И запускаем MySQL, проверяя попутно его версию:
Mysql.exe -V
В результате в консоль должно было вывестись аналогичное первому случаю сообщение mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64) .
Всё, с запуском MySQL командной строки через консоль сервера разобрались, теперь будем подключаться непосредственно к серверу MySQL.
Cервер MySQL может не обязательно находиться на той же машине, на которой вы пытаетесь получить к нему доступ в консоли. Следовательно, для того, чтобы подключение к удалённому серверу MySQL через консоль было возможно, утилита mysql имеет множество параметров, с помощью которых можно указывать необходимые настройки.
Для того, чтобы запустить командную строку MySQL в консоли, нам достаточно прописать в консоли сервера следующее:
Однако, в таком случае вы получите следующую ошибку: ERROR 1045 (28000): Access denied for user ‘ODBC’@’localhost’ (using password: NO) . Так сообщение об ошибке выглядит у меня на Windows. Если же вы используете Linux, то вместо ODBC будет имя вашего системного пользователя, под которым вы совершаете действия в системе.
Всё это потому, что по умолчанию при подключении к MySQL серверу в консоли используется пользователь ODBC на Windows без пароля и системный юзер на Linux с его же паролем. Хост по умолчанию localhost, т.е. данный пользователь может подключаться только с локальной машины.
Поэтому выхода у вас два: либо использовать другого пользователя для входа в командную строку MySQL, либо создать пользователя MySQL с нужной учёткой. Но для второго способа нам без первого всё равно не обойтись 🙂 Поэтому для начала подключаемся к серверу MySQL под стандартным пользователем root , который создаётся при установке MySQL на компьютер и для которого по умолчанию разрешён доступ с localhost:
Mysql -u root -p
При требовании ввести пароль просто нажимаем Enter (если вы его не вводили, конечно, при установке MySQL). После этого вы будете подключены к серверу MySQL с присвоением вам идентификатора подключения и получением доступа к командной строке MySQL.
Если же вы захотите подключиться к серверу MySQL, имеющему специфическое имя хоста или IP или захотите залогиниться под другим пользователем, то используйте команду следующего формата:
Mysql -u имя_пользователя -p пароль_пользователя -h хост_или_IP_сервера_MySQL
Вместо кириллических символов с подчёркиванием, естественно, нужно ввести свои данные латинницей. Кстати, данную команду при желании можете прописать немного в другом формате:
Mysql --user=имя_пользователя --password=пароль_пользователя --host=хост_или_IP_сервера_MySQL
Если по какой-то причине вы не хотите, чтобы ваш пароль подключения к MySQL отображался в консоли (что правильно, на самом деле), можете использовать следующую команду:
Mysql -u имя_пользователя -h хост_или_IP_сервера_MySQL -p
Поскольку пароль не указан явно, вам будет предложено его ввести на следующем шаге. Причём, введённые вами символы отображены не будут даже в виде звёздочек (wildcards), вместо этого будет просто пустая строка.
Помимо указанных настроек подключения возможно использовать следующие параметры, значения которым будут указываться аналогичным с вышеперечисленными образом:
Понятное дело, что на этом список всех параметров подключения к MySQL серверу не ограничивается. В реальности их намного больше.
Если по какой-то причине стандартный вариант с указанием хоста, пользователя и пароля вам не подойдёт, то для выяснения полного перечня параметров подключения вам будет полезна следующая информация — https://dev.mysql.com/doc/refman/5.7/en/connecting.html
После того, как мы подключились к серверу MySQL и запустили командную строку MySQL, время начать жизненный цикл нашей базы данных сайта, который стартует с её создания. Для создания базы данных MySQL через командную строку нужно ввести следующую команду:
CREATE DATABASE имя_базы_данных;
То же самое действие можно сделать и с помощью специальной MySQL утилиты mysqladmin . Запускается она изолированно от командной строки MySQL, т.е. для её использования вам нужно будет выйти из неё или открыть новую консоль сервера.
А затем вызвать следующую команду:
mysqladmin create имя_базы_данных;К слову, с помощью утилиты mysqladmin можно не только создавать и удалять базы данных, не заходя в консоль MySQL, но и оперировать серверной конфигурацией, процессами MySQL, управлять репликациями, пинговать сервера и делать ещё много всего интересного.
Не часто, но иногда требуется создать нового пользователя MySQL. Причём, в консольном режиме.
В командной строке MySQL это делается следующим образом:
CREATE USER "имя_пользователя"@"хост_или_IP_машины" IDENTIFIED BY "пароль_пользователя";
Пользователь создан. Параметр хост_или_IP_машины означает, что при создании пользователя нужно указывать IP, с которого он сможет подключаться к серверу, или имя хоста (подойдёт доменное имя рабочей машины в сети).
Кстати, при указании хоста для подключения к MySQL серверу можно использовать символ процента — % , который означает, что создаваемый пользователь может соединяться с сервером MySQL с любого IP адреса или хоста.
При этом нужно учитывать, что в данном случае localhost не входит в список адресов, указываемых с помощью %, т.к. localhost обозначает подключение через UNIX сокет вместо стандартного TCP/IP. Т.е. если созданный пользователь MySQL будет подключаться к серверу не с помощью сокетов, а по другому протоколу, указываемому при соединении с MySQL сервером в консоли, то ему нужно будет создавать две учётные записи пользователя:
CREATE USER "имя_пользователя"@"%" IDENTIFIED BY "password"; CREATE USER "имя_пользователя"@"localhost" IDENTIFIED BY "password";
С созданием пользователя MySQL в консоли мы разобрались. Теперь зададим права пользователя для выполнения любых действий с нашей только что созданной базой данных следующей командой:
GRANT ALL PRIVILEGES ON имя_базы_данных.* TO "имя_пользователя"@"хост_или_IP_машины";
Опция ALL , как вы поняли, как раз и указывает на то, что пользователю разрешаются любые действия с определённой БД. Полный список прав, которые можно выдавать пользователям с помощью команды GRANT , можно найти здесь (правда, описание параметров на английском) — https://dev.mysql.com/doc/refman/5.7/en/grant.html#grant-privileges
Можно, кстати, использовать несколько параметров привилегий, указывая их при вызове команды через запятую.
Если захотите создать суперюзера, т.е. обладающего глобальными привилегиями для осуществления различных действий со всеми БД на сервере, то используйте следующий вызов команды:
GRANT ALL ON *.* TO "имя_пользователя"@"хост_или_IP_машины";
Кстати, не лишним будет очистить кэш MySQL сервера после установки привилегий. Если будете их менять, то также не забывайте делать это вызовом следующей команды:
FLUSH PRIVILEGES;
А если захотите поменять права пользователя, то используйте сначала следующую команду чтобы сбросить все права:
REVOKE ALL PRIVILEGES ON *.* FROM "имя_пользователя"@"хост_или_IP_машины";
А затем установите ему нужные с помощью GRANT , как это было описано ранее.
Если же вам в будущем потребуется изменить пароль пользователя MySQL, то для этого достаточно выполнить в MySQL Shell следующие команды:
SET PASSWORD FOR "имя_пользователя"@"хост_или_IP_машины" = PASSWORD("новый_пароль"); FLUSH PRIVILEGES;
Сброс кэша привилегий MySQL сервера нужен с той же целью, что и при смене прав — без этого действия смена пароля пользователя MySQL может быть не засчитана, поэтому не ленитесь пользоваться 🙂
В Интернете, кстати, полно примеров использования следующей команды для сброса пароля MySQL пользователя:
UPDATE mysql.user SET Password=PASSWORD("пароль") WHERE User="имя_пользователя";
Но у меня на MySQL 5.7 данный вариант не сработал, выдав ошибку ERROR 1054 (42S22): Unknown column ‘Password’ in ‘field list’ . Её причина оказалось отсутствие поля Password в таблице mysql.user.
Из чего можно предположить, что данный вариант работает лишь для старых версий MySQL, где в таблице пользователей данное поле существовало. Поэтому для уверенности используйте мой первый вариант, если вы, конечно не вынуждены работать с допотопным ПО без возможности обновления версий 🙂
На этом создание нового пользователя MySQL и обзор действий с его правами и паролями закончен. Идём далее.
Теперь, после создания пользователя в MySQL Shell и назначения ему прав на работу с БД нам нужно выбрать эту самую базу, чтобы можно было оперировать самой базой и хранящимися в ней данными.
Для этого используем следующую команду в MySQL консоли:
USE имя_базы_данных;
Если всё прошло успешно, то в консоли выведется сообщение Database changed , что будет сигнализировать о том, что мы выполнили вход в базу MySQL через консоль. Кстати, при соединении с сервером MySQL, изначально можно указывать БД, с которой необходимо будет работать. Для этого нужно прописать следующую команду в консоли сервера:
Mysql --user=имя_пользователя --password=пароль_пользователя --host=хост_или_IP_сервера_MySQL --database=имя_базы_данных
Или то же самое, только с запросом ввода пароля пользователя MySQL:
mysql -u имя_пользователя -h хост_или_IP_сервера_MySQL имя_базы_данных -pНа этом всё. Думаю, никаких сложностей с подключением к базе данных MySQL через консоль теперь у вас не возникнет 🙂
Итак, базу данных MySQL через консоль мы создали. Теперь было бы неплохо научиться с ней работать для случая, когда командная строка MySQL будет единственным средством доступа к хранящимся на сервере данным (как это было у меня, о чём я говорил в начале статьи).
Как известно, состоит из таблиц, внутри которых уже и хранится информация в виде записей с несколькими полями. Поэтому, следуя по иерархии размещения информации, для начала научимся производить типичные CRUD операции с таблицами.
CRUD операции, если кто-то не в курсе, — это операции по созданию, чтению, обновлению и удалению данных от англ. «Create, Read, Update, Delete» (возможно, на собеседованиях вам такое понадобится).
Напоминаю, что для выполнения действий с таблицами вы должны сперва подключиться к БД MySQL с помощью команды USE .
Итак, первая на повестке у нас команда создания таблицы MySQL в БД через командную строку, которая выглядит так:
CREATE TABLE имя_таблицы (название_поля_1 тип_поля_1, название_поля_2 тип_поля_2(размер_поля_2), INDEX(название_поля_1), ...);
Как вы понимаете, полей может быть сколько угодно, типы их могут быть различны, равно как и наличие индексов и ключей опционально.
Кстати, если вы захотите скопировать таблицу в другую базу данных или просто создать копию в текущей, вам помогут следующие команды:
CREATE TABLE новое_имя_таблицы LIKE старое_имя_таблицы; INSERT новое_имя_таблицы SELECT * FROM старое_имя_таблицы;
Данные команды позволяют скопировать структуру таблицы и её данные вместе с индексами и триггерами таблиц. Если вам нужны просто данные и структура (имена полей и их типов данных), то можете обойтись вызовом одной команды:
CREATE TABLE новое_имя_таблицы AS SELECT * FROM старое_имя_таблицы;
Следующая операция из блока CRUD у нас чтение. В случае таблиц чтением будет вывод на экран их структуры. Для этого существует следующие четыре команды:
SHOW FULL COLUMNS FROM имя_таблицы; DESCRIBE имя_таблицы; EXPLAIN имя_таблицы; SHOW CREATE TABLE имя_таблицы;
Первая выводит информацию о полях таблицы БД в табличном виде в консоль MySQL с указанием названия поля, типа данных, наличия ключей, значением по умолчанию и т.д. При использовании ключевого слова FULL можно получить расширенную информацию, включая привилегии на каждое из полей для текущего пользователя, комментарии к каждому из них и значение кодировки.
Вторая и третья команды являются просто сокращёнными формами первой команды без расширенной информации. Зачем было их плодить — даже не представляю… Разве что, чтобы было что спросить на собеседованиях при приёме на работу? 🙂
Четвёртая команда, помимо имени, типов полей и значений их по умолчанию позволяет получить значения ключей таблиц, движков таблиц (InnoDB, MyISAM), кодировку и др.
Update операция в случае таблиц представляет собой изменение их структуры, т.е. различные действия с полями таблиц MySQL:
ALTER TABLE имя_таблицы DROP COLUMN название_поля; ALTER TABLE имя_таблицы ADD COLUMN название_поля VARCHAR(20); ALTER TABLE имя_таблицы CHANGE старое_название_поля новое_название_поля VARCHAR(50); ALTER TABLE имя_таблицы MODIFY название_поля VARCHAR(3);
Первая команда позволяет удалить определённое поле таблицы, вторая — добавить, третья позволяет переименовать поле и попутно изменить тип хранимых в нём данных, а четвёртая — изменить исключительно тип данных.
Тоже самое можно делать и с индексами таблиц с помощью во многом похожих команд:
ALTER TABLE имя_таблицы ADD UNIQUE INDEX имя_индекса (название_поля_1, ...); ALTER TABLE имя_таблицы rename INDEX старое_имя_индекса TO новое_имя_индекса; ALTER TABLE имя_таблицы DROP INDEX имя_индекса;
Приведённые команды позволяют добавлять, переименовывать и удалять индексы из таблиц MySQL через командную строку. Для добавления и удаления индексов есть, кстати, ещё альтернативный вариант использования самостоятельных команд, а не делать это через ALTER TABLE . Поэтому при желании можете использовать их:
CREATE UNIQUE INDEX имя_индекса (название_поля_1, ...) ON имя_таблицы; DROP INDEX имя_индекса ON имя_таблицы;
Приведённые команды являются эквивалентами первой и последней из предыдущего блока. Для переименования индекса, к сожалению, отдельной команды не существует. А для изменения типа индекса, к сожалению, вообще нет никакой возможности в MySQL. Единственный выход — удалить индекс и создать его снова с нужным типом.
Ну, и наконец, мы дошли до последней операции из блока CRUD — к удалению. Удалить таблицы MySQL из БД очень просто. Достаточно в консоли MySQL выполнить следующую команду:
DROP TABLE имя_таблицы;
Иногда на практике возникают ситуации, из-за которых не получается удалить таблицу или изменить её структуру. Как правило, это связано с использованием в БД внешних ключей для связи таблиц между собой. С этой ситуацией я лично сталкивался неоднократно, о чём говорил в начале статьи.
Поэтому, если при удалении или обновлении структуры таблицы или её данных MySQL вернул вам ошибку с текстом Cannot delete or update a parent row: a foreign key constraint fails , то следующая информация вам будет как нельзя кстати.
Для того, чтобы осуществить задуманное, нам нужно временно отключить проверку существования внешних ключей, произвести необходимую операцию, а затем снова включить проверку, т.к. она действительно нужна и позволяет предохраняться от нарушения целостности данных в большинстве случаев.
Собственно говоря, с данной целью внешние ключи MySQL и нужны.
Итак, для удаления данных, которому мешают внешние ключи необходимо выполнить следующие действия в консоли MySQL:
SET FOREIGN_KEY_CHECKS=0; #необходимая_mysql_команда SET FOREIGN_KEY_CHECKS=1;Кстати, если захотите удалить внешний ключ, то процедура будет такой же, как и при удалении индекса:
ALTER TABLE имя_таблицы DROP FOREIGN KEY имя_внешнего_ключа;
Чтобы узнать имя внешнего ключа MySQL таблицы, используйте уже знакомую команду MySQL консоли SHOW CREATE TABLE .
Для таблиц CRUD операции в MySQL консоли мы рассмотрели. Для полной картины данной шпаргалке не хватает только команд для работы с самими данными, хранящимися в таблицах БД. Думаю, многие знают эти команды и пользовались ими на практике, но всё же напомню их ещё раз.
CRUD операции для работы с данными таблиц MySQL будут выглядеть так:
INSERT INTO имя_таблицы (поле1, поле2, ...) VALUES (значение_поля_1, значение_поля_2, ...); SELECT поле1, поле2, ... FROM имя_таблицы; UPDATE имя_таблицы SET поле1 = значение_поля_1, поле2 = значение_поля_2; DELETE FROM имя_таблицы WHERE поле1 = значение_поля_1;
Приведённые выше команды соответствуют операциям создания, чтения, обновления и удаления данных из таблиц БД MySQL. При использовании SELECT и UPDATE также возможно использовать уточняющий оператор WHERE, с помощью которого можно конкретизировать выбор данных способом, описанным в случае использования DELETE.
Также при выборке данных из БД с помощью SELECT можно использовать следующий вариант для получения значений всех полей таблицы:
SELECT * FROM имя_таблицы;
Естественно, что в данных операциях могут использоваться и другие операторы, помимо WHERE. Особенно их много при выборке данных с помощью SELECT: тут и UNION для объединения результатов нескольких запросов, и различные типы JOIN. Все перечислять очень долго и утомительно как для меня, так и для вас будет читать это.
Поэтому давайте договоримся: если вам захочется о чём-то узнать поподробнее — просто напишите об этом в комментариях, и я постараюсь вам ответить. Или же это сделают другие сведущие участники нашего сообщества. Ок? 😉
Так что пока на данном блоке останавливаться не будем.
Если же вам нужно будет удалить все данные из таблицы, то можете воспользоваться следующей MySQL командой:
TRUNCATE имя_таблицы;
Перед её вызовом, как уже ранее и говорилось, вам может потребоваться отключение проверки внешних ключей в случае наличия связанных таблиц MySQL, которая может препятствовать произведению необходимого действия.
Ещё один интересный момент, который здесь нужно учесть, это то, что данная команда не производит сброс счётчика AUTO_INCREMENT, который используется, как известно, для автоматической генерации значения поля без необходимости его ручной установки.
Поля данного типа чаще всего используются для генерации значений главного ключевого поля id, которое используется для установки связей между данными разных таблиц.
Т.е., если до удаления данных таблицы с помощью TRUNCATE максимальное значение счётчика было 1200, то у первой записи после данной процедуры значение идентификатора будет 1201. В принципе, ничего страшного. Если вы задали достаточный размер для данного поля, то переполнение значений вам грозит не скоро.
Однако, в отдельных случаях, когда в коде приложения есть какая-то привязка к значению поля, то данное поведение может доставлять неудобства.
Чтобы этого избежать, используйте вместо команды выше данный вариант:
TRUNCATE TABLE someTable RESTART IDENTITY;
Данный вариант вызова команды TRUNCATE позволит вам сбросить значение счётчика полей с AUTO_INCREMENT . Поэтому значение поля первой добавленной записи после данного удаления будет 1 вместо 1201, как в примере выше.
Жизненный цикл работы с базой данных подходит к концу и завершается вполне логично — её удалением. Для того, чтобы сделать данную операцию в консоли MySQL, нужно выполнить следующую команду (при этом удаляемая БД может быть и не выбрана командой USE ):
DELETE DATABASE имя_базы_данных;
То же самое действие можно сделать и с помощью MySQL утилиты mysqladmin, о которой я уже упоминал в начале статьи при создании БД:
Mysqladmin drop имя_базы_данных;
При вызове команды в консоли сервера появится следующее сообщение:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the ‘имя_базы_данных’ database
Вкратце — это предупреждение о том, что удаление базы данных MySQL — идея очень плохая. Также запрашивается подтверждение действия. Если согласны — пишем y и нажимаем Enter на клавиатуре, после чего на экран выведется следующее сообщение (если всё прошло успешно, конечно же):
Database «имя_базы_данных» dropped
Вот так 🙂
Теперь та же участь постигнет и создаваемого нами пользователя MySQL, чтобы продемонстрировать вам, как это делается. Но для начала неплохо бы перед самим удалением проверить, существует ли требуемый пользователь на самом деле.
Начиная с MySQL 5.7 для обоих этих действий существует одна команда:
DROP USER IF EXISTS имя_пользователя;
В более ранних версиях MySQL требовалось две отдельные команды:
GRANT USAGE ON *.* TO "имя_пользователя"@"хост_или_IP_адрес"; DROP USER "имя_пользователя"@"хост_или_IP_адрес";
К сожалению, в данном случае сообщение о выполнении операции в консоли MySQL традиционно малоинформативно 🙁 Поэтому, чтобы узнать, что удаление пользователя пользователь MySQL всё-таки произошло, можно воспользоваться следующей командой, которая выводит на экран список всех существующих на текущем MySQL сервере пользователей:
SELECT User FROM mysql.user;
Данный вариант команды выведет только имена пользователей. Если же вам нужно будет посмотреть хосты, с которых пользователи могут коннектиться к серверу, и список привилегий, то можете вызвать команду в следующем виде:
SELECT User, Host, Grant_priv FROM mysql.user;
Также в таблице mysql.user существует масса других полей, в которых хранятся другие типы привилегий и прочая информация, с полным списком которой можно познакомиться здесь — https://mariadb.com/kb/en/library/mysqluser-table/
Не смущайтесь, что это документация к СУБД MariaDB. Технически это то же самое, что и MySQL, т.к. MariaDB является всего лишь её ответвлением или форком от англ «fork» — ответвление, вилка.
Зачем её было делать — снова без понятия 🙂 Разве что, чтобы всем гордо заявлять, что «у меня своя СУБД»?.. Но о точных мотивах и различиях MySQL и MariaDB я, честно говоря, не сильно в курсе. Поэтому если вы что-то об этом знаете — было бы интересно прочитать об этом в комментариях.
Вот и всё, жизненный цикл БД и пользователя, начавшийся их созданием и завершившийся удалением подошёл к концу. Следовательно, командная строка MySQL, с которой мы работали в консоли сервера с помощью утилиты mysql , нам больше не нужна.
Осталось только выйти из неё…
Казалось бы, действие тривиальное, но многие в ситуации, когда нужно выйти из командной строки MySQL просто закрывают консоль сервера и открывают её снова. Можно, конечно, и так действовать, но это отнимает лишние секунды рабочего времени и заставляет раздражаться каждый раз, когда это сделать необходимо.
Правильным поведением в данной ситуации будет всего лишь вызов команды exit в командной строке MySQL, после чего сервис с нами вежливо попрощается 🙂
И всего-то 🙂 Так что в следующий раз не нужно плодить консоли и переоткрывать их каждый раз, когда нужно будет выйти из MySQL в консоли и снова получить к ней доступ для управления сервером.
Вот и всё, что я хотел вам сегодня рассказать. Надеюсь, что моя шпаргалка по работе с командной строкой MySQL через консоль пригодится не только мне, но и вам.
Говоря о практическом применении изложенной в статье информации хочется напомнить, что приведёнными конструкциями можно пользоваться не только в командной строке MySQL и консоли сервера, но и в консоли phpMyAdmin и прочего ПО, которое предоставляет такую возможность.
Пользоваться вам ею или нет — ваше дело. Но, как я сказал в самом начале статьи, бывают ситуации, когда использование консоли MySQL может сэкономить ваше время и нервы. А пользоваться ли командной строкой каждый день или нет — дело исключительно вкуса и индивидуальных предпочтений.
Пишите комментариях о том, какие команды вы используете чаще всего. А, возможно, вы знаете ещё какие-то хитрости. В любом случае делитесь своим мнением с остальными и поддерживайте беседы в комментариях других пользователей.
Обещаю, что в процессе вы узнаете много нового, как и я при написании каждой своей статьи 🙂
На этом всё! Удачи и до новых встреч 🙂
P.S. : если вам нужен сайт либо необходимо внести правки на существующий, но для этого нет времени и желания, могу предложить свои услуги.
Более 5 лет опыта профессиональной разработки сайтов. Работа с PHP , OpenCart , WordPress , Laravel , Yii , MySQL , PostgreSQL , JavaScript , React , Angular и другими технологиями web-разработки.
От автора: девушка, у вас машина не заводится? Может «с толкача» попробуем? Я помогу! Не знаете, как это? «С толкача» заводили только сервер СУБД через cmd? Значит, как запустить MySQL через командную строку вы знаете, а автомобиль – даже не догадываетесь. Мадмуазель, вы «странная» блондинка!.
При подготовке каждой статьи мне приходится «основательно» шерстить интернет в поисках справочных материалов по той или иной «сайтостроительной» дисциплине. По большей части «наук» в Сети опубликовано множество подробных руководств и учебников, но только не по MySQL.
Конечно, есть достойные образовательные , но в основной части по СУБД приводятся лишь примеры написания команд, и даже без указания синтаксиса. В такой ситуации любому «чайнику» понять, например, как подключиться к MySQL через командную строку, почти нереально.
Чтобы найти ответ новичку, придется перешерстить с десяток тематических форумов, где профессиональные разработчики (корча из себя чуть ли не «академиков») снизойдут до того, чтобы хоть что-то нормально пояснить начинающему.
Стоит понимать, что настоящему админу следует знать все способы запуска и работы с сервером БД. Понятно, что программные оболочки – самый удобный метод, но ведь они не всегда могут оказаться «под рукой». Поэтому знание того, как включить MySQL через командную строку, не раз спасет и данные сайта, и вашу репутацию специалиста (если собираетесь заниматься веб-разработкой профессионально).
Рассмотрим эту тему более подробно. И не корчите, пожалуйста, мины, если что-то из сказанного в данном материале покажется вам таким же простым, как заводить автомобиль «с толкача» .
Для запуска MySQL из командной строки сначала активируем саму утилиту (cmd), которая входит в стандартный набор Windows. Сделать это можно несколькими способами:
Клавиатурным сочетанием Win+R, а затем в появившемся диалоге «Выполнить» ввести cmd
Через панель «Пуск».
Кроме этого MySQL может поставляться с «родной» оболочкой. Например, если вы устанавливаете сервер БД на локальную машину как самостоятельное «приложение», которое не входит в набор для «джентльменов».
Родная MySQL командная строка запускается также как и обычная cmd. Отличие в работе обеих утилит проявится позже. Об этом мы поговорим в следующем разделе.
Сначала разберемся со встроенной в операционную систему утилитой. Рассмотрим, как запустить MySQL из командной строки Windows. Для этого нужно:
Путь к папке в файловой системе ПК, куда вы установили сервер БД.
Учетная запись пользователя – логин и пароль.
Если вы работаете с Денвером, то исполняемый файл находится на виртуальном диске, который создается после запуска локального сервера, по этому пути: Z:\usr\local\mysql-5.5\bin
Как я уже сказал, командная строка MySQL в Denwer запускается через cmd OC. То есть, сначала запускаем ее, а затем прописываем путь к исполняемому файлу СУБД. Пароль и логин для доступа можно указывать сначала или потом после активации утилиты MySQL. Как по мне, то удобнее прописывать все сразу: Z:\usr\local\mysql-5.5\bin\mysql.exe -u root
Мы зашли на сервер СУБД под правами администратора. У меня пароль для root не установлен, поэтому его значение не указано. После того, как мы запустили MySQL из командной строки, внизу появится справочная информация о версии утилиты, сервера и id подключения.
Теперь давайте проверим правильность проведенной процедуры и «силу» своих прав. Например, выведем список всех таблиц, которые сейчас используются на сервере:
SHOW OPEN TABLES;
SHOW OPEN TABLES ; |
MySQL в командной строке Windows выдал нам лишь таблицы, входящие в состав системной базы. Это потому, что все данные, хранящиеся в ней, используются в работе запущенного экземпляра СУБД. Таким образом мы узнали, что сервер работает и что мы зашли на него с правами администратора, как и планировали.
Схема работы с оболочкой, устанавливаемой вместе с системой управления БД, немного отличается. Чтобы «прочувствовать» все это на себе, давайте активируем MySQL через эту командную строку.
После ее запуска появляется окно, в котором нужно сразу вводить пароль. В остальном весь процесс взаимодействия с утилитой одинаков.
Состав стандартного набора средств СУБД не ограничивается одним «экзешником» сервера. Их ассортимент можно просмотреть, заглянув в ту же папку, где «стоит» сервер БД. Для примера запустим утилиту dump MySQL через командную строку.
Сначала переходим на виртуальный диск, затем с помощью команды cd в ту папку, где хранится утилита. Напомню, что она предназначена для создания резервных копий баз.
На следующем шаге перед тем, как зайти в MySQL через командную строку, нам потребуется указать учетную запись и пароль к ней. Пример использования команды mysqldump:
mysqldump -uroot world>C:\Users\домашний\Desktop\dump\copy.sql
Чтобы команда сработала корректно, указанная директория на момент выполнения должна быть создана. Иначе утилита вернет сообщение, что такого пути не существует.
Также стоит отметить, что указанный пользователь MySQL в командной строке должен обладать правами на изменение базы или учетной записью администратора.
Подобным образом запускаются и другие утилиты, входящие в состав стандартного набора. С их помощью вы сможете выполнять все основные операции по администрированию СУБД: управлять ролями, создавать бэкапов, редактировать содержимое хранилищ. А также устанавливать службу MySQL из командной строки. В общем, все то, без чего сервер БД не заведется «с толкача».
Ниже предоставлен список наиболее полезных и часто используемых команд MySQL с примерами.
mysql в начале строки означает, что команда выполняется после входа вMySQL .
Символ # или $ в начале строки означает, что команда выполняется из командной строки.
Что бы проверить статус сервера MYSQL выполните:
для FreeBSD :
# service mysql-server status
в CentOS / RHEL :
# service mysqld status
MySQL из консоли, если сервер MySQL находится на том же хосте:
Что бы подключиться к серверу MySQL из консоли, если сервер MySQL находится на удаленном хосте db1.example.com:
$ mysql -u username -p -h db1.example.com
Создать базу данных на MySQL сервере:
Mysql create database
Показать список всех баз данных на сервере MySQL :
Mysql use ;
Отобразить все таблицы в базе данных:
Mysql show tables;
Просмотреть формат таблицы в базе:
Mysql describe ;
Удалить базу:
Mysql drop database ;
Удалить таблицу из базы:
Mysql drop table ;
Показать все содержимое таблицы:
Mysql SELECT * FROM ;
Отобразить столбцы и содержимое столбцов в выбранной таблице:
Mysql show columns from ;
Отобразить строки в определенной таблице, содержащие " whatever ":
Mysql SELECT * FROM WHERE = "whatever";
Отобразить все записи в определенной таблице, содержащие " Bob " и телефонный номер " 3444444:
Mysql SELECT * FROM WHERE name = " Bob " AND phone_number = " 3444444 ";
Отобразить все записи, НЕ содержащие имя " Bob " и телефонный номер " 3444444 ", отсортированные по полю phone_number:
Mysql SELECT * FROM WHERE name != " Bob " AND phone_number = " 3444444 " order by phone_number;
Показать все записи, начинающиеся с букв " bob " и телефонного номера " 3444444 " в определенной таблице:
Mysql SELECT * FROM WHERE name like " Bob %" AND phone_number = " 3444444 ";
Показать все записи, начинающиеся с букв " bob " и телефонного номера " 3444444 ", ограничиваясь записями с 1-ой до 5-ой:
Mysql SELECT * FROM WHERE name like " Bob %" AND phone_number = " 3444444 " limit 1,5;
Использование регулярных выражений ("REGEXP BINARY") для поиска записей. Например, для регистро-независимого поиска - найти все записи, начинающиеся с буквы А:
Mysql SELECT * FROM WHERE rec RLIKE "^ a ";
Показать все уникальные записи:
Mysql SELECT DISTINCT FROM ; mysql SELECT , FROM ORDER BY DESC;
Показать количество строк в таблице:
Mysql SELECT COUNT(*) FROM ;
Mysql SELECT SUM(*) FROM ;
Удаление столбца:
Mysql alter table drop column ;
Добавление колонки в базу данных:
Mysql alter table add column varchar (20);
Изменение имени столбца:
Mysql alter table change varchar (50);
Создать столбец с уникальным именем, что бы избежать дубликатов в названиях:
Mysql alter table add unique ();
Изменение размера столбца:
Mysql alter table modify VARCHAR(3);
Удаление столбца из таблицы:
Mysql alter table drop index ;
Mysql LOAD DATA INFILE " /tmp/filename.csv " replace INTO TABLE FIELDS TERMINATED BY "," LINES TERMINATED BY "n" (field1,field2,field3);
Создание нового пользователя - подключение к серверу MySQL под root, переключение к базе данных, добавление пользователя, обновление привилегий:
# mysql -u root -p mysql use mysql; mysql INSERT INTO user (Host,User,Password) VALUES("%"," username ", PASSWORD(" password ")); mysql flush privileges;
Изменений пользовательского пароля из консоли на удаленном хосте db1.example.org:
# mysqladmin -u username -h db1.example.org -p password " new-password "
Изменение пользовательского пароля из консоли MySQL - подключение под root, обновление пароля, обновление привилегий:
# mysql -u root -p mysql SET PASSWORD FOR " user "@" hostname " = PASSWORD(" passwordhere "); mysql flush privileges;
Восстановление/изменение пароля root сервера MySQL - остановка MySQL , запуск без таблиц привилегий, подключение под root, установка нового пароля, выход и перезапуск MySQL .
# /etc/init.d/mysql stop # mysqld_safe -skip-grant-tables & # mysql -u root mysql use mysql; mysql update user set password=PASSWORD(" newrootpassword ") where User="root"; mysql ; flush privileges; mysql quit # /etc/init.d/mysql stop # /etc/init.d/mysql start
Set a root password if there is on root password.
# mysqladmin -u root password newpassword
Обновление пароля root:
# mysqladmin -u root -p oldpassword newpassword
Установка права на подключение к серверу с хоста localhost с паролем " passwd " - подключение подroot, переключение к базе данных, установка привилегий, обновление привилегий:
# mysql -u root -p mysql use mysql; mysql grant usage on *.* to bob @localhost identified by " passwd "; mysql flush privileges;
Установка привилегий пользователю на использование базы данных - подключение под root, переключение к базе данных, установка привилегий, обновление привилегий:
# mysql -u root -p mysql use mysql; mysql INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ("%","databasename","username","Y","Y","Y","Y","Y","N"); mysql flush privileges;
Mysql grant all privileges on databasename .* to username @localhost; mysql flush privileges;
Обновление информации в базе данных:
Mysql UPDATE SET Select_priv = "Y",Insert_priv = "Y",Update_priv = "Y" where = user";
Удаление строки в таблице:
Mysql DELETE from where = "whatever";
Обновление привилегий в базе данных:
Mysql flush privileges;
Создать резервную копию (dump) всех баз данных в файл alldatabases.sql:
# mysqldump -u root -p password -opt ; /tmp/alldatabases.sql
Создать резервную копию одной базы данных в файл databasename.sql:
# mysql dump -u username -p password -databases databasename ; /tmp/databasename.sql
Создать резервную копию одной таблицы в файл databasename.tablename.sql:
# mysql dump -c -u username -p password databasename tablename ; /tmp/databasename.tablename.sql
Восстановление базы данных (или таблицы) из резервной копии:
# mysql -u username -p password databasename < /tmp/databasename.sql
маленькими буквами указаны имена столбцов;
ПРОПИСНЫМИ буквами - типы и атрибуты столцов;
в (скобках) - значение типа столбца.
Создать таблицу, пример 1:
mysql CREATE TABLE (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35), suffix VARCHAR(3), officeid VARCHAR(10), userid VARCHAR(15), username VARCHAR(8), email VARCHAR(35), phone VARCHAR(25), groups VARCHAR(15), datestamp DATE, timestamp TIME, pgpemail VARCHAR(255));Создать таблицу, пример 2:
Mysql create table (personid INT(50) NOT NULL AUTO_INTCREMENT PRIMARY KEY, firstname VARCHAR(35), middlename VARCHAR(50), lastname VARCHAR(50) default "bato");
Установка MySQL проходит довольно просто.Для начала нужно скачать и распаковать исходники (как это делать я думаю писать не стоит).Найти их можно на сайте MySQL в разделе -на любой вкус и для любой ОС,так как 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).
Управление сервером обычно осуществляется из командной строки.Насчет Linux я уже писал,а в Windows 95/98 откройте сеанс DOS и выполните следующие команды:
| cd mysqlbin mysqlbin>mysqld --standalone |
Эта команда запустит демон mysql в фоновом режиме.В Windows 95/98 не предусмотрен запуск mysqld в виде службы.Отсутствие сообщений при этом -хороший знак,значит все в порядке.
При запуске mysqld можно указывать следующие опции:
| -?, --help | Справка |
| -b, --basedir= | Путь к каталогу в котором установлен mysql |
| -h, --datadir | Путь к каталогу,в котором хранятся базы данных. |
| -l, --log= | Имя журнала транзакций |
| -L, --language= | Язык по умолчанию(обычно English). |
| -P, --port= | Порт для соединения. |
| --skip-grant-tables | Игнорировать таблицы привилегий. Это дает любому ПОЛНЫЙ доступ ко всем таблицам.Не следует предоставлять обычным пользователям разрешений на запуск mysqld. |
| --skip-name-resolve | Позволяет предоставлять доступ только тем хостам,чьи IP-адреса указаны в таблицах привилегий.Ипользуется для более высокого уровня защиты. |
| --skip-networking | Использовать подключения только через интерфейс localhost. |
| -V, --version | Вывести информацию о версии. |
Теперь можно попытаться войти в сервер.Для этого используется команда mysql.Изначально существует единственный пользователь,которому предоставляется право входа-root ,которая не имеет пароля.Первое,что нужно сделать-войти под именем root и установить для него пароль. Команда mysql может использовать следующие опции:
Примечание. Команды mysqld и mysql имеют еще некоторые опции,но в данный момент они особого интереса не представляют.
После установки пароля для root нужно перезагрузить сервер командой mysqladmin reload,чтобы изменения вступили в силу.После этого можно попробовать войти снова:
| mysqlbinmysql -u root -p mysql Enter password:******* |
Итак,вы успешно вошли в базу данных mysql,которая используется для администрирования сервера.Что же здесь находится?А находятся здесь 5 таблиц,которые ничем не отличаются от других таблий баз данных,за исключением того,что эти таблицы используются для предоставления доступа к базам данных и таблицам в них пользователям.Рассмотрим каждую из них.
Введите следующую команду,которая покажет таблицы в базе данных mysql:
mysql>show tables;
|
Кратко рассмотрим функции каждой из таблиц:
| 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;
|
1.Команда insert вставляет данные в таблицу,не забывайте завершать команды ";".
2.При вводе пароля используйте функцию password(),иначе пароль работать не будет!
3.Все пароли шифруются mysql,поэтому в поле Password вы видите абракадаьры.Это делается в целях безопасности.
4.Не есть хорошей практикой назначать привилегии пользователям в таблице user,так как в этом случае они являются глобальными и распространяются на все базы данных.Предоставляйте привилегии каждому пользователю к конкретной базе данных в таблице db,которая будет рассмотрена далее.
5.При задании имени хоста для входа через сеть рекомендуется явно указывать полное имя хоста,а не "%".В приведенном выше примере юзеру john разрешается вход на сервер со всех машин домена domain.com.Можно также указывать IP-адреса машин и маски подсетей для большей безопасности.
| 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 используется для расширения диапазона доступа в таблице 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 будет подробно рассмотрена в следующих разделах.
| Привилегия | Колонка | Где используется |
| select | Select_priv | таблицы |
| insert | Insert_priv | таблицы |
| update | Update_priv | таблицы |
| delete | Delete_priv | таблицы |
| index | Index_priv | таблицы |
| alter | Alter_priv | таблицы |
| create | Create_priv | БД,таблицы,индексы |
| drop | Drop_priv | БД или таблицы |
| grant | Grant_priv | БД или таблицы |
| references | References_priv | БД или таблицы |
| reload | Reload_priv | администрирование сервера |
| shutdown | Shutdown_priv | администрирование сервера |
| process | Process_priv | администрирование сервера |
| file | File_priv | доступ к файлам на сервере |
Select -используется для извлечения информации из таблиц.Select можно также использовать не имея разрешения на доступ к какой-либо БД,например,как калькулятор.
Insert -используется для вставки информации в таблицу.Например:
Delete -используется для удаления информации из таблиц.
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 существуют 3 специальных пользователя:monty,admin и dummy.
Суперпользователь.Может входить на сервер как с локального хоста,так и с любого хоста в сети.Имеет доступ ко всем базам данных и все привилегии,но если root может в первый раз войти без пароля,monty должен использовать пароль и должен быть добавлен в таблицы привилегий вручную.
Следует заметить,что следует внести данные как для localhost,так и для "%",то есть любого хоста в сети.
Пользователь,который может входить на сервер с локального хоста без пароля и которому назначаются административные привилегии Reload и Process .Пользователь admin может использовать команды mysqladmin reload, mysqladmin refresh и mysqladmin flush-* ,а также mysqladmin processlist .
Доступа к базам данных admin не имеет.Привилегии для доступа к определенным БД должны быть назначены индивидуально-или в таблице db,или командой GRANT.
Пользователь,который может входить на сервер без пароля,но только с локального хоста.Все глобальные привилегии устанавливаются в "N" кроме "USAGE",что позволяет dummy создавать пользователей без привилегий.Права доступа к базам данных должны быть заданы root"ом.
Создание базы данных в MySQL производится с помощью утилиты mysqladmin .Изначально существует только БД mysql для администратора и БД test,в которую может войти любой пользователь и которая по умолчанию пуста.Приведенный ниже пример иллюстрирует создание базы данных.
Или,находясь в другой базе данных,например в mysql ввести команду:
| mysql>use data1 Database changed. |
Теперь можно создавать таблицы и вводить информацию.
Прежде чем создавать таблицы,необходимо ознакомиться с тем,какие типы данных поддерживает MySQL.Все они представлены в нижеследующей таблице:
| BIGINT [(length)] | 8 байт целое | ||
| BLOB | Двоичный объект (максимальная длина 65535 байт) | ||
| CHAR(NUM) | Строка фиксированной длины (1 | DATE | Сохраняет информацию о дате. Использует формат "YYYY-MM-DD". Может модифицироваться как строка или число. YYYY-MM-DD ("- " может быть ЛЮБОЙ не цифрой) YY-MM-DD ("- " может бытьЛЮБОЙ не цифрой) YYMMDD YYMM Диапазон для этого типа данных от 0000-00-00 до 9999-12-31. В отличие от TIMESTAMP, DATE принимает годы и в виде двух цифр от 0000 до 0099. Это не очень полезно в большинстве случаев. Используйте задание лет четырьмя цифрами в полях типа DATE. Тип DATE имеет длину 4 байта. |
| DATETIME | Объединение типов DATE и TIME. Тип DATETIME идентичен типу TIMESTAMP со следующими исключениями: Когда запись вставляется в таблицу, содержащую поля типа DATETIME, поле DATETIME не изменяется. Диапазон для поля типа DATETIME: "0000-01-01 00:00:00" - "9999-12-31 23:59:59" при использовании в контексте строки, и "00000000000000" - "99991231235959" при использовании в контексте числа. Тип DATETIME имеет длину 8 байт. | ||
| DECIMAL (length,dec) | Десятичное число с плавающей запятой. | ||
| DOUBLE [(length,dec)] | Число (4 или 8 байт) двойной точности с максимальной длиной и фиксированном числом десятичных чисел. | ||
| FLOAT [(precision)] | Номер с плавающей запятой. FLOAT(4) и FLOAT одиночная точность. FLOAT(8) обеспечивает двойную точность. | ||
| FLOAT [(length,decimals)] | Число одиночной точности с максимальной длиной и фиксированном числом десятичных чисел (4 байта). | ||
| INT [(length)] | Целое (4 байта). | ||
| INTEGER [(length)] | Целое число 4 байта | ||
| LONGBLOB | Двоичный объект с максимальной длиной 2**32 байт. | ||
| MEDIUMBLOB | Двоичный объект с максимальной длиной 16777216 байт. | ||
| MEDIUMINT [(length)] | Целое (3 байта). | ||
| REAL [(length,dec)] | Идентично DOUBLE (8 байт). | ||
| SMALLINT [(length)] | Целое (2 байта). | ||
| TINYBLOB | Двоичный объект с максимальной длиной 255 байт. | ||
| TINYINT [(length)] | Целое число (1 байт). | ||
| VARCHAR(NUM) | Строка переменной длины (1 | TIME | Хранит информацию о времени. Использует формат "HH:MM:SS". Может использоваться как строка или число. MySQL тип TIME понимает следующий синтаксис. HH:MM:DD HHMMDD HHMM HH Данные типа TIME имеют длину 3 байта. |
| TIMESTAMP(NUM) | Автоматически изменяется при вставке/обновлении. Имеет формат YYMMDDHHMMSS или YYYYMMDDHHMMSS. Вы можете модифицировать поле TIMESTAMP при выполнении INSERT. Это полезно, когда Вы хотите установить произвольную дату/время для записи. В течение модификаций Вы не должны определять значение для вашего поля TIMESTAMP, или определять NULL как значение, для вставки. Иначе вы получите недопустимое значение для этого поля. Когда используете mysql с ODBC и Access Вы должны использовать значение 14 для NUM, поскольку это заставляет MySQL всегда использовать в годах четыре цифры. Значение 12 заставит MySQL использовать в году две цифры. Значение по умолчанию - 14. Обратите внимание, что в случае таблиц с несколькими полями TIMESTAMP только первое такое поле будет модифицироваться автоматически. |
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 также для загрузки данных из текстового файла.
Пример ввода данных вручную:
Что касается auto_increment в столбце emp_id ,это означает,что числовое значение этого столбца будет автоматически увеличиваться на единицу с каждой новой записью.То есть,если мы ввели значение 1001,то следующее будет 1002 и т.д.Значение в такой столбец вводится один раз для задания точки отсчета,а дальше сервер будет сам подставлять нужные значения.
Предположим,существует некоторый текстовый файл 123.txt,содержащий 2000 записей,которые нужно внести в таблицу.Нужно создать таблицу,имеющую такую же структуру и такое же число полей,как и файл(а также подходящие типы данных).Предположим,что поля в файле разделены запятыми.Кроме того,файл должен находиться в нужной базе данных. Вводим следующую команду:
LOAD DATA INFILE "123.txt" into table customers fields terminated by ",";
Это все.Данные из файла помещаются в таблицу.
Для модификации таблиц и данных в них используются команды update и alter table . Рассмотрим их действие на примере таблицы customers:
| emp_id | emp_name | emp_lname | address | phone |
|---|---|---|---|---|
| 1001 | John | Walker | New York | 1235468798 |
| 1002 | Bill | Smith | Chicago | 7650945237 |
| 1003 | Jack | Nicolson | Dallas | 9874325097 |
Пример действия команды alter table :
Можете проверить это сами:)
Изменение данных в таблицах производится с помощью команды 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 for user... Если это рядовой пользователь,то с этим может разобраться администратор,если же такое сообщение получает администратор,то это уже проблема. Очень часто при установке пароля для root администратор забывает использовать функцию password() ,а просто вводит:
update user set password="mamamia" where user="root";
После этого при попытке войти в базу данных он получает сообщение Access denied.Такой пароль не будет прочитан.Правильно нужно ввести:
update user set password=password("mamamia") where user="root";
Это очень важный шаг и необходимо быть внимательным.Единственный выход при этом в Windows-деинсталлировать сервер и установить по новой,благо еще ничего не настроено.
Если такую проблему испытывает пользователь,вероятно причина в неправильной настройке таблиц привилегий,в первую очередь та же установка пароля,или записи в таблицах db и host неверны.Рассмотрим это на простом примере:
|
|
Очень важный нюанс:в таблице 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;
Я не ставлю целью углубляться в изучение языка SQL,об этом вы можете прочитать в любом руководстве по SQL Server,MySQL в основном поддерживает все основные команды стандарта ANSI 92,но команда Select заслуживает того,чтобы посвятить ей отдельную главу. Команда Select используется для запросов к базе данных с целью извлечения из нее информации.Синтаксис команды следующий:
SELECT select_expression,... , ...] HAVING full_where_definition rows] ]
Как видно из вышеприведенного,вместе с командой Select используются ключевые слова,использование которых очень влияет на ответ сервера.Рассмотрим каждое из них.
Пропускает строки,в которых все выбранные поля идентичны,то есть устраняет дублирование данных.
Предложение команды 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;
При таком запросе будут выбраны все заказчики из Далласа и все заказчики,рейтинг которых меньше 200.В этом запросе оператор NOT применяется только к выражению rating >200.Можно сделать более сложный запрос:
SELECT * FROM Customers WHERE NOT(city = "Dallas" OR rating > 200);
В этом запросе NOT применен к обеим выражениям в скобках.В данном случае,сервер читает выражения в скобках,определяет, соответствует ли истине равенство city = "Dallas" или равенство rating > 200.Если любое условие верно, выражение Буля внутри круглых скобок верно. Однако, если выражение Буля внутри круглых скобок верно,предикат как единое целое неверен, потому что NOT преобразует верно в неверно и наоборот.То есть,будут выбраны все заказчики не находящиеся в Далласе и рейтинг которых меньше 200.
Оператор IN определяет набор значений в которое данное значение может или не может быть включено.Например,запрос
SELECT * FROM Salespeople WHERE city = "Barcelona" OR city = "London";
может быть переписан более просто:
SELECT * FROM Salespeople WHERE city IN ("Barcelona", "London");
IN определяет набор значений с помощью имен членов набора заключенных в круглые скобки и отделенных запятыми.Затем он проверяет различные значения указанного,пытаясь найти совпадение со значениями из набора. Если это случается, то предикат верен. Когда набор содержит значения номеров а не символов, одиночные кавычки опускаются.
Оператор BETWEEN похож на оператор IN. В отличии от определения по номерам из набора, как это делает IN, BETWEEN определяет диапазон, значения которого должны уменьшаться что делает предикат верным. Вы должны ввести ключевое слово BETWEEN с начальным значением, ключевое AND и конечное значение. В отличие от IN, BETWEEN чувствителен к порядку, и первое значение в предложении должно быть первым по алфавитному или числовому порядку.Например:
SELECT * FROM Salespeople WHERE comm BETWEEN .10 AND .12;
SELECT * FROM Salespeople WHERE city BETWEEN "Berlin" AND "London";
LIKE применим только к полям типа CHAR или VARCHAR, с которыми он используется чтобы находить подстроки. Т.е. он ищет поле символа чтобы видеть, совпадает ли с условием часть его строки.В качестве условия он использует групповые символы(wildkards) - специальные символы которые могут соответствовать чему-нибудь. Имеются два типа групповых символов используемых с LIKE:
символ подчеркивания (_) замещает любой одиночный символ.
Если мы зададим следующие условия:
SELECT * FROM Customers WHERE fname LIKE "J%";
то будут выбраны все заказчики,чьи имена начинаются на J:John,Jerry,James и т.д.
Агрегатная функция,производит подсчет значений в столбце или числа строк в таблице.При работе со столбцом использует DISTINCT в качестве аргумента:
SELECT COUNT (DISTINCT snum) FROM Orders;
При подсчете строк имеет синтаксис:
SELECT COUNT (*) FROM Customers;
Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого поля, и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Например, предположим что вы хотите найти наибольшую сумму приобретений полученную каждым продавцом. Вы можете сделать раздельный запрос для каждого из них, выбрав MAX () из таблицы для каждого значения поля. GROUP BY позволит Вам поместить их все в одну команду:
SELECT snum, MAX (amt) FROM Orders GROUP BY snum;
HAVING определяет критерии используемые чтобы удалять определенные группы из вывода, точно также как предложение WHERE делает это для индивидуальных строк.Например:
SELECT cid,cname,price,max(price) //max()-это тоже агрегатная функция FROM customers HAVING max(price)>500;
HAVING действует сходно с WHERE,но с WHERE нельзя использовать агрегатные функции.
Эта команда упорядочивает вывод запроса согласно значениям в том или ином количестве выбранных столбцов. Многочисленные столбцы упорядочиваются один внутри другого,также как с GROUP BY.
Используется в подзапросах.
SELECT cnum, cname, city FROM Customers WHERE EXISTS (SELECT * FROM Customers WHERE city = " San Jose");
Он берет подзапрос как аргумент и оценивает его как верный если тот производит любой вывод или как неверный если тот не делает этого.Этим он отличается от других операторов предиката, в которых он не может быть неизвестным. Например, мы можем решить, извлекать ли нам некоторые данные из таблицы Заказчиков если, и только если, один или более заказчиков в этой таблице находятся в San Jose.
UNION отличается от подзапросов тем что в нем ни один из двух (или больше) запросов не управляются другим запросом. Все запросы выполняются независимо друг от друга, а уже вывод их - объединяется.Например:
SELECT snum, sname FROM Salespeople WHERE city = "London" UNION SELECT cnum, cname FROM Customers WHERE city = "London";
Предложение UNION объединяет вывод двух или более SQL запросов в единый набор строк и столбцов.
DESC-DESCEDENT,вывод данных в обратном порядке(по алфавиту и численным значениям).По умолчанию используется ASC.
Ну вот вкратце и все.MySQL поддерживает почти все основные команды SQL Server,так что более подробно о команде SELECT вы можете прочитать в любом учебнике по языку SQL.
В состав дистрибутива MySQL входят следующие утилиты:
Утилита для администрирования сервера.Может использоваться администратором,а также некоторыми пользователями,которым предоставлены определенные привилегии,например-Reload_priv,Shutdown_priv,Process_priv и File_priv .Данная команда может использоваться для создания баз данных,изменения пароля пользователя(администратор может изменить пароль любому пользователю,а рядовой пользователь-только свой собственный),перезагрузки и остановки сервера,просмотра списка процессов,запущенных на сервере. Mysqladmin поддерживает следующие команды:
Пример использования mysqladmin для изменения пароля:
mysqladmin -u bob password srawberry
Следует заметить,что в случае использования mysqladmin для установки пароля,не требуется использование функции password().Mysqladmin сам заботится о шифровании пароля.
Используется для проверки привилегий пользователя для доступа к конкретной базе данных. Общий синтаксис:
mysqlaccess опции
Полезная утилита для проверки прав доступа пользователя,если он получает сообщение Access denied,при попытке соединиться с базой данных.
Опции:
Используется,чтобы показать,с какими базами данных работает сервер,какие таблицы содержит каждая БД и какие колонки есть в каждой таблице.Синтаксис:
mysqlshow [опции] ]]
Mysqlshow может использовать следующие параметры:
Если ввести mysqlshow без аргументов,будут показаны все базы данных,если указать имя БД, будут показаны все таблицы в ней.
Используется для для создания дампа содержания базы данных (резервной копии).Можно записать содержимое базы данных в файл.Синтаксис:
mysqldump [опции] ]]
Mysqldump может использовать следующие параметры:
С помощью этой утилиты можно осуществлять восстановление таблиц,управление таблицами и сбор статистики.Синтаксис:
isamchk [-?adeiqrsvwzIV] [-k #] [-O xxxx=size] [-Si] [-Sr #] [-O keybuffer=#]
[-O readbuffer=#] [-O writebuffer=#] [-O sort key blocks=#] files
Isamchk используется для проверки непротиворечивости таблиц и устранения проблем,которые это может вызвать. Если пользователь часто удаляет и добавляет данные в таблицах,эта утилита будет очень полезной. Isamchk может использовать следующие опции:
При использовании опции -r нужно остановить mysqld.Перед началом работы нужно перейти в каталог,где находится таблица,нуждающаяся в проверке или ремонте.
Журнал транзакций,используется с опцией --log-isam=file_name демона mysqld.В файл записываются все изменения,происходящие в базе данных.Может использоваться для восстановления базы данных. Если есть резервная копия базы данных до того,как был сгенерирован isam-журнал,то информацию можно легко восстановить. Если нет копии,то нужно использовать все журналы с начала работы базы данных.
Cкрипт для запуска демона mysqld в Unix-версиях.Выполняется при начальной загрузке системы. Для автоматического запуска нужно добавить запись в один из файлов инициализации системы.
| Создание сайтов | | |
Можно не только через PHP. В комплекте с MySQL поставляется консольный клиент для подключения в серверу MySQL. Консольный — это означает, что у программы нет оконного интерфейса, а есть только интерфейс командной строки, в котором обращение к базам данных выполняется с помощью SQL запросов.
Команды для подключения к MySQL в командной строке, а уж тем более сами SQL запросы идентичны и для . В дальнейшем для краткости я буду писать «MySQL», но везде я имею в виду «MySQL или MariaDB», поскольку в данном случае разницы между ними нет.
Подключившись через командную строку к СУБД MySQL можно выполнять самые разные задачи: просматривать базы данных и их таблицы, отправлять запросы к базам данных и получать результаты выполнения этих запросов.
Для подключения к серверу MySQL используется команда клиентская программа, которая называется mysql.exe. Её нужно запустить, но сделать нужно не двойным кликом, как обычную программу, а из командной строки.
Начнём с того, что нажмём сочtтание клавиш Win+r и выполним
Откроется командная строка.
Теперь нам нужно перейти в папку с файлом mysql.exe . Эта папка bin в директории, где установлена ваша СУБД MySQL. К примеру, у меня MariaDB, которая установлена в папке C:\Server\bin\mariadb\ . Следовательно, интересующий нас файл находится в каталоге C:\Server\bin\mariadb\bin\ . Для перехода в этот каталог используется команда cd следующим образом:
Cd C:\Server\bin\mariadb\bin\
В этой команде замените C:\Server\bin\mariadb\bin\ на правильный для вашей системы путь
Теперь запустим файл mysql.exe. Необязательно указывать расширение файла.exe — система сама догадается, что мы имеем ввиду. Также нам нужно использовать опцию -u и -p . После первой опции нужно указать имя пользователя — root . После второй опции — пароль для пользователя. В моём случае пароль не установлен, поэтому я запускаю без опции -p :
Mysql -u root
Может показаться, что мало что изменилось, но новое приглашение командной строки
MariaDB [(none)]>
говорит о том, что мы подключены к серверу MySQL. Точнее, в моём случае мы подключены к серверу MariaDB.
Чтобы посмотреть список баз данных, введём команду:
SHOW DATABASES;
Чтобы последующие запросы отправлялись к определённой базе данных (например, к test), используется команда USE :
USE test;
Чтобы посмотреть таблицы в выбранной базе данных запустите команду:
SHOW TABLES;
Создадим какую-нибудь таблицу:
CREATE TABLE AuthorsTBL (AuthorID INT NOT NULL AUTO_INCREMENT, AuthorName VARCHAR(100), PRIMARY KEY(AuthorID));
И вновь посмотрим содержимое базы данных test.
