Lost mysql password?

posted by onTuesday February 22nd, 2011, 215489 total view

Who works with server and database has to do (like it or not) with the DBMS.
Among the most popular DBMS there is mysql.
To be able to connect to the mysql daemon is necessary, usually, to enter a username and password.
But if you have forgotten your administrator password?
The simple answer is you do not able tologin to the database and can not perform the maintenance issue.
How to fix it?
First stop the mysqld daemon

root@pennegaz:pennega # /etc/rc.d/mysqld stop

Now start mysql in safemode

root@pennegaz:pennega # /usr/bin/mysqld_safe --skip-grant-tables&
[1] 23658

Now you can connect to mysql without a password:

root@pennegaz:pennega # mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.9 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

and proceed to change the root password.
Select the mysql database:

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> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
| user_info                 |
+---------------------------+
24 rows in set (0.00 sec)

Now you can change the password

mysql> update user set password = password('NEW_PASSWORD') where user = 'root' and  host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Before you stop and restart mysql you need to update the permissions on the DB:

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

mysql> quit;
Bye
root@pennegaz:pennega #

Stop and start mysqld

root@pennegaz:pennega # /etc/rc.d/mysqld stop
:: Stopping MySQL Server                                                                                     [BUSY] 110222 11:09:24 mysqld_safe mysqld from pid file /var/lib/mysql/pennegaz.pid ended
                                                                                                             [DONE] 
[1]+  Done                    /usr/bin/mysqld_safe --skip-grant-tables
root@pennegaz:pennega # /etc/rc.d/mysqld start
:: Starting MySQL Server                                                                                     [DONE] 
root@pennegaz:pennega # 

Well now the connection can be made with the new password.

Leave a Reply

Your email address will not be published. Required fields are marked *