mysql client connection attempt to mysql server very slow

computer A with ip has mysql client on it and computer B with ip has the mysql server. mysql client on computer A try to connect to mysql server on computer B :
$ mysql -u username -p -h

it takes at least 5 seconds before mysql prompt show up on the screen after entering password. this can be a problem when computer A is hosting a web application which needs to read it’s database on compter B. page generation would take very long time even when a lot of bandwidth are available. the solution to this problem is very simple. open /etc/hosts file on computer B and add the ip of computer A like this :

save and exit. now the connection attemp should be faster and mysql prompt will show up on the screen in a second. this might be applied to other application too, like ssh for example.

Reblog this post [with Zemanta]

error 13 /root/tmp mysql_install_db

i got such error when installing mysql 5.0.51a for Linux (non RPM, Intel C/C++ compiled, glibc-2.3) on mandrake 10. it took me some hours to make it work…. with google’s help :). here’s the story….

a normal binary distribution installation should be as simple as this (from mysql reference manual or BINARY-INSTALLATION file after unpacking):

shell> groupadd mysqlshell> useradd -g mysql mysqlshell> cd /usr/localshell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -shell> ln -s full-path-to-mysql-VERSION-OS mysqlshell> cd mysqlshell> scripts/mysql_install_dbshell> chown -R root  .shell> chown -R mysql datashell> chgrp -R mysql .shell> bin/mysqld_safe --user=mysql &

on a system where another mysql daemon is already running(eg. mine already running 4.18), the first two lines above could be skipped and the base directory(basedir) could be other than /usr/local (eg. /home/mysql)

the error 13 showed up when i ran scripts/mysql_install_db, why did it try to write to /root/tmp ? it was because the TMP variable set to /root/tmp.
$ env | grep TMP

so i needed to tell the script to write to other writeable directory.

$ mkdir /tmp/mysql
$ chown mysql.mysql /tmp/mysql

to tell the script about /tmp/mysql

$ cp /home/mysql/support-files/my-medium.cnf /home/mysql/my.cnf
$ vi /home/mysql/my.cnf
add TMPDIR=/tmp/mysql under [mysqld] section then save and quit the file. now do

$ cd /home/mysql
$ scripts/mysql_install_db –basedir=/home/mysql –datadir=/home/mysql/data –user=mysql –force –defaults-file=/home/mysql/my.cnf

$ vi support-files/mysql.server
edit basedir= and datadir= to
basedir=/home/mysql and datadir=/home/mysql/data then save and quit

$ support-files/mysql.server start

now try to login

$ bin/mysql -u root -h


next step is to secure the server