如果您正在使用MySQL 数据库(MySQL database),那么您已经意识到在保持数据库安全方面面临的挑战。从使用SQL(SQL)注入的数据库黑客攻击到暴力攻击,很难保证数据安全,尤其是在远程使用数据库时。
有多种方法可以配置SQL服务器以允许远程连接,但您需要小心,因为在MySQL服务器上允许远程连接会使您的数据库成为黑客的轻松目标。如果你想允许安全的远程连接到MySQL数据库,这里是你需要知道的。
在你开始之前(Before You Begin)
在对MySQL(MySQL)数据库进行任何更改之前,备份数据库(backup your database)非常重要,尤其是当您在生产服务器(正在使用的服务器)上工作时。如果出现问题,您对数据库或托管它的服务器所做的任何更改都可能导致严重的数据丢失。
您可能还会发现对服务器连接的更改可能会阻止您以后访问它。如果发生这种情况,您可能需要咨询服务器管理员以获得进一步的支持。一个好主意是在本地运行的MySQL服务器上试用任何更改,以在远程尝试之前检查您的更改是否有效。
如果您要对远程服务器进行更改,也可能需要一种安全的方式来连接和进行更改。SSH(安全外壳)(SSH (Secure Shell))通常是执行此操作的最佳方式,因为它允许您连接到远程服务器。您还可以使用SSH连接到本地网络上的服务器,例如托管在 Raspberry Pi 上的(hosted on a Raspberry Pi)服务器。
本指南将引导您完成配置MySQL以允许远程连接的步骤,但您需要首先确保您可以直接或远程访问托管MySQL服务器的服务器。
假设(Suppose)您没有通过SSH远程访问您的服务器(例如)。在这种情况下,您将无法将MySQL数据库配置为直接允许远程连接,除非您的 root mySQL 帐户已经允许远程连接。因此,您需要先建立此连接,然后才能继续。
编辑你的 MySQL 配置文件(Editing Your MySQL Configuration File)
配置MySQL(MySQL)以允许远程连接的第一步是编辑MySQL配置文件。到此阶段,本指南将假定您已经远程连接到托管您的 mySQL 数据库 的服务器、PC 或Mac并具有控制台访问权限。(Mac)
或者,您可以使用Mac(Mac)或Linux上的开放终端或Windows上的文本编辑器配置本地MySQL服务器。
- 首先,使用您首选的控制台文本编辑器来编辑您的MySQL数据库文件。在Linux 上(Linux),在终端或SSH窗口中键入sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf nano编辑器编辑此文件(假设您的MySQL数据库位于默认位置)。
- 如果您运行的是Windows,请打开文件资源管理器(File Explorer)并访问包含MySQL安装的文件夹(例如C:/Program Files/MySQL/MySQL Server 8.0)。通过双击条目,使用默认文本编辑器(例如记事本(Notepad))打开my.ini文件。(my.ini)如果不存在,请先创建文件(create the file first)。
- 在Mac上,打开终端窗口并输入sudo nano /usr/local/etc/my.cnf。如果您使用 homebrew安装了(using homebrew)MySQL,这是 MySQL 的默认配置文件(MySQL)。
上面引用的位置是MySQL配置文件的默认位置。如果这些命令不起作用,您将需要手动搜索相关文件(my.cnf、mysqld.cnf或my.ini)以找到相关文件路径。
设置安全绑定地址 IP 范围(Setting a Safe Bind-Address IP Range)
- 打开服务器的MySQL配置文件后,使用键盘的箭头键到达文件的绑定地址(bind-address)部分。此 IP 范围限制与数据库的连接,通常设置为仅允许使用127.0.0.1来自本地计算机或服务器的连接。
- 如果您想将MySQL数据库配置为允许使用当前 Internet 连接的设备进行连接,请先找到您的公共 IP 地址(find your public IP address),然后将127.0.0.1替换为该 IP 地址。或者,将其替换为您希望允许连接的设备或服务器的 IP 地址。
- 在某些情况下,您可能希望允许所有(all)远程连接到MySQL数据库。这会带来极大的风险(extreme risk),不应该在生产服务器上使用。但是,如果您想允许这样做,请将127.0.0.1替换为0.0.0.0。
- 记下“基本设置”(Basic Settings)部分中的端口(port )值。这将在下一节中需要。如果不可见,将使用默认值,即端口3306。您可以通过在新行上键入port = xxxx来添加自己的端口,将xxxx替换为合适的端口值。
- 在MySQL配置文件中配置绑定地址(bind-address )后,保存文件。如果您使用的是Linux,请选择Ctrl + O 和Ctrl + X 来执行此操作。在 Mac 上,选择Command + O 和Command + X。Windows 用户可以通过选择File > Save来保存。
- 接下来,Linux和Mac用户可以通过键入mysql.server stop && mysql.server start或mysql.server restart来重启(mysql.server restart)MySQL。您可能需要使用 sudo(using sudo)提升命令(例如sudo mysql.server restart)并使用 mysql.server 文件的适当路径(例如/usr/local/bin/mysql.server)。
- 如果上述命令不起作用,请尝试sudo service mysql restart代替。
- 要在Windows上重新启动(Windows)MySQL ,请通过右键单击开始(Start)菜单并选择Windows PowerShell (Admin)打开一个新的PowerShell窗口。在 PowerShell 窗口中,键入net stop mysql80 ,然后键入net start mysql80,将mysql80替换为您 PC 上的正确服务名称。
如果您不确定Windows上的正确服务名称,请键入net start来查找它。如果您无法重新加载配置,请重新启动服务器并手动重新加载MySQL(如果需要)。
配置你的防火墙(Configuring Your Firewalls)
在这个阶段,您的MySQL数据库应该允许使用您在(MySQL)MySQL配置文件中设置为(MySQL)bind-address值的 IP 地址从设备进行远程连接(或者如果您将此值设置为0.0.0.0 ,则从所有设备进行远程连接)。但是,您的设备或网络防火墙(device or network firewall)仍会阻止连接。
大多数服务器和 PC 使用防火墙来阻止连接,除非授予对特定端口的访问权限。配置它的步骤会有所不同,具体取决于您是在Windows还是Linux上运行(Linux)MySQL。Mac防火墙默认处于禁用状态,因此您无需在此处完成任何其他步骤。
配置 Linux 防火墙(Configure Linux Firewalls)
许多 Linux 服务器使用iptables作为默认的防火墙实用程序。您可以按照以下步骤进行配置。
- 打开终端或SSH连接并输入sudo iptables -A INPUT -p tcp -s XXXX –dport YYYY -j ACCEPT。将XXXX替换为您希望允许MySQL连接的设备的 IP 地址,并将(from)YYYY替换为MySQL配置文件中匹配的端口值(例如3306)。
- 这将临时配置防火墙。如果您使用的是基于 Debian 或 Ubuntu 的Linux服务器,请通过在终端或 SSH 窗口中键入sudo netfilter-persistent save和sudo netfilter-persistent reload来使此更改永久生效。(sudo netfilter-persistent reload )
如果 iptables 不是Linux发行版的默认防火墙工具,则需要查阅发行版的用户手册以获取更多信息。如果某些软件包(例如netfilter-persistent)不可用,请使用您的发行版的软件存储库工具来安装它(例如sudo apt install netfilter-persistent)。
配置 Windows 防火墙(Configure Windows Firewalls)
如果您使用 Windows PC 或服务器来托管数据库,则可以使用以下步骤配置防火墙:
- 右键单击(Right-click)开始(Start)菜单并选择运行(Run)。
- 在“运行”(Run)框中,键入wf.msc并选择“确定(OK)” 。
- 在Windows Defender窗口中,选择入站规则(Inbound Rules )>新规则(New Rule)。
- 在新建入站规则向导(New Inbound Rule Wizard)窗口中,选择端口(Port )>下一步(Next)。
- 在下一个菜单中,从选项中选择TCP,键入3306 (或(3306 )MySQL配置文件中列出的任何端口值),然后选择Next。
- 在操作(Action)菜单中,保留默认选项允许连接(Allow the connection)启用,然后选择下一步(Next)。
- 确认您希望规则适用于所有网络类型,然后选择Next。
- 在提供的端口中输入规则的描述性名称(例如MySQL ),然后选择(MySQL)Finish将其添加到防火墙规则列表中。
如果您在连接时遇到问题,请重复上述步骤,确保使用相同的详细信息(端口 3306 等)在防火墙设置中创建新的出站规则。(outbound rule )您可能还需要配置本地网络路由器以打开必要的阻塞端口(open the necessary blocked ports),以允许与数据库的入站和出站连接。
使用 MySQL 连接到远程服务器(Connecting to a Remote Server Using MySQL)
将MySQL(MySQL)数据库配置为允许远程连接后,您需要实际建立到它的连接。您可以在终端或PowerShell窗口中使用mysql命令(Windows上的mysql.exe )来执行此操作。(mysql.exe )
如果您正在运行Windows,则需要确保在开始之前本地安装了 MySQL 。(MySQL is installed locally)Mac用户可以使用 homebrew(using homebrew)从终端(brew install mysql )(MySQL) 安装 MySQL ,而Linux用户可以使用他们的本地应用程序存储库(例如sudo apt install mysql)安装必要的软件包。
在 Linux 或 Mac 上连接到 MySQL(Connecting to MySQL on Linux or Mac)
- 要连接到Mac或Linux上的远程(Linux)MySQL服务器,请打开一个新的终端窗口并输入mysql -u username -h XXXX:XXXX -p。将XXXX:XXXX替换为您的远程服务器 IP 地址和端口号(例如100.200.100.200:3306),将用户名(username)替换为您的 MySQL 用户名。
- 出现提示时,确认您的密码。如果连接成功,终端会显示成功消息。
在 Windows 上连接到 MySQL(Connecting to MySQL on Windows)
- 要连接到 Windows 上的远程MySQL(Windows)服务器(MySQL),请通过右键单击开始(Start)菜单并选择Windows PowerShell (Admin)打开一个新的PowerShell窗口。
- 在新的 PowerShell 窗口中,键入cd “C:\Program Files\MySQL\MySQL Workbench 8.0\ ” 进入正确的文件夹,将此目录替换为您 PC 上正确的安装目录。例如,如果您的MySQL版本是 8.0.1,请改用MySQL Workbench 8.0.1文件夹。
- 从那里输入.\mysql.exe -u username -h X.X.X.X:XXXX -p。将XXXX:XXXX替换为您的远程服务器 IP 地址和端口号(例如100.200.100.200:3306),将username替换为允许远程访问的MySQL用户名(例如root)。按照(Follow)屏幕上的任何其他说明进行操作。
- 在出现提示时提供您的密码,以完成登录过程并远程访问您的MySQL数据库。(MySQL)
如果这不起作用,请使用这些步骤并使用-h localhost参数,使用SSH (或直接访问它)连接到托管(SSH)MySQL服务器的服务器或 PC。然后,您可以按照以下步骤创建合适的用户帐户。
允许远程用户访问 MySQL 数据库(Allowing Remote User Access to a MySQL Database)
至此,您应该能够使用服务器的 root 用户帐户或其他具有提升权限的用户帐户远程连接到MySQL服务器。(MySQL)由于这种访问级别是不安全的,您可能更愿意创建一个更受限制的帐户来访问您的MySQL数据库。
此帐户对您的MySQL服务器的访问权限有限,只允许它与选定的数据库进行交互。它将无法进行更严重的更改,例如访问其他数据库数据、创建新用户帐户等。
您需要能够远程登录您的MySQL服务器。(MySQL)如果您不能远程使用您的 root 帐户,您需要使用mysql命令通过远程SSH连接或直接访问托管服务器的 PC 或服务器来访问服务器的 shell。
- 在远程MySQL shell(使用mysql工具)中,输入CREATE USER “username”@”x.x.x.x” IDENTIFIED BY “password”; 并选择Enter。将username替换为您希望创建的用户名,将xxxx替换为您希望连接的 IP 地址,并将password替换为合适的密码。
- 您需要为新帐户授予必要的权限。为此,请键入GRANT ALL ON databasename.* TO username@”x.x.x.x”; 并将数据库名称、用户名(databasename, username, )和xxxx 替换为正确的详细信息。如果您愿意,请将databasename替换为*以授予其访问所有数据库的权限。
授予访问权限后,使用上一节中的步骤使用您的新帐户远程连接到您的服务器(例如mysql -u username -h XXXX:XXXX -p)。
保护您的数据库数据(Securing Your Database Data)
无论您使用的是MySQL还是其他类型的SQL数据库,确保连接安全以维护数据安全非常重要。实现此目的的一个好方法是生成 SSH 密钥以远程访问(generate SSH keys for remote access)您的服务器,而不是依赖过时的(且容易猜到的)密码。
如果您担心数据丢失,您可以轻松地在线备份您的数据库(back up your database)。大多数数据库都使用Linux服务器运行 — 您可以轻松地自动执行 Linux 文件备份(automate a Linux file backup easily)。如果你在Windows上运行(Windows)MySQL,你可以为 Windows 设置一个类似的自动备份系统(automatic backup system for Windows),让你可以在紧急情况下恢复你的数据。
How to Allow Remote Connections to MySQL
If you’re wоrking with a MySQL database, then you’re already aware of the challenges you face in keeping your database secure. From database hacking attempts using SQL injections to brute force attacks, it’s difficult to keep your data safe, especially if you’re working with a database remotely.
There are ways to configure an SQL server to allow remote connections, but you need to be careful, as allowing remote connections on a MySQL server can make your database an easy target for hackers. If you want to allow secure, remote connections to a MySQL database, here’s what you’ll need to know.
Before You Begin
Before you make any changes to your MySQL database, it’s important that you backup your database, especially if you’re working on a production server (a server in active use). Any changes you make to your database, or the server hosting it, could result in serious data loss if something goes wrong.
You may also find that changes to your server’s connections may prevent you from accessing it afterward. If this happens, you may need to consult with a server administrator for further support. A good idea is to trial any changes on a locally-run MySQL server to check if your changes work before trying them remotely.
It’s also likely that if you’re making changes to a remote server, you’ll need a safe way to connect and make changes. SSH (Secure Shell) is often the best way to do this, as it allows you to connect to your remote server. You can also use SSH to connect to servers on your local network, such as those hosted on a Raspberry Pi.
This guide will lead you through the steps to configuring MySQL to allow remote connections, but you’ll need to ensure you have direct or remote access to the server hosting your MySQL server first.
Suppose you don’t have remote access to your server via SSH (for example). In that case, you won’t be able to configure your MySQL database to allow remote connections directly unless your root mySQL account already allows remote connections. So, you will need to establish this connection first before you can proceed.
Editing Your MySQL Configuration File
The first step in configuring MySQL to allow remote connections is to edit your MySQL configuration file. By this stage, this guide will assume you have already connected to the server, PC, or Mac hosting your mySQL database remotely and have console access.
Alternatively, you can configure a local MySQL server using an open terminal on Mac or Linux or a text editor on Windows.
- To start, use your preferred console text editor to edit your MySQL database file. On Linux, type sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf into a terminal or SSH window to edit this file using the nano editor (assuming your MySQL database is in the default location).
- If you’re running Windows, open File Explorer and access the folder containing your MySQL installation (eg. C:/Program Files/MySQL/MySQL Server 8.0). Open the my.ini file using your default text editor (eg. Notepad) by double-clicking the entry. If it isn’t there, create the file first.
- On Mac, open a terminal window and type sudo nano /usr/local/etc/my.cnf. This is the default configuration file for MySQL if you’ve installed MySQL using homebrew.
The locations referenced above are the default locations for MySQL configuration files. If these commands don’t work, you’ll need to search for the relevant files (my.cnf, mysqld.cnf, or my.ini) manually to locate the relevant file path.
Setting a Safe Bind-Address IP Range
- Once you’ve opened the MySQL configuration file for your server, use your keyboard’s arrow key to reach the bind-address section of the file. This IP range limits the connections to your database, which is typically set to only allow connections from the local machine or server using 127.0.0.1.
- If you want to configure your MySQL database to allow connections from devices using your current internet connection, find your public IP address first, then replace 127.0.0.1 with that IP address. Alternatively, replace it with an IP address for the device or server you wish to allow connections from.
- In some circumstances, you may wish to allow all remote connections to a MySQL database. This carries extreme risk and shouldn’t be used on a production server. If you want to allow this, however, replace 127.0.0.1 with 0.0.0.0.
- Make note of the port value in the Basic Settings section. This will be required in the next section. If it isn’t visible, the default value will be used, which is port 3306. You can add your own port by typing port = xxxx on a new line, replacing xxxx with a suitable port value.
- Once you’ve configured the bind-address in your MySQL configuration file, save the file. If you’re on Linux, select Ctrl + O and Ctrl + X to do this. On Mac, select Command + O and Command + X. Windows users can save by selecting File > Save.
- Next, Linux and Mac users can restart MySQL by typing mysql.server stop && mysql.server start or mysql.server restart. You may need to elevate the command using sudo (eg. sudo mysql.server restart) and use the appropriate path to the mysql.server file (eg. /usr/local/bin/mysql.server).
- If the above command doesn’t work, try sudo service mysql restart instead.
- To restart MySQL on Windows, open a new PowerShell window by right-clicking the Start menu and selecting Windows PowerShell (Admin). In the PowerShell window, type net stop mysql80 followed by net start mysql80, replacing mysql80 with the correct service name on your PC.
If you’re unsure of the correct service name on Windows, type net start to find it. If you can’t reload your configuration, restart your server and reload MySQL manually (if necessary) instead.
Configuring Your Firewalls
At this stage, your MySQL database should allow remote connections from devices using the IP address you set as the bind-address value in your MySQL configuration file (or from all devices if you set this value to 0.0.0.0 instead). However, connections will still be blocked by your device or network firewall.
Most servers and PCs use a firewall to block connections unless access to a specific port is granted. The steps to configure this will vary, depending on whether you’re running MySQL on Windows or Linux. Mac firewalls are disabled by default, so you shouldn’t need to complete any additional steps here.
Configure Linux Firewalls
Many Linux servers use iptables as the default firewall utility. You can configure it by following the steps below.
- Open a terminal or SSH connection and type sudo iptables -A INPUT -p tcp -s X.X.X.X –dport YYYY -j ACCEPT. Replace X.X.X.X with the IP address for the device you wish to allow MySQL connections from, and replace YYYY with the matching port value from your MySQL configuration file (eg. 3306).
- This will configure the firewall temporarily. If you’re using a Debian or Ubuntu-based Linux server, make this change permanent by typing sudo netfilter-persistent save and sudo netfilter-persistent reload into the terminal or SSH window.
If iptables isn’t the default firewall tool for your Linux distribution, you’ll need to consult your distribution’s user manual for more information. If certain packages (such as netfilter-persistent) are unavailable, use your distribution’s software repository tool to install it (eg. sudo apt install netfilter-persistent).
Configure Windows Firewalls
If you’re using a Windows PC or server to host your database, you can configure your firewall using these steps:
- Right-click the Start menu and select Run.
- In the Run box, type wf.msc and select OK.
- In the Windows Defender window, select Inbound Rules > New Rule.
- In the New Inbound Rule Wizard window, select Port > Next.
- At the next menu, select TCP from the options, type 3306 (or whichever port value is listed in your MySQL configuration file), then select Next.
- At the Action menu, leave the default option to Allow the connection enabled, then select Next.
- Confirm that you want the rule to apply to all network types, then select Next.
- Type a descriptive name for the rule (eg. MySQL) in the port provided, then select Finish to add it to your list of firewall rules.
If you have trouble connecting, repeat these steps above, making sure to create a new outbound rule in your firewall settings using the same details (port 3306, etc). You may also need to configure your local network router to open the necessary blocked ports to allow inbound and outbound connections to your database.
Connecting to a Remote Server Using MySQL
After configuring your MySQL database to allow remote connections, you’ll need to actually establish a connection to it. You can do this using the mysql command (mysql.exe on Windows) from a terminal or PowerShell window.
If you’re running Windows, you will need to make sure that MySQL is installed locally before you begin. Mac users can install MySQL using homebrew from the terminal (brew install mysql), while Linux users can use their local app repository (eg. sudo apt install mysql) to install the necessary packages.
Connecting to MySQL on Linux or Mac
- To connect to your remote MySQL server on Mac or Linux, open a new terminal window and type mysql -u username -h X.X.X.X:XXXX -p. Replace X.X.X.X:XXXX with your remote server IP address and port number (eg. 100.200.100.200:3306) and username with your MySQL username.
- When prompted, confirm your password. If the connection is successful, a success message will appear in the terminal.
Connecting to MySQL on Windows
- To connect to a remote MySQL server on Windows, open a new PowerShell window by right-clicking the Start menu and selecting Windows PowerShell (Admin).
- In the new PowerShell window, type cd “C:\Program Files\MySQL\MySQL Workbench 8.0\” to enter the correct folder, replacing this directory with the correct installation directory on your PC. For example, if your version of MySQL is 8.0.1, use the MySQL Workbench 8.0.1 folder instead.
- From there, type .\mysql.exe -u username -h X.X.X.X:XXXX -p. Replace X.X.X.X:XXXX with your remote server IP address and port number (eg. 100.200.100.200:3306) and username with a MySQL username that allows remote access (such as root). Follow any additional on-screen instructions.
- Provide your password, when prompted, to complete the sign-in process and access your MySQL database remotely.
If this doesn’t work, connect to the server or PC hosting your MySQL server using SSH (or access it directly) using these steps and using the -h localhost argument. You can then create a suitable user account by following the steps below.
Allowing Remote User Access to a MySQL Database
By this point, you should be able to connect to your MySQL server remotely using your server’s root user account or another user account with elevated privileges. As this level of access is unsafe, you may prefer to create a more restricted account for accessing your MySQL database.
This account will have limited access to your MySQL server, allowing it to interact with only selected databases. It won’t be able to make more serious changes, such as accessing other database data, creating new user accounts, etc.
You’ll need to have the ability to sign into your MySQL server remotely. If you can’t use your root account remotely, you’ll need to access your server’s shell using the mysql command via a remote SSH connection or by directly accessing the PC or server hosting the server.
- In your remote MySQL shell (using the mysql tool), type CREATE USER “username”@”x.x.x.x” IDENTIFIED BY “password”; and select Enter. Replace username with the username you wish to create, x.x.x.x with the IP address you wish to connect from, and password with a suitable password.
- You’ll need to grant your new account with the necessary permissions. To do this, type GRANT ALL ON databasename.* TO username@”x.x.x.x”; and replace databasename, username, and x.x.x.x with the correct details. If you wish to, replace databasename with * to grant it access to all databases.
With access granted, use the steps in the section above to connect to your server remotely using your new account (eg. mysql -u username -h X.X.X.X:XXXX -p).
Securing Your Database Data
Whether you’re working with MySQL or another type of SQL database, it’s important to keep your connections secure to maintain your data security. A good way to do this is to generate SSH keys for remote access to your server, rather than relying on outdated (and easily guessable) passwords.
If you’re worried about data loss, you can easily back up your database online. Most databases are run using Linux servers—you can automate a Linux file backup easily. If you’re running MySQL on Windows, you can set up a similar automatic backup system for Windows, allowing you to restore your data in an emergency.