Сервер MySQL

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

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

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

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

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

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

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

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

  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/datavg/datalv  /data  xfs  defaults  1  2

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

  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) в соответствии с фактическими требованиями.

      Отредактируйте файл 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=n#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=n#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

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

      su - mysql
service mysql start

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

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

    Если MySQL установлен с помощью пакета RPM, полученного с официального сайта, файл mysqld находится в каталоге /usr/sbin. Убедитесь, что в команде указан правильный каталог.

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

    При успешном входе в терминал выводится сообщение следующего вида:

    Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.17

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

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

      ALTER USER 'root'@'localhost' IDENTIFIED BY "123456";

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

      CREATE USER 'root'@'%' IDENTIFIED BY '123456';

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

      GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
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:имя пользователя.

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

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

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

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

    • -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