How to reset the mysql root password

Legacy Dynamo
1

Hope this helps someone else. My starting point was a Ubuntu server where I had root access in the shell, but had never written down the root mysql password when I installed the mysqld service. I wanted to enable remote access so I could use the fabulous Sequel Pro database tool, but my regular user didn’t have GRANT privileges, so I couldn’t alter the mysql.user table from the command-line. This took some scraping around the net, and I had to combine instructions written for Windows with the usual Unix instructions to get this all going.

  1. Shell into the server, move into the /etc/mysql directory, and (as root – very important) use your favorite command-line editor to open /etc/mysql/my.cnf.
  2. Locate the line that reads tmpdir = /tmp and insert a new line under that that reads skip-grant-tables, save the file and exit your editor.
  3. Still as root, issue the command ps auxc | grep mysql and note the process id of the mysqld service. The result will look like mysql 4122 0.0 2.9 184592 31652 ? Ssl 19:48 0:00 mysqld where the word mysql is the user running the command, 4122 is the process ID (the thing you need) and all the way at the right is the command name mysqld.
  4. Copy the process id, then type the command kill [paste the id] and press return. mysqld will restart automatically in a heartbeat, since it was started with the mysqld_safe script at startup, and it cannot be killed for very long.

Now you have a mysql server that ANYONE can connect to without a password and do anything at all. So working very quickly, connect to the server (either in the local shell or a desktop app) and issue the following query: UPDATE mysql.user SET Password=PASSWORD('your_new_password') WHERE User='root';.

This should succeed. Next, edit my.cnf again, remove the line about skip-grant-tables and save, then find the process id for mysqld again, kill it, and you are all set. Root is once again accessible using a password, and the grant tables will be used so all of the security is back in place.

Oh, and now you should write that password down somewhere safe.

Walter

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

2

Thanks for sharing Walter - hope I never have to use it.

D

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options