MySQL
Для установки MySQL запустите следующую команду из терминала:
В процессе установки у вас запросят пароль для пользователя root под MySQL.
Как только установка завершится, сервер MySQL запустится автоматически. Вы можете использовать следующую команду в терминале для проверки, что сервер MySQL запущен:
Когда вы запустите эту команду, ввы сможете увидеть что-то похожее на следующую строку:
Если сервер не работает, вы можете набрать следующую команду для его запуска:
Настройка
Вы можете отредактировать файл /etc/mysql/my.cnf для настройки основных параметров — файл журнала, номер порта и пр. Например, чтобы настроить MySQL на ожидание подключений от компьютеров в сети, измените параметр bind-address на IP адрес сервера:
После изменений в /etc/mysql/my.cnf сервис MySQL нужно перезагрузить:
Если вам потребовалось сменить пароль пользователя root в MySQL, введите в терминале:
Сервис MySQL будет остановлен и вас попросят ввести новый пароль.
Драйверы базы данных
Хотя конфигурация по умолчанию для MySQL, предоставляемая пакетами Ubuntu, имеет великолепную функциональность и работает достаточно хорошо, есть некоторые вещи, которые вы можете решить до того как продолжить.
MySQL разработан так, что позволяет хранить данные по-разному. Эти варианты относятся к драйверам (управляющим модулям — engines) как баз данных, так и хранилищ. Существует два основных драйвера, которые вам могут быть интересны: InnoDB и MyISAM. Драйверы хранилищ прозрачны (незаметны) конечным пользователям. MySQL управляет событиями по-разному на нижнем уровне, но независимо от того, какая система хранения данных используется, вы будете взаимодействовать с базой одним и тем же способом.
Каждый драйвер имеет свои преимущества и недостатки.
Хотя смешивание и связывание драйверов баз данных на уровне таблиц разрешается и может быть привлекательным, это снижает эффективность настройки производительности, которую вы смогли бы провести при разделении ресурсов между двумя системами вместо замешивания их в одно целое.
MyISAM более старая из двух. Она может быть быстрее InnoDB при определенных обстоятельствах и предпочтительна при рабочей нагрузке, ориентированной на чтение данных. Некоторые интернет приложения настроены на использование именно MyISAM (однако это не означает, что они будут медленнее под InnoDB). MyISAM также поддерживает тип данных FULLTEXT, который позволяет осуществлять очень быстрый поиск по большому количеству текстовых данных. Однако MyISAM поддерживает блокировку записи только на уровне таблиц. Это означает, что только один процесс может изменять данные в таблице в один момент времени. Поскольку некоторые приложения, использующие таблицу, могут масштабироваться (работать несколькими экземплярами — scales), это может стать серьезной помехой. Здесь также отсутствует журналирование, что может усложнить восстановление данных после сбоя. Следующая ссылка предоставляет некоторые соображения по использованию MyISAM в работающей базе данных.
InnoDB — более современный драйвер, созданный по принципам ACID, что гарантирует надежную обработку транзакций базы данных. Блокировка записи производится на уровне одной записи в таблице. Это означает возможность нескольких изменений в одной таблице одновременно. Кэширование данных происходит также и в оперативной памяти внутри драйвера базы данных, позволяя кэшировать более эффективно чем на уровне блоков файлов. В соответствии с ACID все транзакции журналируются независимо от основных таблиц. Это позволяет намного более надежно восстанавливать данные при проверке целостности данных.
Начиная MySQL 5.5 InnoDB является драйвером по умолчанию и настоятельно рекомендуется вместо MyISAM, если только у вас нет специфических потребностей, уникальных для этого драйвера.
Расширенные настройки
Создание настроенного файла my.cnf
Существует ряд параметров, которые могут быть указаны в файле настроек MySQL, что со временем позволит вам повысить производительность вашего сервера. Для начальной настройки вам может пригодиться Percona's my.cnf generating tool. Этот инструмент позволит вам создать файл my.cnf более оптимизированный под специфические возможности вашего сервера и ваши требования.
Не меняйте ваш существующий файл my.cnf на созданный утилитой, если у вас уже загружены данные в вашу базу данных. Некоторые изменения в этом файле могут быть несовместимы, если вы поменяли то, как хранятся данные на диске, после чего вы не сможете запустить MySQL. Если вы все же решили это сделать и у вас есть данные в базе, вам потребуется выполнить mysqldump и загрузить данные повторно:
Эта операция запросит пароль пользователя root перед созданием копии данных. Желательно убедиться, что другие пользователи и процессы не используют базу во время этой операции. В зависимости от количества данных в вашей базе, это может занять длительное время. Вы не увидите ничего на экране во время этого процесса.
Как только файл выгрузки создан, остановите MySQL:
Теперь сохраните оригинальный файл my.cnf и замените его на новый:
Затем удалите и создайте заново пространство базы данных, а также убедитесь в корректности его владельца перед запуском MySQL:
Теперь все, что осталось — это импортировать ваши данные. Чтобы дать нам представление как идет процесс импорта, полезно использовать утилиту 'Pipe Viewer' (pv). Далее показано как установить и использовать pv для этого случая, но если вы не хотите ее использовать, просто замените pv на cat в соответствующей команде. Не обращайте внимание на время ETA (расчетное время окончания), генерируемое pv, поскольку оно основывается на среднем времени обработки каждой записи при сохранении в файл, однако скорость может сильно меняться от записи к записи при использовании mysqldumps:
Когда все завершится, это будет означать что все получилось!
MySQL Tuner
MySQL Tuner — это полезный инструмент, который подсоединяется к работающему MySQL и предлагает варианты как можно улучшить настройки для вашей рабочей нагрузки. Чем дольше работает сервер, тем лучше рекомендации предоставит mysqltuner. Для рабочего окружения подождите как минимум 24 часа прежде чем запускать утилиту. Вы можете установить mysqltuner из хранилища Ubuntu:
После установки запустите ее:
и ждите ее финального отчета. Верхняя секция предоставляет общую информацию а сервере баз данных, а нижняя часть содержит рекомендации по настройке, необходимые для изменения вашего my.cnf. Многие из них могут быть поправлены вживую на сервере без перезагрузки. Смотрите официальную документацию MySQL (указанную в разделе Ссылки) для перечня параметров, изменяемых "налету". Далее часть примерного отчета по работающей базе, который показывает, что можно извлечь некоторую пользу от увеличения размера кэша запросов:
Один финальный комментарий по настройке базы данных: Хотя мы можем утверждать что определенные настройки самые лучшие, производительность может изменяться от приложения к приложению. Например, что работает великолепно для WordPress, может оказаться не лучшим для Drupal, Joomla или проприетарных приложений. Производительность зависит от типов запросов, использования индексов, насколько эффективно спроектирована база данных и т.д. Вы можете посчитать полезным потратить некоторое время на поиск настроек базы данных под используемые вами приложения. Как только вы пройдете определенную точку, любые ваши изменения будут приводить к минимальным улучшениям и вам будет лучше либо заняться улучшением ваших приложений, либо масштабировать вашу базу данных, используя более производительное оборудование или добавляя зависимые сервера.
Ссылки
Смотрите домашнюю страницу MySQL для дополнительной информации.
Полная документация доступна в форматах как онлайн, так и оффлайн по ссылке MySQL Developers portal.
Для общей информации по SQL смотрите Using SQL Special Edition от Rafe Colburn.
Страница Apache MySQL PHP Ubuntu Wiki также содержит полезную информацию.
Разворачиваем MySQL: установка и настройка
MySQL на сегодняшний день является одной из наиболее распространенных в мире. Достаточно сказать, что по рейтингам 2021 года данная СУБД лишь немного уступала Oracle.
В серии из трех статей мы рассмотрим основные моменты связанные с редакциями MySQL, обсудим архитектуру, процесс установки и базовой настройки. Во второй статье мы займемся созданием репликации БД. И в третьей части мы поговорим о различных способах резервного копирования данных в БД.
Начнем с рассмотрения редакций СУБД MySQL.
Ванильная сборка
Классическая версия MySQL от Oracle это свободная реляционная система управления базами данных. На сегодняшний день разработку и поддержку MySQL осуществляет корпорация Oracle, получившая права на торговую марку вместе с поглощённой Sun Microsystems, которая ранее приобрела шведскую компанию MySQL AB. Такая несколько сложная «пищевая цепочка» не помешала данной БД получить широкое распространение. Далее, когда мы будем рассматривать практические примеры развертывания, будет использоваться именно классическая редакция MySQL.
MariaDB
СУБД MariaDB — ответвление MySQL, разрабатываемое сообществом под лицензией GNU GPL. Разработку и поддержку MariaDB осуществляет компания MariaDB Corporation Ab и фонд MariaDB Foundation. При этом СУБД MariaDB совместима с MySQL до версии 5.6.
Движки MySQL
База данных может работать с несколькими типами движков (engines). В таблице ниже приведены основные характеристики каждого из них.
Name
Vendor
License
Under active development
MySQL versions
MariaDB versions
Sphinx Technologies Inc.
В этой статье я не буду подробно рассматривать все представленные движки, вместо этого будут рассмотрены только основные. Для ознакомления с рекомендациями по использование остальных предлагаю обратиться к следующей статье — Краткий обзор движков таблиц MySQL.
MyISAM
MyISAM это базовый тип хранилища MySQL. Однако, в нем отсутствуют блокировки на строки и нет транзакций. Как можно понять из названия, движок основывается на принципах ISAM и обладает в сравнении с ним рядом полезных дополнений. Так, он имеет возможности по сжатию данных, по созданию полнотекстовых индексов. Но при этом СУБД не является устойчивой к сбоям и не выполняет требования ACID.
Поддерживается с версий MySQL 3.x, до версий MySQL 5.5, являлась системой хранения по умолчанию.
ISAM (Indexed Sequential Access Method — индексно-последовательный метод доступа) — способ хранения данных с возможностью быстрого доступа к ним, является наиболее популярным методом индексирования.
ACID (atomicity, consistency, isolation, durability) — набор требований к транзакционной СУБД, обеспечивающий наиболее надёжную и предсказуемую её работу — атомарность, согласованность, изоляцию и устойчивость.
Атомарность гарантирует, что никакая транзакция не будет зафиксирована в системе частично. Будут либо выполнены все её подоперации, либо не выполнено ни одной.
Согласованность – транзакция, достигающая своего нормального завершения и тем самым фиксирующая свои результаты, сохраняет согласованность базы данных.
Изоляция — во время выполнения транзакции параллельные транзакции не должны оказывать влияния на её результат.
Устойчивость — независимо от проблем с питанием или сбоях в оборудовании изменения, сделанные успешно завершённой транзакцией, должны остаться сохранёнными после возвращения системы в работу.
InnoDB
InnoDB механизм хранения общего назначения, который обеспечивает высокую надежность и высокую производительность. В MySQL 8.0 механизм хранения по умолчанию. Движок был разработан специально для больших таблиц и показывает наибольшую скорость при работе с БД основанных на дисках.
Преимуществами InnoDB является возможность блокировки на уровне таблиц (читаем, если нет блокировки на запись, пишем, если нет блокировок), поддержка восстановления по времени (binary log), репликаций и конкурентного INSERT.
Установка MySQL
Теперь перейдем непосредственно к установке СУБД. Процесс не должен вызвать особых сложностей:
sudo apt update && sudo apt upgrade -y && sudo apt-get install -y mysql-server
После установки необходимо выполнить харденинг, то есть указать настройки безопасности:
• Удалить анонимных пользователей? (Нажмите y | Y для Yes, любую другую клавишу для No): y
• Запретить вход в систему удаленно? (Нажмите y | Y для Yes, любую другую клавишу для No): y
• Удалить тестовую базу данных и получить доступ к ней? (Нажмите y | Y для Yes, любую другую клавишу для No): y
• Обновить таблицы привилегий сейчас? (Нажмите y | Y для Yes, любую другую клавишу для No): y
Далее перезапустим MySQL:
sudo systemctl status mysql
sudo systemctl enable mysql
Для того, чтобы не вводить каждый раз пароль, мы можем его прописать в домашней папке в файле .my.cnf, имеющем следующую структуру:
Архитектура MySQL
Прежде, чем выполнять какие-либо настройки и запросы в СУБД давайте посмотрим, из чего состоит MySQL.
Давайте посмотрим, как это устроено в процессах:
$ ps ax | grep mysqld
$ ps –eLf | grep mysqld
Как мы видим, процесс /usr/sbin/mysqld после успешного запуска порождает множество подпроцессов, которые использует СУБД в своей работе.
На файловом уровне MySQL хранит все данные в каталоге /var/lib/mysql:
$ sudo ls -l /var/lib/mysql
О некоторых представленных здесь файлах и каталогах мы еще будем говорить в следующих статьях.
Основной конфигурационный файл находится в /etc/mysql/mysql.conf.d/:
Для тестовой инсталляции как правило вносить изменения в этот файл не нужно.
Работаем с таблицами
Далее выполним sudo mysql и перейдем непосредственно в командную среду СУБД.
Структура таблиц после установки имеет следующий вид
Сейчас здесь присутствуют только служебные таблицы, созданные вместе с СУБД.
Создадим свою базу Otus_test:
CREATE DATABASE IF NOT EXISTS Otus_test;
Можно было бы прибегнуть к использованию команды CREATE DATABASE но тогда мы бы получили ошибку, если бы такая база существовала.
Перейдем в созданную БД:
Далее давайте создадим тестовую таблицу в которой будет три столбца: id, name, address. Первичным ключом будет id. В качестве движка явно укажем InnoDB.
CREATE TABLE Otus_table
id int NOT NULL AUTO_INCREMENT,
name char(20) NOT NULL,
address char(20) NULL,
Добавим пару записей в таблицу:
INSERT Otus_table(Name, Address) VALUES (‘Admin’, ‘Moscow’);
INSERT Otus_table(Name, Address) VALUES (‘User’, ‘N/A’);
И сделаем выборку по таблице:
SELECT * FROM Otus_table;
Как видно все записи успешно добавлены в таблицу.
Немного о кодировках
Для промышленного использования СУБД необходимо, чтобы данные в кириллице хранились и отображались корректно. Кодировка (characher set) — набор используемых символов. Представление (collation) — набор правил для сравнения символов в наборе. Символьная кодировка может быть задана для сервера, базы данных, таблицы и колонок в таблице.
В качестве примера настроим кодировку UTF8 в качестве кодировки по-умолчанию. Для этого откроем уже известный нам файл конфигураций mysqld.cnf
и добавим в него следующее:
init_connect=‘SET collation_connection = utf8_unicode_ci’
Далее перезапустим БД
systemctl restart mysql
Проверим корректность работы с кириллицей:
INSERT Otus_table(Name, Address) VALUES (‘Пользователь’, ‘N/A’);
SELECT * FROM Otus_table;
Как видно, русскоязычные данные корректно сохранены.
Заключение
В этой статье мы рассмотрели основы работы с CУБД MySQL, установку и базовую настройку. Далее мы будем говорить о репликации, построении отказоустойчивых конфигураций, партиционировании и бэкапах.
Так как данный материал подготовлен в рамках запуска курса «Базы данных», хочу порекомендовать всем читателям бесплатный урок по PostgreSQL 15. В рамках урока обсудим новый функционал, деплой в Яндекс Облаке и настройку защищенного подключения. Варианты обновления с предыдущих версий PostgreSQL и их особенности. Подводные камни при обновлении версий.
.my.cnf – mysql user & password
This one is for lazy ones! If you are paranoid about security, do not use this.
/.my.cnf and add following lines in it and replace mysqluser & mysqlpass values.
For safety, make this file readable to you only by running chmod 0600
Purpose
Next time you run mysql commands mysql, mysqlcheck, mysqdump, etc; they will pick username & password from this file if you do not provide them as argument (-u and -p). It can save your time.
Of-course, if you specify username and password explicitly as part of commands arguments, they will be used.
my.cnf
my.cnf — это файл конфигурации MySQL. Чтобы найти файл, используйте команду:
В Linux’e обычно он находится по такому пути:
Редактирование
Для редактирования необходимо открыть файл my.cnf в текстовом редакторе:
После внесения изменений в файл, необходимо делать перезапуск mysql:
Пример конфигурации
Это пример настроек Mysql для сервера с 1Гб оперативной памяти и двумя ядрами:
Если в вашем конфиге есть настройка thread_concurrency — уберите ее.
Стандартные конфигурации
Для удобства используйте одну из указанных конфигураций для вашего сервера:
Для 2Гб оперативной памяти
Для 4Гб оперативной памяти
Для 8Гб оперативной памяти
Для 16Гб оперативной памяти
Для 32Гб оперативной памяти
Для 64Гб оперативной памяти
Для 128Гб оперативной памяти
Этот текст был написан несколько лет назад. С тех пор упомянутые здесь инструменты и софт могли получить обновления. Пожалуйста, проверяйте их актуальность.