How to enable mysql to allow remote connection

By default, mysql only supports localhost connection. Therefore, it is impossible for the remote server to enter an IP address to connect to your server. The following describes how to allow mysql to configure remote connection, for more information, see.

By default, mysql only supports localhost connection. Therefore, it is impossible for the remote server to enter an IP address to connect to your server. The following describes how to allow mysql to configure remote connection, for more information, see.

Method 1: directly use "permission"-"management to modify the user's choice *. * or enter an IP address.

Method 2: run the GRANT command of mysql.

For example, the newuser user needs to use the newpwd password to link the host from IP: 192.168.1.3 to the mysql server.

Procedure:

The Code is as follows:
Mysql> grant all privileges on *. * TO 'newuser' @ '192. 168.1.3 'identified by 'newpwd' with grant option; Mysql> flush privileges;

Complete configuration method

Suppose we have:

The Code is as follows:
Web-Server: 192.168.1.100 // ubuntu Mysql-Server: 192.168.1.101 // xp

We can follow the steps below to modify:
1. log on to Mysql-Server to connect to local mysql (only local connections are allowed by default)

The Code is as follows:
Microsoft Windows XP [version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C: Documents and Settingskuco> mysql-h localhost-u root-p Enter pass: 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 user configuration of Mysql-Server.

The Code is as follows:

Mysql> USE mysql; -- switch to mysql DB Database changed Mysql> SELECT User, Password, Host FROM user; -- view existing users, passwords, and hosts allowed to be connected + ------ + ---------- + ----------- + | User | Password | Host | + ------ + ---------- + ----------- + | Root | localhost | + ------ + ---------- + ----------- + 1 row in set (0.00 sec) Mysql> -- only one default root user. The password is blank and only localhost connection is allowed. Mysql> -- Next we will add another root user. The password is blank and only 192.168.1.100 connections are allowed. Mysql> grant all privileges on *. * TO 'root' @ '192. 168.1.100 'identified by' 'with grant option; Mysql> -- of course, we can also directly use UPDATE to UPDATE the root user Host, but it is not recommended. The SQL is as follows: Mysql> -- UPDATE user SET Host = '192. 168.1.100 'WHERE User = 'root' AND Host = 'localhost' LIMIT 1;

Grant permission name (all for all permissions) on Database Name (* all ). table Name (* all) to 'user name to be authorized' @ '%' (% indicates all IP addresses, only one IP address can be used) identified by "password ";
The user table (Host, User, and Password) is used for identity check. The server accepts the connection only when the Host and user columns recorded in the User table match the client Host name and user name and provide the correct password.
Specify the Host value in the user table:

* The Host value can be the Host name or IP number, or 'localhost' indicates the local Host.
* You can use the wildcard characters "%" and "_" in the Host column value.
* The 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 of '%' matches all Host names, while '% .mysql.com' matches the mysql.com domain.
All hosts.

3. Modify the Mysql configuration file my. ini.

The Code is as follows:
Bind-address = 127.0.0.1

Comment out the bind-address = 127.0.0.1 line and modify it:

The Code is as follows:
# Bind-address = 127.0.0.1

The Mysql-Server configuration is complete.

4. Connect to the Web-Server and check whether it can be connected.

The Code is as follows:
Kuco @ kuco-desktop: // $/opt/lampp/bin/mysql-h 192.168.1.101-u root-p Enter password: Welcome to the MySQL monitor. Commands end with; or g. Your MySQL connection id is 23 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> -- everything is OK