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

stand alone database server
http://library.linode.com/databases/mys ... sql-server

I figured all was good to go. Then, app server said...


On the database server, service mysql status...


then, I did
service mysql start... again


reboot... same error. Shut down the server, a few hours later tried again. Like a broken record tried, service mysql status...


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
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'

?

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/system_bus_socket: 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/system_bus_socket: 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

_________________

/* TODO: need to add signature to posts */

I used this link to configure stand alone data server
http://library.linode.com/databases/mys ... sql-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)

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)

http://library.linode.com/databases/mys ... sql-server 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
http://library.linode.com/databases/mys ... sql-server
http://library.linode.com/databases/mys ... 0.04-lucid

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       <my own ip>

on wordpress config file, I have changed to what documentation states

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.

_________________

/* TODO: need to add signature to posts */

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');

???


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

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.

_________________

/* TODO: need to add signature to posts */

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


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.)

_________________

/* TODO: need to add signature to posts */

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?