MariaDB. Установка и настройка

Установка MariaDB

Тут все просто — нужно установить пакет mariadb-server

# apt install mariadb-server

Установка проходит без запроса пароля, без внесения изменений в конфигурацию по умолчанию. В таком виде использовать MariaDB на production сервере нежелательно, потому что она будет работать в небезопасном режиме. Чтобы исправить ситуацию запустим скрипт mariadb-secure-installation, поставляемый в комплекте.

# mariadb-secure-installation
..........
Enter current password for root (enter for none): Enter
OK, successfully used password, moving on...

Switch to unix_socket authentication [Y/n] N
 ... skipping.

Change the root password? [Y/n] [Y/n] Y
New password: пароль
Re-enter new password: пароль

Remove anonymous users? [Y/n] Y
 ... Success!

Disallow root login remotely? [Y/n] Y
 ... Success!

Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reload privilege tables now? [Y/n] Y
 ... Success!

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

Вместо этого мы задаем пароль для пользователя базы данных root — что на production сервере делать нежелательно, лучше согласиться на unix_socket. После этого удаляем анонимных пользователей, тестовые базы данных и сбрасываем привилегии.

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

# mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 46
Server version: 10.11.8-MariaDB-0ubuntu0.24.04.1 Ubuntu 24.04

> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0,000 sec)

> SELECT user, plugin, host FROM mysql.user;
+-------------+-----------------------+-----------+
| User        | plugin                | Host      |
+-------------+-----------------------+-----------+
| mariadb.sys | mysql_native_password | localhost |
| root        | mysql_native_password | localhost |
| mysql       | mysql_native_password | localhost |
+-------------+-----------------------+-----------+
3 rows in set (0,002 sec)

> exit
Bye

При подключении меня ждал неприятный сюрприз — почему-то не было запроса пароля. Оказывается, таким образом разработчики решили упростить жизнь пользователям — теперь возможно использование нескольких плагинов аутентификации. И для пользователя root сначала используется плагин unix_socket, а в случае неудачи — mysql_native_password.

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

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

Тут все просто — подключаемся к серверу базы данных и выполняем запрос

CREATE DATABASE `wp_blog_data` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

# mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
..........
> CREATE DATABASE `wp_blog_data` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0,001 sec)

> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wp_blog_data       |
+--------------------+
5 rows in set (0,000 sec)

> exit
Bye

Для удаления базы данных — выполняем следующий запрос

DROP DATABASE `wp_blog_data`

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

Тут тоже просто — подключаемся к серверу базы данных и выполняем запрос

CREATE USER `wp_blog_user`@`localhost` IDENTIFIED BY 'qwerty'

ALTER USER `wp_blog_user`@`localhost` IDENTIFIED BY 'qwe123'
SET PASSWORD FOR `wp_blog_user`@`localhost` = PASSWORD('qwe123')

CREATE USER `wp_blog_user`@`localhost` IDENTIFIED VIA `mysql_native_password` USING PASSWORD('qwerty') OR `unix_socket`

На данном этапе новый пользователь wp_blog_user не имеет прав делать что-либо с базами данных. Давайте предоставим ему доступ к новой базе данных wp_blog_data. Но сначала явно лишим его привилегий на чужие базы — на всякий случай.

GRANT USAGE ON `*`.`*` TO `wp_blog_user`@`localhost`
GRANT ALL PRIVILEGES ON `wp_blog_data`.`*` TO `wp_blog_user`@`localhost`

Пользователь wp_blog_user может выполнять любые действия над всеми таблицами базы данных wp_blog_data. Чтобы изменения вступили в силу — нужно дать команду серверу БД перечитать привилегии.

FLUSH PRIVILEGES

Общий синтаксис GRANT имеет вид

GRANT [Привилегии] ON [БазаДанных.Таблица] TO [Пользователь] [WITH GRANT OPTION]

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

GRANT ALL PRIVILEGES ON `*`.`*` TO `db_admin`@`localhost` IDENTIFIED BY 'qwerty' WITH GRANT OPTION

WITH GRANT OPTION дает возможность назначать другим пользователям любые привилегии, которые есть у самого пользователя.

Список прав доступа, которые могут получить пользователи

• ALL PRIVILEGES — полный доступ к заданной базе данных
• CREATE — позволяет создавать новые таблицы или базы данных
• DROP — позволяет удалять таблицы или базы данных
• DELETE — позволяет удалять строки из таблиц
• INSERT — позволяет добавлять строки в таблицу
• SELECT — позволяет делать выборку из таблиц
• UPDATE — позволит редактировать строки таблиц

Каждый раз после изменения прав доступа нужно использовать FLUSH PRIVILEGES.

Лишение прав доступа практически идентично их назначению

REVOKE [Привилегии] ON [БазаДанных.Таблица] FROM [Пользователь]

Отменим привилегию GRANT OPTION у пользователя db_admin

REVOKE GRANT OPTION ON `*`.`*` FROM `db_admin`@`localhost`

Для удаления пользователя используется команда

DROP USER [Пользователь]

Удалим пользователя db_admin

DROP USER `db_admin`@`localhost`

Файлы конфигурации

Файлов конфигурации несколько — это /etc/my.cnf, /etc/mysql/my.cnf и ~/.my.cnf. Опции из каждого следующего файла конфигурации перезаписывают такие же опции из предыдущих. Файла /etc/my.cnf после установки сервера MariaDB не существует — и лучше его не создавать. Файл /etc/mysql/my.cnf является символической ссылкой на /etc/mysql/mariadb.cnf.

$ ls -la /etc/mysql
total 32
drwxr-xr-x   4 root root 4096 июн 26 09:35 .
drwxr-xr-x 108 root root 4096 июн 26 09:35 ..
drwxr-xr-x   2 root root 4096 июн 26 09:34 conf.d
-rw-------   1 root root  544 июн 26 09:35 debian.cnf
-rwxr-xr-x   1 root root 1756 фев  7 02:51 debian-start
-rw-r--r--   1 root root 1126 фев  6 05:21 mariadb.cnf
drwxr-xr-x   2 root root 4096 июн 26 09:35 mariadb.conf.d
lrwxrwxrwx   1 root root   24 окт 20  2020 my.cnf -> /etc/alternatives/my.cnf
-rw-r--r--   1 root root  839 окт 20  2020 my.cnf.fallback

Посмотрим на файл /etc/mysql/mariadb.cnf

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Директива !includedir предписывает прочитать все файлы .cnf в директориях /etc/mysql/conf.d и /etc/mysql/mariadb.conf.d. Первая из них — традиционная директория файлов конфигурации MySQL, нужна для совместимости, при переходе от MySQL к MariaDB. Во второй — все настройки для сервера и клиентов MariaDB, именно там нужно вносить изменения.

$ ls -la /etc/mysql/mariadb.conf.d
total 48
drwxr-xr-x 2 root root 4096 июн 26 09:35 .
drwxr-xr-x 4 root root 4096 июн 26 09:35 ..
-rw-r--r-- 1 root root  575 фев  6 05:21 50-client.cnf
-rw-r--r-- 1 root root  231 фев  7 02:51 50-mysql-clients.cnf
-rw-r--r-- 1 root root  927 фев  7 02:51 50-mysqld_safe.cnf
-rw-r--r-- 1 root root 3759 фев  7 02:51 50-server.cnf
-rw-r--r-- 1 root root  570 фев  7 02:51 60-galera.cnf

Файл 50-client.cnf содержит общие настойки для всех клиентов MariaDB и MySQL, включает в себя секции [client] и [client-mariadb].

Файл 50-mysql-clients.cnf содержит настойки для разных клиентов MariaDB и MySQL, включает в себя секции [mysql], [mysql_upgrade], [mysqladmin], [mysqlbinlog], [mysqlcheck], [mysqldump], [mysqlimport], [mysqlshow], [mysqlslap].

Файл 50-server.cnf содержит настройки сервера для MariaDB и MySQL, включает в себя секции [server], [mysqld], [mariadb] и [mariadb-X.Y]. В большинстве случаев редактировать нужно именно этот файл конфигурации.

Файл 60-galera.cnf содержит настройки кластера Galera.

Опции сервера и клиента

Все опции MariaDB можно разделить на два типа — опции сервера и опции клиентов. Сервер при запуске читает опции сервера, клиенты при запуске читают опции клиента. Файлы конфигурации состоят из одной или нескольких групп опций, например [server], [client] и т.д.

Опции сервера расположены в следующих группах

Опции клиента расположены в следующих группах

Опции инструментов

Многие инструменты MariaDB также считывают параметры из своих собственных групп опций.

Проверка конфигурации

Чтобы проверить, какие файлы конфигурации были прочитаны и какие настойки используются — можно запустить сервер или клиент с опциями --help и --verbose.

$ mariadb --help --verbose # клиент
mariadb  Ver 15.1 Distrib 10.11.7-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Usage: mariadb [OPTIONS] [database]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysql mariadb-client client client-server client-mariadb
The following options may be given as the first argument:
--print-defaults          Print the program argument list and exit.
--no-defaults             Don't read default options from any option file.
The following specify which files/extra groups are read (specified before remaining options):
--defaults-file=#         Only read default options from the given file #.
--defaults-extra-file=#   Read this file after the global files are read.
..........

$ mariadbd --help --verbose # сервер
mariadbd  Ver 10.11.7-MariaDB-2ubuntu2 for debian-linux-gnu on x86_64 (Ubuntu 24.04)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Starts the MariaDB database server.

Usage: mariadbd [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-10.11 mariadb mariadb-10.11 mariadbd mariadbd-10.11 client-server galera
The following options may be given as the first argument:
--print-defaults          Print the program argument list and exit.
--no-defaults             Don't read default options from any option file.
The following specify which files/extra groups are read (specified before remaining options):
--defaults-file=#         Only read default options from the given file #.
--defaults-extra-file=#   Read this file after the global files are read.
..........

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

$ mariadb --print-defaults
mariadb would have been started with the following arguments:
--socket=/run/mysqld/mysqld.sock

$ mariadb-dump --print-defaults
mariadb-dump would have been started with the following arguments:
--socket=/run/mysqld/mysqld.sock --quick --quote-names --max_allowed_packet=16M

$ mariadbd --print-defaults
mariadbd would have been started with the following arguments:
--socket=/run/mysqld/mysqld.sock --pid-file=/run/mysqld/mysqld.pid --basedir=/usr --bind-address=127.0.0.1
--expire_logs_days=10 --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci

Опции конфигурации

Все опции рассмотреть будет затруднительно — так что рассмотрим лишь некоторые

• user — пользователь ОС, под которым работает сервер (настройка сервера)
• user — пользователь БД, который подключается к серверу (настройка клиента)
• basedir — директория установки сервера MariaDB
• datadir — директория хранения файлов данных
• bind-address — ip-адрес сетевого интерфейса для прослушивания соединений
• port — порт для прослушивания сетевых соединений
• skip-networking — запретить работу по сети, даже через интерфейс обратной петли
• socket — имя файла unix-сокета (это отменяет работу по сети)
• log_output — куда сохранять логи: FILE (файл), TABLE (таблицы БД), NONE (не сохранять)
• general-log — логировать или нет все запросы к серверу
• general-log-file — файл для сохранения всех запросов к серверу
• skip-log-error — не логировать ошибки
• log-error — файл лога ошибок
• log-slow-query или slow-query-log — логировать или нет медленные запросы
• log-slow-query-file или slow-query-log-file — файл лога медленных запросов

Пример файла конфигурации

[mariadbd]
user = mysql
pid-file = /run/mysqld/mysqld.pid
basedir = /usr
datadir = /var/lib/mysql

# Запрещаем (1) или разрешаем (0) принимать сетевые соединения
skip-networking = 1
# Принимать соединения от клиентов через unix-сокет, не по сети
socket = /run/mysqld/mysqld.sock
# Разрешаем принимать сетевые соединения на всех интерфейсах
# bind-address = 0.0.0.0
# Разрешаем принимать только локальные соединения на 127.0.0.1
# bind-address = 127.0.0.1
# Принимать соединения на указанном интерфейсе на порту 3306
# port = 3306

log-output = FILE
# Разрешаем логирование всех запросов и задаем имя файла лога
# Включать только при необходимости, файл лога растет быстро!
general-log = 1
general-log-file = /var/log/mysql/mysql.log
# Разрешаем логирование ошибок и задаем имя файла лога ошибок
# skip-log-error
log-error = /var/log/mysql/error.log
# Разрешаем логирование медленных запросов и задаем имя файла
log-slow-query = 1
log-slow-query-file = /var/log/mysql/slow.log
log-slow-query-time = 10
log-slow-verbosity = query_plan,explain

[client-mariadb]
socket = /run/mysqld/mysqld.sock

[mariadb-dump]
quick
quote-names
max-allowed-packet = 16M

Для логов нужно создать директорию /var/log/mysql, сделать ее владельцем пользователя mysql и разрешить запись в эту директорию.

# mkdir -m 2750 /var/log/mysql
# chown mysql /var/log/mysql

Префиксы для опций

MariaDB поддерживает определенные префиксы, которые можно использовать с опциями — enable, disable (или skip), autoset, maximum.

[mariadb]
# determine a good value automatically
autoset_open_files_limit
# disable the unix socket plugin
disable_unix_socket
# enable the slow query log
enable_slow_query_log
# set max_allowed_packet to maximum value
maximum_max_allowed_packet
# disable external locking for MyISAM
skip_external_locking

Подключение по сети

До этого момента мы подключались с серверу MariaDB через unix-сокет. Давайте настроим сервер, чтобы клиенты могли подключаться по сети. Для начала — через интерфейс обратной петли. Для наглядности будем использовать порт 3333 вместо стандартного 3306.

[mariadbd]
user = mysql
pid-file = /run/mysqld/mysqld.pid
basedir = /usr
datadir = /var/lib/mysql

# Запрещаем (1) или разрешаем (0) принимать сетевые соединения
skip-networking = 0
# Разрешаем принимать только локальные соединения на 127.0.0.1
bind-address = 127.0.0.1
# Принимать соединения на указанном интерфейсе на порту 3333
port = 3333

[client-mariadb]
host = 127.0.0.1
port = 3333

$ sudo systemctl restart mariadb.service

Проверим, что сервер базы данных MariaDB прослушивает tcp-порт 3333

$ ss -tlp
State    Recv-Q   Send-Q   Local Address:Port     Peer Address:Port   Process
LISTEN   0        4096     127.0.0.54:domain      0.0.0.0:*           users:(("systemd-resolve",pid=525,fd=17))
LISTEN   0        4096     127.0.0.53%lo:domain   0.0.0.0:*           users:(("systemd-resolve",pid=525,fd=15))
LISTEN   0        80       127.0.0.1:3333         0.0.0.0:*           users:(("mariadbd",pid=3569,fd=25))
LISTEN   0        4096     *:ssh                  *:*                 users:(("sshd",pid=868,fd=3),("systemd",pid=1,fd=93))

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

$ mariadb --print-defaults
mariadb would have been started with the following arguments:
--host=127.0.0.1 --port=3333

Подключаемся к серверу под пользователем wp_blog_user

$ mariadb --user=wp_blog_user --password=qwerty
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.11.7-MariaDB-2ubuntu2-log Ubuntu 24.04

> SELECT CURRENT_USER();
+------------------------+
| CURRENT_USER()         |
+------------------------+
| wp_blog_user@localhost |
+------------------------+
1 row in set (0,000 sec)

> exit
Bye

Изменим настройки сервера, чтобы можно было подключаться через физический сетевой интрефейс с другой машины. У меня сервер MariaDB установлен на виртуальной машине 192.168.110.62, а клиент планирую установить на виртуальную машину 192.186.110.45.

[mariadbd]
user = mysql
pid-file = /run/mysqld/mysqld.pid
basedir = /usr
datadir = /var/lib/mysql

# Запрещаем (1) или разрешаем (0) принимать сетевые соединения
skip-networking = 0
# Разрешаем принимать локальные соединения на интерфейсе 127.0.0.1
# и внешние соединения на физическом интерфейсе 192.168.110.62
bind-address = 127.0.0.1,192.168.110.62
# Принимать соединения на указанном интерфейсе на порту 3333
port = 3333

[client-mariadb]
host = 127.0.0.1
port = 3333

$ sudo systemctl restart mariadb.service

На другой машине установим клиент MariaDB и зададим настройки для подключения

$ sudo apt install mariadb-client

$ sudo nano /etc/mysql/mariadb.cnf

[client-mariadb]
host = 192.168.110.62
port = 3333

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

$ mariadb --print-defaults
mariadb would have been started with the following arguments:
--host=192.168.110.62 --port=3333

Подключаемся к серверу под пользователем wp_blog_user

$ mariadb --user=wp_blog_user --password=qwerty
ERROR 1130 (HY000): Host '192.168.110.45' is not allowed to connect to this MariaDB server

Получаем вполне ожидаемую ошибку — к серверу может подключаться пользователь wp_blog_user@localhost, а пытается подключиться пользователь wp_blog_user@192.168.110.45. Давайте создадим на сервере еще одного пользователя.

$ mariadb --user=root --password=пароль
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.11.7-MariaDB-2ubuntu2-log Ubuntu 24.04

> GRANT ALL PRIVILEGES ON `wp_blog_data`.`*` TO `wp_blog_user`@`192.168.110.45` IDENTIFIED BY 'qwerty';
Query OK, 0 rows affected (0,007 sec)

> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0,001 sec)

> SELECT user, plugin, host FROM mysql.user;
+--------------+-----------------------+----------------+
| User         | plugin                | Host           |
+--------------+-----------------------+----------------+
| mariadb.sys  | mysql_native_password | localhost      |
| root         | mysql_native_password | localhost      |
| mysql        | mysql_native_password | localhost      |
| wp_blog_user | mysql_native_password | localhost      |
| wp_blog_user | mysql_native_password | 192.168.110.45 |
+--------------+-----------------------+----------------+
5 rows in set (0,002 sec)

> exit
Bye

Возвращаемся на виртуальную машину 192.168.110.45 и пробуем подключиться снова

$ mariadb --user=wp_blog_user --password=qwerty
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.11.7-MariaDB-2ubuntu2-log Ubuntu 24.04

> SELECT CURRENT_USER();
+-----------------------------+
| CURRENT_USER()              |
+-----------------------------+
| wp_blog_user@192.168.110.45 |
+-----------------------------+
1 row in set (0,002 sec)

> exit
Bye

На виртуальной машине 192.168.110.62, где установлен сервер — может подключаться пользователь wp_blog_user@localhost. На виртуальной машине 192.168.110.45, где установлен клиент — может подключаться пользователь wp_blog_user@192.168.110.45. Оба пользователя имеют все права на базу данных wp_blog_data, никаких других прав у них нет.

Дополнительно

• Документация. Файлы конфигурации
• Документация. Опции конфигурации
• MariaDB/MySQL Performance Tuning

Поиск: MariaDB • MySQL • База данных • Конфигурация • Настройка • Сервер

• Web-разработка
  • HTML и CSS
  • JavaScript и TypeScript
  • PHP и MySQL
  • CMS Битрикс
  • CMS WoprdPress
  • Yii2 и Laravel
  • Python и Flask
  • Web-аналитика
  • Разное
• ОС Linux
  • Команды
  • Сценарии
  • Разное
• 1С:Предприятие
  • Программирование
  • Язык запросов
  • Разное
• Локальная сеть
  • Разное

• 1С:Предприятие (31)
• API (29)
• Bash (43)
• CLI (126)
• CMS (139)
• CSS (50)
• Frontend (75)
• HTML (66)
• JavaScript (150)
• Laravel (72)
• Linux (177)
• MySQL (77)
• PHP (126)
• React.js (66)
• SSH (27)
• Ubuntu (71)
• Web-разработка (509)
• WordPress (73)
• Yii2 (69)
• БазаДанных (97)
• Битрикс (66)
• Блог (29)
• Верстка (43)
• ИнтернетМагаз… (84)
• КаталогТоваров (87)
• Класс (30)
• Клиент (31)
• Ключ (31)
• Команда (90)
• Компонент (60)
• Конфигурация (76)
• Корзина (32)
• ЛокальнаяСеть (33)
• Модуль (34)
• Навигация (31)
• Настройка (153)
• ПанельУправле… (29)
• Плагин (33)
• Пользователь (27)
• Практика (101)
• Сервер (86)
• Событие (28)
• Теория (106)
• Установка (77)
• Файл (51)
• Форма (58)
• Фреймворк (192)
• Функция (36)
• ШаблонСайта (68)

• Web-разработка
• 1С:Предприятие
• ОС Linux
• Регулярные выражения
• Разное