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 been using xampp (on windows XP/XAMPP 1.7.4 /PHP 5.3.5) for quite a while now, and last morning I started to get some weird messages when I was developing my website, It happened yesterday and started as something I thought I could handle but apprently I couldn't. These messages were some about timeouts, some were about "can't connect" for my connection string inside my website.

I was thinking that the site might be under attack. as last morning was pretty much like any other morning. I can actually start the admin (the button near the "start/stop" apache) page, but can't login to mysql. I think root was not set, so I tried to use the documentation to reset it.

What happens now is that I just cannot login into MYSQL Server no matter what I do. I keep getting access denied for ''@'localhost' and 'root@localhost' if I try logging as root.

I have been on all the web for help! Please HELP !!

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

You need to restart mysql from the Linux command line like this:

service mysql restart --skip-grant-tables --skip-networking

If you are using Windows, add the two options in the mysqld section of my.ini:

[mysqld]
skip-grant-tables
skip-networking

and then run

net stop mysql (wait 10 seconds)
net start mysql

This will allow immedidate login without authentication and without any remoye connection sneaking in on you.

INSERT INTO mysql.user SET
Host='localhost',
User='mynewuser',
Password=PASSWORD('whateverpassword'),
Select_priv='Y',
Insert_priv='Y',
Update_priv='Y',
Delete_priv='Y',
Create_priv='Y',
Drop_priv='Y',
Reload_priv='Y',
Shutdown_priv='Y',
Process_priv='Y',
File_priv='Y',
Grant_priv='Y',
References_priv='Y',
Index_priv='Y',
Alter_priv='Y',
Show_db_priv='Y',
Super_priv='Y',
Create_tmp_table_priv='Y',
Lock_tables_priv='Y',
Execute_priv='Y',
Repl_slave_priv='Y',
Repl_client_priv='Y';

then

service mysql restart

At least you will have a localhost user named mynewuser with enough privileges to login.

You should then run this query:

select user,host,password from mysql.user;

and see if any new users were added by a hacker. If you see any remote users that should not be there, you can remove them with:

DELETE FROM mysql.user WHERE ...;
FLUSH PRIVILEGES;

If you are using Windows, please remember to remove skip-grants-tables and skip-networking from my.ini, then stop and start mysql service.

CAVEAT

Make a backup of /var/lib/mysql/mysql before you do anything.

UPDATE 2011-09-26 10:41 EDT

If the INSERT INTO mysql.user query does not work, it simply means the query is not compatible with the version of mysql I took this example from.

In order to know the correct INSERT query to user, please run this query:

desc mysql.user;

You will see all columns with the type ENUM('Y','N') for each privilege. Adjust the INSERT query to match all columns and then run that INSERT.

To get the exact columns to set to Y for all privileges, user this query:

SELECT CONCAT('SET ... ',GROUP_CONCAT(CONCAT(column_name,'=''Y'''),';'))
FROM information_schema.columns
WHERE table_schema='mysql'
AND table_name='user'
AND column_type='enum(''N'',''Y'')'
ORDER BY ORDINAL_POSITION\G

This will present to columns to set regardless of the version of MySQL you are using.

share|improve this answer
    
ok, so right after your paragraph "This will allow immedidate login without authentication and without any remoye connection sneaking in on you." I get this "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)" as I tried logging in with mysql -u root –  Ted Sep 26 '11 at 15:02
    
@Ted : just type 'mysql' and hit enter. If you added the two options into my.ini and restarted the mysql service, you should get the mysql prompt immediately. –  RolandoMySQLDBA Sep 26 '11 at 15:16
    
ok, just typed the mysql, pasted in the UPDATE command and got this: "ERROR 1142 (42000): INSERT command denied to user ''@'localhost' for table 'user' (which I always get) –  Ted Sep 26 '11 at 15:24
    
let us continue this discussion in chat –  RolandoMySQLDBA Sep 26 '11 at 16:47
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.