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 &
mysql -u root
Run the following commands:
GRANT ALL PRIVILEGES ON . TO 'yourusername'@'localhost' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;
To get back to the command shell
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!