Lost connection to MySQL server at 'reading initial communication packet

Question

I want to establish a connection between my local machine and MySQL database server via Python.

Can someone tell me how to "bind-address with localhost"?

Answer 1

Can someone tell me how to "bind-address with localhost"?

This is a MySQL configuration directive.

Edit the global my.ini file, in the [mysqld] section:

[mysqld]
# -- various other settings
port = 3306
bind-address = 127.0.0.1
# -- other settings

Save this file, and then restart your server.

Edit

If you want to connect to your local Windows instance of MySQL, simply use 127.0.0.1 as the server's address.

If you want to connect to your remote server, the one running Linux then it is a bit complicated:

1. First, make sure MySQL is listening on the public IP of the Linux server. Change the line bind-address = and set it to the public IP of your server.

2. Make sure port 3306 isn't blocked by any firewall.

3. The user that you use to connect to the server needs to have rights to connect from a remote IP. By default, users are only given rights to connect from localhost - in other words they can only connect if the program is running on the same machine as the server itself.

To grant a user access from a remote IP, run this command from the mysql> shell when logged in with the MySQL root user:

GRANT ALL on somedb.* to someuser@8.8.8.8 identified by 'somepassword';

If you want to grant access to someuser from any remote IP:

GRANT ALL on somedb.* to someuser@% identified by 'somepassword';

After those steps, make sure to restart the MySQL server so it will read the changes in the configuration.