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.
Command line arguments are subject to interpretation by the system's command shell, changing the behavior of the command or changing the value of the arguments before they are passed into the called program.
When an argument (such as the value for --password
) contains an character that the shell may interpret, they need to be either quoted (usually enclosed in single quotes '
in unix or double quote "
in Windows) or escaped individually (usually with a backslash \
before each metacharacter) to avoid interpretation by the shell.
While the specific characters are system-specific, some characters to watch out for include:
$ & ! \ [ ] < > `
If the password, for a really bad example, were set to pa$$word
...
mysql --password=pa$$word # does not work
mysql --password='pa$$word' # works
mysql --password=pa\$\$word # works, but slightly unclear what's going on at first glance
Further reading:
Update: to escape either '
single or "
double quotes in the password, you can either escape them with a leading backslash, or enclose the entire argument in the opposite style of quotes if there are no other characters that the chosen style of quoting isn't compatible with.
mysql --password="like'this" # password has a single quote in the middle
mysql --password='like"this' # password with a double quote in the middle
If you have a single quote and other special characters as well, you're stuck with backslash escaping because, in unix, the double quote is "weaker" than a single quote and many metacharacters are still expanded when enclosed in double quotes but not single quotes.
This is not MySQL-specific but applies to anything with command line arguments.
You can typically use the echo
command to see how the shell is interpreting your arguments.
$ echo foo$bar
foo # literal 'foo' plus the (empty) shell variable $bar
$ echo foo\$bar
foo$bar # backslash prevents expansion of $bar as a variable
$ echo "foo$$bar" # weaker double quote doesn't prevent expansion so
foo9691bar # the $$ expands to the unix process id (pid) of the current shell
$ echo 'foo$$bar'
foo$$bar # "stronger" single quote prevents shell expansion
$ echo "foo'bar"
foo'bar # double quote allows single quote within the literal
Follow-up: The bash shell (and presumably some others) allows escaping single quotes within single-quoted strings, though the convention is bizarre (probably based on some long-forgotten decision lost now to the mists of time):
Replace each '
inside the string with '\''
before wrapping the entire string in single quotes... so the literal string foo'bar
is expressed as 'foo'\''bar'
.
Like I said, bizarre. This is needed because a backslash escapes a single quote outside of a single quoted string, a backslash escapes nothing inside a single-quoted string in bash, and single quoted strings can be closed and reopened by multiple single quotes as long as there are no unescaped intervening characters that have special meaning. So '\''
closes the quoting of the string, then supplies the escaped literal, then reopens the quoting of the string.
Best Answer
It is an authentication error. You can debug this issue by following the below steps:
I hope this answer will help you.