Allow remote connections after MySQL is installed

Source: Internet
Author: User

1. I use this method to change the table Method!!

(It may be that your account is not allowed to log on remotely, only on localhost.) This time, as long as the computer in localhost, log in to MySQL, the "MySQL" Database in the "User" table "host", from "localhost" renamed "%")


C:/mysql/mysql Server 5.1>mysql-u root-p

Enter the appropriate password

Mysql>use MySQL;

Mysql>show tables; (Verify that the table user exists)

Mysql>update user set host= '% ' where user= ' root ';

Mysql>flush privileges;

Mysql>quit

Quit MySQL

C:/mysql/mysql Server 5.1>net Stop MySQL

C:/mysql/mysql server 5.1>net start MySQL

Now, connect this MySQL server remotely.

C:/mysql/mysql Server 5.1>mysql-h 192.168.1.3-u root-p

2. Authorization Act
For example, if you want to myuser use MyPassword to connect to a MySQL server from any host.

GRANT all privileges on * * to ' myuser ' @ ' percent ' identified by ' MyPassword ' with GRANT OPTION;
If you want to allow users to connect to the MySQL server from a host myuser IP 192.168.1.3 and use MyPassword as the password
GRANT all privileges on * * to ' myuser ' @ ' 192.168.1.3 ' identified by ' MyPassword ' with GRANT OPTION;

Remote connection MySQL Authorization method detailed

Now there is a MySQL database on the server, remote access, do not want to publish the root account, so, created a demo account, allowing the demo account anywhere access to the MySQL database Shandong Library.
Programme one:
Run on the machine where MySQL is installed:
1: Create user users
Copy the code code as follows:
CREATE USER Demo identified by "123456"

2.
Copy the code code as follows:
Mysql>grant all privileges on shandong.* to ' demo ' @ '% ' with GRANT OPTION
Give any host access to the data, or you can do the following
GRANT all privileges the shandong.* to ' demo ' @ '% ' of ' identified by ' 123456 ' with GRANT OPTION;

3.
Copy the code code as follows:
Mysql>flush Privileges
Changes take effect

4,  
Copy the code as follows:
mysql>exit 
//exit the MySQL server so that you can log on as a demo on any other host

Reference  
Additionally, when you connect to MySQL with a client, you find that you cannot connect and it appears that you need to re-authorize the user. The operation is as follows:  
[[email protected] mysql]# bin/mysql-uroot-p-H 127.0.0.1-a cws3 
Enter password: < Br>welcome to the MySQL Monitor. Commands end with or/g. 
Your MySQL Connection ID was 1863 to server version:4.1.20-standard 
Type ' help; ' or ' h ' for help. Type '/C ' to clear the buffer. ,
Mysql> Grant all privileges on * * To [email protected] "%" identified by "MySQL";  
Query OK, 0 rows affected (0.17 sec )  
The remote still cannot connect after this change of permissions, but the following actions are available.  
Mysql> Grant all privileges on * * To [ Email protected] "%" identified by "MySQL" with GRANT option; 
Query OK, 0 rows affected (0.17 sec) &nbs P At this point in time, root can be connected remotely and, of course, other non-root users can connect remotely.

Scenario Two:
MySQL 1130 Error Resolution Method:
This error occurs when you connect to MySQL via Mysql-front or MySQL administrator
ERROR 1130:host ***.***.***.*** isn't allowed to connect to this MySQL server
Indicates that the connected user account does not have remote connection permissions and can only log on natively (localhost).
You need to change the host entry in the user table in the MySQL database
Rename localhost to%

Specific steps: Login to MySQL
First use MySQL;
There was an error when the update was provided in the other way.
mysql> Update user set host= '% ' where user = ' root ';
ERROR 1062 (23000): Duplicate entry '%-root ' for key ' PRIMARY '
The host information for the following database is then viewed as follows:
Mysql> Select host from user where user = ' root ';
+-----------------------+
| Host |
+-----------------------+
| % |
| 127.0.0.1 |
| Localhost.localdomain |
+-----------------------+
3 Rows in Set (0.00 sec)
The host already has the% value, so run the command directly:
Copy the code code as follows:
Mysql>flush privileges;

Then connect with MySQL administrator ... Success!!

Allow remote connections after MySQL is installed

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.