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.
- 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';
GRANT ALL ON *.* TO 'myuser'@'192.168.156';
GRANT ALL ON *.* TO 'myuser'@'%';