I am trying to access a MySQL database stored on a windows machine remotely from a raspberry pi. The version of MySQL I am using is Server version: 8.0.19 MySQL Community Server - GPL. I have set up a user on the windows machine called 'RaspberryPi' and gave it access to the database 'database1'.
CREATE USER RaspberryPi@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database1.* TO RaspberryPi@'%';
I am using the python connector to interact with the database https://dev.mysql.com/doc/connector-python/en/
When running python scripts on the local windows machine it works fine however when trying to implement a remote connection it causes errors. The code I am running on the raspberry pi is shown below.
#!/usr/bin/python3.7
import mysql.connector
from mysql.connector import MySQLConnection, Error
def connect():
conn = None
try:
conn = mysql.connector.connect(
host=<IP_ADDRESS_OF_WINDOWS_MACHINE>,
database='database1',
user='RaspberryPi',
password='password',
port=3309
)
if conn.is_connected():
print('Connected to MySQL database')
except Error as e:
print(e)
finally:
if conn is not None and conn.is_connected():
print('Connection closed')
conn.close()
if __name__ =='__main__':
connect()
The result of the above code is:
2003: Can't connect to MySQL server on '<IP_ADDRESS_OF_WINDOWS_MACHINE>:3309' (110 Connection timed out)
I have read on other questions posted about changing the 'bind-address' How to allow remote connection to mysql however in my configuration file located under C:\ProgramData\MySQL\MySQL Server 8.0\my.ini there is no mention of a bind-address in this file.
I have also tried to connect to the database using the workbench on the windows machine but instead of giving it the default 127.0.0.1 hostname I gave it the IP_ADDRESS_OF_WINDOWS_MACHINE and it connected fine.
EDIT
Problem was on port number should be 3306 as mentioned by https://stackoverflow.com/users/6296759/nacho in comments. I am still unable to find the bind-address however in the configuration files for MySQL.
