Настройка сервера базы данных

Сервер PostgreSQL#

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

На рисунке 1 показана архитектура PostgreSQL, а в таблице 1 описаны основные процессы.

Рисунок 1. Архитектура PostgreSQL

Таблица 1. Основные процессы в PostgreSQL

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

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

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

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

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

    # systemctl stop firewalld

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

    # systemctl disable firewalld

    ПРИМЕЧАНИЕ.
    Автоматический запуск будет автоматически отключен, поскольку отключен брандмауэр.

Отключение SELinux#

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

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

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

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

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

    # groupadd  postgres

    # useradd  -g postgres postgres

  2. Задайте пароль для пользователя postgres от имени пользователя root. (Введите пароль дважды для подтверждения.)

    #passwd postgres

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

ПРИМЕЧАНИЕ.
— При тестировании максимальной производительности рекомендуется установить твердотельные накопители NVMe с более высокой производительностью ввода-вывода для создания тестовых экземпляров PostgreSQL, чтобы избежать влияния скорости ввода-вывода диска на результаты теста производительности. В этом разделе в качестве примера используются твердотельные накопители NVMe. Подробнее см. в шагах 1–4.
— В тесте, отличном от теста производительности, выполните следующую команду от имени пользователя root, чтобы создать каталог данных. Затем пропустите этот раздел.
# mkdir /data

  1. Создайте файловую систему (в качестве примера используется xfs от имени пользователя root; создайте файловую систему в соответствии со своими требованиями). Если для диска была создана файловая система, при выполнении этой команды отобразится сообщение об ошибке. Можно использовать параметр -f для принудительного создания файловой системы.

    # mkfs.xfs /dev/nvme0n1

  2. Создайте каталог данных.

    # mkdir /data

  3. Подключите диски.

    # mount -o noatime,nobarrier /dev/nvme0n1 /data

Авторизация каталога данных#

  1. Измените разрешение каталога от имени пользователя root.

    # chown -R postgres:postgres /data/

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

Установка PostgreSQL#

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

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

    $ dnf clean all

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

    $ dnf makecache

  4. Установите сервер PostgreSQL от имени пользователя root.

    #dnf install postgresql-server

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

    $ rpm -qa | grep postgresql

Запуск PostgreSQL#

Инициализация базы данных#

ПРИМЕЧАНИЕ.
Выполните этот шаг от имени пользователя postgres.

  1. Переключитесь на созданного пользователя PostgreSQL.

    # su - postgres

  2. Инициализируйте базу данных. Параметр /usr/bin в команде — это каталог, в котором находится команда initdb.

    $ /usr/bin/initdb -D /data/
Запуск базы данных#

  1. Включите базу данных PostgreSQL.

    $ /usr/bin/pg_ctl -D /data/ -l /data/logfile start

  2. Проверьте, правильно ли запущен процесс базы данных PostgreSQL.

    $ ps -ef | grep postgres

    Если отображается следующая информация, процессы PostgreSQL запущены.

Вход в базу данных#

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

    $ /usr/bin/psql -U postgres

    ПРИМЕЧАНИЕ.
    При первом входе в базу данных пароль вводить не нужно.

Настройка учетных записей и паролей базы данных#

  1. После входа в систему задайте пароль пользователя postgres.

    postgres=#alter user postgres with password '123456';

Выход из базы данных#

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

    postgres=# \q
Остановка работы базы данных#

  1. Остановите работу базы данных PostgreSQL.

    $/usr/bin/pg_ctl -D /data/ -l /data/logfile stop

Удаление PostgreSQL#

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

    $ /usr/bin/pg_ctl -D /data/ -l /data/logfile stop

  2. Выполните команду dnf remove postgresql-server от имени пользователя root, чтобы удалить базу данных PostgreSQL.

    # dnf remove postgresql-server

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

Создание роли#

Для создания роли можно использовать инструкцию CREATE ROLE или команду createuser. Команда createuser инкапсулирует инструкцию CREATE ROLE и должна выполняться в графическом интерфейсе оболочки, а не в графическом интерфейсе базы данных.

CREATE ROLE rolename [ [ WITH ] option [ ... ] ];

createuser rolename

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

  • rolename: имя роли.
  • Параметры для option:
    • SUPERUSER | NOSUPERUSER: определяет, назначаются ли новой роли права суперпользователя. Если этот параметр не задан, используется значение по умолчанию NOSUPERUSER, указывающее, что роли не предоставляются права суперпользователя.
    • CREATEDB | NOCREATEDB: указывает, может ли роль создавать базу данных. Если этот параметр не задан, используется значение по умолчанию NOCREATEDB, указывающее, что роль не может создавать базу данных.
    • CREATEROLE | NOCREATEROLE: определяет, может ли роль создавать роли. Если этот параметр не задан, используется значение по умолчанию NOCREATEROLE, указывающее, что роль не может создавать роли.
    • INHERIT | NOINHERIT: определяет, наследует ли роль разрешения других ролей в группе, в которую эта роль входит. Роль с атрибутом INHERIT может автоматически использовать любые разрешения, назначенные ее прямой или косвенной группе. Если этот параметр не указан, используется значение по умолчанию INHERIT.
    • LOGIN | NOLOGIN: определяет, может ли роль войти в систему. Роль с атрибутом LOGIN можно рассматривать как пользователя. Роль без этого атрибута может использоваться для управления разрешениями базы данных, но не является пользователем. Если этот атрибут не указан, используется значение по умолчанию NOLOGIN. Однако если для создания роли вместо CREATE ROLE используется инструкция CREATE USER, атрибут LOGIN используется по умолчанию.
    • [ENCRYPTED | UNENCRYPTED] PASSWORD’password’: пароль роли. Пароль действителен только для ролей с атрибутом LOGIN. ENCRYPTED | UNENCRYPTED: определяет, следует ли шифровать пароль. Если этот параметр не указан, используется значение ENCRYPTED, то есть пароль зашифрован.
    • VALID UNTIL’timestamp’: указывает метку времени окончания срока действия пароля роли. Если этот параметр не указан, пароль бессрочный.
    • IN ROLE rolename1: перечисляет одну или несколько существующих ролей. Будет добавлена новая роль rolename, которая станет членом rolename1.
    • ROLE rolename2: перечисляет одну или несколько существующих ролей. Эти роли будут автоматически добавлены в качестве членов новой роли rolename. То есть новая роль — это группа пользователей.

Для выполнения этой команды требуется разрешение CREATEROLE или права суперпользователя базы данных.

Пример#

#Создание роли roleexample1, которая может входить в систему.

postgres=# CREATE ROLE roleexample1 LOGIN;

#Создание роли roleexample2 с паролем 123456.

postgres=# CREATE ROLE roleexample2 WITH LOGIN PASSWORD '123456';

#Создание роли с именем roleexample3.

[postgres@localhost ~]$ createuser roleexample3

Просмотр ролей#

Чтобы просмотреть роль, можно выполнить инструкцию SELECT или метакоманду PostgreSQL \du.

SELECT rolename FROM pg_roles;

\du

В предыдущей команде параметр rolename — это имя роли.

Пример#

#Просмотр роли roleexample1.

postgres=# SELECT roleexample1 from pg_roles;

#Просмотр существующих ролей.

postgres=# \du

Изменение роли#

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

Чтобы переименовать существующую роль, используйте инструкцию ALTER ROLE.

ALTER ROLE oldrolername RENAME TO newrolename;

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

  • oldrolername — исходное имя роли.
  • newrolename — новое имя роли.
Пример изменения пользователя#

#Переименование роли roleexample1 на roleexapme2.

postgres=# ALTER ROLE roleexample1 RENAME TO roleexample2;
Изменение пароля пользователя#

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

ALTER ROLE rolename PASSWORD 'password'

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

  • rolename: имя роли.
  • password: пароль.
Пример изменения пароля роли#

#Изменение пароля роли roleexample1 на 456789.

postgres=# ALTER ROLE roleexample1 WITH PASSWORD '456789';

Удаление роли#

Для удаления роли можно использовать инструкцию DROP ROLE или команду dropuser. Команда dropuser инкапсулирует инструкцию DROP ROLE и должна выполняться в графическом интерфейсе оболочки, а не в графическом интерфейсе базы данных.

DROP ROLE rolename;

dropuser rolename

В предыдущей команде параметр rolename — это имя роли.

Пример#

#Удаление роли userexample1.

postgres=# DROP ROLE userexample1;

#Удаление роли userexample2.

[postgres@localhost ~]$ dropuser userexample2

Разрешения роли#

Для предоставления разрешений роли можно использовать инструкцию GRANT.

Предоставление роли разрешения на выполнение операций с таблицей.

GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

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

GRANT { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } ON SEQUENCE sequencename [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

Предоставление роли разрешения на выполнение операций с базой данных.

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE databasename [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

Предоставление роли разрешения на выполнение операций с функцией.

GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

Предоставление роли разрешения на выполнение операций процедурного языка.

GRANT { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE langname [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

Предоставление роли разрешения на выполнение операций со схемой.

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

Предоставление роли разрешения на выполнение операций с табличным пространством.

GRANT { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespacename [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

Назначение отношений членства в роли rolename1 для роли rolename2.

GRANT rolename1 [, ...] TO rolename2 [, ...] [ WITH ADMIN OPTION ]

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

  • SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER, USAGE, CREATE, CONNECT, TEMPORARY, TEMP, EXECUTE и ALL [ PRIVILEGES ]: указывают разрешения пользователя на выполнение операций. ALL [PRIVILEGES]: указывает все разрешения, ключевое слово PRIVILEGES является необязательным в PostgreSQL, но требуется в строгих инструкциях SQL.
  • Предложение ON: указывает объект, в отношении которого предоставляется разрешение.
  • tablename: имя таблицы.
  • Предложение TO: указывает роль, которой предоставляется разрешение.
  • rolename, rolename1 и rolename2: имена ролей.
  • groupname: имя группы ролей.
  • PUBLIC: указывает, что разрешение предоставляется всем ролям, включая пользователей, которые могут быть созданы позже.
  • WITH GRANT OPTION: указывает, что получатель разрешения может предоставлять разрешение другим. Этот параметр невозможно назначить для PUBLIC.
  • sequencename: имя последовательности.
  • databasename: имя базы данных.
  • funcname ([[argmode] [argname] argtype [, …]]): имя функции и ее параметры.
  • langname: название процедурного языка.
  • schemaname: имя схемы.
  • tablespacename: имя табличного пространства.
  • WITH ADMIN OPTION: участник может назначать отношения членства в роли другим ролям и отменять отношения членства в других ролях.
Пример#

#Предоставление разрешения CREATE для базы данных database1 пользователю userexample.

postgres=# GRANT CREATE ON DATABASE database1 TO userexample;

#Предоставление всех разрешений для работы с таблицей table1 всем пользователям.

postgres=# GRANT ALL PRIVILEGES ON TABLE table1 TO PUBLIC;

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

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

Отзыв у роли разрешения на выполнение операций с таблицей.

REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...]

Отзыв у роли разрешения на выполнение операций с последовательностью.

REVOKE [ GRANT OPTION FOR ] { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } ON SEQUENCE sequencename [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]

Отзыв у роли разрешения на выполнение операций с базой данных.

REVOKE [ GRANT OPTION FOR ] { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE databasename [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]

Отзыв у роли разрешения на выполнение операций с функцией.

REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]

Отзыв у роли разрешения на выполнение операций процедурного языка.

REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE langname [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]

Отзыв у роли разрешения на выполнение операций со схемой.

REVOKE [ GRANT OPTION FOR ] { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]

Отзыв у роли разрешения на выполнение операций с табличным пространством.

REVOKE [ GRANT OPTION FOR ] { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespacename [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]

Отзыв у роли rolename2 отношений членства в роли rolename1.

REVOKE [ ADMIN OPTION FOR ] rolename1 [, ...] FROM rolename2 [, ...] [ CASCADE | RESTRICT ]

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

  • GRANT OPTION FOR: разрешение невозможно предоставить другим, однако само разрешение не отзывается.
  • SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER, USAGE, CREATE, CONNECT, TEMPORARY, TEMP, EXECUTE и ALL [ PRIVILEGES ]: указывают разрешения пользователя на выполнение операций. ALL [PRIVILEGES]: указывает все разрешения, ключевое слово PRIVILEGES является необязательным в PostgreSQL, но требуется в строгих инструкциях SQL.
  • Предложение ON: указывает объект, в отношении которого отзывается разрешение.
  • tablename: имя таблицы.
  • Предложение FROM: указывает роль, разрешение которой отзывается.
  • rolename, rolename1 и rolename2: имена ролей.
  • groupname: имя группы ролей.
  • PUBLIC: отзывает неявно определенные группы со всеми ролями. Однако это не означает, что все роли теряют разрешения. Разрешения, полученные напрямую, и разрешения, полученные через группу, остаются в силе.
  • sequencename: имя последовательности.
  • CASCADE: отзыв всех зависимых разрешений.
  • RESTRICT: все зависимые разрешения не отзываются.
  • databasename: имя базы данных.
  • funcname ([[argmode] [argname] argtype [, …]]): имя функции и ее параметры.
  • langname: название процедурного языка.
  • schemaname: имя схемы.
  • tablespacename: имя табличного пространства.
  • ADMIN OPTION FOR: переданная авторизация не отзывается автоматически.
Пример#

#Предоставление разрешения CREATE для базы данных database1 пользователю userexample.

postgres=# GRANT CREATE ON DATABASE database1 TO userexample;

#Предоставление всех разрешений для работы с таблицей table1 всем пользователям.

postgres=# GRANT ALL PRIVILEGES ON TABLE table1 TO PUBLIC;

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

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

Для создания базы данных можно использовать инструкцию CREATE DATABASE или команду createdb. Команда createdb инкапсулирует инструкцию CREATE DATABASE и должна выполняться в графическом интерфейсе оболочки, а не в графическом интерфейсе базы данных.

CREATE DATABASE databasename;

createdb databasename

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

Для использования этой команды требуется разрешение CREATEDB.

Пример#

#Создание базы данных с именем database1.

postgres=# CREATE DATABASE database1;

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

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

\c databasename;

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

Пример#

#Выбор базы данных databaseexample.

postgres=# \c databaseexample;

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

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

\l;
Пример#

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

postgres=# \l;

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

Чтобы удалить базу данных, можно выполнить инструкцию DROP DATABASE или команду dropdb. Команда dropdb инкапсулирует инструкцию DROP DATABASE и должна выполняться в графическом интерфейсе оболочки, а не в графическом интерфейсе базы данных.

ВНИМАНИЕ!
Будьте осторожны при удалении базы данных. После удаления базы данных все таблицы и данные в базе данных будут утеряны.

DROP DATABASE databasename;

dropdb databasename

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

Инструкция DROP DATABASE удаляет элементы системного каталога базы данных и файловые каталоги, содержащие данные.

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

Пример#

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

postgres=# DROP DATABASE databaseexample;

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

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

pg_dump [option]... [databasename] > outfile

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

  • databasename: имя базы данных. Если этот параметр не указан, используется переменная среды PGDATABASE. Если эта переменная среды не задана, используйте имя пользователя, которое инициирует подключение.
  • outfile: файл резервной копии базы данных.
  • option: параметр команды pg_dump. Можно ввести несколько параметров через пробел. Общие параметры команды pg_dump:
    • -f, –file= filename: указанный выходной файл. Если этот параметр пропущен, используется стандартный вывод.
    • -d, –dbname= databasename: база данных для записи дампа.
    • -h, –host= hostname: имя хоста.
    • -p, –port= portnumber: номер порта.
    • -U, –username= username: имя пользователя подключения.
    • -W, –password: принудительный запрос PostgreSQL пароля перед подключением к базе данных.
Пример#

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

[postgres@localhost ~]$ pg_dump -h 192.168.202.144 -p 3306 -U postgres -W database1 > db1.sql

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

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

psql [option]... [databasename [username]] < infile

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

  • databasename: имя базы данных. Если этот параметр не указан, используется переменная среды PGDATABASE. Если эта переменная среды не задана, используйте имя пользователя, которое инициирует подключение.
  • username: имя пользователя.
  • infile: параметр outfile в команде pg_dump.
  • option: параметр команды psql. Можно ввести несколько параметров через пробел. Общие параметры команды psql:
    • -f, –file= filename: указанный выходной файл. Если этот параметр пропущен, используется стандартный вывод.
    • -d, –dbname= databasename: база данных для записи дампа.
    • -h, –host= hostname: имя хоста.
    • -p, –port= portnumber: номер порта.
    • -U, –username= username: имя пользователя подключения.
    • -W, –password: принудительный запрос PostgreSQL пароля перед подключением к базе данных.

Команду psql нельзя использовать для автоматического создания базы данных databasename. Поэтому базу данных databasename необходимо создать перед выполнением команды psql для восстановления базы данных.

Пример#

#Импорт файла сценария db1.sql в базу данных newdb пользователя postgres на хосте 192.168.202.144 через порт 3306.

[postgres@localhost ~]$ createdb newdb
[postgres@localhost ~]$ psql -h 192.168.202.144 -p 3306 -U postgres -W -d newdb < db1.sql

Сервер MariaDB#

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

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

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

Рисунок 2. Логическая архитектура MariaDB

Когда 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#

  1. Измените файл конфигурации от имени пользователя 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 и нажмите клавишу ВВОД.

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

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

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

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

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

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

    # mkfs.xfs /dev/sdb1

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

    # mkdir /data

    # mount /dev/sdb1 /data

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

    В последней строке /dev/nvme0n1p1 — это исключительно пример.

Способ 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. Выполните команду vi /etc/fstab и отредактируйте файл /etc/fstab, чтобы разрешить автоматическое подключение диска данных после перезапуска системы. Например, добавьте содержимое в последнюю строку, как показано на рисунке ниже.

    В последней строке /dev/datavg/datalv — это исключительно пример.

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

  1. В созданном каталоге данных /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. Если пароль не задан, нажмите клавишу ВВОД. Затем задайте пароль.

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

    $ mysql -u root -p

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

    ПРИМЕЧАНИЕ.
    Выполните команду \q или exit, чтобы выйти из базы данных.

Удаление MariaDB#

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

    $ ps -ef | grep mysql
# kill -9 PID

  2. Выполните команду dnf remove mariadb-server от имени пользователя root, чтобы удалить MariaDB.

    # 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: имя хоста.

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

Если задать пароль для существующего пользователя в предложении 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= username:имя пользователя.
    • -p, –password[= password]: пароль.
    • -P, –port= portnumber: номер порта.
    • -h, –host= hostname: имя хоста.
    • -r, –result-file= filename: сохраняет результат экспорта в указанный файл, что эквивалентно \>.
    • -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

Сервер MySQL#

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

MySQL — это система управления реляционными базами данных (RDBMS), разработанная шведской компанией MySQL AB, которая была приобретена корпорацией Sun Microsystems (теперь Oracle). Это одна из самых популярных систем управления реляционными базами данных (RDBMS) в отрасли, особенно для веб-приложений.

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

Язык структурированных запросов (SQL), используемый MySQL, является наиболее распространенным стандартным языком для доступа к базам данных. MySQL использует дистрибутив с двойным лицензированием и доступен в двух версиях: версия для сообщества и коммерческая версия. MySQL оптимален для небольших или средних веб-сайтов ввиду своего небольшого размера, высокой скорости, низкой стоимости и, особенно, открытого исходного кода.

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

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

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

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

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

    # systemctl stop firewalld

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

    # systemctl disable firewalld

    ПРИМЕЧАНИЕ.
    Автоматический запуск будет автоматически отключен, поскольку отключен брандмауэр.

Отключение SELinux#

  1. Измените файл конфигурации от имени пользователя 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 и нажмите клавишу ВВОД.

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

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

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

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

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

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

    # mkfs.xfs /dev/sdb1

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

    # mkdir /data

    # mount /dev/sdb1 /data

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

    В последней строке /dev/nvme0n1p1 — это исключительно пример.

Способ 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. Выполните команду vi /etc/fstab и отредактируйте файл /etc/fstab, чтобы разрешить автоматическое подключение диска данных после перезапуска системы. Например, добавьте содержимое в последнюю строку, как показано на рисунке ниже.

    В последней строке /dev/datavg/datalv — это исключительно пример.

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

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

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

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

Установка MySQL#

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

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

    $ dnf clean all

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

    $ dnf makecache

  4. Установите сервер MySQL от имени пользователя root.

    # dnf install mysql-server

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

    $ rpm -qa | grep mysql-server

Запуск MySQL#

  1. Измените файл конфигурации.

    1. Создайте файл my.cnf от имени пользователя root и измените пути к файлам (включая путь установки программного обеспечения basedir и путь к данным datadir) в соответствии с фактическими требованиями.

      # vi /etc/my.cnf

      Отредактируйте файл my.cnf следующим образом.

      [mysqld_safe]
log-error=/data/mysql/log/mysql.log
pid-file=/data/mysql/run/mysqld.pid
[mysqldump]
quick
[mysql]
no-auto-rehash
[client]
default-character-set=utf8
[mysqld]
basedir=/usr/local/mysql
socket=/data/mysql/run/mysql.sock
tmpdir=/data/mysql/tmp
datadir=/data/mysql/data
default_authentication_plugin=mysql_native_password
port=3306
user=mysql

    2. Убедитесь, что файл my.cnf изменен правильно.

      $ cat /etc/my.cnf

      ВНИМАНИЕ!
      В файле конфигурации параметр baseir указывает путь установки программного обеспечения. Измените его в соответствии с фактическими требованиями.

    3. Измените группу и пользователя файла /etc/my.cnf на mysql:mysql от имени пользователя root.

      # chown mysql:mysql /etc/my.cnf

  2. Настройте переменные среды.

    1. Добавьте путь к двоичным файлам MySQL в параметр PATH от имени пользователя root.

      # echo export  PATH=$PATH:/usr/local/mysql/bin  >> /etc/profile

      ВНИМАНИЕ!
      В команде /usr/local/mysql/bin — это абсолютный путь к файлам bin в каталоге установки программного обеспечения MySQL. Измените его в соответствии с фактическими требованиями.

    2. Выполните следующую команду от имени пользователя root, чтобы переменные среды вступили в силу:

      # source /etc/profile

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

    ПРИМЕЧАНИЕ.
    Вторая строка снизу содержит исходный пароль, который будет использоваться при входе в базу данных.

    # mysqld --defaults-file=/etc/my.cnf --initialize
2020-03-18T03:27:13.702385Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.17) initializing of server in progress as process 34014
2020-03-18T03:27:24.112453Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: iNat=)#V2tZu
2020-03-18T03:27:28.576003Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.17) initializing of server has completed

    Если выходные данные команды содержат фразу “initializing of server has completed”, база данных была инициализирована. В выходных данных команды “iNat=)#V2tZu” in “A temporary password is generated for root@localhost: iNat=)#V2tZu” является исходным паролем.

  4. Запустите базу данных.

    ВНИМАНИЕ!
    Запустите MySQL от имени пользователя mysql, если вы запускаете службу базы данных впервые. Если вы запустите MySQL от имени пользователя root, появится сообщение о том, что файл mysql.log отсутствует. Если вы запустите MySQL от имени пользователя mysql, файл mysql.log будет создан в каталоге /data/mysql/log. Если вы снова запустите базу данных от имени пользователя root, сообщения об ошибке отображаться не будут.

    1. Измените разрешения на доступ к файлу от имени пользователя root.

      # chmod 777 /usr/local/mysql/support-files/mysql.server

    2. Запустите MySQL от имени пользователя root.

      # cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
# chkconfig mysql on

      Запустите MySQL от имени пользователя mysql.

      # su - mysql
$ service mysql start

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

    ПРИМЕЧАНИЕ.
    — Введите исходный пароль, сгенерированный во время инициализации базы данных (3 ).
    — Если MySQL установлен с помощью пакета RPM, полученного с официального сайта, файл mysqld находится в каталоге /usr/sbin. Убедитесь, что в команде указан правильный каталог.

    $ /usr/local/mysql/bin/mysql -uroot -p  -S /data/mysql/run/mysql.sock

  6. Настройте учетные записи и пароли базы данных.

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

      mysql>alter user 'root'@'localhost' identified by "123456";

    2. Создайте пользователя root для всех остальных хостов в домене.

      mysql>create user 'root'@'%' identified by '123456';

    3. Предоставьте разрешения пользователю root.

      mysql>grant all privileges on *.* to 'root'@'%';
mysql>flush privileges;

  7. Выйдите из базы данных.

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

    mysql>exit

Удаление MySQL#

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

    $ ps -ef | grep mysql
# kill -9 PID

  2. Выполните команду dnf remove mysql от имени пользователя root, чтобы удалить MySQL.

    # dnf remove mysql

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

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

Выполните инструкцию 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 FROM mysql.user;

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

  • username: имя пользователя.
  • hostname: имя хоста.
Пример#

#Просмотр пользователя userexample1.

> SHOW GRANTS FOR 'userexample1'@'localhost';

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

> SELECT USER,HOST 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' = 'newpassword';

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

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

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

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

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

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

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

> SET PASSWORD FOR 'userexample'@'localhost' = '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: имя хоста.

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

Если задать пароль для существующего пользователя в предложении 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= username:имя пользователя.
    • -p, –password[= password]: пароль.
    • -P, –port= portnumber: номер порта.
    • -h, –host= hostname: имя хоста.
    • -r, –result-file= filename: сохраняет результат экспорта в указанный файл, что эквивалентно \>.
    • -t: резервное копирование только данных.
    • -d: резервное копирование только структуры таблицы.
Пример#

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

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

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

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

#Резервное копирование таблицы tb1 базы данных db1 пользователя root на порту 3306 хоста с IP-адресом 192.168.202.144 в файл 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 на порту 3306 хоста с IP-адресом 192.168.202.144 в файл 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