My MySQL root user is gone, how do I recover it?

Question

I was trying to move the MySQL tables so them were in another folder, so I messed out the plugins table or something like that and I ended up reinstalling the whole thing.

I did sudo aptitude purge mysql-server then I removed the MySQL data rm -rf /var/lib/mysql/mysql and also I removed the data from the other directory I was trying to move the tables.

After reinstallation I supposed I would be able to login using mysql -u root but it said the user was wrong. I tried with the old password and the same thing. Then I searched for a way to reset the root password.

So I did mysqld_safe --skip-grant-tables then I logged in mysql -u root succefully adn then I tried UPDATE user SET Password=PASSWORD('new-password') where User='root'; with no luck, 0 rows affected. Then I did SELECT * FROM User and all I got was debian-sys-maint, nothing more, no root or any other user.

Any little help will be appreciated =/

Answer 1

Just create the users you need. There may no root@localhost per default.

You could always connect via mysql --defaults-file=/etc/mysql/debian.cnf

Create your new root user with: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'YOUR NEW PASSWORD' WITH GRANT OPTION

Answer 2

Since you only removed /var/lib/mysql/mysql , the reinstall wouldn't have created the default dbs since the data dir (/var/lib/mysql) still exist. Rename the data dir and try a reinstall once again. You can restore the dbs from the backup once you are set with the root account.

-$