Mysql команды пакета mysql


Содержание

Linux-in.com

Список команд MySQL на каждый день

На данный момент MySQL является одной из самых распространённых и популярных систем управления базами данных в интернете. Для активных пользователей данной СУБД мы сформировали подборку самых необходимых и популярных команд MySQL, что поможет оптимизировать вашу работу в сети.

Начнём с базовых понятий программирования данной системе.
Комбинация mysql> в начале строки показывает, что задача осуществляется после входа в систему MySQL.
В то же время символ # или $ в начале строки указывает на происхождение сигнала из командной строки.

Для проверки статуса сервера MYSQL вам необходимо выполнить следующие операции:

для FreeBSD:

в Linux CentOS / RHEL:

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

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

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

Для просмотра перечня всех баз данных на сервере MySQL — вам понадобится

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

Отображение всех таблиц вам обеспечит операция:

Для просмотра формата таблицы в базе вам необходимо использовать:

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

Если же из базы необходимо удалить только таблицу – здесь вам поможет операция:

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

При использовании комбинации user@localhost [(none)]> SELECT * FROM [table name] вы сможете просмотреть всё содержимое таблицы;
Если вы хотите поместить столбцы и их содержимое в выбранной таблице — пропишите:

Если же вы хотите отразить строки в конкретной таблице, что содержат “what“:

Вам понадобилось поместить все данные, содержащие имя“Ted” и номер “5777 в конкретной таблице – не проблема:

А теперь попробуем сделать то же самое с данными, НЕ содержащими имя “Ted” и номер “5777“, отсортированные по полю phone_number:

Следующим шагом покажите все записи, начинающиеся с ‘ted” и телефонного номера “5777” в определенной таблице используя следующие данные:

Усложняем поставленную задачу и показываем все записи, начинающиеся с букв ‘ted” и телефонного номера “5777“, ставим при этом ограничения с первой до пятой строки:

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

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

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

Для подсчёта количества строк и столбиков в таблице, вам понадобится следующее:

Удаление колонки или строки осуществляется при помощи операций:

Для создания нового столбца в БД, нужно применить:

В случае его переименования воспользуйтесь следующими данными:

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

Весьма удобной и часто применяемой является функция импорта файла CSV в табличную базу. Данную операцию позволит нам выполнить строка:

Создание пользователя и настройка пароля в MySQL является одной из основных и первоочередных задач.

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

Внести изменения в пароль пользователя из консоли на удаленном хосте вам поможет:

Для обновления root пароля вам понадобится нижеуказанная простая комбинация:

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

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

Для возобновления/коректировки пароля root сервера MySQL — вам понадобится далее приведённый ряд команд, отражающий остановку MySQL, запуск без таблиц привилегий, подключение под root, установку нового пароля, выход и перезапуск MySQL.

Права на подключения к серверу пользователя с хоста localhost, используя пароль «pass» выполнете следующее — войдите из под root, переключение к базе данных, установка привилегий, обновление привилегий:

В случае необходимости присвоения пользователю привилегий на пользование БД — войдите из под root, переключитесь к базе данных, установите привилегии и обновите их:

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

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

Рассмотрим на примере.
Резервное копирование базы данных(бекап) в файл производится с помощью:

При создании резервной копии таблицы в файл применяем:

В случае необходимости восстановления базы данных из резервной копии вам на помощь придёт:

Работа в системе MySQL это возможность адаптации ваших баз данных в соответствии с потребностями. Приведённые ключевые понятия взаимодействия с MySQL помогут вам в решении базовых задач и станут хорошей основой для дальнейшего управления базами данных.

Записки IT специалиста

Технический блог специалистов ООО»Интерфейс»

  • Главная
  • Основы администрирования MySQL при помощи командной строки

Основы администрирования MySQL при помощи командной строки

  • Автор: Уваров А.С.
  • 23.02.2020

Популярная система управления базами данных MySQL широко применяется для различных нужд, в первую очередь как стандарт де-факто в области интернет хостинга. Не менее широко распространен пакет для управления данной СУБД — phpMyAdmin. Без сомнения, это хороший, удобный продукт, но очень часто случается так, что навыки работы с этим продуктом полностью заменяют навыки работы с самой СУБД. Поэтому в данном материале мы решили познакомить наших читателей с тем, как выполнять основные задачи администрирования MySQL из командной строки.

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

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

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

Управление базами данных и пользователями в MySQL

Если танцевать следует начинать от печки, то работу с СУБД следует начинать с создания баз данных и пользователей этих баз. Задачи по сути своей простые и прекрасно, а главное — просто, решаются из консоли. Для работы с сервером MySQL предназначена одноименная утилита mysql, работа с которой происходит в интерактивном режиме, поэтому вначале подключимся к серверу:

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

Работа в данной среде имеет свои особенности: каждая команда должна завершаться символом ; или \g, о чем, кстати, написано в первой строке приветствия. Выход из данного режима осуществляется командой:

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

Для начала посмотрим список баз:

Как говорится, без лишних подробностей, но для большинства административных задач этого достаточно:

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

Команда select выбирает указанные колонки user, host, опция from указывает откуда мы их выбираем, а именно из таблицы user базы данных mysql.

Первая колонка указывает пользователя, вторая — хост, на котором данному пользователю разрешены подключения, % — обозначает любое значение. Обратите внимание, что если ivanov имеет в настройках localhost, то при подключении к mysql по адресу 127.0.0.1 он не сможет получить доступ, это нужно учитывать, когда вы указываете данные подключения к серверу СУБД в своих приложениях.

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

Особых комментариев здесь не требуется. Например, ivanov имеет права на базы с префиксом ivanov_ при подключении через localhost, а andrey на базы с префиксом andrey_ на любом хосте. Посмотреть привилегии пользователя можно командой:

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

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

Подробный разбор системы прав MySQL выходит далеко за пределы данной статьи, скажем только что ALL PRIVELEGES дает пользователь все права на свои базы, но не позволяет управлять правами доступа для других пользователей. Для этого используется набор прав ALL PRIVELEGES WITH GRANT OPTION, который имеет по умолчанию root. Для простого пользователя такой набор прав избыточен.

Попробуем создать нового пользователя:

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

Затем можем выставлять права по своему усмотрению, например, выдача полных прав на базы с шаблоном имени petrov_:

Обратите внимание, что шаблон следует обернуть символами грависа (`), которые расположены на клавише с русской буквой Ё.

Выдать права на отдельную базу можно так:

Для отбора прав служит команда REVOKE, которая имеет аналогичный синтаксис, только to (кому), заменяем на from (у кого). Например:

Для того чтобы MySQL сервер применил права следует заставить его перезагрузить кэш привилегий командой:

Также вам может понадобиться сменить пароль пользователя:

Переименовать его, причем переименование не обязательно обозначает смену именно имени пользователя, можно изменить как имя, так и хост, в любых комбинациях:

Ну и наконец удалить учетную запись:

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

Это создаст базу данных с кодовой страницей и кодировкой сравнения по умолчанию. Если сервер СУБД отдельно не настраивался, то такой кодировкой скорее всего будет latin1_swedish_ci, что в ряде случаев может вызвать проблемы, если не сейчас, то в дальнейшем, поэтому правилом хорошего тона будет явно указывать кодировку при создании базы. Для UTF-8 это будет так:

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

Проверка, оптимизация, исправление ошибок баз данных MySQL

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

Для проверки базы данных выполните, где andrey_drupal8 — имя базы:

Сразу все базы можно проверить командой :

А так как весь вывод на экран скорее всего не поместится, то есть смысл перенаправить его команде less:

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

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

Илон Маск рекомендует:  Тег code

Для оптимизации используйте ключ —-optimize, оптимизировать можно отдельную базу или несколько, для этого перечислите их после ключа —databases:

а также сразу все:

Выгрузка и загрузка дампов БД MySQL

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

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

Начнем с создания дампов, для этих целей используется утилита mysqldump, синтаксис которой повторяет синтаксис mysqlcheck. Для выгрузки дампа используйте команду:

Чтобы выгрузить сразу несколько баз используйте ключ —databases или —all-databases для создания дампа сразу всех баз. Вывод команды следует направить в файл и указать его расположение, в нашем случае это файл drupal8.sql в домашней директории. Также можно передать вывод по конвейеру архиватору и получить сразу архив:

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

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

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

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

В этом случае каждая база будет загружена в свой источник, если база источник не существует, то она будет создана.

Как видим, создание и восстановление дампов при помощи командной строки происходит буквально в одну строку и гораздо проще, и быстрее, чем с использованием phpMyAdmin или подобных ему инструментов.

Восстановление забытого пароля root MySQL

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

Затем запускаем ее в безопасном режиме с пропуском таблиц привилегий:

Обратите внимание, что после выполнения данной команды приглашение командной строки пропадет, останется один мигающий курсор. Пишем прямо туда:

и попадаем в среду mysql с правами root без ввода пароля.

Нам кажется, что многие уже догадались, что делать дальше, однако выполнить set password в данном режиме не получится, поэтому надо идти другим путем. Вспоминаем, что информация о пользователях хранится в таблице user служебной БД mysql. После чего выполним следующий запрос:

Важно! В новых версиях MySQL вместо колонки password таблицы user используется колонка authentication_string, поэтому вместо set password следует указывать set authentication_string.

Данный запрос установит новый пароль newpassword для всех строк в колонке user которых значится root.

Обновим кэш привилегий:

Выйдем из режима:

Остановим службу и запустим в обычном режиме:

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

Все о WEB программировании

WEB программирование от А до Я

Заказать сайт:

Социальные сети:

Партнеры:

Основные команды MySQL

Доброго времени суток. Очень часто приходится работать с MySQL через консольный клиент. Но бывают команды, которые используются чуть реже. И поэтому их синтаксис забывается. Да, знаешь есть такая, но ее синтаксис не помнишь. Лезешь в google и ищешь. Поэтому и решил выложить список основных консольных команд MySQL

Список основных консольных команд MySQL для работы с базами данных и таблицами:

  • Подключение кMySQL, которая находится на локальной машине

mysql -u username –p

  • Подключение кMySQL, которая находится на удаленной машине

mysql -u username -p -h example.com

  • Просмотр всех баз данных
  • Создание новой базы данных

CREATE DATABASE [название базы данных];

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

USE [название базы данных];

DROP DATABASE [название базы данных];

  • Посмотреть все таблицы в базе данных
  • Посмотреть формат таблицы

DESCRIBE [название таблицы];

  • Создание новой таблицы

CREATE TABLE [название таблицы] (список столбцов);

  • Удаление таблицы

DROP TABLE [название таблицы];

  • Удаление данных из таблицы

TRUNCATE TABLE [название таблицы]

  • Показать все содержимое таблицы

SELECT * FROM [название таблицы];

  • Показать столбцы и значения из таблицы

SHOW COLUMNS FROM [название таблицы];

  • Показать все уникальные значения из таблицы

SELECT DISTINCT [название столбца] FROM [название таблицы];

  • Количество строк в таблице

SELECT COUNT(*) FROM [название таблицы];

  • Сумма значений

SELECT SUM ([название столбца]) FROM [название таблицы];

  • Наибольшее значение среди найденных

SELECT MAX ([название столбца]) FROM [название таблицы];

  • Наименьшее значение среди найденных

SELECT MIN ([название столбца]) FROM [название таблицы];

  • Среднее значение среди найденных

SELECT AVG ([название столбца]) FROM [название таблицы];

  • Удаление столбца из таблицы

ALTER TABLE [название таблицы] DROP COLUMN [название столбца];

  • Добавление колонки в таблицу

ALTER TABLE [название таблицы] ADD COLUMN [название столбца] varchar (20);

  • Загрузка файлаCSV в таблицу

LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’ (field1,field2,field3)

Список консольных команд для работы с пользователями MySQL:

  • Добавление пользователяMySQL на локальной машине

Заходим под root: mysql -u root –p

Переходим в базу данных mysql: USE mysql;

Вставляем запись: INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’, PASSWORD(‘password’));

Перезагружаем таблицу назначения привилегий: flush privileges;

  • Изменяем пароль для пользователяroot

Не надо подключаться к MySQL

mysqladmin -u root password new_password

  • Изменяем пароль для пользователя

SET PASSWORD FOR [пользовтаель]@»%» = PASSWORD([пароль]);

  • Установка прав доступа для пользователя

Переходим к базе данных: use 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’);

Перегружаем таблицу привилегий: flush privileges;

Резервное копирование и восстановление:

  • Создание резервной копии всех баз данных с данными

mysqldump -u root -p password —opt >/tmp/dump.sql

тут используется параметр opt, который значительно ускоряет создание dump

  • Создание резервной копии одной базы данных

mysqldump -u username -p password —databases [название базы данных]>/tmp/dump.sql

  • Создание резервной копии одной таблицы из базы данных

mysqldump -c -u username -p password [название базы данных] [нзвание таблицы] > /tmp/databasename.tablename.sql

  • Восстановление базы данных из резервной копии

Глава 8

Теперь, когда вы изучили основы управления данными с помощью обычных файлов и затем посредством простой, но очень быстрой базы данных dbm, можно перейти к полнофункциональному средству работы с данными: СУРБД или системе управления реляционной базы данных (Relational Database Management System, RDBMS).

Два самых известных приложения СУРБД с открытым исходным кодом — это PostgreSQL и MySQL, хотя существует и множество других. Есть также много коммерческих СУРБД, таких как Oracle, Sybase и DB2, все они многофункциональны и могут действовать на различных платформах. Работающая только под управлением ОС Windows система Microsoft SQL Server — еще одно популярное средство на коммерческом рынке СУБД. У всех этих программных продуктов есть свои достоинства, но с учетом занимаемого пространства и принадлежности к программному обеспечению с открытым кодом авторы книги сосредоточились исключительно на СУРБД MySQL.

Появление MySQL восходит к 1984 г., а коммерческий вариант был разработан и поддерживается под покровительством компании MySQL АВ в течение последних нескольких лет. Поскольку СУРБД MySQL — это программное обеспечение с открытым исходным кодом, условия его использования часто смешивают с аналогичными условиями в других проектах с открытым программным кодом. Несмотря на то, что в большинстве случаев MySQL может применяться в соответствии с Общедоступной лицензией проекта GNU (GPL), есть обстоятельства, требующие покупки коммерческой лицензии для использования этого продукта. Следует внимательно проверить лицензионные требования на Web-сайте MySQL (www.mysql.com) и определить, какая редакция MySQL соответствует вашим потребностям.

Если вам нужна база данных с открытым программным кодом, а условия применения MySQL в соответствии с требованиями лицензии GPL для вас не приемлемы, и вы не хотите покупать коммерческую лицензию, то можете рассмотреть как альтернативу применение мощной СУРБД PostgreSQL (во время написания книги лицензионные условия использования PostgreSQL были менее строгими). Подробности можно найти на Web-сайте www.postgresql.org.

Более подробную информацию о PostgreSQL вы можете найти в нашей книге: Neil Matthew. Beginning Databases with PostgreSQL: From Novice to Professional. Second Edition. — Apress, 2005. (Мэттью H. Базы данных на примере PostgreSQL: от новичка до профессионала. Второе издание).

В этой главе обсуждаются следующие темы:

□ команды администрирования, необходимые для работы с MySQL;

□ основные средства и функции MySQL;

□ API для взаимодействия ваших программ на языке С с базами данных MySQL;

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

Установка

Какой бы вариант системы Linux вы не предпочли, вероятно, для него существует доступная версия MySQL, заранее откомпилированная и готовая к установке. Например, для Red Hat, SUSE и Ubuntu есть заранее откомпилированные пакеты, включенные в современные дистрибутивы этих ОС. Мы рекомендуем вам, как правило, применять заранее откомпилированные версии, поскольку они предоставляют самый легкий способ быстрых установки и запуска MySQL. Если в вашем дистрибутиве нет пакета MySQL или вы хотите получить самый свежий выпуск программного обеспечения, двоичные и исходные пакеты можно загрузить с Web-сайта MySQL.

В этой главе мы описываем установку только заранее откомпилированных версий MySQL.

Пакеты MySQL

Если по какой-то причине вам вместо стандартной версии нужно загрузить MySQL из Интернета, для подготовки и выполнения примеров из этой книги следует применять сборку Standard общедоступной версии (community edition). Вы увидите, что в нее включены пакеты Мах и Debug. Пакет Max содержит дополнительные средства, такие как поддержка необычных типов файлов для хранения и развитых средств, например кластеризации. Пакеты Debug откомпилированы с дополнительным кодом отладки и отладочной информацией; к счастью, вам не понадобится отладка на столь низком уровне.

Не используйте версии Debug при эксплуатации; производительность снижается из-за дополнительной поддержки отладочных средств.

Для разработки приложений на базе MySQL вам придется установить не только сервер, но и библиотеки разработки. Как правило, в вашем диспетчере пакетов (package manager) есть вариант MySQL, нужно только убедиться в том, что установлены и библиотеки средств разработки. На рис. 8.1 показан диспетчер пакетов, готовый установить MySQL с дополнительным пакетом средств разработки, выделенным и готовым к установке.

В других дистрибутивах организация пакетов немного иная. Например, на рис. 8.2 показан объединенный диспетчер пакетов дистрибутива Ubuntu, готовый к установке MySQL.

Установка MySQL также создает пользователя «mysql», имя которого по умолчанию применяется как имя процесса-демона сервера MySQL.

После установки пакетов необходимо проверить, запущена ли автоматически СУРБД MySQL. Во время написания книги некоторые дистрибутивы, например, Ubuntu делали это, в то время как другие, такие как Fedora, нет. К счастью, очень легко проверить, работает ли сервер MySQL:

Если вы видите один или несколько выполняющихся процессов mysqld, следовательно, сервер стартовал. Во многих системах вы также увидите процесс safe_mysqld, утилиту для запуска реального процесса mysqld с корректным идентификатором пользователя.

Если нужно запустить (или перезапустить либо остановить) сервер MySQL, можно использовать панель управления сервисами GUI (GUI services control panel). Панель настройки сервисов (Service Configuration pane) дистрибутива Fedora показана на рис. 8.3.

Вам следует применить редактор настройки сервисов для того, чтобы определить, хотите ли вы, чтобы сервер MySQL автоматически стартовал при каждом запуске ОС Linux.

Настройка после установки

Предположим, что все идет как надо, СУБД MySQL установлена и стартовала с общим стандартным набором параметров. Это предположение можно проверить:

Если вы получите сообщение «Welcome to the MySQL monitor» («Добро пожаловать в монитор MySQL») и затем приглашение

Дополнительную информацию можно получить с помощью команды

Default options are read from the following files in the given order:

Состояние работающего сервера можно также проверить с помощью команды

Вывод не только подтвердит запуск, но и предоставит номер версии используемого вами сервера.

Еще один полезный аспект применения команды

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

После того как вы убедитесь в том, что в двоичный файл сервера включена поддержка InnoDB, для того чтобы сделать его выбираемым по умолчанию механизмом хранения данных, вы должны задать его таковым в файле /etc/my.cnf, иначе по умолчанию будет применяться механизм хранения MyISAM. Редактирование очень простое: в раздел

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

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

Более подробную информацию и другие конфигурационные параметры можно найти в интерактивных руководствах на Web-сайте www.mysql.com.

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

Помните об имеющейся бреши в системе безопасности, упоминавшейся несколько разделов назад и позволяющей любому подключиться без пароля как пользователь root? Сейчас самое время усовершенствовать защиту. Не дайте сбить себя с толку имени пользователя root, применяемому во время установки MySQL. Между пользователем root СУРБД MySQL и пользователем root операционной системы нет никакой связи; MySQL просто регистрирует пользователя с именем «root» как администратора, что делает и ОС Linux. Пользователи базы данных MySQL и идентификаторы пользователей ОС Linux никак не связаны; у MySQL есть собственная встроенная система управления пользователями и правами доступа. По умолчанию пользователь с учетной записью в. вашей установленной системе Linux может зарегистрироваться на вашем сервере MySQL как администратор этой СУРБД. После того как вы ограничите права пользователя root СУРБД MySQL, например, разрешив только локальному пользователю регистрироваться с именем root и установив пароль для такого доступа, вы можете добавить только тех пользователей и только те права доступа, которые абсолютно необходимы для функционирования вашего приложения.

Установить пароль можно любым возможным способом, но самый простой с помощью команды:

$ mysqladmin -u root password newpassword

Она задает начальный пароль

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

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Type ‘help;’ or ‘\h’ for help. Type ‘ \c’ to clear the buffer.

Query OK, 0 rows affected (0.00 sec)

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

Илон Маск рекомендует:  Visual basic для начинающих

Обратите внимание на то, что мы завершаем команды на языке SQL точкой с запятой (

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

Теперь рассмотрим таблицу прав доступа, чтобы убедиться в том, что пароль установлен. Сначала с помощью команды use переключитесь на базу данных mysql и затем запросите внутренние таблицы:

mysql> SELECT user, host, password FROM user;

Команды mysql

To login (from unix shell) use -h only if needed.

Create a database on the sql server.

List all databases on the sql server.

Switch to a database.

To see all the tables in the db.

To see database’s field formats.

To delete a db.

To delete a table.

Show all data in a table.

Returns the columns and column information pertaining to the designated table.

Show certain selected rows with the value «whatever».

Show all records containing the name «Bob» AND the phone number ‘3444444’.

Show all records not containing the name «Bob» AND the phone number ‘3444444’ order by the phone_number field.

Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’.

Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’ limit to records 1 through 5.

Use a regular expression to find records. Use «REGEXP BINARY» to force case-sensitivity. This finds any record beginning with a.

Show unique records.

Show selected records sorted in an ascending (asc) or descending (desc).

Return number of rows.

Sum column.

Join tables on common columns.

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

Change a users password from unix shell.

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

Set a root password if there is on root password.

Update a root password.

Allow the user «bob» to connect to the server from localhost using the password «passwd». Login as root. Switch to the MySQL db. Give privs. Update privs.

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

or

where [field name] = ‘user’; Delete a row(s) from a table.

Update database permissions/privilages.

Delete a column.

Add a new column to db.

Change column name.

Make a unique column so you get no dupes. mysql> alter table [table name] add unique ([column name]); Make a column bigger.

Delete unique from table.

Load a CSV file into a table.

Dump all databases for backup. Backup file is sql commands to recreate all db’s.

Dump one database for backup.

Dump a table from a database.

Restore database (or database table) from backup.

Create Table Example 1.

Create Table Example 2.

Select last N row from table CDR — DB asteriskcdrdb

20 команд MySQL (mysqladmin) для администратора базы данных в Linux

mysqladmin – это утилита командной строки, которая поставляется с MySQL сервером и используется администраторами баз данных для выполнения некоторых простых MySQL задач, таких как установка пароля root или другого пользователя, изменение пароля root или другого пользователя, мониторинг процессов mysql, перезагрузка привилегий, проверка статуса сервера и т.д.

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

1. Как установить пароль MySQL Root?

Если у вас свежая установка MySQL сервера, то она не требует какого-либо пароля для подключения в качестве пользователя root. Для установки в MySQL пароля root пользователя используйте следующую команду:

2. Как изменить пароль MySQL Root?

Если вы хотите изменить или обновить пароль от root в MySQL, то вам нужно напечатать следующую команду. Допустим, ваш старый пароль это 123456, и вы хотите изменить его на новый пароль xyz123:

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

3. Как проверить, запущен ли MySQL сервер?

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

4. Как проверить, какую версию MySQL я использую?

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

5. Как узнать текущий статус MySQL сервера?

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

6. Как проверить статус всех переменных и значений MySQL сервера?

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

7. Как посмотреть все переменные и значения MySQL статуса?

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

8. Как проверить все процессы рабочего MySQL сервера?

Следующая команда отобразить все запущенные процессы запросов к базе данных MySQL:

9. Как создать базу данных на MySQL сервере?

Для создания новой команды на MySQL сервере используйте команду, которая показана ниже:

10. Как удалить базу данных на MySQL сервере?

Для удаления базы данных с MySQL сервера используйте следующую команду. Для подтверждения нажмите ‘y‘.

11. Как перезагрузить/сбросить привилегии MySQL?

Команда reload говорит серверу повторно загрузить таблицы grant. Команда refresh сбрасывает все таблицы и повторно открывает файлы журналов.

12. Как безопасно выключить MySQL сервер?

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

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

13. Некоторые полезные команды MySQL Flush

Ниже несколько полезных flush команд с описанием.

  • flush-hosts: Очистить всю информацию из кэша хоста.
  • flush-tables: Сброс всех таблиц.
  • flush-threads: Очистить кэш всех потоков.
  • flush-logs: Очистить все информационные логи.
  • flush-privileges: Перезагрузить таблицы grant (то же как и reload).
  • flush-status: Очистить переменные статуса.

14. Как завершить спящий клиентский процесс MySQL?

Используйте следующую команду для выявления спящего клиентского процесса MySQL:

Теперь запустите команду с kill и ID процесска, как показано ниже:

Если вам нужно завершить несколько процессов, тогда передайте ID процессов в виде списка, разделённого запятыми:

15. Как вместе запустить несколько команд mysqladmin?

Если вы хотите выполнить одновременно несколько mysqladmin команд, то команда должна выглядеть примерно так:

16. Как подключиться к удалённому mysql серверу?

Для подключения к удалённому MySQL серверу исопльзуйте -h (хост) с IP адресом удалённой машины:

17. Как выполнить команды на удалённом MySQL сервере?

Допустим, вы хотите увидеть статус удалённого MySQL сервера, тогда команда будет:

18. Как запустить/остановить копирование на удалённом второстепенном MySQL сервере?

Для запуска/остановки MySQL репликации на второстепенном (salve) сервере, используйте следующие команды:

19. Как сохранить отладочную информацию MySQL в файлы журналов?

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

20. Опции и использование mysqladmin

Все опции и доступные команды mysqladmin вы сможете узнать набрав:

Мы попытались включить в статью почти все команды mysqladmin с примерами. Если мы пропустили что-то, напишите в комментариях.

Администрирование Mysql из командной строки

Небольшая ремарка насчет постоянного упоминания мной, FreeBSD. В процессе написания, я стараюсь вживую проверять команды, которые пишу, а поскольку Unix системы, расплодились нынче в большом количестве, наборы команд, программ и утилит а так-же опций команд, могут значительно различаться и то что работает во FreeBSD, может не работать в каком-нибудь дистрибутиве Linux. Поэтому мне кажется не будет лишним указать, в какой именно unix системе, это работает точно. На самом деле адаптировать все это под любую другую Unix систему не сложно, всегда можно найти аналог программы, или глянуть man руководство, что-бы уточнить использование опций.

Для начала, как восстановить пароль Mysql пользователя root

Консольный клиент mysql

Не путайте пользователя root, Mysql сервера, с системным пользователем root.

Далее в примерах, ключ \g ( go ), это указание Mysql выполнить команду, пока он не указан, клиент будет переходить на новую строку, ожидая дальнейшего ввода или ключа \g ( как альтернативу можно использовать символ ; – точка с запятой ). Еще один полезный вариант \G, это вертикальный вывод данных, очень удобно, если например в таблице много колонок и они не умещаются на ширину экрана. Полный список ключей можно посмотреть введя ключ \h ( help ).

Самые полезные команды MySQL

Список настроечных параметров и их значения

Список прав у пользователя root@localhost

Создание нового пользователя

Добавим выбранные привилегии для всех таблиц БД dbname пользователю ‘user’@’localhost’

Добавим все привилегии для всех таблиц БД dbname пользователю ‘user’@’localhost’

Удаление прав пользователя ‘user’@’localhost’ для БД dbname:

Новый пароль для root

Управление базой данных

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

Создание базы данных из консоли

Удаления базы данных из консоли

Adminer — удобная замена phpMyAdmin, всего один php-файл.

Замена в поле определенной подстроки на другую

Вставка данных из одной таблицы в другую

Просмотр структуры таблицы blog_posts

Просмотр sql-запроса на создание структуры таблицы blog_posts

Мониторинг и статистика

Список всех баз данных

Список всех таблиц в выбранной базе данных

Статистика по работе сервера

Оптимизация баз данных

При помощи команды mysqlcheck можно выполнять проверку, оптимизацию и исправление ошибок.

Поверка на ошибки БД dbname

Восстановление и оптимизация всех БД

-p – использовать пароль

-A – проверять все базы данных

-r – ремонтировать БД

-o – оптимизировать БД

Скрипт простой оптимизации БД, можно добавить в крон для выполнение раз в сутки

Установим mysqltuner, который показывает статистику по работающему mysql-серверу и дает полезные советы по оптимизации.

При запуске скрипт спросит логин и пароль для root’a. После корректной авторизации будет выведена статистика и разные советы по правке конфигурационного файла MySQL.

Изменение строки приглашения (prompt)

Эти настройки были найдены на просторах инета, но автора, к сожалению, не помню. Делают эти настройки следующие

  • устанавливаем кодировку по-умолчанию в utf8
  • используем less для вывода результатов запроса
  • меняем строку приглашения, \u — пользователь, \h — хост, \d — база данных
  • указываем логин и пароль для mysql-консоли, удобно при разработке на локальном сервере

Дамп (резервная копия)

Дамп базы данных

Дамп выбранных баз

Дамп только структуры, без данных

—add-drop-table — добавляет команду DROP TABLE перед каждой командой CREATE TABLE

—add-locks — добавляет команду LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы

—no-create-db, -n — не добавлять команду CREATE DATABASE , которая добавляется при использовании параметров —databases и —all-databases

—no-data, -d — дампить только структуру таблиц

—no-create-info, -t — не создавать команду CREATE TABLE

—skip-comments — не выводить комментарии.

—compact — использовать компактный формат

—create-options — добавляет дополнительную информацию о таблице в команду CREATE TABLE : тип, значение AUTO_INCREMENT и т.д. Не нужные опции можно вырезать с помощью sed.

—extended-insert, -e — применение команды INSERT с многострочным синтаксисом (повышает компактность и быстродействие операторов ввода)

—tables — дампить только таблицы из списка, следующего за этим параметром, разделитель — пробел

Начиная с версии 5.5.30 при дампе возможно получить следующие сообщение

Для исправления этого надо обновить базы и перезапустить MySQL

Изменение кодировка для текстового поля

Список полей для таблицы table с информацией о поле, в том числе и кодировка поля

Меняем charset для поля field

Изменение кодировка при импорте с дампа

Определение кодировки файла

Конвертирование из кодировки latin1 в utf8

Иногда при компиляция MySQL-python может выскочить такая ошибка configure: error: mysql_config executable not found, это значит, что не установлен пакет libmysqlclient15-dev. Под Ubuntu устанавливается так

Перенос директории с данным (data directory)

Копируем существующею директорию с данными в новое место

Команды MySQL

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

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

  • Команды и ключевые слова SQL нечувствительны к регистру. Все три команды — CREATE, create и CrEaTe — абсолютно идентичны по смыслу. Но чтобы было понятнее, для команд рекомендуется использовать буквы верхнего регистра.
  • Имена таблиц чувствительны к регистру в Linux и Mac OS X, но нечувствительны в Windows. Поэтому из соображений переносимости нужно всегда выбирать буквы одного из регистров и пользоваться только ими. Для имен таблиц рекомендуется использовать буквы нижнего регистра.

Создание баз данных и таблиц

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

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

При успешном выполнении команды будет выведено сообщение:

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

Если вы работаете в панели phpMyAdmin, то достаточно просто выбрать базу users в списке слева, а затем перейти во вкладку SQL. Теперь запросы будут выполняться непосредственно для это базы данных.

Теперь база данных будет готова к продолжению работы со следующими примерами.

Организация доступа пользователей

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

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

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

Илон Маск рекомендует:  Что такое код opengl bibliography

Для добавления или редактирования прав доступа пользователей в базе данных MySQL могут применяться два разных способа (при условии, что модификацией прав доступа занимается пользователь базы данных root): непосредственное выполнение операторов SQL (например, ввод буквы Y вручную в каждое соответствующее поле каждой соответствующей таблицы прав доступа) или использование синтаксических конструкций GRANT и REVOKE. Последний способ является более легким и менее опасным, если допущена небольшая ошибка, поскольку в большинстве случаев попытка выполнения ошибочного запроса окончится неудачей с сообщением об ошибке SQL, но брешь в системе защиты при этом не возникнет.

Чтобы ввести информацию о новом пользователе MySQL, можно применить следующий оператор:

где данные о столбцах (column) и таблицах (table) являются необязательными, а с помощью списка, разделенного запятыми, могут быть заданы дополнительные сведения о типах прав доступа priv_types.

Если предоставлены права доступа ALL на уровне столбца, таблицы или базы данных, то пользователь получает возможность применять только тот набор прав доступа, который соответствует указанному уровню. Необходимо соблюдать исключительную осторожность при предоставлении пользователям следующих прав доступа, поскольку все эти права являются опасными: GRANT, ALTER, CREATE, DROP, FILE, SHUTDOWN, PROCESS. Такие права доступа не требуются ни одному обычному пользователю базы данных, особенно пользователю PHP.

Синтаксическая конструкция оператора отмены прав доступа весьма похожа на соответствующий оператор предоставления прав доступа, но проще него:

После предоставления или отмены прав доступа для любого пользователя необходимо вынудить базу данных выполнить перезагрузку в память новых данных о правах доступа. Для этого требуется ввести команду FLUSH PRIVILEGES. Можно также остановить и снова запустить сервер, но такое решение во многих обстоятельствах неприменимо на практике.

Безусловно, изложенные выше сведения вполне доступны для восприятия, но не дают ответа на такой вопрос: какие же права доступа должны быть фактически предоставлены действующим пользователям PHP? Рассмотрим некоторые случаи, которые часто встречаются на практике.

Локальный сервер, применяемый для разработки

Если доступ осуществляется исключительно локально, то применимы практически любые права доступа. Если в ходе разработки требуется проводить эксперименты со схемой базы данных, то наиболее подходящим является именно такая конфигурация, поэтому разработчику, кроме обычных прав доступа для выполнения операций SELECT, INSERT и UPDATE, могут быть предоставлены права доступа наподобие ALTER, CREATE, DELETE и DROP. При таких обстоятельствах многие администраторы считают приемлемым просто предоставить локальному пользователю права доступа ALL PRIVILEGES к определенной базе данных, как показано ниже (права этого пользователя мы будем использовать в последующих примерах):

Автономный веб-сайт

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

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

Тем не менее в подобной ситуации чаще всего используется также репликация по принципу «ведущий-ведомый». Часто подобные кластеры баз данных MySQL имеют такую конфигурацию, что все запросы на выполнение операций записи поступают в ведущую базу данных, но ведомые базы данных не выполняют никаких других действий, кроме очень быстрого обслуживания операций чтения. В таком случае в каждой ведомой базе данных предоставляются только права доступа SELECT, а в ведущей базе данных предоставляются только права INSERT И UPDATE; при этом, возможно, эти права назначаются двум разным пользователям базы данных.

Создание таблиц

Для определения структуры новой таблицы базы данных служит команда CREATE TABLE. Когда создается таблица базы данных, каждый столбец может содержать дополнительные параметры, помимо имени и типа данных. Если при добавлении новой записи в таблицу поле не должно оставаться пустым, в его определении указывается ключевое слово NOT NULL. Ключевое слово PRIMARY KEY определяет, какое поле будет использоваться в качестве первичного ключа. Автоматическое заполнение ключевого поля можно определить с помощью ключевого слова AUTO_INCREMENT. Например:

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

Если все в порядке, то вы увидите структуру новой таблицы:

Команда DESCRIBE является неоценимым средством отладки, когда нужно убедиться в успешном создании таблицы MySQL. Этой командой можно воспользоваться также для того, чтобы просмотреть имена полей или столбцов таблицы и типы данных в каждом из них (это удобно при работе с консолью, при использовании phpMyAdmin успешные команды подсвечиваются зеленым цветом, а структуру таблицы можно посмотреть по ссылке главного меню «Структура»).

Типы данных MySQL

Типы данных MySQL подразделяются на три основные разновидности: числовые типы; типы, предназначенные для представления даты и времени; а также строковые (или символьные) типы. Применение этих типов данных в основном не связано с какими-либо сложностями, тем более, что для обычного пользователя сайта не имеет значения, например, какой тип данных применяется в сценариях для представления целочисленных данных, INT или MEDIUMINT. Однако программисты могут многое сделать, чтобы добиться создания наиболее компактных и быстродействующих баз данных.

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

Типы данных MySQL

Обозначение Занимаемый объем (байт) Область применения
TINYINT, BOOL 1 При использовании в формате представления без знака позволяет хранить значения от 0 до 255; в противном случае — от -128 до 127. В будущем должен быть предусмотрен новый логический тип, но до сих пор для представления логических значений использовался тип данных TINYINT, т.е. BOOL синоним TINYINT(1)
SMALLINT 2 Целое число в диапазоне от -32768 до 32767
MEDIUMINT 3 Целое число в диапазоне от -8388608 до 8388607
INT, INTEGER 4 Целое число в диапазоне от -2e32 до 2e32 — 1
BIGINT 8 Целое число в диапазоне от -2e64 до 2e64 — 1
FLOAT 4 Число с плавающей точкой одинарной точности
DOUBLE 8 Число с плавающей точкой двойной точности
DECIMAL Произвольное, в зависимости от точности Распакованное число с плавающей точкой, которое хранится в таком же формате, как CHAR. Используется для представления небольших десятичных значений, таких как денежные суммы
DATE 3 Отображается в формате YYYY-MM-DD
DATETIME, TIMESTAMP 8 Отображается в формате YYYY-MM-DD HH:MM:SS
TIME 3 Отображается в формате HHH:MM:SS, где HHH — значение от -838 до 838. Это позволяет применять значения типа time для представления продолжительности времени между двумя событиями
YEAR 1 Отображается в формате YYYY, который представляет значения от 1901 до 2155
CHAR N байт Строка постоянной длины. Строка, имеющая длину меньше объявленной, дополняется справа пробелами. Значение N должно быть меньше или равно 255
VARCHAR N байт Строка переменной длины.
BINARY N байт Сохраняет байтовые строки
TINYBLOB, TINYTEXT до 255 Сохраняет строки, операции сортировки и сравнения данных типа blob выполняются с учетом регистра; операции с данными типа text — без учета регистра
BLOB, TEXT до 64 Кбайт Длинные строки
MEDIUMBLOB, MEDIUMTEXT до 16 Мбайт Длинные строки
LONGBLOB, LONGTEXT до 4 Гбайт Длинные строки
ENUM(value1, . valueN) 1 или 2 Коллекция значений (65536 возможных значений)
SET(value1, . valueN) до 8 Коллекция значений (64 возможных значений)

Тип данных AUTO_INCREMENT

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

Работа с таблицами

Добавление данных в таблицу

Для добавления данных предназначена команда INSERT. Используется она следующим образом:

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

  • числовые значения должны указываться без кавычек;
  • строковые значения всегда должны быть в кавычках;
  • значения даты и времени всегда должны быть в кавычках;
  • функции должны указываться без кавычек;
  • значение NULL никогда не должно заключаться в кавычки.

Наконец, если в строке отсутствует какое-либо значение, оно по умолчанию подразумевается равным значению NULL. Однако если поле не может иметь значение NULL (то есть когда оно было определено как NOT NULL), и вы не указали значение для этого поля, будет сгенерировано сообщение об ошибке.

Давайте вставим в нашу таблицу data несколько пользователей:

При добавлении данных вы должны указывать все столбцы, даже если для некоторых из них значения отсутствуют. Хотя мы и не задаем значение поля id, позволяя MySQL сделать это за нас, следует оставить на его месте метку-заполнитель.

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

Кодировка таблицы

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

Как вы наверное догадались эта ошибка связана с неверной кодировкой таблицы. В моем случае по умолчанию стоит кодировка latin1, которая не позволяет отображать русскоязычные символы. Чтобы изменить кодировку таблицы на приемлемую (например UTF-8) нужно воспользоваться следующей конструкцией:

Манипулирование определениями таблиц

Создав таблицу и начав заполнять ее информацией, вы можете обнаружить, что потребовалось изменить типы полей. Например, увеличить размер поля name, вмещающего 32 символа, до 100 символов. Можно было бы начать все с нуля, полностью переопределив таблицу, но при этом будут утеряны данные. К счастью, MySQL позволяет изменять типы полей без потери данных.

Переименование таблицы

Чтобы переименовать таблицу, следует использовать команду:

Следующая команда переименует таблицу data в users_data:

Изменение типа данных столбца

Чтобы изменить тип данных столбца, следует использовать команду:

Следующая команда изменит поле name таким образом, что оно будет вмещать до 100 символов:

Кроме того, команда MODIFY может принимать два необязательных параметра, изменяющих порядок следования столбцов в таблице. С помощью ключевого слова FIRST можно сделать столбец первым в таблице, а с помощью ключевого слова AFTER имя_столбца – поместить столбец после указанного. Например, следующая команда разместит столбец name после столбца year:

Добавление столбца

Добавить новый столбец позволяет команда:

Следующая команда добавит в таблицу users_data столбец типа DATETIME с именем regDate:

В этой команде, как и в конструкции ALTER TABLE MODIFY, можно определить позицию вставляемого столбца с помощью ключевых слов FIRST и AFTER имя_столбца.

Переименование столбца

Чтобы переименовать столбец, следует использовать команду:

Ниже приводится пример переименования столбца regDate в regTime. При работе с этой командой вы можете одновременно изменять определение столбца. Однако даже если определение столбца не изменяется, вам все же придется указывать его полное определение:

Удаление столбца

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

Следующая команда удалит столбец regTime:

Удаление всей таблицы

Иногда требуется удалить и целую таблицу. Полное удаление таблицы со всеми данными выполняется с помощью команды DROP:

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

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

Как изменить лимиты в MySQL/MariaDB

По умолчанию, настройки лимитов СУБД могут оказаться не оптимальными. В некоторых случаях, это может привести к накапливанию очередей и низкой производительности приложений или, вовсе, привести к ошибке «MySQL server has gone away» — необходимо увеличить стандартные значения.

В рамках данной инструкции мы рассмотрим следующие лимиты:

  1. Максимально разрешенное количество одновременных подключений (max_connections). По умолчанию, равно 151.
  2. Максимально разрешенное количество одновременных подключений для пользователя (max_user_connections). По умолчанию, равно 0 — лимита нет.
  3. Тайм-аут ожидания для запросов (wait_timeout). По умолчанию, 28800 секунд.
  4. Лимит на максимальный размер пакета (max_allowed_packet). По умолчанию, 1048576 байт (1 Мб).

Просмотр текущих значений

Выполняется в оболочке mysql — для подключения вводим:

Посмотреть максимально разрешенное количество подключений:

> SHOW VARIABLES WHERE `variable_name`=’max_connections’;

Максимально разрешенное количество подключений на пользователя:

> SHOW VARIABLES WHERE `variable_name`=’max_user_connections’;

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

> SHOW status WHERE `variable_name` = ‘threads_connected’;

Тайм-аут ожидания для запросов:

> SHOW VARIABLES WHERE `variable_name`=’wait_timeout’;

Максимальный размер пакета:

> SHOW VARIABLES WHERE `variable_name`=’max_allowed_packet’;

Настройка max_connections

Открываем конфигурационный файл mysql:

* в более ранних версиях данный файл находится по пути /etc/my.cnf

В директиве [mysqld] добавляем или изменяем следующую строку:

[mysqld]
.
max_connections = 500

* в данном примере мы разрешим 500 одновременных подключений к MySQL. При превышении данного значения будет отображаться ошибка too many connections.

# systemctl restart mysql || systemctl restart mariadb

* в некоторых системах перезагрузка сервера баз данных выполняется командой service mysql restart или service mysqld restart или service mysql-server restart

Оптимальное значение

Для данного лимита нет золотого стандарта — маленькое значение может привести к выстраиванию очередей запросов, большое — к перегрузке серверного оборудования. Правильнее всего постоянно наблюдать за значениями max_connections и threads_connected и определить для себя свой, так называемый, Best Practices.

На первое время, для сервера можно поставить лимит в 200-300 подключений.

Опция max_user_connections

В отличие от max_connections, данная опция задает лимит для подключений конкретному клиенту. Настройка также выполняется в конфигурационном файле СУБД:

[mysqld]
.
max_connections = 500
max_user_connections = 50

* в данном примере сервер будет принимать 500 одновременных подключений, но от каждого клиента, максимум — 50.

По умолчанию, лимит не задан и равен 0. Посмотреть текущее значение можно командой:

> SHOW VARIABLES WHERE variable_name = ‘max_user_connections’;

Лимит запросов wait_timeout

Настройка также выполняется в конфигурационном файле /etc/my.cnf:

[mysqld]
.
wait_timeout = 1200

* в данном примере устанавливаем лимит на 20 минут.

systemctl restart mysql || systemctl restart mariadb

Максимальный размер пакета max_allowed_packet

Открываем конфигурационный файл my.cnf:

[mysqld]
.
max_allowed_packet = 128M

systemctl restart mysql || systemctl restart mariadb

Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL