Установка mariadb в ubuntu 20.04

Введение в типы данных MariaDB

Как сказано ранее, таблицы – это объекты базы данных, где мы будет хранить постоянную информацию. Каждая таблица состоит из колонок. Колонка содержит данные определённого типа.

Самыми распространёнными типами данных в MariaDB являются следующие

Число:

BOOLEAN значение считается false (т.е. ложь), а любые другие данные расцениваются как true (т.е. истина).
TINYINT (tiny integer, т.е. буквально крошечное целое число), можно использовать как SIGNED (т.е. со знаком), тогда этим типом охватываются числа от -128 до 127, также можно использовать как UNSIGNED (т.е. без знака), тогда в охватываемый диапазон входят целые числа от 0 до 255.
SMALLINT (small integer, т.е. буквально маленькие целые числа), опять же, если используется с SIGNED, то этим типом охватывается диапазон от -32768 до 32767. Диапазон UNSIGNED от 0 до 65535.
MEDIUMINT (medium integer, т.е. буквально средние целые числа, с SIGNED это от -8388608 до 8388607. Без знака это диапазон от 0 до 16777215.
INT (integer, буквально целое число), если используется с SIGNED, охватывает диапазон от -2147483648 до 2147483647, и от 0 до 4294967295 в противном случае.
BIGINT (big integer, т.е. буквально большое целое число), со знаком это диапазон от -9223372036854775808 до 9223372036854775807. Без знака от 0 до 18446744073709551615.

Помните: В TINYINT, SMALLINT, MEDIUMINT, INT и BIGINT, SIGNED предполагается по умолчанию.

DOUBLE(M, D), где M – это общее количество цифр, а D – это количество цифр после десятичной точки, представляет собой числа с двойной точностью с плавающей запятой. Если указана UNSIGNED, отрицательные значения не разрешены.

Строка:

VARCHAR(M) представляет строку переменной длины, где M – это максимально разрешённая длина колонки в байтах (65,535 в теории). В большинстве случае количество байтов идентично количеству символов, кроме символов, которым может потребоваться до 3 байтов (utf8). Например, испанская буква ñ представляет собой один символ, но использует 2 байта.
TINYTEXT – это текстовые значения, с максимальной длиной 255 символов. Эффективная максимальная длина меньше, если значение содержит многобайтную кодировку.
TEXT(M) представляет колонку с максимальной длиной в 65,535 символов. Тем не менее, как и с VARCHAR(M), реальная максимальная длина уменьшается при сохранении многобайтных символов. Если M указана, создаётся самая маленькая колонка типа TEXT, которая достаточно большая для хранения значения длиной в M символов.
MEDIUMTEXT(M) и LONGTEXT(M) похожи на TEXT(M), разница только в максимально разрешённой длине значения, которая составляет, соответственно 16,777,215 и 4,294,967,295.

Дата и время:

DATE представляет дату в формате YYYY-MM-DD.
TIME представляет время в формате HH:MM:SS.sss (чисы, минуты, секунды и милисекунды).
DATETIME является комбинацией DATE и TIME в формате YYYY-MM-DD HH:MM:SS.
TIMESTAMP используется для определения момента, когда строка была добавлена или обновлена.

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

Например, имя человека может легко поместиться в VARCHAR(50), в то время как сообщение в блоге потребует тип TEXT (можно указать M под специфические нужды).

Шаг 1 — Перемещение директории Data MySQL

Подготавливаясь для перемещения директории MySQL data, давайте проверим текущее расположения, для этого запустите интерактивную сессию MySQL с учётными данными администратора.

mysql -u root -p

Когда появится запрос, введите пароль рута MySQL. Затем в приглашении MySQL выполните запрос для показа текущего расположения директории data:

select @@datadir;

+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

Этот вывод подтверждает, что MySQL настроена на использование директории data по умолчанию, /var/lib/mysql/, т.е. эту директорию нам и нужно перемещать. После подтверждения этого, напечатайте exit; для выхода.

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

sudo systemctl stop mysql

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

sudo systemctl status mysql

Теперь, когда сервер отключён, мы скопируем с rsync существующую директорию с базой данных в новое расположение. Использование флага -a сохраняет права доступа и другие свойства каталога, -v обеспечивает вербальный вывод, поэтому вы можете следить за прогрессом.

Внимание: убедитесь, что отсутствуют завершающие слеши после имён директорий, они могут появиться при использовании автозавершения по tab. Когда присутствует завершающий слеш, rsync сбросит содержимое директории в точку монтирования вместо перенесения директории mysql.

sudo rsync -av /var/lib/mysql /home/mial

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

sudo mv /var/lib/mysql /var/lib/mysql.bak

Теперь мы готовы переключиться на настройку.

Что делать?

Во-первых, нужно убедиться, что вы используете правильные имя пользователя и пароль. Для этого нужно подключиться к MySQL с правами администратора (если ошибка 1045 не дает такой возможности, то нужно перезапустить сервер MySQL в режиме —skip-grant-tables), посмотреть содержимое таблицы user служебной базы mysql, в которой хранится информация о пользователях, и при необходимости отредактировать её.

Если изначально была ошибка:

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

значит вы указывали при подключении неверный пароль, так как пользователь root@localhost существует. Сам пароль храниться в зашифрованном виде и его нельзя узнать, можно лишь задать новый

SET PASSWORD FOR root@localhost=PASSWORD(‘новый пароль’);
ERROR 1045 (28000): Access denied for user ‘ODBC’@’localhost’ (using password: YES)

в данном случае в таблице привилегий отсутствует пользователь ‘ODBC’@’localhost’. Его нужно создать, используя команды GRANT, CREATE USER и SET PASSWORD.

Экзотический пример. Устанавливаете новый пароль для root@localhost в режиме —skip-grant-tables, однако после перезагрузки сервера по прежнему возникает ошибка при подключении через консольный клиент:ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
Оказалось, что было установлено два сервера MySQL, настроенных на один порт.

Дефрагментация

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

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

Get-MailboxDatabase -Status | ft Name, DatabaseSize, AvailableNewMailboxSpace

Пример ответа:

Name DatabaseSize AvailableNewMailboxSpace
—- ———— ————————
Base1 686.4 GB 286.4 MB
Base2 170 GB 69.42 GB

* где DatabaseSize — текущий размер базы; AvailableNewMailboxSpace — пространство, которое можно освободить при дефрагментации.

Саму оптимизацию можно выполнить двумя способами:

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

В текущем подразделе мы рассмотрим первый способ.

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

Если используется база на основе группы DAG, сначала необходимо .

Операция дефрагментации выполняется из Exchange Management Shell с применением утилиты eseutil.

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

cd C:\Program Files\Microsoft\Exchange Server\V14\Mailbox\Base1

Выполняем команду для отмонтирования базы:

Dismount-Database Base1

* напомним, что это приведет к отключению базы и приостановки обслуживания.

Запускаем дефрагментацию:

eseutil /d Base1.edb /t \\share\base1_tmp.edb

* где опция d — имя файла базы; t — путь до временного файла на момент дефрагментации, если его не указать, временный файл будет создан в каталоге с основным файлом и, в таком случае, нужно убедиться, что на диске достаточно свободного места (110% от размер дефрагментируемого файла).

После завершения операции, снова подключаем базу:

Mount-Database Base1

Шаг 4 — Перезапуск MySQL

Теперь мы готовы запустить MySQL.

sudo systemctl start mysql
sudo systemctl status mysql

Чтобы убедиться, что новая директория data действительно используется, зайдите в MySQL:

mysql -u root -p

И снова чтобы увидеть информацию о директории data введите:

select @@datadir;

+-------------------+
| @@datadir         |
+-------------------+
| /home/mial/mysql/ |
+-------------------+
1 row in set (0.00 sec)

Примечание: если значение не изменилось, то попробуйте перезапустить весь сервер, а не только службу MySQL.

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

sudo rm -rf /var/lib/mysql.bak

Перезапустите MySQL последний раз, чтобы проверить, что всё работает как и ожидалось:

sudo systemctl restart mysql
sudo systemctl status mysql

Как перейти с MySQL на MariaDB?

Из MySQL в MariaDB — одна из самых нелепо простых миграций, которые вы когда-либо делали в своей жизни. По сути, все, что вам нужно сделать, это:

Убедитесь, что ваш менеджер пакетов имеет доступ к MariaDB.
Остановите MySQL
Установите MariaDB

Например, если вы работаете в Ubuntu 18.04, первым шагом является добавление источников MariaDB в вашу систему следующим образом:

Теперь пришло время остановить MySQL:

и затем установите MariaDB, который запустится после завершения:

Вот и все — вы успешно мигрировали с MySQL на MariaDB.

Конечно , должно быть что-то большее, чем просто 2-3 команды? Как насчет дампа и импорта баз данных? А как насчет пользователей базы данных, паролей и привилегий? А как насчет настроек оптимизации (для администраторов БД)?

Честно говоря, есть нулевая потребность в какой — либо из него. MariaDB является «заменой» MySQL. Помните, что это просто ветка с открытым исходным кодом существующего проекта MySQL, и поэтому обязательно то же самое. Как я уже говорил, различия со временем будут увеличиваться, но на данный момент ничего не нужно делать, кроме остановки MySQL и установки MariaDB.

Наконец, если вы работаете не на Ubuntu 18.04, а на другой платформе, MariaDB имеет полезный инструмент, который может генерировать необходимые вам команды для простого копирования и вставки.

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

Не менее важно, провести тщательное исследование совместимости устанавливаемой версии MariaDB, если вы используете предварительно созданные сторонние инструменты (такие как Magento, Drupal и т.д.)

Скопируйте базу данных MySQL с одного сервера на другой

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

Экспортируйте базу данных на исходном сервере в файл дампа SQL.
Скопируйте файл дампа SQL на конечный сервер
Импортируйте файл дампа SQL на конечный сервер

Давайте посмотрим, как скопировать базу данных classicmodels с сервера на другой.

Сначала экспортируйте базу данных classicmodels в файл db.sql.

>mysqldump -u root -p --databases classicmodels > d:\db\db.sql
Enter password: **********

Обратите внимание , что опция –database позволяет включить mysqldump как операторы CREATE DATABASE и USE в файле SQL дамп. Эти операторы создадут базу данных classicmodels на конечном сервере и сделают новую базу данных базой данных по умолчанию для загрузки данных

Вкратце, следующие операторы включаются в начало файла дампа SQL, когда мы используем –databaseoption.

CREATE DATABASE `classicmodels`.
USE `classicmodels`;

В-третьих, импортируйте файл db.sql на сервер базы данных, предполагая, что файл db.sql был скопирован в папку c:\tmp\.

>mysql -u root -p classicmodels < c:\tmp\db.sql

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

История перед MariaDB

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

MySQL был первоначально выпущен в 1995 году Майклом Видениусом, чтобы предложить альтернативу ограниченным, дорогостоящим предложениям, таким как Microsoft SQL Server или Oracle. После того как MySQL стал настолько популярным, что довольно скоро разработчики забыли, что SQL и MySQL — это две разные вещи.

Оценки меняются, но справедливо сказать, что команды MySQL на момент написания статьи составляли около 45% рынка баз данных.

Короче говоря, MySQL была приобретена Sun Microsystems, которая в свою очередь была приобретена компанией Oracle. В результате крупнейший в мире движок баз данных с открытым исходным кодом принадлежал самой успешной в мире коммерческой базе данных. Для оригинальных создателей MySQL это был смертельный колокол для движка базы данных, используемого и любимого миллионами.

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

В результате в 2010 году родился MySQL с чистым GPL-форком под названием MariaDB.

Теперь вы должны перейти на MariaDB?

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

Например, FAQ по установке WordPress упоминает MySQL и MariaDB на одном дыхании.

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

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

Создание таблицы с Primary и Foreign ключами

Перед тем как всё-таки перейдём к созданию таблицы, нужно ознакомиться с двумя фундаментальными концепциями реляционных баз данных, это primary и foreign ключи.

Ключ primary (главный) содержит значение, которое индивидуально определяет каждый ряд или запись в таблице. А foreign (буквально «внешний») используется для создания связи между данными в двух таблицах, и контролирования данных, которые могут сохраняться в таблице, где размещён foreign ключ. Обычно для primary и foreign ключей выбирают тип INT.

Для иллюстрации, давайте воспользуемся BookstoreDB и создадим две таблицы с именами AuthorsTBL и BooksTBL как показано ниже. Константа NOT NULL означает, что данное поле требует значение и не может быть NULL (не заданным).

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

Если сейчас мы начнём вводить данные, СУБД не будет знать, для какой базы данных они предназначаются. На сервере может быть множество БД – в какой из них пользователь создаёт таблицы? Чтобы разрешить эту неопределённость используется команда USE, после которой указывается имя базы данных, с который вы собираетесь работать:

USE BookstoreDB;

Посмотрите на изменившееся приглашение командной строки:

Теперь все введённые команды и данные будут относиться к БД BookstoreDB.

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

CREATE TABLE AuthorsTBL (
	AuthorID INT NOT NULL AUTO_INCREMENT,
	AuthorName VARCHAR(100),
	PRIMARY KEY(AuthorID)
);
CREATE TABLE BooksTBL (
	BookID INT NOT NULL AUTO_INCREMENT,
	BookName VARCHAR(100) NOT NULL,
	AuthorID INT NOT NULL,
	BookPrice DECIMAL(6,2) NOT NULL,
	BookLastUpdated TIMESTAMP,
	BookIsAvailable BOOLEAN,
	PRIMARY KEY(BookID),
	FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
);

Как видим, команда создания таблицы имеет вид

CREATE TABLE имя_таблицы (
	имя_колонки1 ТИП ДАННЫХ,
	имя_колонки2 ТИП ДАННЫХ
);

Команду можно было вводить построчно или скопировать и вставить за один раз. При построчном вводе СУБД в качестве окончания вводимой команды ожидает точки с запятой (;). Также команду можно было записать в одну строку, например:

CREATE TABLE имя_таблицы (имя_колонки1 ТИП ДАННЫХ, имя_колонки2 ТИП ДАННЫХ);

Никакой разницы нет.

Теперь мы может продолжить и начать вводить данные в AuthorsTBL и BooksTBL.

Как установить пароль root для начала использования MySQL

Для только что установленной MySQL пароль пользователя root является пустым.

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

sudo mysql -u root

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

sudo mysql_secure_installation

Давайте внимательно разберёмся со всем, что говорит нам этот скрипт, поскольку вопросам безопасности вер-сервера следует уделять особое внимание.

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

Перевод:

Примечание: запуск всех элементов этого скрипта рекомендуется для всех серверов MariaDB в продакшене (реальном рабочем использовании).

In order to log into MariaDB to secure it, we'll need the current password for the root user.  If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here.

Перевод:

Для того, чтобы войти в MariaDB для настройки безопасности, нам нужен текущий пароль пользователя root. Если вы только что установили MariaDB и ещё не установили пароль рута, то пароль будет пустым, т.е. просто нажмите Enter.

Затем у нас спрашивают:

OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n

Перевод:

ОК, пароль успешно использован, идём дальше …

Установка пароля root или использование unix_socket гарантирует, что никто не сможет войти в систему под пользователем root MariaDB без надлежащей авторизации.

Ваша учётная запись root уже защищена, поэтому вы можете спокойно ответить «n».

Переключиться на аутентификацию unix_socket [Y/n] n

Я ответил n (нет).

Далее новый вопрос:

Change the root password? [Y/n]

Перевод:

Изменить пароль root?

Отвечаем Y (да) и дважды вводим новый пароль. Не используйте в пароле символ точка с запятой и вообще аккуратнее со специальными символами в пароле.

By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them.  This is intended only for testing, and to make the installation go a bit smoother.  You should remove them before moving into a production environment.
Remove anonymous users? [Y/n]

Перевод:

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

Удалить анонимного пользователя? [Да/нет]

Затем:

Normally, root should only be allowed to connect from 'localhost'.  This ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n]

Перевод:

Обычно, root’у следует разрешать подключаться только с ‘localhost’. Это гарантирует, что кто-то из сети не сможет угадать пароль root’а.

Отключить удалённый вход рута? [Да/нет]

Затем:

By default, MariaDB comes with a database named 'test' that anyone can access.  This is also intended only for testing, and should be removed before moving into a production environment.
Remove test database and access to it? [Y/n]

Перевод:

По умолчанию MariaDB поставляется с базой данных ‘test’, к которой может любой получить доступ. Это также сделано в целях тестирования и она должна быть удалена перед переходом в реальное рабочее окружение.

Удалить тестовую базу данных и доступ к ней? [Да/нет]

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
Reload privilege tables now? [Y/n]

Перевод:

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

Перезагрузить таблицу привилегий сейчас? [Y/n]

Наконец:

All done!  If you've completed all of the above steps, your MariaDB installation should now be secure.

Перевод:

Всё сделано! Если вы завершили все вышеописанные шаги, ваша установленная MariaDB должна быть безопасной.

Ещё раз о пароле MariaDB. Это должен быть надёжный и уникальный пароль. Думайте о нём как о пароле входа на ваш сервер. Он должен отличаться от пароля пользователя Linux.