Как получить удаленный доступ к MySQL

Введение

Многие веб-сайты и приложения начинаются с веб-сервера и базы данных, размещенных на одном компьютере. Однако со временем такая установка может стать громоздкой и трудно масштабируемой. Распространенным решением является разделение этих функций путем настройки удаленной базы данных, что позволяет серверу и базе данных расти в своем собственном темпе на своих собственных машинах.

Одна из наиболее распространенных проблем, с которой сталкиваются пользователи при попытке настроить удаленную базу данных MySQL, заключается в том, что их экземпляр MySQL настроен только на прослушивание локальных подключений. Это значение по умолчанию для MySQL, но оно не будет работать для удаленной настройки базы данных, поскольку MySQL должен иметь возможность прослушивать внешний IP-адрес, по которому можно связаться с сервером.

Включить удаленный доступ

Чтобы включить удаленный доступ к MySQL, откройте файл mysqld.cnf:

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

Перейдите к строке, начинающейся с директивы bind-address. Это будет выглядеть так:

...
 lc-messages-dir = /usr/share/mysql
 skip-external-locking
 #
 # Instead of skip-networking the default is now to listen only on
 # localhost which is more compatible and is not less secure.
 bind-address = 127.0.0.1...

По умолчанию для этого значения установлено значение 127.0.0.1, что означает, что сервер будет искать только локальные соединения. Вам нужно будет изменить эту директиву, чтобы она ссылалась на внешний IP-адрес. В целях устранения неполадок вы можете установить эту директиву на подстановочный IP-адрес, *, :: или 0.0.0.0:

...
 lc-messages-dir = /usr/share/mysql
 skip-external-locking
 #
 # Instead of skip-networking the default is now to listen only on
 # localhost which is more compatible and is not less secure.
 bind-address = 0.0.0.0...

Примечание.bind-addressmysqld.cnf/etc/mysql/mysql.conf.d/mysqld.cnf

...
 [mysqld]
 pid-file = /var/run/mysqld/mysqld.pid
 socket = /var/run/mysqld/mysqld.sock
 datadir = /var/lib/mysql
 log-error = /var/log/mysql/error.log
 bind-address = 0.0.0.0

После редактирования этой строки сохраните и закройте файл ( CTRL+X, Y, затем ENTER, если вы редактировали его с помощью nano ).

Затем перезапустите службу MySQL, чтобы изменения вступили в силу:

sudo systemctl restart mysql

Если у вас есть существующая учетная запись пользователя MySQL, которую вы планируете использовать для подключения к базе данных с удаленного хоста, вам потребуется перенастроить эту учетную запись для подключения с удаленного сервера вместо localhost. Для этого откройте клиент MySQL от имени пользователя root MySQL или другой учетной записи привилегированного пользователя:

sudo mysql

Если у вас включена аутентификация по паролю для root, вместо этого вам нужно будет использовать следующую команду для доступа к оболочке MySQL:

mysql -u root -p

Изменить хост пользователя

Чтобы изменить хост пользователя, вы можете использовать команду MySQL RENAME USER. Выполните следующую команду, убедившись, что вы изменили имя noviello вашей учетной записи пользователя MySQL и IP-адрес вашего удаленного сервера remote_server_ip:

RENAME USER 'noviello'@'localhost' TO 'noviello'@'remote_server_ip';

Создать нового пользователя

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

CREATE USER 'noviello'@'remote_server_ip' IDENTIFIED BY 'password';

Примечаниеcaching_sha2_password

Если вы планируете использовать эту базу данных с приложением PHP, таким как phpMyAdmin, вы можете создать удаленного пользователя, который будет аутентифицироваться с помощью более старого плагина mysql_native_password, хотя и по-прежнему безопасным:

CREATE USER 'noviello'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

Если вы не уверены, вы всегда можете создать пользователя, который проходит аутентификацию с помощью caching_sha2_plugin и ALTER позже с помощью этой команды:

ALTER USER 'noviello'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

Затем предоставьте новому пользователю соответствующие привилегии для ваших конкретных нужд. В следующем примере пользователю предоставляются глобальные привилегии CREATE, ALTER и DROP баз данных, таблиц и пользователей, а также передача данных INSERT, UPDATE и DELETE из любой таблицы на сервере. Он также предоставляет пользователю возможность запрашивать данные с помощью SELECT, создавать внешние ключи с помощью ключа REFERENCES и выполнять операции FLUSH с привилегией RELOAD. Однако вы должны предоставлять пользователям только те разрешения, которые им нужны, поэтому не стесняйтесь изменять привилегии вашего пользователя, если это необходимо.

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'noviellO'@'remote_server_ip' WITH GRANT OPTION;

После этого рекомендуется запустить команду FLUSH PRIVILEGES. Это освободит любую память, которую сервер кэшировал в результате предыдущих операторов CREATE USER и GRANT:

FLUSH PRIVILEGES;

Затем вы можете выйти из клиента MySQL:

exit

Настроить брандмауэр

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

Если вы планируете получать доступ к серверу базы данных только с определенного компьютера, вы можете предоставить этому компьютеру монопольное разрешение на удаленное подключение к базе данных с помощью следующей команды. Убедитесь, что вы заменили remote_IP_address фактическим IP-адресом машины, к которой вы планируете подключиться:

sudo ufw allow from remote_IP_address to any port 3306

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

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

ПредупреждениеНе

sudo ufw allow 3306

Затем попробуйте удаленно получить доступ к вашей базе данных с другого компьютера:

Примечание

mysql -u user -h database_server_ip -p

Заключение

Если вы можете получить доступ к своей базе данных, убедитесь, что проблема связана с директивой bind-address в вашем конфигурационном файле. Обратите внимание, однако, что установка bind-address на 0.0.0.0 небезопасна, поскольку позволяет подключаться к вашему серверу с любого IP-адреса. С другой стороны, если вы по-прежнему не можете получить удаленный доступ к базе данных, проблема может быть вызвана чем-то другим.