Сервер MariaDB

Описание программного обеспечения

Система управления базами данных MariaDB является ответвлением MySQL и поддерживается сообществом разработчиков ПО с открытым исходным кодом. Система управления базами данных MariaDB использует стандартную общественную лицензию (GPL). MariaDB спроектирована так, чтобы обеспечить полную совместимость с MySQL, включая API и командные строки, поэтому она может служить заменой MySQL. В MariaDB также имеется множество новых функций.

На рисунке показана архитектура MariaDB:

image$mariadb logical architecture

Когда MariaDB получает инструкцию SQL, процесс выполнения выглядит следующим образом.

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

  2. Если вход в систему выполнен успешно, клиент отправляет команды SQL на сервер. Средство синтаксического анализа обрабатывает инструкции SQL.

  3. Сервер проверяет, имеется ли у клиента разрешение на получение необходимых ресурсов.

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

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

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

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

Каждая подсистема хранилища управляет данными и хранит их по-разному и поддерживает разные функции и производительность. Например:

  • MyISAM: подходит для сред с большим количеством операций чтения и меньшим количеством операций записи. Она не поддерживает транзакции и поддерживает полнотекстовые индексы.

  • noDB: поддерживает транзакции, блокировки строк и внешние ключи.

  • MEMORY: хранит данные в памяти.

  • CSV: хранит данные в формате CSV.

Настройка среды

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

Отключение брандмауэра и автоматического запуска

Рекомендуется отключить брандмауэр в тестовой среде, чтобы исключить нарушение работы сети. Настройте брандмауэр в соответствии с фактическими требованиями.

  1. Остановите службу брандмауэра от имени пользователя root.

    systemctl stop firewalld

  2. Отключите службу брандмауэра от имени пользователя root.

    systemctl disable firewalld
    Автоматический запуск будет автоматически отключен, поскольку отключен брандмауэр.

Отключение SELinux

Измените файл конфигурации от имени пользователя root.

sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux

Создание группы пользователей и пользователя

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

  1. Создайте пользователя или группу пользователей MySQL от имени пользователя root:

    groupadd mysql
    useradd -g mysql mysql

  2. Задайте пароль для пользователя от имени пользователя root.

    passwd mysql

    Введите пароль дважды для подтверждения.

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

Если необходимо выполнить тест производительности, для каталога данных требуется отдельный диск. Необходимо отформатировать диск и подключить его. Подробнее см. в разделе "Способ 1" или "Способ 2".

В тесте, отличном от теста производительности, выполните команду от имени пользователя root, чтобы создать каталог данных. Затем пропустите этот раздел.

mkdir /data

Способ 1. Использование fdisk для управления дисками от имени пользователя root

  1. Создайте раздел, например /dev/sdb.

    fdisk /dev/sdb

  2. Введите n и нажмите клавишу Enter.

  3. Введите p и нажмите клавишу Enter.

  4. Введите 1 и нажмите клавишу Enter.

  5. Сохраните настройки по умолчанию и нажмите клавишу Enter.

  6. Сохраните настройки по умолчанию и нажмите клавишу Enter.

  7. Введите w и нажмите клавишу Enter.

  8. Создайте файловую систему, например xfs:

    mkfs.xfs /dev/sdb1

  9. Подключите раздел к /data для ОС.

    mkdir /data
    mount /dev/sdb1 /data

  10. Отредактируйте файл /etc/fstab, чтобы разрешить автоматическое подключение диска данных после перезапуска системы. Например, добавьте содержимое в последнюю строку, как показано ниже:

    #
# /etc/fstab
# Created by anaconda on Tue Nov  5 16:17:11 2019
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for for info
#
/dev/mapper/centos-root                   /          xfs   defaults                    0  0
UUID=fa02453a-0d4c-4a30-bf6c-c4d01cbd2c86 /boot      xfs   defaults                    0  0
UUID=006C-8EE2                            /boot/efi  vfat  umask=0077,shortname=winnt  0  0
/dev/mapper/centos-home                   /home      xfs   defaults                    0  0
/dev/mapper/centos-swap                   swap       swap  defaults                    0  0
/dev/nvme0n1p1                            /data      xfs   defaults                    1  2

Способ 2. Использование LVM для управления дисками от имени пользователя root

Установите пакет LVM2 в образ, как указано ниже.

  1. Настройте локальный источник YUM. Подробнее см. в разделе Настройка сервера репозитория. Если репозиторий настроен, пропустите этот шаг.

  2. Установите LVM2:

    yum install lvm2

  1. Создайте физический том, например sdb.

    pvcreate /dev/sdb

  2. Создайте группу физических томов, например datavg.

    vgcreate datavg  /dev/sdb

  3. Создайте логический том, например datalv объёмом 600 ГБ.

    lvcreate -L 600G -n datalv datavg

  4. Создайте файловую систему.

    mkfs.xfs /dev/datavg/datalv

  5. Создайте каталог данных и подключите его.

    mkdir /data
    mount /dev/datavg/datalv /data

  6. Чтобы разрешить автоматическое подключение диска данных после перезапуска системы, отредактируйте файл /etc/fstab. Например, добавьте содержимое в последнюю строку, как показано ниже:

    /dev/databg/datalv  /data  xfs  defaults  1  2

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

В созданном каталоге данных /data создайте каталоги для процессов и предоставьте разрешения группе MySQL или пользователю, созданным от имени пользователя root:

mkdir -p /data/mariadb
cd /data/mariadb
mkdir data tmp run log
chown -R mysql:mysql /data

Установка, запуск и удаление сервера MariaDB

Установка MariaDB

  1. Настройте локальный источник YUM. Подробнее см. в разделе Настройка сервера репозитория.

  2. Очистите кеш:

    dnf clean all

  3. Создайте кеш:

    dnf makecache

  4. Установите сервер MariaDB:

    dnf install mariadb-server

  5. Проверьте установленный пакет RPM.

    rpm -qa | grep mariadb

Запуск сервера MariaDB

  1. Запустите сервер MariaDB от имени пользователя root:

    systemctl start mariadb

  2. Инициализируйте базу данных от имени пользователя root:

    /usr/bin/mysql_secure_installation

    Во время выполнения команды необходимо ввести пароль пользователя базы данных root. Если пароль не задан, нажмите клавишу Enter. Затем задайте пароль.

  3. Войдите в базу данных:

    mysql -u root -p

    После выполнения команды система предложит ввести пароль. Введите пароль, заданный ранее.

Выполните команду \q или exit, чтобы выйти из базы данных.

Удаление MariaDB

  1. Остановите процесс базы данных от имени пользователя root:

    ps -ef | grep mysql
kill -9 <PID>

  2. Выполните команду:

    dnf remove mariadb-server

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

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

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

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

В представленных выше данных:

  • username — имя пользователя.

  • host — имя хоста, то есть имя хоста, на котором пользователь подключается к базе данных. Как локальный пользователь вы можете задать для параметра значение localhost. Если имя хоста не было указано при создании пользователя, по умолчанию используется имя %, что указывает на группу хостов.

  • password — пароль для входа на сервер. Пароль может быть пустым. Если пароль не задан, пользователь может войти на сервер без ввода пароля. Однако это не рекомендуется, поскольку безопасность может оказаться под угрозой.

Чтобы использовать инструкцию CREATE USER, требуется разрешение INSERT для базы данных или глобальное разрешение CREATE USER.

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

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

Пример

  • Создание локального пользователя с паролем 123456 и именем пользователя userexample1:

    CREATE USER 'userexample1'@'localhost' IDENTIFIED BY '123456';

  • Создание пользователя с паролем 123456, именем пользователя userexample2 и именем хоста 192.168.1.100:

    CREATE USER 'userexample2'@'192.168.1.100' IDENTIFIED BY '123456';

Просмотр пользователей

Выполните инструкцию SHOW GRANTS или SELECT, чтобы просмотреть одного или нескольких пользователей.

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

SHOW GRANTS [FOR 'username'@'hostname'];
SELECT USER,HOST,PASSWORD FROM mysql.user WHERE USER='username';

Просмотр всех пользователей:

SELECT USER,HOST,PASSWORD FROM mysql.user;

В представленных выше данных:

  • username — имя пользователя;

  • hostname — имя хоста.

Пример

  • Просмотр пользователя userexample1:

    SHOW GRANTS FOR 'userexample1'@'localhost';

  • Просмотр всех пользователей в базе данных MySQL:

    SELECT USER,HOST,PASSWORD FROM mysql.user;

Изменение пользователей

Изменение имени пользователя

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

RENAME USER 'oldusername'@'hostname' TO 'newusername'@'hostname';

В представленных выше данных:

  • oldusername — исходное имя пользователя;

  • newusername — новое имя пользователя;

  • hostname — имя хоста.

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

Чтобы использовать инструкцию RENAME USER, требуется разрешение UPDATE для базы данных или глобальное разрешение CREATE USER.

Пример изменения пользователя

Переименование пользователя userexample1 на userexample2 и переименование хоста на locahost.

RENAME USER 'userexample1'@'localhost' TO 'userexample2'@'localhost';

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

Используйте инструкцию SET PASSWORD, чтобы изменить пароль пользователя для входа в систему.

SET PASSWORD FOR 'username'@'hostname' = PASSWORD('newpassword');

В представленных выше данных:

  • FOR 'username'@'hostname': имя пользователя и имя хоста для изменения пароля. Это необязательный параметр.

  • PASSWORD('newpassword'): указывает, что для задания нового пароля используется функция PASSWORD(). То есть новый пароль следует передать в функцию PASSWORD() для шифрования.

Функция PASSWORD() — это функция одностороннего шифрования. После того как исходный открытый текст зашифрован, расшифровать его не удастся.

Если в инструкцию SET PASSWORD не добавлено предложение FOR, пароль текущего пользователя будет изменен.

Предложение FOR должно быть указано в формате '<username>'@'<hostname>', где <username> — это имя пользователя учётной записи, а <hostname> — имя хоста учётной записи.

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

Пример изменения пароля пользователя

Изменение пароля пользователя userexample с именем хоста locahost на 0123456.

SET PASSWORD FOR 'userexample'@'localhost' = PASSWORD('0123456') ;

Удаление пользователей

Используйте инструкцию DROP USER, чтобы удалить одну или несколько учётных записей пользователей и соответствующие разрешения.

DROP USER 'username1'@'hostname1' [,'username2'@'hostname2']...;

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

Инструкцию DROP USER можно использовать для удаления одной или нескольких учётных записей базы данных и их исходных разрешений.

Чтобы использовать инструкцию DROP USER, требуется разрешение DELETE для базы данных или глобальное разрешение CREATE USER.

В инструкции DROP USER, если имя хоста учётной записи не указано, по умолчанию используется имя %.

Пример

Удаление локального пользователя userexample.

DROP USER 'userexample'@'localhost';

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

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

GRANT PRIVILEGES ON databasename.tablename TO 'username'@'hostname';

В представленных выше данных:

  • Предложение ON: указывает объект и его уровень, на котором предоставляется разрешение.

  • privileges: указывает разрешения пользователя на выполнение операций, таких как SELECT, INSERT и UPDATE. Чтобы предоставить пользователю все разрешения, используйте ALL.

  • databasename — имя базы данных.

  • tablename — имя таблицы.

  • Предложение TO: задает пароль пользователя и указывает пользователя, которому предоставляется разрешение.

  • username — имя пользователя.

  • hostname — имя хоста.

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

GRANT PRIVILEGES ON *.* TO 'username'@'hostname';

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

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

Пример

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

GRANT SELECT,INSERT ON *.* TO 'userexample'@'localhost';

Удаление разрешений пользователя

Выполните инструкцию REVOKE, чтобы удалить разрешения пользователя. Пользователь при этом не будет удален.

REVOKE privilege ON databasename.tablename FROM 'username'@'hostname';

Параметры в инструкции REVOKE такие же, как и в инструкции GRANT.

Чтобы использовать инструкцию REVOKE, требуется глобальное разрешение CREATE USER или разрешение UPDATE для базы данных.

Пример

Удаление разрешения INSERT локального пользователя userexample:

REVOKE INSERT ON *.* FROM 'userexample'@'localhost';

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

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

Выполните инструкцию CREATE DATABASE, чтобы создать базу данных:

CREATE DATABASE databasename;

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

Пример

Создание базы данных с именем databaseexample:

CREATE DATABASE databaseexample;

Просмотр базы данных

Выполните инструкцию SHOW DATABASES, чтобы просмотреть базу данных:

SHOW DATABASES;

Пример

Просмотр всех баз данных:

SHOW DATABASES;

Выбор базы данных

Как правило, перед созданием таблицы или отправкой запроса к ней необходимо выбрать целевую базу данных. Используйте инструкцию USE для выбора базы данных.

USE databasename;

В предыдущей команде параметр databasename — это имя базы данных.

Пример

Выбор базы данных databaseexample:

USE databaseexample;

Удаление базы данных

Чтобы удалить базу данных, можно выполнить инструкцию DROP DATABASE.

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

DROP DATABASE databasename;

В предыдущей команде параметр databasename — это имя базы данных.

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

Чтобы использовать инструкцию DROP DATABASE, требуется разрешение DROP для базы данных.

DROP SCHEMA является синонимом DROP DATABASE.

Пример

Удаление базы данных databaseexample.

DROP DATABASE databaseexample;

Резервное копирование базы данных

Чтобы создать резервную копию базы данных, выполните команду mysqldump от имени пользователя root.

  • Резервное копирование одной или нескольких таблиц:

    mysqldump [options] databasename [tablename ...] > outfile

  • Резервное копирование одной или нескольких баз данных:

    mysqldump [options] -databases databasename ... > outfile

  • Резервное копирование всех баз данных:

    mysqldump [options] -all-databases > outputfile

В представленных выше данных:

  • databasename — имя базы данных.

  • tablename — имя таблицы данных.

  • outfile — файл резервной копии базы данных.

  • options — параметр команды mysqldump. Можно ввести несколько параметров через пробел. Общие параметры команды mysqldump:

    • -u, --user:имя пользователя.

    • -p, --password: пароль.

    • -P, --port: номер порта.

    • -h, --host: имя хоста.

    • -r, --result-file: сохраняет результат экспорта в указанный файл, что эквивалентно >.

    • -t: резервное копирование только данных.

    • -d: резервное копирование только структуры таблицы.

Пример

Резервное копирование всех баз данных пользователя root на хосте 192.168.202.144 через порт 3306 в файл alldb.sql.

mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 --all-databases > alldb.sql

Резервное копирование базы данных db1 пользователя root на хосте 192.168.202.144 через порт 3306 в файл db1.sql.

mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 --databases db1 > db1.sql

Резервное копирование таблицы tb1 базы данных db1 пользователя root на хосте 192.168.202.144 через порт 3306 в файл db1tb1.sql.

mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 db1 tb1 > db1tb1.sql

Резервное копирование только структуры таблицы базы данных db1 пользователя root на порту 3306 хоста с IP-адресом 192.168.202.144 в файл db1.sql.

mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 -d db1 > db1.sql

Резервное копирование только данных базы данных db1 пользователя root на хосте 192.168.202.144 через порт 3306 в файл db1.sql.

mysqldump -h 192.168.202.144 -P 3306 -uroot -p123456 -t db1 > db1.sql

Восстановление базы данных

Чтобы восстановить базу данных, выполните команду mysql от имени пользователя root.

Восстановление одной или нескольких таблиц:

mysql -h hostname -P portnumber -u username -ppassword databasename < infile

В представленных выше данных:

  • hostname — имя хоста.

  • portnumber — номер порта.

  • username — имя пользователя.

  • password — пароль.

  • databasename — имя базы данных.

  • infile — параметр outfile в команде mysqldump.

Пример

Восстановление базы данных.

mysql -h 192.168.202.144 -P 3306 -uroot -p123456 -t db1 < db1.sql