Deleted user accounts and database by mistake, how to recover it?

Question

I was trying to change my phpmyadmin username and password in the sql query using the code below, but it deleted all of my databases and user accounts.

UPDATE mysql.user SET user='newusername',
password=PASSWORD('newpassword') WHERE user='root';
FLUSH PRIVILEGES;

What can I do to recover the possible best state of my system?

Answer 1

This query renames all root user to newusername, and sets their password to root. It doesn't change any other databases, neither deletes anything.

Your databases should be still there.

ls /var/lib/mysql can show, which dbs are really existing.

The old passwords of the root user are gone, but a rough reversion of its username can be reached by an

UPDATE mysql.user SET user='root' WHERE user='newusername';

query.

If you can't start your mysql server, stop the server, and temporarily restart it with

mysqld_safe --skip-grant-tables

In this mode, the mysql entirely bypasses all authentication. More can you read from it here.

Answer 2

If your database has been deleted, you cannot restore it, it is better to create a new database. If you have actually deleted one of the database tables, its file manually; If you know about its fields, you can return it, otherwise you have to create a new database again.