Tuesday, June 5, 2012

MySQL reconfigure root password

Hi guys,

I ran into an interesting situation today and thought that I'd just mention a real quick painless solution. To make a long story short I did a MySQL dump on my windows machine in order to restore the DB on my Linux box. In the process, you also have to copy the mysql database since the grant tables are stored in that database. Somewhere along in the process though I messed something up and lost my root user account. I then got an access denied for any attempt to log in as root and I couldn't change the password for root either since the user didn't even exist in the mysql.user table.

One would be able to manually insert a user into the table; however I don't recommend that approach. It is just not a clean way of dealing with your most important account as far as MySQL goes.

I am not exactly sure what the problem was yet, but a quick painless solution is to reconfigure the MySQL server:

sudo dpkg-reconfigure mysql-server-5.1

Replace the version number with your version, which you could check by running mysql --version.

Hope this helps someone out!

Happy coding :)

Hermann

No comments:

Post a Comment