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
stand alone database 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
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
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?
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 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');
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
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
> 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
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.
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
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.
# 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 defaultmultiple processes are writing to the same MySQL data directory at the same time
How can I go about that?
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