CODEWITHSUNDEEP
mysqlmysql-workbenchmysql-python
Joan Arau
Joan Arau

Reputation: 151

Remote mysql connection works in workbench but not in python

I setup a mysql server (using mariadb) and database on my raspi and am now trying to connect to it from my MacBook.

Version: mysql Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2

The connection works in Workbench(on the Mac)but I can't get it to connect in python or command line

the /etc/my.cnf on the raspi looks like this:

bind-address = 0.0.0.0

[mysqld]

I have added a superuser with all permissions and am trying to connect on my Mac command line with this:

mysql -u {superuser} -p {superuser} -h '192.168.x.x' -P '3307' -D {securities_master}

Which doesn’t work.

In python I have tried different variations of this:

host = '192.168.x.x'
user='superuser'
password='superuser'
database='securities_master'

import mysql.connector
cnx = mysql.connector.connect(user=user, password=password, host=host, port='3307')
print(cnx.get_server_info())

command line returns this: ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 3

python script returns this: pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '192.168.x.x' ([Errno 61] Connection refused)")

Workbench connection is solid

Upvotes: 2

Views: 2723

Answers (2)

NegaOverflow
NegaOverflow

Reputation: 138

I have just same issue solved like that:

  1. check if your mysql has granted "root" or "youruserID" the right to access from localhost(127.0.0.1) and LAN(192.168.x.x) the DB privileges? For this just ssh into remote server and:

     $ mysql -u root -p
 mysql> SHOW GRANTS

    For me it shows:

     Grants for root@localhost 
----------
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD ‘*———‘ WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                             |

    which means it has been granted privilege from anywhere(@'%') incl. localhost(127.0.0.1)

  2. python access remotely you need to install mysql connector:

     $ pip install mysql-connector-python

  3. within Python3 you can try this (if mariaDB the port is 3307, default mysql is 3306):

     >>> import mysql.connector
 >>> cnx =mysql.connector.connect(user='root', password='xxx', host='yourraspyi_sql_ip', port='3307')
 >>> print(cnx.get_server_info())

Upvotes: 1

Sadi
Sadi

Reputation: 2416

You mentioned that mysql -u {superuser} -p {superuser} -h '192.168.x.x' -P '22' -D {securities_master} Works properly.

Here, the mysql connection port is 22. If that really works, then 

host = '192.168.x.x'
user='superuser'
password='superuser'
database='securities_master'

import pymysql.cursors
connection = pymysql.connect(host=host,
                             port=3306,
                             user=user,
                             password=password,
                             db=database,
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

should be 

host = '192.168.x.x'
user='superuser'
password='superuser'
database='securities_master'

import pymysql.cursors
connection = pymysql.connect(host=host,
                             port=22,
                             user=user,
                             password=password,
                             db=database,
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

The port number has changed to 3306 to 22.

you can check it with

pymysql.connect(db='securities_master', user='superuser', passwd='superuser', host='192.168.x.x', port=22)

Upvotes: 0

Related Questions