One of a short series of posts listing useful MySQL commands that I use frequently when managing servers and databases. These come in useful when I don’t have access to Navicat, my favored tool for working with client databases.

The assumption I make is that you have command line access to your server – through PuTTY, for example, and that you are logged in as a user with permission to work in MySQL.

(note: # indicates a Linux command prompt, mysql> indicates the MySQL command prompt)

Resetting the Root Password for MySQL

This happens far too often – especially in cases where we are taking over website or server maintenance for a new client and the previous incumbant has ‘forgotten’ to provide the root password to mysql.

First, stop the MySQL process

# /etc/init.d/mysql stop

Restart MySQL in safe mode. Not too safe, actually, so don’t do it for longer than this set of commands!

# mysqld_safe --skip-grant-tables &

Login to MySQL at the command line – note that no password is required

# mysql -uroot

Switch to the mysql system table

mysql> use mysql;

Create a new root password

mysql> update user set password=PASSWORD("newrootpassword") where User='root';

Reload the system privileges

mysql> flush privileges;

Leave MySQL

mysql> quit

Stop MySQL (it’s been running in safe mode, remember!)

# /etc/init.d/mysql stop

Restart MySQL in normal mode. The password has been reset.

# /etc/init.d/mysql start