LinuxQuestions.org - mysql remote access failed

- Linux - Server (https://www.linuxquestions.org/questions/linux-server-73/)

- - mysql remote access failed (https://www.linuxquestions.org/questions/linux-server-73/mysql-remote-access-failed-852874/)

mysql remote access failed

Hi,

I'm using slackware 13.1 and I have a problem to accept remote access to mysql server.

The steps I have followed to do that thing are:

vi /etc/my.cnf
line skip-networking is commented
bind-address my_ipaddress
I opened the port 3306 of my firewall

Although I can connect locally, i.e. mysql -u a_user -h localhost -p
I can't do it remotely i.e. mysql -u same_user -h my_ipaddress -p

I have also turned off my firewall, but nothing.

telent 3306 my_ipaddress results in: telnet: connect to address my_ipaddress: Connection refused

My my.cnf file is:

Code:

[client]

port                = 3306

socket                = /var/run/mysql/mysql.sock



[mysqld]

port = 3306

socket = /var/run/mysql/mysql.sock

skip-locking

key_buffer_size = 16M

max_allowed_packet = 1M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M



log-bin=mysql-bin

binlog_format=mixed





server-id        = 1

big-tables

bind-address = my_ipaddress



[mysqldump]

quick

max_allowed_packet = 16M



[mysql]

no-auto-rehash





[myisamchk]

key_buffer_size = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M



[mysqlhotcopy]

interactive-timeout

Any ideas?

"Connection refused" usually means that nothing is listening on that address and port.

What does "lsof -i tcp:3306" return?

Quote:

Originally Posted by AlucardZero (Post 4205764)

"Connection refused" usually means that nothing is listening on that address and port.

What does "lsof -i tcp:3306" return?

Nothing!!! Hm...Isn't it peculiar? It should indeed return something. How is that possible? Locally I can login, so I suppose that the mysql server listens to that port, but with telnet, and lsof seems that it doesn't listen.

Access with "mysql -u a_user -h localhost -p" likely goes through the Unix socket rather than over TCP.

Dumb question but have you restarted mysql since modifying my.cnf?

Check /etc/rc.d/rc.mysqld and comment out the line:

Quote:

SKIP="--skip-networking"

Then restart mysqld

Ok...I think I found something.

Till now I was starting, restarting the mysql server through /etc/rc.d/rc.mysqld restart. I remembered that while it was starting mysql logged all the messages to a file /var/lib/mysql/my_domain.err

And as you can imagine I found errors. I stopped the mysql server and I restarted it through mysqld_safe which is better. And I found the following in my_domain.err file:

Code:

101228 23:06:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

101228 23:06:46  InnoDB: Started; log sequence number 0 12814705

101228 23:06:46 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address

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

101228 23:06:46 [ERROR] Aborting



101228 23:06:46  InnoDB: Starting shutdown...

101228 23:06:51  InnoDB: Shutdown completed; log sequence number 0 12814705

101228 23:06:51 [Note] /usr/libexec/mysqld: Shutdown complete



101228 23:06:51 mysqld_safe mysqld from pid file /var/lib/mysql/weblab.pid ended

So I removed the line bind-address = my_ipaddress and I run again mysqld_safe. Now mydomain.err file contains:

Code:

101228 23:02:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

101228 23:02:38  InnoDB: Started; log sequence number 0 12814705

101228 23:02:38 [Note] Event Scheduler: Loaded 0 events

101228 23:02:38 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.1.46-log'  socket: '/var/run/mysql/mysql.sock'  port: 3306  Source distribution

After that both telnet localhost 3306 and lsof -i tcp:3306 listen to the port 3306. So there is something wrong with bind my_ipaddress

Quote:

Originally Posted by bathory (Post 4205815)

Check /etc/rc.d/rc.mysqld and comment out the line:

Then restart mysqld

Nop...Nothing the same errors as the ones I mentioned exactly before. Now I can't even start the mysql server with /etc/rc.d/rc.mysqld start

I don't know if it will help but I have MySQL version 5.1.46.

What gives

Code:

ps -ef|grep mysql

You should restore the original mysqld_safe to its original and tweak options through /etc/rc.d/rc.mysqld

Ok...I found the solution.

I had to write:

bind-address = 0.0.0.0

It has to be a feature of mysql 5.1x.

Thanks for your help, I wouldn't have managed it without you.

However 0.0.0.0 means that it binds with any address...I hope there isn't any security issue with that

Quote:

However 0.0.0.0 means that it binds with any address...I hope there isn't any security issue with that

It could be a security issue, as it's for every other service that accepts connections on a port. Specially if your server has a public IP. Of course you can use a firewall to protect the server from the internet.
Also make sure you specify who and from where can connect to your server (like user@localhost) and always use strong passwords for these users.

Regards

Quote:

Originally Posted by bathory (Post 4205869)

Nope it an option used also in previous mysql versions. Note that bind-address is used if you want to specify just one interface you want mysqld to listen on. Assigning the 0.0.0.0 address in bind-address is the same as commenting out skip-networking.

It could be a security issue, as it's for every other service that accepts connections on a port. Specially if your server has a public IP. Of course you can use a firewall to protect the server from the internet.
Also make sure you specify who and from where can connect to your server (like user@localhost) and always use strong passwords for these users.

Regards

What I meant was that for some reasons mysql doesn't bind to a specific ip address and it needs to bind to all, i.e. bind-address=my_ipaddress doesn't work but bind-address = 0.0.0.0 works indeed.

So I suppose that mysql 5.1x accepts only 0.0.0.0. However, it seems strange, since I could configure it like that in mysql 5.0x

Hi again,

I just find out another issue. Every time I restart my computer the remote access stops working. Although I'm able to connect locally, i.e. through the command

Code:

mysql -u {mysql-user} -h localhost -p

the mysql daemon doesn't seem to listen on remote requests, i.e.

Code:

telnet localhost 3306

Code:

telnet my_ipaddress 3306

returns

Code:

Trying 127.0.0.1...

telnet: connect to address 127.0.0.1: Connection refused

And for

Code:

lsof -i tcp:3306

I take nothing.

In order to correct that I do the following:

Code:

/etc/rc.d/rc.mysqld stop; mysqld_safe &

and everything starts working normally. How could I solve the issue with the reboot?

Hi,

You should have a look again at my post #8 above.

Regards

I suppose you mean the following post

Quote:

Originally Posted by bathory (Post 4205843)

What gives

Code:

ps -ef|grep mysql

You should restore the original mysqld_safe to its original and tweak options through /etc/rc.d/rc.mysqld

Hm...Could you give me some more details?

Just undo the changes you made in mysqld_safe and/or my.cnf
Then edit /etc/rc.d/rc.mysqld, comment out the line "SKIP=--skip-networking" and try to start/stop the server from the init script