Tuesday, July 24, 2012

MySQL: How to Unblock with 'mysqladmin flush-hosts' Mysql 5 and greater

MySQL Error : 
wrong authentication (username or password incorrect)
too many connections from same machine.

Solution taken: 

Clear with flush-hosts command.
See http://dev.mysql.com/doc/refman/5.1/en/flush.html

STEPS for Linux 

Open terminal window type
mysqladmin -uroot -ppassword flush-hosts;

*[Remember -uroot is your root name with a "-u" before it, and same for password "-p" before password]


Another tip 1

------------------------------------------
Restarting MySQLd solves the problem. FLUSH HOSTS also wipes the internal DNS cache, so be careful with its use - a flood of reverse DNS lookups can severely limit your server’s speed!

Open the terminal and type (for Linux)
/etc/init.d/mysql restart 

Another tip 2 allow many connections
------------------------------------------
To increase the maximum connections that mysql allows, you must edit the file /etc/my.cnf. Log into the server as root and type

nano /etc/MYSQL/my.cnf

it will open the config file (depending upon your cnf file location change the path) and uncomment (remove #) to the following line with the allowed no of connections e.g.
max_connections=10000 
Now press CTRL+X and Yes (Y) to exit from terminal window. Now Type

/etc/init.d/mysql restart 
you will see on the terminal screen
Stopping MySQL database server mysqld: [ OK ]
Starting MySQL database server mysqld: [ OK ]
 
CHECK THE SETTING IS PROPER OR NOT...

If you'd like to check to see if this setting was successfully applied, you may check using one of the following methods: 
  1. login to mysql
    mysql -uadmin -ppassword
    or through mysql administrator tool (Go to tools->MySQL text Console), you will be at the "mysql>" prompt. 
  2. Then give the command
    show variables like 'max_connections'; 
    make sure you put the semi-colon on the end. To exit mysql, use the "quit" command

    .

0 comments:

Post a Comment