Mysql server подключение к серверу. Настройка удаленного подключения к MySQL

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

Чтобы устранить эти проблемы, существуют различные инструменты для удалённого подключения: HeidiSQL в Windows, Sequel Pro для macOS, кроссплатформенный инструмент MySQL Workbench и т.п. С помощью этих инструментов можно создать SSH-подключение к серверу БД и обойти все потенциальные опасности.

Это краткое руководство поможет вам подключиться к удалённому серверу баз данных с помощью MySQL Workbench.

Требования

• Сервер MySQL с SSH-доступом (инструкции по установке можно найти в ).
• Инструмент MySQL Workbench, установленный на локальной машине. MySQL Workbench подходит для большинства операционных систем (Windows, macOS, Ubuntu Linux, RedHat Linux, Fedora и т.д.). Чтобы загрузить инсталлятор, откройте эту страницу .

Также нужно располагать следующей информацией о сервере:

• Внешний IP-адрес сервера MySQL.
• Порт SSH (стандартный — 22).
• Аккаунт пользователя с SSH-доступом к серверу (с парольной аутентификацией или ключами).
• Учётные данные пользователя MySQL.

SSH-подключение к серверу баз данных

После установки MySQL Workbench запустите программу. Создайте новое подключение. Для этого нужно нажать кнопку + (в главном окне рядом с MySQL Connections).

На экране появится окно Connect to Database. Введите запрашиваемые данные:

• В поле Connection Name укажите описательное название соединения (Connection Name помогает отличить одно соединение от другого). Для примера мы используем database_for_myapp.
• Значение Connection Method замените на Standard TCP/IP over SSH
• В поле SSH Hostname укажите IP сервера MySQL. Если вы изменили стандартный порт SSH, укажите пользовательский номер порта через двоеточие после IP-адреса.
• В SSH Username укажите имя пользователя для SSH-подключения.
• В SSH Password укажите пароль выбранного пользователя. Если вместо пароля вы используете ключи, выберите SSH-ключ.
• В полях MySQL Hostname и MySQL Server Port оставьте значения по умолчанию.
• В Username укажите имя пользователя MySQL.
• В Password укажите пароль или оставьте поле пустым. Если вы не храните пароль в MySQL Workbench, программа запросит его при подключении.
• Чтобы убедиться, что все настройки указаны правильно, выберите Test Connection.
• Чтобы создать подключение, нажмите OK.

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

Список баз данных можно найти в SCHEMAS в левой навигационной панели. Стрелка рядом с каждой базой данных позволяет расширить информацию и перемещаться по таблицам и объектам. Вы можете просматривать таблицы, писать сложные запросы и редактировать данные.

Также MySQL Workbench позволяет управлять соединениями. Для этого выберите в меню Database → Connect to Database, а затем нажмите ⌘U в Mac или CTRL+U в Windows и Linux. Чтобы подключиться к другой БД, создайте новое соединение.

Заключение

MySQL Workbench позволяет быстро создать безопасный SSH-туннель к удалённому серверу баз данных 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’);

Для создания базы данных MySQL и последующего подключения к ней мода, необходимо из раздела «Услуги» заказать «Веб-хостинг». Услуга бесплатная и позволяет создать Вам до 10 баз данных. После заказа услуги войдите в её настройки, там будет URL, логин и пароль панели управления веб-хостингом (скриншот ниже).

После авторизации, в меню (которое слева) найдите раздел «Инструменты», в котором нужно будет перейти в пункт «Базы данных». Вы перейдете в раздел Управлениями базами данных, где сможете видеть созданные вами базы данных. В этом разделе можете создать новую базу данных нажатием кнопки «Создать» (скриншот ниже).

После нажатия кнопки «Создать» у Вас появится окно с формами которые необходимо заполнить. Придумайте уникальное имя базы и логин нового пользователя. Обязательно установите галочку в форме "Удаленный доступ", иначе ваш мод не сможет подключиться к базе данных. Очень важный момент! Вам необходимо запомнить введенные данные, так как они будут использоваться при подключении мода, лучше запишите их отдельно себе в текстовый документ. Пример заполнения Вы можете увидеть на скриншоте ниже.

В целом уже была создана база данных и данные можно прописывать в мод, но часто бывает, когда нужно еще импортировать SQL дампы данных в созданную базу данных, они имеют расширение.sql и идут в папке с файлами вашего мода. Если же Вам это не требуется, то можете пропустить последующие два абзаца. Импортировать можно с помощью инструмента «PhpMyAdmin» ( на PhpMyAdmin) который находится в меню веб-хостинга в разделе «Доп. приложения». Для авторизации введите данные которые Вы сами же создавали выше, а именно логин пользователя и пароль от вашей базы данных (смотрите скриншот ниже).

После авторизации слева Вы увидите список ваших баз данных, кликните по вашей созданной базе данных левой кнопкой мыши, после перейдите в пункт меню «Импорт». Выберите пункт «Обзор вашего компьютера», после чего нажмите на кнопку «Выберите файл», все это проиллюстрировано на скриншоте ниже. После выбора необходимого дампа формата.sql, нажмите кнопку «Вперёд», начнётся процесс импорта. По завершению импорта, Вы увидите сообщение «Импорт успешно завершен, выполнено N запросов (имядампа.sql)».

Финальная часть. Внесение данных от базы данных в мод. Для этого, необходимо воспользоваться редактором скриптов pwn (pawno.exe), он идет в стандартном составе сервера для Windows (SA-MP Windows Server Download), скачать его можно с официального сайта SA-MP.com (пусть это не вводит Вас в заблуждение, на нашем хостинге Linux, нам нужно лишь программа pawno.exe из папки pawno). Так-же очень часто этот редактор находится в архиве с вашим скачанным модом.

Откройте редактор pawno.exe. В меню «File» выберите «Open» для открытия вашего исходника мода. Исходник имеет расширение.pwn и находится в папке gamemodes. После открытия, Вам нужно найти и заполнить следующие поля (в скобках пояснения к пунктам) :

#define MYSQL_HOST "" (хост базы данных, всегда указывайте 31.28.170.123 )
#define MYSQL_USER "" (пользователь)
#define MYSQL_DATABASE "" (имя базы)
#define MYSQL_PASSWORD "" (пароль пользователя)

В качестве хоста обязательно указывайте следующее значение - 31.28.170.123. Именно этот IP адрес является хостом базы данных нашего веб-хостинга (не нужно путать это с IP вашего игрового сервера). В остальных полях вводите те данные, которые Вы создавали самостоятельно (ведь не зря мы говорили Вам еще на этапе создания БД про то, что Вам нужно запомнить их). В качестве примера уже введенных данных смотрите скриншот ниже.

Остается лишь скомпилировать изменения в нашем моде, для этого откройте меню «Build» и выберите пункт «Compile» или просто нажмите клавишу F5. После успешной компиляции у Вас появится файл с названием вашего же редактируемого мода, но только не в формате.pwn, а уже в готовом формате.amx который можете загружать на ваш игровой сервер посредством

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

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

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

1. открываем конфигурационный файл для редактирования:
   sudo nano /etc/mysql/my.cnf
2. комментируем строку:
   # bind-address = 127.0.0.1
3. перезапускаем 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 извне зайдите в Панель управления и перейдите в раздел «Базы данных 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

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