Phantom’s Tech Blog

So the one thing I always fumble and have to go look up is how to reset a password for a mySQL user, be it the root user or just a user for a new database.  I use phpmyadmin which is a great tool and helpful provided you have local access to the server but sometimes you just need to use the command line to update the password for a user. The biggest problem with updating via the command line you do not always get a easy to read response as I learned after several attempts to reset the password on a account I had forgotten.  It turns out that after updating the password the response Query OK, 0 rows affected is normal.  I beat my head a while as I was not aware that due to the nature of the set password statement it would not show that a row was affected.

Enough babbling how do you do it?  Well as long as you can login as the root mySQL user then you can reset any user you have with the following command:
mysql> SET PASSWORD for 'username'@'hostname' = PASSWORD("NEW_PASSWORD");

Alternatively you can use the following instructions:
mysql> UPDATE user SET password=PASSWORD("NEW_PASSWORD") WHERE user=”username”;
mysql>FLUSH PRIVILEGES;

Both of the above commands will work however the second option is done by modifying the tables manually where the first option is running a function. Use either method to your preference, just be careful when resetting your root mySQL password.

Now should you have forgotten your root mySQL password do not worry, you can still recover your password. The trick is to restart mySQL and skip the grant tables.
First stop mySQL on your system, then on Unix based systems run #/usr/bin/mysqld_safe --skip-grant-tables &
#mysql -h localhost
mysql>SET PASSWORD for 'root'@'localhost' = PASSWORD("NEW_PASSWORD");

At this point you will want to restart the server and test your login.

So its not that difficult just easy to forget as you will probably reset your password once in a blue moon.

Welcome to my tech blog.  I am starting this blog for a couple of reasons.  First of its a great way to share my knowledge with the world.  Although this information is already out there its a one stop shop for technological know how, or at least I hope it will be. Second its a great place to keep all those commands, and procedures I use when setting up my own systems and saves me the trouble of searching for that command I used last year.

So who am I?

My name is Desmond and I have been in the tech industry for the last 8 years or so.  I have experience in both Microsoft Windows, and Unix/Linux systems.  I also have worked in an Mac environment but its been so long that I am not sure if I will have a lot to offer the Mac users out there, sorry.  I am focused on Linux as I find it a great OS and not to mention its free!

I hope what I offer here is helpful to those out there reading it.  I also want to advise that the instructions that I provide worked for me in my environment.  You will need to adjust the commands to suit your needs and I cannot be held responsible for any damage done.  I am only providing this information for educational purposes.

Well than I guess that it for the intro.   I hope to hear great things from you.  Let me know what you think.