0

I am running 2 Raspberry Pi3 (STRETCH, JESSIE) and a web server (PHP5, MySQL, PHPMyadmin, Apache2) on stretch. I have everything working as expected. However, I need to be able to connect to my MySQL from a remote machine to store data, but I can't connect at all. I have gone UP and DOWN the Internet looking for answers and solution and have tried them all. Still, I can't connect to the MySQL Database. I did the following after reading through Raspberry Pi forum.

Logged into mysql and executed the following commands

CREATE USER 'myusername'@'%' IDENTIFIED BY 'mypassword';

GRANT ALL PRIVILEGES ON *.* TO 'myusername'@'%' WITH GRANT OPTION;

Then, I logged out of mysql and I modified my.cnf file as follows; Commented out the following line in that file.

sudo nano /etc/mysql/my.cnf and commented out #bind-address = 0.0.0.0

My remote machine still can't connect to MySQL database.

However, I did notice something. On the pi that is running the webserver, my program running on it can connect using the ip address of the machine instead of localhost after doing the above steps before it would only connect using localhost. So I naturally assumed that now my program should be able to connect to mysql database from any other devices on the network, but it is not the case.

So, what am I doing wrong.

Improve this question
  • Why is it being closed? Is there something in the description I need to change? Can you point me to a question that is very similar with a working answer. Thank you. – ThN Jun 8 '20 at 20:56
  • I voted to close your question because it is not specific to the Raspberry Pi. IIRC without the bind_address directive MySQL won't listen for external connections. – Steve Robillard Jun 8 '20 at 21:23
  • Why aren't you running RaspiOS Buster with Mariadb 10.3 and PHP7.3? You MUST update the bind-address in your config from 127.0.0.1 or localhost to 0.0.0.0 so that any machine in your LAN can connect to the database? It looks like you've got that piece wrong. – Dougie Jun 8 '20 at 22:31
  • @Dougie yes... for the last two days I've been trying any and/or all solutions I've come across on Raspberry Pi forums, the Internet, blogs, etc, etc, etc... Nothing seems to work. I have changed bind-address to 0.0.0.0 and tried to connect and it also failed. On one of the forum post, someone said completely COMMENT out the bind-address and it should work. As you can see... I am not able to connect. – ThN Jun 9 '20 at 0:47
  • Run sudo netstat -tlnpu | grep mysql to see what address it's listening on you should get 0.0.0.0:3306. Then you just need to create a remote user as per stackoverflow.com/a/16288118/3491475 – Dougie Jun 9 '20 at 11:29
1

First thing to check is that your MySQL (or more likely MariaDB) server is listening on the INADDR_ANY (0.0.0.0) address. That needs a change to bind-address 0.0.0.0 in your config file.

Then use sudo netstat -tlnpu | grep mysql to check that the mysqld task is running and listening OK.

Next create a remote user as per the instructions at MySQL adding user for remote access

Finally the remote user needs a copy of a mysql-client or a remote connector like MariaDB Java Connector

Something like SquirrelSQL is a Java client (runs anywhere even on a Raspberry) that gives you a SQL dashboard (can create database, create tables and run simple or complex SQL with that). Download that from Squirrel SQL download

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.