OperationalError: (OperationalError) (2003, "Can't connect to MySQL server on '192.168.129.139' (111)") None None

Question

I am trying to create a remote database using mysql on an Ubuntu machine running 12.04.

It has a root user with remote login enabled and no password.I have started the server.

output of

sudo netstat -tap | grep mysql

shows

tcp        0      0 localhost:mysql         *:*                     LISTEN      13246/mysqld

I have created a database called nwtopology using (as mentioned root doesn't have a password yet.)

 create database nwtopology
 grant all privileges on *.* to root@192.168.129.221
 FLUSH PRIVILEGES;

From the client machine that also runs Ubuntu 12.04 I use a python script to connect to the remote mysql database using sqlalchemy.

from pox.core import core
import pox.openflow.libopenflow_01 as of
import re
import datetime
import time
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql.expression import exists

log = core.getLogger()
engine = create_engine('mysql://root@192.168.129.139/nwtopology', echo=False)
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

class SourcetoPort(Base):
    """"""
    __tablename__ = 'source_to_port'
    id = Column(Integer, primary_key=True)
    port_no        = Column(Integer)
    src_address    = Column(String,index=True)

    #-----------------------------------------
    def __init__(self, src_address,port_no):
        """"""
        self.src_address = src_address
        self.port_no     = port_no

The create_engine() call is failing with the following error.

POX 0.1.0 (betta) / Copyright 2011-2013 James McCauley, et al.
Traceback (most recent call last):
  File "/home/karthik/ms_thesis/pox/pox/boot.py", line 89, in do_import2
    __import__(name, globals(), locals())
  File "/home/karthik/ms_thesis/pox/custom/tutorial.py", line 39, in <module>
    Base.metadata.create_all(engine)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 2515, in create_all
    tables=tables)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2230, in _run_visitor
    conn = self.contextual_connect(close_with_result=False)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2340, in contextual_connect
    self.pool.connect(),
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 210, in connect
    return _ConnectionFairy(self).checkout()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 371, in __init__
    rec = self._connection_record = pool._do_get()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 697, in _do_get
    con = self._create_connection()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 174, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 256, in __init__
    self.connection = self.__connect()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 316, in __connect
    connection = self.__pool._creator()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line 80, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 280, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 187, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
OperationalError: (OperationalError) (2003, "Can't connect to MySQL server on '192.168.129.139' (111)") None None

I cannot figure out why this is happening?Any help is greatly appreciated?

The error is telling you that there is no MySQL server listening on the `mysql` port on host 192.168.129.139. In fact, there's probably nothing at all listening on that port. (You can verify that with, e.g., `nc 192.168.129.139 3306` at the shell, or`socket.create_connection` in Python.) — abarnert, May 09 '13 at 22:32
If you're asking _why_ no server is listening there… that's not a Python question, or a programming question; you've configured something wrong, and you probably want to go to http://serverfault.com for help with that. — abarnert, May 09 '13 at 22:32
Meanwhile, three obvious possibilities: (1) 192.168.129.139 isn't the address of the machine running MySQL. (2) MySQL isn't running on the same port you're trying to connect to. I believe the default port is 3306 (and my `/etc/services` agrees…), but you can configure it to run on any port, and you can pass any port to the connect call. (3) The MySQL service is listening on the wrong interface—e.g., it's listening on localhost and you're trying to connect over eth0, or vice-versa. — abarnert, May 09 '13 at 22:35

score 14 · Accepted Answer · edited Jan 09 '22 at 22:30

14

Looks like the mysql server is configured to listen only on localhost.

You can test this by running telnet 192.168.129.139 3306 from your client machine.

Most probable reason - mysqld (=MySQL daemon) is configured to do so.

Please try to follow Configuration step described here:

Edit the /etc/mysql/my.cnf file to configure MySQL to listen for connections from network hosts, change the bind-address directive to the server's IP address. For example, in your case, replace 192.168.0.5 with 192.168.129.139.

From:

bind-address            = 192.168.0.5

to:

bind-address            = 192.168.129.139

If there is no such entry and you cannot connect, create a new line. You may also try commenting out the line instead.

After making a change to /etc/mysql/my.cnf the MySQL daemon will need to be restarted:
sudo systemctl restart mysql.service

Then test with telnet or by running your application once again. Also netstat would have second entry for mysqld.

edited Jan 09 '22 at 22:30

questionto42

7,175
4
57
90

answered May 09 '13 at 23:24

vvladymyrov

5,715
2
32
50

+1 Thanks for a clear outline. If someone does this and still cannot connect (and is running Ubuntu) then check your firewall settings and allow 3306 as a port. – Jeel Shah Oct 04 '16 at 23:48
I also had to create a new user with sufficient privileges that could connect from the outside (see [here](http://stackoverflow.com/questions/1559955/host-xxx-xx-xxx-xxx-is-not-allowed-to-connect-to-this-mysql-server)). – MBR May 11 '17 at 08:41
I followed what is given in the answer but still could not get it to work. I then figured out that the "bind-address" parameter is in the "/etc/mysql/mysql.conf.d/mysqld.cnf" file. Commented it out over there and it worked! – Yahya Aug 17 '17 at 07:18
Link is dead by now. – Stefan Falk Feb 01 '19 at 10:35
@StefanFalk Yes, you need to replace the old ubuntu number by a newer one like 18, now edited. In future, that will have to be done again, just search for a newer version if it is dead again. – questionto42 Jan 15 '22 at 15:36

OperationalError: (OperationalError) (2003, "Can't connect to MySQL server on '192.168.129.139' (111)") None None

1 Answers1

Linked

Related