如何在 Raspberry Pi 上打开 MySQL 以进行外部/远程连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18733802/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I open up my MySQL on my Raspberry Pi for Outside / Remote Connections?
提问by Matthew Moisen
I have a Raspberry Pi that stores temperature data for homebrewing activity. I am making a Spring MVC application on my computer and I want to tap the data. Both my Pi and my computer are on the local network. I can SSH and FTP into my RPi perfectly.
我有一个 Raspberry Pi，用于存储家庭酿造活动的温度数据。我正在我的电脑上制作一个 Spring MVC 应用程序，我想点击数据。我的 Pi 和我的电脑都在本地网络上。我可以完美地通过 SSH 和 FTP 连接到我的 RPi。
mysql --192.168.1.102 --u root -p db
Causes a "Can't connect to MySQL server on '192.168.1.102'".
导致“无法连接到 '192.168.1.102' 上的 MySQL 服务器”。
My Java application isn't connecting either, obviously.
显然，我的 Java 应用程序也没有连接。
SHOW VARIABLES WHERE VARIABLE_NAME = 'port' ;
returns the default port, 3306.
返回默认端口 3306。
Is there a setting that must be enabled to allow remote connections into MySQL?
是否必须启用允许远程连接到 MySQL 的设置？
回答by Luke Zaparaniuk
I have recently had the same problem myself. I got it working by doing the following:
我最近自己也遇到了同样的问题。我通过执行以下操作使其工作：
Edit MySQL configuration
编辑 MySQL 配置
By default, MySQL is not configured to accept remote connections. You can enable remote connections by modifying the configuration file:
默认情况下，MySQL 未配置为接受远程连接。您可以通过修改配置文件来启用远程连接：
sudo nano /etc/mysql/my.cnf
Find the [mysqld]section. The line you need to alter is bind-address, which should be set to the default value of 127.0.0.1. You want to edit this line to instead show the IP of your RPi on the network (which would seem to be 192.168.1.102 from your example). Write the changes.
找到该[mysqld]部分。您需要更改的行是bind-address，应将其设置为默认值127.0.0.1。您想编辑此行以在网络上显示您的 RPi 的 IP（从您的示例来看，它似乎是 192.168.1.102）。写下变化。
Restart the MySQL service
重启 MySQL 服务
sudo service mysql restart
Setup MySQL permissions
设置 MySQL 权限
Connect to your MySQL instance as root:
以 root 身份连接到您的 MySQL 实例：
mysql -p -u root
Create a user:
创建用户：
CREATE USER '<username>'@'<ip_address>' IDENTIFIED BY '<password>';
- The apostrophes ( ' ) in the syntax are required
- The IP address is the IP address of the device on the network you are trying to connect from
- 语法中的撇号 ( ' ) 是必需的
- IP 地址是您尝试连接的网络上设备的 IP 地址
Grant permissions to the relevant databases and tables:
授予对相关数据库和表的权限：
GRANT ALL PRIVILEGES ON <database>.* TO '<username>'@'<ip_address>' IDENTIFIED BY '<password>';
- The parameters are those you used to create your user in the previous step
- The * will grant access to all tables within the specified database. Alternatively you could specify a specific table
- You'd probably want to firm up the security by only granting relevant privileges, but that should be enough to test that it works
- 参数是您在上一步中用于创建用户的参数
- * 将授予对指定数据库中所有表的访问权限。或者，您可以指定一个特定的表
- 您可能只想通过授予相关权限来加强安全性，但这应该足以测试它是否有效
That should hopefully do it!
那应该有希望做到！
回答by SausageFingers
The following worked for me, courtesy of a comment found on thisinstructable:
以下内容对我有用，感谢在这个教学中发现的评论：
- Grant access to your remote machine using:
GRANT ALL ON *.* TO 'root'@'192.168.1.%' IDENTIFIED BY 'your_password_here';(I used 192.168.1.% so that any computer on my network can connect to it) - Go into the my.cnf file (
sudo nano /etc/mysql/my.cnf) file and look for "bind-address" and comment this out (put a # in front of the line) - Reload MySQL config (
service mysql reload) - Restart MySQL server (
service mysql restart)
- 授予对远程计算机的访问权限：（
GRANT ALL ON *.* TO 'root'@'192.168.1.%' IDENTIFIED BY 'your_password_here';我使用了 192.168.1.% 以便我网络上的任何计算机都可以连接到它） - 进入 my.cnf 文件 (
sudo nano /etc/mysql/my.cnf) 文件并查找“bind-address”并将其注释掉（在该行前面放一个 #） - 重新加载 MySQL 配置 (
service mysql reload) - 重启 MySQL 服务器 (
service mysql restart)
回答by rinoy
If your issue is not able to remotely connect with MySQL on Raspberry Pi, then try below steps. I had the same issue and got it resolved by performing below commands.
如果您的问题无法在 Raspberry Pi 上远程连接 MySQL，请尝试以下步骤。我遇到了同样的问题，并通过执行以下命令解决了它。
1) sudo nano /etc/mysql/my.cnf
1) sudo nano /etc/mysql/my.cnf
2) # bind-address = 127.0.0.1// comment this line out
2) # bind-address = 127.0.0.1// 注释掉这一行
bind-address = 0.0.0.0 //add this line just below above line
3) sudo /etc/init.d/mysql restart//restart mysql
3) sudo /etc/init.d/mysql restart//重启mysql
4) sudo mysql -u root -p//login to mysql cli as user 'root'
4) sudo mysql -u root -p//以用户'root'登录mysql cli
5) GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'beta' WITH GRANT OPTION;
5) GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'beta' WITH GRANT OPTION;
Here 'root' is the mysql user and 'beta' is the password and here privileges are set for 'root'.Change it accordingly and execute above query in mysql cli.
这里'root'是mysql用户，'beta'是密码，这里为'root'设置了权限。相应地更改它并在mysql cli中执行上述查询。
Good Luck !!!
祝你好运 ！！！
