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.