Why Can't I Remotely Connect to MySQL Using the '%' Wildcard?

Connecting Remotely to MySQL Using '%' Wildcard

Question:

When attempting to connect to a MySQL database remotely using the 'user@'%' account, connection issues arise despite successfully connecting using 'user@'localhost'. Why does '%' not allow connections from any host?

Answer:

To establish remote connections, follow these steps:

1. Configure MySQL Bind Address:

Edit the my.cnf (my.ini on Windows) file and set the bind-address parameter to your machine's IP address:

bind-address = xxx.xxx.xxx.xxx

2. Create Wildcard User and Grant Permissions:

Execute the following commands:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';

FLUSH PRIVILEGES;

3. Open Remote Connection Port:

Depending on your operating system, it may be necessary to open port 3306 to allow remote connections.

Explanation:

'%' does allow connections from any host. However, for remote connections, MySQL must be configured to bind to the appropriate IP address. Additionally, the user must be created with both the 'localhost' and '%' wildcard privileges granted for all databases.

The above is the detailed content of Why Can't I Remotely Connect to MySQL Using the '%' Wildcard?. For more information, please follow other related articles on the PHP Chinese website!