Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a strange situation here:

From time to time I cannot log in with any of my mysql users. I even cannot make a mysql dump.

So I started searching in mysql files and I found that users.MYD and users.MYI are modified in the time when the login problem occurs. The only way to return everything to work is to restore the users.* files from the time when the system was running okay.

I searched about the problem and I found that there was some bug in the MySQL, but it was in the older versions (4.X). I'm running 5.5.

Any ideas? Thanks!

share|improve this question

migrated from stackoverflow.com Apr 15 '13 at 12:29

This question came from our site for professional and enthusiast programmers.

    
Next time you can login as root@localhost, run this query and tell the result: select count(1) from information_schema.columns where table_schema='mysql' and table_name='user'; –  RolandoMySQLDBA Apr 15 '13 at 15:48
    
Its Windows Server 2008. I looked up the logs, there is nothing strange. All users have passwords. I looked up in the MYD file and strange thing happens there. Sometimes the passwords are reversed. Yep, sound strange but I have copy of the MYDS before the problem and I see the same hashed password but in reverse. @RolandoMySQLDBA, this can't happen. I cannot run any query, because I cannot use any user, even root. –  Paul Reed Apr 16 '13 at 9:55
    
@Michael - sqlbot, I mean that the server is using Windows Server 2008, the MySQL is 5.5.20. RolandoMySQLDBA, thank you. :) But it seems that this is not an option in my case. I have about 10-15 mysql users which are used by software I write. Changing the root password wont help me restoring the other users password, yes I can reset it that way, but can't restore.I was thinking about someone hacking me, but in the log there are no external connections.And my logic is like that: "If someone knows some of the users password, he won't reverse my users passwords, he will drop all my databases.". –  Paul Reed Apr 17 '13 at 6:27
    
During the times when you "cannot log in" do you see a specific message? Was your server newly installed as 5.5.20 or was it upgraded from a previous release? –  Michael - sqlbot Apr 17 '13 at 18:23
    
FYI You'll need to register in order to regain control of your migrated question. –  Paul White Aug 22 at 8:07

2 Answers 2

Corrupted mysql database prevents reading the user permissions during the dump process and hence you are out of it.

It seems that you are able to restore the user tables. So, better to stay away from possible mix of data files written by old version and new version of mysql server.

First, make sure to restore and make it run as usual. Then, take a full mysqldump backup of mysql database and restore it again fully, immediately. Hoping, all your data files are now compatible to recent version of server.

But be sure to BACKUP everything before you do it.

share|improve this answer

Theres not enough information here to say with any certainty, but this could very well be that your server has a vulnerability somewhere that is being exploited - someone got access to that file, sets the password for all the users to something so they can then gain access to the database. I would look into your server logs and see if anything weird has been going on.

Assuming it's a ubuntu server, I would start in /var/log/apache2/*.log and check the access and error logs particularly and look for anything that looks like a hack attempt. Then I would turn on MySQL query logging if it's not already and see if any weird queries are being run.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.