Доступ к MySql из сети. Настройка удаленного подключения к MySQL
Эта статья для тех, кто впервые столкнулся с необходимостью установить удаленное соединение с базой данных 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-серверах, запущенных на одной машине. Казалось бы ничего сложного и особенного тут нет, но…
С помощью удаленного подключения к 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 - имя вашего сайта.
В качестве пароля используйте пароль для входа в Панель управления хостингом.
Туннель установлен. Для соединения с базой данных используйте имя пользователя и пароль, заданные вами при создании базы данных в разделе Панели управления «Базы данных». В качестве адреса сервера используйте 127.0.0.1, порт для подключения 3306.
Пример подключения с помощью программы 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
Если соединение установлено, процедуру настройки туннеля можно считать завершенной. В противном случае необходимо тщательно проверить настройки, убедиться в отсутствии сетевых проблем и связаться со службой технической поддержки.
Для сервиса, который работает на другой машине. В общем случае нам необходимо выполнить 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 используется в 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 имеет возможность прослушивать определенные адреса для приема входящих подключений. При установке "по-умолчанию" каждый сервер 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, в случае, если на сервере отключен удаленный доступ, появится ошибка вида.