Set MySQL remote connection root permissions

Source: Internet
Author: User

In the remote connection to MySQL should have encountered, the root user can not connect to the remote MySQL, only local connection, foreign refused to connect.

You need to establish a database account that allows remote logins so that you can operate the database remotely.

Here's how:

By default, the MySQL database system database MySQL system table user rights within users only provide localhost native login;

You need to change permissions to enable remote connection to the MySQL database.

This can be confirmed by the following methods:

Root#mysql-h localhost-uroot-p

Enter Password: ******

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 4 to server Version:4.0.20a-debug

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer.

mysql> use MySQL; (This DB holds various configuration information for MySQL)

Database changed

Mysql> select Host,user from user; (View the user's permission condition)

Mysql> Select Host, user, password from user;

+-----------+------+-------------------------------------------+

| Host | user | password |

+-----------+------+-------------------------------------------+

| localhost | Root | *4acfe3202a5ff5cf467898fc58aab1d615029441 |

| 127.0.0.1 | Root | *4acfe3202a5ff5cf467898fc58aab1d615029441 |

|       localhost |                                            | |

+-----------+------+-------------------------------------------+

4 rows in Set (0.01 sec)

As you can see, you can only access it as a host of localhost.

Workaround:

Mysql> Grant All privileges on * * to ' root ' @ '% ' identified by ' kdm001 ' with Grant option;

(% means all external machines, if a certain machine is specified, the% is changed to the corresponding machine name; ' root ' means the username to be used.)

mysql> flush Privileges; (Run this sentence to take effect, or restart MySQL)

Query OK, 0 rows affected (0.03 sec)

Check again:

Mysql> Select Host, user, password from user;

+-----------+------+-------------------------------------------+

| Host | user | password |

+-----------+------+-------------------------------------------+

| localhost | Root | *4acfe3202a5ff5cf467898fc58aab1d615029441 |

| 127.0.0.1 | Root | *4acfe3202a5ff5cf467898fc58aab1d615029441 |

|       localhost |                                            | |

| %          | Root | *4acfe3202a5ff5cf467898fc58aab1d615029441 |

+-----------+------+-------------------------------------------+

4 rows in Set (0.01 sec)

It can be seen that a new user has been added

Quit, try the effect ....

You are now ready to log in successfully.


This article is from the "Operation and maintenance House (Q Group: 1991706)" blog, please be sure to keep this source http://304076020.blog.51cto.com/7503470/1548676

Set MySQL remote connection root permissions

Related Article

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.