Regarding to my job on managing Unix Servers, sometimes I forgot what password is being used on my new server. It make us sad, angry, hopeless when we lost our password. I just remember it once, but the consequences were many.

One of them is mysql database server. So based on my experience, we still can reset the password using simple steps below. You know, you can search so many things related on how to reset the mysql server root password, but may be this can help you as Unix Administrator or DBA although just a little solution. Some notes, these steps below is used and run on FreeBSD 6.2 box.
1. First, make sure that the Mysql Database Server is currently running.

colo# ps wax | grep mysql
2924  p0  I      0:00.00 /bin/sh ./safe_mysqld
2936  p0  S      0:00.09 /usr/local/mysql/bin/mysqld –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data –user=mysql –pid
2948  p0  S+     0:00.00 grep mysql

2. If you entered wrong password, the server will repond like this

colo# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: YES)

3. But if you already remember the password, of course mysql server will allow you to make a query and give you mysql prompt.

colo# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.27-standard

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

4. Let say, you lost the password and want to reset, try to shutdown the mysql server. You also must know the PID of the mysql server. Usually, you can search on data folder. On my FreeBSD box, I can found it on folder:

/usr/local/mysql-standard-5.0.27-freebsd6.0-i386/data

And you’ll find the file with extension .pid

colo# ls -l
total 20536
-rw-rw—-  1 mysql  wheel      1732 Nov 20 11:23 colo.innchannels.com.err
-rw-rw—-  1 mysql  wheel         5 Nov 20 11:22 colo.innchannels.com.pid
-rw-rw—-  1 mysql  wheel   5242880 Nov 20 11:22 ib_logfile0
-rw-rw—-  1 mysql  wheel   5242880 Jan  1  2001 ib_logfile1
-rw-rw—-  1 mysql  wheel  10485760 Jan  1  2001 ibdata1
drwxr-x—  2 mysql  wheel      1536 Jan  1  2001 mysql
drwxr-x—  2 mysql  wheel       512 Oct 21  2006 test

Let’s try to know the pid

colo# cat colo.innchannels.com.pid
2936

Well, the pid is 2936.

5. Kill the process

colo# kill `cat colo.innchannels.com.pid`
colo# STOPPING server from pid file /usr/local/mysql/data/colo.innchannels.com.pid
071120 11:37:16  mysqld ended
[1]    Done                          ./safe_mysqld

6. Then back to bin directory and use this option: –skip-grant-tables –user=root

colo# cd data
colo# cd ../bin
colo# ./safe_mysqld –skip-grant-tables –user=root &
[1] 3120
colo# Starting mysqld daemon with databases from /usr/local/mysql/data

7. Now, without any password, you can directly entered the mysql prompt and change the password to “jakarta” for example.

colo# ./mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.27-standard

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> update mysql.user set password=PASSWORD(‘jakarta’) where user=’root’;
Query OK, 2 rows affected (0.18 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

8. You can use your new password now, but don’t forget to stop the mysql server and start again without –skip-grant-tables option.

colo# kill `cat /usr/local/mysql-standard-5.0.27-freebsd6.0-i386/data/colo.innchannels.com.pid`
colo# STOPPING server from pid file /usr/local/mysql/data/colo.innchannels.com.pid
071120 11:48:27  mysqld ended
[1]    Done                          ./safe_mysqld –skip-grant-tables –user=root

colo# pwd
/usr/local/mysql-standard-5.0.27-freebsd6.0-i386/bin
colo# ./safe_mysqld –user=mysql &
[1] 3188
colo# Starting mysqld daemon with databases from /usr/local/mysql/data