This almost drove me nuts into breaking apart after trying so many times. And finally I got it working.
Lot of tutorials on the internet about uploading data to mysql server from arduino exists, but no one is talking about making sure how to make mysql server open to remote connection.
Lot of tutorials on the internet about uploading data to mysql server from arduino exists, but no one is talking about making sure how to make mysql server open to remote connection.
Follow the simple steps before you try your sketch to upload to arduino. I have used Arduino Mega and Ethernet Shield mounted ontop of it, shown in above picture.
- Ensure your MySQL server accepts remote connections. By default it may proabbly have an IP address 127.0.0.1 bind to the 3306 port . This won't let your Arduino Ethernet shield connect to mysql server running on a different machine. In my case, I have mysql server running on raspberry pi 3, and it has IP 192.168.1.145. So for arduino to push directly to databse on remote mysql server, you should have mysql server IP 192.168.145 bind to 3306 port of mysql server running on it. So that MySQL server is visible to remote client on local network on which your arduino Ethernet shield is connected to via LAN.
- Then you login to mysql server , and create a user :
CREATE USER 'myuser'@'192.168.156' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';
then you grant privileges as shown below.
GRANT ALL ON *.* TO 'myuser'@'192.168.156';
GRANT ALL ON *.* TO 'myuser'@'%';
Important Note: the ip address shown in above mysql queries 192.168.156 is the ip address of my ethernet client/server establishing connection with database on mysql remote server.
Thats it. This will surely work. Now to test it Have a look at the below Arduino Sketch I used for testing.
I hope I saved your day :-)
