Памятка по MySQL/MariaDB
Периодически возникает необходимость первоначальной настройки MySQL. Опишу несколько особенностей современной настройки.
В некоторых дистрибутивах, например, Debian 9, под mysql подразумевается mariadb, т.е. даже вы устанавливаете пакет mysql-server, то получаете mariadb-server.
Пароль root
Первая особенность - пользователь root недоступен при подключении через TCP. Он остаётся доступным только через unix-сокет. Поскольку подавляющее большинство использует TCP соединение в настройках PHP, соответственно все PHP-админки для MySQL не могут использовать пользователь root. Первое, что приходит в голову - разрешить root для TCP (и это можно сделать убрав в таблице mysql.user из поля plugin значение unix_socket). Однако, лучше не стрелять себе в ногу, а создать своего пользователя, который будет иметь полные привилегии для управления сервером. В таком случае, даже если кто-то получит доступ к вашей админке через интернет, то вынужден будет не только подбирать пароль для root, но ещё и угадывать пользователя. Если это не целевая атака на вашу систему, то у злоумышленника почти нет шансов это сделать.
Для предварительной настройки воспользуемся командной строкой:
mysql -u root -p
Изначально у пользователя root нет пароля и ключ -p можно не использовать, а если использовали, то принимается любой пароль.
Создаём нового пользователя mysuperuser с паролем 123 (я надеюсь вы понимаете, что пользователь и пароль приведёны для примера и в реальности нужно использовать своё оригинальное имя пользователя и сложный пароль):
CREATE USER mysuperuser@localhost IDENTIFIED BY '123';
Теперь мы можем подключиться к нашему серверу баз данных. Однако, мы имеем ограниченные права.
GRANT ALL PRIVILEGES ON *.* TO mysuperuser@localhost WITH GRANT OPTION;
Т.е. задаём все права (ALL) для всех ресурсов (.) для нашего пользователя. WITH GRANT OPTION означает, что будут предоставлены дополнительные права на изменение структуры баз и таблиц.
После релогина в веб-интерфейсе вы получите полные права на управление сервером и сможете создавать пользователей, базы данных, настраивать привилегии и т.д. В некоторых статьях в интернете рекомендуют также перед этим перезагрузить привилегии:
FLUSH PRIVILEGES;
Судя по всему, делать этого не обязательно, т.к. работа с пользователями производилась с помощью команд CREATE и GRANT, а не при помощи INSERT/UPDATE в базе mysql.
Логика подсказывает, что изменить пользователя, в том числе пароль, можно с помощью команды:
ALTER USER mysuperuser@localhost IDENTIFIED BY 'new_password';
Но что-то пошло не так. Как оказывается, эта команда была введена в MySQL-5.7.6 и MariaDB-10.2. Если вы используете более старую версию (а на момент написания этой заметки в Debian доступна из основного репозитория только MariaDB 10.1), то изменить пароль можно следующим образом:
SET PASSWORD FOR mysuperuser@localhost = PASSWORD('new_password');
Кодировка
Как известно, в мире unix уже давно в качестве стандартной кодировки используется UTF-8 (привет пользователям Windows!!!). В MySQL поддержка UTF-8 имеется в нескольких наборах. Далее я буду иметь под словом “кодировка” как непосредственно кодировку, так и правила сортировки collation.
До недавнего времени я использовал кодировку utf8_general_ci, но, как оказалось, я немного отстал от жизни. Эта кодировка появилась первой. Позже была создана ещё одна utf8mb4. Она появилась ещё в версии MySQL 5.5.3. От первой она отличается увеличенным до 4 байт объёмом хранения одного символа. Лично я, поскольку использую русские символы, никогда не сталкивался с какими-то проблемами с utf8. Но, дабы избежать потенциальных проблем, лучше использовать utf8mb4.
Для utf8mb4 существует два основных варианта utf8mb4_general_ci и utf8mb4_unicode_ci. Судя по статьям в интернете, utf8mb4_general_ci является упрощённой и имеющая оптимизации при сортировке. Однако эти оптимизации могут вызвать в некоторых случаях не тот результат, что вы ожидаете. Поэтому лучше использовать кодировку utf8mb4_unicode_ci. К тому же, по некоторым данным, на современном железе разница в скорости сортировки практически незаметна (я думаю. если вы соберётесь создавать высоконагруженное приложение, то проведёте соответствующие тесты и выберете подходящий вариант). И ещё, немаловажно, по заявлению Oracle в MySQL 8.0, которая вышла в след за 5.7 (была изменена нумерация релизов), проведена значительная оптимизация скорости сортировки для utf8mb4, что позволяет получить 20 кратный прирост производительности.
Ну а что современные дистрибутивы? MySQL по умолчанию поставляется Oracle с кодировкой latin1. Переход на utf0mb4 произведён только в версии 8.0. В текущей версии MariaDB из репозитория Debian изначально используется utf8mb_general_ci.
База часовых поясов
Для корректной работы со временем необходимо сформировать базу часовых поясов на базе данных tzdata. Сделать это можно с помощью
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p
Операцию можно повторить, т.к. сгенерированный командой mysql_tzinfo_to_sql скрипт имеет в своём составе команды на удаление существующих таблиц. После обновления базы часовых поясов необходимо перезапустить MySQL-сервер.
База часовых поясов позволит вам устанавливать необходимый часовой пояс и учитывать все изменения для текущей местности при работе с датами из прошлого:
SET TIME_ZONE="Asia/Yekaterinburg"
Обратите внимание, что заметки могут обновляться со временем. Это может быть как исправление найденных ошибок, так и доработка содержания с целью более полного раскрытия темы. Информация об изменениях доступна в репозитории на github. Там же вы можете оставить в Issue ваши замечания по данной заметке.
Если данная заметка оказалась вам полезной, можете поблагодарить автора финансово.