Доступ к mysql из сети определенным адресам. Удаленный доступ для MySQL-сервера. Проверка соединения с БД MySQL

01.04.2019

Сервер MySQL имеет возможность прослушивать определенные адреса для приема входящих подключений. При установке "по-умолчанию" каждый сервер MySQL "слушает" локальный адрес 127.0.0.1 , каждый сервер MariaDB ожидает подключений из любого источника (слушает 0.0.0.0 ). Вслед за в ISPmanager Lite и Business версии 5.95.0 и выше будет добавлена возможность изменять настройки удаленного доступа у подключенных серверов баз данных.

Внимание! Данная функциональность не реализована для PostgreSQL

Lite

Настройки ->Серверы баз данных . Выделите подключенный сервер и нажмите кнопку Изменить . Для изменения прослушиваемого ip-адреса включите на форме удаленный доступ и выберите адрес из появившегося списка:

Business

Для изменения настроек пройдите Настройки кластера"-> Узлы кластера. Выберите узел кластера с ролью Сервер СУБД MySQL, нажмите кнопку Изменить . На форме вам нужно будет выбрать пункт удаленный доступ и выбрать ip-адресс.

Внимание! Для новых установок на локальном узле кластера удаленный доступ выключен. По-умолчанию для всех новых узлов кластера с ролью Сервер СУБД MySQL удаленный доступ будет включен, и в качестве прослушиваемого адреса будет выбран 0.0.0.0

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

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

Установка APS-скриптов

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

Для сервиса, который работает на другой машине. В общем случае нам необходимо выполнить 2 действия:

а) Разрешить серверу MySQL прослушивание на сетевых интерфейсах (не только на 127.0.0.1)

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

Если с первым пунктом более-менее ясно, то по пункту (б) необходимо пояснить. Дело в том, что обычно пользователи создаются вида "username"@"localhost", что позволяет им цепляться с определенными привилегиями к определенным базам данных, но только с localhost. Из сети эти пользователи подключиться не могут - MySQL сообщает им, что пароль или имя пользователя неверны. Фишка заключается в том, что MySQL содержит список пользователей, разделенный по машинам, с которых они могут цепляться. Так, пользователь User1 может подключаться с localhost с паролем ABCD, с адреса 192.168.0.1 он может быть прописан с паролем BBBCCC, а с адреса 192.168.1.1 - уже с паролем BCDE и только для некоторых баз. Т.е. мы можем задать - с каких компьютеров пользователю разрешено подключаться, какой пароль он будет иметь в таком случае и к каким базам у него будут привилегии. На самом же деле, если взглянуть в базу данных mysql, то мы увидим, что это не один пользователь, а 3 разных юзера: user1@localhost, [email protected] и [email protected]. Т.е. с точки зрения MySQL - это разные пользователи, соответственно, имеющие разные привилегии и пароли. Таким образом, чтобы получить доступ из сети, нам необходимо завести дополнительных пользователей в MySQL с указанием уже не localhost. Причем, Вы можете как указать конкретные IP адреса, с которых разрешено этим пользователям подключаться, так и специальный символ "%", который обозначает "любые компьютеры, кроме localhost". Вот пример имен пользователей:

username@"localhost" : подключаться может только с localhost

username@"192.168.0.1" : подключаться может только с 192.168.0.1

username@"%" : подключаться может с любого компьютера, кроме localhost

username@"192.168.0.%" : подключаться может с любого компьютера, IP-адрес которого начинается с 192.168.0.

Как видно, для localhost пользователя необходимо всегда указывать отдельно. Если же необходимо указание нескольких IP-адресов для одного пользователя... придется заводить эти IP-адреса отдельно, к сожалению, либо через запятую, но после каждой запятой - опять конструкция вида IDENTIFIED BY. Итак, давайте дадим к серверу MySQL доступ из сети. 1) Во-первых, необходимо проверить, что сервер слушает запросы не только на localhost.

Для Debian:

Открываем файл /etc/mysql/my.cnf и ищем в нем строчку bind-address. Нам нужно, чтобы она была раскоментирована и содержала значение 0.0.0.0:

# cat /etc/mysql/my.cnf | grep bind-address

bind-address = 0.0.0.0

Если строчку Вы меняли - необходимо перезагрузить сервер MySQL:

# /etc/init.d/mysql restart

Для FreeBSD:

По-умолчанию во FreeBSD сервер MySQL слушает на всех доступных интерфейсах и ничего дополнительно менять не нужно.

2) Теперь необходимо добавить пользователей, имеющих доступ из сети. Без этого сервер будет слушать сеть, но пускать все равно к себе не будет.

# mysql-uroot–p

Password: // приветствиеMySQL

mysql> GRANT ALL PRIVILEGES ON *.* TO "user1"@"192.168.0.%" IDENTIFIED BY "password1";

mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO "user2"@"%" IDENTIFIED BY "password2";

В этом коде пользователю user1 разрешается полный доступ ко всем базам на сервере, но только с компьютеров из сети 192.168.0.*

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

3) Проверяем сетевой доступ.

$ mysql -u username -p -h 192.168.0.100

Вместо 192.168.0.100 - укажите IP адрес машины с MySQL сервером, а вместо username - имя пользователя. Если сервер не пускает - в первую очередь проверьте - а не стоит ли на пути к нему файерволов и открыт ли на этих файерволах порт MySQL: 3306.

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

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

Для настройки простого подключения к MySQL извне зайдите в Панель управления и перейдите в раздел «Базы данных MySQL».

Нажмите кнопки «Доступ по IP» → «Добавить IP» и укажите IP-адрес, с которым вы выходите в сеть. Узнать свой IP-адрес можно на сайте inet.from.sh .

В форме можно указывать в том числе и IP-адреса с символом % в октетах. К примеру, для разграничения доступа с IP-адресов одной подсети:

192.168.1.%

Нажмите «Добавить».

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

сервер для подключения: имя вашего домена, например yourdomain.ru;
порт для подключения: 3306;
имя пользователя и пароль: установленные вами при создании базы данных имя пользователя и пароль.

Mysql -P 3306 -h yourdomain.ru -u mylogin_user -p mylogin_db

Безопасное подключение через SSH-туннель

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

Подключение к БД MySQL с компьютера под управлением ОС MS Windows

Установите на ваш компьютер программу putty . Это бесплатное программное обеспечение, скачать ее можно на официальном сайте .

Запустите программу.

После этого нажмите кнопку «Add».

После этого нажмите кнопку «Open». Установится соединение с вашим хостом.

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

Туннель установлен. Для соединения с базой данных используйте имя пользователя и пароль, заданные вами при создании базы данных в разделе Панели управления «Базы данных». В качестве адреса сервера используйте 127.0.0.1, порт для подключения 3306.

Пример подключения с помощью программы mysql (пароль будет запрошен при подключении):

Внимание: если на вашем компьютере уже работает сервер баз данных MySQL, подключение удаленному серверу по порту 3306 будет невозможно. В этом случае при создании подключения в putty укажите другой порт в качестве "Source port", например, 3307 . Используйте этот порт при подключении к базе данных.

Подключение к БД MySQL с компьютера под управлением ОС Linux

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

Ssh -L3306:localhost:3306 -n [email protected]

3306 (сразу после ключа -L) - порт на локальном компьютере, к которому вы сможете подключаться клиентом mysql;
yourlogin - имя вашего аккаунта (логин в Панели управления);
yourdomain.ru - имя вашего сайта.

В качестве пароля используйте пароль для входа в Панель управления хостингом.

Пример подключения с помощью программы mysql (пароль будет запрошен при подключении):

Mysql -P 3306 -h 127.0.0.1 -u mylogin_user -p mylogin_db

Внимание: если на вашем компьютере уже работает сервер баз данных MySQL, подключение удаленному серверу по порту 3306 будет невозможно. В этом случае при запуске ssh сразу после ключа -L укажите альтернативный порт, например, 3307. Используйте этот порт при подключении к базе данных.

Проверка соединения с БД MySQL

Проверить соединение с БД можно посредством утилиты telnet . Войдите в интерфейс командной строки (в ОС MS Windows: Пуск → Выполнить → cmd.exe), наберите команду:

Telnet 127.0.0.1 3306

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

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

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

Кстати, в Web`е разносят сервер базы данных и web-сервер только в тех случаях, когда мощности одного сервера не хватает. Арендуется выделенный сервер и выноситься на него база данных. Хотелось бы уточнить, что делать это разумно только в пределах одного ДЦ.

Разрешаем подключаться к MySQL удаленно

В конфигурационном файле самого MySQL есть параметр bind-address , который отвечает за это, поэтому:

открываем конфигурационный файл для редактирования:
sudo nano /etc/mysql/my.cnf
комментируем строку:
# bind-address = 127.0.0.1
перезапускаем MySQL:
sudo service mysql restart

Закомментировать эту строку равносильно тому, что присвоить IP-адрес 0.0.0.0 , то есть разрешить подключаться всем. Указать 2 IP-адреса в этой строчке нельзя. Если нужно ограничить подключение к серверу MySQL, нужно использовать возможности iptables.

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

Mysql -u root -p

И командой создаем пользователя:

GRANT ALL PRIVILEGES ON *.* TO "новый_пользователь"@"удаленный_адрес" IDENTIFIED BY "пароль" WITH GRANT OPTION;

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

GRANT SELECT,INSERT,DELETE,UPDATE,CREATE,ALTER ON "имя_базы".* TO "новый_пользователь"@"удаленный_адрес" IDENTIFIED BY "пароль" WITH GRANT OPTION;

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

Полный список всех прав, которые можно указать пользователю:

ALL PRIVILEGES – все права на указанный объект за исключением назначения прав на этот объект;
CREATE – право на создание таблицы;
ALTER – право на изменение таблицы;
DROP – право на уничтожение таблицы;
LOCK TABLES – право на блокировку таблицы;
CREATE TEMPORARY TABLES – право на создание временных таблиц;
CREATE ROUTINE – право на создание хранимых процедур и функций;
ALTER ROUTINE – право на изменение или уничтожение хранимых процедур и функций;
CREATE VIEW – право на создание представления;
TRIGGER – право на создание и уничтожение триггеров;
INDEX – права на создание и уничтожение индексов;
EXECUTE – право на выполнения хранимых процедур и функций;
EVENT – право на создание событий;
CREATE USER – права на создание, уничтожение, переименование пользователя и снятия все прав. Назначается только на глобальном уровне;
SELECT – право на выборку;
DELETE – право на удаление;
INSERT – право на вставку;
UPDATE – право на обновление;
FILE – право на использование команд SELECT … INTO OUTFILE и LOAD DATA INFILE ;
PROCESS – право на просмотр всех процессов командой SHOW PROCESSLIST ;
SHOW DATABASES – право на просмотр списка схем;
SHOW VIEW – право на просмотр списка представлений;
SHUTDOWN – право на закрытие.

Эта статья для тех, кто впервые столкнулся с необходимостью установить удаленное соединение с базой данных MySQL. В статье рассказывается о сложностях, которые могут возникнуть при настройке удаленного соединения и о методах их преодоления.

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

* —host
* —protocol
* —port

Из четырех возможных протоколов удаленное подключение позволяет только TCP/IP, поэтому первым требованием является доступность компьютера из сети по TCP/IP. Далее необходимо добавить имя хоста (или ip адрес машины, на которой расположена база) в строке подключения:
mysql —host=host_name Обратите внимание, что указание в качестве имени хоста значения localhost (или же отсутствие такого параметра, что то же самое, так как это дефолтное значение) приводит к обращению на локальную машину.

Учтите, что параметры имеют две формы записи: длинную и короткую. В свою очередь, если параметр имеет значение (как например, в случае хоста необходимо указать имя хоста к которому нужно подключиться), то короткая форма записи может использоваться с пробелом или без (исключением из этого правила является пароль). Таким образом, следующие три записи являются эквивалентными:

* mysql —host=myhost.ru
* mysql -h myhost.ru
* mysql -hmyhost.ru

Как настроить соединение с удаленной базой?

В MySQL пользователь характеризуется двумя параметрами: именем и хостом, с которого он может обращаться. По умолчанию доступ разрешен только с локальной машины, т.е. для пользователя user@localhost. Права на доступ пользователям даются с помощью команды GRANT. Команда выполняется под рутом.

Например, если я хочу создать юзера, который сможет коннектиться с любого хоста с полными правами, то следует выполнить следующую команду:
GRANT ALL PRIVILEGES ON `имя_базы`.* TO myuser@% IDENTIFIED BY ‘пароль’;

Примечание. Обратите внимание, что данная команда дает доступ пользователю myuser со всех IP кроме 127.0.0.1, соответсвующего localhost.
Для пользователя myuser@localhost необходимо давать права отдельной командой GRANT.

Если вы решите обратиться к локальной машине как к удаленной, используя протокол TCP/IP, то не забывайте, что myuser@localhost и myuser@ip_собственного_компа это разные пользователи и каждому из них права нужно давать отдельной командой.

Второй пример показывает как дать право читать таблицу time_zone в базе mysql пользователю myuser с машины 192.168.0.76 с паролем mypassy:
GRANT SELECT ON mysql.time_zone TO [email protected] IDENTIFIED BY ‘mypassy’;
Возникающие сложности

Если после всех действий у вас возникает ошибка (Can’t connect to Mysql Server on ‘ваш IP"(10061)), значит соединение блокирует firewall (windows firewall, outpost, антивирус или еще что-то).

Если возникает ошибка (Client does not support autentification protocol request by server; consider upgrading MySQL client), клиент (то есть ваша программа) не поддерживает требуемый сервером протокол.
Эту ошибку можно исправить, установив пароль старого формата:
SET PASSWORD FOR user@host = OLD_PASSWORD(‘password’);

Хорошо Плохо

В данной статье описывается что делать если вы забыли администраторский пароль или случайно удалили самого root-пользователя. Статья ориентирована в первую…

Понадобилось мне организовать двухстороннюю репликацию БД на двух mysql-серверах, запущенных на одной машине. Казалось бы ничего сложного и особенного тут нет, но…