Удаленный доступ к MySQL через Workbench

26 апреля 2021, 10:10

Что есть:

Установил на VirtualBox Ubuntu 16.
В настройках виртуальной машины (далее Ubuntu) стоит NAT и Сетевой мост, т.е. видит Интернет по NAT и является частью физической локальной сети.
Гостевой системе установлен статический адрес (192.168.1.99), который пингуется на всех других машинах локальной сети.
Установлено Apache + PHP + MySQL + PhpMyAdmin + Nginx
На любой машине локальной сети можно ввести в браузер 192.168.1.99/phpmyadmin и успешно пройти авторизацию в PhpMyAdmin

Но я привык и мне более удобен MySQL Workbench. Я пытаюсь добавить новое соединение: - в hostname: 192.168.1.99 - port: 3306 - uresname: root - password: соответствующий и нажимаю Тест, то мне пишет, что не может соединится к серверу MySQL по адресу

192.168.1.99:3306

Если в консоли Ubuntu ввести команду netstat -tln, то я понимаю, что

0.0.0.0:80 - это слушают все адреса и порт 80 для показа моего сайта, а
127.0.0.1:3306 - это скорее всего слушает MySQL.

Т.е. я понимаю, что MySQL случает порт 3306 только на localhost.

И теперь вопрос: Как сделать, что-бы он кроме localhost слушал и другие адреса? Другими словами, как подключиться к серверу MySQL, установленного на VirtualBox, удаленно средствами MySQL Workbench?

Answer 1

Что бы настроить удаленный доступ с других IP:

sudo nano /etc/mysql/my.cnf

Ищем секцию [mysqld] и изменяем значение bind-address на 0.0.0.0

У меня файл my.cnf был почти пустой. Там было много текста в виде коментов, то я просто добавил:

[mysqld]
bind-address=0.0.0.0

Перезагружаем сервер:

sudo service mysql restart

Проверяем, что теперь слушает демон MySQL:

  netstat -tln

должно быть 0.0.0.0:3306

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

host ‘192.168.1.83’ is not allowed to connect to this mysql server

где 192.168.1.83 - это IP-адрес моего ПК.

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

Нужно создать пользователя и дать ему нужный доступ. Детальней расмотрим SQL-запрос на создание пользователей:

GRANT [тип прав] ON [имя базы данных].[имя таблицы] TO 'имя пользователя'@'его ip-адрес';

Что-бы создать суперпользователя, типа root, только со своим именем, USER_NAME, с полными правами для всех баз данных с доступом с любого IP и с паролем YOUR_PASSWORD:

GRANT ALL PRIVILEGES ON *.* TO 'USER_NAME'@'%' IDENTIFIED BY 'YOUR_PASSWORD' WITH GRANT OPTION;

вот еще примеры:

создать пользователя для конкретной БД:

GRANT ALL PRIVILEGES ON `my_db`.* TO USER_NAME@'%' IDENTIFIED BY 'YOUR_PASSWORD';

создать пользователя и ограничить доступ только для определенного IP:

GRANT ALL PRIVILEGES ON `my_db`.* TO USER_NAME@'192.168.1.83' IDENTIFIED BY 'YOUR_PASSWORD';

или вовсе только только для работы с сервера, например только через PhpMyAdmin:

GRANT ALL PRIVILEGES ON *.* TO USER_NAME@'localhost' IDENTIFIED BY 'YOUR_PASSWORD';

Если определились с выбором SQL-запроса, тогда его можно запустить в том же phpMyAdmin или в консоли:

Заходим в MySQL:

mysql -u root mysql -p

Пишем нужный SQL-запрос, например:

GRANT ALL PRIVILEGES ON *.* TO 'megaadmin'@'%' IDENTIFIED BY 'superparol' WITH GRANT OPTION;

Для того, чтоб изменения вошли в силу запустите запрос на обновления: FLUSH PRIVILEGES;

выходим с MySQL: EXIT;

Так же это можно сделать в графическом интерфейсе PhpMyAdmin без ввода SQL-запроса. После авторизации под рутом в PhpMyAdmin, заходим во вкладку "Учетные записи пользователей". Там все интуитивно понятно. Ниже опустив экран можно выбрать нужные привилегии на SQL-запросы (Данные), на создание баз данных и таблиц (Структура) и на управлении сервером (Администрирование), а также можно установить ограничение на использование ресурсов.