Reset MySQL password in FreeBSD

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.