MySQL – ‘USE db_name’ Takes Too Long to Change When Login Remotely

MySQLmysql-5.5

I gave SELECT privilege to a user on a particular database, Now whenever I try to connect remotely

$ mysql -h ip-of-remote -u username -p

with that user credentials and try to change the database it takes to long to get Database changed while it connects (goes mysql prompt) very fast

mysql> USE db_name;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

On the contrary, when I ssh the mysql server and then try to login mysql prompt locally (without host) with the same user credentials and then try to change database it quickly changes and I don't get that message

mysql> USE db_name;
Database changed
mysql>

I tried to --disable-auto-rehash in my.cnf but no luck. Please help

[mysql]
no-auto-rehash

Best Answer

since the the following command use my_database; executed too fast when you try this locally (on the same server), and take a log time when you executed remotely, there is some common causes for that like the connection between the server and client is slow, or there is a delay (lag) on attempting to retrieve and verify reverse DNS, you can try to add the following parameter skip_name_resolve for testing to my.ini or my.cnf under [mysqld] section.

Related Question