Resetting a forgotten MySQL root password

Instructions according to MySQL Documentation

"Disable" the current passwords

If you are the SuperUser or the user mysqld runs as, take it down by sending it a kill(1) (don't use -9!!). Its PID is stored in a .pid normally found in the MySQL database directory

1. kill `cat /mysql-data-directory/hostname.pid`

Now restart mysqld with the --skip-grant-tables option and set a new password. There are two alternatives:

Using mysqladmin

That's as simple as

mysqladmin -u root password 'mynewpassword' mysqladmin -h hostname flush-privileges

Using the mysql client

Connect to the mysqld server with

mysql -u root mysql

Issue the following commands in the mysql client

mysql> UPDATE user SET Password=PASSWORD('mynewpassword') WHERE User='root'; mysql> FLUSH PRIVILEGES;

Done.

You should now be able to connect using the new password. You can now stop mysqld and restart it normally.

Clearing Unwanted Binary Log Files

mysql in some configurations can log all database activity to a binary log file (usually in /var/lib/mysql). If you have a busy site this can grow quite large. The recommended management solution in the mysql manual is to remove binary log files that you no longer want. They suggest removing files that are older than 3 days. To remove all binary log files you can use the following command

RESET MASTER;