ym's blog

MySQL stores its databases in the filesystem somewhere, with each database represented by a directory. Exactly where will depend on what your OS is, what distribution you’re using (if you’re on Linux), etc.

If you’re on Linux, try looking in /var/lib/mysql; under that directory you should see a folder for each database. If you see directories other than “mysql” there may be databases in use.

If you don’t have /var/lib/mysql then run ps aux | grep mysql in a terminal and look for the directory specified by the –datadir parameter. If that doesn’t work, find / | grep my.cnf … then look for datadir in the my.cnf file.

On Windows, look in C:\Program Files\MySQL\MySQL Server [your MySQL version]\my.ini for the datadir parameter.

To reset the root password, follow the instructions here: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

——————————————————–

First of all you will need to ensure that your database is stopped:

root@pr101:~# /etc/init.d/mysql stop

Now you should start up the database in the background, via the mysqld_safe command:

root@pr101:~# /usr/bin/mysqld_safe --skip-grant-tables &
[1] 6702
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6763]: started

Here you can see the new job (number “1″) has started and the server is running with the process ID (PID) of 6702.

Now that the server is running with the –skip-grant-tables flag you can connect to it without a password and complete the job:

root@pr101:~$ mysql --user=root mysql
Enter password:

mysql> update user set Password=PASSWORD('new-password-here') WHERE User='root';
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

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

mysql> exit
Bye

Now that you’ve done that you just need to stop the server, so that you can go back to running a secure MySQL server with password restrictions in place. First of all bring the server you started into the foreground by typing “fg”, then kill it by pressing “Ctrl+c” afterwards.

This will now allow you to start the server:

root@pr101:~# /etc/init.d/mysql start
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

Now everything should be done and you should have regained access to your MySQL database(s); you should verify this by connecting with your new password:

root@pr101:~# mysql --user=root --pass=new-password-here

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.24a-Debian_4-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye

If you’d like to automate this process you could start by looking at this simple shell script which will allow you to reset a password with one command.

——————————————————————–

You can recover MySQL database server password with following five easy steps.

Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password.

Step # 3: Connect to mysql server as the root user.

Step # 4: Setup new mysql root account password i.e. reset mysql password.

Step # 5: Exit and restart the MySQL server.

Here are commands you need to type for each step (login as the root user):

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &
Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended
[1]+  Done                    mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p

from http://www.cyberciti.biz/tips/recover-mysql-root-password.html