Mysql как настроить доступ

Доступ к mysql из-вне

Для доступа к MySQL из-вне должно быть выполнено 2 условия:

1. MySQL должен слушать все адреса, а не только 127.0.0.1
2. У пользователя должны быть пермишены для коннекта не только с localhost (с самого сервера где установлена БД), а и других адресов.

Слушать все адреса

Во-первых, изначально mysql слушает только localhost. Нужно исправить это в конфигурационном файле my.cnf (обычно в линуксах путь /etc/mysql/my.cnf). В my.cnf находим строку:

И заменяем её на:

Теперь после service mysql restart мы можем коннектиться к mysql с любого ip (если создан пользователь). При этом для супер-пользователя root доступ из-вне под пустым паролем остаётся закрыт.

bind-address 127.0.0.1 означает, что mysql слушает соединения только с текущего хоста.

Создание пользователя для коннекта к базе данных из-вне

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

В команде CREATE USER мы создали пользователя, который может коннектиться к mysql со всех ip. Мы можем разрешать коннектиться только с определённых ip, например:

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

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

Узнать порт mysql:

После изменения bind-address в MySQL и создания пользователя с пермишенами (либо грант пермишенов текущим пользователям), к базе данных можно подключиться передавая параметр -h . Хост — адрес машины, на которой установлен MySQL:

Источник

Как разрешить удаленные подключения к серверу базы данных MySQL

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

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

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

Настройка сервера MySQL

Первый шаг — настроить сервер MySQL на прослушивание определенного IP-адреса или всех IP-адресов на машине.

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

Для этого вам необходимо отредактировать файл конфигурации MySQL и добавить или изменить значение параметра bind-address . Вы можете установить один IP-адрес и диапазоны IP-адресов. Если адрес 0.0.0.0 , сервер MySQL принимает соединения на всех интерфейсах хоста IPv4. Если в вашей системе настроен IPv6, то вместо 0.0.0.0 используйте :: .

Расположение файла конфигурации MySQL отличается в зависимости от дистрибутива. В Ubuntu и Debian файл находится по адресу /etc/mysql/mysql.conf.d/mysqld.cnf , а в дистрибутивах на основе Red Hat, таких как CentOS, файл находится по адресу /etc/my.cnf .

Найдите строку, которая начинается с bind-address и установите ее значение равным IP-адресу, который сервер MySQL должен прослушивать.

По умолчанию установлено значение 127.0.0.1 (прослушивается только на localhost).

В этом примере мы настроим сервер MySQL для прослушивания всех интерфейсов IPv4, изменив значение на 0.0.0.0

Если есть строка, содержащая skip-networking , удалите ее или закомментируйте, добавив # в начале строки.

В MySQL 8.0 и выше директива bind-address может отсутствовать. В этом случае добавьте его в раздел [mysqld] .

После этого перезапустите службу MySQL, чтобы изменения вступили в силу. Только root или пользователи с привилегиями sudo могут перезапускать службы.

Чтобы перезапустить службу MySQL в Debian или Ubuntu, введите:

В дистрибутивах на основе RedHat, таких как CentOS, для перезапуска службы выполните:

Предоставление доступа пользователю с удаленного компьютера

Следующим шагом будет разрешение доступа к базе данных удаленному пользователю.

Войдите на сервер MySQL как пользователь root, набрав:

Если вы используете старый собственный плагин аутентификации MySQL для входа в систему как root, выполните приведенную ниже команду и введите пароль при появлении запроса:

Изнутри оболочки MySQL используйте оператор GRANT чтобы предоставить доступ удаленному пользователю.

• database_name — это имя базы данных, к которой будет подключаться пользователь.
• user_name — это имя пользователя MySQL.
• ip_address — это IP-адрес, с которого пользователь будет подключаться. Используйте % чтобы разрешить пользователю подключаться с любого IP-адреса.
• user_password — пароль пользователя.

Например, чтобы предоставить доступ к базе данных dbname пользователю с именем foo с паролем my_passwd с клиентского компьютера с IP 10.8.0.5 , вы должны запустить:

Настройка межсетевого экрана

Последний шаг — настроить брандмауэр, чтобы разрешить трафик на порт 3306 (порт по умолчанию MySQL) с удаленных машин.

Iptables

Если вы используете iptables в качестве брандмауэра, приведенная ниже команда разрешит доступ с любого IP-адреса в Интернете к порту MySQL. Это очень небезопасно.

Разрешить доступ с определенного IP-адреса:

UFW — это брандмауэр по умолчанию в Ubuntu. Чтобы разрешить доступ с любого IP-адреса в Интернете (очень небезопасно), запустите:

Разрешить доступ с определенного IP-адреса:

БрандмауэрD

FirewallD — это инструмент управления брандмауэром по умолчанию в CentOS. Чтобы разрешить доступ с любого IP-адреса в Интернете (очень небезопасно), введите:

Чтобы разрешить доступ с определенного IP-адреса через определенный порт, вы можете создать новую зону FirewallD или использовать расширенное правило. Итак, создайте новую зону с именем mysqlzone :

Проверка изменений

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

Где user_name — это имя пользователя, mysql_server_ip вы предоставили доступ, а mysql_server_ip — это IP-адрес хоста, на котором работает сервер MySQL.

Если все настроено правильно, вы сможете войти на удаленный сервер MySQL.

Если вы получаете сообщение об ошибке, подобное приведенному ниже, то либо порт 3306 не открыт , либо сервер MySQL не прослушивает IP-адрес .

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

Выводы

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

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

1. Настройте сервер MySQL для прослушивания всего или определенного интерфейса.
2. Разрешите доступ удаленному пользователю.
3. Откройте порт MySQL в вашем брандмауэре.

Если у вас есть вопросы, не стесняйтесь оставлять комментарии ниже.

Источник

Создание пользователей MySQL/MariaDB и предоставление прав доступа

Учетные записи в СУБД MySQL/MariaDB представлены в виде связки @ . Это может вызвать путаницу, поэтому необходимо быть внимательнее, например, учетные записи root@localhost и root@192.168.0.15 — разные.

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

Рассмотрим два варианта создания учетных записей в СУБД MySQL/MariaDB.

1. С помощью команды GRANT.

Данный метод позволяет одной командой сразу и создать пользователя, и дать ему права. Но, начиная с MySQL 8, она возвращает ошибку — разработчики запретили ее использование и сначала требуется создать пользователя (с помощью CREATE USER).

> GRANT ON TO [IDENTIFIED BY ] ;

Например, эта команда предоставляет права доступа пользователю и, если его не существует, создает его:

> GRANT ALL PRIVILEGES ON *.* TO ‘dbuser’@’localhost’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;

ALL PRIVILEGES: предоставляет полные права на использование данных.
*.* : права предоставляются на все базы и все таблицы.
dbuser: имя учетной записи.
localhost: доступ для учетной записи будет предоставлен только с локального компьютера.
password: пароль, который будет задан пользователю.
WITH GRANT OPTION: будут предоставлены дополнительные права на изменение структуры баз и таблиц.

2. С помощью команды CREATE USER.

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

Пример создания учетной записи:

> CREATE USER ‘dbuser’@’localhost’ IDENTIFIED BY ‘password’;

После можно задать права командой:

> GRANT ALL PRIVILEGES ON *.* TO ‘dbuser’@’localhost’;

Посмотреть существующих пользователей и их привилегии

> SELECT user, host FROM mysql.user;

Список привилегий (для кажого пользователя смотряться отдельно):

> SHOW GRANTS FOR ‘root’@’localhost’;

* где ‘root’@’localhost’ — учетная запись, для которой смотрим привилегии; если упустить FOR, команда выдаст результат для пользователя, под которым выполнено подключение к СУБД.

Смена пароля

Команды отличаются в зависимости от версии СУБД.

MySQL

Версия 5.7.6 и более современная:

> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘New_Password’;

Версия 5.7.5 и древнее:

> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘New_Password’);

* посмотреть версию СУБД можно командой mysql -V.

MariaDB

В MariaDB команда для смены пароля одна, независимо от версии:

> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘New_Password’);

* в данном примере будет задан пароль New_Password для пользователя root.

Смена пароля пользователю root после установки

Для старых версий СУБД и новых процедуры различаются.

MySQL mysqladmin -u root password

Для смены пароля root необходимо выполнить команду в оболочке mysql по инструкции, описанной выше.

MySQL 8 и выше

Пароль создается автоматически при установке системы. Его можно посмотреть командой:

grep ‘password’ /var/log/mysqld.log

Мы увидим что-то на подобие:

2021-08-16T20:14:13.173577Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: oi25?wznro%W

* где oi25?wznro%W — пароль для пользователя root.

Заходим в оболочку sql с помощью данного пароля:

Сброс пароля для root

При необходимости, мы можем сбросить пароль для суперпользователя mysql. Для этого необходимо запустить сервер баз данных в безопасном режиме и ввести команду на смену пароля.

Для начала необходимо остановить сервис:

systemctl stop mysql

systemctl stop mariadb

Создаем каталог /var/run/mysqld и зададим для него нужные права:

mkdir -p /var/run/mysqld

chown mysql:mysql /var/run/mysqld

* в некоторых системах данного каталога может не быть, однако, для безопасного режима mysql он необходим.

Запускаем в безопасном режиме СУБД:

Запускаем оболочку sql — система не потребует пароль:

Выполняем запрос FLUSH PRIVILEGES:

Выходим из оболочки:

Уничтожаем процесс, который запустили ранее:

После, запускаем mysql в нормальном режиме:

systemctl start mysql

systemctl start mariadb

Другие примеры

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

1. Особые права

Предоставление особых прав пользователю:

> GRANT SELECT, UPDATE ON base1.* TO ‘dbuser’@’localhost’ IDENTIFIED BY ‘password’;

* права на выборку и обновление данных во всех таблицах базы base1 для пользователя dbuser
** список всех возможных прав: all privileges, alter, create, create temporary tables, delete, drop, execute, file, index, insert, lock tables, process, references, reload, replication client, replication slave, select, show databases, shutdown, super, update, usage

2. Удаленное подключение

Разрешение на удаленное подключение и использование базы MySQL:

> GRANT ALL PRIVILEGES ON *.* TO ‘dbuser’@’192.168.0.55’ IDENTIFIED BY ‘password’;

* предоставит права пользователю dbuser, который будет подключаться с компьютера с IP-адресом 192.168.0.55.

3. Права на резервное копирование

Создание учетной записи MySQL с правами создания резервных копий:

> GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES ON *.* TO ‘backup’@’localhost’ IDENTIFIED BY ‘backup’;

4. Права доступа к определенной таблице

По сути, это такое же предоставление прав, но с указанием конкретной таблицы после базы:

> GRANT ALL PRIVILEGES ON base1.table1 TO ‘dbuser’@’localhost’ IDENTIFIED BY ‘password’;

* в данном примере предоставлены все права на таблицу table1 в базе base1.

5. Удаление пользователя

Нам может также понадобиться удалить ранее созданного пользователя. Это делается в два этапа:

> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘dbuser’@’localhost’;

> DROP USER ‘dbuser’@’localhost’;

* в данном примере мы первой командой отняли все права у пользователя dbuser (localhost) и второй — удалили его.

6. Права на создание других пользователей

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

GRANT CREATE USER ON *.* TO ‘creator’@’localhost’;

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

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

GRANT CREATE USER ON *.* TO ‘creator’@’localhost’ WITH GRANT OPTION;

* обратите внимание, что мы просто добавили WITH GRANT OPTION.

Возможные ошибки

1. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Причина: в новых версиях по умолчанию активированы политики на проверку сложности пароля. Их список можно посмотреть командой:

> SHOW VARIABLES LIKE ‘validate_password%’;

Вывод команды будет, примерно, следующим:

+—————————————+———+
| Variable_name | Value |
+—————————————+———+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+—————————————+———+

• validate_password_check_user_name — пароль не должен совпадать с именем пользователя.
• validate_password_dictionary_file — использовать специальный файл со словарем запрещенных паролей.
• validate_password_length — минимальная длина пароля.
• validate_password_mixed_case_count — сколько, как минимум, должно быть символов в малой и большой раскладках.
• validate_password_number_count — какое минимальное количество цифр использовать в пароле.
• validate_password_policy — позволяет задать определенный набор правил. Доступны значения LOW (или 0), MEDIUM (1), STRONG (2).
• validate_password_special_char_count — минимальное количество специальных символов (например, # или !).

1. Привести пароль в соответствие требованиям политик.
2. Отключить политику, которая не позволяет использовать желаемый пароль. Например, чтобы отключить требование использовать цифры вводим:

> SET GLOBAL validate_password_number_count = 0;

2. ERROR 1728 (HY000): Cannot load from mysql.tables_priv. The table is probably corrupted

Причина: система считает, что таблица tables_priv в базе mysql неисправна.

Решение: чаще всего, ошибка возникает при переносе баз с одного сервера в другой. Если версии СУБД разные, таблица tables_priv может работать со сбоями. Для исправления необходимо выполнить команду mysql_upgrade — она проверяет все таблицы на совместимость с текущей версией MySQL/MariaDB и вносит исправления. Применение команды:

Источник