Cant connect to database server?

Cant connect to database server?

I installed mysql. Created a database. Then, cloned to another Linode. I figured it doesnt hurt for app server to have mysql.

After, I followed these two links

database set up

http://library.linode.com/databases/mys … 0.04-lucid">http://library.linode.com/databases/mysql/ubuntu-10.04-lucid

stand alone database server

http://library.linode.com/databases/mys … sql-server">http://library.linode.com/databases/mysql/standalone-mysql-server

I figured all was good to go. Then, app server said…
> 'your php installation appears to be missing the mysql extension which is required by wordpress'

On the database server, service mysql status…
> 'start: Unable to connect to system bus: Failed to connect to socket /var/run/dbus/systembussocket: No such file or directory'

then, I did

service mysql start… again
> 'start: Unable to connect to system bus: Failed to connect to socket /var/run/dbus/systembussocket: No such file or directory'

reboot… same error. Shut down the server, a few hours later tried again. Like a broken record tried, service mysql status…
> mysql start/running, process 2077

However, it still doesnt connect to app server.

/var/log/mysql/error.log

110818  1:24:34 [Note] Event Scheduler: Purging the queue. 0 events
110818  1:24:34  InnoDB: Starting shutdown...
110818  1:24:35  InnoDB: Shutdown completed; log sequence number 0 44233
110818  1:24:35 [Note] /usr/sbin/mysqld: Shutdown complete

110818  1:24:35 [Note] Plugin 'FEDERATED' is disabled.
110818  1:24:35  InnoDB: Started; log sequence number 0 44233
110818  1:24:35 [Note] Event Scheduler: Loaded 0 events
110818  1:24:35 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
110818  1:24:44 [Note] /usr/sbin/mysqld: Normal shutdown

110818  1:24:44 [Note] Event Scheduler: Purging the queue. 0 events
110818  1:24:44  InnoDB: Starting shutdown...
110818  1:24:44  InnoDB: Shutdown completed; log sequence number 0 44233
110818  1:24:44 [Note] /usr/sbin/mysqld: Shutdown complete

110818  1:24:44 [Note] Plugin 'FEDERATED' is disabled.
110818  1:24:44  InnoDB: Started; log sequence number 0 44233
110818  1:24:44 [Note] Event Scheduler: Loaded 0 events
110818  1:24:44 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
110818  1:50:46 [Note] /usr/sbin/mysqld: Normal shutdown

110818  1:50:46 [Note] Event Scheduler: Purging the queue. 0 events
110818  1:50:46  InnoDB: Starting shutdown...
110818  1:50:46  InnoDB: Shutdown completed; log sequence number 0 44233
110818  1:50:46 [Note] /usr/sbin/mysqld: Shutdown complete

110818  1:50:46 [Note] Plugin 'FEDERATED' is disabled.
110818  1:50:46  InnoDB: Started; log sequence number 0 44233
110818  1:50:46 [Note] Event Scheduler: Loaded 0 events
110818  1:50:46 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
110818  1:50:52 [Note] /usr/sbin/mysqld: Normal shutdown

110818  1:50:52 [Note] Event Scheduler: Purging the queue. 0 events
110818  1:50:52  InnoDB: Starting shutdown...
110818  1:50:53  InnoDB: Shutdown completed; log sequence number 0 44233
110818  1:50:53 [Note] /usr/sbin/mysqld: Shutdown complete

110818  1:50:54 [Note] Plugin 'FEDERATED' is disabled.
110818  1:50:54  InnoDB: Started; log sequence number 0 44233
110818  1:50:54 [Note] Event Scheduler: Loaded 0 events
110818  1:50:54 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
110818  5:12:47 [Note] /usr/sbin/mysqld: Normal shutdown

110818  5:12:47 [Note] Event Scheduler: Purging the queue. 0 events
110818  5:12:47  InnoDB: Starting shutdown...
110818  5:12:47  InnoDB: Shutdown completed; log sequence number 0 44233
110818  5:12:47 [Note] /usr/sbin/mysqld: Shutdown complete

110818  5:16:02 [Note] Plugin 'FEDERATED' is disabled.
110818  5:16:03  InnoDB: Started; log sequence number 0 44233
110818  5:16:03 [Note] Event Scheduler: Loaded 0 events
110818  5:16:03 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
110818  5:38:02 [Note] /usr/sbin/mysqld: Normal shutdown

110818  5:38:02 [Note] Event Scheduler: Purging the queue. 0 events
110818  5:38:02  InnoDB: Starting shutdown...
110818  5:38:02  InnoDB: Shutdown completed; log sequence number 0 44233
110818  5:38:02 [Note] /usr/sbin/mysqld: Shutdown complete

110818 13:37:26 [Note] Plugin 'FEDERATED' is disabled.
110818 13:37:27  InnoDB: Started; log sequence number 0 44233
110818 13:37:27 [Note] Event Scheduler: Loaded 0 events
110818 13:37:27 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
110818 13:37:54 [Note] /usr/sbin/mysqld: Normal shutdown

110818 13:37:54 [Note] Event Scheduler: Purging the queue. 0 events
110818 13:37:54  InnoDB: Starting shutdown...
110818 13:37:55  InnoDB: Shutdown completed; log sequence number 0 44233
110818 13:37:55 [Note] /usr/sbin/mysqld: Shutdown complete

110818 13:39:11 [Note] Plugin 'FEDERATED' is disabled.
110818 13:39:08  InnoDB: Started; log sequence number 0 44233
110818 13:39:08 [Note] Event Scheduler: Loaded 0 events
110818 13:39:08 [Note] /usr/sbin/mysqld: ready for connections.

Then, tried mysql -u root -p, I was able to log in.

Then, tried

root@o:~# mysql -u 127.0.0.1 -p

Enter password:

ERROR 1045 (28000): Access denied for user '127.0.0.1'@'localhost' (using password: YES)

root@o:~# mysql -u 127.0.0.1 -p

Enter password:

ERROR 1045 (28000): Access denied for user '127.0.0.1'@'localhost' (using password: YES)

root@o:~# ps -ef | grep mysql

mysql 2077 1 0 13:39 ? 00:00:00 /usr/sbin/mysqld

root 2416 2386 0 13:50 pts/0 00:00:00 grep –color=auto mysql

root@o:~# ps aux | grep mysqld

mysql 2077 0.0 3.1 112568 16048 ? Ssl 13:39 0:00 /usr/sbin/mysqld

root 2418 0.0 0.1 3376 748 pts/0 S+ 13:50 0:00 grep --color=auto mysqld

on http://library.linode.com/databases/mys … sql-server">http://library.linode.com/databases/mysql/standalone-mysql-server

it states 'you dont have to install apache' ?

/etc/hosts

127.0.0.1 localhost

192.168.192.168 mysql.example.com mysql

192.168.192.169 app.example.com app

so, it just syncs with hostname mysql, and app

/etc/mysql/my.cnf

bind-address = mysql

shouldnt this stay as 127.0.0.1; doesnt this create a loop and more secure.

CREATE DATABASE webapplications;

GRANT ALL ON webapplications.* TO admin@'app' IDENTIFIED BY 'PASSWORD';

This defaults "Host" to 'app', not 'localhost'

?

15 Replies

is this a permission issue?

when I restart the server

user@server:~$service mysql status

status: Unable to connect to system bus: Failed to connect to socket /var/run/dbus/systembussocket: No such file or directory

user@server:~$service mysql status

start: Unable to connect to system bus: Failed to connect to socket /var/run/dbus/systembussocket: No such file or directory

when I do 'service mysql status' as root, it says it is running. How can I fix the permission issue?

Only root can manage services. As a normal user, do "sudo service mysql status" to do things.

For the actual problem, I would guess "your php installation appears to be missing the mysql extension which is required by wordpress" is probably a problem, but I don't know much about WordPress and it could be lying. You must, however, be able to connect to the MySQL server from the system running WordPress before it'll work:

$ mysql -u username -h 192.0.2.123 -p databasename

…replacing "username", "192.0.2.123", and "databasename" with the username, IP address, and database name WordPress is configured for, and entering the password when prompted.

And you'll want bind-address to be the private IP address of the database server, or else it won't be reachable from the application server at all. Yes, it's more secure to leave it as 127.0.0.1, but that's pretty useless :-)

I used this link to configure stand alone data server

http://library.linode.com/databases/mys … sql-server">http://library.linode.com/databases/mysql/standalone-mysql-server

I replaced username, 192.0.2.123 ,databasename to what wordpress configured to…

First, I tried from the app server.

user@app:~$ mysql -u username -h 192.0.2.123 -p databasename
Enter password: 
ERROR 1130 (HY000): Host 'app.domain.com' is not allowed to connect to this MySQL server
user@app:~$ sudo ufw status
Status: active

To                         Action      From
--                         ------      ----
80/tcp                     ALLOW       Anywhere
2222                       ALLOW       <my own="" ip="">Anywhere                   ALLOW       192.0.2.123 (data private ip)</my> 

I also tried on the database server````
$ mysql -u username -h 192.0.2.123 -p databasename
ERROR 1130 (HY000): Host 'mysql.domain.com' is not allowed to connect to this MySQL server
user@mysql:~$ mysql -u username -p databasename
ERROR 1045 (28000): Access denied for user 'username'@'localhost' (using password: YES)
user@mysql:~$ mysql -u username -p
ERROR 1045 (28000): Access denied for user 'username'@'localhost' (using password: YES)

<url url="http://library.linode.com/databases/mysql/standalone-mysql-server"><link_text text="http://library.linode.com/databases/mys … sql-server">http://library.linode.com/databases/mysql/standalone-mysql-server</link_text></url> that link shows @app not localhost

CREATE DATABASE webapplications;

GRANT ALL ON webapplications.* TO admin@'app' IDENTIFIED BY 'PASSWORD';

mysql> SELECT User, Host, Password FROM mysql.user;

+------------------+-----------+-------------------------------------------+
| User | Host | Password |
+------------------+-----------+-------------------------------------------+
| root | localhost | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB |
| debian-sys-maint | localhost | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB |
| username | app | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB |
+------------------+-----------+-------------------------------------------+

(username; and password encryption substituted)

thats what I did. I used these two links as guides

<url url="http://library.linode.com/databases/mysql/standalone-mysql-server"><link_text text="http://library.linode.com/databases/mys … sql-server">http://library.linode.com/databases/mysql/standalone-mysql-server</link_text></url>

<url url="http://library.linode.com/databases/mysql/ubuntu-10.04-lucid"><link_text text="http://library.linode.com/databases/mys ... 0.04-lucid">http://library.linode.com/databases/mysql/ubuntu-10.04-lucid</link_text></url>

Also, /etc/mysql/my.cnf shows bind-address = mysql; I changed that to data servers private IP number.

user@mysql:~$ ufw status
Status: active
To Action From
-- ------ ----
Anywhere ALLOW 192.632.2.125 (app private ip)
2222 ALLOW
````

on wordpress config file, I have changed to what documentation states
> wp-config.php

/** MySQL hostname */

define('DB_HOST', 'mysql');

I turned off the firewall on both servers, same issue.

ERROR 1130 (HY000): Host 'app.domain.com' is not allowed to connect to this MySQL server

@superdupler:

I turned off the firewall on both servers, same issue.

ERROR 1130 (HY000): Host 'app.domain.com' is not allowed to connect to this MySQL server

Now that's a permission error. Have a look at http://dev.mysql.com/doc/refman/5.1/en/create-user.html

I think the trouble is that the mysql.user row is expecting 'app', but the connection is coming from 'app.domain.com'.

You might want to do authentication by IP address, instead of by hostname. That will take care of the ambiguity, and will let you add new app servers without canoodling with the /etc/hosts on your database server. (One less thing to remember!)

If you do so, remember to disable reverse DNS lookups (skip-name-resolve in your my.cnf). Handling reverse DNS lookups for internal-use IP addresses requires an entire set of Internet infrastructure (see RFC 6304). The operators of that system would greatly appreciate not receiving a query every time someone hits your web site. :-)

> I think the trouble is that the mysql.user row is expecting 'app', but the connection is coming from 'app.domain.com'.

I am not sure what you mean…

+------------------+-----------+-------------------------------------------+
| User             | Host      | Password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB |
| debian-sys-maint | localhost | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB |
| username         | app       | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB |
+------------------+-----------+-------------------------------------------+ 

you want me to change app to private IP

| username         | 192.168.192.168        | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB | 

on wordpress wp-config.php

/** MySQL hostname */

define('DB_HOST', 'mysql');

I also should change this ('DB_HOST', 'mysql') to private database IP 192.168.192.168

define('DB_HOST', '192.168.192.168');

???
> You might want to do authentication by IP address, instead of by hostname. That will take care of the ambiguity, and will let you add new app servers without canoodling with the /etc/hosts on your database server. (One less thing to remember!)

on the database server /etc/hosts

127.0.0.1       localhost.localdomain   localhost
#63.183.468.42 mysql.domain.com mysql
192.168.192.168 mysql.domain.com mysql
192.168.192.169  app.domain.com app
# The following lines are desirable for IPv6 capable hosts
::1     ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

I commented out mysql public IP

==============================

on the app servers````
127.0.0.1 localhost.localdomain localhost
93.197.456.21 app.domain.com app
192.168.192.168 mysql.domain.com mysql

192.168.192.169 app.domain.com app

The following lines are desirable for IPv6 capable hosts

::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
````

I commented out app server private IP
> If you do so, remember to disable reverse DNS lookups (skip-name-resolve in your my.cnf). Handling reverse DNS lookups for internal-use IP addresses requires an entire set of Internet infrastructure (see RFC 6304). The operators of that system would greatly appreciate not receiving a query every time someone hits your web site.
I dont see skip-name-resolve on my.cnf

thanks for guiding me on the right direction. I got it working.

192.168.192.169 app.domain.com app

192.168.192.168 mysql server

on the database server vi my.cnf

bind-address=my-server-private-IP

bind-address=192.168.192.168

then, mysql -u root -p mysql

I deleted the previous username with Host app, and recreated it

+------------------+-----------+-------------------------------------------+
| User             | Host      | Password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB |
| debian-sys-maint | localhost | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB |
| username         | app       | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB |
+------------------+-----------+-------------------------------------------+ 

GRANT ALL ON databasename.* TO username@'192.168.192.169' IDENTIFIED BY 'PASSWORD';

+------------------+-----------+---------------------------------------------------+
| User             | Host      | Password                                          |
+------------------+-----------+---------------------------------------------------+
| root             | localhost         | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB |
| root             | mysql             | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB |
| root             | 127.0.0.1         | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB |
| debian-sys-maint | localhost         | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB |
| username         | 192.168.192.169   | *2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB |
+------------------+-----------+---------------------------------------------------+ 

then

mysql> update db set Host='192.168.192.169' where Db='databasename';

mysql> update user set Host='192.168.192.169' where user='username';

mysql> quit

I reboot both app, and database servers

Then, I logged in to the app server.

mysql -u username -h privateIP -p databasename

it worked. I was able to login to the database server. I was also able to check table status of the database from app.domain.com

However, wordpress still couldnt connect. So, I tried various things with wp-config.php

so, on wordpress on wp-config.php

/** MySQL hostname */

define('DB_HOST', 'mysql');

I changed mysql to private IP of the database server, and also tried mysql.domain.com

private IP worked.

thank you once again. I really appreciate it.

Good to know it's working! Sometimes it takes a little kicking to get things working over a network. But it builds character :-)

On the "skip-name-resolve" thing: it's not in my.cnf by default, so you'd have to add it. It, along with a whole slew of other options, is documented here.

yeah, it definitely builds character.

regarding skip-name-resolve, skip-external-locking, and skip-networking

bind-address = IP

skip-networking

skip-external-locking

skip-name-resolve

like that…?

I got skip-external-locking from linode library. Then, I saw this on the link you posted
> if you use this option on a system on which lockd does not fully work (such as Linux), it is easy for mysqld to deadlock

Maybe I also should uncomment skip-external-locking.

Yup, like that. You can also include a comment above it, in case you forget why the heck you put it there in the first place:

# Disable DNS lookups -- requires that grantsnot include hostnames, but
# may help reduce deadlocks due to DNS slowness.
skip-name-resolve

(Yes, I know there's a typo in it. "Grantsnot" is an inherently funny word.)

On skip-external-locking, it looks like you can leave it out since external locking is disabled by default, applies only to MyISAM, and only needs to be enabled when multiple processes are writing to the same MySQL data directory at the same time. (Frankly, I had no idea that was even possible.)

I was reading that if I connect to my database through mysql.socket instead of IP the performance will go up.

How can I go about that?

If I recall from a separate thread, you have your database server on a separate Linode from the application server, right? I'm pretty sure the mysql socket you're thinking of is a Unix socket, which only works for localhost communication, so isn't applicable if you have the database server on a separate Linode.

Is is true Unix sockets can be (well, ok, are) lower overhead than TCP, but unless you have a whole slew of really tiny connections to your database server, and your application is doing no pooling of connections whatsoever (uncommon, and if so, generally easy to fix with most popular setups), I suspect the TCP overhead is not a major performance bottleneck for you.

– David

thank you. Is there any way to boost performance between app server, and database server?

Where are your most common bottlenecks? Improve those and it'll boost performance. :-)

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct