Python MYSQL will not connect-CodePudding

I have been helping troubleshoot a process on an old UBUNTU 16 server. We cannot migrate a site to a new server until we understand how it works as we need to recreate and troubleshoot.

Basically its a python3 script that connects to a MYSQL database and reads some data, and updates data under a certain condition. It is basically linked to ZOHO hence the need for this programming language.

The error has been diagonsed as a mysql connection error. Error 111 connection refused. The database is localhost

I have 100% verified the access for the username, password and database visibiity and that port 3306 is open. I can login using a MYSQL command on the CLU and show the database and its tables.

The connector is 100% pulling in the correct variables from the config.py as I have added a print(HOST, DBASE, USER, PASSWORD) just before the connector and verified they are correct.

I have also tried 127.0.0.1 instead of localhost. I have also (dont shoot me) put in the root username and password to remove any permissions issues

I am wondering if its possible to expand on the mysql error in python so I can get a very clear message as to WHAT part is causing an issue. Is there any command in MYSQL that could have been issued to specifically block python.

I have disabled UFW just in case, made no difference, and the general logs for MYSQL dont appear to contain anything useful

Exact error:

mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)

Note: The server is Ubuntu 16, running MYSQL 5.7.26-0ubuntu0.16.04.1 with localhost access only. Python is 2.7.12

As noted this is an old server, that I want to scrap ASAP but until I can prove that the zoho connector is fixable, we cant proceed as its a blocker.

CodePudding user response：

As you can see in theexample below.

the port is separate from the host

import mysql.connector

cnx = mysql.connector.connect(
    host='localhost',
    database='import_test',
    user='testuser',
    password='password***',
    port=3306
)

CodePudding user response：

Turns out to be the most basic of all issues. On the old server we cloned from, everyting was open, zero lockdown including MYSQL listening to the world. On the new server, mysql had skip-networking was enabled.

Whereas the web instances were happily connecting directly to the socket, mysql was not activly listening on port 3306. I had changed my.cnf to enable networking but a different issue was stopping those changes taking place.

Once I had mysql working on port 3306 and I could see it listening, python now connected ok so some of the answers were correct. MYSQL was 'not working;' in a global sense.. HOWEVER this is a step backwards in terms of security. I did not want MYSQL open to the world

So I modified the code to use the unix socket of /var/run/mysqld/mysqld.sock, renenabled skip-networking and voila.. all is now fine with python, port 3306 is shutdown again and I have my first trial by fire of python complete

My config file defines DATABASE as local host, USER and PASSWORD but now has SOCKET as /var/run/mysqld/mysqld.sock and my code is

import mysql.connector

from models import ExportRecord

from config import HOST, USER, PASSWORD, DATABASE, SOCKET

def connect():
    return mysql.connector.connect(host=HOST, user=USER, passwd=PASSWORD, database=DATABASE, unix_socket=SOCKET)