Организация удаленного доступа к серверу MySQL

Что такое MySQL и зачем может понадобиться удаленный доступ к нему

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

Подготовительный этап

Чтобы разрешить доступ к своим базам данных потребуется:

• Сервер, управляемый актуальной операционной системой (например, Ubuntu 20.04);
• Доступ к системной учетной записи «root»;
• Привилегированный доступ к сервису управления базами данных.

Разблокировка доступа к MySQL извне

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

Войдите на свой сервер как привилегированный пользователь и уточните, какой именно сетевой порт использует MySQL:

Как видим, прослушивается TCP-порт 3306. Разрешим доступ к нему в настройках брандмауэра:

ufw allow 3306

Далее нужно найти, в каком именно из файлов, описывающих настройки MySQL, объявлен адрес, прослушиваемый службой:

grep -r "bind" /etc/mysql/

Замените значение директивы bind-address на 0.0.0.0 и проверьте результат:

service mysql restart

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

Проверка доступности службы

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

mysql -h <IP_сервера_БД> -u<имя_пользователя> -p

Затем введите или вставьте скопированный ранее в буфер пароль от созданной учетной записи, чтобы попасть на сервер управления базами данных.
Теперь можно, например. посмотреть на содержимое базы:

show databases;
show tables in <имя_базы_данных>;
select * from <имя_базы_данных>.<имя_таблицы>;

Краткие итоги

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

Литейный пр., д. 26, Лит. А

+7 (812) 403-06-99

ООО «ИТГЛОБАЛКОМ ЛАБС»

Литейный пр., д. 26, Лит. А

+7 (812) 403-06-99

ООО «ИТГЛОБАЛКОМ ЛАБС»

20 декабря 2022

С помощью phpMyAdmin

• создание, копирование и удаление базы данных;
• создание таблиц;
• редактирование табличных полей;
• поиск информации по БД;
• выполнение SQL-запросов;
• просмотр прав пользователя и управление ими;
• импорт и экспорт данных.

Изображение от Freepik

Веб-интерфейс поможет разобраться в работе с СУБД даже начинающим системным сисадминам и разработчикам: чтобы настроить нужную функцию, достаточно зайти в раздел с соответствующим ей названием и выбрать из списка подходящие параметры. Например, найти данные можно с помощью пункта «Поиск».

Шаг 1. Выберите существующую базу данных или создайте новую. После авторизации в админке необходимо открыть раздел меню «Основное» и перейти в пункт «Базы данных». Выберите нужную БД из появившегося списка. При создании новой базы не забудьте разрешить к ней удаленный доступ.

С помощью SSH

Подключение к серверу с MySQL по SSH начинающие разработчики выбирают редко, так как в этом случае отсутствует графический интерфейс, а управление БД осуществляется с помощью командной строки операционной системы. Однако такое решение имеет свои преимущества — подключение защищено шифрованием, а значит, передаваемые данные нельзя перехватить, и доступно больше функций для работы с информацией. Разбираемся, как подключиться к базе данных MySQL с помощью SSH в ОС Windows и дистрибутивах Linux.

Windows

Шаг 1. Настройки Session. В поле Host Name необходимо указать ftp.доменное_имя, Port — 22, Connection type — SSH.

Шаг 2. Настройки Connection. Выберите тип соединения с сервером SSH, Telnet. Перейдите к заполнению полей: Source port — 3306 (порт для локального доступа после установки туннеля), Destination — localhost:3306 (хост, к которому подключаются по заданному порту) и Local (локальное подключение). Forwarded port заполнится автоматически после нажатия кнопки Add.

Linux

В дистрибутивах Линукс удаленное подключение к MySQL создается с помощью командной строки — Терминала. Последовательность команд:

1. Создание SSH-туннеля:
2. Настройка удаленного подключения к MySQL через конфигурационный файл:

   sudo nano /etc/mysql/my.cnf.

3. Создание комментария:

   # bind-address = 127.0.0.1

4. Сохранение правок и обновление параметров MySQL

   sudo service mysql restart.

5. Создание пользователя
6. Проверка подключения через Telnet.
7. Соединение с сервером:

Заключение

MySQL облегчает работу с данными сайта. Управлять БД напрямую с сервера, на котором она расположена, не всегда удобно, иногда может потребоваться дистанционная работа. Чтобы обезопасить подключение к серверу и доступ к данным, используют сочетание SSH-туннеля и специальных программ: в Windows — PuTTY, в Linux — Терминала. Когда пользователь нуждается в интуитивно понятном интерфейсе, он может воспользоваться phpMyAdmin.

I had to this challenge when working on a Java Project with MySQL server as the database.

Here’s how I did it:

First, confirm that your MySQL server configuration to allow for remote connections. Use your preferred text editor to open the MySQL server configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Scroll down to the bind-address line and ensure that is either commented out or replaced with 0.0.0.0 (to allow all remote connections) or replaced with Ip-Addresses that you want remote connections from.

Once you make the necessary changes, save and exit the configuration file. Apply the changes made to the MySQL config file by restarting the MySQL service:

sudo systemctl restart mysql

Next, log into the MySQL server console on the server it was installed:

mysql -u root -p

SELECT host FROM mysql.user WHERE user = "root";

This gave me this output:

+-----------+
| host |
+-----------+
| localhost |
+-----------+

USE my_database;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'my-password';

SELECT host FROM mysql.user WHERE user = "root";

This gave me this output:

+-----------+
| host |
+-----------+
| % |
| localhost |
+-----------+

Finally, you can try connecting to the MySQL server from another server using the command:

mysql -u username -h mysql-server-ip-address -p

mysql -u root -h 34.69.261.158 -p

You should get this output depending on your MySQL server version:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 

Resources: How to Allow Remote Connections to MySQL

I hope this helps

Внимание! После ручной правки конфигураций MySQL — my.cnf требуется перезапустить сервер MySQL. 

ISPmanager5 — Службы — MySQL/MariaDB — Рестарт или в консоли сервера:

systemctl restart mysql
systemctl restart mariadb

Создание удалённого подключения в ISPmanager 6

В панели ISPmanager — раздел Настройки — Серверы баз данных выделите сервер MySQL и нажмите кнопку Изменить.

В панелях с версией ниже 5.95 опции Удаленный доступ нет. Необходимо выполнить настройки вручную. В конфигурационном файле /etc/my.cnf (/etc/mysql/my.cnf для Debian) должна отсутствовать строка skip-networking, если она присутствует — следует её закомментировать:

#skip-networking

Также следует добавить/изменить:

bind-address = IP_сервера

Для удалённого подключения к базам данных требуется при создании базы отметить Удалённый доступ.

Если при создании базы вы не выбрали данную опцию, её можно указать в Управлении пользователями баз данных. Для этого перейдите в раздел Инструменты — Базы данных — выделите нужную базу — нажмите кнопку Пользователи — затем кнопку Изменить.

Создание удалённого подключения в консоли

В конфигурационном файле /etc/my.cnf (/etc/mysql/my.cnf для Debian) должна отсутствовать строка skip-networking, если она присутствует — следует её закомментировать:

#skip-networking

Также следует добавить/изменить:

bind-address = IP_сервера

Подключитесь к сервер MySQL суперпользователем:

mysql -u root -p

create user 'dbuser'@'%' identified by 'password'

Можно проверить, что мы создали, командой, выводящей всех пользователей с их хостом и паролем:

select user,host,password from mysql.user;

Создайте базу, например, testdb:

create database testdb;

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

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

Наиболее часто назначаемые права:

• ALL PRIVILEGES — пользователю предоставляется полный доступ к базе данных.
• CREATE — пользователю разрешено создавать базы данных и таблицы.
• DROP — пользователю разрешено удалять базы данных и таблицы.
• DELETE — пользователю разрешено удалять строки из определенной таблицы.
• INSERT — пользователю разрешено вставлять строки в определенную таблицы.
• SELECT — пользователю разрешено читать базу данных.
• UPDATE — пользователю разрешено обновлять строки таблицы.
• GRANT OPTION — пользователю разрешено изменять права других пользователей.

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

GRANT permission ON database.table TO 'username'@'%';

где permission — вид предоставляемой привилегии.

GRANT ALL PRIVILEGES ON testdb.* TO 'dbuser'@'%';

Предоставить права на вставку:

GRANT INSERT ON *.* TO 'dbuser'@'%';

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

GRANT SELECT, INSERT, DELETE ON testdb.* TO 'dbuser'@'%';

Можно ограничить права пользователя на добавление данных в определенной базе, указав её перед точкой. Для ограничения прав на доступ к определенной таблице укажите нужную таблицу после точки:

GRANT INSERT *database_name.table_name* TO 'dbuser'@'%';

Отозвать все привилегии пользователя:

REVOKE ALL PRIVILEGES ON testdb.* FROM 'dbuser'@'%';

Посмотреть текущие привилегии пользователя:

SHOW GRANTS FOR 'dbuser'@'%';

DROP USER 'dbuser'@'%';

После завершения настройки прав необходимо перезагрузить привилегии:

FLUSH PRIVILEGES;

Этот материал был полезен?

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

sudo nano /etc/mysql/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

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

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

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

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';

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

Заходим в MySQL:

mysql -u root mysql -p

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

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

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

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