Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In an attempt to tune MySQL to make it work with a recent installation of Drupal I had to modify the MySQL settings on my server. After modifying the configuration file for MySQL (/etc/my.cnf) MySQL stopped working. After some attempts I make it start again but now all my php/MySQL webistes are not being able to connect to their DBs.

Here is why is so confusing:

  • If I check a phpinfo.php on any given site, the MySQL info is there
  • phpmyadmin runs just fine
  • I can run mysql from SSH using root but I see that mysql activity is reduced, look:

[root@server mysql]# mysqladmin processlist

+-----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
| Id  | User      | Host      | db        | Command        | Time | State              | Info             |
+-----+-----------+-----------+-----------+----------------+------+--------------------+------------------+
| 7   | eximstats | localhost | eximstats | Sleep          | 30   |                    |                  |
| 103 | DELAYED   | localhost | eximstats | Delayed insert | 30   | Waiting for INSERT |                  |
| 104 | DELAYED   | localhost | eximstats | Delayed insert | 149  | Waiting for INSERT |                  |
| 105 | DELAYED   | localhost | eximstats | Delayed insert | 149  | Waiting for INSERT |                  |
| 119 | root      | localhost |           | Query          | 0    |                    | show processlist |
+-----+-----------+-----------+-----------+----------------+------+--------------------+------------------+

My websites using MySQL almost all say:

Error establishing a database connection

Another say:

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'website_USER'@'localhost' (using password: NO)

This is my current my.cnf:

[mysqld]
#datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#pid-file=/var/lib/mysql/mysqld.pid
#skip-bdb
#skip-innodb
#err-log=/var/log/mysql.log
#bind-address = server.escalehost.com
log-bin = /var/lib/mysql/log-bin


#innodb_buffer_pool_size=2M
#innodb_additional_mem_pool_size=500K
#innodb_log_buffer_size=500K
#innodb_thread_concurrency=2
#max_connections=125
#table_cache=2500
#thread_cache_size=100
#thread_stack=250K
#wait_timeout=10
#join_buffer=5M
#myisam_sort_buffer_size=15M
#query_cache_size=15M
#read_rnd_buffer_size=5M
max_allowed_packet = 64M
#open_files_limit=8602

#[client]
#port           = 3306
#socket=/var/lib/mysql/mysql.sock

#[mysql.server]
#user=mysql
#basedir=/var/lib

[mysqld_safe]
#socket=/var/lib/mysql/mysql.sock
#err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I commented most of it to return it to its simplest version... How can I make the web side to connect to mysql?

share|improve this question
2  
After modifying the configuration file for MySQL (/etc/my.cnf) MySQL stopped working: Telling us something more about those modifications would certainly help us help you :) –  Expert System May 13 '13 at 14:30
    
You're right! In a few words I re-vamped all the specs to higher amounts. When that didn't worked I started reducing specs until MySQL actually worked. Then, even when not reducing those specs MySQL wouldn't start, what I did was renaming the my.cnf to make it inexistent so when I started the mysql it create it one. –  Escale May 13 '13 at 14:36
    
What versions of PHP and MySQL are you using ? Also, when you look at the output of phpinfo(), what is the "Client API Version" in the MySQL section ? –  Expert System May 13 '13 at 15:24
    
PHP 5.2.9 Mysql Client API version 5.0.96 –  Escale May 13 '13 at 15:38
add comment

migrated from stackoverflow.com May 13 '13 at 20:54

This question came from our site for professional and enthusiast programmers.

3 Answers

You are using the wrong credentials for the MySQL instance you are connecting to.

share|improve this answer
    
I believe is something in those terms. How can I restore the access to it? –  Escale May 13 '13 at 14:46
add comment

Have you tried to login on an ssh-shell yourself, using the user and password? Try:

mysql --user=user_name --password=your_password db_name"

If it doesn't work, you have the answer to your question.

share|improve this answer
    
Yes I have. I have the info for most of the sites and that works just fine. –  Escale May 13 '13 at 14:45
    
I'm able to log into mysql using a random user. Once logged in, any suggested mysql test? –  Escale May 13 '13 at 14:49
    
Are you able to execute any queries once logged in or do those get delayed as well? –  noamik May 13 '13 at 16:38
    
Hello. The connection is made back again. The problem was on the PHP configuration, not MySQL. A copy of php.ini was made and then Php has to be rebuilt again. All back to normal Thank you all for your time. –  Escale May 13 '13 at 16:45
add comment

The Error establishing a database connection means that either the username, the passowrd or the host are incorrectly configured.

Pick one of the sites reporting Error establishing a database connection, dig into its configuration and find out what values it is using for those 3 parameters. Then try connecting to the DB from an ssh-shell using mysql --user=USERNAME --host=HOST --password=PASSOWRD.

(Maybe during your modifications, you broke the host configuration.)

share|improve this answer
    
Yes! I thought about that! I made a query and all of them are pointing to localhost. I even added this to the host configuration (which I found out had nothing in it) mysqld: ALL : ALLOW mysqld-max: ALL : ALLOW sshd: ALL ALL: ALL mysqld : 127.0.0.1 : ALLOW –  Escale May 13 '13 at 16:20
    
So, when executing mysql --user=USERNAME --host=HOST --password=PASSOWRD through ssh (using the values from the website's config (not the values returned by a query in the DB)) you connect successfully ? –  Expert System May 13 '13 at 16:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.