a different way to recover your lost mysql root password

Yesterday my friend at the office asked me for help to reset a mysql root password because he had forgotten  it somehow.  He told me that he had tried the usual way to do it but it just didn’t work. Tutorials for the problem are so many in the internet. If you ask google using these keywords (mysql root password reset), there are many of them will show up on your screen. Most of them will tell you to do these steps (which is the usual way) :

Step # 1: Stop the MySQL server process
# /etc/init.d/mysql stop

Step # 2: Start the MySQL (mysqld) .server/daemon process with the –skip-grant-tables option so that it will not prompt for password
# mysqld_safe –skip-grant-tables &

Step # 3: Connect to mysql server as the root user
# mysql -u root

Step # 4: Setup new root password
mysql> use mysql;
mysql> UPDATE user SET Password=PASSWORD(’new-password’) WHERE User=’root’;
mysql> flush privileges;
mysql> quit
Step # 5: Stop MySQL server
# /etc/init.d/mysql stop

Step # 6: Start MySQL server and test it with the new-password

# /etc/init.d/mysql start
# mysql -u root -p

I’ve also tried those steps, but it just didn’t work. For a few hours i searched the internet looking for a different way or some other clues about that subject. I finally found some clues, it had something to do with mysql old password. After reading the page, i had an idea to do a little change on steps #4 above, like this :

Step # 4: Setup new root password
mysql> use mysql;
mysql> UPDATE user SET Password=OLD_PASSWORD(’new-password’) WHERE User=’root’;
mysql> flush privileges;
mysql> quit

I make it bold for you to see the difference easier. And it worked like a charm.

mysql client connection attempt to mysql server very slow

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

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 :
192.168.10.5 192.168.10.5

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.

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 127.0.0.1

mysql>

next step is to secure the server