LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
Search this Thread
Old 12-28-2010, 02:11 PM   #1
simeon.mattes
Member
 
Registered: Feb 2009
Posts: 60

Rep: Reputation: 15
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:
  1. vi /etc/my.cnf
  2. line skip-networking is commented
  3. bind-address my_ipaddress
  4. 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?

Last edited by simeon.mattes; 12-28-2010 at 02:18 PM.
 
Old 12-28-2010, 02:17 PM   #2
AlucardZero
Senior Member
 
Registered: May 2006
Location: USA
Distribution: Debian
Posts: 4,599

Rep: Reputation: 516Reputation: 516Reputation: 516Reputation: 516Reputation: 516Reputation: 516
"Connection refused" usually means that nothing is listening on that address and port.

What does "lsof -i tcp:3306" return?
 
Old 12-28-2010, 02:28 PM   #3
simeon.mattes
Member
 
Registered: Feb 2009
Posts: 60

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by AlucardZero View Post
"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.

Last edited by simeon.mattes; 12-28-2010 at 02:41 PM.
 
Old 12-28-2010, 02:54 PM   #4
AlucardZero
Senior Member
 
Registered: May 2006
Location: USA
Distribution: Debian
Posts: 4,599

Rep: Reputation: 516Reputation: 516Reputation: 516Reputation: 516Reputation: 516Reputation: 516
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?
 
Old 12-28-2010, 03:12 PM   #5
bathory
Guru
 
Registered: Jun 2004
Location: Piraeus
Distribution: Slackware
Posts: 10,884

Rep: Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322
Check /etc/rc.d/rc.mysqld and comment out the line:
Quote:
SKIP="--skip-networking"
Then restart mysqld

Last edited by bathory; 12-28-2010 at 03:19 PM.
 
Old 12-28-2010, 03:22 PM   #6
simeon.mattes
Member
 
Registered: Feb 2009
Posts: 60

Original Poster
Rep: Reputation: 15
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

Last edited by simeon.mattes; 12-28-2010 at 03:26 PM.
 
Old 12-28-2010, 03:30 PM   #7
simeon.mattes
Member
 
Registered: Feb 2009
Posts: 60

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by bathory View Post
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.

Last edited by simeon.mattes; 12-28-2010 at 03:37 PM.
 
Old 12-28-2010, 03:40 PM   #8
bathory
Guru
 
Registered: Jun 2004
Location: Piraeus
Distribution: Slackware
Posts: 10,884

Rep: Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322
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
 
Old 12-28-2010, 03:47 PM   #9
simeon.mattes
Member
 
Registered: Feb 2009
Posts: 60

Original Poster
Rep: Reputation: 15
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

Last edited by simeon.mattes; 12-28-2010 at 03:50 PM.
 
Old 12-28-2010, 04:19 PM   #10
bathory
Guru
 
Registered: Jun 2004
Location: Piraeus
Distribution: Slackware
Posts: 10,884

Rep: Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322
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.
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

Last edited by bathory; 12-28-2010 at 04:42 PM.
 
Old 12-29-2010, 04:01 AM   #11
simeon.mattes
Member
 
Registered: Feb 2009
Posts: 60

Original Poster
Rep: Reputation: 15
Quote:
Originally Posted by bathory View Post
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

Last edited by simeon.mattes; 12-29-2010 at 04:04 AM.
 
Old 01-20-2011, 12:51 AM   #12
simeon.mattes
Member
 
Registered: Feb 2009
Posts: 60

Original Poster
Rep: Reputation: 15
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
or
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?
 
Old 01-20-2011, 01:59 AM   #13
bathory
Guru
 
Registered: Jun 2004
Location: Piraeus
Distribution: Slackware
Posts: 10,884

Rep: Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322
Hi,

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

Regards
 
Old 01-21-2011, 02:15 AM   #14
simeon.mattes
Member
 
Registered: Feb 2009
Posts: 60

Original Poster
Rep: Reputation: 15
I suppose you mean the following post

Quote:
Originally Posted by bathory View Post
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?

Last edited by simeon.mattes; 01-21-2011 at 02:16 AM.
 
Old 01-21-2011, 02:58 AM   #15
bathory
Guru
 
Registered: Jun 2004
Location: Piraeus
Distribution: Slackware
Posts: 10,884

Rep: Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322Reputation: 1322
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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
access to remote mysql server abd_bela Debian 1 06-03-2009 11:02 AM
can access to mysql from remote,help!!! ajaxer Debian 2 06-30-2008 03:52 AM
Mysql remote access dereko Linux - Server 10 04-22-2008 07:46 PM
Remote MySQL access gauge73 Linux - Software 2 03-05-2006 08:24 AM
MySQL 4.1 remote access james.reid Linux - Software 0 01-03-2006 05:55 AM


All times are GMT -5. The time now is 05:17 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration