[SOLVED] mysql connect to server at 'localhost' failed

Horus1 · 07-12-2013, 08:09 AM

Hello,
I've VPS with Debian 6 and imscp control panel ,
after uploaded my WordPress site and DB,
got this Error establishing a database connection
i did Google it to fix the error and following this guide [[ http://www.debianhelp.co.uk/mysql.htm]]To give localhost permission to access all databases
got this error
mysql: connect to server at 'localhost' failed

please,how can i fix this issues with my sql connections

zhjim · 07-12-2013, 08:46 AM

Severel things to check:

First if the server is running

Code:

ps aux | grep -i mysql

Next on which port

Code:

netstat -tulpn | grep -i mysql

If running and on default port 3306 lets try to connect

Code:

mysql -u root -p

as the user root and have us prompt for the password.

If that still gives the error try with the host option for mysql

Code:

mysql -h 127.0.0.1 -u root -p

or

Code:

mysql -h localhost -u root -p

or some other ip you have mysql running on. See the output of netstat.

If that all goes to now avail try to restart mysql without privileges and give the output of the mysql.user table to see how the permissions are set. or just reset them following the tut you linked.

Code:

service mysql stop
/usr/sbin/mysqld --skip-grant-tables

That stops mysql and restarts without any access restrictions.

Horus1 · 07-12-2013, 09:31 AM

Quote:

Originally Posted by zhjim

Severel things to check:

First if the server is running

Code:

ps aux | grep -i mysql

Next on which port

Code:

netstat -tulpn | grep -i mysql

If running and on default port 3306 lets try to connect

Code:

mysql -u root -p

as the user root and have us prompt for the password.

If that still gives the error try with the host option for mysql

Code:

mysql -h 127.0.0.1 -u root -p

or

Code:

mysql -h localhost -u root -p

or some other ip you have mysql running on. See the output of netstat.

If that all goes to now avail try to restart mysql without privileges and give the output of the mysql.user table to see how the permissions are set. or just reset them following the tut you linked.

Code:

service mysql stop
/usr/sbin/mysqld --skip-grant-tables

That stops mysql and restarts without any access restrictions.

output is

[[ root 576 0.0 0.0 2676 992 ? S Jul10 0:00 /bin/sh /usr/bin/mysqld_safe
mysql 736 0.0 0.6 159636 10716 ? Sl Jul10 0:27 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
root 737 0.0 0.0 1668 496 ? S Jul10 0:00 logger -t mysqld -p daemon.error
root 16046 0.0 0.0 1780 616 pts/0 S+ 18:14 0:00 grep -i mysql
---------------------------------------------------------------------------

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 736/mysqld

-----------------------------------------------------------------------------

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 958
Server version: 5.1.66-0+squeeze1 (Debian)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
]]now what after i follow this steps
PS at imscp (Port 23) TELNET DOWN

zhjim · 07-12-2013, 09:59 AM

Good mysql is up and running. How do you actually connect? Through the iscp admin panel or from a shell?
Are you doing this on the machine itself or from a remote host?

To help you further please include output of the shell or other error message into code blocks. (the button with the # on it).

Horus1 · 07-12-2013, 07:00 PM

Quote:

Originally Posted by zhjim

Good mysql is up and running. How do you actually connect? Through the iscp admin panel or from a shell?
Are you doing this on the machine itself or from a remote host?

To help you further please include output of the shell or other error message into code blocks. (the button with the # on it).

I did this from shell and the machine itself.

via imscp connect with phpmyadmin,when browse the site ,got this error

( Error establishing a database connection )please where is the error.

zhjim · 07-15-2013, 04:56 AM

phpmyadmin is on the same server? If not then you are note able to connect cause mysqld is not listening to a network address.

Lets first get the connection from the shell up and running. As the root user please do the following and copy and paste the output from the shell

Code:

mysql -p

Horus1 · 07-15-2013, 06:52 AM

Quote:

Originally Posted by zhjim

phpmyadmin is on the same server? If not then you are note able to connect cause mysqld is not listening to a network address.

Lets first get the connection from the shell up and running. As the root user please do the following and copy and paste the output from the shell

Code:

mysql -p

first of all, thank you for your help.

i had many issues,

-telnet down

-cannot connect mysql

-when go to add domain ,i was delete it before, got [[Domain with that name already exists on the system.]]

please how can i fix these errors.

output

Code:

# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1862
Server version: 5.1.66-0+squeeze1 (Debian)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

i tried to connect mysql with ,

localhost
localhost:3306
local host
127.0.0.1
127.0.0.1:3306
localhost.mydomain.com
mysql.mydomain.com
sql.mydomain.com

and run this script (dbtest.php)

got , Unknown MySQL server host '#server4.mydomain.com.local' (1)
PS i'm able to connect phpmyadmin smoothly.

zhjim · 07-15-2013, 07:16 AM

This post got quite big. Just read it once and then go through it step by step. We are getting there. Do not despair

Quote:

Originally Posted by Horus1

-telnet down

Telnet is deprecated. Please stop using it. Use SSH which encrypts traffic going over the network. Makes things more secure

Quote:

Originally Posted by Horus1

-cannot connect mysql

We have that one sorted out. The output you gave indicates that you can connect using the shell on the host the mysqld service resides. You can tell from the promtp

Code:

mysql>

You had been there before. I just not did get it right.

Quote:

Originally Posted by Horus1

-when go to add domain ,i was delete it before, got [[Domain with that name already exists on the system.]]

I guess this is through imscp. And as the message indicates the domain already exists. See if you can get a list of all configured domains within imscp. And also try if you can resolve this using the dig, nslookup or host command. Depending which one is available.

Quote:

Originally Posted by Horus1

Code:

# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1862
Server version: 5.1.66-0+squeeze1 (Debian)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

This one show that you are able to connect to mysql. Do a "Show database" and have a list of all the available domains. At least mysql should show up. This one is used for internals of mysqld.

Quote:

Originally Posted by Horus1

i tried to connect mysql with ,

localhost
localhost:3306
local host
127.0.0.1
127.0.0.1:3306
localhost.mydomain.com
mysql.mydomain.com
sql.mydomain.com

and run this script (dbtest.php)

The reason why not one of this could work is that mysqld is listening through socket. Also I think mysql client program uses the default socket path if 127.0.0.1 or localhost is used. But that might be distribution specific.

Quote:

Originally Posted by Horus1

got , Unknown MySQL server host '#server4.mydomain.com.local' (1)
PS i'm able to connect phpmyadmin smoothly.

Is phpmyadmin running on the same host as the mysqld service? I guess so. phpmyadmin normaly tries socket first, than localhost then the hostname.

Okay next should be the hostname and connection through this.
For this to work we first have to check that the domain is resolved by DNS. please make sure that the settings within the panel has the right collation between the name of the host and an ip that is on a network side interface (eth0 I'd say). If that one is good. try ping the host with the name. If the ping goes through we can get mysql to listen on that IP.

Change the option of bind-address inside /etc/mysql/my.cnf or through the panel. Use the outward facing IP or the hostname we configured before.

Horus1 · 07-15-2013, 09:28 PM

Quote:

Okay next should be the hostname and connection through this.
For this to work we first have to check that the domain is resolved by DNS. please make sure that the settings within the panel has the right collation between the name of the host and an ip that is on a network side interface (eth0 I'd say). If that one is good. try ping the host with the name. If the ping goes through we can get mysql to listen on that IP.

sorry,how i can do that,by edit

Code:

/etc/resolv.conf

or

Code:

/etc/network/interfaces

or

Code:

/etc/hosts

PS my

Code:

/etc/resolv.conf

nameserver 127.0.0.1

nameserver 8.8.8.8

nameserver 8.8.4.4

zhjim · 07-16-2013, 01:50 AM

Either configure the domain through the panel or use the /etc/hosts file. See man hosts for further information. While on it also read the manpage of

Code:

man resolv.conf
man interfaces

Maybe that can help as well http://debian-handbook.info/browse/stable/

Some recent post you wanted to connect to server4.mydomain.com.local for your mysql. Where'd you get that from?

Horus1 · 07-16-2013, 08:17 AM

Quote:

Some recent post you wanted to connect to server4.mydomain.com.local for your mysql. Where'd you get that from?

from /etc/hosts

zhjim · 07-16-2013, 09:33 AM

please post the content of /etc/hosts. That might clear somethings. What also helps is to know if you are only working on the machine itself, over the panel, from remote host or all together? Please state that with most of the output of the command your using.

Horus1 · 07-16-2013, 09:55 AM

Quote:

Originally Posted by zhjim

please post the content of /etc/hosts. That might clear somethings. What also helps is to know if you are only working on the machine itself, over the panel, from remote host or all together? Please state that with most of the output of the command your using.

Code:

# nano /etc/hosts

Quote:

# 'hosts' file configuration.
127.0.0.1 server4.horusone.com.local localhost
172.245.33.143 server4.horusone.com server4
::ffff:172.245.33.143 server4.horusone.com server4
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ff02::3 ip6-allhosts

is /etc/resolv.conf content correct?

zhjim · 07-17-2013, 06:55 AM

Inside /etc/resolv.conf the line with

Code:

nameserver 127.0.0.1

should only be there if you have a name server running on that machine. Else remove it or comment it in. Just put a # (hash) sign before it.

Horus1 · 07-18-2013, 08:27 AM

Quote:

Originally Posted by zhjim

Inside /etc/resolv.conf the line with

Code:

nameserver 127.0.0.1

should only be there if you have a name server running on that machine. Else remove it or comment it in. Just put a # (hash) sign before it.

I did that, but same error

Unknown MySQL server host '#localhost' (1)