alamster
  • About

Published

June 2, 2008

alamster in Database | June 2, 2008

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.

Share this:

  • Twitter
  • Facebook

Related

alamster

Published

June 2, 2008

  • Related Content by Tag
  • change root mysql db
  • login to 127.0.0.1 in mysql db
  • reset mysql password
Independent Publisher empowered by WordPress