Accidental overwrite of 'mysql' database in mysql

3 posts / 0 new
Last post
#1 Tue, 06/03/2014 - 04:50
essdeeay

Accidental overwrite of 'mysql' database in mysql

== Debian 7 ==

I exported mysql databases from another server, using the command mysqldump --all-databases >db.sql, and then imported them into my server's mysql, using the command mysql <db.sql and in doing so have overwritten my own mysql database.

Luckily for me, no virtual server actually uses their database, so I can remove, re-create in Virtualmin, but there are several wordpress installations with their own databases. I tested a Wordpress installation which still seemed to work, but I don't know where the permissions on these databases lie, so I don't know how much damage I've done.

Needless to say, I don't have a backup.

1) How can I restore/re-create the default user database? 2) Why do the Wordpress installations still work?

Many thanks, Steve

Tue, 06/03/2014 - 13:31
andreychek

Howdy,

The "mysql" database is what contains all the users and database permissions.

I'm not sure why any of your WordPress databases currently work, unless what it's been overwritten with happens to have the users in it that are needed to access those databases.

There is information here on how to re-create the mysql database with defaults:

http://stackoverflow.com/questions/8911115/how-to-recover-recreate-mysql...

Do you happen to have any backups of your Virtual Servers?

If so, after restoring your mysql database, you could then restore those in order to re-create your database users and permissions.

If not, you would probably need to disable, then re-enable, the MySQL feature for your Virtual Servers. However, that would also cause it to delete your databases, so you would need to make backups of those, and re-create them once the users are restored.

-Eric

Wed, 06/04/2014 - 08:11
essdeeay

Thank you Eric,

There are several words going round and round in my head at the moment... "backups stupid backups stupid backups etc..."

No, I didn't have a backup.

In the end I installed Virtualmin to a test box and noted the default GRANTS on the virtual servers database and wordpress installation. I then re-instated those grants by hand, getting the password for the wordpress databases from the virtual server's wp-config.php file - which worked.

However, one point to note. If you create the user in the GRANT statement along with IDENTIFIED BY PASSWORD '' (which is good for security otherwise the user won't have a password at all!), it doesn't set the REAL password - something to do with it being a hashed password. You must also separately use SET PASSWORD... in order to set the REAL password.

Many thanks, Steve

Topic locked