Mysql grant all privileges on ... Do not take effect on the solution

Source: Internet
Author: User
Tags mysql in mysql login

Scenario: I run the Centos,mysql in the virtual machine under the Mac terminal with SSH operation in CentOS

MySQL -u root -p

After logging in to MySQL with Root

Use

Grant  All Privileges  on DB1. *  to [Email protected] ' % '  by ' User1 '  with Grant option;

The user named User1 (the password is User1,identified by followed by a password) to all hosts logged on (% refers to any host ) grants the operations database DB1 all data tables ( Db1.* refers to all the permissions DB1 all data tables .

At this time

Select *  from MySQL. user \g;

Find User1 permissions or N, that is, the above grant operation is invalid, why?

Reason:

At this time under MySQL root login, execute show grants;

Grant  All Privileges  on *. *  to ' Root '@'localhost' xxxxxxxxxxxx

For the root user to log on to the local host, and then I was in the Mac terminal SSH operation in the virtual machine MySQL, the root user of course no ownership.

Workaround: Go back to the virtual machine to log in to MySQL root operation.

In virtual machine MySQL, add:

Grant  All Privileges  on *. *  to ' Root '@'%'by'root password '  withGrantoption;

Add and then use

Select * MySQL. user \g;

Found a row, the content is the user root, the host is%, has all permissions.

Then you can go back to Mac in MySQL login root.

If this does not work, you can use the following methods:

Open Mysqlworkbench on Mac (additional download required), log on to the virtual machine mysql with MySQL root user.

I am at this point the address of the virtual machine is 192.168.20.101 (make sure that the virtual machine's firewall is off or the MySQL port 3306 is turned on).

After logging in, select Users and privileges in the upper-left corner to add a user, and add User Rights administration roles the card.

Mysql grant all privileges on ... Do not take effect on the solution

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.