MySQL permits passwordless logins, wont let me run anything

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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