Lost mysql password?
posted by pennegaonTuesday February 22nd, 2011, 253032 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&  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] + 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.