Upon installation, root@localhost
does not have a password.
You should be able to connect without a password by just doing this:
$ mysql -uroot
Try the following:
$ mysqladmin --no-defaults --port=3308 --user=root password 'somenewpassword'
or you can do it the stubborn way:
$ service mysql restart --skip-grant-tables --skip-networking
$ mysql -e"UPDATE mysql.user SET password=password('somenewpassword') WHERE user='root'"
$ service mysql restart
$ mysql -uroot -p
UPDATE 2013-01-02 16:47 EDT
I am sorry, I overlooked the port number.
Here is the problem: you cannot use root@localhost against a non-3306 port unless you use the TCP/IP protocol. Please try this:
$ mysqladmin --no-defaults --port=3308 --user=root --protocol=tcp password 'somenewpassword'
SHOW GRANTS
only gives you back whatever you are connected as, which was root@localhost
.
root@localhost
and root@'%'
and completely different users. Just do
SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR root@'%';
root@localhost
lets you connect from the DB Server via mysql.sock (the socket file)
root@'%'
lets you connect via TCP/IP, but you must explicitly connect with that protocol. Otherwise, mysqld connects you as root@localhost
. This is true even if you attempted to connect using 127.0.0.1
.
SUGGESTION #1
For the sake of security, you should use netblocks instead of '%'. For example, if you web servers at 10.1.2.20, 10.1.2.30, and 10.1.2.40, you should create
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.1.2.%'
IDENTIFIED BY PASSWORD '*fdfgdgdggfggfgfg' WITH GRANT OPTION;
instead of having a remote root.
SUGGESTION #2
If you really want the remote root, here is what you do
UPDATE mysql.user SET host='%'
WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;
That will do what you want, but remote root is not recommended.
SUGGESTION #3
Since root@'%'
already exists, then remove the root@localhost.
DELETE FROM mysql.user WHERE user='root' AND host='localhost';
FLUSH PRIVILEGES;
This will leave root@'%'
as the only root user, but you must connect explicitly with TCP/IP.
Best Answer
I think the problem stems from how MySQL determines how to authenticate a user.
I mentioned this
Jan 18, 2012
in MySQL error: Access denied for user 'a'@'localhost' (using password: YES)I quote from MySQL 5.0 Certification Study Guide
Pages 486,487 state the following on mysql's authentication algorithm:
Now look at the strings
::1
andlocalhost
. What is the first letter's ASCII value of each ?Since
::1
is alphabetically first, you get authenticated asroot@::1