Fresh MYSQL install, Access denied for user 'root'

Question

I had an old lamp server that I wanted to move to a new machine, so I did a mysqldump, installed Ubuntu Server 13.10 on a new machine, installed lamp during installation, then imported my old mysql databases from the old lamp server. Everything seemed to work perfect right after the mysql import, to my surprise.

Then I tried setting up a cron job to mysqldump all databases every hour to a backup server. Just to make sure it worked, I tried manually running mysqldump on the new server just to make sure it worked (instead of waiting for the cron job to run). Anyways, the mysqldump function would not work, and for some reason now I can't access mysql at all. I tried:

mysql -u root -p

and get "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)"

Also, none of my PHP scripts can access mysql databases as well. So I am locked out.

I don't know why running mysqldump (or crontab) would lock me out, so I'm thinking it has to do with importing all the databases from my old lamp server (which was running an older version of mysql).

I'm still a linux newbie for the most part so any help would be appreciated!

Solution 1

I still don't know why I got locked out, but to resolve the issue I had to reset the mysql root password, which I did following the instructions on this site (but I modified them for Ubuntu 13.10): https://help.ubuntu.com/community/MysqlPasswordReset

Stop the mysql daemon process using this command :

sudo pkill mysqld

Start the mysqld daemon process using the --skip-grant-tables option with this command

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

start the mysql client process using this command

mysql -u root

from the mysql prompt execute this command to be able to change any password

FLUSH PRIVILEGES;

Then reset/update your password

SET PASSWORD FOR root@'localhost' = PASSWORD('password');

If you have a mysql root account that can connect from everywhere, you should also do:

UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';

Once you have received a message indicating a successful query (one or more rows affected), flush privileges:

FLUSH PRIVILEGES;

Then stop the mysqld process and relaunch it with the classical way:

sudo pkill mysqld
sudo service mysql restart

Some of those steps might be unnecessary but that's how I successfully reset the mysql root user password on Ubuntu Server 13.10 after importing a mysqldump file from an old lamp server

Solution 2

On mine the problem was that I can only access mysql from CLI being logged in as root.

Solution 3

Once you kill the mysqld process, you need to create a /var/run/mysqld directory to be used by the MySQL process to store and access a socket file:

$ sudo mkdir -p /var/run/mysqld
$ sudo chown mysql:mysql /var/run/mysqld

This is missing in a lot of answers. You can then start the mysql process with the --skip-grant-tables options successfully. You can run the command:

$ jobs

To confirm that the process is running before proceeding.

N/B This was on Ubuntu 18.04

Comments

[soulemane moumie]

have a look here stackoverflow.com/questions/19220085/…

[Kuba]

i had to use 'sudo' for a root... you can have a look here if you want to get rid of sudo stackoverflow.com/a/37241990/839273

[Salvador P.]

For mysql 5.7.X the password column has been changed to authentication_string, the rest for me worked :)

[Houman]

But why is this ?