Last night, I was a bit of an idiot...

I was looking to clean up some old databases on my server in the livingroom and I couldn't get connected to it using TCP/IP over SSH.

I tried to give the root account rights to access the database from anywhere, but all I got was the message:

ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

Trying to log in locally resulted in the same thing.

I then went to Google to figure out how to recover the root account, and for the most part, it wasn't successful.

So I had an idea, which was to create a new account, and give it permissions to everything. The steps are as follows:

Log into your MySQL server using SSH

Stop MySQL using this command:

sudo /etc/init.d/mysql stop

Start MySQL in safe mode:

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

Enter MySQL:

mysql -u root

Run the following commands:

FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON . TO 'yourusername'@'localhost' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;

FLUSH PRIVILEGES;

To get back to the command shell

exit

Stop and start MySQL with only:

sudo /etc/init.d/mysql stop" & "sudo /etc/init.d/mysql start

I found that trying "sudo service mysql stop (and start)" resulted in the server becoming unresponsive. I don't know if it was another reason, but if I used the commands that I listed, it always worked right away.

From there, modify any website configuration files to reflect the changes of the username.

I will figure out a way to get my root account back some day!

Next Post