Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I was attempting to allow remote logins to MySQL, so i changed /etc/mysql/my.cnf to bind to 0.0.0.0 instead of 127.0.0.1 (I was gonna put it right back, I promise!), but it didn't work so I attempted to change user root's host to * (which i later realized should have been %). Anyway, I'm now unable to login to the MySQL server even locally, unless I try to log in without a password, in which case it lets me in, but when I try to execute any commands it informs me that I'm not authorized to do that and that I'm user ''@'localhost'. I've shut down the server and run it with --skip-grant-tables, which lets me log in as root and I can actually do stuff, but I'm not sure how to get normal, local passworded root logins back. I just checked and my PHP app (which has it's own set of credentials) works fine. And yes, I put the bind back to 127.0.0.1. In case it isn't obvious I'm pretty new to MySQL.

Server is running Ubuntu 12.04 LTS.

EDIT: To be clear, I'm not currently running it with --skip-grant-tables.

share|improve this question

3 Answers 3

up vote 3 down vote accepted

First of all get rid of --skip-grant-tables with

service mysql restart

Next, let's make sure mysql's root users have the correct password:

SELECT user,host FROM mysql.user WHERE password=PASSWORD('rootpassword');

If nothing comes back, fix the password like this:

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

Next, let's make sure all users have a password:

SELECT user,host FROM mysql.user WHERE password='';

If something comes back, remove those users:

DELETE FROM mysql.user WHERE password='';
FLUSH PRIVILEGES;

When I saw the message

when I try to execute any commands it informs me that im not authorized to do that and that im user ''@'localhost'. I've shut down the server and run it with --skip-grant-tables, which lets me log in as root and I can actually do stuff, but I'm not sure how to get normal, local passworded root logins back

then I said to myself was : OMG he has anonymous logins. Please remove them as follows:

mysql -uroot -p -e"DELETE FROM mysql.user WHERE user=''; FLUSH PRIVILEGES;"

Please also remove all references to test databases from mysql.db

mysql -uroot -p -e"DELETE FROM mysql.db WHERE LEFT(db,4)='test'; FLUSH PRIVILEGES;"

This will secure your mysql installation

share|improve this answer
    
I have no users with a blank name or blank password. I've tried updating the root password as you suggested several times, and I just did it again and for some fucking reason it worked this time. I swear i entered the SAME COMMANDS. goddamn it. Thanks for your help –  thefinn93 May 24 '12 at 1:06

You should NOT keep running mysql server with --skip-grant-table. Otherwise, you will not have any control over the users connecting to your server!

You have to undo the changes you have made to your root user (and any other users). When done, just stop mysql server and start it normally.

share|improve this answer

After starting mysqld with --skip-grant-tables you should login into mysql and execute :


use mysql;
UPDATE user SET Host='%' WHERE Host='*' AND User='root';


After that logout from mysql and restart server without --skip-grant-tables.

Try to login as root and check everything is ok. (priviliges etc.)

--skip-grant-tables is for administration purpose only you should not run mysql normally with this parameter

share|improve this answer

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.