MySQL server remote connection?

Question

I am trying to setup MySql 5.5 server for remote access from Ubuntu host 192.168.1.139, so I added line: bind-address=192.168.1.139 to the server's config file: /etc/mysql/my.cf When i tried to restart mysqld, i've got the following error:

[Note] Server hostname (bind-address): '192.168.1.139'; port: 3306
[Note]   - '192.168.1.139' resolves to '192.168.1.139';
[Note] Server socket created on IP: '192.168.1.139'.
[ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address
[ERROR] Do you already have another mysqld server running on port: 3306 ?

Can anyone help with the problem cause? And how can i open MySQL server for connection from any host/user , e.g. what should be the syntax for that in my.cf?

Answer 1

bind-address is for your local IP addresses or network interfaces. You cannot bind to a remote address. You'll need to set up rules in a firewall in order to do such limitations. Run ifconfig to see what are your network interfaces. Use

bind-address = 0.0.0.0

to allow connections to any interface. I recommend you this post https://serverfault.com/a/139326

Answer 2

I think this message

[ERROR] Do you already have another mysqld server running on port: 3306 ?

can be pretty misleading as this can also be related to the binding address and not the port itsel.

In my case, the database failed to start on system init because I was binding to the 10.0.0.11 IP address, whose interface wasn't ready on startup. To solve it (on MariaDB/Fedora 24):

mkdir /lib/systemd/system/mariadb.service.d
echo "[Unit]
After=network-online.target
Wants=network-online.target
" > /lib/systemd/system/mariadb.service.d/waitForNetwork.conf