Reset MySQL password in FreeBSD

Just another post for my archive
One our sandbox accidently delete root account with host ‘localhost’. We need to recover the account by grant new root account back into system.
The steps are :
1. Turn off mysql service
sandbox# /usr/local/etc/rc.d/mysql-server stop

2. Run mysql with skip grant table mode
sandbox# mysqld_safe –skip-grant-tables &
[1] 5187
sandbox# Starting mysqld daemon with databases from /var/db/mysql
sandbox#
3. Login as user root with empty password
sandbox# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.51a FreeBSD port: mysql-server-5.0.51a
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>
4. Change root password with something
mysql> update user set password=password(’xxxxyyyy’) where user=’root’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
5. Turn off mysql
sandbox# /usr/local/etc/rc.d/mysql-server stop
6. Turn on mysql service
sandbox# /usr/local/etc/rc.d/mysql-server start
Finish
P.S :
Just special note for login into mysql database, these methods are different :
This method use ’127.0.0.1′ as host.
sandbox# mysql -uroot -p -h 127.0.0.1
This method use ‘localhost’ as host
sandbox# mysql -uroot -p
sandbox# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.0.51a FreeBSD port: mysql-server-5.0.51a
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> use mysql;
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> select user,host from user;
+——-+———–+
| user | host |
+——-+———–+
| root | 127.0.0.1 |
| fikri | localhost |
| hanz | localhost |
| root | localhost |
+——-+———–+
4 rows in set (0.11 sec)
root can use ’127.0.0.1′ and ‘localhost’ as host information.