MySQL Quick Reference

From 5dollarwhitebox.org Media Wiki

Reset MySQL Root Password

Should you find yourself forgetting or losing your MySQL Root Password, you can do the following (or similar) to reset it:

Add 'skip-grant-tables' to the MySQL server's 'my.cnf' usually found at '/etc/my.cnf' in RedHat/Fedora/CentOS or '/etc/mysql/my.cnf' in Debian/Ubuntu. Needs to go under the '[mysqld]' section. Similar to:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-grant-tables

Then restart MySQL:

/etc/init.d/mysql restart

You should then be able to login to mysql without a password:

linuxbox /]# mysql
mysql>

Now flush privileges (This clears privileges back to where they should be without 'skip-grant-tables')

mysql> FLUSH PRIVILEGES;

Then reset the root user's password:

mysql> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('password');

Where 'password' is the cleartext password you want to set for root@localhost

Now remove 'skip-grant-tables' from '/etc/my.cnf' and you are all set