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

Введение

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

Одна из наиболее распространённых проблем, с которой сталкиваются пользователи при удалённой настройке базы данных MySQL, заключается в том, что их экземпляр MySQL настроен на прослушивание только локальных подключений. Это настройка MySQL по умолчанию, но она не подходит для настройки удалённой базы данных, поскольку MySQL должна иметь возможность прослушивать внешние IP-адреса, по которым доступен сервер. Чтобы включить эту возможность, откройте файл 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
. . .

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

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

sudo systemctl restart mysql

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

sudo mysql

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

mysql -u root -p

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

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

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

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

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

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'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-адреса. С другой стороны, если вы по-прежнему не можете получить удалённый доступ к базе данных, возможно, проблема вызвана другой причиной.