How to let MySQL allow remote connection method

Method One, directly uses phpMyAdmin to modify the user select *.* or enter the IP address in "permission" management.

Method Two, use the MySQL grant command to operate

For example: Allow NewUser users to link to the MySQL server from the ip:192.168.1.3 host using the newpwd password

Specific steps:

The code is as follows	Copy Code
Mysql>grant all privileges in *.* to ' newuser ' @ ' 192.168.1.3′identified by ' newpwd ' with GRANT OPTION; Mysql>flush privileges;

Full Configuration method

Suppose we have:

The code is as follows	Copy Code
web-server:192.168.1.100//ubuntu Mysql-server:192.168.1.101//XP

We can follow the following steps to modify:
1, login mysql-server connect local Mysql (default only allow local connection)

The code is as follows	Copy Code
Microsoft Windows XP [version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:documents and Settingskuco>mysql-h localhost-u root-p Enter Password: Welcome to the MySQL Monitor. Commands End With; or G. Your MySQL Connection ID is 13 Server Version:5.1.45-community-log MySQL Community Server (GPL) Type ' help, ' or ' h ' for help. Type ' C ' to clear the current input statement. Mysql>

2, modify the Mysql-server User Configuration

code is as follows

copy code

mysql> Use MySQL; --Switch to MySQL DB Database changed mysql> SELECT user, Password, host from user;--View existing users, passwords, and host computers that are allowed to connect +------ +----------+-----------+ | User | Password | host      | +------+----------+-----------+ | root |          | localhost | +------+----------+-----------+ 1 row in Set (0.00 sec) Mysql>-only one default root user, the password is empty, only allow localhost connection mysql>--below we add a new root user with null password, allowing only 192.168.1.100 to connect mysql> GRANT all privileges on *.* to ' root ' @ ' 192. 168.1.100 ' identified by ' with GRANT OPTION; Mysql>-Of course, we can also update the root Host directly with update, but not recommended, SQL is as follows: mysql>--Update user SET host= ' 192.168.1.100 ' wher E user= ' root ' and host= ' localhost ' LIMIT 1;

The grant permission name (all permissions with all) on the library name (* all). Table name (* All) to ' the username ' to be authorized ' @ '% (% of all IP, can only some IP) identified by "password";
The identity check is performed using the user table (Host, user, and password) in 3 range columns. The server accepts a connection only if the host and user columns of the user table record match the client host name and username and provide the correct password.
Specify the method in the user table host value:

* The host value can be either a hostname or an IP number, or ' localhost ' indicates the local host.
* You can use the wildcard character "%" and "_" in the Host column value.
* Host value '% ' matches any host name, the null host value is equivalent to '% '. They have the same meaning as the pattern-matching operation of the LIKE operator. For example, the '% ' host value matches all host names, while '%.mysql.com ' matches the mysql.com domain
All the hosts.

3, modify the Mysql configuration file My.ini

The code is as follows	Copy Code
Bind-address = 127.0.0.1

Comment out the line bind-address = 127.0.0.1, which is modified to:

The code is as follows	Copy Code
#bind-address = 127.0.0.1

The Mysql-server end configuration is complete.

4, connect web-server, check if you can connect

code is as follows	copy code
Kuco@kuco-deskt op:/$/opt/lampp/bin/mysql-h 192.168.1.101-u root-p Enter Password: Welcome to the MySQL monitor.  Command s end with; or G. Your MySQL connection ID is Server version:5.1.45-community-log MySQL Community server (GPL) Type ' Help, ' or ' h ' for help. Type ' C ' to clear the current input statement. Mysql>--All OK