How do I open up my MySQL on my Raspberry Pi for Outside / Remote Connections?

Question

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.

mysql --192.168.1.102 --u root -p db

Causes a "Can't connect to MySQL server on '192.168.1.102'".

My Java application isn't connecting either, obviously.

SHOW VARIABLES WHERE VARIABLE_NAME = 'port' ;

returns the default port, 3306.

Is there a setting that must be enabled to allow remote connections into MySQL?

Answer 1

I have recently had the same problem myself. I got it working by doing the following:

Edit MySQL configuration

By default, MySQL is not configured to accept remote connections. You can enable remote connections by modifying the configuration file:

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.

Restart the MySQL service

sudo service mysql restart

Setup MySQL permissions

Connect to your MySQL instance as root:

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

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!