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