OpenSource в заметках
Некоторые рождены быть администраторами баз данных, а другие поручают им администрирование своих БД. Если вы относитесь ко второй группе, но вы нуждаетесь лишь в элементарных операциях, вроде создания или резервного копирования баз данных, то знайте, что выполнять эти операции намного проще, чем кажется. Давайте рассмотрим самые простые элементы администрирования баз данных MySQL.
Когда я говорю «простые», я и имею ввиду простые. Эта статья не сделает из вас профессионального администратора баз данных MySQL. Однако отсюда вы сможете почерпнуть некоторые знания о MySQL, которых вам вполне может хватить для решения задач, вроде настройки WordPress или создания резервной копии базы данных MySQL.
Вы также можете выполнять все эти операции при помощи phpMyAdmin, что, впрочем, и делают множество людей. Так зачем же напрягаться, забираясь в дебри командной строки? Первой причиной использования командной строки является отсутствие необходимости устанавливать и поддерживать дополнительное программное обеспечение, особенно, если вы общаетесь с MySQL нечасто.
Также, умение работать с MySQL из командной строки даёт вам возможность работать с ней и из скриптов. Вы же не можете работать из скриптов с phpMyAdmin (некоторые могут, но это уже больше напоминает извращения)? Так что, если вы хотите, например, организовать еженедельное копирование ваших баз данных, то умение работать с MySQL из командной строки будет весьма кстати.
В этой статье а освещу лишь основные моменты работы с MySQL: создание баз данных и таблиц, создание пользователей, удаление баз данных и отдельных таблиц, восстановление баз данных и работу с их резервными копиями. Подразумевается, что MySQL у вас уже установлен и вы готовы начать работу с командами.
Подключение
Итак, давайте начнём с подключения к MySQL при помощи команды:
Вместо username введите имя существующего в вашей инсталляции MySQL пользователя.
Теперь, находясь в оболочке mysql-клиента, можно увидеть список доступных БД при помощи команды:
Обратите на точку с запятой в конце. Она сообщает MySQL о том, что команда завершена. Если строку не завершить этим символом, то после нажатия Enter MySQL будет ожидать ввода продолжения команды в новой строке. Такое поведение MySQL часто оказывается полезным при вводе нескольких команд за один раз.
Также имейте ввиду, что MySQL хранит историю команд. Нажмите стрелку вверх и вы увидите введённую вами ранее команду.
В случае, если вам необходимо начать работу с какой-то конкретной базой данных, воспользуйтесь командой:
заменив databasename на имя нужной вам базы данных. Нужной вам базы данных не существует и вам необходимо её создать? Нет проблем!
Создание баз данных и таблиц
Создать базы данных очень просто. Просто введите в оболочке MySQL команду:
заменив dbname на имя создаваемой БД.
Также, вы можете воспользоваться программой mysqladmin, чтобы создать новую БД:
Этот способ быстрее, если вам нужно лишь создать новую базу данных и, конечно, медленнее, если после создания БД вам нужно ввести ещё серию команд. Отчасти такому замедлению способствует необходимость каждый раз вводить пароль пользователя MySQL. В принципе, вы можете создать файл
/.my.cnf, поместив в него ваше имя пользователя и пароль MySQL, но я не рекомендую вам этого делать, поскольку это значительно снизит уровень безопасности.
Утилиту mysqladmin вы можете использовать для решения многих административных задач. Например, вы можете запускать, останавливать и перезагружать сервер MySQL. Ну, к примеру, остановить MySQL-сервер по какой-то причине? Воспользуйтесь командой:
Если желаете ознакомиться со списком всех доступных команд mysqladmin, воспользуйтесь ключом —help:
Чтобы создать таблицу в БД, используется инструкция CREATE TABLE table_name, после которой следует описание создаваемой таблицы. Поскольку каждая таблица обычно имеет несколько полей для хранения информации различных типов, то команды создания таблиц обычно получаются весьма длинными. Вот, например, команда, создающая таблицу для WordPress:
В этом примере я опустил некоторые инструкции ради краткости, однако основные моменты здесь отображены. После инструкции CREATE TABLE вы видите инструкции с описанием полей таблицы. Описание поля таблицы состоит из имени поля, типа данных, которые буду в нём храниться, а также определения, может ли поле иметь значение NULL (то есть, не содержать данных). Инструкции описания полей таблицы разделены запятыми, а последняя строка указывает MySQL, какой движок (в данном примере — MyISAM) использовать для работы с таблицей, а также опции, специфичные для таблицы в целом.
Не волнуйтесь, если для вас всё это кажется тарабарщиной. Всё, что вы сейчас увидели в примере выше фактически создаёт т. н. схему базы данных. Если вы пользуетесь приложениями, в комплекте которых поставляется схема БД для них, то вам вовсе необязательно знать, что именно делают инструкции, описанные в схеме.
Создание и настройка пользователя
Прежде, чем начать, вы должны знать пароль пользователя root в вашей инсталляции MySQL. Это не тот же самый root, который присутствует в вашей операционной системе. Чтобы не использовать учётную запись root для работы со всеми БД в вашей системе, вы можете создать отдельного пользователя. Например, чтобы работать с БД моего WordPress, я использую отдельного пользователя, наделённого не столь высокими привилегиями, как пользователь root.
Создать пользователя очень просто. Войдите в оболочку MySQL и дайте команду:
заменив bob на имя нужного вам пользователя, а password на пароль пользователя.
Однако, созданный пользователь не сможет делать ничего полезного до тех пор, пока ему не предоставить необходимые привилегии в базам данных. Например, если вы хотите дать пользователю bob полный доступ к базе данных wordpress_db, достаточно дать команду:
Вы можете ограничить перечень операций, которые будет разрешено выполнять указанному пользователю. Например, вы можете разрешить лишь операции SELECT, INSERT и DELETE. Подробнее узнать об этом вы можете в соответствующем разделе руководства MySQL.
Создание дампа базы данных
Базы данных MySQL хранятся на диске в двоичном виде в файлах с данными, индексных файле и файлах, описывающих структуру таблиц. Таким образом, каждая БД MySQL хранится в своём каталоге, расположенном в /var/lib/mysql, плюс три файла для каждой таблицы и файл .opt, содержащий параметры БД.
Конечно, можно организовать резервное копирование баз данных, путём простого копирования двоичных файлов, описанных выше, однако гораздо удобнее работать с дампами БД. Как это делается? Очень просто. MySQL имеет утилиту, называемую mysqldump. Например, чтобы сделать дамп БД wordpress_db, вы можете использовать команду:
которая сделает дамп БД wordpress_db в текстовом виде и сохранит его в файле wordpress.sql. Созданный текстовый файл будет содержать SQL-инструкции, выполнив которые, можно будет воссоздать базу данных, и будет иметь больший размер, нежели размер двоичных файлов БД. Например, размер моей БД WordPress составляет 39 мегабайт, в то время как дамп этой БД получился размером в 41 мегабайт. Это несколько больше размера БД в двоичном виде, поскольку дамп содержит некоторую избыточную информацию.
Также mysqldump может создавать дампы не всей БД, а отдельных таблиц.
Восстановить БД из дампа ещё проще. Давайте представим, что необходимо восстановить базу данных с именем wordpress_db из файла wordpress.sql, который мы создали ранее. Всё, что нам понадобится для этого, это имя базы данных, путь к файлу дампа и имя пользователя MySQL, имеющего достаточные привилегии для этого:
База данных, дамп которой вы собираетесь загрузить, должна существовать к моменту восстановления. Так, если вы переносите базы данных с одного хоста на другой при помощи дампов, вам нужно прежде создать пустые базы данных, и только потом загружать в них дампы.
Итоги
Конечно, для полноценного администрирования MySQL вы должны знать гораздо больше. Как я уже говорил, можно вполне неплохо администрировать MySQL и при помощи phpMyAdmin. В будущих статьях я расскажу вам о нём, а также о более продвинутом использовании MySQL. А пока, я надеюсь, это руководство станет полезным для пользователей, которых интересуют лишь простые операции с MySQL, знание которых не требует квалификации администратора баз данных.
Команды MySQL в Linux
Ниже предоставлен список наиболее полезных и часто используемых команд MySQL с примерами.
mysql в начале строки означает, что команда выполняется после входа вMySQL.
Символ # или $ в начале строки означает, что команда выполняется из командной строки.
Что бы проверить статус сервера MYSQL выполните:
для FreeBSD:
в CentOS / RHEL:
Что бы подключиться к серверу MySQL из консоли, если сервер MySQL находится на том же хосте:
Работа с базами, таблицами — просмотр, удаление, редактирование записей. Консоль
Создать базу данных на MySQL сервере:
Показать список всех баз данных на сервере MySQL:
Отобразить все таблицы в базе данных:
Просмотреть формат таблицы в базе:
Удалить таблицу из базы:
Показать все содержимое таблицы:
Отобразить столбцы и содержимое столбцов в выбранной таблице:
Отобразить строки в определенной таблице, содержащие » whatever «:
Отобразить все записи в определенной таблице, содержащие » Bob » и телефонный номер » 3444444 :
Отобразить все записи, НЕ содержащие имя » Bob » и телефонный номер » 3444444 «, отсортированные по полю phone_number :
Показать все записи, начинающиеся с букв » bob » и телефонного номера » 3444444 » в определенной таблице:
Показать все записи, начинающиеся с букв ‘ bob » и телефонного номера » 3444444 «, ограничиваясь записями с 1-ой до 5-ой:
Использование регулярных выражений ( «REGEXP BINARY» ) для поиска записей. Например, для регистро-независимого поиска — найти все записи, начинающиеся с буквы А :
Показать все уникальные записи:
Показать количество строк в таблице:
Подсчитать количество столбцов в таблице:
Добавление колонки в базу данных:
Изменение имени столбца:
Создать столбец с уникальным именем, что бы избежать дубликатов в названиях:
Изменение размера столбца:
Удаление столбца из таблицы:
Загрузка файла CSV в таблицу:
Пользователи, пароли сервера MySQL — добавление, изменение пользователей и паролей. Консоль
Создание нового пользователя — подключение к серверу MySQL под root, переключение к базе данных, добавление пользователя, обновление привилегий:
Изменений пользовательского пароля из консоли на удаленном хосте db1.example.org :
Изменение пользовательского пароля из консоли MySQL — подключение под root, обновление пароля, обновление привилегий:
Восстановление/изменение пароля root сервера MySQL — остановка MySQL, запуск без таблиц привилегий, подключение под root, установка нового пароля, выход и перезапуск MySQL.
Set a root password if there is on root password.
Обновление пароля root:
Установка права на подключение к серверу с хоста localhost с паролем » passwd » — подключение подroot, переключение к базе данных, установка привилегий, обновление привилегий:
Установка привилегий пользователю на использование базы данных — подключение под root, переключение к базе данных, установка привилегий, обновление привилегий:
Обновление информации в базе данных:
Удаление строки в таблице:
Обновление привилегий в базе данных:
Резервные копии — создание, восстановление бд . Консоль
Создать резервную копию (dump) всех баз данных в файл alldatabases.sql :
Создать резервную копию одной базы данных в файл databasename.sql :
Создать резервную копию одной таблицы в файл databasename.tablename.sql :
Восстановление базы данных (или таблицы) из резервной копии: