How to Allow Remote Connections to MySQL
It is not uncommon to host databases and web servers on the same local machine. However, many organizations are now moving to a more distributed environment.
A separate database server can improve security, hardware performance, and enable you to scale resources quickly. In such use cases, learning how to manage remote resources effectively is a priority.
This tutorial shows you how to enable remote connections to a MySQL database.
- Access to a terminal window/command line
- Remote MySQL server
- Sudo or root privileges on local and remote machines
Note: If you do not have direct access to your MySQL server, you need to establish a secure SSH connection. In case you need assistance, we have prepared a comprehensive tutorial on how to use SSH to connect to a remote server. This article a must-read for anyone new to the process.
MySQL Server Remote Connection
Allowing connections to a remote MySQL server is set up in 3 steps:
1. Edit MySQL config file.
2. Configure firewall.
3. Connect to remote MySQL server.
Step 1: Edit MySQL Config File
1.1 Access mysqld.cnf File
Use your preferred text editor to open the mysqld.cnf file. This example uses the nano text editor in Ubuntu 18.04. Enter the following command in your command-line interface to access the MySQL server configuration file:
The location of the file may vary based on the distribution and version in use. If the MySQL configuration file is not it its default location try using the Linux find command to detect it.
1.2 Change Bind-Address IP
You now have access to the MySQL server configuration file. Scroll down to the bind-address line and change the IP address. The current default IP is set to 127.0.0.1. This IP limits MySQL connections to the local machine.
The new IP should match the address of the machine that needs to access the MySQL server remotely. For example, if you bind MySQL to 0.0.0.0, then any machine that reaches the MySQL server can also connect with it.
Once you make the necessary changes, save and exit the configuration file.
Note: Remote access is additionally verified by using the correct credentials and user parameters you have defined for your MySQL users.
1.3 Restart MySQL Service
Apply the changes made to the MySQL config file by restarting the MySQL service:
Next, your current firewall settings need to be adjusted to allow traffic to the default MySQL port.
Step 2: Set up Firewall to Allow Remote MySQL Connection
While editing the configuration file, you probably observed that the default MySQL port is 3306.
If you have already configured a firewall on your MySQL server, you need to open traffic for this specific port. Follow the instructions below that correspond to your firewall service in use.
Option 1: UFW (Uncomplicated Firewall)
UFW is the default firewall tool in Ubuntu. In a terminal window, type the following command to allow traffic and match the IP and port:
The system confirms that the rules were successfully updated.
Option 2: FirewallD
The firewalld management tool in CentOS uses zones to dictate what traffic is to be allowed.
Create a new zone to set the rules for the MySQL server traffic. The name of the zone in our example is mysqlrule, and we used the IP address from our previous example 133.155.44.103:
You have successfully opened port 3306 on your firewall.
Option 3: Open Port 3306 with iptables
The iptables utility is available on most Linux distributions by default. Type the following command to open MySQL port 3306 to unrestricted traffic:
To limit access to a specific IP address, use the following command instead:
This command grants access to 133.155.44.103. You would need to substitute it with the IP for your remote connection.
It is necessary to save the changes made to the iptables rules. In an Ubuntu-based distribution type the following commands:
Note: If the previous commands do not work, try installing the program with:
Type the ensuing command to save the new iptables rules in CentOS:
Step 3: Connect to Remote MySQL Server
Your remote server is now ready to accept connections. Use the following command to establish a connection with your remote MySQL server:
The -u username in the command represents your MySQL username. The -h mysql_server_ip is the IP or the hostname of your MySQL server. The -p option prompts you to enter the password for the MySQL username.
You should see an output similar to the one below:
How to Grant Remote Access to New MySQL Database?
If you do not have any databases yet, you can easily create a database by typing the following command in your MySQL shell:
To grant remote user access to a specific database:
The name of the database, the username, remote IP, and password need to match the information you want to use for the remote connection.
How to Grant Remote Access to Existing MySQL Database
Granting remote access to a user for an existing database requires a set of two commands:
User1 is now able to access yourDB from a remote location identified by the IP 133.155.44.103.
Note: Learn everything you need to know about database servers and how they work in our article What Is a Database Server.
In this article, you have gained valuable insight into the general principles of a remote MySQL connection.
With the appropriate credentials, a user originating from the specified IP address can now access your MySQL server from a remote machine.
Как разрешить удаленные подключения к серверу базы данных MySQL
По умолчанию сервер MySQL прослушивает соединения только с localhost, что означает, что к нему могут получить доступ только приложения, работающие на том же хосте.
Однако в некоторых ситуациях необходимо получить доступ к серверу MySQL из удаленного места. Например, если вы хотите подключиться к удаленному серверу MySQL из вашей локальной системы, или при использовании многосерверного развертывания, когда приложение выполняется на другом компьютере, чем сервер базы данных. Один из вариантов — получить доступ к серверу MySQL через туннель SSH, а другой — настроить сервер MySQL на прием удаленных подключений.
В этом руководстве мы рассмотрим шаги, необходимые для разрешения удаленных подключений к серверу MySQL. Те же инструкции применимы и для MariaDB.
Настройка сервера MySQL
Первый шаг — настроить сервер MySQL на прослушивание определенного IP-адреса или всех IP-адресов на машине.
Если сервер MySQL и клиенты могут связываться друг с другом через частную сеть, то лучшим вариантом будет настроить сервер MySQL на прослушивание только частного IP-адреса. В противном случае, если вы хотите подключиться к серверу через общедоступную сеть, настройте сервер MySQL на прослушивание всех IP-адресов на машине.
Для этого вам необходимо отредактировать файл конфигурации MySQL и добавить или изменить значение параметра bind-address . Вы можете установить один IP-адрес и диапазоны IP-адресов. Если адрес 0.0.0.0 , сервер MySQL принимает соединения на всех интерфейсах хоста IPv4. Если в вашей системе настроен IPv6, то вместо 0.0.0.0 используйте :: .
Расположение файла конфигурации MySQL отличается в зависимости от дистрибутива. В Ubuntu и Debian файл находится по адресу /etc/mysql/mysql.conf.d/mysqld.cnf , а в дистрибутивах на основе Red Hat, таких как CentOS, файл находится по адресу /etc/my.cnf .
Найдите строку, которая начинается с bind-address и установите ее значение равным IP-адресу, который сервер MySQL должен прослушивать.
По умолчанию установлено значение 127.0.0.1 (прослушивается только на localhost).
В этом примере мы настроим сервер MySQL для прослушивания всех интерфейсов IPv4, изменив значение на 0.0.0.0
Если есть строка, содержащая skip-networking , удалите ее или закомментируйте, добавив # в начале строки.
В MySQL 8.0 и выше директива bind-address может отсутствовать. В этом случае добавьте его в раздел [mysqld] .
После этого перезапустите службу MySQL, чтобы изменения вступили в силу. Только root или пользователи с привилегиями sudo могут перезапускать службы.
Чтобы перезапустить службу MySQL в Debian или Ubuntu, введите:
В дистрибутивах на основе RedHat, таких как CentOS, для перезапуска службы выполните:
Предоставление доступа пользователю с удаленного компьютера
Следующим шагом будет разрешение доступа к базе данных удаленному пользователю.
Войдите на сервер MySQL как пользователь root, набрав:
Если вы используете старый собственный плагин аутентификации MySQL для входа в систему как root, выполните приведенную ниже команду и введите пароль при появлении запроса:
Изнутри оболочки MySQL используйте оператор GRANT чтобы предоставить доступ удаленному пользователю.
- database_name — это имя базы данных, к которой будет подключаться пользователь.
- user_name — это имя пользователя MySQL.
- ip_address — это IP-адрес, с которого пользователь будет подключаться. Используйте % чтобы разрешить пользователю подключаться с любого IP-адреса.
- user_password — пароль пользователя.
Например, чтобы предоставить доступ к базе данных dbname пользователю с именем foo с паролем my_passwd с клиентского компьютера с IP 10.8.0.5 , вы должны запустить:
Настройка межсетевого экрана
Последний шаг — настроить брандмауэр, чтобы разрешить трафик на порт 3306 (порт по умолчанию MySQL) с удаленных машин.
Iptables
Если вы используете iptables в качестве брандмауэра, приведенная ниже команда разрешит доступ с любого IP-адреса в Интернете к порту MySQL. Это очень небезопасно.
Разрешить доступ с определенного IP-адреса:
UFW — это брандмауэр по умолчанию в Ubuntu. Чтобы разрешить доступ с любого IP-адреса в Интернете (очень небезопасно), запустите:
Разрешить доступ с определенного IP-адреса:
БрандмауэрD
FirewallD — это инструмент управления брандмауэром по умолчанию в CentOS. Чтобы разрешить доступ с любого IP-адреса в Интернете (очень небезопасно), введите:
Чтобы разрешить доступ с определенного IP-адреса через определенный порт, вы можете создать новую зону FirewallD или использовать расширенное правило. Итак, создайте новую зону с именем mysqlzone :
Проверка изменений
Чтобы убедиться, что удаленный пользователь может подключиться к серверу MySQL, выполните следующую команду:
Где user_name — это имя пользователя, mysql_server_ip вы предоставили доступ, а mysql_server_ip — это IP-адрес хоста, на котором работает сервер MySQL.
Если все настроено правильно, вы сможете войти на удаленный сервер MySQL.
Если вы получаете сообщение об ошибке, подобное приведенному ниже, то либо порт 3306 не открыт , либо сервер MySQL не прослушивает IP-адрес .
Приведенная ниже ошибка указывает на то, что пользователь, которого вы пытаетесь войти в систему, не имеет разрешений на доступ к удаленному серверу MySQL.
Выводы
MySQL, самый популярный сервер баз данных с открытым исходным кодом по умолчанию, прослушивает входящие соединения только на localhost.
Чтобы разрешить удаленные подключения к серверу MySQL, вам необходимо выполнить следующие шаги:
How to Allow MySQL remote connections in Ubuntu Server 18.04
This tutorial explains how to allow remote connections to the MySQL/MariaDB server on Ubuntu 18.04. The default behavior of the Ubuntu MySQL Server blocks all remote connections. Which prevent us from accessing the database server from the outside.
Note that to allow mysql remote connections we need to edit the MySQL main configuration file. If you are using MariaDB Database server, configuration file going to be «/etc/mysql/mariadb.conf.d/50-server.cnf», If you have installed MySQL Database server configuration file is: «/etc/mysql/mysql.conf.d/mysqld.cnf».
Open the /etc/mysql/mariadb.conf.d/50-server.cnf file (or /etc/mysql/mysql.conf.d/mysqld.cnf).
Under the [mysqld] section, locate the line:
And change it to:
Save the configuration file, and restart the MySQL server:
Run the netstat command and make sure that mysql server listen on socket 0 0.0.0.0:3306.
The output should be similar to the following:
How it works..
By default the mysql daemon on Ubuntu 18.04 is only listening for connections on localhost (127.0.0.1), which mean you cannot login to the server from a remote computer. This setting is controlled by the bind-address in the MySQL/MariaDB configuration file. By default it is set to: «bind-address = 127.0.0.1» which prevents other hosts from accessing our mysql server.
To allow remote access, we changed the value of the bind-address to: «0.0.0.0».
By changing value to 0.0.0.0, we instruct MySQL to bind to all available interfaces and by doing that we allow remote connections to the MySQL Server on Ubuntu 18.04.
Open port 3306 from Ubuntu Firewall
UFW firewall is disabled by default in Ubuntu 18.04, so you don’t have to worry about opening mysql port 3306 if you didn’t enable UFW.
But if have enabled UFW then it will block the mysql remote access, so you need to add firewall rule to open the port 3306.
From a another Linux machine, you can run nmap against your server IP to check whether port 3306 is open or not.
Create Remote MySQL user and grant remote access to databases
Now that our MySQL server allows remote connections, we still need to have a mysql user that is allowed to access the server from outside the localhost. To create a mysql user that is allowed to connect from any host, login in the MySQL console and run:
Then you can grant access to databases using the GRANT ALL command:
If you want to grant access to all databases on the server, run:
If you want to create a user that is only allowed to login from a specific host, replace ‘%’ with host IP or domain name when creating the user.
To test the connection, try to access the MySQL server from a remote computer:
Here 192.168.1.100 is the IP address of my Ubuntu Server where MySQL server is running.
Note that, enabling remote connections to MySQL server is not good practice from a security standpoint. So don’t expose your database server to outside unless you must, especially in a production environment.
Port 3306 appears to be closed on my Ubuntu server
According to an open port finder, port 3306 for the offending server appears to be closed. I have C++ and Java programs of my own listening on arbitrary ports without any issues. Why is this happening and how can I fix it?
Ubuntu installed is Ubuntu 11.10 (GNU/Linux 2.6.32-042stab072.10 x86_64)
Results of netstat -tuple on each server
Offending server
Working server
5 Answers 5
The problem was that the server was listening internally only.
Removing the line bind-address 127.0.0.1 from /etc/mysql/my.cnf solved the issue.
Newer versions of Ubuntu (≥16.04) may have this line in /etc/mysql/mysql.conf.d/mysqld.cnf .
My suggestion, if you are sure that the ports are closed (I find it weird for a VPS to have that port closed) is to change the configuration file of MySQL to use another.
Simply open the configuration file in the terminal, sudo nano /etc/mysql/mysql.conf , and look for the [mysqld] section. In it, look for the line that reads port = 3306 . Change it to another port not used and save the file.
Then simply either restart the VPS or restart the service, like sudo service mysql restart .
Just to note that, if the file mysql.conf is not in the one I mentioned above it can be in this other places:
And if the service command does not work, then do this:
If the problem persists then in my case I would check iptables (I would actually delete everything in iptables just to start fresh if this could be an option) or any other firewall-enabled option.
Since they are VPS, I would also check the VPS Control Panel to see if it has any option that can block ports.
Apart from that, I would run nmap on the VPS to see what ports you have opened. You need to run it from outside the VPS to see what ports they have opened.
netstat -tuplen is also a good idea to see what opened ports you have on the server and which ones are in LISTEN mode.